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,
ordinal_position,
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)),
plural_or_singular AS (SELECT routine_schema, routine_name, parameters, parameter_name,
ordinal_position, data_type,
CASE WHEN regexp_replace(parameter_name,'(stats_|stats$|status|state|is_|has_|pos$|alias|cvs|address|value$|key$|pays$|group$|lock$|_to$|_id$|_code$|_type$|_name$|posts_to|sales_tax|trans_type|_date$|_time$|_flag$|number|sys|class$|basis$)','','g') ~* '(?<!((^|_)[[:alnum:]]{0,2}|(^|_)[bcdfghjklmnpqrsztvwxz]{0,4}|s))s(_|$)'
OR parameter_name~*'(ids|codes)$' THEN 'Perhaps plural' ELSE 'Perhaps singular' END AS comment_about_the_parameter_name
FROM params)
SELECT routine_schema, routine_name, parameters, parameter_name, ordinal_position, data_type, comment_about_the_parameter_name,
CASE WHEN comment_about_the_parameter_name='Perhaps plural' AND data_type!~*'(array|json|xml|int|numeric|float)' THEN 'Perhaps should be singular'
WHEN comment_about_the_parameter_name='Perhaps singular' AND data_type~*'(array|json|xml)' THEN 'Perhaps should be plural'
ELSE 'OK' END AS evaluation
FROM plural_or_singular
ORDER BY evaluation DESC, comment_about_the_parameter_name, routine_schema, routine_name, parameters;