1. 为什么这仨高级指令在PL/pgSQL里最容易被误解1.1 从一次线上存储过程事故说起前阵子在处理一个订单结转的存储过程时因为同时用到PERFORM、GET DIAGNOSTICS和RETURN这三个高级语法踩了一串我之前根本没意识到的坑。里面的核心逻辑是这样的有一段需要“执行某个聚合查询但不关心结果只关心有没有数据”我让同事先写结果他直接写了SELECT COUNT(*)在函数体里直接飘红。PL/pgSQL不允许裸的SELECT语句出现在非顶层位置除非配合INTO、RETURN QUERY、FOR循环这些载体。新手看到报错后第一反应是改用SELECT COUNT(*) INTO v_cnt然后再拿v_cnt去判断。这确实能绕过语法问题但代码会变得啰嗦而且很多场景下你根本不需要那个具体数字只需要知道“有没有”。这时候真正的解法是PERFORM。PERFORM把“只执行、不接收结果”变成一种合法且干净的写法但它丢弃结果后你又失去了对执行情况的感知于是需要GET DIAGNOSTICS把ROW_COUNT等状态读出来。等到函数要把结果交给调用方时又得分清RETURN、RETURN NEXT、RETURN QUERY之间的差异。这三个关键字单看都不难组合起来却是新手项目里最常见的翻车点。1.2 它们到底解决什么问题PERFORM解决的是“只执行、不接收”的语法合法性问题GET DIAGNOSTICS解决的是“执行完之后我怎么知道刚才发生了什么”RETURN家族解决的是“函数到底以什么形式把结果交出去”。在PL/pgSQL里这三者是一套完整闭环你执行了一段逻辑拿不到结果集时用PERFORM想知道影响行数时用DIAGNOSTICS想构造返回集时用RETURN QUERY。这套闭环不是纸面上的考点而是真实生产环境里的高频操作。批量更新、库存扣减、定期归档、错误日志记录几乎每个环节都会碰到。只是文档里把它们分散在不同的章节讲很多人看完就忘实际写代码时又因为不熟悉而绕远路。1.3 为什么说“高级”而不是“冷门”它们一点都不冷门几乎所有带流程控制的存储过程都绕不开。冷门的是“组合使用的方式”。我在做代码评审时经常看到这样的结果PERFORM被误当成SELECT INTO的替代品GET DIAGNOSTICS被写错关键字上下文RETURN NEXT和RETURN QUERY被混用导致结果集顺序错乱。这篇文章就围绕这三样把常见用法和真正能跑起来的组合方式讲透。2. PERFORM丢掉结果集把副作用留在函数里2.1 基本语法与执行逻辑PERFORM在PL/pgSQL中的角色是执行一条SQL命令并丢弃它产生的任何结果集。它最常见的场景是调用一个返回值的函数但你不关心返回值只关心它是否报错或者只关心它执行的副作用比如写日志、触发触发器、加锁。基本形式是PERFORM 1 FROM users WHERE id user_id;执行后查询返回的行数会被丢弃但FOUND状态会被设置。所以后面可以直接判断PERFORM 1 FROM orders WHERE id order_id; IF NOT FOUND THEN RAISE EXCEPTION 订单不存在: %, order_id; END IF;这比先SELECT COUNT(*) INTO v_cnt再判断更清爽也不会占用一个变量。我自己写权限校验时特别喜欢这套PERFORM 1 FROM role_permissions WHERE role_id p_role AND permission_key p_key; IF FOUND THEN PERFORM audit_log(auth_user, access_allowed, p_key); END IF;PERFORM后面可以跟任意SELECT查询也可以跟函数调用。比如等待锁释放时的PERFORM pg_sleep(0.1);配合重试循环比在应用层sleep更内聚。它的本质就是一个“执行并遗忘”的开关语法上合法语义上明确。2.2 为什么不能直接拿它来取数据PERFORM最大的限制是“丢弃结果”。如果你要取结果必须用SELECT INTO、RETURN QUERY或者OUT参数。很多人把PERFORM理解成“执行SELECT并忽略报错”这是错的——它同样会抛异常只是不返回数据。一个常见误区是试图从PERFORM中取出最后一次插入的IDPERFORM INSERT INTO audit_log(msg) VALUES (x) RETURNING id;如果你期待PERFORM后面跟着RETURNING还能拿到id就错了。RETURNING的结果同样被丢弃。此时应该用INSERT ... RETURNING id INTO v_id或者配合GET DIAGNOSTICS拿RESULT_OID。不过现代PostgreSQL里更推荐RETURNING INTO直接又清晰。另外一个容易忽略的细节是PERFORM后面不能带INTO子句。因为PERFORM的语义就是丢弃数据如果在语句里混入INTO相当于又要丢弃又要保存逻辑上是冲突的。遇到这种需求老老实实改用SELECT ... INTO。2.3 动态SQL里没有PERFORM的位置PERFORM不能执行动态SQL。如果你要把表名、条件拼出来执行只能用EXECUTEEXECUTE format(DELETE FROM %I WHERE ts now() - interval %L, tab_name, keep_interval);EXECUTE同样会执行并丢弃结果但它可以配合INTO取结果。记住这个分界线静态写死的SQL用PERFORM需要拼接的SQL用EXECUTE。这是PL/pgSQL里最容易混淆的分界线很多人在动态SQL里套PERFORM然后报语法错误最后不得不全部改成EXECUTE。3. GET DIAGNOSTICS把最近一次执行的状态装进变量3.1 先搞清楚CURRENT和STACKED两种形态GET DIAGNOSTICS有两条路径GET DIAGNOSTICS用于提取当前语句执行后的状态GET STACKED DIAGNOSTICS则只能在EXCEPTION块中使用用于提取正在处理的异常信息。普通场景下最常见的用法是取ROW_COUNTUPDATE orders SET status SHIPPED WHERE id p_order_id; GET DIAGNOSTICS v_affected ROW_COUNT;v_affected就是刚才UPDATE实际影响的行数。为什么强调“实际”因为PostgreSQL默认不会因为影响0行而报错很多业务需要有这个值才能决定下一步。比如库存扣减UPDATE inventory SET qty qty - p_qty WHERE product_id p_pid AND qty p_qty; GET DIAGNOSTICS v_updated ROW_COUNT; IF v_updated 0 THEN RAISE EXCEPTION 库存不足或商品不存在; END IF;这种写法的价值在于把“条件更新是否成功”的判断从先SELECT再UPDATE的两步压成一条命令加一个行数判断减少了并发间隙。两个会话同时扣库存时只有一个能真正更新到行另一个拿到ROW_COUNT为0然后走异常分支。3.2 ROW_COUNT与PERFORM的组合拳你可能会问PERFORM丢弃了结果集怎么知道它返回了多少行答案就是GET DIAGNOSTICS。PERFORM执行后ROW_COUNT会被设置为该查询返回的行数然后结果被丢弃。于是可以写出这样的逻辑PERFORM 1 FROM queue_items WHERE status PENDING; GET DIAGNOSTICS v_cnt ROW_COUNT; IF v_cnt 0 THEN PERFORM process_pending_queue(); END IF;这个组合非常实用相当于“探测有没有数据再决定要不要做重型操作”而且比SELECT COUNT(*) INTO v_cnt省去了一次聚合开销。我习惯把它叫作“PL/pgSQL里的盲操作探测器”只管触发不管接收但通过诊断信息保持对执行情况的知情权。3.3 异常块中用GET STACKED DIAGNOSTICS收集错误线索在EXCEPTION子句里普通GET DIAGNOSTICS已经拿不到错误本身的信息了必须用GET STACKED DIAGNOSTICS。常用的目标项包括RETURNED_SQLSTATE、MESSAGE_TEXT、PG_EXCEPTION_DETAIL、PG_EXCEPTION_HINT、PG_EXCEPTION_CONTEXT。一个典型的日志表记录异常CREATE TABLE proc_error_log ( id bigserial PRIMARY KEY, err_sqlstate text, err_msg text, err_context text, logged_at timestamptz DEFAULT now() ); CREATE OR REPLACE FUNCTION safe_archive() RETURNS void AS $$ DECLARE v_sqlstate text; v_msg text; v_context text; BEGIN DELETE FROM archive_tmp; INSERT INTO archive_main SELECT * FROM archive_tmp; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS v_sqlstate RETURNED_SQLSTATE, v_msg MESSAGE_TEXT, v_context PG_EXCEPTION_CONTEXT; INSERT INTO proc_error_log(err_sqlstate, err_msg, err_context) VALUES (v_sqlstate, v_msg, v_context); RAISE; END; $$ LANGUAGE plpgsql;这里的PG_EXCEPTION_CONTEXT会把你带到出错行所在的位置排查跨函数嵌套问题时特别有用。生产环境我一般会在最外层包一层这个日志表里的err_context能直接定位到是哪个函数、哪一行触发。3.4 别忘了RESULT_OID和PG_CONTEXTRESULT_OID用于INSERT语句没有RETURNING时的表OID现代用法很少但如果你在兼容老代码知道有这回事就行。PG_CONTEXT则是普通GET DIAGNOSTICS可以拿到的当前执行上下文配合RAISE NOTICE输出当前函数调用栈GET DIAGNOSTICS v_ctx PG_CONTEXT; RAISE NOTICE current ctx: %, v_ctx;这个在调试多层嵌套函数时非常救命。你想象一下一个函数调了五个层级不知道当前到底卡在哪层一条PG_CONTEXT直接打印出来比靠猜快得多。4. RETURN家族返回标量、返回一行还是返回一个查询4.1 RETURN与函数返回型的契约标量函数里RETURN负责携带返回值void函数里RETURN后面什么都不带只是提前退出。但很多人栽在“RETURN能不能作为流程控制语句”这个问题上。看这段CREATE FUNCTION check_status(p_id int) RETURNS text AS $$ BEGIN IF p_id 0 THEN RETURN INVALID; END IF; RETURN VALID; END; $$ LANGUAGE plpgsql;这是RETURN的经典用法同时也是“提前返回”的模式执行到第一个RETURN时函数立刻结束。需要记住的是RETURN的表达式类型必须严格匹配函数声明的返回类型PostgreSQL的类型检查很严格比如从integer到text必须显式转换。4.2 RETURN NEXT一行一行地造结果集RETURN NEXT只适用于RETURNS SETOF或RETURNS TABLE类型的函数。它的行为是“把当前行的投影追加到结果集尾巴上然后继续执行函数”。注意它不结束函数。一个生成序列的例子CREATE FUNCTION generate_interval_labels( p_start date, p_end date ) RETURNS SETOF text AS $$ DECLARE v_day date : p_start; BEGIN WHILE v_day p_end LOOP RETURN NEXT to_char(v_day, YYYY-MM-DD); v_day : v_day 1; END LOOP; END; $$ LANGUAGE plpgsql;调用时SELECT * FROM generate_interval_labels(2025-01-01, 2025-01-05);RETURN NEXT不会产生中间物化函数体逐行追加最终由无形的RETURN结束。很多人会忘记在循环结束后写上RETURN;——其实不写也行函数体走到END会自动返回但写上会更清晰尤其是当后面还有代码时。4.3 RETURN QUERY把查询结果整体倒入返回集RETURN QUERY后面直接跟一条SELECT相当于把查询结果全部追加到函数的结果集里。它比写循环一行行RETURN NEXT优雅得多CREATE FUNCTION get_open_orders(p_customer_id bigint) RETURNS TABLE(order_id bigint, amount numeric) AS $$ BEGIN RETURN QUERY SELECT id, amount FROM orders WHERE customer_id p_customer_id AND status OPEN ORDER BY created_at DESC; END; $$ LANGUAGE plpgsql;如果需要拼接动态SQLRETURN QUERY EXECUTE也支持但参数建议用USING不要用format硬拼值RETURN QUERY EXECUTE format(SELECT * FROM %I WHERE created_at $1, dyn_table) USING v_since;注意RETURN QUERY不会清空之前RETURN NEXT已经追加的数据。两者可以混用先RETURN NEXT加一行汇总再RETURN QUERY加一批明细这在生成报表时很爽。但混用时一定要清楚追加顺序——代码从上到下执行结果集顺序就是追加顺序。4.4 OUT参数场景下RETURN的微妙行为函数声明了OUT参数后RETURN不再承载返回值它是用来填充这些参数的。例如CREATE FUNCTION split_range( p_min int, p_max int, OUT mid int ) AS $$ BEGIN mid : (p_min p_max) / 2; RETURN; END; $$ LANGUAGE plpgsql;OUT参数的赋值是普通变量赋值函数结束时带出。RETURN在这里仅仅表示“到此为止”。如果漏了RETURN函数也会正常结束但如果你在后面还写了可能出错的代码行为就会变得很鬼畜。所以我的习惯是OUT参数函数里始终写清晰的RETURN代表逻辑出口。5. 三元组合的实战订单结转存储过程的完整实现5.1 需求描述某订单系统需要每天把已完成的订单从orders表结转进order_archive同时给客户发放积分。结转条件订单状态为COMPLETED订单日期早于结算日且未结转过。要求存储过程返回两个值本次结转成功的订单数、失败被跳过的订单数。如果某一单中途失败不能影响整体结转但要记录错误信息。这个需求把PERFORM、GET DIAGNOSTICS、RETURN全都用上了非常适合作为组合示例。5.2 建表与存储过程先建两张基础表CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL, status text NOT NULL, amount numeric(12,2) NOT NULL, completed_at timestamptz, archived_at timestamptz ); CREATE TABLE order_archive ( id bigint PRIMARY KEY, customer_id bigint NOT NULL, amount numeric(12,2) NOT NULL, archived_at timestamptz DEFAULT now() ); CREATE TABLE order_error_log ( id bigserial PRIMARY KEY, order_id bigint, err_msg text, err_context text, created_at timestamptz DEFAULT now() );存储过程的思路是用FOR循环遍历待结转订单对每条订单用PERFORM探测是否存在用UPDATE尝试打标记用GET DIAGNOSTICS确认影响行数避免并发任务重复处理。成功后插入归档表并发放积分所有可能失败的操作都包一层BEGIN/EXCEPTION记录错误后继续。CREATE OR REPLACE FUNCTION run_order_archive(p_cutoff timestamptz) RETURNS TABLE(success_count bigint, failed_count bigint) AS $$ DECLARE v_order RECORD; v_updated bigint; v_sqlstate text; v_msg text; v_ctx text; v_success bigint : 0; v_failed bigint : 0; BEGIN FOR v_order IN SELECT id, customer_id, amount FROM orders WHERE status COMPLETED AND completed_at p_cutoff AND archived_at IS NULL LOOP BEGIN -- 用PERFORM探测订单是否仍未被归档 PERFORM 1 FROM orders WHERE id v_order.id AND archived_at IS NULL; GET DIAGNOSTICS v_updated ROW_COUNT; IF v_updated 0 THEN CONTINUE; END IF; -- 抢锁式更新并发场景下只有一个会话能拿到影响行数 UPDATE orders SET archived_at now() WHERE id v_order.id AND archived_at IS NULL; GET DIAGNOSTICS v_updated ROW_COUNT; IF v_updated 0 THEN CONTINUE; END IF; -- 插入归档表 INSERT INTO order_archive(id, customer_id, amount) VALUES (v_order.id, v_order.customer_id, v_order.amount); -- 发放积分不关心返回行数只关心是否报错 PERFORM award_points(v_order.customer_id, v_order.amount); v_success : v_success 1; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS v_sqlstate RETURNED_SQLSTATE, v_msg MESSAGE_TEXT, v_ctx PG_EXCEPTION_CONTEXT; INSERT INTO order_error_log(order_id, err_msg, err_context) VALUES (v_order.id, v_msg || SQLSTATE: || v_sqlstate, v_ctx); v_failed : v_failed 1; END; END LOOP; RETURN QUERY SELECT v_success, v_failed; END; $$ LANGUAGE plpgsql;注意几点award_points是假设存在的积分发放函数返回值会被PERFORM丢弃但如果它抛异常会被当前块捕获。当订单开始处理时UPDATE已抢到行锁即使后续INSERT失败由于PL/pgSQL的EXCEPTION块会建立子事务失败时该订单的UPDATE也会回滚不会出现“归档失败但订单被标记已归档”的脏状态。整个函数处于一个外部事务中EXCEPTION块只回滚当前块内部操作不影响FOR循环的继续执行。5.3 调用与验证用SELECT * FROM run_order_archive(now() - interval 1 day);调用返回的是一行两列。这就是RETURN QUERY配合RETURN TABLE返回集合的效果。如果你不想返回表格也可以用OUT参数但返回TABLE更适合调用方直接看结果。我把一批测试数据造进去后实测返回success_count | failed_count --------------------------- 3 | 1同时order_error_log里能看到失败订单的信息。这里PERFORM和GET DIAGNOSTICS的配合点是先探测存在性再用ROW_COUNT判断避开并发RETURN QUERY则提供了清晰的调用结果。6. 实际项目中踩过的坑性能、异常与调试经验6.1 PERFORM与FOUND之间千万别插别的语句我发现很多同事喜欢这么写PERFORM 1 FROM users WHERE id p_uid; RAISE NOTICE checking...; IF FOUND THEN这是错的。FOUND是PL/pgSQL的全局状态它只反映“最近一次执行的SQL语句是否有行”。中间插入RAISE NOTICE其实不改变FOUND因为RAISE NOTICE不是SQL语句但如果插入另一条SQL或SELECT INTO就会覆盖。最保险的写法是立刻快照PERFORM 1 FROM users WHERE id p_uid; v_flag : FOUND;把状态立刻保存到变量里后面再怎么折腾都不怕。不要心存侥幸觉得中间只有一行函数调用没问题什么时候在多线程、多连接环境下出了问题回溯起来很花时间。6.2 GET DIAGNOSTICS要紧跟目标语句在同一个函数里两个UPDATE之间隔着一段循环如果你在第二个UPDATE之后写GET DIAGNOSTICS v ROW_COUNT;拿到的永远是“最近一次执行的SQL”的行数。可能不是你想的那个UPDATE而是FOR UPDATE游标、SELECT、PERFORM等。所以要么紧跟目标语句要么在目标语句后立即读取并存入局部变量。这里的本质是GET DIAGNOSTICS不是“从哪个语句读状态”而是“当前状态是什么”。状态本身是全局的只有马上读取才可靠。你可以把它类比成温度计温度计显示的是当前室温不是一小时前你设定的温度。6.3 EXCEPTION块会拖慢性能要控制粒度PL/pgSQL的BEGIN/EXCEPTION块本质上是一个子事务大量在循环内使用会带来明显开销。上面示例中每行订单都进入一个BEGIN/EXCEPTION块在数据量超过几万时性能会很难看。更好的做法是能提前校验的先校验只在真正可能偶发失败的操作外面包EXCEPTION。我实测过一万行的结转每行都包异常块时耗时约三秒把异常块挪到批量边界后耗时降到零点几秒。性能敏感的存储过程一定要控制异常块粒度能用WHERE条件挡掉的错误就别依赖异常捕获。6.4 调试辅助把DIAGNOSTICS和RAISE NOTICE做成封装每条GET STACKED DIAGNOSTICS都写三行变量赋值很啰嗦。我习惯封装成一个调试函数CREATE FUNCTION log_error_context() RETURNS text AS $$ DECLARE v_sqlstate text; v_msg text; v_ctx text; BEGIN GET STACKED DIAGNOSTICS v_sqlstate RETURNED_SQLSTATE, v_msg MESSAGE_TEXT, v_ctx PG_EXCEPTION_CONTEXT; RETURN format(SQLSTATE:%s MSG:%s CTX:%s, v_sqlstate, v_msg, v_ctx); END; $$ LANGUAGE plpgsql;然后在异常块里EXCEPTION WHEN OTHERS THEN INSERT INTO error_log(err_text) VALUES (log_error_context()); RAISE; END;这样每个异常块只要一行调用代码清爽很多。注意该调试函数内部使用了GET STACKED DIAGNOSTICS只能在EXCEPTION块里被调用别在正常流程里误用否则会报错。6.5 关于RETURN QUERY的内存观感RETURN QUERY并不是一次性把结果集全部物化到内存再返回它本质上是把查询结果投入结果集管道。但如果函数内部先RETURN QUERY一个大查询又继续做大量计算可能会占用外部查询的临时文件。推荐做法是纯查询逻辑尽量用RETURN QUERY复杂计算再一行行RETURN NEXT不要试图在同一个函数里既做大查询又做长循环那样会让执行计划变得难以预测。把它们拆成两个函数反而更好测、更好用。在我自己的项目里组合这些语法写出的存储过程代码量比原来用SELECT INTO加循环的版本少了一半并且边界情况处理得更干净。下次你遇到“只执行不接收”的需求时可以想起PERFORM遇到“执行完想知情”的瞬间可以想起GET DIAGNOSTICS遇到“既想返回明细又想带汇总”的需求可以想起RETURN NEXT与RETURN QUERY的顺序。这几个语法单独背没感觉组合进真实业务流程才能体会到PL/pgSQL写起来其实挺顺手。