CODEWITHSUNDEEP
sqlpostgresqlmaterialized-views
Hakan SONMEZ
Hakan SONMEZ

Reputation: 2226

How to get materialized views that refer to a table in postgresql

In postgresql it is possible to get all views that refer to a table by simple sql thanks to information_schema.views table. But I need an equivalent sql to get materialized views that refer to the table.

For views following works

SELECT * 
FROM information_schema.view_table_usages AS u
JOIN information_schema.views AS v on u.view_schema = v.table_schema AND u.view_name = v.table_name
WHERE u.table_name = 'my_table'

Upvotes: 3

Views: 3797

Answers (3)

husarz96
husarz96

Reputation: 15

Check out this:

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

Upvotes: 1

AdamKG
AdamKG

Reputation: 14056

information_schema is a SQL-standard thing, and the SQL standard has no notion of materialized views, which is why they don't show up. You can use \d+ in psql to get a view definition, including of information_schema.view_table_usages, which is itself a view. Then all you need to do is change the filtered relkind from v (for views) to m (for materialized views).

psql (12.4 (Debian 12.4-1+build2))
Type "help" for help.

testdb=# create table t as select c from generate_series(1, 3) c;
SELECT 3
testdb=# create view vt as select * from t;
CREATE VIEW
testdb=# create materialized view mvt as select * from t;
SELECT 3
testdb=# \d+ information_schema.view_table_usage 
                                 View "information_schema.view_table_usage"
    Column     |               Type                | Collation | Nullable | Default | Storage | Description 
---------------+-----------------------------------+-----------+----------+---------+---------+-------------
 view_catalog  | information_schema.sql_identifier |           |          |         | plain   | 
 view_schema   | information_schema.sql_identifier |           |          |         | plain   | 
 view_name     | information_schema.sql_identifier |           |          |         | plain   | 
 table_catalog | information_schema.sql_identifier |           |          |         | plain   | 
 table_schema  | information_schema.sql_identifier |           |          |         | plain   | 
 table_name    | information_schema.sql_identifier |           |          |         | plain   | 
View definition:
 SELECT DISTINCT current_database()::information_schema.sql_identifier AS view_catalog,
    nv.nspname::information_schema.sql_identifier AS view_schema,
    v.relname::information_schema.sql_identifier AS view_name,
    current_database()::information_schema.sql_identifier AS table_catalog,
    nt.nspname::information_schema.sql_identifier AS table_schema,
    t.relname::information_schema.sql_identifier AS table_name
   FROM pg_namespace nv,
    pg_class v,
    pg_depend dv,
    pg_depend dt,
    pg_class t,
    pg_namespace nt
  WHERE nv.oid = v.relnamespace AND v.relkind = 'v'::"char" AND v.oid = dv.refobjid AND dv.refclassid = 'pg_class'::regclass::oid AND dv.classid = 'pg_rewrite'::regclass::oid AND dv.deptype = 'i'::"char" AND dv.objid = dt.objid AND dv.refobjid <> dt.refobjid AND dt.classid = 'pg_rewrite'::regclass::oid AND dt.refclassid = 'pg_class'::regclass::oid AND dt.refobjid = t.oid AND t.relnamespace = nt.oid AND (t.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])) AND pg_has_role(t.relowner, 'USAGE'::text);



testdb=# SELECT DISTINCT current_database()::information_schema.sql_identifier AS view_catalog,
    nv.nspname::information_schema.sql_identifier AS view_schema,
    v.relname::information_schema.sql_identifier AS view_name,
    current_database()::information_schema.sql_identifier AS table_catalog,
    nt.nspname::information_schema.sql_identifier AS table_schema,
    t.relname::information_schema.sql_identifier AS table_name
   FROM pg_namespace nv,
    pg_class v,
    pg_depend dv,
    pg_depend dt,
    pg_class t,
    pg_namespace nt
  WHERE nv.oid = v.relnamespace AND v.relkind = 'm'::"char" AND v.oid = dv.refobjid AND dv.refclassid = 'pg_class'::regclass::oid AND dv.classid = 'pg_rewrite'::regclass::oid AND dv.deptype = 'i'::"char" AND dv.objid = dt.objid AND dv.refobjid <> dt.refobjid AND dt.classid = 'pg_rewrite'::regclass::oid AND dt.refclassid = 'pg_class'::regclass::oid AND dt.refobjid = t.oid AND t.relnamespace = nt.oid AND (t.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])) AND pg_has_role(t.relowner, 'USAGE'::text)  ;
 view_catalog | view_schema | view_name | table_catalog | table_schema | table_name 
--------------+-------------+-----------+---------------+--------------+------------
 testdb       | public      | mvt       | testdb        | public       | t
(1 row)

Upvotes: 2

Adrian Klaver
Adrian Klaver

Reputation: 19620

Closest I can come up with:

SELECT * FROM pg_matviews WHERE definition ilike '%my_table%' ;

From here:

https://www.postgresql.org/docs/current/view-pg-matviews.html

Upvotes: 5

Related Questions