Get triggers/table names in PostgreSQL

Question

How can I get a listing of all triggers for a database along w/ the table that it belongs to?

I assume that I need pg_trigger and pg_tables but I'm not quite sure how to join them together.

Answer 1

This works for me:

select trg.tgname,
 CASE trg.tgtype::integer & 66
 WHEN 2 THEN 'BEFORE'
 WHEN 64 THEN 'INSTEAD OF'
 ELSE 'AFTER'
 end as trigger_type,
 case trg.tgtype::integer & cast(28 as int2)
 when 16 then 'UPDATE'
 when 8 then 'DELETE'
 when 4 then 'INSERT'
 when 20 then 'INSERT, UPDATE'
 when 28 then 'INSERT, UPDATE, DELETE'
 when 24 then 'UPDATE, DELETE'
 when 12 then 'INSERT, DELETE'
 end as trigger_event,
 ns.nspname||'.'||tbl.relname as trigger_table,
 obj_description(trg.oid) as remarks,
 case
 when trg.tgenabled='O' then 'ENABLED'
 else 'DISABLED'
 end as status,
 case trg.tgtype::integer & 1
 when 1 then 'ROW'::text
 else 'STATEMENT'::text
 end as trigger_level
FROM pg_trigger trg
 JOIN pg_class tbl on trg.tgrelid = tbl.oid
 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE trg.tgname not like 'RI_ConstraintTrigger%'
 AND trg.tgname not like 'pg_sync_pg%'

It doesn't include DDL triggers though.

Answer 2

here is improved one, which gets trigger function with its namespace:

 SELECT trg.tgname as tigger_name,
 CASE trg.tgtype::INTEGER & 66
 WHEN 2 THEN 'BEFORE'
 WHEN 64 THEN 'INSTEAD OF'
 ELSE 'AFTER'
 END AS trigger_type,
 CASE trg.tgtype::INTEGER & cast(28 AS INT2)
 WHEN 16 THEN 'UPDATE'
 WHEN 8 THEN 'DELETE'
 WHEN 4 THEN 'INSERT'
 WHEN 20 THEN 'INSERT, UPDATE'
 WHEN 28 THEN 'INSERT, UPDATE, DELETE'
 WHEN 24 THEN 'UPDATE, DELETE'
 WHEN 12 THEN 'INSERT, DELETE'
 END AS trigger_event,
 ns.nspname||'.'||tbl.relname AS trigger_table,
 obj_description(trg.oid) AS remarks,
 CASE
 WHEN trg.tgenabled='O' THEN 'ENABLED'
 ELSE 'DISABLED'
 END AS status,
 CASE trg.tgtype::INTEGER & 1
 WHEN 1 THEN 'ROW'::TEXT
 ELSE 'STATEMENT'::TEXT
 END AS trigger_level,
 n.nspname || '.' || proc.proname AS function_name
 FROM pg_trigger trg
 JOIN pg_proc proc ON proc.oid = trg.tgfoid
 JOIN pg_catalog.pg_namespace n ON n.oid = proc.pronamespace
 JOIN pg_class tbl ON trg.tgrelid = tbl.oid
 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
 WHERE 
 trg.tgname not like 'RI_ConstraintTrigger%'
 AND trg.tgname not like 'pg_sync_pg%';

Answer 3

Start up psql with -E. That will show you the commands it issues. Then run \dft to show the triggers.

The SQL it generates is,

SELECT n.nspname as "Schema",
 p.proname as "Name",
 pg_catalog.pg_get_function_result(p.oid) as "Result data type",
 pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
 WHEN p.proisagg THEN 'agg'
 WHEN p.proiswindow THEN 'window'
 WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
 ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE (
 p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
 )
 AND pg_catalog.pg_function_is_visible(p.oid)
 AND n.nspname <> 'pg_catalog'
 AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

That will return a table like this,

 Schema | Name | Result data type | Argument data types | Type 
--------+--------------------+------------------+---------------------+---------
 public | checkauthtrigger | trigger | | trigger
 public | do_stupid_stuff | trigger | | trigger
 public | postgis_cache_bbox | trigger | | trigger
(3 rows)