先说个我自己的经历。前几年做一个营销数据中台每天凌晨要从订单库同步几百万行明细到分析库然后按用户、按商品、按渠道做汇总。最初的方案很典型写一个 Java 定时任务把明细全查出来在内存里分组求和再一批一批写回 PostgreSQL。结果业务量一上来任务从半小时变成两个半小时还经常 OOM。后来被逼无奈把整个加工逻辑重写成 PostgreSQL 存储过程加函数直接在数据库里跑执行时间从两个半小时压到四分钟。那次重构给我的冲击非常大——很多问题其实是架构姿势的问题数据管道并不一定要靠应用层“搬砖”。“数据搬运工”这个词说的就是那种把数据库当纯存储、把所有加工逻辑都搬到应用层的开发方式。PostgreSQL 存储过程与函数不是不能用的老古董恰恰是解决这类问题的高效工具。这篇文章我不打算讲理论和概念而是从一个实际写过大量 PL/pgSQL 的人角度讲清楚它到底能解决什么问题、怎么写才不容易踩坑并给出可以直接抄的实战案例。1. 为什么你还在当“数据搬运工”职责划分决定架构上限1.1 “搬运工”式开发的典型症状很多人一听到“存储过程”第一反应是“这东西不是老系统才用吗”“写了不好维护”。但实际上我见过的大量性能事故根源不是存储过程太慢而是应用层把数据拉来拉去太慢。典型的“搬运工”代码长这样先select几百万行数据到应用服务器内存用 Java/Python 写循环一条条处理或分组聚合再开一个事务把结果逐条update/insert回 PostgreSQL如果数据量再大点就上分页、多线程、断点续传。这套玩法的隐形成本非常高应用服务器和数据库服务器之间要传海量原始数据网络 IO 和 JSON/ORM 序列化消耗大量资源应用层循环处理几百万行GC 和内存压力都很大而且一旦中间某一步出错你得自己处理断点、重试和幂等。1.2 数据库层加工的优势在哪里PostgreSQL 的存储过程与函数本质上就是把“数据的加工逻辑”放在离数据最近的地方执行。它的核心优势有三个第一免去大量数据搬运。汇总、过滤、关联、去重这些操作在数据库内部完成应用层只拿最终结果网络 IO 量级可能差几百倍。第二事务和一致性由数据库保证。在 PL/pgSQL 里一批数据要么全部成功要么全部回滚不用在应用层自己拼事务边界。第三批量操作的性能远高于逐行操作。PostgreSQL 在存储过程里做集合操作INSERT ... SELECT、UPDATE ... FROM、DELETE USING是引擎内部优化过的比应用循环一条条改快几个数量级。1.3 什么场景适合下沉到数据库不是所有逻辑都适合写进存储过程。以我的经验这几类场景最值得下沉批量数据加工ETL、日报/月报统计、数据归档、清洗、去重复杂报表查询多表关联、窗口函数、递归查询用函数封装成参数化报表入口定时任务要执行的操作配合 pg_cron 或系统定时调度直接在库里完成需要强事务保证的多步数据变更比如先更新主表、再写流水、最后更新汇总表。如果你只是给前端提供一个简单的 CRUD 接口那用 ORM 和普通 SQL 完全没问题没必要为了用而用。这篇文章讲究的是在它该发挥价值的地方把它用好。2. 存储过程与函数的语法骨架先写对再写快2.1 函数和存储过程的区别SELECT 与 CALL 只是表面差异PostgreSQL 从 11 版本开始真正支持存储过程CREATE PROCEDURE在这之前只有函数CREATE FUNCTION。两者最常见的区别是调用方式函数用SELECT调用可以嵌在 SQL 里存储过程用CALL调用不能直接嵌在查询里。但更深层的区别在事务控制上。先说函数它运行在 SQL 表达式上下文里默认不允许自己提交或回滚事务。一个函数如果中途报错整个语句失败由外层事务处理回滚。这个特性其实很安全适合作为“计算单元”或“数据访问接口”。存储过程则不同它可以在内部使用COMMIT和ROLLBACK适合做需要分段提交的批处理任务。比如归档一个月的历史数据每处理一万条提交一次避免一个大事务把事务日志撑爆。这在函数里做不到在存储过程里则是常规操作。2.2 一个最小可运行示例从建表到写函数我们不说空话先建两张测试表然后写第一个函数。CREATE TABLE sales ( id serial PRIMARY KEY, product_id integer NOT NULL, sale_date date NOT NULL, amount numeric(12,2) NOT NULL ); CREATE TABLE product ( id integer PRIMARY KEY, name text NOT NULL, category text ); INSERT INTO product VALUES (1, 机械键盘, 外设), (2, 显示器, 外设), (3, 升降桌, 家具);现在写一个函数根据产品分类统计某段时间内的销售额。CREATE OR REPLACE FUNCTION get_category_sales( start_date date, end_date date, cat text ) RETURNS TABLE ( category text, total_amount numeric ) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT p.category, SUM(s.amount)::numeric(12,2) FROM sales s JOIN product p ON p.id s.product_id WHERE s.sale_date BETWEEN start_date AND end_date AND (cat IS NULL OR p.category cat) GROUP BY p.category ORDER BY p.category; END; $$;调用方式SELECT * FROM get_category_sales(2025-01-01, 2025-01-31, NULL);这里有几个关键点值得说明RETURNS TABLE是返回结果集的常见方式比RETURNS SETOF record直观调用方直接当普通表查LANGUAGE plpgsql是 PostgreSQL 默认的过程语言支持变量、循环、异常处理也是本文的主力语言CREATE OR REPLACE FUNCTION只能替换函数体不能修改参数列表和返回类型如果签名变化需要先DROP函数默认是VOLATILE的如果函数只做查询不修改数据可以标记为STABLE或IMMUTABLE能提升在复杂查询里的优化空间。2.3 存储过程的写法CALL 入口与分段事务示例存储过程的语法跟函数很像最大差异在于可以用事务控制语句。下面是一个历史数据归档存储过程的简化版本CREATE OR REPLACE PROCEDURE archive_sales( up_to_date date ) LANGUAGE plpgsql AS $$ DECLARE v_batch_size integer : 10000; v_moved integer : 0; BEGIN LOOP WITH batch AS ( SELECT id FROM sales WHERE sale_date up_to_date ORDER BY id LIMIT v_batch_size FOR UPDATE SKIP LOCKED ), del AS ( DELETE FROM sales WHERE id IN (SELECT id FROM batch) RETURNING * ) INSERT INTO sales_archive SELECT * FROM del; GET DIAGNOSTICS v_moved ROW_COUNT; RAISE NOTICE 归档 % 行, v_moved; COMMIT; EXIT WHEN v_moved v_batch_size; END LOOP; END; $$;调用CALL archive_sales(2024-12-31);这个例子演示了三个实用技巧使用LIMIT ... FOR UPDATE SKIP LOCKED分批取数据避免多个归档任务并发时互相阻塞每批COMMIT一次防止大事务GET DIAGNOSTICS v_moved ROW_COUNT获取上一条语句影响的行数。注意存储过程里的COMMIT会结束当前事务并开启新事务所以如果过程中途失败已提交批次不会回滚。这既是优点也是缺点后面我会专门讲什么时候该用它、什么时候不该用。2.4 变量、循环与流程控制别把 SQL 写成游标地狱PL/pgSQL 支持IF/ELSIF、CASE、FOR、WHILE、LOOP等各种控制结构。但我想劝一句能用一条 SQL 完成的事情绝对不要用循环。存储过程不是用来逐行处理数据的它是用来组织 SQL 的。比如你要给每个订单生成一个流水号新手可能写成FOR r IN SELECT * FROM orders WHERE status new LOOP INSERT INTO order_log(order_id, log_time, content) VALUES (r.id, now(), 订单已创建); END LOOP;这种写法性能很差正确的做法是INSERT INTO order_log(order_id, log_time, content) SELECT id, now(), 订单已创建 FROM orders WHERE status new;同样的业务一条 SQL 完成效率差距几十倍。游标和循环只适合真正无法集合化的场景比如逐行调用外部 API、逐行解析不规则文本、或者数据处理依赖上一行的计算结果。3. 三个拿来就能用的实战案例统计、归档、动态查询3.1 案例一销售日报汇总函数带参数默认值日报统计是每个业务系统都有的需求。写一个带默认参数、返回结果集的函数既能让应用层少写很多代码也能让 SQL 逻辑收敛到一处。CREATE OR REPLACE FUNCTION daily_sales_report( report_date date DEFAULT CURRENT_DATE ) RETURNS TABLE ( product_id integer, product_name text, category text, order_count bigint, total_amount numeric(12,2) ) LANGUAGE plpgsql STABLE AS $$ BEGIN RETURN QUERY SELECT p.id, p.name, p.category, COUNT(s.id) AS order_count, COALESCE(SUM(s.amount), 0)::numeric(12,2) AS total_amount FROM product p LEFT JOIN sales s ON s.product_id p.id AND s.sale_date report_date GROUP BY p.id, p.name, p.category ORDER BY total_amount DESC; END; $$;调用-- 查指定日期 SELECT * FROM daily_sales_report(2025-03-18); -- 不传参数默认今天 SELECT * FROM daily_sales_report();这里用LEFT JOIN是为了让没有销量的产品也出现报表更完整。标记为STABLE是因为函数内不修改数据对于相同输入在同一个事务内结果一致PostgreSQL 在查询优化时可以做更多优化。3.2 案例二动态 SQL 与防注入的参数化查询业务里经常有“可选筛选条件”的查询需求用户可能传产品分类、可能传日期范围、可能传最低金额组合不定。最忌讳的做法是凭字符串拼接 SQL那既容易出错又有 SQL 注入风险。PL/pgSQL 里的EXECUTE ... USING可以动态拼接结构但参数值用占位符传入从根上规避注入。CREATE OR REPLACE FUNCTION search_sales( p_category text DEFAULT NULL, p_min_amount numeric DEFAULT NULL, p_start_date date DEFAULT NULL, p_end_date date DEFAULT NULL ) RETURNS TABLE ( sale_id integer, product text, category text, sale_date date, amount numeric(12,2) ) LANGUAGE plpgsql AS $$ DECLARE v_sql text; BEGIN v_sql : SELECT s.id, p.name, p.category, s.sale_date, s.amount FROM sales s JOIN product p ON p.id s.product_id WHERE 1 1; IF p_category IS NOT NULL THEN v_sql : v_sql || AND p.category $1; END IF; IF p_min_amount IS NOT NULL THEN v_sql : v_sql || AND s.amount $2; END IF; IF p_start_date IS NOT NULL THEN v_sql : v_sql || AND s.sale_date $3; END IF; IF p_end_date IS NOT NULL THEN v_sql : v_sql || AND s.sale_date $4; END IF; v_sql : v_sql || ORDER BY s.sale_date DESC, s.id DESC; RETURN QUERY EXECUTE v_sql USING p_category, p_min_amount, p_start_date, p_end_date; END; $$;注意看动态拼接的只是固定的 SQL 片段所有外部变量都通过USING传进去PostgreSQL 会按参数处理不会把p_category的值解释成 SQL 代码。这是我见过最容易被写坏的场景很多人图省事直接用字符串拼接结果上线没几天就出问题。这个写法值得当模板收藏。3.3 案例三带事务控制的批量价格调整存储过程电商运营经常要批量调价比如“某分类下所有商品涨价 5%但不得超过某个上限”。这类操作必须在事务里执行要么全成功要么全回滚。CREATE OR REPLACE PROCEDURE batch_adjust_price( p_category text, p_rate numeric, p_max_price numeric ) LANGUAGE plpgsql AS $$ DECLARE v_affected integer; BEGIN UPDATE product SET price LEAST(price * (1 p_rate), p_max_price) WHERE category p_category AND price p_max_price; GET DIAGNOSTICS v_affected ROW_COUNT; RAISE NOTICE 受影响商品数: %, v_affected; IF v_affected 0 THEN RAISE EXCEPTION 没有商品需要调价操作已回滚; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END; $$;这个过程的好处是如果调价条件匹配不到任何商品直接抛异常回滚避免“静默成功”。如果执行到一半遇到唯一约束冲突等错误异常块会回滚整个事务。注意存储过程里的COMMIT会让之前未提交的数据变更对外可见。在并发量高的生产环境这种操作最好放在业务低峰期跑。而且它不会破坏外层调用方的事务因为CALL一个事务控制型存储过程时它管理的是自己的一段事务边界。4. 深入坑底事务边界、锁等待与函数内的查询性能4.1 函数内不能 COMMIT这不是缺陷是自我保护很多从 Oracle 或 SQL Server 转过来的开发者习惯在存储过程里到处COMMIT到了 PostgreSQL 里发现函数不允许容易被误导成“功能残缺”。实际上PostgreSQL 的设计有其道理函数是 SQL 表达式的一部分如果函数里提交了事务外层 SQL 的原子性就崩溃了。想象一下SELECT * FROM some_function();如果some_function里提交了事务这条SELECT本身就不是原子操作了中途失败回滚时会发现前面的提交回不去了数据一致性没法保证。所以函数内的禁止COMMIT是底层约束不是缺功能。要分段提交用CREATE PROCEDURE。4.2 锁等待与死锁批处理最容易忽视的坑存储过程里的批处理最怕的不是慢而是“跟别人互相锁”。我见过一个实际案例归档任务半夜跑跟业务侧的一个大查询锁冲突两边一直等最后等到了锁超时任务失败第二天早上业务方发现数据没归档。排查定位到的是归档 SQL 里用了FOR UPDATE而且没有SKIP LOCKED。我的经验是批处理任务要加SKIP LOCKED跳过正在被其他事务处理的行宁可这一批少处理一点也不要卡死整个任务批次之间要COMMIT缩短持锁时间尽量按主键或索引顺序处理避免多个任务互相以不同顺序加锁导致死锁不要在长事务里做大量写操作锁的持有时间越长冲突概率指数上升。4.3 函数里的 SQL 写法决定性能上限同样的业务逻辑写法不同性能可能差几十倍。最常见的几个性能杀手杀手一函数内逐行查询。在循环里写SELECT ... INTO每循环一次执行一次 SQL几万次循环就是几万次查询。正确做法是用RETURN QUERY或者把结果先装入临时表再一次性处理。杀手二缺少合适索引。函数里做WHERE sale_date BETWEEN ...但表上没有索引全表扫描几百万行自然慢。写函数前先检查执行计划这是基本素养。杀手三函数内使用VARCHAR或TEXT做不必要的隐式转换。比如拿text列跟integer参数比较PostgreSQL 可能会做隐式类型转换导致索引失效。杀手四大量使用NOT IN而不是NOT EXISTS。当子查询结果里有NULL时NOT IN会直接返回空结果而且性能往往不如NOT EXISTS。在 PL/pgSQL 里这个问题一样存在写代码时要敏感。4.4search_path、权限与SECURITY DEFINER隐性问题引起的线上故障PostgreSQL 的对象解析依赖search_path。如果你的函数里写的是不带模式名的表名而数据库用户有多个 schema 包含同名表函数执行时解析到哪张表取决于search_path。这会导致一个非常隐蔽的问题开发环境正常生产环境报“表不存在”或者更可怕的“操作了错误的表”。我的建议是函数体内所有表名都带上 schema比如public.sales在函数定义时设置SET search_path public, pg_temp如果使用SECURITY DEFINER以函数属主身份执行务必同时收紧search_path否则有提权风险。SECURITY DEFINER是另一个高频踩坑点。它适合“普通用户调用但需要更高权限操作”的场景但权限是把双刃剑不限制好搜索路径就可能被恶意用户引导去操作不可控的对象。能不用尽量不用实在要用配合REVOKE和SET search_path一起来。5. 调试、测试与长期维护让 PL/pgSQL 代码活得久一点5.1 用RAISE NOTICE和GET DIAGNOSTICS快速定位问题写 PL/pgSQL 最原始也最高效的调试方式就是打日志。RAISE NOTICE会输出到客户端和 PostgreSQL 日志定位问题非常直接。CREATE OR REPLACE FUNCTION debug_test() RETURNS void LANGUAGE plpgsql AS $$ DECLARE v_count integer; BEGIN SELECT COUNT(*) INTO v_count FROM sales; RAISE NOTICE 当前 sales 表行数: %, v_count; GET DIAGNOSTICS v_count ROW_COUNT; RAISE NOTICE 上一条 SQL 影响行数: %, v_count; END; $$;GET DIAGNOSTICS还能获取返回的状态比如RESULT_OID、PG_CONTEXT。PG_CONTEXT特别有用能告诉你异常发生在哪个函数、哪一行配合异常捕获排错很快。5.2 轻量单元测试方案断言函数也能顶半边天大型项目可以上pgTAP这种专业的 PostgreSQL 测试框架。但很多中小项目用不上那么重的东西我分享一个轻量做法写一个异常断言函数专门验证“某个函数是否应该报错”。CREATE OR REPLACE FUNCTION expect_error( p_sql text ) RETURNS boolean LANGUAGE plpgsql AS $$ BEGIN EXECUTE p_sql; RAISE EXCEPTION 预期会失败但没有失败: %, p_sql; EXCEPTION WHEN OTHERS THEN RETURN true; END; $$;测试时SELECT expect_error($$SELECT * FROM no_such_table()$$); -- 返回 true说明函数正确报了错这套思路配合事务型测试测试完回滚可以做到快速回归。关键是把“测试 SQL”集中到一个 schema 里跑完直接ROLLBACK不影响业务数据。5.3 版本管理与命名规范没人愿意维护“天书”PL/pgSQL 代码维护性差很多时候不是语言本身的问题而是写的人没把代码当工程。我给自己定的几条规矩命名规范函数名以业务动宾结构开头比如get_sales_total、archive_old_data、adjust_product_price游标命名用cur_前缀变量用v_前缀常量用c_前缀注释必须写“为什么”比如“这里不能用 NOT IN因为历史遗留数据有空值”而不是写“查询销售表”这种废话用迁移工具管版本不用手动去生产库执行 SQL而是把函数定义放进 Flyway 或 Liquibase 的变更脚本里每次修改就是一个新的版本脚本带上CREATE OR REPLACE可追溯、可回滚。我在实际项目里还发现一个实用技巧把函数定义脚本和调用示例放一起形成一个“接口文档”团队其他人看的时候能立刻知道入参出参和业务效果比单独维护文档靠谱得多。5.4 监控函数执行时长哪些函数在偷偷消耗性能最后提一个容易忽略的点函数写得再漂亮也要监控才能真正知道好坏。PostgreSQL 的pg_stat_user_functions视图可以统计函数的调用次数和总耗时SELECT schemaname, funcname, calls, total_time, self_time FROM pg_stat_user_functions ORDER BY total_time DESC LIMIT 20;当然这个视图默认不记录需要先开启track_functionsALTER SYSTEM SET track_functions pl; SELECT pg_reload_conf();pl表示只跟踪过程语言函数all会连 SQL 函数一起统计消耗会更大。我一般用pl就够了重点观察那些total_time惊人的函数再针对性地打开EXPLAIN ANALYZE去看内部 SQL。5.5 跨版本兼容从 PostgreSQL 11 到 17 的变化PostgreSQL 的存储过程和函数语法整体非常稳定但版本升级时偶尔有细微变化。我的经验是PostgreSQL 11 引入CREATE PROCEDURE如果要支持 11 以下版本只能用函数模拟批处理PostgreSQL 14 开始COMMIT AND CHAIN在存储过程里也被支持可以无缝延续事务特性变量PostgreSQL 15 对json/jsonb的处理加强函数里操作 JSON 数据更方便如果项目用的老版本可以先在测试库跑一遍所有函数定义脚本大部分兼容问题在执行阶段就会暴露。我个人的建议是尽量使用较新的版本至少 PostgreSQL 14 起步这样存储过程的事务控制能力、date/interval运算和jsonb处理都比较完善写起代码也更顺手。收尾一个长期收益极高的投资坦白说我从一开始也是抗拒写存储过程的觉得在应用层写代码更“现代”。但在经历了几次大数据量加工的性能事故又把一部分核心逻辑迁移到 PostgreSQL 存储过程和函数之后我的态度发生了很大变化。如果你也在维护一个报表系统、一个定时数据处理任务、或者一个复杂查询接口我建议你先别急着在应用层堆代码花一个下午试试用函数封装一段查询逻辑或用存储过程重写一个批量任务对比一下执行计划和耗时。用 PL/pgSQL 写东西前期需要一点学习成本但它降低的是长期的数据传输和运维成本。另外分享一个我现在的习惯任何一段新写的 PL/pgSQL 函数我都要求自己给出至少一个调用示例和一组预期结果。这样三个月后再回来看这段代码不需要回忆当时想了什么直接看注释和调用示例就能恢复上下文。PostgreSQL 存储过程与函数不是让你放弃应用层而是帮你把“数据问题”和“业务问题”分得更清楚。数据加工尽量在数据的地方完成应用层专注流程编排、权限控制和用户体验这才是分工合理的架构。希望这篇文章能让你少走一些我走过的弯路。
