postgresql——WITH RECURSIVE

前言

RECURSIVE，递归查询。他将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特性。通过使用RECURSIVE，一个WITH查询可以引用它自己的输出。

准备工作

CREATE TABLE "resource" (
  "id" int4 NOT NULL constraint resource_pk primary key,
  "name" text,
  "parent_id" int4
);

INSERT INTO "public"."resource" VALUES (1, '系统管理', 0);
INSERT INTO "public"."resource" VALUES (2, '基础数据管理', 0);
INSERT INTO "public"."resource" VALUES (3, '组织管理', 1);
INSERT INTO "public"."resource" VALUES (5, '题目管理', 2);
INSERT INTO "public"."resource" VALUES (4, '公司管理', 3);

这是一个无限等级的菜单栏列表。

实验

WITH RECURSIVE res AS (
    SELECT id, name, parent_id
    FROM resource 
    WHERE id = 4
    UNION
    SELECT rr.id,
           rr.name || ' > ' || r.name,
           rr.parent_id
    FROM res r INNER JOIN resource rr ON rr.id = r.parent_id
)
select * from res;

结果：

递归查询过程
1、计算非递归项。对UNION（但不对UNION ALL），抛弃重复行。把所有剩余的行包括在递归查询的结果中，并且也把它们放在一个临时的工作表中。
2、只要工作表不为空，重复下列步骤：

• 计算递归项，用当前工作表的内容替换递归自引用。对UNION（不是UNION ALL），抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。
• 用中间表的内容替换工作表的内容，然后清空中间表。

拆解分析
可以分成两部分：
① non-recursive term（非递归部分），即上例中的union前面部分
② recursive term（递归部分），即上例中union后面部分

1、执行非递归部分

SELECT id, name, parent_id
FROM resource 
WHERE id = 4

结果集和working table为
4 | 公司管理 |  3

2、执行递归部分,如果是UNION,要用当前查询的结果和上一个working table的结果进行去重，然后放到到临时表中。然后把working table的数据替换成临时表里面的数据。

SELECT rr.id,
       rr.name || ' > ' || r.name,
       rr.parent_id
FROM res r INNER JOIN resource rr ON rr.id = r.parent_id

结果集和working table为
3 | 组织管理 > 公司管理 |  1

3、同2，直到数据表中没有数据。

SELECT rr.id,
       rr.name || ' > ' || r.name,
       rr.parent_id
FROM res r INNER JOIN resource rr ON rr.id = r.parent_id

结果集和working table为
  1 | 系统管理 > 组织管理 > 公司管理 | 0

4、结束递归，将前几个步骤的结果集合并，即得到最终的WITH RECURSIVE的结果集

WITH RECURSIVE 使用限制

1、 如果在recursive term中使用LEFT JOIN，自引用必须在“左”边
2、 如果在recursive term中使用RIGHT JOIN，自引用必须在“右”边
3、 recursive term中不允许使用FULL JOIN
4、 recursive term中不允许使用GROUP BY和HAVING
5、 不允许在recursive term的WHERE语句的子查询中使用CTE的名字
6、 不支持在recursive term中对CTE作aggregation
7、 recursive term中不允许使用ORDER BY
8、 LIMIT / OFFSET不允许在recursive term中使用
9、 FOR UPDATE不可在recursive term中使用
10、 recursive term中SELECT后面不允许出现引用CTE名字的子查询
11、 同时使用多个CTE表达式时，不允许多表达式之间互相访问（支持单向访问）
12、 在recursive term中不允许使用FOR UPDATE

总结

recursive是pgsql中提供的一种递归的机制，比如当我们查询一个完整的树形结构使用这个就很完美，但是我们应该避免发生递归的死循环，也就是数据的环状。