【Postgres】メッセージや変数の中身をconsole.log()みたいに表示するraise文

概要

raise文を使えば、以下のようにJavaScriptのconsole.log()に近いデバッグ機能を利用したり、エラーハンドリングにも活用できます。

• 文字列のメッセージやFunction内の変数・引数を表示する
• raise exceptionで任意のタイミングにて例外を発生させ、処理をexception句にジャンプさせる

項目	内容
取り扱う内容	• PostgreSQLのraise文によるデバッグ・ログ出力機能 • エラーハンドリングの実装パターン • ログレベルの使い分けはざっくり
想定読者	• PostgreSQLでの開発経験がある人 • PostgreSQLでFunctionの開発を行う人
ゴール	• raise文をデバッグ用途および例外処理に活用できるようになる

簡単な使用例

以下は、DB Function中の任意の箇所でraise noticeしてメッセージを表示するサンプルです。

CREATE OR REPLACE FUNCTION f_simple_raise_notice(
    name TEXT,
    age INTEGER
) RETURNS VOID AS $$
BEGIN
    -- 処理の開始を表示
    RAISE NOTICE 'function execution started';

    -- 受け取った引数を表示
    RAISE NOTICE 'input parameters: name = %, age = %', name, age;

    -- 処理の終了を表示
    RAISE NOTICE 'function execution completed';
END;
$$ LANGUAGE plpgsql;

-- 関数を呼び出すSELECT文
SELECT f_simple_raise_notice('Alice', 30);

-- 関数を削除
DROP FUNCTION f_simple_raise_notice(TEXT, INTEGER);

実行結果：
raise noticeで記載した内容が出力されています。2行目では、受け取った引数の値を正しく表示できています。

function execution started
input parameters: name = Alice, age = 30
function execution completed

DBeaver上での見え方：

応用編：実践的な関数における利用例

表示したいメッセージのレベル感や用途が実際の処理に近くなるように、もう少し複雑な関数でraise文を使用します。raise exceptionにより任意のタイミングで例外を起こす例も含んでいます。

実現する機能：ユーザーログイン管理システム

• 登録済のユーザーマスタ情報（m_userテーブル）に対して、引数で受け取ったidに対応するユーザーの最終ログイン日時（last_login_at列）を、現在日時に更新する
• 無効化されたユーザーや、存在しないユーザーを受け取った場合の対応も行う（いずれも一般的にはエラーとして例外を発生させるべきですが、raise文のパターンを増やすために、存在しないユーザーへの処理はメッセージでお知らせするだけにします）
• 処理の各段階にraise文を仕込んで、ログメッセージを出力する

処理イメージ

事前準備：必要なテーブルとデータの登録

必要なテーブルとデータを予め投入する処理です。

-- ユーザーマスタの削除と再作成
DROP TABLE IF EXISTS m_user;
CREATE TABLE IF NOT EXISTS m_user (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    status TEXT NOT NULL,
    last_login_at TIMESTAMPTZ
);

-- テストデータの挿入
INSERT INTO m_user (name, status, last_login_at) VALUES
('Alice', 'active', '2023-01-01 10:00:00'),
('Bob', 'inactive', '2023-01-01 09:00:00'),
('Cathy', 'active', '2023-01-02 11:00:00');

-- エラーログテーブルの削除と再作成
DROP TABLE IF EXISTS t_error;
CREATE TABLE IF NOT EXISTS t_error (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL,
    message TEXT NOT NULL
);

-- テーブル内容の確認
SELECT * FROM m_user;
SELECT * FROM t_error;

作成したテーブルに登録済のデータ：

m_user

id	name	status	last_login_at
1	Alice	active	2023-01-01 10:00:00.000 +0900
2	Bob	inactive	2023-01-01 09:00:00.000 +0900
3	Cathy	active	2023-01-02 11:00:00.000 +0900

t_error
レコード無し

id	error_dt	error_message

Function定義

raise文をいくつか仕込んだFunction定義です。raise 〇〇の部分はログレベルに対応しています（処理中断を引き起こすexception以外は、メッセージを表示する点において挙動は同じです）

-- 数値でユーザーIDを受け取る関数
CREATE OR REPLACE FUNCTION f_update_login_at(p_user_id INTEGER)
RETURNS TEXT AS $$
DECLARE
    v_user RECORD;
    v_result TEXT;
BEGIN
    RAISE INFO '[%] 処理を開始します: ユーザーid %', clock_timestamp(), p_user_id;
    
    -- ユーザー存在確認
    SELECT * INTO v_user FROM m_user WHERE id = p_user_id;
    IF NOT FOUND THEN
        -- 存在しないユーザーであればメッセージだけ返して終わる（要件次第でraise exceptionしてもよい）
        v_result := format('対象ユーザーが見つかりません: id %s', p_user_id);
        RAISE NOTICE '[%] %', clock_timestamp(), v_result;
        RETURN v_result;
    END IF;

    IF v_user.status = 'inactive' THEN
        -- 例外を自分で起こしてexception句に飛ぶ
        RAISE EXCEPTION '非アクティブユーザーの処理はできません: id %', p_user_id;
    END IF;

    -- 更新処理実行
    UPDATE m_user SET last_login_at = clock_timestamp() WHERE id = p_user_id;
    
    v_result := format('処理が正常に完了しました: ユーザーid %s', p_user_id);
    RAISE INFO '[%] %', clock_timestamp(), v_result;
    RETURN v_result;
EXCEPTION
    WHEN OTHERS THEN
        -- エラーログを記録
        INSERT INTO t_error (created_at, message)
        VALUES (clock_timestamp(), SQLERRM);
        -- エラー情報を返す
        v_result := format('処理中にエラーが発生しました: %s', SQLERRM);
        RAISE WARNING '[%] %', clock_timestamp(), v_result;
        RETURN v_result;
END;
$$ LANGUAGE plpgsql;

動作確認

呼び出しパターンと出力メッセージ想定

Function内で`v_result_をRETURNしているので、raise文によるメッセージ表示に加えて、関数の戻り値が結果として表示されます。

パターン	呼び出し文	呼び出し文の結果（その他、メッセージ表示やデータ登録等）
1. 有効なユーザーID渡して実行する（id = 1のAlice）	SELECT f_update_login_at(1);	'処理が正常に完了しました: ユーザーid 1' （INFO メッセージにて処理開始と完了のメッセージが出力され、m_userのAliceレコードに更新がかかります）
2. 非アクティブユーザーを渡して実行する（id = 2のBob）	SELECT f_update_login_at(2);	'処理中にエラーが発生しました: 非アクティブユーザーの処理はできません: id 2' （WARNING メッセージが出力され、t_errorにエラーが挿入されます）
3. 存在しないユーザーで実行する（id = 999）	SELECT f_update_login_at(999);	'対象ユーザーが見つかりません: id 999' （NOTICE メッセージが出力されます）

上記の3パターンを実行するSQL：

SELECT f_update_login_at(1);
SELECT f_update_login_at(2);
SELECT f_update_login_at(999);

実行時raise文一式：

[2024-10-15 17:21:27.810293+09] 処理を開始します: ユーザーid 1
[2024-10-15 17:21:27.81139+09] 処理が正常に完了しました: ユーザーid 1
[2024-10-15 17:21:27.943738+09] 処理を開始します: ユーザーid 2
[2024-10-15 17:21:27.944734+09] 処理中にエラーが発生しました: 非アクティブユーザーの処理はできません: id 2
[2024-10-15 17:21:28.210873+09] 処理を開始します: ユーザーid 999
[2024-10-15 17:21:28.211122+09] 対象ユーザーが見つかりません: id 999

呼び出しパターン実行後の、各テーブルselect結果

-- テーブル内容の確認
SELECT * FROM m_user;
SELECT * FROM t_error;

m_user：
Aliceの最終ログイン日時が更新されています。

id	name	status	last_login_at
2	Bob	inactive	2023-01-01 09:00:00.000 +0900
3	Cathy	active	2023-01-02 11:00:00.000 +0900
1	Alice	active	2024-10-15 17:21:27.811 +0900

t_error：
例外処理を行っている、無効化されたユーザーへ処理が試みられたことが記録されています。

id	created_at	message
1	2024-10-15 17:21:27.944 +0900	非アクティブユーザーの処理はできません: id 2

後始末：リソースの削除

-- テーブルの削除
DROP TABLE IF EXISTS m_user;
DROP TABLE IF EXISTS t_error;

-- 関数の削除
DROP FUNCTION IF EXISTS f_update_login_at(INTEGER);

実務で検討・確認すべき内容

• 例外を登録するt_errorの定義情報や例外処理は本題ではないためシンプルにしています。実務で使う際は要件を確認してください。
• ユーザーが無効化されている場合だけでなく、ユーザーが存在しない場合もraise exceptionすべきかもしれません。
• m_userのstatusはTEXT型で定義していますが、実際にはm_statusを新規作成して定義するか、カスタムtypeを定義すべきかもしれません。
• RECORD型として定義しているv_userは、m_userの構造が変更されても動的に結果を定義・投入できるので使っています。存在しない列にアクセスしようとしても定義時点でエラーが検出されないので、より明示的な定義が適切かもしれません。
• Postgres公式ドキュメントに、いろんな使い方が書かれています。メッセージを表示するだけではなくlogとして書き残す設定の存在にも言及があります。