postgres/pgplsql 戻り値とOUT引数の関係

Question

SQLServerのストアードをPostgres9.4のpgplsqlに移植しています。
SQLServerのストアードの基本的な構造は、ストーアドファンクションの戻り値に処理が成功したか否かを示すvarchra型（DB発行のエラーコードをセットする場合もある）、OUT引数には処理に応じてレコードセットや処理結果をセットしています。

pgplsqlで、以下のサンプルコードを書くと、エラーが発生します。
【エラーの内容】
ERROR: OUT パラメータのない関数では、RETURN にはパラメータを指定できません
行 10: return c_ok;

【サンプルコード】
create or replace function sample(
i_prm in varchar,
o_rs out varchar)
returns varchar as
$$
declare
c_ok varchar := 'ok';
begin
o_rs := '最終的にはここをレコードセットにしたい';
return c_ok;
end;
$$ language plpgsql;

【質問】
pgplsqlでは、OUT引数があるとき、ストアードファンクションの戻り値を書くことはできないのでしょうか？つまり、OUT引数を書くときのストアードは戻り値なしにしなければ文法上エラーになるのか、という質問です。

※ストアードファンクションの戻り値を書かずに、戻り値の部分をOUT引数（上記の場合OUT引数が2個になる）にすればエラーにならない事は確認しています。
ただ、今までの構成が、前段に書きました通り、戻り値とOUT引数を組み合わせて作成しているので、出来るだけ同じ構成にしたいと考えております。

よろしくお願いします。

Comments

[退会済みユーザー]

細かい話ですが、「PL/pgSQL」です。

[bontenmaru]

すいません！以後、気を付けます。ご指摘ありがとうございます。

[Orlofsky]

PL/pgSQL タグを追加しては？

[bontenmaru]

はい、タグの追加をします。このサイトを使い始めたばかりで、わからない事が多々ございますが、ご指導ありがとうございます。

[Orlofsky]

コードは https://teratail.com/help#about-markdown の[コードを入力]を使ってください。

[bontenmaru]

書式指定があるのですね、、、失礼しました。ご指摘ありがとうございます。以後、書式を守ります。

Answer 1

pgplsqlでは、OUT引数があるとき、ストアードファンクションの戻り値を書くことはできないのでしょうか？
つまり、OUT引数を書くときのストアードは戻り値なしにしなければ文法上エラーになるのか

OUTパラメータがあり、RETUNの型も指定することはできます。
ですが、希望されている形にはなりません。

ERROR: OUT パラメータのない関数では、RETURN にはパラメータを指定できません
SQLステート:42804

上記のエラーコードの別な意味は「datatype_mismatch」で型が違うという事です。
付録 A. PostgreSQLエラーコード

どういうことかと言うと、returnsで指定するのは戻り値の型です。
returns varchar と指定した場合は、戻り値は単一の項目でなければなりません。
複数項目の戻り値がある場合のreturnsの型はrecordです。(独自のtypeも指定できます）
なので「型が違う」というエラーなのです。

結論としては、記述は変更せざるをえないという事です。
追記

postgresのストアドの戻りは、単一行であったり複数行であったりはしますが、表だと考えて下さい。
SQL内で使用するものなので、そういった考えです。
functionの異常はSQLSTATEで判断するようにします。
ストアド内でraiseにより例外を発生させ(40.8. エラーとメッセージ)
呼び出し元でSQLSTATEによる判定を行うようにします。

最終的にはレコードセットを返却するという事なので、参考までに

create or replace function sample(
　i_prm in  varchar
)
returns setof record as
$$
declare
begin
  if ??? then
    raise SQLSTATE '99999';
    return;
  end if;
return query select a,b,c from xxx where yyy=i_prm ;
end;
$$ language plpgsql;

※戻り値の型がrecordの場合、利用する側で項目名を指定する必要があるので面倒です。

select * from sample('para') as xxx(a,b,c)

typeを定義してrecordの代わりに指定すると、テーブルをselectするのと変わりがありません

Comments

[bontenmaru]

ご教示ありがとうございました。よく理解できました。
追記頂いたSQLSTATEについてですが、SQLSTATEとSQLERRMはOUT引数に定義しなくても、呼出元（SPRING-JPAになります）で参照することができるのしょうか？
新しく質問を立てたほうが良いのか判断できず、こちらに記しました。
よろしくお願い致します。

[sazi]

SQLSTATEやSQLERRMはpostgresが返却するものなので、それがストアドかどうかは関係ありません。
postgresに意図して認識させるにはraiseしかありません。
例えば、selectでの文法エラー、insertやupdateの実行結果などと同じ扱いです。

[bontenmaru]

SQLSTATEやSQLERRMがストアド云々に関係なくpostgresが返却する点、認識させるためにraiseする点、よくわかりました。
仰せの通り、単純にpostgresのエラー機能とストアドをごっちゃに考えず、シンプルに整理できました。
ただ、一点だけ、レコードセットと処理結果（エラー有無など）を呼出元へ返却したい場合の『定番的な手法』が見いだせないでおります。
OUT引数とreturns setof record as、、、だと、型エラーになる、、、
ご回答頂いた解説文の後半に『ストアド内でraiseにより例外を発生させ(40.8. エラーとメッセージ)呼び出し元でSQLSTATEによる判定を行うようにします』と、あるように、ストアド内でraiseを発生させるだけで、例えばSPRING-JPA側でエラー内容を受け取とることができるのでしょうか？
ストアド内でraiseを発生させても呼出元へ処理結果を渡すには、OUTパラメータやreturnsなどに、エラー内容をセットする必要があるように思うのですが、間違っていますでしょうか？
例えば、type文などで、レコードのFILEDと、処理結果にあたるFILEDを一緒に定義すれば、レコード部分と結果を一つの塊として呼出元に返せるような気がしますが、レコードのFILEDと処理結果のFIELDを一緒に定義するのは、ソースコードのお行儀として少し抵抗を感じます。
java側に関してはこの質問の主旨に反するかとおもいますが、詳細はともあれ、PL/pgSQLのOUTパラメータやreturnsに書かなくても呼出元（java側）で参照できるかどうかについては、今回の質問の範囲としてご教示いただけますと幸いでございます。
よろしくお願い致します。

[sazi]

SQLSTATEのコードの割当をどうするか（postgresの未使用帯域に割当）はありますけど、SQL発行時にcatchすれば良いのではないでしょうか。

[sazi]

例えば、ストアド内で記述しているSQLを実行した際に発生するエラー（例えばキー重複であったり、型の不一致であったり）もSQLSTATEで返却されます。
単に、raiseで任意にエラーを発生させているだけですから、受け取り側ではSQLSTATEを参照する以外に特殊な処理が必要な訳ではありません。

[bontenmaru]

よく理解できました。詳しくご説明下さり、本当にありがとうございました。

Answer 2

41.3.1. 関数引数の宣言
を見てもINやOUTを指定したCREATE FUNCTIONの事例はあるけど、
そこではRETURN文を使ってないのでなんとも言えず、
RETURN分の事例ではINやOUTを使っておらず。
ニントモカントモ