Query goal: | Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent. |
Notes about the query: | 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. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Fixing suggestion: | Rename trigger with an ALTER TRIGGER statement. Rename rule with an ALTER RULE statement. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
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), '<b>table</b>') AS pattern FROM active_db) AS pat GROUP BY pattern ORDER BY Count(*) DESC, pattern; |
Collection name | Collection 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 |
Category name | Category 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. |
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. |