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

[takayuki-maruyama ＠ nova-system.com]

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

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

やりたいこととしては、例えばテーブル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;

これについて、次のように呼び出しているのですが、実行結果は返ってくるもの
の１行のみで、しかも内容はすべて空となってしまいます。
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を組立＆実行する
>> 　　方向で試しています
> 
> もう少し、やりたいことを具体的に説明できませんか？
> 
>> ＜現状＞
>> ネット上の情報を参考にいろいろ試しているのですが、まともな出力結果１つ得
>> られていません。
>> もしかして、実装自体不可能なのでは？とも思っています....
>> ○RETURNS SETOF RECORDを指定＋動的SQLをEXECUTEで実行後RETURN NEXTを指定
>> 　→値が何も返らない
> 
> どういう関数を定義し、どのように呼び出しているのでしょうか？
> 「値が何も返らない」とは、具体的にどうなるのでしょう？
> 「何かエラーが起こっている」のに、拾えていないといったことはありませんか？