Query goal: | Find names of user-defined types and domains that contain too generic (noise) words. |
Notes about the query: | The query does not consider the routines that are a part of an extension. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Low (Many false-positive results) |
Query license: | MIT License |
Data source: | system catalog only |
SQL query: | Click on query to copy it
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; |
Collection name | Collection description |
---|---|
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 . |
Category name | Category description |
---|---|
Domains | Queries of this category provide information about reusable specifications of column properties. |
Naming | Queries of this category provide information about the style of naming. |
User-defined types | Queries of this category provide information about user-defined types in the database. |