递归查询视图依赖

1. 前言

• 适用版本：【8.1.0（及以上）】

本文通过介绍with recursive递归查询的办法来实现查询视图的层级依赖关系

2. 实现简介

1. 对于postgres生态来说，视图的依赖关系没有现成的查询方法，需要对系统表pg_depend及pg_rewrite编写复杂的组合查询才能得知，而对于Oracle和MySql，该需求都较易实现，分别查询USER_DEPENDENCIES和INFORMATION_SCHEMA.VIEWS即可轻易查出，因此在pg生态来说有必要编写一个直观的视图来查看各个视图与基表或与其他视图的层级依赖关系。本文通过with recursive递归查询的办法来实现视图的层级依赖关系查询。效果如下：

首先建立两个基表t1、t2，表结构随意，再建立一系列的视图进行层级关联，关联关系也随意。本文的建表及视图语句如下：

create table t1 (a int, b int) distribute by hash(a);
create table t2 (a int, b int) distribute by hash(a);
create view v1 as select * from t1;
create view v2 as select * from v1;
create view v3 as select * from v2;
create view v4 as select * from v3;
create view v5 as select * from t2;
create view v6 as select * from v5;
create view v7 as select * from v6;
create view v8 as select * from v2;

实际的局点中，用户的视图依赖关系肯定要比这复杂得多，本文仅讲解原理。对于上述的视图依赖关系，可以通过如下查询得出：

SELECT c.ev_class::regclass::varchar AS objname, pc.oid::regclass::varchar AS refobjname, pc.relkind AS relkind
     FROM pg_depend a,pg_depend b,pg_class pc,pg_rewrite c
    WHERE a.refclassid=1259
      AND a.classid=2618
      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,pc.relkind
  ORDER BY relkind;

其中倒数第二行的16384表示过滤掉系统对象，relkind表示上级依赖对象的类型，r说明依赖于基表，v表示依赖另一个视图。其查询结果如下：

可见这种查询并不直观，只能通过肉眼分析得出递归的依赖关系，对用户并不友好。莫急，先将上述查询保存为视图，例如起名为PUBLIC.gs_view_dependency。接下来我们来学习一下with recursive语法的使用方法，从pg官网可以get到的知识是，WITH语句通常被称为通用表表达式（Common Table Expressions）或者CTEs。WITH语句作为一个辅助语句依附于主语句，WITH语句和主语句都可以是SELECT，INSERT，UPDATE，DELETE中的任何一种语句。WITH语句还可以通过增加RECURSIVE修饰符来引入它自己，从而实现递归：

WITH RECURSIVE语句包含了两个部分

（非递归部分）non-recursive term，即上图中的union all前面的部分

（递归部分）recursive term，即上图中union all后面的部分

执行步骤如下

执行non-recursive term。（如果使用的是union而非union all，则需对结果去重）其结果作为recursive term中对result的引用，同时将这部分结果放入临时的working table中

重复执行如下步骤，直到working table为空：用working table的内容替换递归的自引用，执行recursive term，（如果使用union而非union all，去除重复数据），并用该结果（如果使用union而非union all，则是去重后的结果）替换working table

因此，照葫芦画瓢，我们可以先给这个CTE查询起个名字，例如：

WITH RECURSIVE get_view_dependency
AS
( ... )

然后，我们可以把非递归部分写成：

SELECT a.objname, a.refobjname, a.refobjname path FROM PUBLIC.gs_view_dependency a where a.relkind = 'r'

因为查找到类型为’r’的基表的时候，递归就应该结束了。并且我们需要将refobjname组装成一条path来实现依赖路径的直观表示。因为是递归，所以我们需要不止一次的调用PUBLIC.gs_view_dependency，因此我们给它起个别名a。而递归的条件显而易见，应该是用refobjname来与objname做内关联，因此我们可以写作：

INNER JOIN get_view_dependency cte ON b.refobjname=cte.objname

其中cte就用来指代上一次的递归查询结果，而递归的退出条件就应该是最开始写的类型为’r’的情况。其中b就应该是递归部分每次调用的PUBLIC.gs_view_dependency。而我们除了要查询objname和refobjname外，更重要的是要组成一个path，而path每个节点最好通过一个’->'来直观表示，因此递归部分我们就可以写成：

SELECT b.objname, b.refobjname, cte.path ||' -> '|| b.refobjname path FROM PUBLIC.gs_view_dependency b
INNER JOIN get_view_dependency cte ON b.refobjname=cte.objname

因此，整个CTE递归查询便可以写成：

WITH RECURSIVE get_view_dependency
AS
(
SELECT a.objname, a.refobjname, a.refobjname path FROM PUBLIC.gs_view_dependency a where a.relkind = 'r'
UNION ALL
SELECT b.objname, b.refobjname, cte.path ||' -> '|| b.refobjname path FROM PUBLIC.gs_view_dependency b
INNER JOIN get_view_dependency cte ON b.refobjname=cte.objname
)
SELECT * FROM get_view_dependency;

为了方便以后的使用，我们可以给它起个名字保存起来，例如CREATE VIEW PUBLIC.gs_view_table_dependency AS …。效果就是文章开头所示那样，便大功告成。

3. 总结

在数据库中，查询视图是一种非常有用的工具，它可以帮助我们更好地组织和管理数据。然而，查询视图的层级依赖关系可能会让人感到困惑。本文将介绍一种通过 with recursive 递归查询的办法来实现查询视图的层级依赖关系的方法，希望对你有所帮助。