Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed.
Notes
In the returned subquery of view/materialized view the query replaces each newline character with the line break (br) tag for the better readability in case the query result is displayed in a web browser.
Type
General (Overview of some aspect of the database.)
SELECT
table_schema,
table_name,
type,
regexp_replace(view_definition,'[\r\n]',' ','g') AS view_definition
FROM (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, regexp_replace(definition,'[\r\n]',' ','g') AS definition
FROM pg_catalog.pg_matviews) AS foo
WHERE view_definition ~*'(union|except|intersect)[[:space:]]+all'
ORDER BY table_schema, table_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
Description
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.