2009年7月20日

[pgsql-jp: 39933] Re:PL/pgSQLでのプロシジャ実装について

お世話になります。丸山です。

ご指摘いただいた件、説明不足で失礼しました。
以下、長文失礼します。

やりたいこととしては、例えばテーブルtbl_hogeについて
CREATE OR REPLACE FUNCTION sp_demo(
IN a INTEGER,

IN b INTEGER,
IN c INTEGER,
・・・・
というパラメータを指定した時に、
・パラメータaが指定されたときは
 SELECT x, y, z FROM tbl_hoge WHERE a = $1;
・パラメータaとbが指定されたときは
 SELECT x, y, z FROM tbl_hoge WHERE a = $1 AND b = $2;
・パラメータcが指定されたときは
 SELECT x, y, z FROM tbl_hoge WHERE c = $3
のようにパラメータの指定内容で検索条件を変えて、x, y, zの結果を複数行で
得ようとしています。


現在は、次のようにプロシジャを記述しています。

CREATE OR REPLACE FUNCTION sp_demo(
IN a INTEGER,
IN b INTEGER,
IN c INTEGER,
・・・・
OUT x integer,
OUT y character varying,
OUT z character varying
) RETURNS SETOF record AS
$BODY$
DECLARE
sqlstr VARCHAR;
wherestr VARCHAR;
rec RECORD;
BEGIN
sqlstr := 'SELECT x, y, z';
sqlstr := sqlstr || ' FROM tbl_hoge';
IF $1 != 0 THEN
wherestr := wherestr || ' a = ' || $1;
END IF;
・・・・
sqlstr := sqlstr || wherestr;
EXECUTE sqlstr;
RETURN NEXT;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

これについて、次のように呼び出しているのですが、実行結果は返ってくるもの
の1行のみで、しかも内容はすべて空となってしまいます。
SELECT * FROM sp_demo(1, 2, 3);

複数行を取得したいので、OUTパラメータを除いた上で上記EXECUTEの部分を
FOR rec IN EXECUTE sqlstr LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
とするのが本来かとも思ったのですが、今度は実行時に次のようなエラーとなっ
てしまいます。
SELECT * FROM sp_demo(1, 2, 3);
ERROR: a column definition list is required for functions returning
"record"

ネットなどでいろんな情報を見る限りでは、今回のように複数行・複数カラムを
動的SQLで取得しているケースはないようで、そもそもがPL/pgSQLの仕様上ムリ
なのかとも思っています。
ただ、その辺りの判断もつけかねていますので、もし同様の経験をされた方がい
らっしゃれば、アドバイスいただければと思っています。


それでは、よろしくお願いします。

chuuken kenkou さんは書きました:
> chuukenです。
>
>> <制約>
>> ・実行結果は結果集合(複数行)で受け取りたい
>> ・検索項目(複数)をパラメータとして渡し、値が設定されている項目を
>>  キーにして入力内容でデータを検索する
>>  ※ロジックが煩雑になるので、プロシジャ内で動的SQLを組立&実行する
>>   方向で試しています
>
> もう少し、やりたいことを具体的に説明できませんか?
>
>> <現状>
>> ネット上の情報を参考にいろいろ試しているのですが、まともな出力結果1つ得
>> られていません。
>> もしかして、実装自体不可能なのでは?とも思っています....
>> ○RETURNS SETOF RECORDを指定+動的SQLをEXECUTEで実行後RETURN NEXTを指定
>>  →値が何も返らない
>
> どういう関数を定義し、どのように呼び出しているのでしょうか?
> 「値が何も返らない」とは、具体的にどうなるのでしょう?
> 「何かエラーが起こっている」のに、拾えていないといったことはありませんか?


投稿者 xml-rpc : 2009年7月20日 08:45
役に立ちました?:
過去のフィードバック 平均:(0) 総合:(0) 投票回数:(0)
本記事へのTrackback: http://hoop.euqset.org/blog/mt-tb2006.cgi/86864
トラックバック
コメント
コメントする




画像の中に見える文字を入力してください。