13.2 SQL 与 PL/pgSQL 函数

PostgreSQL function 可以出现在 SELECT 列表、WHERE、索引表达式、 生成列、约束、触发器和另一个例程中。正因为它嵌入查询，函数声明不只是 文档；优化器会相信波动性、严格性、并行安全、成本和预估行数。

本节先把函数看成一个带类型和规划属性的数据库 API，再进入 PL/pgSQL 控制流。

13.2.1 参数、返回值、集合与多态

先选最小语言

如果函数只需要一条或几条集合查询，优先 LANGUAGE sql：

CREATE FUNCTION shop_ch13.allowed_transition(
    p_from text,
    p_to text
)
RETURNS boolean
LANGUAGE sql
IMMUTABLE
STRICT
PARALLEL SAFE
AS $function$
    SELECT (p_from, p_to) IN (
        ('created', 'paid'),
        ('created', 'canceled'),
        ('created', 'expired'),
        ('paid', 'packing'),
        ('packing', 'shipped'),
        ('shipped', 'completed')
    )
$function$;

需要局部变量、分支、循环、动态 SQL、异常处理或多条命令编排时，才使用 LANGUAGE plpgsql。语言选择和 function/procedure 选择是两个维度： PL/pgSQL 既可以实现 function，也可以实现 procedure。

PostgreSQL 还支持其他过程语言和 C 扩展；它们引入安装、信任、二进制兼容 与崩溃边界，不属于“为了少写 SQL”就启用的选项。参见 User-Defined Functions。

参数模式与调用方式

常见参数模式：

命名参数允许：

SELECT *
FROM shop_ch13.transition_order(
    p_order_id        => 101,
    p_expected_version => 0,
    p_target_status   => 'canceled',
    p_actor           => 'api:user-42'
);

命名调用提高可读性，却也把参数名变成外部兼容面。CREATE OR REPLACE FUNCTION 不能随意改已有输入参数名；驱动和 SQL 可能已经按名调用。

默认参数必须位于无默认输入参数之后。增加默认参数看似兼容，却可能与已有 重载产生歧义。发布前要用实际调用类型测试解析，而不是只看 DDL 成功。

标量、复合与集合返回

标量

RETURNS boolean

适合纯判断或单一计算。调用者可把它嵌入表达式。

多列单行

本章使用 RETURNS TABLE：

CREATE FUNCTION shop_ch13.order_snapshot(p_order_id bigint)
RETURNS TABLE (
    result_order_id bigint,
    result_order_ref text,
    result_total_minor bigint,
    result_status text,
    result_version bigint,
    result_updated_at timestamptz
)
...

调用时把函数放在 FROM：

SELECT *
FROM shop_ch13.order_snapshot(102);

不要依赖 SELECT function(...) 返回的匿名复合显示格式；明确列形状更适合 驱动映射和版本评审。

集合

RETURNS SETOF some_type 或 RETURNS TABLE (...) 可以返回多行。集合函数 应回答：

• 顺序是否有合同；若有，函数内部或调用方必须显式 ORDER BY；
• 最大行数是多少；
• 能否被谓词下推或内联；
• ROWS 预估是否合理；
• 空集与一行 NULL 是否被清楚区分。

无 ORDER BY 的集合没有稳定顺序。把测试机当前顺序冻结为 API 行为，会在 计划、并行度或版本变化时失败。

表的复合类型

RETURNS shop_ch13.sales_order 很方便，但把函数 API 与整张表的物理列强 绑定。新增、删除、重排列会改变结果类型。对外接口通常更适合命名输出列或 专用复合类型。

多态类型

多态函数让实参类型决定返回类型。PostgreSQL 14+ 的 anycompatible 类型族会为多个实参选择共同类型：

CREATE FUNCTION clamp_value(
    value anycompatible,
    low   anycompatible,
    high  anycompatible
)
RETURNS anycompatible
LANGUAGE sql
IMMUTABLE
STRICT
PARALLEL SAFE
AS $function$
    SELECT greatest($2, least($1, $3))
$function$;

调用：

SELECT clamp_value(12, 0, 10);              -- integer 10
SELECT clamp_value(12.5::numeric, 0, 10);   -- numeric 10

anyelement/anyarray 要求相关参数是同一具体类型族；anycompatible* 允许 寻找可隐式转换的共同类型。多态并不表示动态类型逃逸：解析阶段必须能从 输入推导出实际类型。

使用多态前问三个问题：

1. 不同类型是否真的共享相同语义，而不只是共享运算符名字？
2. 隐式转换会不会丢精度或选到意外类型？
3. 错误是否比几个显式重载更难理解？

重载是类型解析协议

同一 schema 可以有同名、不同输入类型的函数：

quote_id(bigint)
quote_id(uuid)

PostgreSQL 根据参数数量、类型、隐式转换、首选类型和 search_path 解析。 未定型字符串字面量、默认参数和 VARIADIC 会增加歧义：

SELECT quote_id('42');          -- '42' 初始类型 unknown
SELECT quote_id(42::bigint);    -- 明确

对安全敏感调用：

• schema-qualify function；
• 给不明确的实参加显式 cast；
• 不在不受信 schema 中暴露可劫持的同名重载；
• 避免依赖微妙的隐式转换优先级。

官方 Function Overloading 明确提醒：重载在存在不可信用户的数据库中带来额外安全注意事项。

SQL body 的两种写法

字符串 body：

AS $function$
    SELECT ...
$function$;

在函数执行时解析。SQL-standard body：

RETURN expression;

或 BEGIN ATOMIC ... END 在创建时解析，能更早发现对象与类型错误，也能建立 更明确的依赖，但不适用于所有动态场景。无论使用哪一种，都要把 source 纳入版本库；从 pg_get_functiondef() dump 出来的结果是运行态证据，不是 源代码评审的替代品。

13.2.2 波动性、严格性、并行安全与规划影响

波动性是承诺，不是优化提示

三类波动性：

VOLATILE 是默认值。不要为了“让它更快”错误标成 IMMUTABLE。优化器可对 不可变常量调用做预计算，prepared statement 还可能复用已折叠结果。

本章：

allowed_transition(text,text) -> IMMUTABLE
order_snapshot(bigint)        -> STABLE
transition_order(...)         -> VOLATILE
capture_payment(...)          -> VOLATILE
trigger functions             -> VOLATILE

波动性也决定可见快照

对 SQL 和标准过程语言函数：

• STABLE / IMMUTABLE 内部查询使用调用语句建立的快照；
• VOLATILE 函数执行的每条查询可取得更新的快照；
• STABLE / IMMUTABLE 不能直接包含非 SELECT SQL 命令。

从表读取的函数通常最多是 STABLE，不是 IMMUTABLE。PostgreSQL 不会 彻底证明你对 IMMUTABLE 的承诺；错误标签可能返回过期或不一致结果。

依赖 TimeZone、lc_*、配置参数或 collation 的转换也往往不是 IMMUTABLE。例如时间文本解析在不同设置下可能不同。

完整语义见 Function Volatility Categories。

STRICT 的精确含义

STRICT 等价于 RETURNS NULL ON NULL INPUT：

任一输入为 NULL
  -> 不执行函数 body
  -> 直接返回 NULL

它不是“做严格校验”。如果 NULL 应返回业务错误、空集合或默认值，就不能 声明 STRICT。

本章的纯判断和 snapshot 是 strict；command function 需要自己给出输入 错误合同，因此没有用 STRICT 静默短路。

并行标签

默认是 UNSAFE。修改数据库、改事务状态、访问 sequence、持久改配置的 函数必须 unsafe；访问临时表、cursor、prepared statement 或 backend-local 状态通常 restricted。

把不安全函数误标 safe 不只是性能问题，可能报错或产生错误结果。拿不准就 保留默认 UNSAFE。规则由 CREATE FUNCTION 定义。

COST 与 ROWS

规划器不知道自定义函数真实成本，只能使用声明：

ALTER FUNCTION expensive_match(text)
COST 1000;

ALTER FUNCTION expand_tokens(text)
ROWS 20;

• COST 使用 cpu_operator_cost 单位；
• 对 set-returning function，cost 是每行成本；
• ROWS 只用于集合返回，默认估算可能与实际相差很大。

错误估算会改变 join 顺序、调用次数和计划形状。先用真实计划和数据证明偏差， 再调整；不要把 COST 当成强制 hint。

SQL function 内联与可观测性

满足条件的简单 SQL function 可能被优化器内联，调用形态会融入外层查询。 这通常有利于谓词优化，但意味着：

• 不要依赖函数一定作为独立执行节点；
• 函数级计数不等于完整调用 trace；
• 观察时同时看外层 query、plan 和 pg_stat_statements；
• 安全敏感函数不能靠“看起来像独立调用”建立边界。

SECURITY DEFINER、配置属性和更复杂 body 会限制可用的优化。不要为了内联 牺牲权限正确性。

从目录审计声明

routine-catalog.sql 读取：

SELECT
    p.oid::regprocedure,
    p.prokind,       -- f=function, p=procedure
    p.provolatile,   -- i/s/v
    p.proisstrict,
    p.proparallel,   -- s/r/u
    p.prosecdef,
    p.proconfig
FROM pg_proc AS p
...

DDL source 说明意图；pg_proc 证明目标数据库实际装了什么。发布门禁要比较 两者，而不是二选一。

13.2.3 异常、子事务与错误契约

错误是接口结果的一部分

不稳定的做法：

RAISE EXCEPTION 'bad order';

它默认使用通用 P0001，调用方只能解析 message。更好的合同：

RAISE EXCEPTION USING
    ERRCODE = 'P3613',
    MESSAGE = 'order status transition rejected',
    DETAIL = format(
        'order_id=%s transition=%s->%s',
        OLD.order_id,
        OLD.status,
        NEW.status
    ),
    HINT = 'Use an allowed transition through the command API.';

客户端判断：

SQLSTATE P3613 -> domain transition rejected
SQLSTATE 40001 -> retry whole transaction within budget
SQLSTATE 42501 -> deployment/privilege defect, do not retry

message 给人读，SQLSTATE 给程序判断。命名约束、schema/table/column 和 routine context 也应保留给诊断。

自定义 SQLSTATE 可以使用除 00000 之外的五字符编码，但应维护集中注册表。 不要使用以 000 结尾的 category code，因为异常处理只能匹配整个类别， 难以精确捕获。

默认传播通常是正确答案

没有 EXCEPTION 块时，函数错误向外传播，调用语句失败；调用者事务进入 相应失败状态。这保留了原子性。

不要在底层函数中这样写：

EXCEPTION WHEN OTHERS THEN
    RETURN NULL;

它会：

• 把权限错误、数据损坏和编程错误伪装成“无结果”；
• 丢掉 SQLSTATE 和上下文；
• 可能让外层事务提交部分工作；
• 让告警与重试策略失去依据。

尤其注意：OTHERS 不捕获 QUERY_CANCELED 和 ASSERT_FAILURE；显式捕获 它们通常也不明智。

EXCEPTION 块形成子事务

PL/pgSQL：

BEGIN
    -- inner block
    UPDATE ...;
    PERFORM risky_call();
EXCEPTION
    WHEN SQLSTATE 'P3613' THEN
        ...
END;

进入带 handler 的 block 后，内部持久化修改在错误时回滚；局部变量保持错误 发生时的值，handler 继续执行。底层由子事务实现，进入/退出比普通 block 昂贵。

本章 exception-probe.sql 证明：

event=caught-inner-subtransaction
sqlstate=P3613
status_after=created
version_after=0

非法更新没有逃出 inner block，外层仍取得错误字段。整个 probe 最后 ROLLBACK，不污染 fixture。

读取原始错误字段

在 handler 中：

GET STACKED DIAGNOSTICS
    caught_state   = RETURNED_SQLSTATE,
    caught_message = MESSAGE_TEXT,
    constraint_id  = CONSTRAINT_NAME,
    detail_text    = PG_EXCEPTION_DETAIL,
    hint_text      = PG_EXCEPTION_HINT,
    context_text   = PG_EXCEPTION_CONTEXT;

优先保留结构化字段；不要用正则从 message 提取约束名。控制结构与可用字段 见 PL/pgSQL Control Structures。

只捕获能解决的错误

合理用途：

• 把已知底层约束错误转换成稳定领域 SQLSTATE，同时保留 cause；
• 对一项可跳过的批任务记录失败后继续；
• 实现确有必要的补偿分支；
• 测试某个失败后内部修改确实回滚。

不合理用途：

• 用 unique violation 实现常规 upsert，而不用 ON CONFLICT；
• 在函数里无限重试 serialization failure；
• 捕获所有错误并写一条 NOTICE；
• 把 statement timeout 当成空结果；
• 在 trigger 中吞错，让非法主写入提交。

重试属于更外层的整事务协议

一个 function 调用可能读写多张表、触发多个 trigger。若收到 40001 或 40P01，重试其中某条内部 SQL 不能还原事务入口快照。应由知道完整业务 意图的一层，在有界预算内重放整个事务。

自定义领域拒绝 P3613/P3614/P3616/P3618 不是瞬态数据库错误：

• P3613：调用命令错误；
• P3614：事务最终事实不一致；
• P3616：先重新读取，再由业务决定；
• P3618：支付前置条件错误。

把所有错误都自动重试只会放大负载和隐藏缺陷。

本节检查表

发布一个 function 前确认：

1. 输入类型、参数名与默认值是否是有意的兼容面；
2. 返回标量、单行、多行和顺序是否明确；
3. 多态与重载能否对实际实参唯一解析；
4. volatility 是否真能兑现；
5. NULL 是否应该 strict 短路；
6. parallel 标签是否符合内部行为；
7. COST/ROWS 是否有证据；
8. 成功、空结果、领域拒绝和系统错误是否可区分；
9. handler 是否只捕获能处理的 SQLSTATE；
10. 失败是否保持调用者事务原子性；
11. 目录属性、ACL 与 source 是否一致；
12. 能否在应用角色下执行正负路径测试。

上一节：先决定逻辑放在哪里 · 返回本章目录 · 下一节：触发器与约束触发器 · 查看全书目录 · 查看索引中心