1. 为什么你还在当“数据搬运工”先讲个我经常在团队里看到的场景业务给一份Excel开发写个脚本导进数据库然后应用层for循环逐条调接口、做校验、算价格、再回写。系统刚上线时没问题等数据量到了几百万、报表开始按秒卡、接口超时频发的时候所有人第一反应是加机器、加缓存很少有人回头看一眼这些逻辑为什么非要在应用层吭哧吭哧干我做了十几年数据相关工作最深的体会是数据库不只是用来存数据的它本身就是一个计算引擎。你把数据从库里捞出来搬到应用层处理再搬回去这不叫架构设计这叫“数据搬运”。而PostgreSQL这种成熟的关系型数据库内置了完整的存储过程与函数能力完全可以把大量业务规则、数据校验、批量计算、事务控制直接下推到数据库层执行。这篇博文我会用一套能直接上生产的实战案例把 PostgreSQL 存储过程与函数从设计思路、语法细节、性能调优到排错方法整个串一遍。适合这几类人看被“搬运数据”折磨的CRUD工程师、想提升数据库设计能力的中级开发者、以及需要在项目里做数据归档或复杂报表的运维/后端同学。不需要你有多深的基础但最好已经写过一点SQL至少知道INSERT、SELECT长什么样。2. 动手前必须搞懂存储过程与函数的边界2.1 存储过程 vs 函数到底选谁很多人把存储过程和函数混着叫其实在PostgreSQL里它们是两个不同的对象选择错了会直接影响你能不能在一个“过程”里做事务控制。简单说函数FUNCTION必须有返回值可以在SELECT里直接调用像一个高级版的表达式。函数内部不能直接执行COMMIT或ROLLBACK事务边界由调用方控制。存储过程PROCEDURE是PostgreSQL 11开始支持的对象不需要返回值通过CALL调用内部可以用COMMIT和ROLLBACK自主管理事务。我见过不少团队在PG 11以前想用“存储过程”其实写的是函数。这也解释了为什么网上搜PostgreSQL存储过程教程出来的都是CREATE FUNCTION——因为很长一段时间里大家就是用函数来模拟过程的效果。那实际项目里怎么选我的经验是三条场景推荐对象原因要被SELECT、视图、表达式调用函数函数可以嵌在SQL里过程不行整套流程需要自己控制提交/回滚存储过程过程体内可写COMMIT/ROLLBACK简单的数据查询封装函数更灵活复用场景多如果你不确定默认先用函数因为函数的使用场景明显更广报错信息和调试手段也更成熟。2.2 PL/pgSQL 语法骨架先看懂再动手PostgreSQL写存储过程/函数主要用 PL/pgSQL这是PG自带的编程语言语法和Oracle的PL/SQL很像不会写也没关系把它当成“带流程控制的SQL”来理解就好。一个最小可运行的函数长这样CREATE OR REPLACE FUNCTION hello(name text) RETURNS text LANGUAGE plpgsql AS $$ BEGIN RETURN Hello, || name; END; $$;这里面有几个关键点新手经常踩坑$$ ... $$是美元引号用来包裹函数体。之所以不用单引号是避免函数体里再有引号时转义到怀疑人生。写函数体时默认用它。LANGUAGE plpgsql必须写不写PG不知道你用的是哪门语言。也可以写LANGUAGE sql但plpgsql支持条件、循环、变量、异常处理实战基本都选它。CREATE OR REPLACE是覆盖更新但只能改函数体不能改参数列表和返回类型。想改签名就先 DROP 再 CREATE。调用方式有两种SQL里直接查SELECT hello(pg);或者用DO块临时执行一段逻辑DO $$ DECLARE msg text; BEGIN msg : hello(pg); RAISE NOTICE %, msg; END; $$;2.3 环境准备装对版本少踩一半坑写代码最怕环境先出问题。PostgreSQL安装本身不复杂但很多人卡在“装完psql不认识”这一步。热搜里一堆“无法将psql识别为cmdlet、函数、脚本文件”的问题十有八九就是装完没把bin目录加到PATH。Windows下安装时安装向导里有个“Add PostgreSQL bin directory to the PATH”的选项一定要勾上。如果已经装完了才想起这回事手动把C:\Program Files\PostgreSQL\16\bin加到系统环境变量PATH里重开终端即可。Linux下相对简单Debian/Ubuntu系sudo apt update sudo apt install postgresql postgresql-contrib装完默认会创建 postgres 超级用户用sudo -u postgres psql进入命令行。版本方面我建议直接用14以上版本最好16或17。因为存储过程11才有一些性能优化比如存储过程内的更好的计划缓存在12以后才明显改善版本太老你再怎么调优都白搭。准备阶段最后一个动作是建测试库CREATE DATABASE demo; \c demo然后就可以开练了。3. 核心思路把业务逻辑“下沉”到什么程度3.1 数据到底该在哪处理先想清楚一个哲学问题一段业务逻辑放应用层和放数据库层本质区别是什么放应用层的优势是生态丰富、调试直观、容易做复杂的流程编排劣势是每次处理数据都要经过“数据库 - 网络 - 应用内存 - 计算 - 网络 - 数据库”这条链路数据量大时网络开销和序列化开销非常可观。放数据库层的优势是数据不用离开数据库计算在数据所在的地方就地完成配合事务能保证强一致性劣势是写起来不如Java/Python舒服调试手段弱一些。我的判断标准是看“数据的亲密程度”如果一个操作主要依赖数据库内的数据且需要原子性要么全成功要么全失败那就强烈建议下沉到存储过程/函数里。比如订单创建要同时扣库存、写流水、更新汇总结账时要批量计算并更新一堆账户余额。这些操作如果拆在应用层一步步执行任何一个环节挂了都会留下脏数据。3.2 数据库端处理的三笔实在收益我们团队有个报表系统原来做法是应用服务从PG里查出几百万行明细在Java内存里做分组聚合再回写结果表。每次跑批要20多分钟还会把数据库IO打满。后来我用一个存储过程重写在库内直接完成聚合和回写跑批时间缩短到3分钟网络传输量直接少了95%。第二笔收益是事务边界被大大简化。原来应用层要小心翼翼地管理事务调多个接口时得自己做补偿现在把扣库存和创建订单打包进一个函数里要么全部成功要么全部回滚应用层只需要调一次函数省了一堆补偿代码。第三笔收益是权限控制更精细。你可以只给应用账号EXECUTE某个函数的权限而不暴露底层表。这样应用层不能随便DELETE或UPDATE只能按你定义的规则“做事”从根上防止了误操作和数据泄露。3.3 哪些逻辑千万别放数据库里任何方案都有边界存储过程不是万能药。需要调用外部HTTP接口的逻辑千万别在数据库里调。数据库里没有好用的HTTP客户端硬调会阻塞连接而且外部服务的抖动会直接影响数据库稳定性。复杂的字符串处理和文件解析比如PDF解析、复杂的正则清洗数据库里做很痛苦应用层有现成库。需要GPU或大规模并行计算的逻辑PG本身不擅长交给专门的引擎。一句话总结数据库擅长的是“集合操作 事务一致性”应用层擅长的是“复杂编排 外部交互”。各司其职才是正解。4. 实战案例用一套订单核心流程打通全部技巧接下来是重头戏我会从一个电商订单的核心交易链路出发带大家一步步写出可用的存储过程和函数。这套代码我抽掉了具体业务噪音但核心思路和线上是一致的。4.1 业务背景与表结构设计场景下单时系统要从商品表扣减库存创建订单主表和明细表写一条库存流水并返回新订单ID。整个过程要保证原子性任何一个环节失败所有修改都回滚。先建表CREATE TABLE products ( id bigserial PRIMARY KEY, sku varchar(32) UNIQUE NOT NULL, name varchar(128) NOT NULL, stock_qty integer NOT NULL DEFAULT 0, price numeric(10,2) NOT NULL, updated_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE orders ( id bigserial PRIMARY KEY, order_no varchar(32) UNIQUE NOT NULL, customer_id bigint NOT NULL, total_amount numeric(12,2) NOT NULL, status smallint NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE order_items ( id bigserial PRIMARY KEY, order_id bigint NOT NULL REFERENCES orders(id), product_id bigint NOT NULL REFERENCES products(id), quantity integer NOT NULL, price numeric(10,2) NOT NULL ); CREATE TABLE stock_log ( id bigserial PRIMARY KEY, product_id bigint NOT NULL, order_no varchar(32), change_type varchar(16) NOT NULL, qty_before integer NOT NULL, qty_after integer NOT NULL, created_at timestamptz NOT NULL DEFAULT now() );为什么这么设计三个细节值得说order_items 里冗余了 price。下单时商品价格可能变订单明细必须“冻结”当时的成交价。这是订单系统的基本功。stock_log 记录 qty_before 和 qty_after。流水表不是为了记“减了2件”而是要能追溯“从10变8”。对不上账的时候靠这张表复盘。orders.order_no 用了 UNIQUE 约束。这是幂等设计的前提防止同一个订单号被重复创建。4.2 下单核心函数事务、行锁与并发控制创建订单的函数如下CREATE OR REPLACE FUNCTION create_order( p_customer_id bigint, p_order_no text, p_items jsonb ) RETURNS bigint LANGUAGE plpgsql AS $$ DECLARE v_order_id bigint; v_total numeric(12,2) : 0; v_product record; v_qty integer; v_old_stock integer; v_new_stock integer; BEGIN -- 1. 校验重复订单号 IF EXISTS (SELECT 1 FROM orders WHERE order_no p_order_no) THEN RAISE EXCEPTION 订单号已存在: %, p_order_no USING ERRCODE P0001; END IF; -- 2. 插入订单主表 INSERT INTO orders (order_no, customer_id, total_amount) VALUES (p_order_no, p_customer_id, 0) RETURNING id INTO v_order_id; -- 3. 遍历订单明细 FOR v_product IN SELECT * FROM jsonb_to_recordset(p_items) AS items(product_id bigint, quantity int) LOOP -- 行级锁锁定商品行阻止并发修改 SELECT stock_qty INTO v_old_stock FROM products WHERE id v_product.product_id FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 商品不存在: %, v_product.product_id; END IF; IF v_old_stock v_product.quantity THEN RAISE EXCEPTION 库存不足: 商品ID % 仅剩 % 件, v_product.product_id, v_old_stock; END IF; v_new_stock : v_old_stock - v_product.quantity; -- 4. 扣减库存 UPDATE products SET stock_qty v_new_stock, updated_at now() WHERE id v_product.product_id; -- 5. 写库存流水 INSERT INTO stock_log (product_id, order_no, change_type, qty_before, qty_after) VALUES (v_product.product_id, p_order_no, ORDER, v_old_stock, v_new_stock); -- 6. 累计金额 SELECT price INTO v_product FROM products WHERE id v_product.product_id; v_total : v_total v_product.price * v_product.quantity; -- 7. 插入订单明细 INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (v_order_id, v_product.product_id, v_product.quantity, v_product.price); END LOOP; -- 8. 更新订单总金额 UPDATE orders SET total_amount v_total WHERE id v_order_id; RETURN v_order_id; END; $$;这段代码看起来长实际上串了五个核心知识点第一行锁FOR UPDATE。这是并发控制的重中之重。两个用户同时下单同一件商品时如果都不加锁都读到库存是10都扣到8最终库存错乱。加上FOR UPDATE第二个事务会一直等到第一个事务提交或回滚然后读到最新的库存值。没有这把锁整个函数就是有并发隐患的。第二异常处理。RAISE EXCEPTION会直接终止函数并让整个事务回滚。这也是函数不能自己COMMIT的原因它必须把“决定权”交给最外层调用方由调用方决定最终提交还是全部回滚。如果你用存储过程来写内部可以COMMIT但一旦中间出了错前面已提交的修改是回不去的所以核心交易逻辑我反而更推荐函数。第三变量命名。我用了p_前缀表示入参v_前缀表示局部变量。这是PG社区常见的命名规范能有效防止“变量名和列名冲突”这种经典问题。如果你声明一个变量叫status表里也有字段叫statusPL/pgSQL 会优先当变量解析很容易埋雷。第四jsonb_to_recordset。这是接收复杂参数的利器。应用层只需要把购物车里的商品列表序列化成 JSON 数组传进来数据库就能自动展开成行。这比传一堆零散参数优雅得多也方便扩展。第五RETURNING id INTO。插入后直接把自增主键取回来省掉一次SELECT。调用方式SELECT create_order( 1001, ORD20250101001, [{product_id: 1, quantity: 2}, {product_id: 2, quantity: 1}]::jsonb );如果一切正常会返回订单ID。为了确认效果可以查库存和流水SELECT * FROM products ORDER BY id; SELECT * FROM stock_log ORDER BY id; SELECT * FROM orders;我再提示一个并发测试方法开两个psql窗口同时执行同一个下单调用观察其中一个是否会出现等待。当第一个窗口执行COMMIT后第二个窗口才会继续执行。这就是FOR UPDATE的作用。4.3 批量订单归档游标、事务块和批量操作第二个真实场景是数据归档。线上订单表越来越大查询越来越慢常规做法是把N个月前的订单迁移到历史表。这里有个常见误区直接在存储过程里写一条INSERT INTO archive SELECT ... WHERE created_at ...然后DELETE FROM orders WHERE ...。数据量小没问题但线上几百万行的大事务会长时间持有锁主库都跟着遭殃。更稳的做法是分批处理每批处理一小部分就提交一次避免大事务。代码如下CREATE OR REPLACE PROCEDURE archive_orders(p_before_date date) LANGUAGE plpgsql AS $$ DECLARE v_batch_size constant int : 5000; v_affected int; BEGIN LOOP -- 每批最多归档5000条 WITH batch AS ( SELECT id FROM orders WHERE created_at p_before_date AND status 2 -- 只归档已完成订单 ORDER BY id LIMIT v_batch_size FOR UPDATE SKIP LOCKED ), move_detail AS ( INSERT INTO order_items_archive SELECT oi.* FROM order_items oi JOIN batch b ON oi.order_id b.id ) INSERT INTO orders_archive SELECT o.* FROM orders o JOIN batch b ON o.id b.id; GET DIAGNOSTICS v_affected ROW_COUNT; DELETE FROM orders o USING batch b WHERE o.id b.id; COMMIT; EXIT WHEN v_affected v_batch_size; END LOOP; END; $$;我特意用了存储过程而不是函数原因在于循环里需要COMMIT函数做不到。每处理5000条就提交一次锁的持有时间被限制在很短的范围内。FOR UPDATE SKIP LOCKED是另一个关键。归档任务如果同时跑多个实例每个实例会锁定自己那份数据不会互相抢SKIP LOCKED 让后来的实例跳过已经被锁的行而不是傻等。这对并行批处理非常友好。调用方式CALL archive_orders(2024-01-01::date);注意归档前必须先把order_items_archive和orders_archive两张表建好表结构和原表保持一致。我一般在生产环境用CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);来快速建表。这个案例回答了很多人对“存储过程和函数到底怎么选”的疑惑当你的业务逻辑是一个独立的、需要自己控制事务边界的任务时选存储过程当你要写的是被其他SQL复用的计算单元选函数。4.4 动态报表查询安全使用动态SQL第三个场景是报表。运营提了一个需求按天/按周/按月统计订单金额按商品/按客户/按地区分组。如果用一条固定SQL写死每次改动都得发版。用动态SQL就灵活了。下面这个函数可以根据传入的分组列名和日期范围生成不同维度的报表CREATE OR REPLACE FUNCTION get_order_summary( p_group_col text, p_start date, p_end date ) RETURNS TABLE(group_name text, total_amount numeric, order_count bigint) LANGUAGE plpgsql AS $$ DECLARE v_sql text; BEGIN -- 白名单校验只允许指定字段避免SQL注入 IF p_group_col NOT IN (product_id, customer_id, status) THEN RAISE EXCEPTION 非法的分组字段: %, p_group_col; END IF; v_sql : format( SELECT %I::text AS group_name, SUM(oi.quantity * oi.price)::numeric(12,2) AS total_amount, COUNT(DISTINCT o.id)::bigint AS order_count FROM orders o JOIN order_items oi ON oi.order_id o.id WHERE o.created_at %L::date AND o.created_at %L::date GROUP BY %I ORDER BY total_amount DESC, p_group_col, p_start, p_end, p_group_col ); RETURN QUERY EXECUTE v_sql; END; $$;这里有一个安全细节我必须强调动态SQL最怕SQL注入。我用了一个白名单校验字段不在允许列表里就直接报错。同时format()函数里的%I会把标识符安全转义%L会把字面量安全转义。这些手段组合起来意味着如果你传入p_group_col id; DROP TABLE orders;--白名单会直接把它拦下来。为什么要用动态SQL而不是写三条固定SQL核心原因是减少维护成本。报表维度一多每个维度一条SQL后期改逻辑要改好几处还容易漏。动态SQL把变化的部分参数化规则统一后期只需维护这一处。调用方式SELECT * FROM get_order_summary(product_id, 2025-01-01, 2025-02-01); SELECT * FROM get_order_summary(customer_id, 2025-01-01, 2025-02-01);5. 把存储过程做稳性能、错误处理与排错速查5.1 性能设计避免“慢SQL制造机”写存储过程不等于性能好写得不好反而会变成性能黑洞。第一个通病在循环里逐条处理集合。能用一条UPDATE搞定的事别在LOOP里写一万次UPDATE。PL/pgSQL在循环里的逐行操作每行都要做一次SQL解析和执行计划生成性能损耗非常大。如果实在要循环尽量把操作封装成数组批量处理。第二个通病忽略了执行计划缓存。PL/pgSQL默认会对简单的SQL语句做计划缓存同一函数第二次执行时复用第一次的执行计划。这个机制对大多数场景是优点但如果表数据分布极不均匀或者查询条件变化极大旧计划可能不适用。遇到复杂的动态SQL建议在函数里用EXECUTE ... USING它会每次生成新的执行计划保证准确。第三个通病函数内不做数据量级判断。比如归档任务如果一张表只有1000条让函数一上来就跑批次循环每个批次提交一次纯属脱裤子放屁。合理做法是加个初判如果总行数小于某个阈值直接一次搬完否则走分批。5.2 错误处理与可观测性生产环境的函数必须能“说得清自己干了什么”。一个很容易被忽视的设计是让函数返回执行日志或状态码而不是只返回一个成功/失败。比如批量归档函数我通常会加个返回值表明这轮归档了多少条、耗时多久。PL/pgSQL里可以这么写RAISE NOTICE 批次执行完成, 处理 % 条, 耗时 % ms, v_affected, clock_timestamp() - v_start_time;如果是关键商业逻辑还需要把错误信息记录到专门的日志表CREATE TABLE func_log ( id bigserial PRIMARY KEY, func_name text NOT NULL, params jsonb, error_msg text, created_at timestamptz NOT NULL DEFAULT now() );函数里用EXCEPTION WHEN OTHERS THEN捕获异常并写日志BEGIN -- 业务逻辑 EXCEPTION WHEN OTHERS THEN INSERT INTO func_log (func_name, params, error_msg) VALUES (create_order, to_jsonb(p_items), SQLERRM); RAISE; END;注意最后一定要RAISE把异常重新抛出去否则应用层会以为成功了结果数据没变这是最坑的。5.3 常见问题与排查技巧速查表我把平时群里问得最多的问题整理成了表格每一条都对应真实的踩坑现场症状可能原因解决办法psql不是内部或外部命令/cmdlet无法识别安装时未把bin目录加入PATH把PostgreSQL安装目录的bin加进系统PATH重开终端用CALL调用报错“函数不存在”把存储过程写成了函数或版本低于PG 11用CREATE FUNCTION重写或用CALL调用CREATE PROCEDURE创建的对象函数里写COMMIT报语法错误函数不允许事务控制改用存储过程或者把事务边界挪到调用方插入中文乱码客户端编码和数据库编码不一致SET client_encoding UTF8;或PGCLIENTENCODINGUTF8查询一直很慢函数里加个循环后更慢循环内逐条执行SQL产生了大量计划解析改写为集合操作/批量SQL减少循环次数并发下单时库存超卖忘了SELECT FOR UPDATE查询库存时加FOR UPDATE锁定目标行报表函数返回列名含义不清晰RETURNS TABLE里字段名和调用方预期不一致定义RETURNS TABLE时就明确字段名并把注释写清楚动态SQL报语法错误但又看不出问题个别标识符没正确转义用format()的%I和%L转义必要时先RAISE NOTICE打印生成的SQL权限不足无法执行函数应用账号没有EXECUTE权限GRANT EXECUTE ON FUNCTION xxx TO app_user;最后分享一个我自己的排错习惯写复杂函数之前先把SQL单独跑通再把SQL包进函数。很多人在函数里写错了SQL报错信息被PL/pgSQL包装得面目全非排查难度直接翻倍。先在psql里把核心SQL验证好再搬进函数体能省掉70%的调试时间。6. 我在实际项目中的几点经验文章写到这主体内容差不多结束了。最后分享几个我在真实项目中踩过的坑和沉淀下来的习惯。第一个经验核心交易逻辑用函数ETL批处理用存储过程。这是我目前最稳定的分类标准。交易逻辑要求强一致函数天然适合配合外部事务批处理要求自己控制提交节奏存储过程最合适。第二个经验任何函数都要考虑“会不会被并发调用”。我在一个库存系统里曾因为没有加FOR UPDATE上线第二天就出现了超卖被运维半夜叫起来修数据那种痛苦至今难忘。从那以后凡是更新前需要读旧值的逻辑我默认先加锁。第三个经验存储过程里的动态SQL一定一定要做白名单校验。即使你认为自己写的接口不会给用户传任意字段的机会也要防一手。因为你不知道未来哪个同事为了省事会把这个函数直接暴露出去。白名单校验几行代码换来的是不担惊受怕。说实话我并不是建议大家把所有逻辑都往数据库里塞那样会走向另一个极端。我的建议是凡是“数据亲密度高、需要原子性、重复执行频率高”的操作优先考虑用存储过程或函数封装。一旦你习惯了这个思路你会发现应用层代码清爽了一大截系统性能和一致性也会有质的提升。这套实战案例里的代码都能在本地直接跑通。你可以动手建库建表把函数逐个写进去跑一遍再加点并发测试看看效果感受会完全不一样。