WITH active_db AS (SELECT
t.tgname AS active_element_name,
n.nspname AS table_schema,
c.relname AS table_name,
'TRIGGER' AS type
FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class c,
pg_catalog.pg_trigger t
WHERE n.oid = c.relnamespace
AND c.oid = t.tgrelid
AND t.tgisinternal=FALSE
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION ALL SELECT
r.rulename,
n.nspname AS schemaname,
c.relname AS tablename,
'RULE' AS type
FROM pg_rewrite r JOIN pg_class c ON c.oid = r.ev_class JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE r.rulename <> '_RETURN'::name
AND n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT type, active_element_name AS suspected_active_element_name, table_schema, table_name
FROM active_db
WHERE active_element_name ILIKE '%' || table_name || '%'
OR translate(active_element_name,'_','') ILIKE '%' || translate(table_name,'_','') || '%'
ORDER BY table_schema, table_name, type, active_element_name;