CODEWITHSUNDEEP
sqlpostgresql
SMW
SMW

Reputation: 339

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

Upvotes: 0

Views: 40

Answers (1)

user330315
user330315

Reputation:

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'

Upvotes: 1

Related Questions