PostgreSQLとOracleによるデータベース相互移行マニュアル第5回

connectby()関数の使い方

connectby()関数は複合型を返します。LEVEL疑似列に相当する列も生成されますので、connectby()関数をFROM句で使用することで同等の結果を得ることができます。connectby()関数の呼び出し仕様は次のとおりです。

connectby( TEXT relname,

	            TEXT keyid_fld,

	            TEXT parent_keyid_fld,

	            TEXT start_with,

	            INT max_depth

	            [, TEXT branch_delim] )

relnameは、階層問い合わせの対象となるテーブルの名前
keyidは、階層条件に従ってソートする対象となる列（キーID列）の名前
parent_keyid_fldは、親となる行のキーIDを保持する列（親キーID列）の名前
start_withには、スタート行の親キーID列の値を指定
max_depthには、検索する階層の深さを指定（0は子となる行がなくなるまで）

なお、connectby()関数には、筆者が試したところ次のような制限がありました。

スタート行は1つでなければいけない
スタート行の親キーID値にNULLは指定できない

これらの制限があるために、図8の担当者マスタ表に、ダミーとなるスタート行を加えなければなりません。つまり、次のような変更を加える必要があります。

 INSERT INTO 担当者マスタ

	   VALUES ( 0, '', '', 0, '1900-01-01', 0 );

	

	UPDATE 担当者マスタSET MGR_ID = 0

	   WHERE MGR_ID IS NULL;

こうしたうえで、リスト5のようなSQL文を発行します。

すると、図10のように同様の結果が得られます。branch列には、branch_delim引数に与えた文字を区切り文字にして、スタート行からのパスが返されます。

SQL> SELECT * FROM 担当者マスタ;

branch	\|	担当者（階層表示）
---------	+	------------
0~1	\|	鈴木
0~1~8	\|	宇賀神
0~2	\|	小野
0~2~6	\|	伊藤
0~2~7	\|	佐瀬
0~3	\|	斎藤
0~3~4	\|	藤本
0~3~4~9	\|	岡田
0~3~5	\|	小林
(9 rows)

図10：PostgreSQLでの階層問い合わせの実行結果

connectby()関数は、デフォルトでは使用できません。前述した contrib/tablefuncフォルダに、tablefunc.sqlというファイルがあります。これをconnectby()関数を使用するデータベースに対して、次のように実行すると、connectby()関数が登録されて使用できるようになります。

% psql tsukaeru tablefunc.sql

詳しくは、同フォルダにあるREADME.tablefuncファイルを参照してください。