How to check which table is called which trigger in postgresql?

Question

I have ten tables and five triggers in my database. I don't know the trigger name, but I need to edit the trigger. So I need to know which table is called to which trigger? Using,

SELECT tgname 
FROM pg_trigger;

we can print all the trigger name. But it did not show which table is called to which trigger. Can anyone explain to me?

Answer 1

You need to join that to pg_class:

SELECT trg.tgname,
       ns.nspname||'.'||tbl.relname as trigger_table
FROM pg_trigger trg
 JOIN pg_class tbl on trg.tgrelid = tbl.oid
 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE NOT tgisinternal

Answer 2

Use information_schema.triggers. It's more portable and simpler.

test=> \x
Expanded display is on.
test=> select * from information_schema.triggers;
-[ RECORD 1 ]--------------+---------------------------------
trigger_catalog            | testuser
trigger_schema             | public
trigger_name               | some_trigger
event_manipulation         | INSERT
event_object_catalog       | craig
event_object_schema        | public
event_object_table         | some_table
action_order               | 
action_condition           | 
action_statement           | EXECUTE PROCEDURE some_trigger()
action_orientation         | STATEMENT
action_timing              | BEFORE
action_reference_old_table | 
action_reference_new_table | 
action_reference_old_row   | 
action_reference_new_row   | 
created                    | 

The only downside is that you don't get the raw trigger function name like you do from joining pg_trigger.tgfoid on pg_proc.oid to get pg_proc.proname. You get the trigger name and you get an action_statement field with EXECUTE PROCEDURE funcname(args).

Answer 3

You can use the below query, It will show the user defined triggers with detailed description.

SELECT event_object_table,trigger_name,event_manipulation,action_statement,action_timing FROM information_schema.triggers ORDER BY event_object_table,event_manipulation;

It will give the trigger name, table name, event manipulation, action and action timing.