pgAdminでOUTパラメータ付きのストアドのデバッグ実行する

はじめに

前回「PostgreSQLでストアドプロシージャ開発をするその2 ～デバッガ～」でpgAdminのデバッガ機能について説明したが、実際に使ってみるとOUTパラメータを持つストアドプロシージャのデバッグで問題があった。

今回は、OUTパラメータ付きのストアドプロシージャをpgAdminでデバッグ実行する際の問題と回避策について書いてみる。

問題

pgAdminのDebugger機能でOUTパラメータを持つストアドプロシージャを実行しようとすると、引数の数が一致しないエラーが発生する場合がある。

問題が発生するケース

以下のようなOUTパラメータを持つプロシージャをデバッグしようとした場合。

test_parameters.sql

-- IN, INOUT, OUTパラメータを使用したテストプロシージャ
CREATE OR REPLACE PROCEDURE test_parameters(
    IN p_input_value INTEGER,           -- 入力パラメータ
    INOUT p_inout_value INTEGER,        -- 入出力パラメータ
    OUT p_output_value INTEGER          -- 出力パラメータ
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 入力値をログ出力
    RAISE NOTICE 'Input value: %', p_input_value;
    RAISE NOTICE 'INOUT value (before): %', p_inout_value;
    
    -- 出力パラメータに計算結果を設定
    p_output_value := p_input_value * 2;
    
    -- INOUTパラメータを変更
    p_inout_value := p_inout_value + p_input_value;
    
    RAISE NOTICE 'INOUT value (after): %', p_inout_value;
    RAISE NOTICE 'Output value: %', p_output_value;
END;
$$;

エラーの内容

ストアドのデバッグ実行

pgAdminのDebugger機能で上記プロシージャをデバッグ実行しようとすると、以下のエラーが発生する。

ERROR: procedure public.test_parameters(integer, integer) does not exist
LINE 1: CALL public.test_parameters (
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883
Character: 10

原因

pgAdminのデバッガの動作について

pgAdminのデバッガでは、プロシージャをデバッグ実行する際にOUTパラメータについては入力パラメータではないため省略されていると思われる。

回避策: INOUTパラメータへの変更

方針

デバッグ時に一時的にOUTパラメータをINOUTパラメータに変更する。

修正コード

test_parameters_debug.sql

CREATE OR REPLACE PROCEDURE test_parameters_debug(
    IN p_input_value INTEGER,           -- 入力パラメータ
    INOUT p_inout_value INTEGER,        -- 入出力パラメータ
    INOUT p_output_value INTEGER DEFAULT NULL        -- 出力パラメータ
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 入力値をログ出力
    RAISE NOTICE 'Input value: %', p_input_value;
    RAISE NOTICE 'INOUT value (before): %', p_inout_value;
    
    -- 出力パラメータに計算結果を設定
    p_output_value := p_input_value * 2;
    
    -- INOUTパラメータを変更
    p_inout_value := p_inout_value + p_input_value;
    
    RAISE NOTICE 'INOUT value (after): %', p_inout_value;
    RAISE NOTICE 'Output value: %', p_output_value;
END;
$$;

変更点

変更箇所	変更内容	理由
パラメータ宣言	`OUT` → `INOUT`	pgAdminデバッガでの引数認識を変更する
デフォルト値	`DEFAULT NULL`を追加	入力値が不要な場合でも引数として渡せるようにする
プロシージャ名	`_debug`サフィックス追加	本番用と区別する

デバッグを実施

1. デバッグ用プロシージャの作成

先ほど修正したプロシージャを作成する。

2. pgAdminでのデバッグ実行

プロシージャの選択 test_parameters_debugを右クリック
Debuggerの起動「Debugging」→「Debug」を選択
引数の設定:
- p_input_value: 10
- p_inout_value: 5
- p_output_value: NULL（またはそのまま）
ブレークポイントの設定必要な行にブレークポイントを配置
実行開始デバッグ実行を開始

3. デバッグ結果の確認

-- 実行結果例
NOTICE:  Input value: 10
NOTICE:  INOUT value (before): 5
NOTICE:  INOUT value (after): 15
NOTICE:  Output value: 20
CALL

注意点

項目	注意内容
パラメータの型一致	`INOUT`に変更する際も元の型を維持する
デフォルト値	`NULL`以外のデフォルト値は慎重に設定する
関数名の管理	デバッグ用と本番用の関数名を明確に区別する
権限の管理	デバッグ用プロシージャにも適切な権限を設定する

参考

PostgreSQL公式ドキュメント - PL/pgSQL Functions
https://www.postgresql.org/docs/current/plpgsql.html
pgAdmin4 Documentation - Debugger
https://www.pgadmin.org/docs/pgadmin4/latest/debugger.html

おわりに

pgAdminのデバッガでOUTパラメータ付きのストアドプロシージャをデバッグする際は、一時的にOUTパラメータをINOUTパラメータに変更することで問題を回避できた。今回のテストでは、IN、INOUT、OUTパラメータを組み合わせたプロシージャで実際に問題が発生することを確認し、回避策の有効性を検証してみた。この方法は完璧ではないが、実際のデバッグ作業では非常に有効だった。

さらに調査をしたらスマートな方法が見つかるかもしれないが見つからなかった…。
ローカル環境でデバッグするときはこの方法で一旦回避しようと思う。