SMW SMW - 4 months ago 8
SQL Question

Given a trigger function name - how do I get tables it is set on?

Some tables has trigger functions:

CREATE TRIGGER x
AFTER INSERT OR UPDATE OR DELETE
ON tab_1 FOR EACH ROW
EXECUTE PROCEDURE trige2();


So basicly if I know the table name I can easily get the triggers becuase it is in the table properties in PgAdmin.

But what if I only know the trigger procedure name
trige2
and I want to get list of all tables the trigger is invoked by.

In the above example the result should be
tab_1

Answer

This can be done by joining pg_trigger, pg_class and pg_proc

SELECT ps.nspname as function_schema, 
       p.proname as trigger_function, 
       ts.nspname as table_schema, 
       tbl.relname as table_name, 
       trg.tgname as trigger_name
FROM pg_trigger trg
  JOIN pg_class tbl on tbl.oid = trg.tgrelid
  JOIN pg_namespace ts on ts.oid = tbl.relnamespace
  JOIN pg_proc p on p.oid = trg.tgfoid
  JOIN pg_namespace ps on ps.oid = p.pronamespace

With the above you can get the information by either supplying the table name using where tbl.relname = 'tab_1' or supplying the trigger name using where trg.tgname = 'x' or supplying the function name: where p.proname = 'trige2'

Comments