PostgreSQL / 系统课时
SQL 与 PL/pgSQL 函数
SQL 与 PL/pgSQL 函数
本课解决 PostgreSQL 函数“能运行但声明不准确”的问题。你将分别使用 SQL 与 PL/pgSQL 编写可复用函数,并根据函数是否读取表、是否产生副作用,正确选择波动性和并行属性。
学习目标
- 判断简单查询函数与过程逻辑函数应使用哪种语言
- 编写可直接调用的 SQL 和 PL/pgSQL 函数
- 正确声明
IMMUTABLE、STABLE、VOLATILE与并行属性
先选择合适的函数语言
LANGUAGE sql 适合用一条查询或表达式完成计算。它结构短、优化空间大,常用于字段标准化、只读查询和简单聚合。
LANGUAGE plpgsql 适合需要变量、条件、循环或异常处理的逻辑。它能组织多条 SQL,但不应为了“看起来更正式”而替代本可用一条 SQL 完成的函数。
| 需求 | 推荐语言 | 原因 |
|---|---|---|
| 单条查询返回结果 | SQL | 代码短,语义直接 |
| 条件分支或中间变量 | PL/pgSQL | 支持过程控制 |
| 多步骤数据处理 | PL/pgSQL | 可控制执行顺序与异常边界 |
创建两个可复用函数
下面先创建商品价格表,再实现一个读取价格的 SQL 函数和一个计算折扣价的 PL/pgSQL 函数。整个示例可在空白测试库中直接执行。
DROP TABLE IF EXISTS product_price;
CREATE TABLE product_price (
product_id bigint PRIMARY KEY,
price numeric(10, 2) NOT NULL CHECK (price >= 0)
);
INSERT INTO product_price (product_id, price)
VALUES
(101, 120.00),
(102, 80.00);
CREATE OR REPLACE FUNCTION get_product_price(p_product_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE
PARALLEL SAFE
STRICT
AS $$
SELECT price
FROM product_price
WHERE product_id = p_product_id
$$;
CREATE OR REPLACE FUNCTION calculate_discounted_price(
p_price numeric,
p_rate numeric
)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
PARALLEL SAFE
STRICT
AS $$
DECLARE
v_rate numeric := LEAST(GREATEST(p_rate, 0), 1);
BEGIN
-- 折扣率被限制在 0 到 1,避免得到负数价格
RETURN round(p_price * (1 - v_rate), 2);
END;
$$;
SELECT
product_id,
get_product_price(product_id) AS original_price,
calculate_discounted_price(
get_product_price(product_id),
0.15
) AS discounted_price
FROM product_price
ORDER BY product_id;预期输出:
product_id | original_price | discounted_price
------------+----------------+------------------
101 | 120.00 | 102.00
102 | 80.00 | 68.00
(2 rows)get_product_price 读取表数据,同一条 SQL 语句内结果保持稳定,因此声明为 STABLE。calculate_discounted_price 只依赖参数,给定相同参数总是返回相同结果,因此可声明为 IMMUTABLE。
正确声明波动性
波动性不是性能标签,而是对函数行为的承诺。声明过强会让优化器错误地提前计算或复用结果。
| 属性 | 行为承诺 | 典型场景 |
|---|---|---|
IMMUTABLE |
相同参数永远得到相同结果 | 纯数学计算、文本转换 |
STABLE |
同一条语句内结果不变 | 只读表查询、读取当前事务快照 |
VOLATILE |
每次调用都可能变化 | 写数据、序列、随机数、时钟 |
未显式声明时默认是 VOLATILE。读取业务表的函数通常不能标为 IMMUTABLE,因为表中数据会在不同语句之间发生变化。
选择并行属性
并行属性决定函数能否在并行工作进程中执行。用户自定义函数默认是 PARALLEL UNSAFE,只有确认内部操作安全时才应放宽。
PARALLEL SAFE:可在并行工作进程中执行,例如纯计算或安全的只读查询。PARALLEL RESTRICTED:只能由并行计划的主进程执行,适合依赖部分会话状态的函数。PARALLEL UNSAFE:会写数据、操作序列、临时表或产生无法并行保证的副作用。
函数调用的其他函数也必须满足相应安全级别。只要无法确认,就保留默认的 PARALLEL UNSAFE,不要为了追求并行计划而冒险声明。
验证函数元数据
可以从系统目录检查实际保存的声明。provolatile 中 i、s、v 分别代表不可变、稳定和易变;proparallel 中 s、r、u 分别代表安全、受限和不安全。
SELECT
proname,
provolatile,
proparallel
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
AND proname IN (
'get_product_price',
'calculate_discounted_price'
)
ORDER BY proname;预期输出:
proname | provolatile | proparallel
-----------------------------+-------------+-------------
calculate_discounted_price | i | s
get_product_price | s | s
(2 rows)常见声明错误
把读取表数据的函数标为 IMMUTABLE,可能导致表达式索引或预计算结果长期复用旧值。把包含写操作的函数标为 PARALLEL SAFE,可能让并行执行产生错误或不可预测行为。
另一个常见问题是忽略 STRICT。当任一参数为 NULL 时,STRICT 会直接返回 NULL,避免进入函数体;只有函数确实遵守这一语义时才应使用。
本课总结
- 单条查询优先使用 SQL 函数,过程控制再使用 PL/pgSQL。
- 波动性描述结果随时间和语句变化的程度,不能按性能愿望随意声明。
- 并行属性必须覆盖函数内部及其调用链的真实安全边界。
下一课预告
下一课将把 PL/pgSQL 扩展到变量、循环、动态 SQL 和异常捕获,并通过一个可运行的库存调整函数演示完整控制流。