Copy SQL

WITH indexes AS (SELECT schemaname, tablename, indexname, indexdef, 'lower' AS opposite_function
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND 
indexdef LIKE '%(upper(%'
AND
schemaname 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, tablename, indexname, indexdef, 'upper' AS opposite_function
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND 
indexdef LIKE '%(lower(%'
AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
),
indexes_with_opposite AS (SELECT schemaname, tablename, indexname, indexdef, opposite_function, translate(substring(indexdef,'[(][^()]*[)]'),'()','') AS indexed_column
FROM indexes),
view_usage AS (SELECT table_schema, table_name, vtu.view_schema, vtu.view_name, (SELECT view_definition FROM information_schema.views AS v WHERE v.table_schema=vtu.view_schema AND v.table_name=vtu.view_name) AS view_def
FROM information_schema.tables AS t LEFT JOIN information_schema.view_table_usage AS vtu  USING (table_schema, 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,
(SELECT definition FROM pg_matviews AS mv WHERE mv.schemaname=dependent_ns.nspname AND mv.matviewname=dependent_view.relname) AS view_def
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))
SELECT io.schemaname, io.tablename, io.indexname, io.indexdef, regexp_replace(vu.view_def,'[\r\n]','
','g') AS view_definition
FROM indexes_with_opposite AS io INNER JOIN view_usage AS vu ON io.schemaname=vu.table_schema AND io.tablename=vu.table_name
WHERE vu.view_def ILIKE '%' || io.opposite_function || '(' || io.indexed_column || '%' OR 
vu.view_def ILIKE '%' || io.opposite_function || '((' || io.indexed_column || '%' OR 
vu.view_def ILIKE '%' || io.opposite_function || '(' || io.tablename || '.' || io.indexed_column || '%' OR 
vu.view_def ILIKE '%' || io.opposite_function || '((' || io.tablename || '.' || io.indexed_column || '%'
ORDER BY schemaname, tablename, indexname;