PostgreSQL ストアドプロシージャサンプル

OUTパラメータ付サンプル

バージョン14以降であればOUTパラメータが有効になるのですが、バージョン14未満の場合プロシージャからの戻り値を取得する場合INOUTとしないといけません。

CREATE OR REPLACE PROCEDURE sooni.proceeds_kudamono(
	p_name character varying,
	p_quantity integer,
	INOUT p_earnings integer,
	INOUT p_result_message character varying,
	p_isdebug boolean default false)
LANGUAGE 'plpgsql'
AS $BODY$

DECLARE
PRGNAME CONSTANT character varying := 'proceeds_kudamono()';

BEGIN
	if P_isdebug then
		set client_min_messages = 'debug1';
	else
		set client_min_messages = 'NOTICE';
	end if;
	raise info  '[ % ] p_name:%  p_quantity:%  p_earnings:% P_isdebug:%  p_result_message:%'
	, PRGNAME,p_name,p_quantity,p_earnings,p_result_message,P_isdebug;
	p_earnings := 10;
	p_result_message := '正常終了';
	raise debug  '[ % ] これはdebugです ',PRGNAME;
END;
$BODY$;

ストアドプロシージャの実行例（psql版）

OracleのOUTパラメータ付ストアドプロシージャを呼び出す時はあらかじめOUTパラメータを変数定義しておく必要があるのですが、PostgreSQL（psql、pgAdmin4を利用時に限る）はラクチンです。なぜならOUTパラメータに相当するところにはリテラルでダミー値を記載しておけば、selectの結果セットのように返却してくれます。ただし他の（非Native）SQL発行ツール等ではこの方法は使えない場合があります。その場合は以下の無名ブロックを利用する必要があります。

myposdb=# call sooni.proceeds_kudamono('みかん',1,0,null,true);
LOG:  文: call sooni.proceeds_kudamono('みかん',1,0,null,true);
INFO:  [ proceeds_kudamono() ] p_name:みかん  p_quantity:1  p_earnings:0 P_isdebug:<NULL>  p_result_message:t
DEBUG:  [ proceeds_kudamono() ] これはdebugです
LOG:  期間: 3.064 ミリ秒
 p_earnings | p_result_message

------------+------------------
         10 | 正常終了
(1 行)


myposdb=#

SQL実行ツール等からの実行には無名ブロックでCALLします

OUTパラメータをもつストアドプロシージャの実行には無名ブロックを使う必要があります。