複数ステップのプロシージャ作成：データチェック、挿入、ログ記録

実際のビジネスシナリオでは、単に1つの操作をやるだけじゃなくて、いくつかのアクションをチェーンでやる必要があることが多いよ。たとえば注文が入ったとき、まず顧客データをチェックして、注文を保存して、監査用にログを残す、みたいな。複数ステップのプロシージャを使えば、これらのステップを1つのロジックにまとめて、トランザクションで整合性も保証できる。どこかで失敗したら全部ロールバックされるから安心！

PostgreSQLの新しいバージョン（特にCREATE PROCEDUREが追加されてから）では、関数とプロシージャの違い、トランザクションの扱い方（SAVEPOINTやロールバック、エラーブロックなど）をちゃんと理解しておくのが大事だよ。

複数ステッププロシージャの基本構造

典型的なビジネスプロシージャはこんなステップでできてる：

1. データチェック — 引数のバリデーション、顧客や商品が存在するかの確認など。
2. データ挿入 — 実際にレコードを追加（または更新）する。
3. ログ記録や監査 — 成功・失敗の情報を記録する。

各ステップは1つのトランザクション内（アトミックに）やってもいいし、処理が長い場合や部分的なエラー処理が必要な場合はSAVEPOINTを作って、例外処理ブロックでローカルにロールバックすることもできるよ。

例：整合性チェック付きの注文追加

こんな状況を考えてみよう — 3つのテーブルがある：

• customers — 顧客
• orders — 注文
• order_log — 注文ログ

スキーマを用意しよう：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    amount NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_log (
    log_id SERIAL PRIMARY KEY,
    order_id INT,
    log_message TEXT NOT NULL,
    log_date TIMESTAMP NOT NULL DEFAULT NOW()
);

複数ステッププロシージャの作成：関数 or プロシージャ？

重要！

• トランザクションを完全にコントロールしたい（セーブポイントや明示的なCOMMIT/ROLLBACKが必要）ならCREATE PROCEDUREを使おう。
• ロジックがアトミック（「全部やるか、全部やらないか」）で、他のSQLから呼び出すなら関数を使おう。

関数バージョン（アトミックなロジック）：

CREATE OR REPLACE FUNCTION add_order(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 1. 顧客チェック
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'ID % の顧客は存在しません', p_customer_id;
    END IF;

    -- 2. 注文挿入
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. ログ記録
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, '注文が正常に作成されました。');

    RAISE NOTICE '注文 %（顧客 %）が正常に追加されました', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

ポイント：PostgreSQLの関数は常に1つの外部トランザクション内で実行される。関数内でCOMMIT、ROLLBACK、SAVEPOINTなどのトランザクション制御は使えない。ロールバックやコミットは外側でやる。

エラー処理とエラーログ付きバージョン：

CREATE OR REPLACE FUNCTION add_order_with_error_logging(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    BEGIN
        -- 顧客チェック
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID % の顧客は存在しません', p_customer_id;
        END IF;

        -- 注文挿入
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- ログ記録
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, '注文が正常に作成されました。');

        RAISE NOTICE '注文 %（顧客 %）が正常に追加されました', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('エラー: %', SQLERRM));
            RAISE; -- 関数のトランザクション全体をロールバック
    END;
END;
$$ LANGUAGE plpgsql;

BEGIN ... EXCEPTION ... ENDブロック：PL/pgSQLの関数やプロシージャ内では、このブロックが仮想セーブポイントを作る。ブロック内でエラーが起きたら、その中の変更だけロールバックされる。

部分コミットとステップごとの処理：プロシージャが必要な理由

ステップごとにコミットしたい（本当に部分的な確定が必要）ならプロシージャを使おう！

PostgreSQL 11以降はCREATE PROCEDUREでサーバーサイドのトランザクションやセーブポイントを操作できるようになった。プロシージャだけ（関数じゃなく！）でCOMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINTが明示的に使える。ただし：PL/pgSQLのプロシージャ内でROLLBACK TO SAVEPOINTは使えないから、例外ハンドラを使おう。

ステップごとの処理とエラー処理付きプロシージャの例

CREATE OR REPLACE PROCEDURE add_order_step_by_step(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 最初のブロック：顧客チェック
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'ID % の顧客は存在しません', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('エラー（バリデート）: %', SQLERRM));
            RETURN;
    END;

    -- 2番目のブロック：注文挿入
    BEGIN
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('エラー（注文）: %', SQLERRM));
            RETURN;
    END;

    -- 3番目のブロック：成功時のログ記録
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, '注文が正常に作成されました。');
    EXCEPTION
        WHEN OTHERS THEN
            -- ここはログ記録が失敗しても気にしない
            RAISE NOTICE '注文 % のログ書き込みに失敗', v_order_id;
    END;

    RAISE NOTICE '注文 %（顧客 %）が正常に追加されました（プロシージャ）', v_order_id, p_customer_id;
END;
$$;

プロシージャの呼び出し：

CALL add_order_step_by_step(1, 150.50);

トランザクションとプロシージャのベストプラクティス

• アトミックなビジネス操作には関数を使おう — 「全部やるか全部やらないか」が必要なとき。
• ステップごとのコミットや部分的なロールバックが必要ならプロシージャを使おう。明示的なトランザクション外（autocommitモード）で呼び出すのがコツ。
• 「部分ロールバック」にはBEGIN ... EXCEPTION ... ENDブロックを使おう — この中でPL/pgSQLが自動でセーブポイントを作って、エラー時にそのブロックだけロールバックしてくれる。
• エラーは必ずログに残そう — 何がうまくいかなかったか、後で分かるようにするのが大事！
• PL/pgSQLのプロシージャ内でROLLBACK TO SAVEPOINTは使わないで — PostgreSQL 17+の制限でシンタックスエラーになるよ。

テスト：成功パターンと失敗パターン

-- 顧客を追加
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- 関数を呼び出し（成功するはず）
SELECT add_order(1, 300.00);

-- 存在しない顧客で関数を呼び出し（エラーになる）
SELECT add_order(999, 100.00);

-- ログテーブルをチェック
SELECT * FROM order_log;