Find pairs of names of different types of routines that are very similar but not equal.
Notes
The query finds the pairs of routine names where the names are not equal but the Levenshtein distance between the names is less than three. The query uses a function from the fuzzystrmatch extension. The query does not consider routines of extensions because PostgreSQL supports overloading of routines.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Make sure that the names are correct and there are no duplication or unused routines.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
WITH routines AS (SELECT routine_schema, routine_name, specific_name, routine_type
FROM INFORMATION_SCHEMA.routines AS r
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 NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d inner join pg_catalog.pg_proc pc ON d.objid=pc.oid
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
pc.proname || '_' || pc.oid = r.specific_name))
SELECT r1.routine_schema AS routine1_schema, r1.routine_name AS routine1_name, r1.routine_type AS routine1_type,
r2.routine_schema AS routine2_schema, r2.routine_name AS routine2_name, r2.routine_type AS routine1_type
FROM routines AS r1, routines AS r2
WHERE r1.specific_name<>r2.specific_name
AND r1.routine_name<>r2.routine_name
AND levenshtein(r1.routine_name,r2.routine_name)<=2
ORDER BY routine1_name, routine2_name;
DROP EXTENSION IF EXISTS fuzzystrmatch;
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
Description
Duplication of implementation elements
Queries of this catergory provide information about the duplication of the database objects.
Naming
Queries of this category provide information about the style of naming.
Overloading
Queries of this category provide information about overloading of routines.
Unused implementation elements
Queries of this catergory provide information about the database objects that are not used.
User-defined routines
Queries of this category provide information about the user-defined routines