函式跟程序的差異

在很多程式語言裡，function 跟 procedure 幾乎沒差別。但在 SQL 裡就不一樣了。 在 PostgreSQL 裡，function 跟 procedure 不只是兩種執行程式碼的方式。這是兩種完全不同的思維模式。

SQL 裡的 function 不能改資料庫的資料。它只能處理傳進來的資料，然後根據這些資料回傳結果。它就是要給你在 SELECT 查詢裡用的。

SQL 裡的 procedure 就是拿來改資料庫的。所以它可以用 transaction（跟 function 不一樣），可以寫東西進資料庫。但不能在 SELECT 查詢裡用。

來個簡單比較：

SQL 裡的差異

一般 SQL 裡，function 很像一個運算式：它計算然後回傳一個值。 procedure 就是指令：它做事，但不會出現在運算式裡。

SQL 裡的函式

SELECT calculate_discount(200);

• 可以用在 WHERE、ORDER BY、INSERT、UPDATE 等等。
• 要純粹：不能改資料庫狀態（如果是 IMMUTABLE/STABLE）。

SQL 裡的程序

CALL process_order(123);

• 不會回傳結果。
• 可以做 COMMIT、ROLLBACK、呼叫 RAISE、跑迴圈。

PL/pgSQL 裡的差異

PostgreSQL 裡的 function 可以想成一組運算。 超靈活：可以傳參數、用 if、迴圈、cursor、子查詢、回傳 row、scalar、table。

PL/pgSQL 裡的函式

CREATE FUNCTION square(x INT) RETURNS INT AS $$
BEGIN
    RETURN x * x;
END;
$$ LANGUAGE plpgsql;

重點：

• 一定要有 RETURNS
• 可以用 DECLARE、BEGIN、END、LOOP、IF、CASE
• 不能做 COMMIT/ROLLBACK
• 可以在 SELECT、UPDATE、CHECK、WHERE、RETURNING 裡呼叫

呼叫方式：

SELECT square(5);  -- 會回傳 25

PL/pgSQL 裡的程序

程序就是控制行為的機制。 超適合這些情境：

• 要做很多步驟跟邏輯；
• 更新或插入大量資料；
• 要用transaction 控制：COMMIT、ROLLBACK、SAVEPOINT。

CREATE PROCEDURE log_event(msg TEXT) AS $$
BEGIN
    INSERT INTO logs(message) VALUES (msg);
    COMMIT;
END;
$$ LANGUAGE plpgsql;

重點：

• 沒有 RETURNS
• 只能用 CALL 呼叫
• 允許用 COMMIT、ROLLBACK、SAVEPOINT
• 適合批次處理、migration、ETL

呼叫方式：

CALL log_event('處理完成');

為什麼要分 function 跟 procedure？

因為它們在 SQL 裡目標完全不同：

程序最大優勢 — COMMIT

程序可以自己控制 transaction。 也就是說你可以在程序裡直接：

BEGIN;
-- 邏輯
SAVEPOINT point1;
-- 嘗試更新
ROLLBACK TO point1;
COMMIT;

而function 裡 COMMIT 跟 ROLLBACK 都被禁止。 你硬要用就會看到：ERROR: invalid transaction termination in function

這代表function 一定要是 deterministic 跟安全的，但 procedure 可以做「骯髒工作」—— 清理、log、insert 都可以。

比較表

什麼時候該用哪個？

用 function，如果你：

• 想要回傳值；
• 在 SELECT 裡呼叫、要 filter 資料；
• 只是簡單計算、檢查或包 SQL。

用 procedure，如果你：

• 要做複雜的事；
• 需要 transaction 控制；
• 要批次處理、搬資料、archive、log。