再帰的SQL

あまプロではシステム開発の案件をお請けしております。
先日、データベースでちょっと複雑なSQLを書いたんであメログ。

例えばこんなテーブルが有りまして

SELECT * FROM directory;
 id | parentid | name 
----+----------+-------
  0 |          | root
  1 |        0 | home
  2 |        0 | usr
  3 |        0 | var
  4 |        0 | etc
  5 |        1 | hoge
  6 |        2 | bin
  7 |        3 | log
  8 |        2 | local
  9 |        8 | bin
 10 |        1 | geho
 11 |        3 | lib
 12 |        0 | sbin
(13 rows)

IDと親IDと名前の列が有るとします。
まぁ良く有る親子関係の表です。
で、そのテーブルを基に階層構造のフルパスを生成せなあきませんでした。

 id | name  | parentid |    fullpath   
----+-------+----------+----------------
  0 | root  |          | /
  1 | home  |        0 | /home
  2 | usr   |        0 | /usr
  3 | var   |        0 | /var
  4 | etc   |        0 | /etc
  5 | hoge  |        1 | /home/hoge
  6 | bin   |        2 | /usr/bin
  7 | log   |        3 | /var/log
  8 | local |        2 | /usr/local
  9 | bin   |        8 | /usr/local/bin
 10 | geho  |        1 | /home/geho
 11 | lib   |        3 | /var/lib
 12 | sbin  |        0 | /sbin
(13 rows)

ちょっと列がずれてますけど…こんな感じの結果を取得したいんです。
勿論、１ＳＱＬで実現したい訳です。
案件で使っていたDBがたまたまPostgreSQLだったので、再帰的SQLが使えました。

WITH RECURSIVE full_path_dir( ID, name, parentID, fullpath) AS (
  SELECT
    ID,
    name,
    parentID,
    '/' AS fullpath
  FROM
    directory
  WHERE parentID is NULL
  UNION ALL
  SELECT
    dir.ID,
    dir.name,
    dir.parentID,
    CASE WHEN fpd.fullpath = '/' THEN '' ELSE fpd.fullpath END ||'/'|| dir.name
  FROM
    full_path_dir fpd,
    directory dir
  WHERE fpd.ID = dir.parentID
)
SELECT * FROM full_path_dir ORDER BY ID;

仕組みがなかなか理解出来ませんでしたが、お掛けでプログラム側でループ処理を実装する手間が省けました〜。