1. 为什么自定义函数必须讲规范1.1 从一次线上事故说起先说一个我亲眼见过的教训。某公司的订单系统早期为了赶业务进度开发人员在 PostgreSQL 里写自定义函数时完全放飞自我——函数名有的叫get_data、有的叫f_order参数类型混用 varchar 和 text还有几个函数根本没考虑权限就直接SECURITY DEFINER。上线半年后业务方需要统计某个时间段的订单金额运维从监控里看到数据库 CPU 飙升到 90%慢查询日志里清一色是这个函数的调用。排查下来问题出在一个自定义函数里它内部用WHERE order_date BETWEEN start AND end过滤但因为入参声明成text类型隐式转换让索引完全失效全表扫描了千万级数据。更麻烦的是这个函数被十几个业务接口间接调用想改参数类型就得同步改所有上游。那次事故之后团队花了一整周梳理函数补命名规则、改类型、加注释、建回归测试才算把债还上。这种场景在真实项目里太常见了。PostgreSQL 的自定义函数是灵活性很高的工具但灵活性意味着你必须给自己套上缰绳。函数一旦失控轻则代码难维护重则拖垮整个数据库。所以我想结合这几年踩过的坑把自定义函数的创建与使用规范完整梳理一遍覆盖命名、参数设计、稳定性标记、安全设置、性能排查和团队协作希望能帮你少走弯路。1.2 规范带来的四个核心收益为什么专门强调规范因为 PostgreSQL 的函数机制和普通 Java 方法、Python 函数不一样它的行为会直接影响查询计划、缓存和权限体系。没有规范几乎必然踩坑。一、可维护性。数据库里的函数用久了数量会膨胀。没有统一命名和注释三个月后你自己都搞不清某个函数是干什么的、能不能删。有了规范函数名本身就是文档。二、可预测的性能。函数的稳定性标记、入参类型、是否内联直接决定优化器怎么处理调用。遵守规范意味着你能预期这个函数是走索引还是全表扫是每条记录执行一次还是只执行一次。三、安全性。PostgreSQL 的权限模型很细致。函数如果没有规范地使用SECURITY DEFINER或者没有SET search_path约束很容易成为越权访问的后门。规范里明确这些才能守住底线。四、协作效率。多数数据库是多人共用的。A 写的函数 B 要接手如果没有统一风格每次都是重新读代码成本极高。规范本质上是团队之间的契约。2. 创建函数前的设计规范命名、参数与返回类型2.1 函数命名清晰、统一、可检索命名是第一个要定的规矩。我见过f_get_user_name、user_name_get、getUserName三种风格混在一个库里查询pg_proc的时候眼睛都快看瞎了。命名规范最推荐的是模块前缀 动作 业务对象比如-- 订单模块生成订单号 order_gen_order_no() -- 用户模块查询用户等级 user_get_level(bigint) -- 报表模块按月统计销售额 report_sales_by_month(date, date)模块前缀解决了检索问题。在\df order_*里一敲订单模块的函数全列出来比\df *get*靠谱得多。动作词统一用get、gen、calc、check这类简洁动词不要一会儿get一会儿fetch一会儿load。还要注意一个细节函数名不要用 PostgreSQL 内置函数的名字。比如你写一个now()系统会优先用你的自定义函数还是内置函数虽然同名不同参可以共存但生产环境里极易引发混淆排查问题时会非常痛苦。规范里直接禁止和内置函数重名能省掉一批诡异故障。2.2 参数设计的三条红线自定义函数的参数设计直接影响调用方的体验和 SQL 的执行效率。这里有三条红线每条都是血泪教训。第一条禁止裸用 varchar 和 text 做关联过滤条件。回到开头那个事故WHERE order_date BETWEEN $1 AND $2如果$1是text而表里 order_date 是date/timestamp类型PostgreSQL 会走隐式转换索引直接失效。正确做法是参数类型和列类型保持完全一致必要时用::date显式转换。第二条参数个数别超过五六个。函数参数一旦超过这个数调用方的可读性急剧下降。如果确实需要传多个值优先考虑 JSONB 聚合参数或者定义复合类型。比如报表查询可能有很多过滤条件与其写func(a, b, c, d, e, f)不如CREATE TYPE report_filter AS ( start_date date, end_date date, category text, min_amount numeric );然后函数只收一个report_filter参数内部用filter.start_date取属性。这样加条件字段不影响已有调用。第三条默认值要谨慎使用。PostgreSQL 允许CREATE FUNCTION f(a int DEFAULT 1)但默认值在函数重载时会带来调用歧义。后面第 5 节会展开讲这里先记住默认值只在确实有业务语义时才加不要为了图省事默认。2.3 返回类型怎么定标量、集合还是 JSON返回类型同样要建立规范。别等到RETURNS TABLE写了一半才想返回值怎么拼。三选一看业务场景标量返回返回单值的场景比如calc_discount(amount numeric)返回打折后金额。这类函数注意精度问题numeric和double precision不能随便混否则浮点误差会疯涨。集合返回返回多行多列用RETURNS TABLE(...)或SETOF。月度报表、明细查询这类场景天然适合。注意列名一旦定下来不要在函数内部再改别名调用方SELECT * FROM func()的列名是函数声明里的名字。JSONB 返回适合给前端一次性返回聚合结果或内部模块间传递半结构化数据。返回 JSONB 的函数可以少写几个接口但代价是会让查询优化器完全无法下推条件性能在数据量大时会明显下降。所以规范里规定JSONB 只用于数据结构不稳定、数据量小的场景数据量大或者做报表聚合时必须用集合返回。3. 创建函数时的核心规范语言、稳定性与安全3.1 SQL 还是 PL/pgSQL选型逻辑PostgreSQL 的CREATE FUNCTION支持多种语言项目里日常用的基本是SQL和PL/pgSQL。很多人上来就写LANGUAGE plpgsql其实未必是对的。SQL 语言函数函数体内就是一条 SQL 语句。好处是 PostgreSQL 可以把函数体直接内联到调用方的查询计划里性能极佳优化器能看到你函数体里面的表过滤条件。适合封装单条查询逻辑CREATE OR REPLACE FUNCTION user_get_level(uid bigint) RETURNS int LANGUAGE sql STABLE AS $$ SELECT level FROM user_profile WHERE user_id uid; $$;PL/pgSQL 函数支持变量、循环、异常捕获、条件分支适合复杂过程逻辑。比如要多次查询、要做错误处理那就必须用 PL/pgSQL。但代价是它是解释执行的优化器看不到函数内部的逻辑函数调用之间往往会形成黑盒。我的建议是能用 SQL 函数就不写 PL/pgSQL。单一 SQL 可内联性能好维护也简单一旦逻辑需要多语句再换 PL/pgSQL并在函数开头写清楚为什么这里不能用 SQL。这套选型逻辑能避免大量性能浪费。3.2 稳定性标记VOLATILE、STABLE、IMMUTABLE这是 PostgreSQL 函数规范里最容易被忽视、但影响巨大的部分。声明错了轻则查询计划劣化重则结果直接错误。VOLATILE默认函数每次调用都返回不同结果比如now()、random()。优化器不会对此类函数做缓存和常量折叠每次执行都实时调用。STABLE在单个 SQL 语句内给定相同参数返回相同结果但不同语句之间可能变化。比如user_get_level查询用户当前等级一个查询里多次调用会复用结果但如果这条 SQL 跑很久期间数据变了也不影响本次结果。IMMUTABLE完全纯函数相同输入永远相同输出。比如calc_discount(amount numeric)。这种函数优化器会做最大胆的优化常量折叠、索引条件改写。一个很典型的错误有人把STABLE函数写成VOLATILE结果查询里带这个函数条件的列索引完全用不上反过来把VOLATILE函数标成IMMUTABLE会在分区裁剪和物化视图上拿到错误结果。规范里明确涉及查表的函数用STABLE纯计算的用IMMUTABLE取时间、随机数的用VOLATILE。3.3 异常处理什么时候必须捕获PL/pgSQL 的BEGIN ... EXCEPTION WHEN others THEN是双刃剑。它能捕获异常但代价非常大每个异常块都会创建一个子事务subtransaction在事务块里大量使用会显著拖慢性能还会产生事务 ID 膨胀。规范的几个要求第一函数内部不要无脑捕获WHEN others。数据库异常本来就应该向上抛捕获之后吞掉会让上层无从感知。比如唯一键冲突你捕获了返回一个false调用方想重试都拿不到约束名。第二确实需要捕获的场景精准指定异常类型。WHEN unique_violation、WHEN foreign_key_violation都是常用场景CREATE FUNCTION order_insert(...) RETURNS text LANGUAGE plpgsql AS $$ BEGIN INSERT INTO orders(...) VALUES (...); RETURN ok; EXCEPTION WHEN unique_violation THEN RETURN duplicate_order_no; END; $$;第三捕获异常后要给出有意义的返回信息不要只写RETURN NULL。调用方根本不知道发生了什么排查问题就像大海捞针。3.4 搜索路径与权限安全底线安全是函数规范不能妥协的部分。**search_path**一定要在函数定义里显式写SET search_path pg_catalog, public或者你的业务 schema。不写的话函数在执行时会沿用调用方的搜索路径。攻击者只要在某个前置 schema 里放同名函数你的函数内部依赖的表或函数就可能被劫持数据泄露的风险极高。提示写SECURITY DEFINER之前先想清楚是否真的需要。它表示函数以拥有者权限执行常用于让普通用户通过函数操作特定表。但如果你用了SECURITY DEFINER却不在函数体里SET search_path锁定范围基本等于给越权攻击开了门。另外函数执行权限默认是PUBLIC所有人都能调用。大部分内部业务函数应该REVOKE ALL ON FUNCTION ... FROM PUBLIC之后只GRANT给特定角色。这属于最小权限原则别嫌麻烦出事就晚了。4. 实战三类常见自定义函数的完整实现4.1 标量函数订单号格式化一个典型的订单号生成函数要求生成20240615-000123格式。我先拆需求日期前缀取当天后面流水号来自序列不足六位补零且同一天内不能重复。CREATE SEQUENCE IF NOT EXISTS order_seq START 1; CREATE OR REPLACE FUNCTION order_gen_order_no() RETURNS text LANGUAGE plpgsql VOLATILE SET search_path pg_catalog, public AS $$ DECLARE seq_val bigint; today text; new_no text; BEGIN seq_val : nextval(order_seq); today : to_char(current_date, YYYYMMDD); new_no : today || - || lpad(seq_val::text, 6, 0); RETURN new_no; END; $$; REVOKE ALL ON FUNCTION order_gen_order_no() FROM PUBLIC; GRANT EXECUTE ON FUNCTION order_gen_order_no() TO app_role;几个设计要点说明函数标记VOLATILE因为每次调用序列值都在变。lpad补零保证了位数统一。SET search_path锁死执行环境。执行权限收紧给应用的专用账号。你可能注意到序列和表绑定问题——如果订单表删了重建序列会继续还是重置这是个坑。规范里建议序列独立于表创建不要把序列嵌入建表语句里方便业务上手动重置或调整起始值。4.2 表函数按月份统计销量报表类函数非常常见。业务要求传入起止日期返回每个月的订单总量和销售额然后看板系统直接SELECT * FROM report_sales_by_month(...)。CREATE OR REPLACE FUNCTION report_sales_by_month( start_date date, end_date date ) RETURNS TABLE ( month text, order_cnt bigint, total_amt numeric(12,2) ) LANGUAGE sql STABLE SET search_path pg_catalog, public AS $$ SELECT to_char(order_date, YYYY-MM) AS month, count(*)::bigint AS order_cnt, sum(amount)::numeric(12, 2) AS total_amt FROM orders WHERE order_date start_date AND order_date end_date 1 GROUP BY to_char(order_date, YYYY-MM) ORDER BY month; $$; REVOKE ALL ON FUNCTION report_sales_by_month(date, date) FROM PUBLIC; GRANT EXECUTE ON FUNCTION report_sales_by_month(date, date) TO read_only_role;这里用LANGUAGE sql而不是 PL/pgSQL正是因为函数体只有一个查询SQL 语言函数可以直接被内联报表查询往往数据量大这一点性能收益很实在。区间过滤用了 start_date AND end_date 1这是一个我特别推荐的写法用开区间避免BETWEEN在日期时间类型边界上的精度问题。如果你用BETWEEN start_date AND end_date而 order_date 是timestamp类型结束日期当天凌晨零点之后的数据就全部漏掉了。4.3 触发器函数审计日志自动写入很多系统需要在订单更新时自动记录操作日志。用触发器函数最合适因为它在 UPDATE/INSERT 时自动被调用不需要业务方手动感知。CREATE OR REPLACE FUNCTION audit_order_changes() RETURNS trigger LANGUAGE plpgsql SET search_path pg_catalog, public AS $$ BEGIN IF TG_OP UPDATE THEN INSERT INTO order_audit( order_id, old_status, new_status, change_time, operator ) VALUES ( OLD.order_id, OLD.status, NEW.status, now(), current_user ); END IF; RETURN NEW; END; $$; CREATE TRIGGER trg_order_audit AFTER UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION audit_order_changes();触发器的规范点函数里不要做复杂业务计算只做状态记录审计表只追加不更新操作人用current_user而不是传参——因为你永远无法信任应用层传过来的字符串。还有触发器函数一定要处理好TG_OPINSERT、UPDATE、DELETE 分别走不同逻辑别用一个大杂烩判断把所有操作混在一起。注意触发器函数里的 INSERT 是独立事务的一部分。如果主事务回滚审计记录也会回滚这是符合预期的设计。但有人想在回滚后仍然保留审计日志那种需求就不要依赖触发器做应该用应用层异步上报。5. 常见坑与排查实录5.1 函数重载陷阱与隐式转换PostgreSQL 支持函数重载同名函数可以参数类型不同。这很方便但也是一个坑源。比如你有两个函数CREATE FUNCTION f(a int) RETURNS text ... CREATE FUNCTION f(a text) RETURNS text ...调用SELECT f(123)PostgreSQL 会做隐式转换类型解析规则非常复杂123是 unknown 类型系统可能选 int 版本可能选 text 版本取决于哪个路径代价低。结果可能是函数行为错乱但完全没报错。我踩过一次两个函数一个计算折扣一个拼接字符串同名calc。业务传了个字符串进去系统选了 int 版本做了隐式转换结果折扣算错了三倍运维查了半天才发现是函数重载解析的问题。规范里要求禁止同一个业务含义下使用同名函数做不同类型的参数重载除非重载的函数内部逻辑确实属于同一行为。如果只是参数类型不同的两个业务就换名字。必须重载时要写清楚每个版本的用途并给调用方显式类型转换比如SELECT calc(123::int)。5.2 缓存计划带来的参数嗅探问题这个坑深得很。PL/pgSQL 函数里的 SQL 语句在首次执行时会生成执行计划并缓存后续调用即使参数变了也可能复用老计划。专业叫法是 plan caching 或者说参数嗅探问题。最常见的例子函数里写WHERE amount threshold第一次调用传入的 threshold 是 5优化器以为过滤性很强选择了索引扫描第二次传入 threshold 是 50此时应全表扫更快但缓存计划仍然走索引结果性能雪崩。解决办法有两个思路。一个是用 PL/pgSQL 的EXECUTE ... USING动态 SQL让每次执行重新生成计划。另一个是CREATE FUNCTION ... LANGUAGE plpgsql后用参数标记或者干脆拆成 SQL 语言函数让优化器内联。规范里明确如果函数内的 SQL 是条件复杂、数据倾斜严重的查询优先考虑 SQL 语言函数必须用 PL/pgSQL 时对关键查询用动态 SQL 规避缓存计划问题。5.3 权限不足与被锁住的函数权限问题出现得非常密集。常见报错permission denied for function和permission denied for schema是两回事前者是函数本身没有执行权限后者是函数内部查到某个表但调用方没有表的 SELECT 权限。我的排查流程是这样先\df 函数名看函数执行权限和所属 owner然后SHOW search_path;确认 schema 是否正确再用SET ROLE 调用角色;模拟真实执行环境看报什么错。很多权限问题在模拟执行时能立刻复现。还要强调一个麻烦点执行CREATE OR REPLACE FUNCTION时如果你改了函数签名旧的同名函数不会被替换而是新增一个重载版本。这经常导致权限配置到旧函数上新函数默认只有 PUBLIC 权限出现奇怪的状态。规范里要求变更函数签名时要先DROP FUNCTION旧版本再创建或者确认重载是有意的。5.4 性能排查EXPLAIN 与函数内联自定义函数性能问题的排查思路跟普通 SQL 不完全一样。关键看函数是否能被内联。EXPLAIN SELECT * FROM func()时如果计划里出现Subquery Scan on func或Function Scan说明优化器把函数当作黑盒处理了如果是 SQL 语言函数且条件简单计划里能直接看到函数内部的表扫描和过滤条件那就是内联了。我一般用EXPLAIN (ANALYZE, BUFFERS)跑一次重点看 actual time 和 buffers。如果函数内部查询走全表但条件存在唯一索引优先怀疑参数类型不匹配导致的隐式转换。加::type显式转换后索引往往就生效了。另一个容易被忽略的点函数里调用函数。如果外部函数体内查了 10 次内部函数每次都要独立执行独立的 SQLN1 问题同样存在。规范里遇到这种情况建议把内部函数改成 SQL 语言函数以便合并计划或者在外部函数里一次性 join 取数然后用记录变量逐行处理。6. 团队协作中的函数治理经验6.1 注释与文档规范代码是给人看的尤其是数据库函数这种共享资产。函数头部必须写清楚五件事用途说明、参数含义、返回值含义、异常情况、依赖项用到哪些表、序列、外部函数。-- 功能生成订单号 -- 返回字符串格式 YYYYMMDD-NNNNNN -- 依赖order_seq 序列 -- 注意每天流水不重置需要重置时人工处理 CREATE OR REPLACE FUNCTION order_gen_order_no() ...好的注释能让三个月后的你快速进入状态。我再推荐一个小技巧把函数的变更历史写在函数末尾的注释里比如-- v2.1 2024-05 增加前缀区分业务线。这样单独查函数就能看到演化历史不用翻大量迁移脚本。6.2 变更管理与版本迁移数据库函数上线不是开发环境改完重启就行必须有版本迁移机制。团队里建议所有函数变更都通过迁移脚本管理脚本文件命名带版本号和变更人标记并按顺序执行。回滚方案也必须准备DROP FUNCTION或恢复上一版本的函数体脚本单独存放。这里有个细节CREATE OR REPLACE FUNCTION不会自动删除旧版本的依赖视图或物化视图如果函数签名变了依赖它的视图会挂。规范变更时先查依赖pg_depend视图可以看到。6.3 测试习惯函数必须有自动化测试。PostgreSQL 里可以用pgTAP或直接写测试 SQL 脚本我自己的习惯是保留一份测试 SQL 清单核心函数每个分支都跑一遍包括正常参数、边界参数、异常参数-- 订单号生成测试 SELECT order_gen_order_no() ~ ^\d{8}-\d{6}$; -- 月统计测试空区间 SELECT count(*) FROM report_sales_by_month(2099-01-01, 2099-02-01);这些测试代码纳入版本库函数变更时跑一遍能避免大量回归问题。真实项目中我见过因为一个函数改了时间格式导致十几个报表页面同时出错的事故就是测试没跟上。最后再分享一点个人体会函数规范这件事最核心的不是某一条命名规则而是每次创建函数前先问一句设计合理吗。我在实际项目里养成一个习惯每次写CREATE FUNCTION之前列一个小清单命名是否符合模块规范参数类型和表列类型是否严格匹配稳定性标记是否正确search_path 有没有锁权限是不是最小授予全部打勾之后再落脚本。这套清单看起来简单但帮我挡掉了绝大多数生产事故。自定义函数是 PostgreSQL 里既灵活又危险的能力。用好了它是业务逻辑的优雅封装用乱了它就是埋在数据库里的定时炸弹。希望这篇文章里这些经过实战验证的规范细节能让你在写函数的时候更有底气。有什么你自己踩过的独特坑也欢迎在评论区一起聊聊。