Re: 7.1 PL/pgSQL EXECUTE Command

On Wed, 3 Jan 2001, Brian Troxell wrote:

> I know that the beta 7.1 release adds the capability to do dynamic
> queries in PL/pgSQL using the new EXECUTE command.

From one of my triggers:

-- Create trigger function to be run upon deletes
CREATE FUNCTION spares_dbs_delete_trig() RETURNS opaque AS '
BEGIN

EXECUTE '' DROP TRIGGER spares_'' || OLD.dbs_name || ''_update_trig ON
'' || OLD.dbs_name || '';'';

EXECUTE '' DROP TABLE '' || OLD.dbs_name || '';'';
EXECUTE '' DROP SEQUENCE '' || OLD.dbs_name || ''_'' || OLD.dbs_name ||
''_id_seq;'';

RETURN OLD;
END;
' LANGUAGE 'plpgsql';

Assuming that OLD.dbs_name == 'site1', will do the following:

DROP TRIGGER spares_site1_update_trig ON site1;
DROP TABLE site1;
DROP SEQUENCE site1_site1_id_seq;

As to what in the world I'm doing... don't ask. It's messy :)

--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/