The names should contain table name in order to make the names better understandable.
Notes
The query does not return information about internally generated triggers, i.e., triggers that enforce, for instance, referential integrity. The query considers a possibility that underscores (_) in the table name have been removed from the trigger/rule name.
Type
Problem detection (Each row in the result could represent a flaw in the design)
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 NOT ILIKE '%' || table_name || '%'
AND translate(active_element_name,'_','') NOT 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 automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem 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.