再帰クエリについて（PostgreSQL)

ちょっと再帰クエリについて調べてみたのでメモ。
自身のテーブルを再帰的に参照していくというもの。

========================================================================
WITH RECURSIVE tree_rec(id, parent, name, path, names) as(
    SELECT id, parent, name, array[id] as path,array[name] as names
    FROM tree
    WHERE parent IS NULL
    UNION ALL
    SELECT tree.ID, tree.parent, tree.name
               , tree_rec.path || tree.ID as path
               , tree_rec.names || tree.name as names
    FROM tree
    INNER JOIN tree_rec
    ON tree_rec.id = tree.parent
)

SELECT id, parent, name, path, names
          , array_upper(path, 1) AS level
          , array_to_string(names,'_')
FROM tree_rec
ORDER BY id;

========================================================================

結果、こんな感じ。
左3列がtreeテーブル本来のデータ。

 

 再帰SQLについて

 配列関数について