1. 先把 PERFORM、GET DIAGNOSTICS、RETURN 这东西的定位理清楚总有人说 PL/pgSQL 的存储过程不好写功能写大了以后到处都是 RAISE、IF、LOOP一旦涉及动态结果集和行数校验代码就像一盘散沙。我自己刚开始从普通 SQL 转到 PostgreSQL 存储过程时也在这几个关键字上栽过跟头PERFORM 到底和 SELECT 有什么区别GET DIAGNOSTICS 能拿到的信息是不是就一个行数RETURN 家族里那么多写法什么时候该用哪一种这些问题不搞明白写出来的存储过程往往能用但关键时刻容易出问题而且不好排查。这篇文章不打算讲基础语法而是直接瞄准这三个在高级场景里绕不开的关键字从使用场景、代码示例、报错排查到综合实战一起过一遍。适合已经把 PL/pgSQL 基本语法用熟、开始写中大型业务逻辑的开发者。如果你是刚接触存储过程建议先把 CREATE FUNCTION、变量声明、IF/LOOP 这些基础过一遍再回来看这篇文章会顺畅很多。1.1 PERFORM执行“查询”但不要“结果”PL/pgSQL 里直接写SELECT * FROM xxx跟在 SQL 客户端里执行不太一样。在函数内部如果SELECT后面没有INTO或RETURN QUERY等接收结果的配套语句PostgreSQL 会报错或者把结果集直接返回给调用方造成不可控的行为。PERFORM 就是为了解决“我只需要执行这条查询不需要把结果集返回出来”的场景而存在。它的语法格式和 SELECT 基本一致唯一不同的是结果会被丢弃。执行完之后最近一次查询是否返回了至少一行可以通过系统变量FOUND来判断。很多人把 PERFORM 理解为“无用的 SELECT”其实它的主要价值在于做存在性判断、调用副作用函数、获取行锁以及避免无意义的中间结果集污染最终输出。后面我会专门用一个小节展开它的高频用法。1.2 GET DIAGNOSTICS给代码开一份诊断报告存储过程里最难受的一件事是执行完一条 INSERT / UPDATE / DELETE 之后你根本不知道它到底影响了几行。尤其是在批量同步、归档数据、并发扣减这类场景里影响行数和预期不一致就是业务 bug 的前兆。GET DIAGNOSTICS 就是 PL/pgSQL 提供的“诊断信息提取器”。它可以读取最近一条 SQL 命令的执行信息最常见的取值项是ROW_COUNT也就是最近命令处理的行数。除此之外还能拿到 SQLSTATE、异常消息文本、异常上下文堆栈等。放在异常处理块里它几乎等价于给存储过程装了一个黑匣子能快速定位是哪一层函数、哪一个语句出了问题。后面我会讲清楚普通诊断和堆栈诊断的区别这个区别在嵌套函数里非常重要。1.3 RETURN返回值的总调度RETURN 在 PL/pgSQL 里并不是简单的一个“退出语句”。单独看它可能就是退出函数但组合上NEXT、QUERY、QUERY EXECUTE之后它就是构造结果集的三种完全不同的姿势。比如RETURN NEXT是每次追加一行适合在循环里逐步生成结果RETURN QUERY是把查询结果一次性追加到输出RETURN QUERY EXECUTE则是执行动态 SQL 并把结果追加进来。很多人在函数里想返回多行却只用一个RETURN SELECT结果发现只返回第一行然后整个函数就终止了。这就是没有把三种 RETURN 用法吃透导致的。还有 OUT 参数和 RETURNS TABLE 之间的选择也常常让初学者迷路。这部分我会在第 4 节详细拆开。2. PERFORM 高频用法与三个最容易踩的坑PERFORM 看着简单真正用的时候却有不少细节。我挑三个最常见的场景再附带对应的典型错误你看完基本能避开大部分坑。2.1 用 PERFORM 做存在性检查配合 FOUND假设你现在要判断某个用户是否存在同时不想把结果集暴露出去。早期我很喜欢写SELECT * FROM users WHERE id 100;在函数外面没问题但在函数内部这条语句如果没有INTOPostgreSQL 会报“query has no destination for result data”之类的错误。就算不报错一旦真的查到数据结果集也会被抛给客户端完全不是我们想要的。正确写法是用 PERFORMDO $$ DECLARE v_exist boolean : false; BEGIN PERFORM 1 FROM users WHERE id 100; IF FOUND THEN RAISE NOTICE 用户 100 存在; ELSE RAISE NOTICE 用户 100 不存在; END IF; END $$;这里PERFORM 1 FROM ...是 SELECT 1只要能返回一行FOUND就是真。我用1而不是*是为了尽量减少数据传输的成本虽然 PERFORM 本来就不返回结果但这种习惯会在 SELECT INTO 等场景里同样受益。2.2 PERFORM 拿锁、调函数避免结果集污染客户端PERFORM 经常被拿来执行“有副作用但不需要返回值”的语句比较典型的是行锁和咨询锁。比如你想在存储过程开头锁住某个活动行防止并发重复发放优惠券直接写PERFORM 1 FROM campaigns WHERE id 100 FOR UPDATE;这条语句会拿到该行的更新锁但不会把行内容返回给客户端。如果改成SELECT * FROM campaigns WHERE id 100 FOR UPDATE;那么这条查询的结果集同样会被存进内存或直接返回在一些驱动下还会造成资源浪费。再比如调用一些自定义函数函数本身做了资金校验但返回值你不关心也可以PERFORM check_user_balance(100);这时候你只需要 FOUND 来判断这个函数是否有返回行或者干脆不关注结果。2.3 动态 SQL 场景不要试图 PERFORM 字符串这是我最想提醒的一点。PERFORM 后面跟的是静态查询语句它的语法结构在编译阶段就被确定了。如果你想执行动态拼接出来的 SQL比如-- 错误示范 PERFORM format(SELECT 1 FROM users WHERE id %s, v_id);这种写法很多时候并不会报错但实际执行的是一个 SELECT 字符串常量而不是你想要的动态查询。结果是 FOUND 总是为真因为字符串常量本身就是一个单行结果。真正应该用的是EXECUTEEXECUTE format(SELECT 1 FROM users WHERE id %s, v_id); IF FOUND THEN RAISE NOTICE 存在; END IF;注意 EXECUTE 执行后同样会更新 FOUND所以“动态 SQL 里的 PERFORM”本质上就是 EXECUTE不是 PERFORM 多态。提示我见过不少开发者在 PERFORM 和 EXECUTE 之间反复切换改来改去最后甚至写出PERFORM EXECUTE(...)这种语法错误。记住一句话静态查询想丢弃结果用 PERFORM动态查询想丢弃结果用 EXECUTE。3. GET DIAGNOSTICS行数、OID 与异常上下文一网打尽GET DIAGNOSTICS 能拿到的信息比大多数人想象的丰富。除了最常用的ROW_COUNT还有RESULT_OID、RETURNED_SQLSTATE、PG_CONTEXT、PG_EXCEPTION_DETAIL、PG_EXCEPTION_HINT等。掌握它等于给存储过程加上了肉眼可见的执行反馈。3.1 最常用的赋值项先看基本语法GET DIAGNOSTICS variable item [, variable item ...];常用的 item 项我用一个表格列出来诊断项类型说明ROW_COUNTbigint最近一条 SQL 命令处理的行数UPDATE/DELETE/INSERT 时最常用RESULT_OIDoid最近一次 INSERT 生成的行 OID仅在表和命令支持时有效RETURNED_SQLSTATEtext最近执行的错误码常用于异常分类PG_CONTEXTtext当前执行位置的调用上下文适合嵌套函数调试PG_EXCEPTION_DETAILtext异常详细信息只能在异常块内使用PG_EXCEPTION_HINTtext异常提示信息只能在异常块内使用PG_EXCEPTION_CONTEXTtext异常发生时的调用栈信息只能在异常块内使用你可能会问RESULT_OID 看起来挺古老现在还有用吗实际上PostgreSQL 默认表通常没有 OID所以这个诊断项更多是兼容历史遗留系统。真要在插入后拿到主键我建议用INSERT ... RETURNING id INTO variable不仅兼容性更好还能同时拿到多个字段。但如果你的表确实启用了 OID并且是单行插入RESULT_OID 依然可以直接用。3.2 影响行数校验的标准动作让我用一个实际场景来展示。假设你要更新一批用户的会员等级但前提是只允许更新 100 个用户超过或不足都要报错DO $$ DECLARE v_rows bigint; BEGIN UPDATE users SET member_level 3 WHERE id IN (SELECT id FROM users WHERE point 5000 LIMIT 100); GET DIAGNOSTICS v_rows ROW_COUNT; IF v_rows 100 THEN RAISE EXCEPTION 期望更新 100 行实际更新 % 行, v_rows; END IF; END $$;这里的关键点是GET DIAGNOSTICS 必须在 UPDATE 语句之后立即执行。因为 PL/pgSQL 中几乎每一条语句都会刷新最近命令信息中间一旦多插入了任何其他 SQL比如一次轻量 SELECT 或 RAISE NOTICE都可能覆盖掉刚才的行数。我之前踩过这个坑在 GET DIAGNOSTICS 前面加了一个 SELECT 做日志查询结果拿到的行数变成了那个 SELECT 的行数整个校验逻辑直接失效。3.3 嵌套调用时用 STACKED 诊断快速定位在异常处理块里GET DIAGNOSTICS默认读取的是当前这条命令的信息但如果你想拿到异常发生时的调用堆栈就要用GET STACKED DIAGNOSTICS。这个细节很有用因为存储过程一旦嵌套三层以上外层很难知道内层到底在哪个函数、哪一行崩了。看这段代码CREATE OR REPLACE FUNCTION inner_check(p_id int) RETURNS void LANGUAGE plpgsql AS $$ BEGIN PERFORM 1 FROM users WHERE id p_id; IF NOT FOUND THEN RAISE EXCEPTION inner user not found; END IF; END $$; CREATE OR REPLACE FUNCTION outer_check() RETURNS void LANGUAGE plpgsql AS $$ DECLARE v_ctx text; BEGIN BEGIN PERFORM inner_check(99999); EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_ctx PG_EXCEPTION_CONTEXT; RAISE NOTICE 捕获异常上下文: %, v_ctx; END; END $$;执行 outer_check 之后v_ctx 里会包含 PL/pgSQL function inner_check(...) 内部出错的位置调用栈而不是只给一个孤零零的错误码。在生产环境排查线上问题的时候这一段上下文能节省大量时间。如果用GET DIAGNOSTICS v_ctx PG_CONTEXT;拿到的只是当前 EXCEPTION 块所在的位置等于只告诉你“我被捕了”却没告诉你“我从哪里来”定位能力就弱了一大截。3.4 注意先取数再做事否则 ROW_COUNT 会被覆盖再强调一遍这个顺序问题因为实在太容易踩到。有些开发者在执行完 DELETE 之后先去 RAISE NOTICE 看一下变量再 GET DIAGNOSTICS。注意RAISE NOTICE 并不是 SQL 命令它不会覆盖诊断信息这种场景倒是安全的。真正危险的是在中间加了一条SELECT、INSERT、UPDATE、DELETE或者EXECUTE不管是执行了什么 SQL只要执行了最近的诊断信息就被替换了。还有一种情况是执行完 DML 之后调用了另一个函数而那个函数内部如果也有 DML行数同样会被覆盖。稳妥的办法是把 GET DIAGNOSTICS 放在目标 SQL 语句后的第一顺位并且用变量提前存起来后续再做任何分支判断。4. RETURN 家族RETURN、RETURN NEXT、RETURN QUERY 怎么选如果存储过程只是返回一个标量RETURN 很直观。但当你需要返回一个复杂结果集时三种 RETURN 用法的选择会影响性能、可读性和调试便利。4.1 三种写法的区别我把核心区别整理成一张表写法行为适用场景RETURN expression结束函数并返回一个值返回标量、单行单列RETURN NEXT expression把当前值追加到结果集然后继续执行在循环里逐行生成结果集RETURN QUERY query把查询结果整体追加到结果集然后继续执行直接把现有查询结果作为子集返回RETURN QUERY EXECUTE dynamic_query执行动态 SQL 并把结果集追加动态表名、动态查询条件很多人以为RETURN NEXT和 RETURN 的区别只是是否在循环里其实还有一个更关键的特性RETURN NEXT不会结束函数执行。它只是把一行结果“塞进返回缓冲区”然后继续跑后面的代码。例如CREATE OR REPLACE FUNCTION get_even_numbers(p_limit int) RETURNS SETOF int LANGUAGE plpgsql AS $$ DECLARE i int; BEGIN FOR i IN 1..p_limit LOOP IF i % 2 0 THEN RETURN NEXT i; END IF; END LOOP; RETURN; END $$;这里最后的RETURN不带任何值只是告诉函数“结果集已经构造完了可以结束了”。如果漏了最后这个 RETURN函数会报错吗其实是需要的因为 SETOF 函数必须在最后执行一个无参 RETURN 来收尾。4.2 RETURN QUERY 与动态查询用RETURN QUERY可以把一个普通查询的结果直接作为返回结果集的一部分。它的好处是不需要逐行循环性能更高代码也清晰。比如要返回所有积分大于 5000 的用户CREATE OR REPLACE FUNCTION get_vip_users() RETURNS TABLE(user_id int, user_name text) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT id, name FROM users WHERE point 5000; END $$;这里要注意RETURN QUERY 之后函数不会停下来如果后面还有代码它们还是会继续执行。如果需要把多个查询的结果都返回出来可以写多个 RETURN QUERY结果集会按顺序拼接。动态 SQL 的版本是RETURN QUERY EXECUTECREATE OR REPLACE FUNCTION get_users_by_table(p_table_name text) RETURNS TABLE(user_id int, user_name text) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY EXECUTE format( SELECT id, name FROM %I WHERE point 5000, p_table_name ); END $$;format函数里的%I会把表名安全地转成标识符避免 SQL 注入。这一步我强烈建议每个写动态 SQL 的人都形成肌肉记忆千万别图省事直接用字符串拼接。4.3 OUT 参数和 RETURNS TABLE 的分工RETURNS TABLE 适合那种“我需要一个明确命名、明确类型的输出结构”的函数。OUT 参数则可以在函数签名里把输出参数一个个列出来调用时可以直接用SELECT * FROM func(...)查看。差异主要体现在灵活度上。举个例子返回多个字段的时候RETURNS TABLE 需要把所有字段都写进 TABLE 类型里OUT 参数则可以和普通函数参数并列。实战中我习惯这样选如果结果集是查询得到的用 RETURNS TABLE如果结果集是由多个业务分支在不同位置逐步拼出来的用 OUT 参数配合 RETURN NEXT 更好因为 OUT 参数的值可以直接作为一行输出代码读起来更像“填表格”。其实两者都能实现同样的效果关键是你和团队更接受哪种风格。注意如果函数同时声明了 OUT 参数最后的RETURN expression会把表达式赋值给第一个 OUT 参数吗不会RETURN expression 在这种函数里通常不能带表达式否则会报错。你只需要用不带参数的 RETURN 来结束流程OUT 参数值会在 RETURN NEXT 时被缓存为一行。5. 一个综合实战批量发放优惠券的存储过程前面几节分别讲了三个关键字但它们在实际项目里很少单独出现。我设计了一个模拟场景把 PERFORM、GET DIAGNOSTICS、RETURN QUERY 串进同一个函数里这样你就能看到它们如何配合。5.1 表结构和函数目标假设一套简化的会员系统里有两张表CREATE TABLE campaigns ( id int PRIMARY KEY, status text NOT NULL DEFAULT active ); CREATE TABLE coupons ( id bigserial PRIMARY KEY, user_id int NOT NULL, campaign_id int NOT NULL, amount numeric(10,2) NOT NULL, status text NOT NULL DEFAULT issued );现在要实现一个函数给指定用户在某活动下发放一张优惠券返回优惠券编号、活动编号、用户编号。整个过程需要做三件事使用 PERFORM 锁定活动行防止并发发放超额。使用 GET DIAGNOSTICS 校验插入是否真正影响了一行。使用 RETURN QUERY 返回结果集。5.2 完整的函数代码CREATE OR REPLACE FUNCTION issue_coupon( p_campaign_id int, p_user_id int, p_amount numeric ) RETURNS TABLE(coupon_id bigint, campaign_id int, user_id int) LANGUAGE plpgsql AS $$ DECLARE v_campaign_status text; v_coupon_id bigint; v_rows bigint; BEGIN -- 第一步先读取活动状态如果不存在或已结束直接返回空结果集 SELECT status INTO v_campaign_status FROM campaigns WHERE id p_campaign_id; IF NOT FOUND OR v_campaign_status active THEN RAISE EXCEPTION 活动不存在或已结束; END IF; -- 第二步使用 PERFORM 锁定活动行避免并发下重复发放 PERFORM 1 FROM campaigns WHERE id p_campaign_id FOR UPDATE; -- 第三步插入优惠券并取回自增主键 INSERT INTO coupons (user_id, campaign_id, amount, status) VALUES (p_user_id, p_campaign_id, p_amount, issued) RETURNING id INTO v_coupon_id; -- 第四步用 GET DIAGNOSTICS 校验影响行数 GET DIAGNOSTICS v_rows ROW_COUNT; IF v_rows 1 THEN RAISE EXCEPTION 插入异常预期 1 行实际 % 行, v_rows; END IF; -- 第五步构造结果集返回 RETURN QUERY SELECT v_coupon_id, p_campaign_id, p_user_id; END $$;5.3 关键步骤逐行解读很多人可能会问第二步的 PERFORM 看起来和第一步的 SELECT 有重复的查询为什么不直接合并这里有个很重要的并发语义SELECT status INTO只是读取活动状态它没有锁定任何东西。如果两个会话同时执行这个函数可能都读到 active然后同时执行 INSERT导致发放次数翻倍。而PERFORM 1 ... FOR UPDATE会在活动行上拿一把 UPDATE 级别的锁。第二个会话一旦执行到这里就会等待第一个会话提交或回滚从根源上避免了并发重复发放。第三步用INSERT ... RETURNING id INTO v_coupon_id直接取得了主键比先 INSERT 再 SELECT 要安全得多也省了一次查询。这也是我在实际项目中更倾向于使用 RETURNING 而不是 RESULT_OID 的原因。第四步的 GET DIAGNOSTICS 不是为了拿主键而是要确认写入是成功的。一般 INSERT 不报错就说明成功了一行但程序员要学会对“不明显假设”保持警惕。特别是当 INSERT 语句可能涉及触发器、规则或者继承表时实际影响行数可能不是 1。有了这个校验即使未来表上加了触发器也能第一时间发现问题。第五步RETURN QUERY SELECT v_coupon_id, ...是一行很短的写法。因为这里只有一个输出行你也可以用RETURN NEXT搭配 OUT 参数但 RETURNS TABLE 配合 RETURN QUERY 的可读性更好调用方直接把这个函数当普通表来查询SELECT * FROM issue_coupon(10, 100, 9.9);6. 调试方法与常见报错速查存储过程调试是很多人的痛点但只要你掌握几个小工具调试效率能明显提升。6.1 用 RAISE NOTICE PG_CONTEXT 做轻量级断点应用代码里有 IDE 的断点PL/pgSQL 里没有。但 RAISE NOTICE 可以在关键位置打印变量配合 GET DIAGNOSTICS 拿到上下文就能还原出整个执行路径。我通常会在函数入口打印入参在可疑分支打印中间变量在异常块里打印 SQLSTATE 和上下文。BEGIN RAISE NOTICE issue_coupon start: campaign%, user%, p_campaign_id, p_user_id; -- ...业务代码... EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_ctx PG_EXCEPTION_CONTEXT; RAISE WARNING 异常上下文: %, v_ctx; RAISE; END;这里的RAISE;不带任何参数会重新抛出原始异常这样上层仍然能看到错误信息但你已经多打了一条上下文日志。如果在开发环境还想看到更底层的细节可以在会话里设置SET client_min_messages debug;这样能打出 PL/pgSQL 的执行细节但生产库不建议开日志量会很大。6.2 常见报错与解决对照表我整理了这几种语法最容易踩到的报错和解决方案基本覆盖了我日常见到的场景报错或异常现象原因解决办法query has no destination for result data函数内直接执行 SELECT没有 INTO 或 RETURN QUERY用 PERFORM 丢弃结果或用 SELECT INTO 受领变量PERFORM 后 FOUND 恒为真写成PERFORM SELECT...或把动态字符串放进了 PERFORM改用 EXECUTE 动态 SQLGET DIAGNOSTICS 拿到的行数是 0 或不对中间被其他 SQL 命令覆盖了诊断信息把 GET DIAGNOSTICS 放到目标 SQL 后第一顺位RETURN NEXT 后函数没有返回缺少最后的无参 RETURNSETOF 函数需要在 RETURN NEXT 之后补一个 RETURNRETURN QUERY EXECUTE 传参报错没有用 format 的 %I导致表名或列名拼接错误一律用 format %I/%L 转义OUT 参数函数中写 RETURN expression 报错OUT 参数模式下不允许 RETURN 带值改成 RETURN NEXT 或直接使用 OUT 参数赋值异常块里 GET DIAGNOSTICS 拿不到异常上下文用的是普通 GET DIAGNOSTICS改成 GET STACKED DIAGNOSTICS这张表虽然不长但每一行都是我实际调试中碰过的。尤其是第一个报错刚写存储过程的人几乎都会遇到一次后面才慢慢记住 PERFORM 的用处。7. 最后再分享一点我的使用习惯这三个关键字里我实际工作中用 GET DIAGNOSTICS 的频率是最高的。很多线上问题其实都是“数据被多改了或漏改了”而这类问题最直接的信号就是影响行数不对。在关键写操作后面加一行行数校验成本极低但能让存储过程在出现问题前就主动报错而不是悄悄把脏数据写进去。PERFORM 看起来有些“绕”但它在并发控制上的价值非常高。FOR UPDATE 锁行、咨询锁防重入这些都是普通 SELECT 做不到的干净用法。我现在的习惯是只要某个查询的结果我不关心只是在函数内部做一个存在判断或副作用调用就一定写成 PERFORM坚决不给客户端带结果集。RETURN 家族则要按数据结构来选。能一次查完的用 RETURN QUERY需要在循环中逐步构造的用 RETURN NEXT动态条件多的用 RETURN QUERY EXECUTE。总之把每个关键字背后的适用场景记清楚写出来的 PL/pgSQL 不仅更稳调试起来也爽快得多。
1. 先把 PERFORM、GET DIAGNOSTICS、RETURN 这东西的定位理清楚总有人说 PL/pgSQL 的存储过程不好写功能写大了以后到处都是 RAISE、IF、LOOP一旦涉及动态结果集和行数校验代码就像一盘散沙。我自己刚开始从普通 SQL 转到 PostgreSQL 存储过程时也在这几个关键字上栽过跟头PERFORM 到底和 SELECT 有什么区别GET DIAGNOSTICS 能拿到的信息是不是就一个行数RETURN 家族里那么多写法什么时候该用哪一种这些问题不搞明白写出来的存储过程往往能用但关键时刻容易出问题而且不好排查。这篇文章不打算讲基础语法而是直接瞄准这三个在高级场景里绕不开的关键字从使用场景、代码示例、报错排查到综合实战一起过一遍。适合已经把 PL/pgSQL 基本语法用熟、开始写中大型业务逻辑的开发者。如果你是刚接触存储过程建议先把 CREATE FUNCTION、变量声明、IF/LOOP 这些基础过一遍再回来看这篇文章会顺畅很多。1.1 PERFORM执行“查询”但不要“结果”PL/pgSQL 里直接写SELECT * FROM xxx跟在 SQL 客户端里执行不太一样。在函数内部如果SELECT后面没有INTO或RETURN QUERY等接收结果的配套语句PostgreSQL 会报错或者把结果集直接返回给调用方造成不可控的行为。PERFORM 就是为了解决“我只需要执行这条查询不需要把结果集返回出来”的场景而存在。它的语法格式和 SELECT 基本一致唯一不同的是结果会被丢弃。执行完之后最近一次查询是否返回了至少一行可以通过系统变量FOUND来判断。很多人把 PERFORM 理解为“无用的 SELECT”其实它的主要价值在于做存在性判断、调用副作用函数、获取行锁以及避免无意义的中间结果集污染最终输出。后面我会专门用一个小节展开它的高频用法。1.2 GET DIAGNOSTICS给代码开一份诊断报告存储过程里最难受的一件事是执行完一条 INSERT / UPDATE / DELETE 之后你根本不知道它到底影响了几行。尤其是在批量同步、归档数据、并发扣减这类场景里影响行数和预期不一致就是业务 bug 的前兆。GET DIAGNOSTICS 就是 PL/pgSQL 提供的“诊断信息提取器”。它可以读取最近一条 SQL 命令的执行信息最常见的取值项是ROW_COUNT也就是最近命令处理的行数。除此之外还能拿到 SQLSTATE、异常消息文本、异常上下文堆栈等。放在异常处理块里它几乎等价于给存储过程装了一个黑匣子能快速定位是哪一层函数、哪一个语句出了问题。后面我会讲清楚普通诊断和堆栈诊断的区别这个区别在嵌套函数里非常重要。1.3 RETURN返回值的总调度RETURN 在 PL/pgSQL 里并不是简单的一个“退出语句”。单独看它可能就是退出函数但组合上NEXT、QUERY、QUERY EXECUTE之后它就是构造结果集的三种完全不同的姿势。比如RETURN NEXT是每次追加一行适合在循环里逐步生成结果RETURN QUERY是把查询结果一次性追加到输出RETURN QUERY EXECUTE则是执行动态 SQL 并把结果追加进来。很多人在函数里想返回多行却只用一个RETURN SELECT结果发现只返回第一行然后整个函数就终止了。这就是没有把三种 RETURN 用法吃透导致的。还有 OUT 参数和 RETURNS TABLE 之间的选择也常常让初学者迷路。这部分我会在第 4 节详细拆开。2. PERFORM 高频用法与三个最容易踩的坑PERFORM 看着简单真正用的时候却有不少细节。我挑三个最常见的场景再附带对应的典型错误你看完基本能避开大部分坑。2.1 用 PERFORM 做存在性检查配合 FOUND假设你现在要判断某个用户是否存在同时不想把结果集暴露出去。早期我很喜欢写SELECT * FROM users WHERE id 100;在函数外面没问题但在函数内部这条语句如果没有INTOPostgreSQL 会报“query has no destination for result data”之类的错误。就算不报错一旦真的查到数据结果集也会被抛给客户端完全不是我们想要的。正确写法是用 PERFORMDO $$ DECLARE v_exist boolean : false; BEGIN PERFORM 1 FROM users WHERE id 100; IF FOUND THEN RAISE NOTICE 用户 100 存在; ELSE RAISE NOTICE 用户 100 不存在; END IF; END $$;这里PERFORM 1 FROM ...是 SELECT 1只要能返回一行FOUND就是真。我用1而不是*是为了尽量减少数据传输的成本虽然 PERFORM 本来就不返回结果但这种习惯会在 SELECT INTO 等场景里同样受益。2.2 PERFORM 拿锁、调函数避免结果集污染客户端PERFORM 经常被拿来执行“有副作用但不需要返回值”的语句比较典型的是行锁和咨询锁。比如你想在存储过程开头锁住某个活动行防止并发重复发放优惠券直接写PERFORM 1 FROM campaigns WHERE id 100 FOR UPDATE;这条语句会拿到该行的更新锁但不会把行内容返回给客户端。如果改成SELECT * FROM campaigns WHERE id 100 FOR UPDATE;那么这条查询的结果集同样会被存进内存或直接返回在一些驱动下还会造成资源浪费。再比如调用一些自定义函数函数本身做了资金校验但返回值你不关心也可以PERFORM check_user_balance(100);这时候你只需要 FOUND 来判断这个函数是否有返回行或者干脆不关注结果。2.3 动态 SQL 场景不要试图 PERFORM 字符串这是我最想提醒的一点。PERFORM 后面跟的是静态查询语句它的语法结构在编译阶段就被确定了。如果你想执行动态拼接出来的 SQL比如-- 错误示范 PERFORM format(SELECT 1 FROM users WHERE id %s, v_id);这种写法很多时候并不会报错但实际执行的是一个 SELECT 字符串常量而不是你想要的动态查询。结果是 FOUND 总是为真因为字符串常量本身就是一个单行结果。真正应该用的是EXECUTEEXECUTE format(SELECT 1 FROM users WHERE id %s, v_id); IF FOUND THEN RAISE NOTICE 存在; END IF;注意 EXECUTE 执行后同样会更新 FOUND所以“动态 SQL 里的 PERFORM”本质上就是 EXECUTE不是 PERFORM 多态。提示我见过不少开发者在 PERFORM 和 EXECUTE 之间反复切换改来改去最后甚至写出PERFORM EXECUTE(...)这种语法错误。记住一句话静态查询想丢弃结果用 PERFORM动态查询想丢弃结果用 EXECUTE。3. GET DIAGNOSTICS行数、OID 与异常上下文一网打尽GET DIAGNOSTICS 能拿到的信息比大多数人想象的丰富。除了最常用的ROW_COUNT还有RESULT_OID、RETURNED_SQLSTATE、PG_CONTEXT、PG_EXCEPTION_DETAIL、PG_EXCEPTION_HINT等。掌握它等于给存储过程加上了肉眼可见的执行反馈。3.1 最常用的赋值项先看基本语法GET DIAGNOSTICS variable item [, variable item ...];常用的 item 项我用一个表格列出来诊断项类型说明ROW_COUNTbigint最近一条 SQL 命令处理的行数UPDATE/DELETE/INSERT 时最常用RESULT_OIDoid最近一次 INSERT 生成的行 OID仅在表和命令支持时有效RETURNED_SQLSTATEtext最近执行的错误码常用于异常分类PG_CONTEXTtext当前执行位置的调用上下文适合嵌套函数调试PG_EXCEPTION_DETAILtext异常详细信息只能在异常块内使用PG_EXCEPTION_HINTtext异常提示信息只能在异常块内使用PG_EXCEPTION_CONTEXTtext异常发生时的调用栈信息只能在异常块内使用你可能会问RESULT_OID 看起来挺古老现在还有用吗实际上PostgreSQL 默认表通常没有 OID所以这个诊断项更多是兼容历史遗留系统。真要在插入后拿到主键我建议用INSERT ... RETURNING id INTO variable不仅兼容性更好还能同时拿到多个字段。但如果你的表确实启用了 OID并且是单行插入RESULT_OID 依然可以直接用。3.2 影响行数校验的标准动作让我用一个实际场景来展示。假设你要更新一批用户的会员等级但前提是只允许更新 100 个用户超过或不足都要报错DO $$ DECLARE v_rows bigint; BEGIN UPDATE users SET member_level 3 WHERE id IN (SELECT id FROM users WHERE point 5000 LIMIT 100); GET DIAGNOSTICS v_rows ROW_COUNT; IF v_rows 100 THEN RAISE EXCEPTION 期望更新 100 行实际更新 % 行, v_rows; END IF; END $$;这里的关键点是GET DIAGNOSTICS 必须在 UPDATE 语句之后立即执行。因为 PL/pgSQL 中几乎每一条语句都会刷新最近命令信息中间一旦多插入了任何其他 SQL比如一次轻量 SELECT 或 RAISE NOTICE都可能覆盖掉刚才的行数。我之前踩过这个坑在 GET DIAGNOSTICS 前面加了一个 SELECT 做日志查询结果拿到的行数变成了那个 SELECT 的行数整个校验逻辑直接失效。3.3 嵌套调用时用 STACKED 诊断快速定位在异常处理块里GET DIAGNOSTICS默认读取的是当前这条命令的信息但如果你想拿到异常发生时的调用堆栈就要用GET STACKED DIAGNOSTICS。这个细节很有用因为存储过程一旦嵌套三层以上外层很难知道内层到底在哪个函数、哪一行崩了。看这段代码CREATE OR REPLACE FUNCTION inner_check(p_id int) RETURNS void LANGUAGE plpgsql AS $$ BEGIN PERFORM 1 FROM users WHERE id p_id; IF NOT FOUND THEN RAISE EXCEPTION inner user not found; END IF; END $$; CREATE OR REPLACE FUNCTION outer_check() RETURNS void LANGUAGE plpgsql AS $$ DECLARE v_ctx text; BEGIN BEGIN PERFORM inner_check(99999); EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_ctx PG_EXCEPTION_CONTEXT; RAISE NOTICE 捕获异常上下文: %, v_ctx; END; END $$;执行 outer_check 之后v_ctx 里会包含 PL/pgSQL function inner_check(...) 内部出错的位置调用栈而不是只给一个孤零零的错误码。在生产环境排查线上问题的时候这一段上下文能节省大量时间。如果用GET DIAGNOSTICS v_ctx PG_CONTEXT;拿到的只是当前 EXCEPTION 块所在的位置等于只告诉你“我被捕了”却没告诉你“我从哪里来”定位能力就弱了一大截。3.4 注意先取数再做事否则 ROW_COUNT 会被覆盖再强调一遍这个顺序问题因为实在太容易踩到。有些开发者在执行完 DELETE 之后先去 RAISE NOTICE 看一下变量再 GET DIAGNOSTICS。注意RAISE NOTICE 并不是 SQL 命令它不会覆盖诊断信息这种场景倒是安全的。真正危险的是在中间加了一条SELECT、INSERT、UPDATE、DELETE或者EXECUTE不管是执行了什么 SQL只要执行了最近的诊断信息就被替换了。还有一种情况是执行完 DML 之后调用了另一个函数而那个函数内部如果也有 DML行数同样会被覆盖。稳妥的办法是把 GET DIAGNOSTICS 放在目标 SQL 语句后的第一顺位并且用变量提前存起来后续再做任何分支判断。4. RETURN 家族RETURN、RETURN NEXT、RETURN QUERY 怎么选如果存储过程只是返回一个标量RETURN 很直观。但当你需要返回一个复杂结果集时三种 RETURN 用法的选择会影响性能、可读性和调试便利。4.1 三种写法的区别我把核心区别整理成一张表写法行为适用场景RETURN expression结束函数并返回一个值返回标量、单行单列RETURN NEXT expression把当前值追加到结果集然后继续执行在循环里逐行生成结果集RETURN QUERY query把查询结果整体追加到结果集然后继续执行直接把现有查询结果作为子集返回RETURN QUERY EXECUTE dynamic_query执行动态 SQL 并把结果集追加动态表名、动态查询条件很多人以为RETURN NEXT和 RETURN 的区别只是是否在循环里其实还有一个更关键的特性RETURN NEXT不会结束函数执行。它只是把一行结果“塞进返回缓冲区”然后继续跑后面的代码。例如CREATE OR REPLACE FUNCTION get_even_numbers(p_limit int) RETURNS SETOF int LANGUAGE plpgsql AS $$ DECLARE i int; BEGIN FOR i IN 1..p_limit LOOP IF i % 2 0 THEN RETURN NEXT i; END IF; END LOOP; RETURN; END $$;这里最后的RETURN不带任何值只是告诉函数“结果集已经构造完了可以结束了”。如果漏了最后这个 RETURN函数会报错吗其实是需要的因为 SETOF 函数必须在最后执行一个无参 RETURN 来收尾。4.2 RETURN QUERY 与动态查询用RETURN QUERY可以把一个普通查询的结果直接作为返回结果集的一部分。它的好处是不需要逐行循环性能更高代码也清晰。比如要返回所有积分大于 5000 的用户CREATE OR REPLACE FUNCTION get_vip_users() RETURNS TABLE(user_id int, user_name text) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT id, name FROM users WHERE point 5000; END $$;这里要注意RETURN QUERY 之后函数不会停下来如果后面还有代码它们还是会继续执行。如果需要把多个查询的结果都返回出来可以写多个 RETURN QUERY结果集会按顺序拼接。动态 SQL 的版本是RETURN QUERY EXECUTECREATE OR REPLACE FUNCTION get_users_by_table(p_table_name text) RETURNS TABLE(user_id int, user_name text) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY EXECUTE format( SELECT id, name FROM %I WHERE point 5000, p_table_name ); END $$;format函数里的%I会把表名安全地转成标识符避免 SQL 注入。这一步我强烈建议每个写动态 SQL 的人都形成肌肉记忆千万别图省事直接用字符串拼接。4.3 OUT 参数和 RETURNS TABLE 的分工RETURNS TABLE 适合那种“我需要一个明确命名、明确类型的输出结构”的函数。OUT 参数则可以在函数签名里把输出参数一个个列出来调用时可以直接用SELECT * FROM func(...)查看。差异主要体现在灵活度上。举个例子返回多个字段的时候RETURNS TABLE 需要把所有字段都写进 TABLE 类型里OUT 参数则可以和普通函数参数并列。实战中我习惯这样选如果结果集是查询得到的用 RETURNS TABLE如果结果集是由多个业务分支在不同位置逐步拼出来的用 OUT 参数配合 RETURN NEXT 更好因为 OUT 参数的值可以直接作为一行输出代码读起来更像“填表格”。其实两者都能实现同样的效果关键是你和团队更接受哪种风格。注意如果函数同时声明了 OUT 参数最后的RETURN expression会把表达式赋值给第一个 OUT 参数吗不会RETURN expression 在这种函数里通常不能带表达式否则会报错。你只需要用不带参数的 RETURN 来结束流程OUT 参数值会在 RETURN NEXT 时被缓存为一行。5. 一个综合实战批量发放优惠券的存储过程前面几节分别讲了三个关键字但它们在实际项目里很少单独出现。我设计了一个模拟场景把 PERFORM、GET DIAGNOSTICS、RETURN QUERY 串进同一个函数里这样你就能看到它们如何配合。5.1 表结构和函数目标假设一套简化的会员系统里有两张表CREATE TABLE campaigns ( id int PRIMARY KEY, status text NOT NULL DEFAULT active ); CREATE TABLE coupons ( id bigserial PRIMARY KEY, user_id int NOT NULL, campaign_id int NOT NULL, amount numeric(10,2) NOT NULL, status text NOT NULL DEFAULT issued );现在要实现一个函数给指定用户在某活动下发放一张优惠券返回优惠券编号、活动编号、用户编号。整个过程需要做三件事使用 PERFORM 锁定活动行防止并发发放超额。使用 GET DIAGNOSTICS 校验插入是否真正影响了一行。使用 RETURN QUERY 返回结果集。5.2 完整的函数代码CREATE OR REPLACE FUNCTION issue_coupon( p_campaign_id int, p_user_id int, p_amount numeric ) RETURNS TABLE(coupon_id bigint, campaign_id int, user_id int) LANGUAGE plpgsql AS $$ DECLARE v_campaign_status text; v_coupon_id bigint; v_rows bigint; BEGIN -- 第一步先读取活动状态如果不存在或已结束直接返回空结果集 SELECT status INTO v_campaign_status FROM campaigns WHERE id p_campaign_id; IF NOT FOUND OR v_campaign_status active THEN RAISE EXCEPTION 活动不存在或已结束; END IF; -- 第二步使用 PERFORM 锁定活动行避免并发下重复发放 PERFORM 1 FROM campaigns WHERE id p_campaign_id FOR UPDATE; -- 第三步插入优惠券并取回自增主键 INSERT INTO coupons (user_id, campaign_id, amount, status) VALUES (p_user_id, p_campaign_id, p_amount, issued) RETURNING id INTO v_coupon_id; -- 第四步用 GET DIAGNOSTICS 校验影响行数 GET DIAGNOSTICS v_rows ROW_COUNT; IF v_rows 1 THEN RAISE EXCEPTION 插入异常预期 1 行实际 % 行, v_rows; END IF; -- 第五步构造结果集返回 RETURN QUERY SELECT v_coupon_id, p_campaign_id, p_user_id; END $$;5.3 关键步骤逐行解读很多人可能会问第二步的 PERFORM 看起来和第一步的 SELECT 有重复的查询为什么不直接合并这里有个很重要的并发语义SELECT status INTO只是读取活动状态它没有锁定任何东西。如果两个会话同时执行这个函数可能都读到 active然后同时执行 INSERT导致发放次数翻倍。而PERFORM 1 ... FOR UPDATE会在活动行上拿一把 UPDATE 级别的锁。第二个会话一旦执行到这里就会等待第一个会话提交或回滚从根源上避免了并发重复发放。第三步用INSERT ... RETURNING id INTO v_coupon_id直接取得了主键比先 INSERT 再 SELECT 要安全得多也省了一次查询。这也是我在实际项目中更倾向于使用 RETURNING 而不是 RESULT_OID 的原因。第四步的 GET DIAGNOSTICS 不是为了拿主键而是要确认写入是成功的。一般 INSERT 不报错就说明成功了一行但程序员要学会对“不明显假设”保持警惕。特别是当 INSERT 语句可能涉及触发器、规则或者继承表时实际影响行数可能不是 1。有了这个校验即使未来表上加了触发器也能第一时间发现问题。第五步RETURN QUERY SELECT v_coupon_id, ...是一行很短的写法。因为这里只有一个输出行你也可以用RETURN NEXT搭配 OUT 参数但 RETURNS TABLE 配合 RETURN QUERY 的可读性更好调用方直接把这个函数当普通表来查询SELECT * FROM issue_coupon(10, 100, 9.9);6. 调试方法与常见报错速查存储过程调试是很多人的痛点但只要你掌握几个小工具调试效率能明显提升。6.1 用 RAISE NOTICE PG_CONTEXT 做轻量级断点应用代码里有 IDE 的断点PL/pgSQL 里没有。但 RAISE NOTICE 可以在关键位置打印变量配合 GET DIAGNOSTICS 拿到上下文就能还原出整个执行路径。我通常会在函数入口打印入参在可疑分支打印中间变量在异常块里打印 SQLSTATE 和上下文。BEGIN RAISE NOTICE issue_coupon start: campaign%, user%, p_campaign_id, p_user_id; -- ...业务代码... EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_ctx PG_EXCEPTION_CONTEXT; RAISE WARNING 异常上下文: %, v_ctx; RAISE; END;这里的RAISE;不带任何参数会重新抛出原始异常这样上层仍然能看到错误信息但你已经多打了一条上下文日志。如果在开发环境还想看到更底层的细节可以在会话里设置SET client_min_messages debug;这样能打出 PL/pgSQL 的执行细节但生产库不建议开日志量会很大。6.2 常见报错与解决对照表我整理了这几种语法最容易踩到的报错和解决方案基本覆盖了我日常见到的场景报错或异常现象原因解决办法query has no destination for result data函数内直接执行 SELECT没有 INTO 或 RETURN QUERY用 PERFORM 丢弃结果或用 SELECT INTO 受领变量PERFORM 后 FOUND 恒为真写成PERFORM SELECT...或把动态字符串放进了 PERFORM改用 EXECUTE 动态 SQLGET DIAGNOSTICS 拿到的行数是 0 或不对中间被其他 SQL 命令覆盖了诊断信息把 GET DIAGNOSTICS 放到目标 SQL 后第一顺位RETURN NEXT 后函数没有返回缺少最后的无参 RETURNSETOF 函数需要在 RETURN NEXT 之后补一个 RETURNRETURN QUERY EXECUTE 传参报错没有用 format 的 %I导致表名或列名拼接错误一律用 format %I/%L 转义OUT 参数函数中写 RETURN expression 报错OUT 参数模式下不允许 RETURN 带值改成 RETURN NEXT 或直接使用 OUT 参数赋值异常块里 GET DIAGNOSTICS 拿不到异常上下文用的是普通 GET DIAGNOSTICS改成 GET STACKED DIAGNOSTICS这张表虽然不长但每一行都是我实际调试中碰过的。尤其是第一个报错刚写存储过程的人几乎都会遇到一次后面才慢慢记住 PERFORM 的用处。7. 最后再分享一点我的使用习惯这三个关键字里我实际工作中用 GET DIAGNOSTICS 的频率是最高的。很多线上问题其实都是“数据被多改了或漏改了”而这类问题最直接的信号就是影响行数不对。在关键写操作后面加一行行数校验成本极低但能让存储过程在出现问题前就主动报错而不是悄悄把脏数据写进去。PERFORM 看起来有些“绕”但它在并发控制上的价值非常高。FOR UPDATE 锁行、咨询锁防重入这些都是普通 SELECT 做不到的干净用法。我现在的习惯是只要某个查询的结果我不关心只是在函数内部做一个存在判断或副作用调用就一定写成 PERFORM坚决不给客户端带结果集。RETURN 家族则要按数据结构来选。能一次查完的用 RETURN QUERY需要在循环中逐步构造的用 RETURN NEXT动态条件多的用 RETURN QUERY EXECUTE。总之把每个关键字背后的适用场景记清楚写出来的 PL/pgSQL 不仅更稳调试起来也爽快得多。
