PostgreSQL 写个函数说难不算难说简单吧真要写得规范、跑得稳、又能处理各种边界情况没点踩坑经验还真不行。我在项目里见过不少同事一提到写数据库函数就发怵总觉得 SQL 是一次性查询能跑就行结果业务一复杂几十上百行的重复 SQL 散落在各个服务里改一次需求牵一发动全身。其实 PostgreSQL 的函数体系非常完整从最简单的 SQL 函数到支持分支、循环、异常处理的 PL/pgSQL再到能把 Python 逻辑搬进数据库的 PL/Python基本覆盖了在数据库内部做逻辑处理的绝大多数需求。这篇文章我不打算通篇贴官方文档而是直接从一个真实可跑的函数入手把语法、设计思路、性能注意事项、高频报错排查一次讲清楚。适合这几类人看每天都在写 SQL 的后端开发、做数据分析和报表的运维同学以及刚接触 PostgreSQL 想系统学函数的初学者。文章里的示例我都用 PG 16 实测过你直接复制到自己的环境里把表名和字段名换成自己的基本就能跑起来。1. 拿到需求先别急着写函数该不该用、怎么设计1.1 什么时候值得写函数什么时候不值得先解决一个最容易被忽略的问题你真的需要函数吗我在实际项目里总结过一个很简单的判断标准——如果你发现同一段 SQL 逻辑在三个以上的地方出现而且每次都要带着一堆 join 条件、状态过滤、金额计算复制粘贴那把它收敛进函数就非常值。举个例子订单系统里统计“某个用户在指定时间段内的有效订单金额”后台管理、定时任务、报表导出三个地方都要用每次重写很容易在某个地方漏掉状态过滤条件造成数据对不上。收敛到一个函数里调用方只传用户 ID 和日期范围逻辑只维护一份改一处处处生效这就是函数最朴素的价值。反过来如果这段逻辑要深度依赖外部 HTTP 接口、需要复杂的字符串模板渲染或者本身只是一次性的临时分析那就老老实实留在应用层。数据库函数擅长数据密集型的逻辑不适合做外部 I/O 的编排和调度硬写进数据库只会让数据库背上不该背的包袱出了问题还不好排查因为外部接口的调用日志跟数据库日志完全是两套体系。另一个判断维度是性能。函数在数据库内部执行数据不用在应用和数据库之间来回传输对几百万行数据的聚合计算来说这个差距通常是一个数量级以上。我之前优化过一个报表任务原来在应用层循环查库一次跑半个多小时改成 PG 函数后所有聚合在数据库内部完成三分钟出结果。但注意性能优势的前提是函数里的 SQL 写得对、索引齐全否则函数内部照样全表扫描换了个地方慢而已。1.2 语言怎么选SQL 函数还是 PL/pgSQLPostgreSQL 里写函数第一道选择题是用什么语言写。我最常用的两个是 SQL 和 PL/pgSQL它们解决的问题不一样选错了后面会很难受。SQL 函数适合纯粹的查询逻辑函数体就是一条 SELECT没有变量声明、没有分支循环。它的最大优势是函数体可以被外部查询规划器整体参与优化简单场景下性能和手写 SQL 几乎一致。PL/pgSQL 则适合需要分支、循环、异常处理的场景更像一门把 SQL 当积木来搭的过程式语言可以在函数里先查一个结果再根据结果决定下一步做什么。另外还有 PL/Python、PL/Perl 这类额外语言扩展但我个人不太建议在核心业务里用多一层语言运行时部署和排障都会复杂不少。这里有个常见误会PL/pgSQL 一定比 SQL 函数慢。其实不对PL/pgSQL 只是多了过程控制你写的 SQL 部分同样会走规划器优化真正影响性能的是 SQL 本身的组织方式而不是你选了哪种语言。我见过有人在 PL/pgSQL 里写循环逐行处理慢得离谱但也见过同一个人用一条 UPDATE 批量处理性能立刻恢复正常问题的根源从来不是语言而是写法。对比维度SQL 函数PL/pgSQL 函数适用场景单条查询、简单计算分支、循环、异常处理语法结构函数体直接写 SQL需要 BEGIN...END 块优化机制可被外部查询合并优化过程控制为主SQL 部分独立优化复杂度低新手友好中高需要过程式思维2. 从零写第一个函数语法骨架与美元符号引用2.1 CREATE FUNCTION 的核心语法先看一个完整的语法骨架后面所有示例都是在这个框架里填内容CREATE OR REPLACE FUNCTION 函数名(参数名 参数类型 [, ...]) RETURNS 返回类型 [LANGUAGE 语言] [VOLATILE | STABLE | IMMUTABLE] [SECURITY INVOKER | SECURITY DEFINER] AS $$ 函数体 $$;几个我实际用下来最在意的点一个一个说。首先是 CREATE OR REPLACE它只能替换函数体不能改参数列表和返回类型。想改签名只能 DROP 再 CREATE而且 DROP 的时候如果有视图、触发器依赖这个函数还会连带报错所以早期设计参数时要想清楚不然后面很被动。然后是 RETURNS 后面可以跟好几种形式标量类型比如 integer、text复合类型比如表名或者自定义行类型最常见的是返回结果集时用 TABLE(列名 类型, ...)调用方直接 SELECT * FROM 函数名(...) 就把函数当表查了。这个写法在数据报表场景里特别实用函数返回的结果可以直接跟其他表 join不需要中间落临时表。还有一点容易被忽略函数参数的名字。PostgreSQL 9.2 之后支持命名参数调用比如 func_name(a : 1, b : 2)参数顺序错了也能调对写函数的时候给参数起个清晰的名字等于给自己省事儿。2.2 美元符号引用是怎么一回事很多新手第一次看到 $$ 都很懵不明白为什么函数体要用两个美元符包起来。它其实是 PostgreSQL 的美元符号引用字符串本质是给字符串换一种引号写法。因为函数体里到处都是单引号SQL 字符串字面量就靠单引号表示如果函数体还用单引号包那内部的单引号就得写成两个单引号转义层层嵌套下来眼睛直接看花还容易漏写。用 $$ 包一层内部的单引号全部原样保留清清爽爽。如果你在函数体里还需要再写一层美元符号引用还能用带标签的写法比如 $body$ ... $body$甚至 $func$ ... $func$只要首尾的标签一致就行。这个技巧在写嵌套的 PL/pgSQL 代码时救过我很多次比如函数体内要动态生成另一段含美元引用的代码字符串外层用 $outer$内层用 $inner$互相不干扰。2.3 先跑通一个最小函数不管需求多复杂我建议先写一个最小函数把环境跑通。比如CREATE OR REPLACE FUNCTION test_add(a integer, b integer) RETURNS integer LANGUAGE plpgsql AS $$ BEGIN RETURN a b; END; $$; SELECT test_add(3, 5);能返回 8说明你的 PostgreSQL 环境支持 PL/pgSQL 语言函数创建和调用链路都正常。如果这步直接报错说 language plpgsql does not exist多半是数据库模板里没带 plpgsql 扩展执行 CREATE EXTENSION plpgsql; 一般能解决。对了从 JavaScript 转过来的同学注意一下写 PostgreSQL 函数的时候别把箭头函数那套带进来这里没有 x x 1 这种写法。PostgreSQL 函数的标准形态就是 CREATE FUNCTION 加 AS $$ ... $$参数是有名字有类型的返回类型也要声明清楚跟 JS 的箭头函数完全是两套东西混着写只会把自己绕晕。3. 实战手写一个订单统计函数从签名到性能验证3.1 需求描述与函数签名设计写函数最怕的就是把需求想得太简单等写完才发现参数不够用。我拿一个真实场景举例统计用户在指定时间范围内的订单汇总信息包括订单数、支付总金额、取消单数。这个函数后面要被定时任务和报表后台同时调用所以不能只是临时跑一次。先定义签名CREATE OR REPLACE FUNCTION stat_user_orders( p_user_id bigint, p_start_time timestamptz, p_end_time timestamptz ) RETURNS TABLE( total_orders bigint, paid_amount numeric(12,2), cancelled_orders bigint ) LANGUAGE plpgsql STABLE AS $$ ... $$;这里有几个设计上的讲究。第一时间参数用 timestamptz 而不是 timestamptimestamptz 带时区信息避免因为会话时区不同导致统计边界错乱。第二金额用 numeric(12,2)精确计算避免浮点误差订单金额这种东西差一分钱都是事故。第三返回结果用 TABLE 而不是拼一个 JSON 字符串调用方可以直接 join 这个函数的返回结果灵活性高很多。3.2 完整函数体与逐段说明函数体我直接写出来注释放在后面逐个说明BEGIN RETURN QUERY SELECT COUNT(*)::bigint AS total_orders, COALESCE(SUM(CASE WHEN status paid THEN amount ELSE 0 END), 0)::numeric(12,2) AS paid_amount, COUNT(*) FILTER (WHERE status cancelled)::bigint AS cancelled_orders FROM orders WHERE user_id p_user_id AND created_at p_start_time AND created_at p_end_time; END;注意几个细节。RETURN QUERY 是 PL/pgSQL 里返回结果集的标准方式它把 SQL 的查询结果直接作为函数返回值比先查到变量里再拼出来要高效得多。时间过滤用 和 而不是 BETWEEN这是做时间范围统计最常见的错误来源BETWEEN 是闭区间会把结束时间点的数据也圈进来导致日报和月报在边界上多出一批记录。SUM 遇到没有匹配行时会返回 NULL所以包一层 COALESCE让报表那边不用额外判空。COUNT(*) FILTER (WHERE ...) 是 PostgreSQL 里做条件计数的很实用的写法比 SUM(CASE WHEN ...) 直观不少尤其在条件多的时候FILTER 子句的可读性优势非常明显。3.3 调用方式与性能验证调用方式很灵活直接当表查就行SELECT * FROM stat_user_orders(1001, 2024-01-01 00:00:0008, 2024-02-01 00:00:0008);如果你发现函数跑得慢第一件事不是瞎优化函数体而是先看执行计划。在 psql 里执行EXPLAIN ANALYZE SELECT * FROM stat_user_orders(1001, 2024-01-01 00:00:0008, 2024-02-01 00:00:0008);看计划里是不是全表扫描有没有走到索引。orders 表上务必有 (user_id, created_at) 的联合索引这条统计才会快。我调过一个类似的函数一开始只建了 user_id 单列索引时间过滤没法下推到索引层面数据一多就慢补了联合索引后查询时间从几秒降到几十毫秒。另外 PL/pgSQL 函数默认每次调用都会重新规划 SQL这是正常行为不要因为这个就怀疑函数写法有问题。还有一个小坑要提醒如果这个函数后面要在大数据量的表上频繁调用函数的稳定性标记一定要写对。我在这里标记了 STABLE意思是同一事务内、同一参数下函数结果不会变化但不保证跨事务不变优化器可以做更多优化。如果漏写PG 默认按 VOLATILE 处理很多优化机会就浪费了。4. pg json 函数技巧在函数里处理 jsonb 数据4.1 为什么数据库里直接处理 JSON 越来越常见现在的业务数据里 JSON 字段几乎是标配了前端配置、第三方回调、埋点事件全喜欢塞到 jsonb 字段里。PostgreSQL 的 JSON 能力在同类数据库里算非常能打的写函数时结合 jsonb 操作符能省下大量应用层解析代码。这里先强调一个基础点用 jsonb 而不是 jsonjsonb 是二进制存储支持索引和高效操作符json 基本只能存和取性能也差一截。很多后端同学习惯把 JSON 原样存进去查询时取出来在应用层反序列化再处理数据量小的时候没什么感觉等单表几百万行的时候每次查出来反序列化的开销会让人非常难受。把这些解析和过滤的逻辑写进 PG 函数让数据库直接把你要的字段算好再返回来才是在用数据库该有的能力。4.2 常用 jsonb 操作符与函数组合我实际项目里用到最多的几个操作和函数列出来给你参考>CREATE OR REPLACE FUNCTION stat_event_types(p_start timestamptz, p_end timestamptz) RETURNS TABLE(event_type text, cnt bigint) LANGUAGE sql STABLE AS $$ SELECT details-event_type AS event_type, COUNT(*)::bigint AS cnt FROM event_logs WHERE event_time p_start AND event_time p_end AND details ? event_type GROUP BY details-event_type ORDER BY cnt DESC; $$;这里?操作符判断键是否存在避免没这个键的记录被塞进来。写 JSON 函数时最烦的是类型问题JSON 里取出来的数字是文本比如 123想比较大小或者做运算必须 ::numeric 或者 ::int 转一下忘了转就会出现各种类型不匹配的报错排查起来还不太直观。4.3 大 JSON 对象处理的性能教训处理超大 JSON 字段时要克制。有一次我在函数里对一条记录里的 jsonb 做深度解析一个字段 200KB函数一调用就占几百毫秒 CPU后来改成分割成多个小字段存储速度立刻上来了。经验是JSONB 适合存低频读取、结构多变的配置不适合存高频分析的明细数据分析场景还是老老实实拆列该建的索引建上不要让数据库每次查询都去解析一个巨大的 JSON 对象。在函数里调用 jsonb 函数时也尽量不要对大对象反复嵌套解析能一次取出来就一次取出来放在变量里复用。你写的每一层 jsonb_array_elements 嵌套都是对整段数据的一次遍历嵌套多了性能是成倍往下掉的。5. 动态 SQL 和循环的取舍能不用就别用5.1 什么时候才需要动态 SQLPL/pgSQL 里的 EXECUTE 可以在函数内部拼接并执行动态 SQL。但我的意见很直接能用静态 SQL 写清楚就千万别碰动态 SQL。动态 SQL 的问题在于绕过了 SQL 预编译每次执行都要重新解析规划执行计划缓存基本失效而且拼接 SQL 容易产生注入风险排查起来还费劲。真要用的场景一般是表名、列名、排序字段这种没法参数化的地方。比如写一个通用分页查询函数要根据不同的表头做排序EXECUTE format(SELECT * FROM %I ORDER BY %I LIMIT %L, table_name, col_name, limit_val);这里的 format 函数配合 %I标识符转义和 %L字面量转义是防注入的关键。%I 会把拼接内容当标识符处理自动加双引号转义%L 会当成字面量自动加单引号。千万不要图省事用字符串直接拼接-- 错误示范直接用字符串拼接 EXECUTE SELECT * FROM || table_name || WHERE id || id_val;id_val 如果是字符串一个单引号就能改写你的 SQL。我写完这种函数的第一件事就是拿; DROP TABLE users; --这种输入去测一把测完心里才有底。5.2 函数里的循环大部分都能用 SQL 替代PL/pgSQL 支持 FOR 循环遍历查询结果但循环就是性能杀手。数据库的强项是集合操作不是逐行处理。我常见到有人这么写FOR rec IN SELECT id FROM orders WHERE status pending LOOP PERFORM process_order(rec.id); END LOOP;如果 process_order 每条记录里又查两次库这个函数跑完就是灾难。遇到这种需求优先把逻辑改写成一条 UPDATE 批量处理比如把所有 pending 状态统一更新再批量处理明细实在没法批量的再考虑在函数里用临时表暂存中间结果尽量减少循环内的 I/O 次数。这里不是说要彻底禁止循环而是每一次循环都要压到最小成本。循环体内能少一次查询就少一次能批量处理的就不要单条处理。我见过有人在函数里循环调用另一个函数嵌套三层最后整个任务跑了六个小时改成集合操作后十分钟跑完了这个差距是实实在在的。5.3 EXCEPTION 捕获异常但别拿它当 if 用PL/pgSQL 捕获异常很方便BEGIN -- 一些操作 EXCEPTION WHEN unique_violation THEN RETURN NULL; END;但这个写法有个非常隐蔽的坑一旦进入 EXCEPTION 块整个 BEGIN..END 块内已做的修改都会回滚包括你可能已经完成的 UPDATE 或者前面几条记录的写入。所以不要拿异常捕获当普通的 if 分支来用指望捕获异常后前面的操作还能保留那是不存在的。捕获异常本身也是有成本的正常路径下每一条语句都要额外记录一些信息事务状态也要维护异常处理用得越多正常性能损耗越大。别指望用异常来做业务流控该用 if 判断前置条件就用 if该加唯一约束兜底就加约束异常捕获只留给真正无法预期的错误。6. 高频报错排查从函数不存在到权限问题6.1 function xxx(integer) does not exist这是写 PostgreSQL 函数遇到次数最多的报错没有之一。要注意 PG 的函数重载机制函数名相同但参数类型不同是两个完全不同的函数调用时参数类型必须完全匹配才找得到。常见原因是函数声明的是 bigint但调用时传了一个 integer 字面量或者反过来PG 不会帮你做隐式转换直接报函数不存在。排查方式很简单在 psql 里执行 \df 函数名看一下数据库里这个函数到底有哪些签名。还有一个容易被忽略的场景函数建在了一个非 public 的 schema 里而调用时 search_path 没有包含这个 schema明明函数存在却报 does not exist。这种问题最容易让人满头问号查了半天最后发现是 schema 路径的问题。6.2 permission denied for function函数默认权限是 SECURITY INVOKER也就是以调用者权限执行。如果调用者没有函数底层表的权限即便函数是可调用的执行时也会报权限错误。解决办法有两个方向要么给用户授底层表的权限要么用 SECURITY DEFINER 让函数以定义者权限执行。SECURITY DEFINER 这个选项要非常谨慎。它意味着所有能调用这个函数的用户都临时获得了函数内部表的访问权等价于把你的表开放给了这些用户。所以用了 SECURITY DEFINER 的函数内部的 SQL 必须严格校验入参绝对不能出现字符串拼接的 SQL否则一个低权限用户就能通过注入拿到你不希望他看到的数据。6.3 函数里的中文和编码问题写函数时函数体里用了中文字符串返回后客户端显示乱码多半是 client_encoding 和服务端不一致。建议连接串里明确指定 UTF8数据库初始化时选对编码别等到乱码了再排查那个排查过程很折磨人。还有一个容易忽略的细节如果你在函数里写了 SET search_path要确认这个设置对会话的影响。函数里其实建议显式写 SET search_path public, pg_temp保证调用时不会被外部会话的 search_path 干扰避免明明存在同名函数却报 does not exist 的诡异问题。这个配置在你挂载了多个 schema、或者同一个函数名在不同 schema 里都存在的时候尤其重要。6.4 测试函数的建议流程我自己调试函数有一个固定流程可以给你直接参考先用 SELECT 函数名(参数) 直接调用确认返回结果符合预期。再 EXPLAIN ANALYZE 看执行计划确认走对了索引。用小数据量验证边界条件比如空日期范围、全取消订单、同用户多订单。最后用一个大数据量的真实场景压测观察耗时和锁情况。这套流程帮我挡掉过很多次低级事故。比如日期边界多算一天、金额 NULL 导致报表报错、参数传空值时函数直接崩掉这些问题小数据量时都能看出来越早发现越好等上了生产再去核对报表数据就晚了。尤其是报表类的函数建议跑完函数后用一段独立的 SQL 交叉验证两边结果对上了再收工。7. 几个值得长期坚持的写函数习惯7.1 函数体里别用裸的 SELECT先说我个人最受用的一条函数体里不要用裸的 SELECT。返回单值的时候用 INTO 赋给变量返回结果集的时候用 RETURN QUERY。新手最容易犯的错是把一条 SELECT 塞进 PL/pgSQL 函数体里就完事既不赋给变量也不返回函数一执行就报错query has no destination for result data。看到这个报错你就知道SELECT 的结果没地方放函数不知道该拿它怎么办。如果只是想“执行”一个查询而不关心结果PL/pgSQL 里应该用 PERFORM比如 PERFORM check_and_update()而不是 SELECT check_and_update()。这个区别虽然小但会让你的函数语义清晰很多别人读代码的时候也更容易理解你这步到底是要结果还是要副作用。7.2 把稳定性标记当成文档写写函数时顺手把稳定性标记写对STABLE 和 IMMUTABLE 不只是给优化器看的信息也是给后来读代码的人看的文档。一个函数如果只读不写标记 STABLE如果完全确定性纯计算比如日期格式转换标记 IMMUTABLE只有真的会改数据或者依赖当前时间之外的不确定因素才用 VOLATILE。稳定性标记写错的后果很实际。该标 IMMUTABLE 的函数如果标成了 VOLATILE你就没法用它建表达式索引反过来一个实际上依赖当前时间的函数标成了 IMMUTABLE优化器可能会把结果缓存下来返回错误数据。标记错了不光是性能问题严重时是正确性问题。7.3 函数脚本纳入版本管理最后一条生产环境的函数脚本一定要收进代码仓库。数据库函数一旦跑在线上改起来牵扯面广不留版本记录早晚要吃苦头。我在一个老项目里接手过一个没人能说清是谁写的统计函数代码仓库里没有记录数据库里也没有注释既不敢删也不敢改最后花了整整两天逆向理解逻辑才敢动它。现在我的习惯是所有函数都用迁移脚本管理每次变更走和业务代码一样的 review 流程函数脚本里写上变更日期和原因。这样出了问题能快速回滚排查的时候能知道这个函数是什么时候、因为什么需求改的省掉无数冤枉时间。这个习惯看着不起眼但真到线上出故障的时候你会发现它是你救命的底线。