WITH Tr AS (SELECT
n.nspname AS trigger_schema,
t.tgname AS trigger_name,
em.text AS event_manipulation,
CASE WHEN c.relkind IN ('r','p') THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE' END AS table_type,
c.relname AS trigger_table
FROM pg_namespace n,
pg_class c,
pg_trigger t,
pg_proc p,
pg_namespace np,
( VALUES (4,'INSERT'), (8,'DELETE'), (16,'UPDATE'), (32,'TRUNCATE')) em(num, text)
WHERE n.oid = c.relnamespace
AND c.oid = t.tgrelid
AND t.tgfoid=p.oid
AND p.pronamespace=np.oid
AND (t.tgtype::integer & em.num) <> 0
AND NOT t.tgisinternal
AND NOT pg_is_other_temp_schema(n.oid))
SELECT Tr.trigger_schema, Tr.trigger_table, Tr.table_type, Count(DISTINCT Tr.trigger_name) AS number_of_triggers,
string_agg(Tr.event_manipulation,',' ORDER BY Tr.event_manipulation) AS events
FROM Tr
GROUP BY Tr.trigger_schema, Tr.trigger_table, Tr.table_type
ORDER BY Tr.trigger_schema, Tr.trigger_table;