严格来说，这个过程是迭代（iteration）而不是递归（recursion），但 RECURSIVE 是 SQL 标准委员会选用的术语。
Postgres 递归查询的一般结构包含：
- 非递归 select 语句
- Union 或 union all
- 递归 select 语句
WITH RECURSIVE name_cte AS (
SELECT statement /* non-recursive statement */
UNION [ALL]
SELECT statement /*recursive statement referencing the above select statement */
)
SELECT * FROM name_cte;
Postgres 递归查询如何运作
- 评估非递归语句并建立一个临时数据表
- 评估递归项目并将它们加入到临时数据表
- 重复步骤 2 直到工作数据表为空
Union 和 union all 的差别在于 union all 允许重复，而 union 则不允许任何重复。
范例
前 10 个自然数
WITH RECURSIVE tens AS (
SELECT 1 as n
UNION ALL
SELECT n+1 FROM tens
)
SELECT n FROM tens limit 10;
这是 Postgres 递归查询的基本范例，它会印出前 10 个自然数。
寻找自然数阶乘的 Postgres 递归查询：
WITH RECURSIVE fact (n, factorial)
AS (
SELECT 1 as n, 5 as factorial
union all
SELECT n+1, factorial*n FROM fact where n < 5
)
SELECT * FROM fact;
这个查询会输出两个数据表，一个包含前五个自然数，另一个数据表包含为了寻找阶乘所执行的计算。
我们可以只印出最后一行，但在在这里我们可以看到迭代和计算是如何发生的。
印出费波那契数列（Fibonacci series）的 Postgres 递归查询：
WITH RECURSIVE fibb
AS (
SELECT 1::bigint as n, 0::bigint as a, 1::bigint as b
UNION ALL
SELECT n+1, b as a, (a+b) as b FROM fibb
)
SELECT b FROM fibb limit 10;
这会印出直到第 10 个数的费波那契数列。
组织阶层
透过 Postgres 递归查询的帮助，我们可以找出组织阶层：
建立数据表：
INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, 'Abhi', NULL),
(2, 'Bhargav', 1),
(3, 'Chay', 1),
(4, 'Dravid', 1),
(5, 'Erin', 1),
(6, 'Ford', 2),
(7, 'Gagan', 2),
(8, 'Harry', 3),
(9, 'Isaac', 3),
(10, 'Jack', 4),
(11, 'Kiran', 5);
Abhi 是老板，他会在第一层。Bhargav、Chay、Dravid、Erin 在下一层，其余的人会在最后一层。
查询：
WITH RECURSIVE subordinates AS (
SELECT employee_id, manager_id, full_name, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, level+1
FROM employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;
输出将会是：
