How to check if trigger exists in PostgreSQL?

Question

I want to check the correct migration of databases that add triggers to some tables. I am using sqitch , so I would like to find a way to test it using SQL queries. I believe this is possible using postgres system tables, but at present I cannot find a way to do this.

Answer 1

Use the pg_trigger directory.

A simple search for the books table:

 select tgname from pg_trigger where not tgisinternal and tgrelid = 'books'::regclass; tgname --------------- books_trigger (1 row) 

Using pg_proc to get the source of the trigger function:

 select tgname, proname, prosrc from pg_trigger join pg_proc p on p.oid = tgfoid where not tgisinternal and tgrelid = 'books'::regclass; tgname | proname | prosrc ---------------+---------------+------------------------------------------------ books_trigger | books_trigger | + | | begin + | | if tg_op = 'UPDATE' then + | | if new.listorder > old.listorder then + | | update books + | | set listorder = listorder- 1 + | | where listorder <= new.listorder + | | and listorder > old.listorder + | | and id <> new.id; + | | else + | | update books + | | set listorder = listorder+ 1 + | | where listorder >= new.listorder + | | and listorder < old.listorder + | | and id <> new.id; + | | end if; + | | else + | | update books + | | set listorder = listorder+ 1 + | | where listorder >= new.listorder + | | and id <> new.id; + | | end if; + | | return new; + | | end (1 row) 

An example of using the pg_get_triggerdef() function:

 select pg_get_triggerdef(t.oid) as "trigger declaration" from pg_trigger t where not tgisinternal and tgrelid = 'books'::regclass; trigger declaration -------------------------------------------------------------------------------------------------------------- CREATE TRIGGER books_trigger BEFORE INSERT OR UPDATE ON books FOR EACH ROW EXECUTE PROCEDURE books_trigger() (1 row)