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]','
','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;