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;