Listing tables USED BY materialized view in PostgreSQL?

Question

I've googled for it, both couldn't find an answer.

How can I list tables used by materialized view (must not use the information_schema views) in PostgreSQL?

Thanks in advance for your help.

Answer 1

Refer this post: How to get materialized views that refer to a table in postgresql

Writing the query here as well:

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
      v.relname='<YOUR MATERIALIZED VIEW HERE>' 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", 'm'::"char", 'p'::"char"])) and
    pg_has_role(t.relowner, 'USAGE'::text);