The list of all the queries

Input parameters with the same name have different types

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;

Categories where the query belongs to

Category nameCategory description
Data typesQueries of this category provide information about the data types and their usage.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
NamingQueries of this category provide information about the style of naming.
OverloadingQueries of this category provide information about overloading of routines.
User-defined routinesQueries of this category provide information about the user-defined routines

The list of all the queries