PHP小码农

Appearance

SQL 函数 vs PL/pgSQL 函数 ​

PostgreSQL 允许你用同一种 CREATE FUNCTION 语法写两种截然不同的函数：LANGUAGE sql 的函数体是一段交给优化器的查询，LANGUAGE plpgsql 的函数体是一段带变量、分支、异常处理的程序。本节讲清两者的执行模型差异、sql 函数内联（Inlining）的条件清单、plpgsql 为什么永远慢一档，以及「先用 sql，直到必须上 plpgsql」这条实践原则背后的依据。

与「函数参数与返回类型」的分工

函数参数与返回类型 讲参数怎么声明、返回值怎么定型；本节只聚焦函数体语言的选择：同样的逻辑写成 sql 还是 plpgsql，性能与能力上有什么区别。

前置知识

阅读本节前，建议先了解：函数参数与返回类型、CREATE FUNCTION 基础

基础概念 ​

两种语言最本质的差别不在语法，而在优化器能不能看见函数体内部。

text
LANGUAGE sql                          LANGUAGE plpgsql
┌────────────────────────┐            ┌────────────────────────┐
│ CREATE FUNCTION        │            │ CREATE FUNCTION        │
│  函数体 = SQL 语句     │            │  函数体 = 块结构文本   │
│  创建时解析为查询树    │            │  创建时仅校验块结构    │
└───────────┬────────────┘            └───────────┬────────────┘
            │                                     │
            │ 调用时：                            │ 首次调用：
            │  ① 可内联 → 拼进调用查询            │  ① 编译为执行树
            │     由调用者的规划器统一优化        │  ② 每条 SQL 走 SPI
            │  ② 不可内联 → 单独规划后执行        │     解析 → 规划 → 执行
            ▼                                     ▼
      优化器「看得见」                     优化器「看不见」

对照表：

维度LANGUAGE sqlLANGUAGE plpgsql
函数体形态一条或多条 SQL 语句块：DECLARE / BEGIN / END
局部变量无有
控制流 IF / LOOP无有
异常捕获无EXCEPTION 子句
动态 SQL无EXECUTE
输出日志无RAISE
可被内联可以永远不可以
计划缓存无（每次规划或内联）会话内按语句缓存
调用开销低有 SPI + 执行树解释开销
典型定位表达式封装、单查询映射业务逻辑、多步写入、错误处理

关键术语：

  • 内联（Inlining）：优化器把 sql 函数的函数体展开到调用点，随后与调用查询一起规划。
  • SPI（Server Programming Interface）：plpgsql 执行每条 SQL 语句时走的内部接口，负责解析、规划、执行。
  • 计划缓存（Plan Cache）：plpgsql 在同一会话中缓存已规划语句，避免每行都重新规划。
  • 常量折叠（Constant Folding）：参数全为常量且函数为 IMMUTABLE 时，调用点直接被替换成结果值。
  • 条件下推（Qual Pushdown）：内联后，调用方的 WHERE 条件可以进入函数体内的查询，从而使用索引。

先摆最反直觉的一条：plpgsql 不是「高级语法所以更强」，而是「优化器看不透所以更慢」。把一个单行表达式包成 plpgsql 函数，性能可能比 sql 版差一个数量级；反过来，让 sql 函数去做上千次逐行判断，又可能慢于 plpgsql——因为 sql 无法保存中间状态。

最小示例——同一逻辑两种写法：

sql
-- sql 版
CREATE FUNCTION is_even_sql(i bigint) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS $$ SELECT i % 2 = 0 $$;

-- plpgsql 版：逻辑一模一样，但优化器看不见内部
CREATE FUNCTION is_even_plpgsql(i bigint) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  RETURN i % 2 = 0;
END;
$$;
sql
SELECT is_even_sql(42) AS sql_result, is_even_plpgsql(42) AS plpgsql_result;
text
 sql_result | plpgsql_result
------------+----------------
 t          | t
(1 row)

结果相同，但 EXPLAIN 下的执行方式完全不同。

语法与用法 ​

LANGUAGE sql 的骨架：

text
CREATE FUNCTION 名称 (参数...)
RETURNS 返回类型
LANGUAGE sql
[ IMMUTABLE | STABLE | VOLATILE ]
[ SET search_path = ... ]
AS $$
  SQL 语句;      -- 可多条，用分号分隔
  SQL 语句       -- 最后一条必须是 SELECT 或带 RETURNING 的写语句
$$;

LANGUAGE plpgsql 的骨架：

text
CREATE FUNCTION 名称 (参数...)
RETURNS 返回类型
LANGUAGE plpgsql
[ IMMUTABLE | STABLE | VOLATILE ]
AS $$
[ <<label>> ]
[ DECLARE
    变量名 类型 [ := 初值 ];
]
BEGIN
  语句;
  ...
[ EXCEPTION
    WHEN 条件 THEN
      语句;
]
END;
$$;

最小可运行示例：

sql
CREATE FUNCTION sql_version(p_n integer) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $$ SELECT p_n * 2 $$;

CREATE FUNCTION plpgsql_version(p_n integer) RETURNS integer
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  RETURN p_n * 2;
END;
$$;
sql
SELECT sql_version(21) AS a, plpgsql_version(21) AS b;
text
 a  | b
----+----
 42 | 42
(1 row)

详细说明 ​

LANGUAGE sql 的执行模型 ​

sql 函数体在创建时就被解析成查询树，存进 pg_proc.prosrc（文本）与内部表示。调用时有两种走向：

  1. 可内联：函数体被替换到调用点，跟着调用查询一起规划。此时函数调用「消失」了。
  2. 不可内联：以调用时给定的参数单独规划一次，然后执行。

无论哪种，优化器都有机会看到函数体内部的表、条件与表达式。这正是 sql 函数的全部价值来源。

内联：sql 函数性能的关键 ​

内联最直接的收益是常量折叠与索引下推。

sql
CREATE TABLE t_inline (id integer PRIMARY KEY, payload text);
INSERT INTO t_inline SELECT g, 'v' || g FROM generate_series(1, 100000) AS g;
ANALYZE t_inline;

CREATE FUNCTION f_identity(i integer) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $$ SELECT i $$;

EXPLAIN (COSTS OFF) SELECT * FROM t_inline WHERE id = f_identity(500);
text
                QUERY PLAN
-------------------------------------------
 Index Only Scan using t_inline_pkey on t_inline
   Index Cond: (id = 500)
(2 rows)

函数调用在计划里完全消失，Index Cond 里直接是常量 500。

把同一个函数写成 plpgsql：

sql
CREATE FUNCTION f_identity_pg(i integer) RETURNS integer
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  RETURN i;
END;
$$;

EXPLAIN (COSTS OFF) SELECT * FROM t_inline WHERE id = f_identity_pg(500);
text
                   QUERY PLAN
-------------------------------------------------
 Seq Scan on t_inline
   Filter: (id = f_identity_pg(500))
(2 rows)

Filter 里残留着函数调用，索引用不上——十行的函数体，写错了语言就从索引扫描退化成全表扫描。

内联的条件清单 ​

内联不是无条件发生的，条件相当苛刻，而且官方文档并未完整列出。对标量函数，需要同时满足（核心几条）：

条件说明
语言是 sqlplpgsql、c、internal 一律不内联
不是 SECURITY DEFINER安全上下文不能改变，因此不内联
函数体是单条 SELECT 表达式不能是多条语句拼起来的
函数体不含 FROM / 子查询 / CTE一旦引用表就不满足标量内联条件
不含聚合、窗口函数count()、row_number() 等出局
不含 GROUP BY / HAVING / ORDER BY / DISTINCT / LIMIT / UNION同上
返回恰好一列，且类型与声明一致需要隐式转换就不行
不声明 RETURNS SETOF / TABLE / RECORD集合返回走另一套（表函数）内联规则
定义里没有 SET 子句有 SET search_path 就不内联
不是递归调用自身只展开最外层一次
声明 IMMUTABLE 时，体内不得调用非 IMMUTABLE 函数否则假设被破坏
声明 STABLE 时，体内不得调用 VOLATILE 函数同上
声明 STRICT 时，优化器必须能证明「任一参数为 NULL 则结果为 NULL」一般难以满足
实参若含易变或昂贵表达式，在体内只能被引用一次防止重复求值

注意最后一条：参数在内体被引用多次是常见的「内联杀手」。

sql
-- 参数 p 在体内用了两次，调用时传的是易变表达式 → 不内联
CREATE FUNCTION f_twice(p integer) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $$ SELECT p + p $$;

EXPLAIN (COSTS OFF) SELECT f_twice(random()::integer * 0) FROM t_inline LIMIT 1;

表函数（RETURNS SETOF / RETURNS TABLE）另有独立的一套条件，宽严不同：要求函数声明为 STABLE 或 IMMUTABLE、不能是 STRICT、函数体是单条 SELECT（可以是复杂查询，甚至含 CTE）、实参里不能有易变表达式或子查询。这套条件让可内联的表函数比视图更强大——视图不能带参数，它却能带。

LANGUAGE plpgsql 的执行模型 ​

plpgsql 函数体对 CREATE FUNCTION 而言只是一段字符串。创建时（check_function_bodies = on，默认值）会做块结构的语法校验；真正的编译发生在首次调用时，编译成执行树后在本会话内复用。

执行时，函数体里每一条 SQL 语句都要经过 SPI：

text
plpgsql 一次调用
  ├─ 进入函数：建立执行环境
  ├─ 语句 1：SPI 解析 → 规划（或命中本会话计划缓存）→ 执行
  ├─ 语句 2：同上
  └─ 返回：销毁执行环境

代价主要体现在三处：

  • 无法内联，优化器对函数体内部一无所知；
  • 逐语句的 SPI 开销，即使命中计划缓存，仍有执行树解释与上下文切换；
  • 类型转换与变量赋值，plpgsql 的变量有独立的内存管理。

sql 函数做不到的事 ​

这些能力 sql 完全没有，遇到就得换语言：

需求sqlplpgsql
保存中间结果✗DECLARE v integer;
IF / CASE 分支逻辑✗✓
FOR / LOOP 循环✗✓
捕获并处理错误✗EXCEPTION WHEN ...
动态拼接 SQL✗EXECUTE
输出 NOTICE / 日志✗RAISE
逐行游标处理✗FOR r IN SELECT ...
事务控制✗仅过程 CALL 支持

另外，sql 函数体内不能使用 COMMIT、SAVEPOINT、VACUUM 这类语句。

plpgsql 的块结构：DECLARE / BEGIN / EXCEPTION ​

sql
CREATE FUNCTION safe_divide(p_a numeric, p_b numeric)
RETURNS numeric
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
  v_result numeric;
BEGIN
  IF p_b = 0 THEN
    RAISE EXCEPTION '除数不能为零'
      USING ERRCODE = '22012';
  END IF;

  v_result := round(p_a / p_b, 4);
  RETURN v_result;
EXCEPTION
  WHEN division_by_zero THEN
    RETURN NULL;
END;
$$;
sql
SELECT safe_divide(10, 4) AS ok;
text
   ok
--------
 2.5000
(1 row)
sql
SELECT safe_divide(10, 0) AS zero;
text
ERROR:  除数不能为零

注意 EXCEPTION 子句天然形成一个子事务：块内回滚不影响外层事务。这意味着异常处理有真实代价——每次进入带 EXCEPTION 的块都要建立子事务。不要把 EXCEPTION 当 try/catch 随手包住整个函数，只在确实需要容错处使用。

只读的 SELECT ... INTO 也要注意：

sql
CREATE FUNCTION lookup_amount(p_customer text)
RETURNS numeric
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  v_amount numeric;
BEGIN
  SELECT sum(amount) INTO v_amount FROM demo.orders WHERE customer = p_customer;
  RETURN COALESCE(v_amount, 0);
END;
$$;

SELECT ... INTO 在 plpgsql 里很常用，但它是把结果取到一个变量，和 CREATE TABLE ... AS 的 SELECT INTO 完全是两回事。

动态 SQL：EXECUTE 与 format() ​

需要拼接表名、列名、排序方向时，只有 plpgsql 能做：

sql
CREATE FUNCTION count_rows(p_table text)
RETURNS bigint
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  v_count bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', p_table) INTO v_count;
  RETURN v_count;
END;
$$;

format() 的占位符必须选对：

占位符用途等价函数
%I标识符（表名、列名、schema）quote_ident()
%L字面量（字符串、值）quote_literal()
%s直接转成字符串，不做转义拼字符串，仅用于已确认安全的片段
sql
-- 安全：表名走 %I，用户输入走参数
EXECUTE format('SELECT count(*) FROM %I WHERE status = $1', p_table)
  INTO v_count USING p_status;
sql
-- 危险：%s 直接拼接用户输入 —— SQL 注入
EXECUTE 'SELECT count(*) FROM orders WHERE customer = ' || quote_literal(p_customer)
  INTO v_count;                                          -- 若手写拼接易错
EXECUTE format('SELECT count(*) FROM orders WHERE customer = %s', p_customer)
  INTO v_count;                                          -- 明确错误用法

只要值可以当参数传，就一律用 USING，不拼字符串。动态 SQL 是 plpgsql 的安全薄弱点，凡是接受外部输入的动态语句都必须走 %I / %L / USING。

RETURN QUERY vs 直接 SELECT ​

plpgsql 里返回行集必须显式 RETURN QUERY：

sql
CREATE FUNCTION paid_orders(p_customer text)
RETURNS TABLE(id bigint, amount numeric)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT o.id, o.amount
  FROM demo.orders AS o
  WHERE o.customer = p_customer
    AND o.status = 'paid'
  ORDER BY o.amount DESC;
END;
$$;

RETURN QUERY 后不需要再写 RETURN；如果需要在同一函数里追加结果，可以多次 RETURN QUERY，或改用 RETURN NEXT。

对比 sql 版的写法——没有任何 RETURN，最后一条 SELECT 就是返回值：

sql
CREATE FUNCTION paid_orders_sql(p_customer text)
RETURNS TABLE(id bigint, amount numeric)
LANGUAGE sql STABLE
AS $$
  SELECT o.id, o.amount
  FROM demo.orders AS o
  WHERE o.customer = p_customer
    AND o.status = 'paid'
  ORDER BY o.amount DESC;
$$;

两者功能等价，但 sql 版可被内联，调用方的额外条件有机会下推进函数体；plpgsql 版永远是一堵墙。

性能对比 ​

同一逻辑，两种语言：

sql
CREATE FUNCTION is_even_sql(i bigint) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS $$ SELECT i % 2 = 0 $$;

CREATE FUNCTION is_even_pg(i bigint) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  RETURN i % 2 = 0;
END;
$$;
sql
\timing on
SELECT count(*) FROM generate_series(1, 1000000) AS g WHERE is_even_sql(g);
SELECT count(*) FROM generate_series(1, 1000000) AS g WHERE is_even_pg(g);
text
 count
--------
 500000
(1 row)

Time: 210.418 ms
 count
--------
 500000
(1 row)

Time: 1084.762 ms

数字仅供感受量级

上表的具体毫秒数强依赖硬件与 PostgreSQL 版本，不同机器上可能相差数倍；要关注的是两者的比例关系——简单标量函数上，plpgsql 通常是 sql 版的数倍到十余倍。

但结论不是「永远用 sql」。同一批数据做逐行累加：

sql
CREATE FUNCTION sum_evens_sql(p_n integer) RETURNS bigint
LANGUAGE sql IMMUTABLE
AS $$
  SELECT COALESCE(sum(g), 0) FROM generate_series(2, p_n, 2) AS g;
$$;

CREATE FUNCTION sum_evens_pg(p_n integer) RETURNS bigint
LANGUAGE plpgsql IMMUTABLE
AS $$
DECLARE
  v_total bigint := 0;
  g       integer;
BEGIN
  FOR g IN 2..p_n BY 2 LOOP
    v_total := v_total + g;
  END LOOP;
  RETURN v_total;
END;
$$;

这里 sql 版把整个计算交给一条聚合查询，plpgsql 版是 n 次循环加赋值——循环次数一上来，plpgsql 的逐次开销就会反超。选择语言要看「能不能表达成一条查询」，而不是盲目追求内联。

选择判据 ​

text
能不能写成一条 SELECT？
├─ 能 ──> 用 LANGUAGE sql
│          （还会自动获得内联机会）
└─ 不能
    ├─ 需要 IF / LOOP / 变量 ──> LANGUAGE plpgsql
    ├─ 需要 EXCEPTION 容错 ──> LANGUAGE plpgsql
    ├─ 需要动态 SQL ──────────> LANGUAGE plpgsql
    └─ 需要 RAISE 日志 ───────> LANGUAGE plpgsql

再补一条经验判据：中间结果需要「记住」时才用 plpgsql。如果整个逻辑能表达成一个查询（哪怕带 CTE、窗口函数、LATERAL），就用 sql。

场景推荐理由
单位换算、金额含税sql纯表达式，可常量折叠
字符串整形、拼接sql单表达式
「查一个聚合值」sql可内联，条件可下推
参数化视图（带参数的查询）sql表函数内联优于视图
「写入并返回 id」sqlRETURNING 一步到位
多步写入 + 校验 + 回滚语义plpgsql需要变量与分支
逐行处理 + 累加状态plpgsql需要循环与变量
按参数动态选表/列plpgsql需要 EXECUTE
容错：失败时降级返回默认值plpgsql需要 EXCEPTION
输出调试信息plpgsql需要 RAISE

SQL 标准函数体（BEGIN ATOMIC） ​

PostgreSQL 14 起支持 SQL 标准的函数体写法，不用美元引用包裹字符串：

sql
CREATE FUNCTION add_two(a integer, b integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
RETURN a + b;
sql
CREATE FUNCTION classify(p_amount numeric)
RETURNS text
LANGUAGE sql
IMMUTABLE
BEGIN ATOMIC
  SELECT CASE
           WHEN p_amount >= 1000 THEN 'large'
           WHEN p_amount >= 100  THEN 'medium'
           ELSE 'small'
         END;
END;
text
CREATE FUNCTION

它的特点：

  • 函数体在创建时就被完整解析，语法错误立即暴露，不像 AS $$ ... $$ 那样可能延迟到运行时；
  • LANGUAGE 可以省略，此时默认为 sql；
  • 语法上是标准 SQL，可移植性更好，但限制了可写的语句种类。

两种写法的取舍

AS $$ ... $$ 兼容所有版本、写法灵活、便于脚本生成；BEGIN ATOMIC 错误暴露更早、更标准。存量项目大多沿用 AS $$ ... $$，新项目可以按团队习惯二选一，不要在同一套函数里混用两种风格。

在 pg_proc 里区分 ​

sql
SELECT p.proname,
       l.lanname,
       p.provolatile,
       p.prosecdef
FROM pg_proc AS p
JOIN pg_language AS l ON l.oid = p.prolang
WHERE p.proname IN ('is_even_sql', 'is_even_pg', 'f_identity', 'f_identity_pg')
ORDER BY p.proname;
text
   proname    | lanname | provolatile | prosecdef
--------------+---------+-------------+-----------
 f_identity   | sql     | i           | f
 f_identity_pg| plpgsql | i           | f
 is_even_pg   | plpgsql | i           | f
 is_even_sql  | sql     | i           | f
(4 rows)

provolatile 是单字符编码：

值含义
iIMMUTABLE
sSTABLE
vVOLATILE（默认）

想统计库里有多少 plpgsql 函数、其中多少还是默认的 VOLATILE：

sql
SELECT l.lanname,
       p.provolatile,
       count(*) AS funcs
FROM pg_proc AS p
JOIN pg_language AS l ON l.oid = p.prolang
WHERE p.pronamespace = 'public'::regnamespace
GROUP BY 1, 2
ORDER BY 1, 2;
text
 lanname | provolatile | funcs
---------+-------------+-------
 plpgsql | v           |    37
 sql     | i           |    12
 sql     | s           |     5
(3 rows)

睁着一堆 plpgsql + v（未声明易变性）的函数，往往就是性能与优化机会的流失点。

实战示例 ​

下面用一个完整流程演示两种语言的实际差异。

建表与造数据 ​

sql
CREATE SCHEMA IF NOT EXISTS demo;
SET search_path = demo, public;

CREATE TABLE demo.orders (
  id          bigserial PRIMARY KEY,
  customer    text           NOT NULL,
  amount      numeric(12, 2) NOT NULL,
  status      text           NOT NULL DEFAULT 'pending',
  created_at  timestamptz    NOT NULL DEFAULT now()
);

INSERT INTO demo.orders (customer, amount, status)
SELECT
  'cust_' || (g % 50),
  round((random() * 500)::numeric, 2),
  (ARRAY['pending', 'paid', 'cancelled'])[1 + (g % 3)]
FROM generate_series(1, 2000) AS g;

ANALYZE demo.orders;
text
CREATE TABLE
INSERT 0 2000
ANALYZE

同一需求，两种写法：税后金额 ​

sql
CREATE FUNCTION demo.with_tax_sql(p_amount numeric, p_rate numeric DEFAULT 0.06)
RETURNS numeric
LANGUAGE sql IMMUTABLE
AS $$ SELECT round(p_amount * (1 + p_rate), 2) $$;

CREATE FUNCTION demo.with_tax_pg(p_amount numeric, p_rate numeric DEFAULT 0.06)
RETURNS numeric
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  RETURN round(p_amount * (1 + p_rate), 2);
END;
$$;
sql
SELECT demo.with_tax_sql(100) AS sql_v, demo.with_tax_pg(100) AS pg_v;
text
 sql_v  |  pg_v
--------+--------
 106.00 | 106.00
(1 row)

用 EXPLAIN 观察内联差异 ​

sql
EXPLAIN (COSTS OFF)
SELECT * FROM demo.orders WHERE amount > demo.with_tax_sql(500.00);
text
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on orders
   Filter: (amount > 530.00)
(2 rows)

常量折叠发生：with_tax_sql(500.00) → 530.00。

sql
EXPLAIN (COSTS OFF)
SELECT * FROM demo.orders WHERE amount > demo.with_tax_pg(500.00);
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on orders
   Filter: (amount > demo.with_tax_pg(500.00::numeric, 0.06))
(2 rows)

plpgsql 版调用被原样保留。注意这里两者都是 Seq Scan，因为 amount 上没有索引；若存在 amount 上的表达式索引或范围索引，内联与否就会带来计划差异。

给 amount 加索引再看一次 ​

sql
CREATE INDEX idx_orders_amount ON demo.orders (amount);
ANALYZE demo.orders;

EXPLAIN (COSTS OFF)
SELECT * FROM demo.orders WHERE amount > demo.with_tax_sql(500.00);
text
                     QUERY PLAN
-----------------------------------------------------
 Index Scan using idx_orders_amount on orders
   Index Cond: (amount > 530.00)
(2 rows)
sql
EXPLAIN (COSTS OFF)
SELECT * FROM demo.orders WHERE amount > demo.with_tax_pg(490.00);
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on orders
   Filter: (amount > demo.with_tax_pg(490.00::numeric, 0.06))
(2 rows)

同样的查询形状，一个走索引、一个全表扫。这就是「句式相同的两段代码，性能差一个量级」的完整成因。

破坏内联的几种写法 ​

sql
-- 1) 带 SET 子句 → 不内联
CREATE FUNCTION demo.wt_set(p_amount numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE
SET search_path = demo, public
AS $$ SELECT round(p_amount * 1.06, 2) $$;

-- 2) 声明 SECURITY DEFINER → 不内联
CREATE FUNCTION demo.wt_definer(p_amount numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE SECURITY DEFINER
AS $$ SELECT round(p_amount * 1.06, 2) $$;
sql
EXPLAIN (COSTS OFF)
SELECT * FROM demo.orders WHERE amount > demo.wt_set(500.00);
text
                            QUERY PLAN
--------------------------------------------------------------------
 Seq Scan on orders
   Filter: (amount > demo.wt_set(500.00::numeric))
(2 rows)
sql
EXPLAIN (COSTS OFF)
SELECT * FROM demo.orders WHERE amount > demo.wt_definer(500.00);
text
                                QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on orders
   Filter: (amount > demo.wt_definer(500.00::numeric))
(2 rows)

两个都没能常量折叠。如果你既想要 SECURITY DEFINER 又想要内联，只能把逻辑拆开：内层用普通 sql 函数（可内联），外层用 SECURITY DEFINER 薄薄地包一层。

逐行处理：plpgsql 的领地 ​

按状态汇总并同时输出每档占比——需要中间变量，只能 plpgsql：

sql
CREATE FUNCTION demo.status_share()
RETURNS TABLE(status text, cnt bigint, pct numeric)
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  v_total bigint;
BEGIN
  SELECT count(*) INTO v_total FROM demo.orders;

  IF v_total = 0 THEN
    RETURN;
  END IF;

  RETURN QUERY
  SELECT o.status,
         count(*),
         round(100.0 * count(*) / v_total, 2)
  FROM demo.orders AS o
  GROUP BY o.status
  ORDER BY count(*) DESC;
END;
$$;
sql
SELECT * FROM demo.status_share();
text
  status   | cnt  |  pct
-----------+------+-------
 paid      |  667 | 33.35
 cancelled |  667 | 33.35
 pending   |  666 | 33.30
(3 rows)

这里 IF v_total = 0 THEN RETURN; 是纯粹的防御分支——sql 没有 IF，也就写不出这种「提前退出」。反过来说，如果去掉这个分支，整段逻辑可以改写成一条带窗口函数的 SELECT，那就应该回到 sql：

sql
CREATE FUNCTION demo.status_share_sql()
RETURNS TABLE(status text, cnt bigint, pct numeric)
LANGUAGE sql STABLE
AS $$
  SELECT o.status,
         count(*),
         round(100.0 * count(*) / sum(count(*)) OVER (), 2)
  FROM demo.orders AS o
  GROUP BY o.status
  ORDER BY count(*) DESC;
$$;
sql
SELECT * FROM demo.status_share_sql();
text
  status   | cnt  |  pct
-----------+------+-------
 paid      |  667 | 33.35
 cancelled |  667 | 33.35
 pending   |  666 | 33.30
(3 rows)

能力允许时优先 sql：这次改写用一个窗口函数消灭了变量与分支。

动态 SQL 与标识符安全 ​

sql
CREATE FUNCTION demo.count_by_status(p_schema text, p_table text, p_status text)
RETURNS bigint
LANGUAGE plpgsql STABLE SECURITY INVOKER
AS $$
DECLARE
  v_count bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I.%I WHERE status = $1', p_schema, p_table)
    INTO v_count
    USING p_status;
  RETURN v_count;
END;
$$;
sql
SELECT demo.count_by_status('demo', 'orders', 'paid') AS paid_count;
text
 paid_count
------------
        667
(1 row)

用户传进来的表名走 %I（标识符转义），状态值走 USING（参数绑定），两层都堵死。若写成 %s 拼接，一个名为 orders; DROP TABLE x; -- 的「表名」就能造成破坏。

异常容错 ​

sql
CREATE FUNCTION demo.try_cast_int(p_input text)
RETURNS integer
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  RETURN p_input::integer;
EXCEPTION
  WHEN invalid_text_representation THEN
    RETURN NULL;
END;
$$;
sql
SELECT demo.try_cast_int('42') AS ok, demo.try_cast_int('abc') AS bad;
text
 ok | bad
----+-----
 42 |
(1 row)

同样的功能在 PostgreSQL 内置函数里已有等价物（pg_input_is_valid() 配 ::integer），能用内置就别自建——内置函数是 C 实现且更了解优化器。

清理 ​

sql
DROP FUNCTION demo.status_share();
DROP FUNCTION demo.status_share_sql();
DROP FUNCTION demo.count_by_status(text, text, text);
DROP FUNCTION demo.try_cast_int(text);
DROP FUNCTION demo.with_tax_sql(numeric, numeric);
DROP FUNCTION demo.with_tax_pg(numeric, numeric);
DROP FUNCTION demo.wt_set(numeric);
DROP FUNCTION demo.wt_definer(numeric);
DROP SCHEMA demo CASCADE;

注意事项 ​

  • plpgsql 永远不能被内联。这不是版本限制或配置开关，而是设计决定：优化器无法看见它的内部，因此无法做条件折叠与下推。凡是「被误写进 plpgsql 的简单表达式」，都会成为长期的性能税。

    sql
    -- 一个常见的反模式：查询封装成 plpgsql
CREATE FUNCTION bad_lookup(p_id bigint) RETURNS text
LANGUAGE plpgsql STABLE
AS $$
DECLARE v text;
BEGIN
  SELECT payload INTO v FROM t_inline WHERE id = p_id;
  RETURN v;
END;
$$;

    改成 LANGUAGE sql + 直接 SELECT 即可，简单且更快。

  • sql 函数的返回值是「最后一条语句」。若中间写了多条语句，只有最后的 SELECT / RETURNING 生效；最后一条不是 SELECT 就报错（除非返回 void）。

  • sql 函数体内不能有变量，别指望用 AS $$ SELECT $1 AS x, x + 1 $$ 这种方式复用中间结果。

  • EXCEPTION 子句有真实代价。它隐式开启子事务，每次进入都要保存与恢复状态。把整个函数体包在 EXCEPTION 里是常见的性能陷阱。

  • RAISE EXCEPTION 与 RAISE NOTICE 语义不同：前者中止执行（默认 ERRCODE 为 P0001），后者只输出信息。生产代码里给业务异常显式指定 ERRCODE：

    sql
    RAISE EXCEPTION '余额不足：当前 %，需要 %', v_balance, p_amount
  USING ERRCODE = '23514';

  • plpgsql 的语法错误可能延迟到运行时。创建时不报错不等于没问题，新建后必须实际调用一次。想提前暴露可在 DO 块或测试用例中覆盖各分支。

  • 动态 SQL 拼接字符串是头号注入面。表名、列名用 %I，值用 USING 或 %L，绝不用 %s 拼用户输入。

  • RETURN QUERY 与 RETURN NEXT 不能混用于同一结果集之外的语义。RETURN QUERY 追加一次查询的全部结果，RETURN NEXT 追加一行；两者可以在同一函数中先后使用，但顺序就是输出顺序。

  • plpgsql 的 SELECT ... INTO 没找到行时把变量置为 NULL，不会报错。必须自己用 IF NOT FOUND THEN 或 COALESCE 处理。

  • sql 函数里的函数调用不会「自动」内联。内联条件很苛刻，判断方法只有一个：看 EXPLAIN 里函数调用是否消失。

  • 别用函数替代视图。若只是给一段固定查询起个名字、不需要参数，用 CREATE VIEW 而不是函数——视图没有任何调用开销。

最佳实践 ​

  • 默认选 LANGUAGE sql。只有当逻辑确实写不成一条查询时，才升级到 plpgsql。这是本节最重要的一条。

  • 用 EXPLAIN 验证内联，而不是凭感觉。写完 sql 函数后跑一次 EXPLAIN (COSTS OFF)，确认函数调用是否已在计划里消失：

    sql
    EXPLAIN (COSTS OFF) SELECT * FROM demo.orders WHERE amount > demo.with_tax_sql(500.00);

  • 改写成一条查询的通用手段：CASE 替 IF，generate_series 替循环，窗口函数替累加变量，COALESCE / NULLIF 替空值分支，LATERAL 替嵌套过程。

  • 需要「提前退出」时，先想想能不能用 WHERE 表达。IF v_total = 0 THEN RETURN; 常可等价为查询里的 WHERE 条件。

  • 需要 SECURITY DEFINER 时拆成两层：内层普通 sql 函数负责可内联的计算，外层 SECURITY DEFINER 只做权限边界与最小逻辑。

  • 避免在函数定义里写 SET 子句来「顺手固定环境」，它会直接关闭内联。若确实需要固定 search_path（SECURITY DEFINER 必须），接受放弃内联的代价。

  • plpgsql 里把能合并的语句合并。一次 SELECT ... INTO 取回多个值，胜过多次单值查询：

    sql
    SELECT count(*), COALESCE(sum(amount), 0)
  INTO v_cnt, v_sum
FROM demo.orders WHERE status = p_status;

  • 避免 plpgsql 里逐行 UPDATE。用集合式 UPDATE ... FROM 一条搞定，减少 SPI 往返。

  • 给所有函数显式声明易变性。内联判断、索引使用、常量折叠都依赖它，详见 IMMUTABLE / STABLE / VOLATILE。

  • EXCEPTION 只用在真正需要容错的边界，不要用「包住整个函数防崩」的写法。

  • 动态 SQL 统一走 format() + USING，并把它当作代码审查的必查项写入规范。

  • 给 plpgsql 写测试用例，覆盖每个 IF 分支与 EXCEPTION 分支；扩展 plpgsql_check 可以在测试阶段做静态检查。

  • 函数体保持短小。一个函数只做一件事；长过程应拆成若干个能独立测试的 sql 函数。

下一节

继续学习：函数重载与可变参数

参考链接 ​