Number of underlying tables of derived tables

Copy SQL

WITH derived_tables AS (SELECT DISTINCT
source_ns.nspname as table_schema,
source_table.relname as table_name,
dependent_ns.nspname as view_schema,
dependent_view.relname as view_name,
CASE WHEN dependent_view.relkind='v' THEN 'VIEW'
WHEN dependent_view.relkind='m' THEN 'MATERIALIZED VIEW' END AS type
FROM pg_depend 
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE dependent_view.relkind IN ('m','v')
AND dependent_ns.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 
(dependent_ns.nspname ||'.'|| dependent_view.relname<>source_ns.nspname||'.'||source_table.relname)) 
SELECT view_schema, view_name, type, Count(DISTINCT table_schema || '.' || table_name) AS nr_of_tables
FROM derived_tables
GROUP BY view_schema, view_name, type
ORDER BY Count(*) DESC, type, view_schema, view_name;