SQL 函數與 Stored Procedure：PostgreSQL 函數生態系統完全指南 | PostgreSQL
2026/07/02
函數（Function） 與 Stored Procedure 是 PostgreSQL 中封裝可重用邏輯的核心機制。Function 自 PostgreSQL 誕生起即存在，可在查詢中直接呼叫並回傳值；Stored Procedure 則於 PG11 正式引入，支援獨立的 Transaction 控制。掌握兩者的差異、參數模式、回傳型別、Function Inlining、Polymorphic Functions 等進階特性，是資料庫端程式設計的關鍵能力。
Function vs Procedure 核心差異
上一篇我們介紹了 PL/pgSQL 語言本身，本篇聚焦於 PostgreSQL 的函數生態系統——包含 SQL Function、PL/pgSQL Function、Stored Procedure 的完整語法與進階特性。
| 特性 | Function | Procedure（PG11+） |
|---|---|---|
| 呼叫方式 | SELECT func() 或在查詢中使用 | CALL proc() |
| 回傳值 | 必須宣告回傳型別（含 void） | 無回傳值（可用 INOUT 參數模擬） |
| Transaction 控制 | 不可在函數內 COMMIT/ROLLBACK | 可在內部執行 COMMIT/ROLLBACK |
| 可用於查詢 | 可用在 SELECT、WHERE、FROM 等 | 不可在 SQL 語句中使用 |
| 主要用途 | 計算、查詢、資料轉換 | 批次處理、多步驟 Transaction 流程 |
選擇原則：若需要在 SELECT 中使用或需要回傳資料，選用 Function；若需要在執行過程中控制 Transaction（如批次提交），選用 Procedure。
四種實作語言
PostgreSQL 支援多種程式語言撰寫函數：
| 語言 | 特色 | 適用場景 |
|---|---|---|
| SQL | 最輕量，可被 Planner 內聯（Inlining）優化 | 簡單計算、型別轉換 |
| PL/pgSQL | 完整程式語言，支援條件、迴圈、例外處理 | 複雜業務邏輯（最常用） |
| C | 效能最高，直接編譯為共享函式庫 | 計算密集型、系統級操作 |
| PL/Python | 可用 Python 生態系（NumPy、scikit-learn） | ML 推論、文字處理、呼叫外部 API |
-- 查詢已安裝的程式語言
SELECT lanname, lanpltrusted FROM pg_language ORDER BY lanname;
CREATE FUNCTION 完整語法
CREATE [OR REPLACE] FUNCTION function_name (
[ parameter_name ] [ IN | OUT | INOUT | VARIADIC ] parameter_type
[ DEFAULT default_value ]
[, ...]
)
RETURNS return_type
LANGUAGE language_name
[ IMMUTABLE | STABLE | VOLATILE ]
[ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
[ COST execution_cost ]
[ ROWS result_rows ]
[ SECURITY { INVOKER | DEFINER } ]
[ SET configuration_parameter { TO | = } value ]
AS $$
-- 函數主體
$$;
參數模式：IN / OUT / INOUT / VARIADIC
IN 參數（預設）
CREATE OR REPLACE FUNCTION calculate_tax(
amount NUMERIC,
tax_rate NUMERIC
)
RETURNS NUMERIC
LANGUAGE sql IMMUTABLE
AS $$ SELECT amount * tax_rate / 100.0; $$;
SELECT calculate_tax(1000, 8.5); -- 85.00
OUT 參數（多值回傳）
CREATE OR REPLACE FUNCTION get_min_max(
IN arr NUMERIC[],
OUT min_val NUMERIC,
OUT max_val NUMERIC
)
LANGUAGE sql IMMUTABLE
AS $$
SELECT MIN(v), MAX(v) FROM unnest(arr) AS v;
$$;
SELECT * FROM get_min_max(ARRAY[3, 1, 4, 1, 5, 9, 2, 6]);
-- min_val | max_val
-- --------+--------
-- 1 | 9
INOUT 參數
CREATE OR REPLACE FUNCTION double_value(INOUT val NUMERIC)
LANGUAGE sql IMMUTABLE
AS $$ SELECT val * 2; $$;
SELECT * FROM double_value(7); -- 14
VARIADIC 參數（不定數量）
CREATE OR REPLACE FUNCTION sum_all(VARIADIC nums NUMERIC[])
RETURNS NUMERIC
LANGUAGE sql IMMUTABLE
AS $$
SELECT SUM(v) FROM unnest(nums) AS v;
$$;
-- 多種呼叫方式
SELECT sum_all(1, 2, 3); -- 6
SELECT sum_all(1, 2, 3, 4, 5, 6); -- 21
SELECT sum_all(VARIADIC ARRAY[10, 20, 30]); -- 60（展開陣列）
預設值與具名參數呼叫
CREATE OR REPLACE FUNCTION create_order(
customer_id BIGINT,
product_id BIGINT,
quantity INT DEFAULT 1,
discount_pct NUMERIC DEFAULT 0.0,
notes TEXT DEFAULT NULL
)
RETURNS BIGINT
LANGUAGE plpgsql AS $$
DECLARE
new_order_id BIGINT;
BEGIN
INSERT INTO orders (customer_id, product_id, quantity, discount_pct, notes)
VALUES (customer_id, product_id, quantity, discount_pct, notes)
RETURNING id INTO new_order_id;
RETURN new_order_id;
END;
$$;
-- 位置引數（只傳必要參數）
SELECT create_order(42, 101);
-- 具名引數（Named Arguments）— 跳過中間參數
SELECT create_order(
customer_id => 42,
product_id => 101,
notes => '急件優先處理',
discount_pct => 5.0
);
-- 混合使用（位置在前，具名在後）
SELECT create_order(42, 101, quantity => 3, notes => '生日特惠');
RETURNS TABLE / SETOF（回傳集合）
RETURNS TABLE（推薦，欄位名稱清晰）
CREATE OR REPLACE FUNCTION get_active_users(min_age INT DEFAULT 18)
RETURNS TABLE(
user_id BIGINT,
username TEXT,
email TEXT,
age INT,
created_at TIMESTAMPTZ
)
LANGUAGE plpgsql STABLE AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.username, u.email,
DATE_PART('year', AGE(u.birthdate))::INT,
u.created_at
FROM users u
WHERE u.is_active = TRUE
AND DATE_PART('year', AGE(u.birthdate)) >= min_age
ORDER BY u.username;
END;
$$;
-- 可直接在 FROM 子句使用，支援 WHERE 過濾
SELECT * FROM get_active_users(21) WHERE email LIKE '%@gmail.com';
RETURNS SETOF（回傳已存在的型別）
-- 回傳整張表的行型別
CREATE OR REPLACE FUNCTION get_users_by_dept(dept_name TEXT)
RETURNS SETOF users
LANGUAGE sql STABLE
AS $$
SELECT * FROM users
WHERE department = dept_name
ORDER BY last_name, first_name;
$$;
-- 回傳純量型別的集合
CREATE OR REPLACE FUNCTION get_user_emails(dept_name TEXT)
RETURNS SETOF TEXT
LANGUAGE sql STABLE
AS $$
SELECT email FROM users WHERE department = dept_name;
$$;
RETURN NEXT（逐行回傳，適合自訂邏輯）
CREATE OR REPLACE FUNCTION generate_series_custom(
start_val INT, end_val INT, step INT DEFAULT 1
)
RETURNS SETOF INT
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
current_val INT := start_val;
BEGIN
WHILE current_val <= end_val LOOP
RETURN NEXT current_val;
current_val := current_val + step;
END LOOP;
END;
$$;
SELECT * FROM generate_series_custom(1, 10, 2);
-- 1, 3, 5, 7, 9
Function Overloading（同名不同參數）
PostgreSQL 允許多個函數擁有相同名稱，只要參數型別或數量不同：
-- 版本 1：TEXT 輸入
CREATE OR REPLACE FUNCTION format_phone(phone TEXT)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
SELECT REGEXP_REPLACE(
REGEXP_REPLACE(phone, '[^0-9]', '', 'g'),
'^(\d{2})(\d{4})(\d{4})$', '(\1) \2-\3'
);
$$;
-- 版本 2：帶國碼
CREATE OR REPLACE FUNCTION format_phone(phone TEXT, country_code TEXT)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
SELECT '+' || country_code || ' ' || format_phone(phone);
$$;
-- PostgreSQL 依傳入型別/數量自動選擇版本
SELECT format_phone('0912345678'); -- TEXT 版本
SELECT format_phone('0912345678', '886'); -- 兩參數版本
-- 查看同名函數的所有版本
SELECT proname, pg_get_function_arguments(oid) AS args
FROM pg_proc WHERE proname = 'format_phone';
注意：刪除 Overloaded 函數時必須指定參數型別：DROP FUNCTION format_phone(TEXT)。
Function Inlining（SQL 函數內聯最佳化）
SQL 函數在符合特定條件時，查詢規劃器會將函數定義展開（Inline） 到外部查詢中，使 Planner 能進行整體最佳化：
-- 可被內聯的 SQL 函數
CREATE OR REPLACE FUNCTION active_users()
RETURNS SETOF users
LANGUAGE sql STABLE
AS $$
SELECT * FROM users WHERE is_active = TRUE;
$$;
-- Planner 展開後等效於：
-- SELECT * FROM users WHERE is_active=TRUE AND username LIKE 'A%';
SELECT * FROM active_users() WHERE username LIKE 'A%';
可內聯的條件：
- 語言為
SQL（PL/pgSQL 無法內聯） - 函數主體是單一
SELECT語句 - 不含
LIMIT、OFFSET、DISTINCT ON - 不使用
SECURITY DEFINER - PG14 之前不含
VARIADIC
-- 驗證是否被內聯
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM active_users() WHERE id > 1000;
-- 若看到直接掃描 users 表（非 Function Scan），代表已內聯
經驗法則：能用純 SQL 寫的簡單函數就用 LANGUAGE sql（可內聯），需要條件邏輯、迴圈、例外處理時用 PL/pgSQL。
Polymorphic Functions（多型函數）
多型函數允許一個函數處理多種型別的輸入，避免為每種型別重複 Overloaded 版本。
| 型別 | 說明 |
|---|---|
anyelement | 任何純量型別 |
anyarray | 任何陣列型別 |
anynonarray | 任何非陣列型別 |
anyenum | 任何 Enum 型別 |
anycompatible（PG13+） | 自動型別提升（如 INT → BIGINT） |
-- 回傳陣列的第一個元素（自動適應型別）
CREATE OR REPLACE FUNCTION first_element(arr anyarray)
RETURNS anyelement
LANGUAGE sql IMMUTABLE
AS $$ SELECT arr[1]; $$;
SELECT first_element(ARRAY[1, 2, 3]); -- INTEGER 1
SELECT first_element(ARRAY['a', 'b', 'c']); -- TEXT 'a'
SELECT first_element(ARRAY[1.1, 2.2]); -- NUMERIC 1.1
安全移除 NULL 值
CREATE OR REPLACE FUNCTION remove_nulls(arr anyarray)
RETURNS anyarray
LANGUAGE sql IMMUTABLE
AS $$
SELECT ARRAY(
SELECT elem FROM unnest(arr) AS elem WHERE elem IS NOT NULL
);
$$;
SELECT remove_nulls(ARRAY[1, NULL, 3, NULL, 5]); -- {1,3,5}
SELECT remove_nulls(ARRAY['a', NULL, 'c']); -- {a,c}
anycompatible（PG13+）
-- 允許不同但相容的型別（如 INT 和 BIGINT 自動提升）
CREATE OR REPLACE FUNCTION greatest_compat(a anycompatible, b anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE
AS $$
SELECT CASE WHEN a > b THEN a ELSE b END;
$$;
SELECT greatest_compat(3::INT, 5::BIGINT); -- BIGINT 5（自動提升）
SELECT greatest_compat(3.14, 3); -- NUMERIC 3.14
SECURITY DEFINER vs INVOKER
| 面向 | SECURITY INVOKER（預設） | SECURITY DEFINER |
|---|---|---|
| 執行身份 | 呼叫者 | 函數擁有者 |
| 類比 | 一般程式 | Unix setuid |
| 適用場景 | 一般用途 | 權限封裝（允許透過函數存取受限資料） |
| 風險 | 低 | 需防範 search_path 攻擊 |
-- 允許一般使用者查詢薪資統計，但不能直接存取薪資表
CREATE OR REPLACE FUNCTION get_dept_salary_stats(dept_name TEXT)
RETURNS TABLE(avg_salary NUMERIC, min_salary NUMERIC, max_salary NUMERIC, headcount BIGINT)
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = public, pg_temp -- 必須！防止 search_path 攻擊
AS $$
SELECT ROUND(AVG(salary), 2), MIN(salary), MAX(salary), COUNT(*)
FROM hr.employee_salaries
WHERE department = dept_name;
$$;
-- 授予執行權限（不給表級權限）
GRANT EXECUTE ON FUNCTION get_dept_salary_stats(TEXT) TO app_user;
函數屬性
波動性（Volatility）
| 宣告 | 意義 | 可用於索引 |
|---|---|---|
IMMUTABLE | 相同輸入永遠回傳相同結果 | 可 |
STABLE | 同一 Transaction 內相同輸入回傳相同結果 | 不可 |
VOLATILE（預設） | 每次呼叫可能不同或有副作用 | 不可 |
-- IMMUTABLE：可用於函數索引
CREATE FUNCTION celsius_to_fahrenheit(c NUMERIC)
RETURNS NUMERIC LANGUAGE sql IMMUTABLE
AS $$ SELECT c * 9.0 / 5.0 + 32; $$;
CREATE INDEX idx_temp_f ON weather (celsius_to_fahrenheit(temp_c));
並行安全性（Parallel Safety）
-- PARALLEL SAFE：可在並行 Worker 中安全執行
CREATE FUNCTION compute_hash(data TEXT)
RETURNS TEXT LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$ SELECT MD5(data); $$;
-- PARALLEL RESTRICTED：只能在 Leader 中執行
-- PARALLEL UNSAFE（預設）：不能在並行模式中執行
COST 與 ROWS
-- COST：估計函數的執行成本（預設 100）
CREATE FUNCTION is_valid_email(email TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
COST 5 -- 比預設 100 便宜，鼓勵 Planner 積極使用
AS $$
BEGIN
RETURN email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END;
$$;
-- ROWS：回傳集合函數的預估行數（預設 1000）
CREATE FUNCTION get_recent_orders(days INT)
RETURNS SETOF orders LANGUAGE sql STABLE
ROWS 50 -- 告知 Planner 預期回傳約 50 筆
AS $$
SELECT * FROM orders
WHERE created_at > NOW() - (days || ' days')::INTERVAL;
$$;
CREATE PROCEDURE 與 Transaction 控制
Procedure 最重要的特性是在程序內部 COMMIT/ROLLBACK：
CREATE OR REPLACE PROCEDURE batch_process_orders(batch_size INT DEFAULT 1000)
LANGUAGE plpgsql AS $$
DECLARE
processed_count INT := 0;
total_count INT := 0;
order_record RECORD;
BEGIN
SELECT COUNT(*) INTO total_count
FROM orders WHERE status = 'pending';
RAISE NOTICE '開始批次處理，共 % 筆訂單', total_count;
FOR order_record IN
SELECT id, customer_id, amount FROM orders WHERE status = 'pending'
LOOP
BEGIN
UPDATE orders
SET status = 'processing', processed_at = NOW()
WHERE id = order_record.id;
INSERT INTO shipments (order_id, estimated_date)
VALUES (order_record.id, NOW() + INTERVAL '3 days');
processed_count := processed_count + 1;
-- 每 batch_size 筆提交一次，釋放鎖和 WAL 壓力
IF processed_count % batch_size = 0 THEN
COMMIT; -- Function 不可用！Procedure 專屬
RAISE NOTICE '已完成 % / % 筆', processed_count, total_count;
END IF;
EXCEPTION WHEN OTHERS THEN
INSERT INTO error_log (entity_id, error_msg, occurred_at)
VALUES (order_record.id, SQLERRM, NOW());
ROLLBACK; -- 回滾到上一個 COMMIT 點
END;
END LOOP;
COMMIT;
RAISE NOTICE '批次處理完成，共處理 % 筆', processed_count;
END;
$$;
-- 呼叫（必須在 autocommit 模式）
CALL batch_process_orders(500);
重要限制：CALL 必須在 Transaction Block 外，或在已 COMMIT 過的 Procedure 中呼叫。在顯式 BEGIN ... END Transaction 內呼叫含 COMMIT 的 Procedure 會報錯。
版本演進
| 版本 | 新功能 |
|---|---|
| PG 11 | CREATE PROCEDURE + Transaction 控制、CALL 語法 |
| PG 12 | SQL Function OUT 參數行為改善 |
| PG 13 | anycompatible 系列多型型別 |
| PG 14 | VARIADIC + 多型組合放寬、OUT 參數 DROP 改善 |
| PG 15+ | SQL 標準函數語法（RETURN 替代 AS $$...$$） |
-- PG14+ 簡潔語法
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT LANGUAGE sql IMMUTABLE
RETURN a + b; -- 不需要 AS $$...$$ 包裝
常見陷阱
STABLE 函數中的副作用
-- 錯誤：有副作用（INSERT）的函數標記為 STABLE
CREATE FUNCTION get_and_log_user(uid BIGINT)
RETURNS TEXT LANGUAGE plpgsql
STABLE -- 錯誤！應改為 VOLATILE
AS $$
BEGIN
SELECT username INTO ... FROM users WHERE id = uid;
INSERT INTO access_log ...; -- 副作用！
RETURN ...;
END;
$$;
型別不一致導致索引失效
-- 問題：參數 INT 與欄位 BIGINT 不一致 → 隱式轉換 → 索引失效
CREATE FUNCTION find_user(uid INT) -- 應該用 BIGINT
RETURNS TEXT LANGUAGE sql STABLE AS $$
SELECT username FROM users WHERE id = uid; -- id 是 BIGINT
$$;
NULL 回傳影響 WHERE
-- 函數回傳 NULL 時，WHERE 條件會過濾掉該行
CREATE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC LANGUAGE sql IMMUTABLE AS $$
SELECT CASE WHEN b = 0 THEN NULL ELSE a / b END;
$$;
-- 使用時注意 NULL 傳播：WHERE safe_divide(a, b) > 1 不會包含 b=0 的行
總結
函數與 Stored Procedure 是 PostgreSQL 資料庫端程式設計的完整工具箱：
- SQL Function 可被 Planner 內聯，適合簡單計算；PL/pgSQL Function 功能完整，適合複雜邏輯
- 參數模式 IN/OUT/INOUT/VARIADIC 提供靈活的輸入輸出設計
- RETURNS TABLE / SETOF 讓函數像虛擬表一樣在
FROM子句使用 - Polymorphic Functions（
anyelement/anycompatible）實現型別泛化，避免重複 Overloading - SECURITY DEFINER 實現權限封裝，但必須搭配
SET search_path防注入 - 函數屬性（IMMUTABLE/STABLE/VOLATILE、PARALLEL SAFE、COST/ROWS）是 Planner 最佳化的關鍵提示
- Stored Procedure（PG11+）支援 Transaction 控制，是批次 ETL 的最佳選擇
下一篇，我們將深入探討 分區管理——掌握 PostgreSQL 宣告式分區（Declarative Partitioning）與手動分區的完整操作實務。