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/