WITH user_defined_derived_tables AS (SELECT
table_schema,
table_name,
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
view_definition,'FROM','<br><b>FROM</b>','g'),
'ORDER BY','<br><b>ORDER BY</b>','g'), 
'WHERE','<br><b>WHERE</b>','g'), 
'GROUP BY','<br><b>GROUP BY</b>','g'), 
'SELECT','<b>SELECT</b>','g'), 
'LEFT JOIN','<font color=red>LEFT JOIN</font>','g'), 
'RIGHT JOIN','<font color=red>RIGHT JOIN</font>','g'),  
'HAVING','<br><b>HAVING</b>','g'),
'UNION','<br><b>UNION</b>','g'),
'EXCEPT','<br><b>EXCEPT</b>','g'),
'INTERSECT','<br><b>INTERSECT</b>','g') AS def,
table_type
FROM (SELECT 
views.table_schema, 
views.table_name, 
views.view_definition,
'VIEW' AS table_type
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, definition, 'MATERIALIZED VIEW' AS table_type
FROM pg_catalog.pg_matviews
ORDER BY table_name) AS foo)
SELECT table_schema, table_name, def, table_type
FROM user_defined_derived_tables
WHERE def~*'rank[(][)][[:space:]]+over' AND def~*'dense_rank[(][)][[:space:]]+over'
ORDER BY table_schema, table_name;