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)
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:
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
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.