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 parameter_name ~* '(d$|.+(d|[^t]t)e_.+)'
AND parameter_name !~*'((^|_)id$|kood$|(^|_)oid$|(^|_)uuid$|hind$|seisund$|brand$|^d*d$|periood$|arv$|laud$|kraad$|l(ü|y)hend$)'
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;