Postgres find which tables are modified in DML trigger

Question

I have hundreds of DML triggers across multiple schemas. Most of them modify the DML of more than one table.

Using information_schema.triggers and pg_trigger tables I am able to find the list of triggers and the trigger event tables alone.

Is it possible to get the list of tables that are modified(trigger target tables) by each trigger?

Laurenz Albe · Accepted Answer · 2020-01-28 12:46:17Z

1

No, because the trigger action is a function, and functions are stored as plain strings in the prosrc column of the pg_proc system catalog table.

You could perform a case insensitive search for table names in that column, but you are going to come up with false positives (any if dynamic SQL is used, with a couple of false negatives too).

answered Jan 28, 2020 at 12:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres find which tables are modified in DML trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related