Find the names of views and materialized views that have prefix or suffix. Follow the same naming style as in case of base tables (derived tables are also tables). Thus, if base tables do not have prefixes or suffixes, then derived tables shouldn't have these as well.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the derived table so that it does not have prefix and suffix.
Data Source
system catalog only
SQL Query
WITH named_objects AS (
SELECT
c.oid,
n.nspname AS container_name,
'SCHEMA' AS container_type,
c.relname AS object_name,
CASE WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
END AS object_type
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind IN ('v','m')
),
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
FROM named_user_defined_objects
WHERE object_name ILIKE ('v\_%')
OR object_name ILIKE ('view\_%')
OR object_name ILIKE ('vaade\_%')
OR object_name ILIKE ('\_%v')
OR object_name ILIKE ('\_%view')
OR object_name ILIKE ('\_%vaade')
ORDER BY object_type, container_name, object_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.