Goal Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name.
Notes The query does not consider objects that are a part of an extension. The names that contain type names date and text are omitted because these are often a part of the name that one cannot omit.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Low (Many false-positive results)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH named_objects AS (
SELECT 
translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid AS oid,
specific_schema || '.' || regexp_replace(specific_name,'_[0-9]*$','') || '(' || pg_get_function_identity_arguments(translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid) || ')' AS container_name, 
'ROUTINE' AS container_type,
parameter_name AS object_name, 
 'PARAMETER' AS object_type,
 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 parameter_name IS NOT NULL

UNION ALL SELECT 
c.oid,
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
at.attname AS object_name,
'COLUMN' AS object_type,
co.data_type
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN INFORMATION_SCHEMA.columns AS co ON co.table_schema=n.nspname AND co.table_name=c.relname AND co.column_name=at.attname
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r','v','m','f','p')
AND at.attisdropped='f'
AND at.attnum>0

),
named_user_defined_objects AS (
SELECT container_name, container_type, object_name, object_type, data_type
FROM named_objects nob
WHERE NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
d.objid=nob.oid)
)

SELECT container_name, container_type, object_name AS suspected_name, object_type, data_type
FROM named_user_defined_objects
WHERE ((object_name ILIKE '%' || data_type || '%' AND data_type NOT IN ('date','text'))
OR (object_name ILIKE data_type ))
ORDER BY object_type, container_name, object_name;

Collections

This query belongs to the following collections:

NameDescription
Lexicon bad smells and linguistic antipatternsQueries made to find the occurrences of lexicon bad smells and linguistic antipatterns
Categories

This query is classified under the following categories:

NameDescription
NamingQueries of this category provide information about the style of naming.

Further reading and related materials:

Reference
Smell "Useless type indication": Abebe, S.L., Haiduc, S., Tonella, P., Marcus, A., 2011. The effect of lexicon bad smells on concept location in source code. In 2011 IEEE 11th International Working Conference on Source Code Analysis and Manipulation (pp. 125-134). IEEE.