# SQL 与 PL/pgSQL 函数

LLMS 索引： [llms.txt](/llms.txt)

---

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

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

## 13.2.1 参数、返回值、集合与多态 {#item-13-2-1}

### 先选最小语言

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

```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](https://www.postgresql.org/docs/18/xfunc.html)。

### 参数模式与调用方式

常见参数模式：

| 模式 | 含义 | 是否参与调用输入 |
|---|---|---|
| `IN` | 输入，默认模式 | 是 |
| `OUT` | 命名输出列 | 否 |
| `INOUT` | 输入后作为输出 | 是 |
| `VARIADIC` | 把尾部实参收成数组 | 是 |

命名参数允许：

```sql
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 成功。

### 标量、复合与集合返回

#### 标量

```sql
RETURNS boolean
```

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

#### 多列单行

本章使用 `RETURNS TABLE`：

```sql
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`：

```sql
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`
类型族会为多个实参选择共同类型：

```sql
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$;
```

调用：

```sql
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 可以有同名、不同输入类型的函数：

```sql
quote_id(bigint)
quote_id(uuid)
```

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

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

对安全敏感调用：

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

官方
[Function Overloading](https://www.postgresql.org/docs/18/xfunc-overload.html)
明确提醒：重载在存在不可信用户的数据库中带来额外安全注意事项。

### SQL body 的两种写法

字符串 body：

```sql
AS $function$
    SELECT ...
$function$;
```

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

```sql
RETURN expression;
```

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

## 13.2.2 波动性、严格性、并行安全与规划影响 {#item-13-2-2}

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

三类波动性：

| 声明 | 对同一语句的承诺 | 是否可写数据库 | 典型例子 |
|---|---|---:|---|
| `VOLATILE` | 每次调用都可能不同 | 是 | `random()`、命令函数 |
| `STABLE` | 同一语句内相同输入结果稳定 | 否 | 查询当前配置或表快照 |
| `IMMUTABLE` | 相同输入永久得到相同结果 | 否 | 纯数学、固定规则 |

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

本章：

```text
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](https://www.postgresql.org/docs/18/xfunc-volatility.html)。

### `STRICT` 的精确含义

`STRICT` 等价于 `RETURNS NULL ON NULL INPUT`：

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

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

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

### 并行标签

| 标签 | 规划含义 |
|---|---|
| `PARALLEL SAFE` | 可在 parallel worker 中运行 |
| `PARALLEL RESTRICTED` | 并行计划中只能由 leader 运行 |
| `PARALLEL UNSAFE` | 出现在查询中会阻止并行计划 |

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

把不安全函数误标 safe 不只是性能问题，可能报错或产生错误结果。拿不准就
保留默认 `UNSAFE`。规则由
[CREATE FUNCTION](https://www.postgresql.org/docs/18/sql-createfunction.html)
定义。

### `COST` 与 `ROWS`

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

```sql
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](/labs/ch13/routine-catalog.sql) 读取：

```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 异常、子事务与错误契约 {#item-13-2-3}

### 错误是接口结果的一部分

不稳定的做法：

```plpgsql
RAISE EXCEPTION 'bad order';
```

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

```plpgsql
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.';
```

客户端判断：

```text
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` 块时，函数错误向外传播，调用语句失败；调用者事务进入
相应失败状态。这保留了原子性。

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

```plpgsql
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
```

它会：

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

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

### `EXCEPTION` 块形成子事务

PL/pgSQL：

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

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

本章 [exception-probe.sql](/labs/ch13/exception-probe.sql) 证明：

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

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

### 读取原始错误字段

在 handler 中：

```plpgsql
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](https://www.postgresql.org/docs/18/plpgsql-control-structures.html)。

### 只捕获能解决的错误

合理用途：

- 把已知底层约束错误转换成稳定领域 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. 能否在应用角色下执行正负路径测试。

---

[上一节：先决定逻辑放在哪里](../01/) · [返回本章目录](../) · [下一节：触发器与约束触发器](../03/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)