Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs.
Notes
The query uses regexp_replace to put the letters in the names between (b) tags for the better readability in case the query result is displayed in a web browser.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the database objects and remove the letters in question.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
SELECT schema, regexp_replace(suspected_name, '([õäöüÕÄÖÜ])', E'\\1', 'g') AS suspected_name, container, type
FROM (
SELECT schema_name AS schema, schema_name AS suspected_name, catalog_name AS container, 'SCHEMA' AS type
FROM information_schema.schemata
WHERE schema_name~'[õäöüÕÄÖÜ]'
UNION SELECT table_schema AS schema, table_name AS suspected_name, table_schema AS container, table_type AS type
FROM information_schema.tables
WHERE table_type='VIEW' AND table_name~'[õäöüÕÄÖÜ]'
UNION SELECT table_schema, column_name, table_name, 'VIEW COLUMN' AS type
FROM information_schema.columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='VIEW') AND column_name~'[õäöüÕÄÖÜ]'
UNION SELECT specific_schema, routine_name, specific_schema, 'ROUTINE' AS type
FROM information_schema.routines
WHERE routine_name~'[õäöüÕÄÖÜ]'
UNION SELECT specific_schema, parameter_name, specific_name, 'PARAMETER' AS type
FROM information_schema.parameters
WHERE parameter_name~'[õäöüÕÄÖÜ]'
UNION SELECT nspname, relname, nspname, 'MATERIALIZED VIEW' AS type
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
WHERE relkind = 'm'
AND relname~'[õäöüÕÄÖÜ]'
UNION SELECT nspname, attname, relname, 'COLUMN' AS type
FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
LEFT JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
WHERE relkind = 'm' AND attnum>=1 AND attname~'[õäöüÕÄÖÜ]' ) AS foo
ORDER BY type, schema, suspected_name;
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
Description
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
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