WITH views AS (SELECT
views.table_schema,
views.table_name,
'VIEW' AS type,
views.view_definition
FROM
information_schema.views
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT schemaname, matviewname, 'MATERIALIZED VIEW' AS type, definition
FROM pg_catalog.pg_matviews),
views_on_multiple_tables AS (SELECT view_schema, view_name
FROM (SELECT t.table_schema, t.table_name, vtu.view_schema, vtu.view_name, 'VIEW' AS type
FROM
information_schema.tables AS t LEFT JOIN information_schema.view_table_usage AS vtu
ON
t.table_schema = vtu.table_schema AND
t.table_name = vtu.table_name
WHERE t.table_type = 'BASE TABLE' AND t.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT DISTINCT
source_ns.nspname as source_schema,
source_table.relname as source_table,
dependent_ns.nspname as dependent_schema,
dependent_view.relname as dependent_view,
'MATERIALIZED VIEW' 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='m'
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)) AS foo
WHERE view_name IS NOT NULL
GROUP BY view_schema, view_name
HAVING Count(*)>1),
views_processed AS (SELECT
table_schema,
table_name,
type,
view_definition,
regexp_replace(view_definition,'[\r\n]','<br>','g') AS view_definition_changed
FROM views
WHERE (table_schema, table_name) IN (SELECT view_schema, view_name
FROM views_on_multiple_tables))
SELECT table_schema, table_name, type, view_definition_changed AS view_definition,
CASE
WHEN (view_definition~*'(?<!(LEFT|RIGHT|FULL|CROSS))[[:space:]]+JOIN[[:space:]]')='t' THEN 'USES NEWER SYNTAX'
WHEN (view_definition!~*'[[:space:]]JOIN[[:space:]]')='t' THEN 'PERHAPS USES OLDER SYNTAX' END AS explanation
FROM views_processed
WHERE EXISTS (SELECT *
FROM views_processed
WHERE view_definition!~*'[[:space:]]JOIN[[:space:]]')
AND EXISTS (SELECT *
FROM views_processed
WHERE view_definition~*'(?<!(LEFT|RIGHT|FULL|CROSS))[[:space:]]+JOIN[[:space:]]')
ORDER BY table_schema, table_name;