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 函数。整个示例可在空白测试库中直接执行。

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

预期输出:

text
 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 分别代表安全、受限和不安全。

sql
SELECT
    proname,
    provolatile,
    proparallel
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace
  AND proname IN (
      'get_product_price',
      'calculate_discounted_price'
  )
ORDER BY proname;

预期输出:

text
           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 和异常捕获,并通过一个可运行的库存调整函数演示完整控制流。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

SQL 与 PL/pgSQL 函数

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通