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;
Collections
This query belongs to the following collections:
Name
Description
Find problems about names
A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
Description
Naming
Queries of this category provide information about the style of naming.
Triggers and rules
Queries of this category provide information about triggers and rules in a database.