PostgreSQL/MogDB/openGauss怎样获取表上依赖于该表的视图

原创阎书利 2022-11-25

714

一、实现语句

postgres=# select relnamespace,relname,relkind from pg_class where oid in(          
           select c.ev_class
     from pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
    where a.refclassid=1259      
      and b.deptype='i'
      and a.classid=2618
      and a.objid=b.objid
      and a.classid=b.classid
      and a.refclassid=b.refclassid
      and a.refobjid<>b.refobjid
      and pc.oid=a.refobjid     
      and c.oid=b.objid
      and relnamespace in (select oid from pg_namespace where nspname='public') and pc.relname='t1');
 relnamespace | relname | relkind 
--------------+---------+---------
(0 rows)

二、创建一个依赖于t1表的视图a1测试

postgres=# create view  a1 as select * from t1;
CREATE VIEW
postgres=# select relnamespace,relname,relkind from pg_class where oid in(          
           select c.ev_class
     from pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
    where a.refclassid=1259      
      and b.deptype='i'
      and a.classid=2618
      and a.objid=b.objid
      and a.classid=b.classid
      and a.refclassid=b.refclassid
      and a.refobjid<>b.refobjid
      and pc.oid=a.refobjid     
      and c.oid=b.objid
      and relnamespace in (select oid from pg_namespace where nspname='public') and pc.relname='t1');
 relnamespace | relname | relkind 
--------------+---------+---------
         2200 | a1      | v
(1 row)

查询语句里的， a.refclassid=1259，这个1259是pg_class的oid,a.classid=2618的2618是pg_rewrite的oid。

结果里relnamespace=2200是public这个schema的oid，可以查询pg_namespace得到

postgres=# select oid,nspname from pg_namespace where oid=2200;
 oid  | nspname 
------+---------
 2200 | public
(1 row)

因此依赖于public.t1的视图是public.a1。

三、MogDB/openGauss也可以用同样的方法

MogDB=# select relnamespace,relname,relkind from pg_class where oid in(          
MogDB(#            select c.ev_class
MogDB(#      from pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
MogDB(#     where a.refclassid=1259      
MogDB(#       and b.deptype='i'
MogDB(#       and a.classid=2618
MogDB(#       and a.objid=b.objid
MogDB(#       and a.classid=b.classid
MogDB(#       and a.refclassid=b.refclassid
MogDB(#       and a.refobjid<>b.refobjid
MogDB(#       and pc.oid=a.refobjid     
MogDB(#       and c.oid=b.objid
MogDB(#       and relnamespace in (select oid from pg_namespace where nspname='public') and pc.relname='t1');
 relnamespace | relname | relkind 
--------------+---------+---------
         2200 | a1      | v
(1 row)