嚴格來說，這個過程是迭代（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;
輸出將會是：
