WITH trigger_routines_with_tg_op AS (SELECT
p.oid,
np.nspname AS routine_schema,
p.proname AS routine_name,
p.prosrc AS routine_definition,
regexp_replace(p.prosrc,'[\r\n]','<br>','g') AS routine_definition_format
FROM pg_proc p,
pg_namespace np,
pg_type
WHERE p.pronamespace=np.oid
AND p.prorettype=pg_type.oid
AND pg_type.typname='trigger'
AND np.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND p.prosrc~*'TG_OP')
SELECT routine_schema, routine_name, routine_definition_format, 'Is not associated with an INSERT trigger' AS explanation
FROM trigger_routines_with_tg_op AS tp
WHERE routine_definition~*'''INSERT'''
AND NOT EXISTS (SELECT *
FROM pg_trigger
WHERE pg_trigger.tgfoid=tp.oid
AND (tgtype::integer & 4)<>0)
UNION SELECT routine_schema, routine_name, routine_definition_format, 'Is not associated with an UPDATE trigger' AS explanation
FROM trigger_routines_with_tg_op AS tp
WHERE routine_definition~*'''UPDATE'''
AND NOT EXISTS (SELECT *
FROM pg_trigger
WHERE pg_trigger.tgfoid=tp.oid
AND (tgtype::integer & 16)<>0)
UNION SELECT routine_schema, routine_name, routine_definition_format, 'Is not associated with a DELETE trigger' AS explanation
FROM trigger_routines_with_tg_op AS tp
WHERE routine_definition~*'''DELETE'''
AND NOT EXISTS (SELECT *
FROM pg_trigger
WHERE pg_trigger.tgfoid=tp.oid
AND (tgtype::integer & 8)<>0)
UNION SELECT routine_schema, routine_name, routine_definition_format, 'Is not associated with a TRUNCATE trigger' AS explanation
FROM trigger_routines_with_tg_op AS tp
WHERE routine_definition~*'''TRUNCATE'''
AND NOT EXISTS (SELECT *
FROM pg_trigger
WHERE pg_trigger.tgfoid=tp.oid
AND (tgtype::integer & 32)<>0)
ORDER BY routine_schema, routine_name;