WITH base_tables AS (SELECT nspname AS table_schema, 
relname AS table_name,
relnatts AS number_of_columns,
(SELECT string_agg(attname, ', ' ORDER BY attnum) FROM pg_attribute AS a WHERE attnum>=1 AND attisdropped='f' AND a.attrelid=c.oid) AS table_columns
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (nspname='public' OR rolname<>'postgres')
AND relkind='r'),
views AS (SELECT nspname AS view_schema, 
relname AS view_name,
CASE WHEN relkind='v' THEN 'VIEW'
ELSE 'MATERIALIZED VIEW' END AS type,
(SELECT string_agg(attname, ', ' ORDER BY attnum) FROM pg_attribute AS a WHERE attnum>=1 AND attisdropped='f' AND a.attrelid=c.oid) AS view_columns,
pg_get_viewdef(c.oid) AS view_def,
relnatts AS number_of_columns
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (nspname='public' OR rolname<>'postgres')
AND relkind IN ('v', 'm')),
dependencies AS (
SELECT nt.nspname AS table_schema, d.refobjid::regclass::text AS table_name, nv.nspname AS view_schema, v.oid::regclass::text AS view_name, Count(DISTINCT a.attname) AS number_of_dependent_columns
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 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)
GROUP BY nt.nspname, d.refobjid::regclass, nv.nspname, v.oid::regclass),
views_depend_on_one_table AS (SELECT view_schema, view_name
FROM dependencies
GROUP BY view_schema, view_name
HAVING Count(*)=1)
SELECT table_schema, table_name, regexp_replace(table_columns,',',',
','g') AS columns, view_schema, view_name, type, regexp_replace(view_def,'[\r\n]','
','g') AS view_definition
FROM base_tables INNER JOIN dependencies USING (table_schema, table_name)
INNER JOIN views_depend_on_one_table USING (view_schema, view_name)
INNER JOIN views USING (view_schema, view_name)
WHERE base_tables.number_of_columns=dependencies.number_of_dependent_columns 
AND base_tables.number_of_columns=views.number_of_columns
AND view_def!~*'[[:space:]](WHERE|HAVING)[[:space:]]'
AND table_columns=view_columns
ORDER BY view_schema, view_name, table_schema, table_name;