スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

PL/pgSQLで戻り値でテーブルを使う時の注意点

追記しました。PL/pgSQLで戻り値にテーブルの代わりに型を使う

PostgreSQLのPL/pgSQLでは戻り値として、テーブルの構造をした値を返せます。
例えば以下になります。

CREATE OR REPLACE FUNCTION aaa(
) RETURNS TABLE(
value1 text
,value2 text
) AS $$
DECLARE
BEGIN
RETURN QUERY SELECT '1111'::text, '2222'::text;
RETURN QUERY SELECT '3333'::text, '4444'::text;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM aaa();
value1, value2
1111, 2222
3333, 4444

複雑な検索をPHPなどのプログラムに記述せずPL/pgSQLで作るときには、この仕組みが欠かせません。
しかし、TABLE()内の戻り値のカラムを定義するところに、変な癖があるので注意が必要です。
今回はPostgreSQL8.4.4で確認しています。
まず、説明のためのテーブルを作成します。

CREATE TABLE t_user (
user_id bigint
,user_name text
);
INSERT INTO t_user (user_id, user_name) VALUES (1, 'abc'), (2, 'def');

ここで、user_nameだけを返すPL/pgSQLを作成してみます。癖を知らずに書くとおそらく次のように書くと思います。

CREATE OR REPLACE FUNCTION bad_sample1(
) RETURNS TABLE(
user_name text
) AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
user_name
FROM
t_user
;
END;
$$ LANGUAGE plpgsql;

動作されてみるとすぐわかりますが、正しい答えを返しません。二行のNULLが返ってきました。
どうも、TABLE()内で定義した名前をBEGIN?END内で使うと正しく動作しないようです。
そこで、名前がぶつからない様に修正します。

CREATE OR REPLACE FUNCTION good_sample1(
) RETURNS TABLE(
r_user_name text
) AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
user_name
FROM
t_user
;
END;
$$ LANGUAGE plpgsql;

これは正しい値を返します。基本は他に出てこないプレフィックスを付けることで回避できます。
ちなみに私の場合、引数にはp_, TABLE()内には、r_, DECLARE?BEGIN間にはw_をつけています。
さて、すべてがこれでうまくいくわけではありません。
次の関数をみてください。

CREATE OR REPLACE FUNCTION bad_sample2(
) RETURNS TABLE(
r_user_name text
) AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
r_user_name || 'postfix'
FROM
good_sample1()
;
END;
$$ LANGUAGE plpgsql;

これは、先ほど定義した関数を内部で呼んでいます。TABLE()内の名前とBEGIN?END間の名前がかぶっているため動きません。
修正するなら、こんな感じでしょうか?

CREATE OR REPLACE FUNCTION good_sample2(
) RETURNS TABLE(
r_user_name2 text
) AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
r_user_name || 'postfix'
FROM
good_sample1()
;
END;
$$ LANGUAGE plpgsql;

何か、きもいっすね(笑)
というか、PHPとかで利用する場合、実害もあります。
PDOやZend_DBなんかでアクセスすると、row['user_name']のように連想配列でアクセスするのですが
呼ぶ関数がgood_sample1かgood_sample2かでrow['r_user_name'], row['r_user_name2']のようにそれぞれ変えなければなりません。
これはちょっと、めんどくさいです。PHPとかでコールする時にSQLで工夫して

$sql1 = "SELECT r_user_name as user_name FROM good_sample1()";
$sql2 = "SELECT r_user_name2 as user_name FROM good_sample2()";

のように、名前を変換してやれば同じようにアクセスできますが、やはりめんどうです。
そこで、私が使っている解決策ですが、戻り値のテーブルを作ることで名前の重複が避けられます。

CREATE TABLE r_user (
user_name text
);

CREATE OR REPLACE FUNCTION nice_sample1(
) RETURNS SETOF r_user AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
user_name
FROM
t_user
;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION nice_sample2(
) RETURNS SETOF r_user AS $$
DECLARE
BEGIN
RETURN QUERY SELECT
user_name || 'postfix'
FROM
nice_sample1()
;
END;
$$ LANGUAGE plpgsql;

これで、心置きなくBEGIN?END間で好きな名前が使えます。
PHP側も単純に$sql = "select * from nice_sample1()";で$row['user_name']でアクセス可能です。

このサンプルだけ見てしまうと、検索するのにいちいちテーブル作るのか!
と思ってしまうかもしれませんが、カラムが多かったり今回の例のように多段で関数をコールする場合などは、参照用のテーブルを使う方が便利です。
私も多段で無い場合は普通、TABLE()を使っています。
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


この記事にトラックバックする(FC2ブログユーザー)

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。