PHP小码农

Appearance

执行动态 SQL（EXECUTE） ​

前面几节写的 SQL 都是「写死在函数体里」的静态语句：表名、列名、条件结构在编译时就确定，只有数据值会随变量变化。但有些场景做不到——比如「按传入的表名统计行数」「按传入的列名排序」「每月创建一张分区表」。这些语句的结构本身在运行时才确定，必须把 SQL 当字符串拼装出来再执行。PL/pgSQL 为此提供了 EXECUTE 语句。

本节讲清三件事：什么时候必须用 EXECUTE、USING 与 $1 占位符如何避免 SQL 注入、format() 与 quote_ident() 如何安全地拼接标识符。动态 SQL 是 PL/pgSQL 里最容易写出安全漏洞的地方，本节会反复对照「错误写法」与「推荐写法」。

前置知识

阅读本节前，建议先了解：记录类型（RECORD / ROWTYPE）

基础概念 ​

静态 SQL 与动态 SQL ​

PL/pgSQL 里的 SQL 语句分成两类：

类别特征表名/列名数据值计划缓存
静态 SQL语句文本固定写在函数体里编译时确定由变量自动替换为参数会缓存
动态 SQL语句文本由运行时拼装运行时确定用 USING 传参不缓存，每次重新规划

静态 SQL 的「变量自动替换」是 PL/pgSQL 最方便的特性：

sql
-- 静态 SQL：v_dept 会被自动当成查询参数，不需要手动拼字符串
SELECT count(*) INTO v_cnt FROM demo_staff WHERE dept = v_dept;

PL/pgSQL 会把语句里出现的变量名替换成查询参数（$1），再把变量的当前值作为参数值传入。这既避免了转义问题，也让执行计划可以被缓存复用。

但这个机制只能替换数据值，不能替换表名、列名、关键字。下面这些需求静态 SQL 全都做不到：

  • 表名来自参数：SELECT count(*) FROM <某张表>
  • 列名来自参数：ORDER BY <某个列> DESC
  • 语句结构变化：WHERE 子句拼几个条件、拼几个 OR
  • 执行 DDL/工具语句：CREATE TABLE part_2026_09 PARTITION OF ...
  • 需要对语句重新规划：希望每次都按当前参数值生成计划

这些场景统一用 EXECUTE。

数据值不是标识符：两类东西必须分开处理 ​

动态 SQL 安全问题的根源，是很多人把「数据值」和「标识符」当成同一类东西来拼接。它们完全不同：

对象举例正确传递方式错误方式
数据值'张三'、42、'2026-09-28'USING 传入，语句里写 $1直接字符串拼接
标识符表名 demo_staff、列名 deptformat('%I', ...) 或 quote_ident()$1 占位符（无效）
字面量文本要在 SQL 文本里出现的一个字符串常量format('%L', ...) 或 quote_literal()手工加引号

三条铁律：

  • USING 的 $1、$2 只能承载数据值，不能承载表名/列名。写 EXECUTE 'SELECT * FROM $1' USING tab 一定失败。
  • 标识符必须经过 quote_ident() 或 format('%I', ...)，否则遇到含大写、空格、特殊字符的名字会出错，遇到恶意名字会成为注入点。
  • 千万不要用 || 把用户输入直接拼进 command-string。这是 PL/pgSQL 中最典型的 SQL 注入来源。
text
动态 SQL 的两次「翻译」
────────────────────────────────────────────────────────────────
你想要执行的语句（人的想法）
   SELECT dept, count(*) FROM demo_staff GROUP BY dept
        │
        ├── 结构部分（表名/列名/关键字）──► 必须由代码拼进字符串
        │                                    format('%I', 'demo_staff')
        │
        └── 数据部分（值）──────────────► 交给 USING，语句里用 $1
                                             USING '研发'
────────────────────────────────────────────────────────────────
最终 command-string： SELECT dept, count(*) FROM demo_staff WHERE dept = $1
运行时绑定参数：      $1 = '研发'

语法与用法 ​

EXECUTE 的语法原型：

text
EXECUTE command-string
        [ INTO [STRICT] target ]
        [ USING expression [, ... ] ];

-- 各部分含义
-- command-string : 求值得到 text 的表达式（通常是字符串字面量或 format() 的结果）
-- INTO target    : 可选。接收结果的行变量/记录变量/变量列表
-- STRICT         : 可选。要求结果"恰好一行"，否则报错
-- USING expr     : 可选。要绑定的参数值，在语句里用 $1、$2 ... 引用

最小可运行示例——按传入的表名统计行数：

sql
CREATE TABLE IF NOT EXISTS demo_staff(
  id     integer PRIMARY KEY,
  name   text    NOT NULL,
  dept   text    NOT NULL,
  salary numeric(10,2) NOT NULL DEFAULT 0,
  active boolean NOT NULL DEFAULT true
);

INSERT INTO demo_staff(id, name, dept, salary, active)
VALUES (1, '张三', '研发', 15000, true),
       (2, '李四', '销售', 12000, true),
       (3, '王五', '研发', 18000, true)
ON CONFLICT (id) DO NOTHING;

CREATE OR REPLACE FUNCTION app_count_rows(p_table text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_cnt bigint;
BEGIN
  -- 表名是标识符：用 %I 自动加引号
  EXECUTE format('SELECT count(*) FROM %I', p_table) INTO v_cnt;
  RETURN v_cnt;
END;
$$;

SELECT app_count_rows('demo_staff') AS total;
-- total: 3

如果这句写成 EXECUTE 'SELECT count(*) FROM ' || p_table，功能上大部分时候也能跑，但只要 p_table 里含有大写字母、空格或引号就会出错；如果 p_table 来自用户输入，那就是一个注入点。format('%I', ...) 是必须的。

详细说明 ​

静态 SQL 的变量替换：先看清「免费」的能力 ​

在学 EXECUTE 之前，先把静态 SQL 能做的事用足——它能做的时候不要用动态 SQL。

sql
CREATE OR REPLACE FUNCTION app_static_demo(p_dept text, p_min numeric)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_cnt bigint;
BEGIN
  -- 数据值全部由变量自动替换，不需要任何转义
  SELECT count(*) INTO v_cnt
    FROM demo_staff
   WHERE dept = p_dept
     AND salary >= p_min
     AND active;

  RETURN v_cnt;
END;
$$;

SELECT app_static_demo('研发', 10000) AS cnt;   -- cnt: 2

三点要记住：

  • 替换发生在优化类命令里：SELECT、INSERT、UPDATE、DELETE、MERGE，以及内嵌它们的部分工具命令（如 EXPLAIN、CREATE TABLE ... AS SELECT）。
  • 非优化类命令（工具语句）不支持参数：CREATE TABLE、ALTER TABLE、VACUUM 等语句不能接收查询参数，因此变量自动替换对它们无效——想在 CREATE TABLE 里带一个名字，只能用 EXECUTE 拼字符串。
  • 名称解析的优先级由 plpgsql.variable_conflict 控制（默认 error）。变量名与列名同名时默认报错，详见变量声明与赋值。

EXECUTE 语法逐项拆解 ​

command-string：任何求值为 text 的表达式。实践中两种写法：

sql
-- 写法一：常量字符串（无任何动态成分时）
EXECUTE 'ANALYZE demo_staff';

-- 写法二：format() 拼装（有动态成分时，推荐）
EXECUTE format('ANALYZE %I', p_table);

关于变量替换的一个重要区别：静态 SQL 会自动替换变量，而 EXECUTE 的 command-string 不做任何变量替换。下面这段是错的：

sql
-- 错误！EXECUTE 的字符串里写 v_table，会被当成字面量，而不是变量值
EXECUTE 'SELECT count(*) FROM v_table' INTO v_cnt;
-- 报错：relation "v_table" does not exist  (SQLSTATE 42P01)

command-string 里需要嵌什么，就必须自己拼进去；数据值则交给 USING。

INTO：接收结果。规则与静态 SQL 的 INTO 一致：

  • 行变量或变量列表作目标 → 结果的列数、类型必须与目标完全匹配；
  • 记录变量（record）作目标 → 自动适配结果结构；
  • 返回多行时只取第一行；返回零行时目标被赋为 NULL；
  • 没写 INTO → 结果被丢弃。

USING：绑定参数。值是表达式，可以引用函数的参数和局部变量。表达式在进入 EXECUTE 之前就求值完毕，因此不存在「值里含引号导致语法错」的问题。

USING 参数与 $1 占位符 ​

USING 里的表达式按出现顺序对应 $1、$2、$3……同一个占位符可以出现多次。

sql
CREATE OR REPLACE FUNCTION demo_using_demo(p_dept text, p_min numeric)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_cnt bigint;
BEGIN
  EXECUTE
    'SELECT count(*) FROM demo_staff WHERE dept = $1 AND salary >= $2'
    INTO v_cnt
    USING p_dept, p_min;

  RETURN v_cnt;
END;
$$;

SELECT demo_using_demo('研发', 15000) AS cnt;    -- cnt: 2

为什么 USING 优于手工拼接？官方给出的两条理由值得逐字理解：

  • 避免把值转成文本再转回来的运行时开销。USING 传的是原生类型值，SQL 引擎直接拿到 numeric，不需要「先格式化成正文字符串，再解析回 numeric」。
  • 几乎不可能被 SQL 注入。因为根本不需要引用或转义——值不参与语法解析。

USING 的两条限制必须注意：

  • $1 这类占位符只能用在优化类命令里。工具语句（CREATE TABLE、ALTER TABLE、VACUUM 等）即使只是要放一个数据值，也必须拼接文本。
  • 占位符不能表示表名、列名。这类「结构成分」只能拼进字符串。
sql
-- 错误！工具语句不接受参数占位符
EXECUTE 'CREATE TABLE $1 (id integer)' USING p_table;
-- 报错：syntax error at or near "$1"  (SQLSTATE 42601)

-- 推荐：表名用 %I 拼进字符串
EXECUTE format('CREATE TABLE %I (id integer)', p_table);

数据值 vs 标识符：SQL 注入的根因 ​

用一段对照代码把注入看清。假设有个函数按部门名过滤员工，部门名来自调用方（可能是用户输入）。

sql
CREATE OR REPLACE FUNCTION app_unsafe(p_dept text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_cnt bigint;
BEGIN
  -- 错误！直接把值拼进 SQL 文本，用户输入会变成 SQL 语法的一部分
  EXECUTE 'SELECT count(*) FROM demo_staff WHERE dept = ''' || p_dept || ''''
    INTO v_cnt;
  RETURN v_cnt;
END;
$$;

正常输入 研发 时，拼出的语句是：

text
SELECT count(*) FROM demo_staff WHERE dept = '研发'

但如果调用方传入的是：

text
x' OR '1'='1

拼出的语句就变成：

text
SELECT count(*) FROM demo_staff WHERE dept = 'x' OR '1'='1'

条件恒真，查询范围被完全改变。如果换成 x'; DROP TABLE demo_staff; --，后果更严重（DDL 在 EXECUTE 里同样能执行）。

修复方式只有一个方向——值走 USING，名字走 %I：

sql
CREATE OR REPLACE FUNCTION app_safe(p_dept text)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_cnt bigint;
BEGIN
  -- 推荐：值作为参数传入，不参与语法解析
  EXECUTE 'SELECT count(*) FROM demo_staff WHERE dept = $1'
    INTO v_cnt
    USING p_dept;
  RETURN v_cnt;
END;
$$;

SELECT app_safe('研发')                    AS normal_input,   -- 2
       app_safe('x'' OR ''1''=''1')        AS attack_input;   -- 0

注意最后一个结果：注入字符串被当成一个普通的部门名去匹配，匹配不到任何行，返回 0。这正是我们想要的结果——恶意输入被老老实实当成数据。

format() 的 %I / %L / %s / 位置参数 ​

format() 是拼装动态 SQL 的主力函数，格式串里的说明符决定了参数被如何转义：

说明符作用NULL 参数的行为等价函数
%s当作普通字符串插入（不转义）当作空字符串无
%I当作 SQL 标识符，必要时加双引号报错（不允许 NULL）quote_ident()
%L当作 SQL 字面量，自动加单引号并转义输出字符串 NULL（不带引号）quote_nullable()
%%输出一个普通的 % 字符—无
sql
SELECT format('Hello %s', 'World');            -- Hello World
SELECT format('Hello %s', NULL);               -- Hello （NULL 变空串）
SELECT format('%I', 'Foo bar');                -- "Foo bar"
SELECT format('%I', 'demo_staff');             -- demo_staff （无需加引号时保持原样）
SELECT format('%L', 'O''Reilly');              -- 'O''Reilly'
SELECT format('%L', NULL);                     -- NULL （SQL 关键字，表示空值）
SELECT format('100%% done');                   -- 100% done

%I 遇到 NULL 会直接报错，这是刻意设计——标识符为空通常意味着上游逻辑出了问题，早失败好过拼出一条奇怪的语句：

sql
SELECT format('%I', NULL);
-- ERROR:  null values cannot be formatted as an SQL identifier
-- SQLSTATE: 22004 (null_value_not_allowed)

格式串支持位置说明符，形如 %n$，用于同一个参数多次引用或调整顺序：

sql
SELECT format('%1$s 和 %1$s', 'Alice');        -- Alice 和 Alice
SELECT format('%2$s / %1$s', 'first', 'second'); -- second / first
-- 位置说明符可与类型说明符组合
SELECT format('SELECT * FROM %1$I WHERE %2$I = $1', 'demo_staff', 'dept');
-- SELECT * FROM demo_staff WHERE dept = $1

格式串还支持宽度与对齐（%10s、%-10s），不过在 SQL 拼装里用得很少，日志排版时才偶尔用得上。

quote_ident() / quote_literal() / quote_nullable() ​

不借助 format() 时，可以用这三个函数手工完成同样的转义：

sql
SELECT quote_ident('Foo bar');       -- "Foo bar"
SELECT quote_ident('demo_staff');    -- demo_staff
SELECT quote_literal('O''Reilly');   -- 'O''Reilly'
SELECT quote_literal(42.5);          -- '42.5'
SELECT quote_literal(NULL);          -- NULL （SQL 空值，不是字符串）
SELECT quote_nullable(NULL);         -- NULL 字符串？不——返回 SQL NULL
SELECT quote_nullable('abc');        -- 'abc'

关键差别：

  • quote_literal(NULL) 返回 SQL NULL，一旦参与 || 拼接，整个字符串会变成 NULL，语句就废了。
  • quote_nullable(NULL) 返回字符串 NULL（不带引号的四个字母），拼进 SQL 后正好表示 SQL 空值，这是它存在的理由。

手工拼接的写法长这样（可用，但不如 format() 直观）：

sql
EXECUTE 'SELECT count(*) FROM ' || quote_ident(p_table)
     || ' WHERE dept = ' || quote_nullable(p_val)
  INTO v_cnt;

能用 format() 就别手工 ||：格式串一眼能看出哪些位置是标识符、哪些是字面量，可读性和可维护性都更好。官方文档也把 format() 的 %I 列为「更清晰的做法（a cleaner approach）」。

INTO 与 INTO STRICT ​

EXECUTE 的 INTO 与静态 SQL 的 INTO 行为一致，但 STRICT 的价值在动态场景里更突出——因为动态 SQL 的目标结构在写代码时看不见，多一行少一行很容易被忽略。

sql
CREATE OR REPLACE FUNCTION demo_exec_strict(p_id integer)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_row demo_staff%ROWTYPE;
BEGIN
  -- 普通 INTO：零行时字段全为 NULL，不报错
  EXECUTE 'SELECT * FROM demo_staff WHERE id = $1' INTO v_row USING p_id;
  IF NOT FOUND THEN
    RETURN format('普通 INTO：id=%s 无数据，字段全为 NULL', p_id);
  END IF;
  RETURN format('普通 INTO：%s 命中', v_row.name);
END;
$$;

CREATE OR REPLACE FUNCTION demo_exec_strict2(p_id integer)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_row demo_staff%ROWTYPE;
BEGIN
  -- STRICT：不是恰好一行就抛错（NO_DATA_FOUND / TOO_MANY_ROWS）
  EXECUTE 'SELECT * FROM demo_staff WHERE id = $1' INTO STRICT v_row USING p_id;
  RETURN format('STRICT：%s 命中', v_row.name);
END;
$$;

SELECT demo_exec_strict(1)   AS a,      -- 普通 INTO：张三 命中
       demo_exec_strict(999) AS b;      -- 普通 INTO：id=999 无数据，字段全为 NULL

SELECT demo_exec_strict2(999);
-- ERROR:  query returned no rows
-- SQLSTATE: P0002 (no_data_found)

STRICT 的两种失败模式对应两个 SQLSTATE：

情况错误SQLSTATE
零行NO_DATA_FOUNDP0002
多行TOO_MANY_ROWSP0003

STRICT 执行成功时，FOUND 一定为 true。如果开启 plpgsql.print_strict_params（可在函数体首行写 #print_strict_params on），报错的 DETAIL 里还会带上传给语句的参数值，排查时很方便：

text
ERROR:  query returned no rows
DETAIL:  parameters: $1 = '999'
CONTEXT:  PL/pgSQL function demo_strict_detail(integer) line 6 at EXECUTE

不缓存计划：EXECUTE 的执行开销 ​

官方文档对 EXECUTE 有一句非常关键的说明：

Also, there is no plan caching for commands executed via EXECUTE. Instead, the command is always planned each time the statement is run.

也就是说：通过 EXECUTE 执行的语句每次都要重新解析、重新规划。这带来一正一反两个后果。

正面：正因为不缓存计划，EXECUTE 可以执行每次结构都不同的语句，这是它存在的根本理由。另外，当「最优计划强依赖参数值」时，用 EXECUTE 可以确保不会选中一个对当前参数很差的通用计划（关于 plan_cache_mode 与通用/自定义计划的取舍，见 PL/pgSQL 概述）。

反面：每次规划都有 CPU 开销。把本来能用静态 SQL 表达的语句改写成 EXECUTE，会白白丢掉计划缓存：

sql
-- 不推荐：语句结构完全固定，没有任何动态成分，却用 EXECUTE
EXECUTE 'SELECT count(*) FROM demo_staff WHERE dept = $1' INTO v_cnt USING p_dept;

-- 推荐：结构固定就用静态 SQL，享受计划缓存
SELECT count(*) INTO v_cnt FROM demo_staff WHERE dept = p_dept;

两者功能等价，区别只在计划缓存的得失。判断标准是「语句结构是否真的随调用变化」，而不是「哪种写法看起来更灵活」。

如果确实需要动态执行一个结构固定、计划强依赖参数的语句，又希望每次都用当前参数重新规划，EXECUTE 是一种可用手段；另一种手段是让静态语句配合 SET plan_cache_mode = force_custom_plan，后者不涉及字符串拼装，更安全。

RETURN QUERY EXECUTE 与 RETURN QUERY ​

在返回集合的函数（RETURNS SETOF ... 或 RETURNS TABLE(...)）里，RETURN QUERY 把一条查询的结果当成返回值追加到结果集；RETURN QUERY EXECUTE 则是它的动态版本。两者的用法在 EXIT / CONTINUE / RETURN 里有完整讲解，此处先看动态版本的形态：

sql
CREATE OR REPLACE FUNCTION app_dyn_search(p_table text, p_col text, p_val text)
RETURNS SETOF demo_staff
LANGUAGE plpgsql
AS $$
BEGIN
  -- 动态版本：语句结构与 RETURN QUERY 相同，只是文本由 format() 拼出
  RETURN QUERY EXECUTE
    format('SELECT * FROM %I WHERE %I = $1 ORDER BY id', p_table, p_col)
    USING p_val;
END;
$$;

SELECT id, name, dept FROM app_dyn_search('demo_staff', 'dept', '研发');
--  id | name | dept
-- ----+------+------
--   1 | 张三 | 研发
--   3 | 王五 | 研发
-- (2 rows)

RETURN QUERY EXECUTE 也支持 USING，参数占位符规则与普通 EXECUTE 完全一致。

常见错误与 SQLSTATE ​

把动态 SQL 最容易撞上的错误集中成一张表：

报错信息（节选）SQLSTATE触发原因处理方式
syntax error at or near "$1"42601在工具语句里用了 $1 占位符工具语句只能拼文本；数据值也必须 format('%L', ...)
there is no parameter $142P02语句里有 $1，但 USING 没提供对应参数补全 USING 列表，或修正占位符编号
relation "v_table" does not exist42P01忘了拼表名，把变量名当成了字面量用 format('%I', ...) 拼表名
column "xxx" does not exist42703列名拼错，或该列在当前查询里不存在核对列名；必要时用系统目录校验
null values cannot be formatted as an SQL identifier22004format('%I', NULL)先用 IF ... IS NULL THEN RAISE ... 拦掉
query returned no rowsP0002INTO STRICT 零行改用普通 INTO 并判 FOUND，或在 EXCEPTION 里处理
query returned more than one rowP0003INTO STRICT 多行加 LIMIT 1，或改用 FOR 循环/RETURN QUERY
invalid input syntax for type integer22P02用 %L 把值拼成字面量后类型不匹配优先用 USING 传原生类型
structure of query does not match function result type42804INTO 目标与结果列不匹配让结果列表与目标结构一致
permission denied for table xxx42501动态访问的表没有权限检查角色权限，或改用 SECURITY DEFINER 并固定 search_path

42P02 值得单独记一下——它的报错文本是 there is no parameter $1，意思是「语句里引用了 $1，但执行时没有绑定这个参数」。动态拼装 SQL 时，占位符编号与 USING 列表按位置对应，插入一个参数就会让后面所有编号错位，这是最容易犯的错：

sql
-- 错误！语句里用了 $2，但 USING 只给了一个参数
EXECUTE 'SELECT count(*) FROM demo_staff WHERE dept = $2' INTO v_cnt USING p_dept;
-- ERROR:  there is no parameter $2
-- SQLSTATE: 42P02

-- 推荐：占位符从 $1 起连续编号，且与 USING 顺序严格对应
EXECUTE 'SELECT count(*) FROM demo_staff WHERE dept = $1' INTO v_cnt USING p_dept;

实战示例 ​

示例一：按表名与列名做动态聚合 ​

需求：写一个通用统计函数，传入表名、分组列名，返回「分组值 → 计数」的结果集。表名与列名都是标识符，必须用 %I。

sql
CREATE OR REPLACE FUNCTION app_group_count(p_table text, p_col text)
RETURNS TABLE(grp text, cnt bigint)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  -- 列名出现两次，都经 %I 转义；结果列名用 AS 固定下来
  RETURN QUERY EXECUTE
    format(
      'SELECT %1$I::text AS grp, count(*) AS cnt
         FROM %2$I
        GROUP BY %1$I
        ORDER BY cnt DESC, grp',
      p_col, p_table
    );
END;
$$;

SELECT * FROM app_group_count('demo_staff', 'dept');
--  grp  | cnt
-- ------+-----
--  研发 |   2
--  销售 |   1

注意 %1$I 的用法：同一个列名在 SELECT 列表和 GROUP BY 里都要出现，用位置说明符只需传一次参数，也避免了两次转义结果不一致的可能。

示例二：动态列名做更新并返回受影响行数 ​

需求：按传入的列名，把某列的值批量替换。列名动态、值动态，分别用 %I 和 $1。

sql
CREATE OR REPLACE FUNCTION app_update_col(
  p_id  integer,
  p_col text,
  p_val text
) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  v_rows integer;
BEGIN
  -- 先用系统目录校验列名，避免非法列名带来的 42703
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = current_schema()
       AND table_name   = 'demo_staff'
       AND column_name  = p_col
  ) THEN
    RAISE EXCEPTION '未知列名：%', p_col USING ERRCODE = '42703';
  END IF;

  EXECUTE format('UPDATE %I SET %I = $1 WHERE id = $2', 'demo_staff', p_col)
     USING p_val, p_id;

  GET DIAGNOSTICS v_rows = ROW_COUNT;
  RETURN v_rows;
END;
$$;

SELECT app_update_col(2, 'dept', '华东') AS updated;    -- updated: 1
SELECT app_update_col(2, 'no_such', 'x');
-- ERROR:  未知列名：no_such
-- SQLSTATE: 42703

两个要点：

  • 动态 SQL 里更新/删除后用 GET DIAGNOSTICS ... = ROW_COUNT 拿受影响行数，FOUND 对 EXECUTE 也可用（语句影响至少一行时为 true），但 ROW_COUNT 能拿到具体数量。
  • 列名白名单校验是纵深防御。即使用了 %I，也建议对「只允许改哪几列」做显式限制——%I 保证语法安全，白名单保证业务安全。

示例三：动态拼装查询条件 ​

需求：按可选参数拼装 WHERE 子句。这是动态 SQL 最经典也最容易写坏的场景。

一个不推荐的思路是「条件生效几个就拼几个占位符」，编号由参数个数算出来，再用 CASE 按参数个数分别调用 EXECUTE ... USING：

text
不推荐：占位符编号靠 array_length() 计算，再用 CASE 分支匹配不同的参数个数
  IF p_dept IS NOT NULL THEN v_sql := v_sql || ' AND dept = $' || (n + 1); ... END IF;
  CASE array_length(v_args, 1)
    WHEN 1 THEN RETURN QUERY EXECUTE v_sql USING p_dept;
    WHEN 2 THEN RETURN QUERY EXECUTE v_sql USING p_dept, p_min;
  END CASE;

问题在于：编号计算与 USING 列表必须手工保持同步，改动一处就要连带改多处，错位了就报 42P02。真实项目里有两条更好的路。

  • 固定参数个数，用 NULL 表示「该条件不生效」：
sql
CREATE OR REPLACE FUNCTION app_search2(
  p_dept text DEFAULT NULL,
  p_min  numeric DEFAULT NULL
) RETURNS SETOF demo_staff
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  -- 推荐：语句结构固定，条件用"参数为空则短路"的方式表达
  RETURN QUERY
    SELECT * FROM demo_staff
     WHERE (p_dept IS NULL OR dept = p_dept)
       AND (p_min  IS NULL OR salary >= p_min)
       AND active
     ORDER BY id;
END;
$$;

(p_dept IS NULL OR dept = p_dept) 这种「空值短路」写法的优点是完全不需要动态 SQL：语句结构固定、享受计划缓存、没有任何注入面。代价是某些参数组合下优化器可能选不到最优计划。绝大多数业务查询里，这个代价远小于动态拼装带来的复杂度与风险。

  • 确实需要动态拼装时，建议把「拼语句」和「传参数」分成两段清晰的代码，并把占位符编号写死而不是靠 array_length 计算：
sql
CREATE OR REPLACE FUNCTION app_search3(
  p_dept_enabled boolean,
  p_dept text,
  p_min  numeric
) RETURNS SETOF demo_staff
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  v_sql text;
BEGIN
  -- 推荐：占位符编号显式写死，便于人工核对
  IF p_dept_enabled THEN
    v_sql := 'SELECT * FROM demo_staff
               WHERE ($1 IS NULL OR dept = $1)
                 AND ($2 IS NULL OR salary >= $2)
                 AND active
               ORDER BY id';
  ELSE
    v_sql := 'SELECT * FROM demo_staff
               WHERE ($2 IS NULL OR salary >= $2)
                 AND active
               ORDER BY id';
  END IF;

  RETURN QUERY EXECUTE v_sql USING p_dept, p_min;
END;
$$;

USING p_dept, p_min 在两个分支里都提供了两个参数，因此 $1、$2 始终有值可用——多传的参数不会报错，少传的才会报 42P02。这是个实用技巧：让所有分支的 USING 列表保持一致，就能用同一套占位符编号。

示例四：动态创建分区（工具语句，必须拼文本） ​

需求：按月份自动创建分区表。CREATE TABLE ... PARTITION OF 是工具语句，不能使用 $1，一切都要靠 %I 拼。

sql
-- 分区父表：按月份做范围分区
CREATE TABLE IF NOT EXISTS demo_log(
  id   bigint GENERATED ALWAYS AS IDENTITY,
  ts   timestamptz NOT NULL,
  msg  text,
  PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

CREATE OR REPLACE FUNCTION app_ensure_partition(p_month text)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_part  text;
  v_start date;
  v_end   date;
  v_sql   text;
BEGIN
  -- 输入校验：月份必须是 YYYY-MM 形状，否则早失败
  IF p_month !~ '^\d{4}-\d{2}$' THEN
    RAISE EXCEPTION '非法月份格式：%', p_month USING ERRCODE = '22023';
  END IF;

  v_start := to_date(p_month || '-01', 'YYYY-MM-DD');
  v_end   := v_start + interval '1 month';
  v_part  := 'demo_log_' || to_char(v_start, 'YYYY_MM');

  -- 推荐：分区名由固定前缀 + 已校验的月份生成，%I 再加一层保险
  v_sql := format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF demo_log
       FOR VALUES FROM (%L) TO (%L)',
    v_part, v_start, v_end
  );

  EXECUTE v_sql;
  RETURN v_part;
END;
$$;

SELECT app_ensure_partition('2026-09') AS partition_name;
-- partition_name: demo_log_2026_09

这里 %L 用在日期字面量上，生成的是 '2026-09-01' 这样的字符串常量，而不是被当成标识符：

text
format('... FOR VALUES FROM (%L) TO (%L)', v_start, v_end)
  -> ... FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')

如果把 %L 写成 %I，会得到 "2026-09-01"，PartitionDatum 解析时会报错；如果写成 %s，会得到裸的 2026-09-01（被解释为减法表达式）。三种说明符各司其职，不能混用。

示例五：注入防护前后对照（同一需求，三种写法） ​

需求：给定部门名，返回该部门的员工姓名列表。分别用三种写法实现，观察安全性与可读性差异。

sql
-- 写法一：错误！直接拼接用户输入
CREATE OR REPLACE FUNCTION app_bad(p_dept text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT name FROM demo_staff WHERE dept = ''' || p_dept || ''' ORDER BY id';
END;
$$;

-- 写法二：可用但不推荐。手工 quote_literal，容易漏掉某个拼接点
CREATE OR REPLACE FUNCTION app_mediocre(p_dept text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT name FROM demo_staff WHERE dept = ' || quote_literal(p_dept)
    || ' ORDER BY id';
END;
$$;

-- 写法三：推荐。值走 USING，语句文本里只有占位符
CREATE OR REPLACE FUNCTION app_good(p_dept text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT name FROM demo_staff WHERE dept = $1 ORDER BY id'
    USING p_dept;
END;
$$;

SELECT array_agg(x) FROM app_good('研发') AS x;                       -- {张三,王五}
SELECT array_agg(x) FROM app_good('x'' OR ''1''=''1') AS x;          -- NULL（未命中任何行）

三种写法的对比：

写法注入风险类型处理可读性结论
字符串直接 ||高全部转成文本差禁止
quote_literal() 拼接低（正确使用的前提下）全部转成文本中可用但不推荐
USING + $1极低保留原生类型好推荐

using 版本还有一个隐含优势：p_dept 以原生 text 类型传给 SQL 引擎，如果函数参数是 numeric、timestamptz 等类型，完全不需要考虑「文本表示是否会被解析器正确理解」。

注意事项 ​

  • EXECUTE 的字符串里不做变量替换。写 EXECUTE 'SELECT ... WHERE x = v_val' 不会替换 v_val，需要拼进字符串（数据值应改用 USING）。

  • $1 只能承载数据值。表名、列名、关键字等结构成分必须用 %I 拼进字符串；写 FROM $1 一定报 42601。

  • 工具语句一律不能使用占位符。CREATE TABLE、ALTER TABLE、VACUUM、ANALYZE 等，即使只是放一个数据值也要拼文本，此时用 %L 或 quote_nullable()。注意 EXPLAIN <优化语句> 与 CREATE TABLE ... AS SELECT 这类「内含优化语句」的命令是例外，它们可以使用占位符。

  • 没有计划缓存。每次执行都要重新解析与规划。结构固定的语句应优先写成静态 SQL。

  • INTO 多行时只取第一行且无 ORDER BY 时「第一行」不确定。需要确定性就加 ORDER BY，或改用 RETURN QUERY 返回多行。

  • INTO STRICT 的两个错误码：零行 → P0002（NO_DATA_FOUND）；多行 → P0003（TOO_MANY_ROWS）。

  • 不要用 EXECUTE 执行 SELECT INTO。PL/pgSQL 的 EXECUTE 不支持 SELECT INTO 语句形式，应写普通 SELECT 并把 INTO 作为 EXECUTE 自身的一部分：

    sql
    -- 错误！执行器不接受 SELECT ... INTO 这种写法
EXECUTE 'SELECT * INTO v_row FROM demo_staff WHERE id = 1';

-- 推荐：INTO 写在 EXECUTE 语法里
EXECUTE 'SELECT * FROM demo_staff WHERE id = 1' INTO v_row;

  • record 变量最适合接动态结果。动态 SQL 的列集合在编写时不可知，record 能自动适配；用 %ROWTYPE 则要求结果列与表结构完全一致。

  • 动态 SQL 的权限检查以函数所有者的权限为基础（SECURITY DEFINER 时）。若函数是 SECURITY DEFINER，务必固定 search_path，否则动态语句可能被诱导执行到非预期对象上：

    sql
    CREATE OR REPLACE FUNCTION app_definer_demo(p_table text)
RETURNS bigint
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
DECLARE v_cnt bigint;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', p_table) INTO v_cnt;
  RETURN v_cnt;
END;
$$;

最佳实践 ​

  • 能静态就不动态。先用足静态 SQL 的变量替换能力，只有「结构必须变化」时才上 EXECUTE：

    sql
    -- 推荐：值变化用静态 SQL
SELECT count(*) INTO v_cnt FROM demo_staff WHERE dept = p_dept;

-- 只在表名/列名/结构变化时才用动态 SQL
EXECUTE format('SELECT count(*) FROM %I', p_table) INTO v_cnt;

  • 拼装一律走 format()，不用 || 手工拼。格式串本身就是一份「哪些位置是标识符、哪些是字面量」的文档：

    sql
    -- 推荐
EXECUTE format('UPDATE %I SET %I = $1 WHERE id = $2', p_table, p_col)
   USING p_val, p_id;

-- 不推荐：转义点分散，容易出现漏网之鱼
EXECUTE 'UPDATE ' || quote_ident(p_table) || ' SET ' || quote_ident(p_col)
     || ' = ' || quote_literal(p_val) || ' WHERE id = ' || p_id;

  • 数据值一律走 USING，不要用 %L 拼。%L 用在「必须出现字面量的工具语句」里，普通 DML 用 $n 更安全、更快：

    sql
    -- 推荐
EXECUTE 'SELECT count(*) FROM demo_staff WHERE dept = $1' INTO v_cnt USING p_dept;

-- 不推荐：把值变成文本再拼进 SQL
EXECUTE format('SELECT count(*) FROM demo_staff WHERE dept = %L', p_dept) INTO v_cnt;

  • 对动态的表名/列名加白名单。%I 只保证语法安全，不保证业务安全：

    sql
    -- 推荐：显式限定允许访问的表
IF p_table NOT IN ('demo_staff', 'demo_log') THEN
  RAISE EXCEPTION '不允许访问的表：%', p_table USING ERRCODE = '42501';
END IF;

  • 用 RAISE ... USING ERRCODE 抛出业务错误而不是靠拼接的 SQL 报错。看得懂的错误码比看不懂的解析错误更有价值。

  • 及时取回受影响行数。动态 DML 之后用 GET DIAGNOSTICS n = ROW_COUNT，或直接用 FOUND。不要假设「执行了就等于改了」。

  • 把动态语句记进日志以便排查。RAISE DEBUG '动态 SQL: %', v_sql; 配合 log_min_messages 调级，线上排障时能一眼看到实际执行的语句。

  • SECURITY DEFINER + 动态 SQL 必须固定 search_path，且优先用完全限定的对象名（schema.table）：

    sql
    -- 推荐：完全限定 + 固定 search_path，双重保险
EXECUTE format('SELECT count(*) FROM public.%I', p_table) INTO v_cnt;

下一节

下一节我们学习：IF / THEN / ELSIF / ELSE，掌握 PL/pgSQL 中最基础的分支控制结构，包括 ELSIF 的拼写与 NULL 条件的走向。

参考链接 ​