The number of derived table columns with an array, a user-defined, XML, JSON, or JSONB type

Copy SQL

WITH cols AS (SELECT table_schema, table_name, table_type, column_name, 
CASE WHEN data_type='USER-DEFINED' THEN udt_name || '(' || data_type || ')' ELSE data_type END AS data_type
FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE table_type='VIEW'
AND 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 n.nspname AS table_schema, c.relname AS table_name, 'MATERIALIZED VIEW' AS table_type, at.attname AS column_name, 
CASE WHEN t.typtype='d' THEN 
CASE WHEN bt.typcategory='A' THEN translate(bt.typname,'_','') || '(ARRAY)' ELSE bt.typname END
ELSE 
CASE WHEN t.typcategory='A' THEN translate(t.typname,'_','') || '(ARRAY)' 
WHEN t.typcategory='C' THEN t.typname || '(USER-DEFINED)'
ELSE t.typname END
END AS data_type
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_attribute AS at ON c.oid=at.attrelid 
INNER JOIN pg_type AS t ON at.atttypid=t.oid
LEFT JOIN pg_type AS bt ON t.typbasetype=bt.oid
WHERE (n.nspname='public' OR rolname<>'postgres')
AND c.relkind='m'
AND at.attisdropped = false
AND at.attnum>=1)
SELECT table_type, data_type, Count(*) AS number_of_columns
FROM cols
WHERE data_type~*'(json|xml|array|user-defined)'
GROUP BY CUBE (table_type, data_type)
ORDER BY table_type, data_type, Count(*) DESC;