Thread: Clone all user defined triggers in schema

During schema duplication, I need to duplicate all user-defined triggers in
new schema in 8.1+

I created a query which returns trigger definitions which will be run
against new
schema.

Is this the best way or is it possible to get CREATE TRIGGER statements
without schema names or with new schema names directly ?


SELECT
  REPLACE( pg_catalog.pg_get_triggerdef(pg_trigger.oid),
     ' ON mycurrentschema.', ' ON mynewschema.' ) as newtriggerdef
FROM pg_catalog.pg_trigger
join pg_catalog.pg_class on pg_trigger.tgrelid = pg_class.oid
JOIN pg_catalog.pg_namespace ON pg_namespace.oid=pg_class.relnamespace
WHERE not pg_trigger.tgisconstraint
    and pg_namespace.nspname='myschema'


Andrus.