PostgreSQLで階層データ取得

PostgreSQL8.4からの新機能であるWITH RECURSIVEが便利であったのでメモ
WITH RECURSIVEは階層構造のデータを再起的に取得するのに利用でき、例えば以下のようなテーブルとデータがあったとして

CREATE TABLE user_mst
(
  user_id bigserial NOT NULL PRIMARY KEY,
  family_name character(32),
  first_name character(32),
  boss_id bigint
);


INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 1, '田中', '一郎', -1);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 2, '田中', '二郎', 1);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 3, '田中', '三郎', 1);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 4, '田中', '四郎', 1);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 5, '田中', '五郎', 2);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 6, '山田', '六郎', -1);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 7, '山田', '七郎', 6);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 8, '山田', '八郎', 7);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 9, '山田', '九郎', 8);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 10, '山田', '十郎', 9);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 11, '山田', '十一郎', 10);
INSERT INTO user_mst( user_id, family_name, first_name, boss_id) VALUES( 12, '佐藤', '十二郎', -1);

これに対して以下のSQLを実行すると、子供のデータを再起的に取得することができる。

WITH RECURSIVE user_hierarchy AS (
  SELECT user_id FROM user_mst WHERE user_id = 1
  UNION ALL
       SELECT user_mst.user_id FROM user_mst, user_hierarchy WHERE user_mst.boss_id = user_hierarchy.user_id
)
SELECT * FROM user_mst
WHERE user_id IN (
    SELECT user_id FROM user_hierarchy ORDER BY user_hierarchy.user_id
);

ここでのuser_idを変更して確認してみると確かにそうなっていることが確認できる。

それでは逆に親のデータを再起的に取得したい場合は、UNIONしているSELECTのWHERE条件で使っているuser_idとboss_idを入れ替えて以下のようにするだけである。

WITH RECURSIVE user_hierarchy AS (
  SELECT user_id, boss_id FROM user_mst WHERE user_id = 10
  UNION ALL
       SELECT user_mst.user_id, user_mst.boss_id FROM user_mst, user_hierarchy WHERE user_mst.user_id = user_hierarchy.boss_id
)
SELECT * FROM user_mst
WHERE user_id IN (
    SELECT user_id FROM user_hierarchy ORDER BY user_hierarchy.user_id
);

なかなか便利そう