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'
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
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)
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