varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

19-Sept-2005 Issue: 119

Archives | General Tidbits | Google General Bits | Docs | Castellano | Português | Subscriptions | Notifications | | Prev | Next

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org.

Helpful Little functions
Various Utility Functions 14-Sept-2005

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;

Contributors: david at fetter.org, elein at varlena.com
Where's my ODBC?
[pgsql-advocacy] hard to find odbc driver 14-Sep-2005

ODBC is hard to find through the PostgreSQL website. It isn't directly listed on the Downloads page. This is where you go:

In versions you have a choice of dll, mm, msi and src.

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


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

Top
Google
Search General Bits & varlena.com Search WWW