Clone all user defined triggers in schema

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Clone all user defined triggers in schema
Date: 2006-11-26 00:31:06
Message-ID: ekanad$572$2@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message John DeSoi 2006-11-26 01:41:13 Re: Development of cross-platform GUI for Open Source DBs
Previous Message novnov 2006-11-25 23:16:21 Re: Editing contrib modules which are loaded by default?