Goal Find names of user-defined types and domains that contain too generic (noise) words.
Notes The query does not consider the routines that are a part of an extension.
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 system catalog only
SQL Query
WITH named_objects AS (SELECT 
pt.oid,
n.nspname AS container_name, 
'SCHEMA' AS container_type,
pt.typname AS object_name,
CASE WHEN pt.typtype='d' THEN 'DOMAIN'
WHEN pt.typtype='e' THEN 'ENUMERATION TYPE'
WHEN pt.typtype='r' THEN 'RANGE TYPE' END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND pt.typtype NOT IN ('b','c')
),
named_user_defined_objects AS (
SELECT container_name, container_type, object_name, object_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 AS suspected_object_type
FROM named_user_defined_objects
WHERE object_name~*'(andmed|info|domeen|tüüp|tyyp|data|domain|type)'
AND object_name!~*'database'
ORDER BY object_type, container_name, object_name;

Collections

This query belongs to the following collections:

NameDescription
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:

NameDescription
DomainsQueries of this category provide information about reusable specifications of column properties.
NamingQueries of this category provide information about the style of naming.
User-defined typesQueries of this category provide information about user-defined types in the database.