厳密に言えば、このプロセスは反復（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;
このクエリは 2 つのテーブルを出力します。1 つは最初の 5 つの自然数を含み、もう 1 つのテーブルには階乗を求めるために実行された計算が含まれます。
最後の行だけを出力することもできますが、ここでは反復と計算がどのように行われるかを見ることができます。
フィボナッチ数列を出力する 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;
出力は次のようになります：
