The list of all the queries

Derived table on top of another derived table

Query goal: Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables.
Notes about the query: The query uses a fragment of a query from: https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/. In the returned subquery of view/materialized view the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser.
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: Instead of creating a derived table (view, materialized view) on top of another derived table create it on top of base tables.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH view_on_view AS (SELECT DISTINCT nt.nspname AS view_schema, d.refobjid::regclass::text AS view_name, 
CASE WHEN c.relkind='v' THEN 'VIEW'
ELSE 'MATERIALIZED VIEW' END AS view_type,
nv.nspname AS dependent_view_schema, v.oid::regclass::text AS dependent_view_name, v.oid AS dependent_view_oid,
CASE WHEN v.relkind='v' THEN 'VIEW'
ELSE 'MATERIALIZED VIEW' END AS dependent_view_type
FROM pg_attribute AS a 
   JOIN pg_depend AS d
      ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid
   JOIN pg_rewrite AS r 
      ON r.oid = d.objid
   JOIN pg_class AS v 
      ON v.oid = r.ev_class
      JOIN pg_namespace AS nv
	ON nv.oid=v.relnamespace
   JOIN pg_class AS c
	ON c.oid=d.refobjid
   JOIN pg_namespace AS nt
	ON nt.oid=c.relnamespace
WHERE v.relkind IN  ('v','m')
  AND c.relkind IN ('v','m')
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass 
  AND d.deptype = 'n'
  AND a.attisdropped='f'
    AND nt.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND nv.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT view_schema, view_name, view_type, dependent_view_schema, dependent_view_name, dependent_view_type, regexp_replace(pg_get_viewdef(dependent_view_oid),'[\r\n]','<br>','g') AS dependent_view_def
FROM view_on_view
ORDER BY view_schema, view_name, dependent_view_schema, dependent_view_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
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.

Reference materials for further reading

Reference
https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/
Rule 9 in: Delplanque, J., Etien, A., Auverlot, O., Mens, T., Anquetil, N., Ducasse, S.: CodeCritics applied to database schema: Challenges and first results. In: 2017 IEEE 24th International Conference on Software Analysis, Evolution and Reengineering (SANER), pp. 432-436. IEEE, (2017).

The list of all the queries