Query goal: | Find named input parameters of routines that have the same name but different type. It could be a mistake or deliberate due to overloading of routines. |
Notes about the query: | The query does not consider objects that are a part of an extension. In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Low (Many false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Change the name of the parameter or the type of the parameter to ensure consistency. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH params AS (SELECT specific_schema AS routine_schema, regexp_replace(specific_name,'_[0-9]*$','') AS routine_name, pg_get_function_identity_arguments(translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid) AS parameters, parameter_name, data_type FROM information_schema.parameters WHERE specific_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 = parameters.specific_name) AND parameter_mode='IN' AND parameter_name IS NOT NULL) SELECT parameter_name, Count(*) AS number_of_params, Count(DISTINCT data_type) AS number_of_different_types, string_agg(routine_schema ||'.'|| routine_name || '(' || parameters || ') ' || Upper(data_type),';<br>' ORDER BY data_type, routine_schema, routine_name) AS types FROM params AS p GROUP BY parameter_name HAVING Count(DISTINCT data_type)>1 ORDER BY Count(DISTINCT data_type) DESC, Count(*) DESC, parameter_name; |
Category name | Category description |
---|---|
Data types | Queries of this category provide information about the data types and their usage. |
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. |
Overloading | Queries of this category provide information about overloading of routines. |
User-defined routines | Queries of this category provide information about the user-defined routines |