PostgreSQL查询视图的依赖关系
一、前言
最近来了个新需求,要求得到数据库里的视图是建立在哪些表或视图上的(以下简称视图的依赖关系)。
对于Oracle和MySql,该需求都较易实现,分别查询USER_DEPENDENCIES和INFORMATION_SCHEMA.VIEWS、再加些简单的解析即可。
但对于PostgreSQL就很困难,因为这种依赖关系分散在多个系统表(pg_depend, pg_rewrite)中,且十分不直观,相关资料也很少。
二、借鉴
经过一番努力,终于度到一篇有价值的帖子:《postgresql/greenplum查询依赖于表上的视图》,相应的sql为:
SELECT c.ev_class::regclass::varchar AS objname, pc.oid::regclass::varchar AS refobjname FROM pg_depend a,pg_depend b,pg_class pc,pg_rewrite c WHERE a.refclassid=1259 -- 1259是pg_depend的oid AND a.classid=2618 -- 2618是pg_rewrite的oid AND b.deptype='i' -- 内部依赖 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 GROUP BY c.ev_class,pc.oid;
已经基本上达到了预期。
三、改进
不过其中的语句的查询结果是把系统视图与自建视图混合在一起了,还需继续改进。该如何对它们进行区分?
记得以前看过一篇资料,好像说过,非系统对象的OID是从16384(0x4000)开始分配的,于是将其加入查询条件:
SELECT c.ev_class::regclass::varchar AS objname, pc.oid::regclass::varchar AS refobjname FROM pg_depend a,pg_depend b,pg_class pc,pg_rewrite c WHERE a.refclassid=1259 -- 1259是pg_depend的oid AND a.classid=2618 -- 2618是pg_rewrite的oid AND b.deptype='i' -- 内部依赖 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 (a.objid>=16384 or a.refobjid>=16384) -- 过滤掉系统对象 GROUP BY c.ev_class,pc.oid;
结果符合预期,问题解决。
声明:内容可以转载,但须标明出处(http://www.cnblogs.com/wggj),请尊重作者(闻歌感旧)的劳动,谢谢!