Goal Find the names of parameters that are too generic. The routines work with data/information. Thus, it is not a good style to use generic words like data, information, parameter, etc. In the names of parameters. Moreover, avoid too generic parameter names like: id, tyyp, kood, aeg, kp,type, code, time, date, fk, pk, param.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
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))
SELECT routine_schema, routine_name, parameters, parameter_name AS suspected_name, ordinal_position, data_type
FROM params
WHERE (parameter_name~*'(andme(?!baas)|(?<!eel)data(?!base)|info|parameter|parameeter)'
OR parameter_name~*'^([^[:alpha:]]*|(in|out)[_]*)(id|tyyp|tuup|kood|aeg|kp|type|code|time|date|fk|pk|value|info|data|number|param)([^[:alpha:]]*|[_]*(in|out))$' 
OR parameter_name~*'^([^[:alpha:]]*|(in|out)[_]*)(nimi|nimetus|kommentaar(id){0,1}|kirjeldus(ed){0,1}|staatus|seisund|olek|tyyp|tüüp|name(s){0,1}|comment(s){0,1}|description(s){0,1}|explanation(s){0,1}|state|status|type)([^[:alpha:]]*|[_]*(in|out))$'
) 
ORDER BY routine_schema, routine_name, parameters, ordinal_position;

Collections

This query belongs to the following collections:

Find problems about names

A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.

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 .

NameDescription
Find problems about namesA selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview.
Find problems automaticallyQueries, 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:

Naming

Queries of this category provide information about the style of naming.

User-defined routines

Queries of this category provide information about the user-defined routines

NameDescription
NamingQueries of this category provide information about the style of naming.
User-defined routinesQueries of this category provide information about the user-defined routines