|
||||||||||||
|
||||||||||||
|
||||||||||||
|
||||||||||||
|
||||||||||||
Generate Month Numbers (for drop down boxes). The function returns a row for each month, for example, "1 January", "2 February", etc.
CREATE TYPE monthnum ( num INTEGER, month TEXT ); CREATE OR REPLACE FUNCTION monthnums() RETURNS SETOF monthnum AS ' DECLARE monrow monthnum%ROWTYPE; i integer; mon text; BEGIN FOR i IN 1..12 LOOP SELECT INTO mon to_char((i::text || ''/15/'' || ''03'')::date, ''Mon''); monrow.num = i; monrow.month = mon; RETURN NEXT monrow; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; COMMENT ON FUNCTION monthnums(TEXT) IS $$ Returns type monthnum tuples like (1, 'Jan')(2, 'Feb'), etc. $$; An even simpler version offers the same functionality in a view which does the same LOOP via generate_series(): CREATE VIEW monthnums AS SELECT s.i+1, to_char(date_trunc('year', current_date)+s.i*'1 month'::interval, 'Month') FROM generate_series(0,11) AS s(i); COMMENT ON VIEW monthnums IS $$ Returns tuples like (1, 'Jan')(2, 'Feb'), etc. $$;
Technique for Catching and Ignoring Errors When you want to ignore errors for task such as DROP TABLE you can create a function which catches the exception and simply raises a notice or performs a different action. The valid exceptions are listed in Appendix A. of the manual. Just insert underscores where the spaces happen to be. CREATE OR REPLACE FUNCTION drop_table(TEXT) RETURNS VOID STRICT LANGUAGE plpgsql AS $$ BEGIN BEGIN EXECUTE 'DROP TABLE ' || $1; EXCEPTION WHEN UNDEFINED_TABLE THEN RAISE NOTICE 'Table % not defined. Moving on anyhow.', $1; RETURN; END; RAISE NOTICE 'Dropped table %', $1; RETURN; END; $$; COMMENT ON FUNCTION drop_table(TEXT) IS $$ This function drops a table if it exists and does not raise an error if it does not exist. $$;
Radio Buttons If only one row in a table can contain TRUE while all others must contain FALSE, then that column must behave like a radio button. If one row is set to true then all others must be set to false. A common example of this is a set of email addresses where only one is the "main" address. This example is a team where only one person can be captain. Here we set a trigger to set the old captain to FALSE before we insert or update the new record.
DROP TABLE players CASCADE; CREATE TABLE players (id SERIAL PRIMARY KEY, pname TEXT, captain BOOLEAN); INSERT INTO players VALUES (DEFAULT, 'Sarah', 'f'); INSERT INTO players VALUES (DEFAULT, 'Katie', 'f'); INSERT INTO players VALUES (DEFAULT, 'Lacey', 'f'); INSERT INTO players VALUES (DEFAULT, 'Fairlie', 't'); CREATE OR REPLACE FUNCTION onetrue_captain () RETURNS TRIGGER AS ' BEGIN IF NEW.captain = TRUE THEN UPDATE players SET captain = FALSE WHERE captain = TRUE; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; COMMENT ON ontrue_captain IS 'Set radio button trigger on captain column for table players'; CREATE TRIGGER onecaptainins BEFORE INSERT ON players FOR EACH ROW EXECUTE PROCEDURE onetrue_captain(); CREATE TRIGGER onecaptainupd BEFORE UPDATE ON players FOR EACH ROW EXECUTE PROCEDURE onetrue_captain(); SELECT * FROM players; INSERT INTO players VALUES (DEFAULT, 'Jill', 't'); SELECT * FROM players; INSERT INTO PLAYERS VALUES (DEFAULT, 'Ophelia', 't'); SELECT * FROM players; UPDATE players SET captain = TRUE WHERE id = 3; SELECT * FROM players;
ODBC is hard to find through the PostgreSQL website. It isn't directly listed on the Downloads page. This is where you go:
This may be corrected someday, but until then this is how where can find it in one pass: http://www.postgresql.org/ftp/odbc/
|
||||||||||||
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in! Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009 |