Goal Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables.
Notes 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.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
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
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]','
','g') AS dependent_view_def FROM view_on_view ORDER BY view_schema, view_name, dependent_view_schema, dependent_view_name;
Collections

This query belongs to the following collections:

NameDescription
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

This query is classified under the following categories:

NameDescription
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.

Further reading and related materials:

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).