[pgsql-jp: 39388] 再帰クエリ機能パッチ

Yoshiyuki Asaba y-asaba @ sraoss.co.jp
2008年 5月 15日 (木) 16:04:45 JST


浅羽です。

SQL99 から入った再帰クエリを PostgreSQL でも使えるように、パッチを作り
ました。8.4 に取り込んでもらうように動く予定ですが、まずは使っていただ
いたり、コメントを頂けると幸いです。

なお、まだいくつか制限事項がありますので、パッチダウンロードを含めて以
下のURL をご覧ください。

http://www.sraoss.jp/~y-asaba/pgsql/recursive-query.html

再帰クエリは再帰的なデータ構造を SQL でも簡単に扱えるようにするもので
す。以下のサイトで詳しく紹介されています。

http://www.atmarkit.co.jp/fnetwork/tokusyuu/01sql99/sql99_1b.html

例えば、以下のような組織構造があり、部門 A より下を抽出することを考え
ます。

  ROOT ---> A ---> B ---> C ---> F
    |              |
    |              +----> D
    |
    +-----> E ---> G

テーブルとデータを以下のようにします。

CREATE TABLE department (
  id INT PRIMARY KEY,  -- 部署 ID
  parent_department INT REFERENCES department, -- 上位部署の ID
  name TEXT -- 部署名
);

INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
INSERT INTO department VALUES (3, 2, 'C');
INSERT INTO department VALUES (4, 2, 'D');
INSERT INTO department VALUES (5, 0, 'E');
INSERT INTO department VALUES (6, 4, 'F');
INSERT INTO department VALUES (7, 5, 'G');

これを以下のようにして、部門 A 以下を取得できます。

WITH RECURSIVE subdepartment AS
(
  -- 非再帰項
  SELECT * FROM department WHERE name = 'A'

  UNION ALL

  -- 再帰項
  SELECT d.* FROM department AS d, subdepartment 
    WHERE d.parent_department = subdepartment.id
)
SELECT * FROM subdepartment ORDER BY name;

 id | parent_department | name
----+-------------------+------
  1 |                 0 | A
  2 |                 1 | B
  3 |                 2 | C
  4 |                 2 | D
  6 |                 4 | F
(5 rows)

--
Yoshiyuki Asaba
y-asaba @ sraoss.co.jp



pgsql-jp メーリングリストの案内