Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent.
Notes
To find patterns the query replaces in the name table name with the lowercase word "table". The word is between (b) tags for the better readability in case the query result is displayed in a web browser. The query does not consider internal triggers, which are generated by the system to implement a constraint (for instance, a referential constraint). The query does not find SELECT rules that are created automatically by the system to support views.
Type
General (Overview of some aspect of the database.)
Rename trigger with an ALTER TRIGGER statement. Rename rule with an ALTER RULE statement.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH active_db AS (SELECT t.tgname AS trigger_name,
c.relname AS table_name
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, r.tablename
FROM
pg_catalog.pg_rules r, pg_catalog.pg_namespace n, pg_catalog.pg_authid u
WHERE r.schemaname = n.nspname
AND n.nspowner = u.oid
AND (n.nspname = 'public' OR u.rolname <> 'postgres'))
SELECT pattern, Count(*) AS cnt
FROM (SELECT replace(trigger_name, lower(table_name), 'table') AS pattern
FROM active_db) AS pat
GROUP BY pattern
ORDER BY Count(*) DESC, pattern;
Collections
This query belongs to the following collections:
Name
Description
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 .
Lexicon bad smells and linguistic antipatterns
Queries made to find the occurrences of lexicon bad smells and linguistic antipatterns
Categories
This query is classified under the following categories:
Name
Description
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
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.
Further reading and related materials:
Reference
Smell "Identifier construction rules": Abebe, S.L., Haiduc, S., Tonella, P. and Marcus, A., 2011, September. The effect of lexicon bad smells on concept location in source code. In 2011 IEEE 11th International Working Conference on Source Code Analysis and Manipulation (pp. 125-134). IEEE.