在交易中呼叫程序和函式

現在的資料庫系統，商業邏輯常常直接寫在 server 端——用程序（procedure）和函式（function）來實作。用 PostgreSQL 的時候，搞懂函式和程序的差異（特別是 11+ 版本開始有程序）還有它們跟交易的互動方式，真的很重要。

下面我會講 PostgreSQL 17 裡，根據官方文件和目前的限制，交易機制、巢狀呼叫、還有程序/函式裡面部分回滾的重點。

重點概念：函式 vs 程序

函式（CREATE FUNCTION）——永遠都在同一個外部交易裡執行；在函式裡不能用明確的交易指令（BEGIN、COMMIT、ROLLBACK、SAVEPOINT）。

• 所有變更只會在外部交易層級 commit 或 rollback。
• 要「部分回滾」的話，可以用 BEGIN ... EXCEPTION ... END，但這不能讓你在函式裡面 commit。

程序（CREATE PROCEDURE）——就是為了讓你直接在 server 端控管交易（像是做部分 commit、分階段 rollback 之類的）。

• 在程序（PL/pgSQL）裡可以用 COMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINT。
• 重點：在 PL/pgSQL 程序裡不能用 ROLLBACK TO SAVEPOINT（會報語法錯誤）。
• 程序只能用獨立的 SQL 指令 CALL ... 來呼叫，不能用 SELECT 或在其他函式裡面呼叫。

怎麼在一個程序/函式裡呼叫另一個？

函式之間可以直接用名字呼叫其他函式：

-- 範例：計算折扣的函式
CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF order_total >= 100 THEN
        RETURN order_total * 0.1;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 處理訂單的函式會呼叫另一個函式
CREATE OR REPLACE FUNCTION process_order(order_id INT, order_total NUMERIC)
RETURNS VOID AS $$
DECLARE
    discount NUMERIC;
BEGIN
    discount := calculate_discount(order_total);
    RAISE NOTICE '折扣：%', discount;
    INSERT INTO orders_log (order_id, order_total, discount)
    VALUES (order_id, order_total, discount);
END;
$$ LANGUAGE plpgsql;

全部都在同一個外部交易裡執行！任何一個函式出錯，所有變更都會被回滾。

呼叫程序和巢狀交易

程序可以在其他程序裡用 CALL ... 呼叫（PostgreSQL 17 支援呼叫堆疊 CALL proc1() -> CALL proc2()），但交易規則還是一樣：

• 交易指令（COMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINT）只能在程序的最上層用。
• 如果你在已經有明確交易的情況下（像是 client 關掉 autocommit），在程序裡面用 COMMIT/SAVEPOINT 會報錯。

有交易控管的程序範例

-- 分批 commit 的程序（只在 autocommit 連線模式下有效）
CREATE PROCEDURE process_batch_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT order_id, order_total FROM incoming_orders LOOP
        BEGIN
            -- 每一批資料都分開存
            INSERT INTO orders (order_id, total) VALUES (rec.order_id, rec.order_total);
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO order_errors(order_id, err_text) VALUES (rec.order_id, SQLERRM);
        END;
        COMMIT;
    END LOOP;
END;
$$;

-- 呼叫程序
CALL process_batch_orders();

每次 COMMIT 後，會自動開始新的交易。

PL/pgSQL 裡的部分回滾（savepoint-like 行為）

PL/pgSQL（不管是函式還是程序）不支援 ROLLBACK TO SAVEPOINT 指令。

要回滾部分程式碼的變更，只能用 BEGIN ... EXCEPTION ... END 區塊：

BEGIN
    -- 一些操作
    BEGIN
        -- 可能會出錯的操作
    EXCEPTION WHEN OTHERS THEN
        -- 這個區塊的變更都會被回滾
        RAISE NOTICE '區塊內回滾！';
    END;
END;

在程序裡也可以用 SAVEPOINT 和 RELEASE SAVEPOINT，但不能用 ROLLBACK TO SAVEPOINT。這些主要是用來分階段，但只能靠 exception 處理來控管。

限制和最佳實踐

1. 函式——只能做原子操作：要嘛全部成功，要嘛全部回滾。只要有錯，所有變更都沒了。
2. 程序——只能用 CALL：只能用獨立 SQL 指令呼叫，不能從 SELECT/函式裡呼叫。巢狀交易控管可以，但要嚴格遵守 PL/pgSQL 的限制。
3. 部分回滾——只能用 EXCEPTION：官方推薦、支援的部分回滾方式（類似 SAVEPOINT）。
4. 巢狀程序只有用 CALL 呼叫時才能控管交易：不然會報錯。

邏輯和交易互動的常見問題

我可以在函式裡做「巢狀」交易嗎？

不行。全部都在同一個交易裡。要部分回滾只能用 EXCEPTION 區塊。

我可以在函式或匿名區塊裡用 COMMIT/ROLLBACK 嗎？

不行，這樣會報語法錯誤。要用程序。

可以從函式裡呼叫程序嗎？

不行，只能用 CALL 指令。不能從函式/SELECT 呼叫。

可以在程序裡用 ROLLBACK TO SAVEPOINT 嗎？

不行！PL/pgSQL 不允許。只能用 EXCEPTION 區塊。