WITH Tr AS (SELECT
n.nspname AS trigger_schema,
t.tgname AS trigger_name,
em.text AS event_manipulation,
c.relname AS trigger_table,
np.nspname AS routine_schema,
p.proname AS routine_name,
p.prosrc AS routine_definition,
(SELECT DISTINCT action_condition FROM INFORMATION_SCHEMA.triggers AS it WHERE it.trigger_schema=n.nspname AND it.event_object_table=c.relname AND it.trigger_name=t.tgname) AS action_condition,
CASE t.tgtype::integer & 1
WHEN 1 THEN 'ROW'
ELSE 'STATEMENT'
END AS action_orientation,
CASE t.tgtype::integer & 66
WHEN 2 THEN 'BEFORE'
WHEN 64 THEN 'INSTEAD OF'
ELSE 'AFTER'
END AS action_timing,
EXISTS (SELECT 1 FROM pg_constraint AS c WHERE c.oid=t.tgconstraint AND c.contype='t') AS is_constraint_trigger,
t.tginitdeferred AS is_init_deferred
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)),
triggers_columns AS (SELECT Tr.trigger_schema,
trigger_table,
action_timing,
event_manipulation,
action_orientation,
action_condition,
string_agg(coalesce(event_object_column,'')::text, ',<br>' ORDER BY event_object_column) AS update_columns,
is_constraint_trigger,
is_init_deferred,
Tr.trigger_name,
routine_name,
regexp_replace(routine_definition,'[\r\n]','<br>','g') AS routine_definition
FROM Tr LEFT JOIN information_schema.triggered_update_columns AS TUC ON Tr.trigger_schema=TUC.trigger_schema AND Tr.trigger_name=TUC.trigger_name AND
Tr.trigger_table=TUC.event_object_table
GROUP BY Tr.trigger_schema, Tr.trigger_table, action_timing, event_manipulation, action_orientation, action_condition, is_constraint_trigger, is_init_deferred, Tr.trigger_name, routine_name, regexp_replace(routine_definition,'[\r\n]','<br>','g'))
SELECT routine_definition,
string_agg(DISTINCT action_timing || ' ' || event_manipulation || ' ' || action_orientation || CASE WHEN action_condition IS NOT NULL THEN ' WHEN(' || action_condition || ') ' ELSE ' ' END || trigger_schema || '.' || trigger_table || CASE WHEN update_columns<>'' THEN '(' || update_columns || ')' ELSE '' END, ',<br>') AS triggers
FROM triggers_columns
GROUP BY routine_definition;