PostgreSQL8.4から利用可能な「with recursive」使った再帰クエリの利用方法。

会社の下に営業部とシステム開発部があり、それぞれの下に課があるテーブル「department」を用意する。

parent_idには、上位部署のidが格納されている。

idparent_idname
1会社
21営業部
31システム開発部
42営業1課
52営業2課
63システム開発1課

このテーブルから、「営業部」とその配下の部署を検索するSQLは以下の通り。

結果。

idparent_idname
21営業部
42営業1課
52営業2課

SQLの解説。(with句とオプションのrecursive修飾子について)

まずは、with句。

with句は、「検索結果に名前を付けて、メモリ上に仮想のテーブルを作る」ための文。

構文は以下の通り。

上記の記述では、まずselect文1が実行されて、その検索結果がメモリ上で仮想テーブルになる。

そして、select文2の中で、そのテーブルを検索対象として使用できる。

例えば、「会社の部署の中で、”営業”に関係している”課”」を検索したい場合。

1.部署名が「課」で終わってる行を検索

2.1.の結果から、さらに部署名が「営業」で始まっている行を検索

結果。

idparent_idname
42営業1課
52営業2課

このwith句を前半と後半に分けて考える。

まず、SQLの前半部分。

これは、「かっこ内のselect文を実行し、その結果をtempテーブルとする」という意味になる。

このテーブルは実際にはDB上には作られない、SQLが終わると消えてしまう仮想テーブルである。

かっこ内のselect文は「課」で終わってる部門の検索なので、結果は下記のようになる。

idparent_idname
42営業1課
52営業2課
63システム開発1課

次に、with句の後半部分。

このselect文では、with句の前半で作られた仮想テーブル「temp」の中で、「営業」で始まっている行を検索している。

結果は以下の通り。

idparent_idname
42営業1課
52営業2課

つまり、もう一度前半と後半を一緒に見てみると、

1.まず、部署名が「課」で終わってる行を検索

2.1.の結果から、さらに部署名が「営業」で始まっている行を検索

再帰検索をするには、with句にオプションのrecursive修飾子を付けて、with recursiveとする。

recursive修飾子を付加すると、with句で一時テーブルを作成するselect文で検索する対象テーブルに、その自分自身(つまりその一時テーブル自身)を指定することができる。

例えば、手続き型プログラミングでの再帰呼び出しは、関数が自分自身を呼ぶ。

それと同じように、一時テーブルを作成する時に、その作成途中の自分自身を検索対象にしてしまう。

「with recursive」のSQLを前半、中盤、後半に分けてみてみる。

1.前半

この部分は、再帰の先頭部分を決めるSQLになる。

1行目は、tempという一時テーブルを作成するSQLの最初の部分。

そのtempの構成内容が記述されているのが2行目以降。

このselect文がまず実行される。

これによって、nameが「営業部」の以下の結果が返る。

idparent_idname
21営業部

現時点では、これが一時テーブル「temp」の中身になる。(1行だけのテーブル)

2.中盤

ここが実質上、再帰をしている部分。

union allはとりあえず置いておいて、その次のselect文の中身を見てみる。

元のテーブル「dept」全体の「parent_id」カラムの内容と、一時テーブル「temp」の「id」の内容が一致しているものを検索する。

つまり、一時テーブルで検索された部署(営業部)を「自分の親だ」と思っている部署が検索される。

今現在の一時テーブルの内容が、

idparent_idname
21営業部

で、idは「2」であり、deptからは「parent_id」が「2」の、以下の行が検索される。

idparent_idname
42営業1課
52営業2課

というわけで、2行が検索された。

で、ここで忘れてはいけないのは、この段階ではwithで作成される一時テーブル「temp」が「まだ作られてる最中だ」ということ。

つまり、この検索結果が一時テーブル「temp」の内容に書き換わる。

さっきまで1行しかなかった「temp」が、今回の2行の検索結果に書き換わったということ。(ただし、最初の1行のtempは消えない。どこかに保存されている)

そして、さらにこの2行の内容を元にして、先ほどの以下のselect文がまた実行される。

つまり今度は、「親の部署のidが、4か5の部署はないか?」という検索が実行される。

この様に次々に親が書き換わって、その子供が検索されて、その結果またが親になって、その子供が検索されて、それがまた親になって、、、、、というのが繰り返される。

今回の例では、これ以上の子供はないので、ここで子供の検索は終わる。

そして最後に、さっき置いておいたunion all。

これが指定されているので、全ての検索結果(この場合は1行の検索結果と2行の検索結果)が結合されて、以下のようになる。

これで一時テーブル「temp」の作成が完了した。

idparent_idname
21営業部
42営業1課
52営業2課

以上がwith recursiveを使った再帰検索の中身。

もう一度、with recursiveを使ったselect文全体を見てみる。

最後に再帰検索は、where句の条件を間違えると無限ループに陥りやすい為、実行には最新の注意が必要となる。