POSTGRES の再帰処理の基本 ( WITH RECURSIVE )

準備

「データ的に木構造」なテーブルを作っておく
(RDBなのでもちろん、直接木構造のデータを作ることはできない)

DROP TABLE tree;

CREATE TABLE tree (
  id integer,
  parent integer
);

INSERT INTO tree
  (id, parent)
VALUES
  (1, null),
  (2, 1),
  (3, 1),
  (4, 3),
  (5, null),
  (6, 5),
  (7, 6),
  (8, 7)
;

データの状態

SELECT * FROM tree;
 id | parent
----+--------
  1 |
  2 |      1
  3 |      1
  4 |      3
  5 |
  6 |      5
  7 |      6
  8 |      7
(8 rows)

• 1は2と3を子に持つ
• 3は4を子に持つ
• 5から8まではまた別系統で、4世代分の親子関係を持つ

クエリ例1

id 1のレコードと、id 1 を親に持つレコードを再帰的に得る。

WITH RECURSIVE r AS (
       SELECT * FROM tree WHERE id = 1
     UNION ALL
       SELECT tree.* FROM tree, r WHERE tree.parent = r.id
     )
SELECT * FROM r ORDER BY id;

結果

 id | parent
----+--------
  1 |
  2 |      1
  3 |      1
  4 |      3

id 1 のレコードを基準に、子の子までレコードをたどることが出来た。
1の直接の子ではない4も結果に含まれているのが分かる。

クエリ例2

このデータの場合だと id 5 はもっと子の構造が深いが、それでも再帰的に結果を得ることが出来る。

WITH RECURSIVE r AS (
       SELECT * FROM tree WHERE id = 5
     UNION ALL
       SELECT tree.* FROM tree, r WHERE tree.parent = r.id
     )

SELECT * FROM r ORDER BY id;

 id | parent
----+--------
  5 |
  6 |      5
  7 |      6
  8 |      7
(4 rows)

参考

チャットメンバー募集

何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。

Twitter