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:

• PostgreSQL Home Page
• Download Page
• via FTP
• odbc
• versions

This may be corrected someday, but until then this is how where can find it in one pass: http://www.postgresql.org/ftp/odbc/

Contributors: Chris Mair list at 1006.org