postgresql - in postgres, get name & schema of relations on which materialized view depends -
i'm looking print schema & name of relations materialized views in schema depend on:
select c.relname, d.classid, d.objid, pg_describe_object( d.classid, d.objid, d.objsubid) pg_class c join pg_namespace n on c.relnamespace = n.oid left join pg_depend d on c.oid = d.objid n.nspname = 'direct' , d.deptype = 'n'
this gives like:
relname | relname | classid | objid | pg_describe_object ------------------------+---------+---------+-------+--------------------------------------------------- cases | | 2618 | 33736 | rule _return on materialized view case_categories benefit_investigations | | 2618 | 33928 | rule _return on materialized view bi_intervals
the description returned gives hint, doesn't contain schema of relation. how actual dependency schema , name? [nb i'm using postgres 9.6]
here go:
select distinct view_cs.nspname, view_c.relname, tab_cs.nspname, tab_c.relname pg_depend view_d join pg_class view_c on view_c.oid = view_d.refobjid , view_c.relkind = 'm' join pg_type view_ct on view_ct.oid = view_c.reltype join pg_namespace view_cs on view_cs.oid = view_ct.typnamespace join pg_depend tab_d on tab_d.objid = view_d.objid join pg_class tab_c on tab_c.oid = tab_d.refobjid , tab_c.relkind = 'r' join pg_type tab_ct on tab_ct.oid = tab_c.reltype join pg_namespace tab_cs on tab_cs.oid = tab_ct.typnamespace view_d.deptype = 'n'
the trick have join pg_depend
twice - once view-rule relation , once rule-table relation.
Comments
Post a Comment