Query goal: | Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Associate the function with a trigger or drop it. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH trigger_routines AS (SELECT routines.routine_schema, routines.routine_name FROM information_schema.routines WHERE routine_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND data_type='trigger'), routines_with_triggers AS (SELECT n.nspname AS routine_schema, p.proname AS routine_name FROM pg_catalog.pg_trigger t INNER JOIN pg_catalog.pg_proc p ON t.tgfoid=p.oid INNER JOIN pg_catalog.pg_namespace n ON p.pronamespace=n.oid ) SELECT routine_schema, routine_name FROM trigger_routines tr WHERE NOT EXISTS (SELECT 1 FROM routines_with_triggers rwt WHERE tr.routine_schema=rwt.routine_schema AND tr.routine_name=rwt.routine_name) ORDER BY routine_schema, routine_name; |
SQL query | Description |
---|---|
WITH trigger_routines AS (SELECT routines.routine_schema, routines.routine_name FROM information_schema.routines WHERE routine_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND data_type='trigger'), routines_with_triggers AS (SELECT n.nspname AS routine_schema, p.proname AS routine_name FROM pg_catalog.pg_trigger t INNER JOIN pg_catalog.pg_proc p ON t.tgfoid=p.oid INNER JOIN pg_catalog.pg_namespace n ON p.pronamespace=n.oid ) SELECT format('DROP FUNCTION %1$I.%2$I RESTRICT;', routine_schema, routine_name) AS statements FROM trigger_routines tr WHERE NOT EXISTS (SELECT 1 FROM routines_with_triggers rwt WHERE tr.routine_schema=rwt.routine_schema AND tr.routine_name=rwt.routine_name) ORDER BY routine_schema, routine_name; | Drop the function. The query assumes that trigger functions do not have parameters. |
Collection name | Collection description |
---|---|
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 . |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Triggers and rules | Queries of this category provide information about triggers and rules in a database. |
Unused implementation elements | Queries of this catergory provide information about the database objects that are not used. |
Reference |
---|
https://en.wikipedia.org/wiki/Dead_code |
The corresponding code smells in case of cleaning code are "F4: Dead Function" and "G9: Dead Code". (Robert C. Martin, Clean Code) |