find views

find.mat.views.sql

SELECT DISTINCT v.oid::regclass AS matrialized_view,
        ns.nspname AS schema,                           -- mview schema,
        d.refobjid::regclass AS ref_table               -- name of table in relation
        FROM pg_depend AS d                             -- objects that depend on a table
            JOIN pg_rewrite AS r                        -- rules depending on a table
                ON r.oid = d.objid
            JOIN pg_class AS v                          -- views for the rules
                ON v.oid = r.ev_class
            JOIN pg_namespace AS ns                     -- schema information
                ON ns.oid = v.relnamespace
        WHERE v.relkind = 'm'                           -- filter materialized views only
            -- dependency must be a rule depending on a relation
            AND d.classid = 'pg_rewrite'::regclass 
            AND d.refclassid = 'pg_class'::regclass     -- referenced objects in pg_class (tables and views)
            AND d.deptype = 'n'                         -- normal dependency
            -- qualify object
            AND ns.nspname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit') -- system schemas
            AND NOT (v.oid = d.refobjid)                -- not self-referencing dependency