[pgsql-jp: 28158] 階層問い合わせ関数 (PL/pgSQLで行を返すサンプル 7.3にて）

[Mashiki]

　Mashikiです。

oracleで言うところのstart with、connect by句を用いた階層問合わせの
ようなものをplpgsqlで実現してみるサンプルを作ってみました。

# 私は過去この方法を聞くために当MLへの入会しましたので万感の思いです。

/* テーブル定義 (pidは親のidです) */
create table treetest (
   id int4,
   name text,
   pid int4,
   primary key(id)
);

/* サンプルデータ */
insert into treetest values (1, 'aaa', null);
insert into treetest values (2, 'abb', 1);
insert into treetest values (3, 'acc', 1);
insert into treetest values (4, 'bdd', 2);
insert into treetest values (5, 'cee', 3);
insert into treetest values (6, 'cff', 3);

/* tree 階層問い合わせ関数サンプル */
create or replace function tree(int4) returns setof record as '
declare
   rec1 record;
   rec0 record;
   cnt  int4;
begin
   cnt := 0;
   select $1, 0, cnt into rec0;
   return next rec0;
   for rec1 in
         select *
         from tree0($1, 1) as t(id int4, level int4)
   loop
      cnt := cnt+1;
      select rec1.id, rec1.level, cnt into rec0;
      return next rec0;
   end loop;
   return;
end;
' language 'plpgsql';

/* tree 再帰部分 */
create or replace function tree0(int4, int4) returns setof record as '
declare
   rec1 record;
   rec2 record;
begin
   for rec1 in
      select  id, $2
      from treetest
      where pid = $1
      order by id
   loop
      return next rec1;
      for rec2 in
         select *
         from tree0(rec1.id, $2+1) as t(id int4, level int4)
      loop
         return next rec2;
      end loop;
   end loop;
   return;
end;
' language 'plpgsql';


/* 呼び出しサンプル */
select id, pid, name, tree.level, lpad('',tree.level)||name
 from treeTest
 join tree(1) as tree(id int4, level int4, ord int4) using(id)
 order by tree.ord
;

結果
 id | pid | name | level | ?column?
----+-----+------+-------+----------
  1 |     | aaa  |     0 | aaa
  2 |   1 | abb  |     1 |  abb
  4 |   2 | bdd  |     2 |   bdd
  3 |   1 | acc  |     1 |  acc
  5 |   3 | cee  |     2 |   cee
  6 |   3 | cff  |     2 |   cff
(6 rows)

と、一応期待通りの動作を得られました。

まだ、この方法だと
・テーブル毎に関数を定義しなくてはならない
・idと親idの列名も固定されている
・idの型も決めうちになっている
・親が同じ場合、兄弟に相当する行の並び順がid順固定になる
　(これはoracleでもコントロールできたか不明)
・再帰部をスマートに書きたい(2番目のselectを書きたくない)
など、クリアしたい部分もあるのでもう少し掘ってみます。