The list of all the queries

Derived table names have prefix or suffix

Query goal: 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.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Rename the derived table so that it does not have prefix and suffix.
Data source: system catalog only
SQL query: Click on query to copy it

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 where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
NamingQueries of this category provide information about the style of naming.

The list of all the queries