はじめに
PostgreSQLでは、業務ロジックをDB側に持たせる手段として「ストアドプロシージャ（PROCEDURE）」と「ストアドファンクション（FUNCTION）」があります。両者は似ていますが役割が異なるため、違いを整理しつつPL/pgSQLの基礎から解説します。
この後の流れ
- ストアドプロシージャとは何か
- PROCEDUREとFUNCTIONの違い
- PL/pgSQLの基本構文
- 制御構文（条件分岐・ループ）
- トランザクション制御（COMMIT/ROLLBACK）
- 応用：エラーハンドリングと例外処理
- まとめ
1. ストアドプロシージャとは何か
DBサーバー内に保存され、呼び出すことで実行できる一連のSQL処理・ロジックのまとまりです。アプリケーション側（Python等）に書いていたロジックの一部をDB内に移すことで、以下のメリットがあります。
| メリット | 内容 |
|---|---|
| ネットワーク往復の削減 | 複数SQLを1回の呼び出しで完結できる |
| ロジックの一元管理 | 複数アプリから同じ処理を共通利用できる |
| トランザクション制御 | 手続き内で明示的にCOMMIT/ROLLBACKできる（FUNCTIONにはできない） |
2. PROCEDUREとFUNCTIONの違い
PostgreSQL 11以降、PROCEDUREとFUNCTIONは明確に使い分けられています。
| 項目 | FUNCTION | PROCEDURE |
|---|---|---|
| 戻り値 | 必須（RETURNSで型指定） | 基本的に不要（OUTパラメータで代替可） |
| 呼び出し方 | SELECT my_func(...) |
CALL my_proc(...) |
| トランザクション制御 | 内部でCOMMIT/ROLLBACK不可 | 内部でCOMMIT/ROLLBACK可能 |
| 主な用途 | 値の計算・変換・SELECT内での利用 | バッチ処理、複数ステップの業務トランザクション |
判断基準: 「値を1つ計算して返したいだけ」ならFUNCTION、「複数のテーブル更新を含む一連の業務処理をトランザクション単位で実行したい」ならPROCEDUREを選びます。
3. PL/pgSQLの基本構文
PostgreSQL独自の手続き型言語がPL/pgSQLです。基本構造は以下の通りです。
CREATE OR REPLACE PROCEDURE update_stock(
p_product_id INT,
p_quantity INT
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE products
SET stock = stock - p_quantity
WHERE product_id = p_product_id;
INSERT INTO stock_log (product_id, change_amount, changed_at)
VALUES (p_product_id, -p_quantity, NOW());
END;
$$;
呼び出しは以下の通りです。
CALL update_stock(101, 5);
FUNCTIONの場合は戻り値を伴います。
CREATE OR REPLACE FUNCTION get_stock(p_product_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
v_stock INT;
BEGIN
SELECT stock INTO v_stock FROM products WHERE product_id = p_product_id;
RETURN v_stock;
END;
$$;
SELECT get_stock(101);
4. 制御構文（条件分岐・ループ）
CREATE OR REPLACE PROCEDURE apply_discount(p_product_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
v_price NUMERIC;
BEGIN
SELECT price INTO v_price FROM products WHERE product_id = p_product_id;
IF v_price >= 10000 THEN
UPDATE products SET price = price * 0.9 WHERE product_id = p_product_id;
ELSIF v_price >= 3000 THEN
UPDATE products SET price = price * 0.95 WHERE product_id = p_product_id;
ELSE
RAISE NOTICE '割引対象外の商品です: %', p_product_id;
END IF;
END;
$$;
複数行に対するループ処理（FOR文）の例です。
CREATE OR REPLACE PROCEDURE recalculate_all_stock()
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT product_id FROM products LOOP
UPDATE products
SET stock = (SELECT COALESCE(SUM(change_amount), 0) FROM stock_log WHERE product_id = rec.product_id)
WHERE product_id = rec.product_id;
END LOOP;
END;
$$;
5. トランザクション制御（COMMIT/ROLLBACK）
PROCEDURE内では明示的にトランザクションを制御できます。これがFUNCTIONにはない最大の特徴です。
CREATE OR REPLACE PROCEDURE process_orders_batch()
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT order_id FROM orders WHERE status = '未処理' LOOP
BEGIN
UPDATE orders SET status = '処理済' WHERE order_id = rec.order_id;
COMMIT; -- 1件ごとに確定させる（バッチ処理での部分コミット）
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE '注文ID % の処理に失敗しました: %', rec.order_id, SQLERRM;
END;
END LOOP;
END;
$$;
注意:
COMMIT/ROLLBACKはトップレベルのPROCEDURE呼び出し（CALL）のコンテキストでのみ有効です。他のPROCEDURE/FUNCTIONから呼ばれるネストした状態では使えません。
6. 応用：エラーハンドリングと例外処理
CREATE OR REPLACE PROCEDURE transfer_stock(
p_from_product_id INT,
p_to_product_id INT,
p_quantity INT
)
LANGUAGE plpgsql
AS $$
DECLARE
v_from_stock INT;
BEGIN
SELECT stock INTO v_from_stock FROM products WHERE product_id = p_from_product_id;
IF v_from_stock < p_quantity THEN
RAISE EXCEPTION '在庫不足です。product_id=%, 現在庫=%', p_from_product_id, v_from_stock;
END IF;
UPDATE products SET stock = stock - p_quantity WHERE product_id = p_from_product_id;
UPDATE products SET stock = stock + p_quantity WHERE product_id = p_to_product_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'エラーが発生したためロールバックします: %', SQLERRM;
ROLLBACK;
END;
$$;
RAISE EXCEPTIONで意図的にエラーを発生させ、EXCEPTION WHEN OTHERSでキャッチしてロールバックする、という基本パターンです。
7. まとめ
| 観点 | FUNCTION | PROCEDURE |
|---|---|---|
| 値の計算・変換 | ◎ | △ |
| 複数テーブルの一括更新 | △ | ◎ |
| トランザクション制御 | 不可 | 可能 |
| SELECT文の中で使う | 可能 | 不可（CALLでのみ実行） |
| バッチ処理・夜間ジョブ | 不向き | 向いている |
さいごに
PostgreSQLのPROCEDUREは、アプリ側で書きがちな「複数更新＋条件分岐＋トランザクション」というロジックをDB側に寄せられる強力な機能です。