平时写SQL写多了你会发现一个非常尴尬的场景同一段逻辑这个报表里用一次那个接口里又写一遍哪天业务规则变了你得翻遍所有脚本去改少改一处就是线上事故。我在几年前第一次用PostgreSQL的自定义函数时就是被这种重复劳动逼的。把公共逻辑封装成一个函数之后效果立竿见影——不只是少写了代码更重要的是规则只维护一份改一处全库生效。PostgreSQL用户自定义函数User-Defined FunctionUDF是数据库里极其实用的一项能力。你可以把一段复杂的查询、计算或业务校验逻辑封装成函数之后像调用内置函数一样直接使用。它适合谁不管你是后端开发、数据分析师还是数据库管理员只要日常要和PostgreSQL打交道这个东西迟早都要用上。这篇内容我会从函数的设计思路、完整语法拆解、真实案例实操到常见坑点排查一次讲透不绕弯子完全可照着操作。1. 动手之前先想清楚自定义函数到底解决什么问题1.1 函数是什么和普通SQL语句有什么本质区别先做个最直白的类比。你去餐厅吃饭如果每道菜都要从种菜开始那这顿饭基本不用吃了。函数就是后厨里的“预制菜包”——把洗菜、切菜、配调料这些流程提前封装好客人点单时直接下锅炒就行。PostgreSQL里的函数也是这个道理。它是存储在数据库里的一段逻辑可以接收参数、执行计算、查询数据最后返回结果。和一条SQL语句的区别在于函数可以被反复调用调用方不需要关心内部实现。函数支持参数传递同样的逻辑可以适配不同的输入。函数可以封装复杂的流程控制包括条件判断、循环、异常处理。函数可以组合使用一个函数内部调用另一个函数形成逻辑复用。PostgreSQL的函数和有些数据库的“存储过程”概念不完全一样。PostgreSQL里也有PROCEDURE存储过程但函数和存储过程有个关键区别函数必须在RETURNS子句中声明返回类型并且支持在SQL语句里直接调用存储过程则侧重于独立执行不要求有返回值。日常工作里绝大多数场景用FUNCTION就够了。1.2 什么时候该写函数什么时候千万别写写函数不是目的解决问题才是。根据我的实际经验下面这些场景强烈建议用函数某段聚合统计逻辑在多处重复出现比如“按客户维度统计近30天有效订单金额”。业务规则经常变化比如折扣规则、积分计算规则封装成函数后只改一处即可。需要在INSERT或UPDATE时做复杂的默认值计算或数据校验。需要循环处理一批数据比如把某个表里符合条件的记录逐条更新。需要返回结构化结果集给报表或接口直接使用。但这事不能走极端。我的建议是以下几种情况不要硬写函数一条简单SELECT能解决的不要无脑封装。函数也有开销调用一个SQL语言函数和直接执行一条SQL性能上多少还是有差异。一次性脚本不要写成函数。有些人图省事临时跑个数据处理也建函数结果函数表里堆积了一大堆“一次性”垃圾过两个月自己都看不懂。过于复杂的业务逻辑也不要全塞进函数里。函数适合做封装但不适合代替应用层做完整的业务流程编排。那种十几个表联动、多步骤事务的逻辑放在应用层管理会更清晰。1.3 函数体的实现语言怎么选PostgreSQL函数的一大特性是支持多种过程语言。核心的包括SQL语言函数函数体就是一条或多条SQL语句简单直接适合封装查询逻辑。PL/pgSQL函数PostgreSQL内置的过程语言支持变量、IF判断、循环、异常捕获语法风格和Oracle的PL/SQL很像是绝大多数场景的首选。C语言函数性能极致适合计算密集型逻辑但需要编译动态库门槛较高日常开发基本用不上。其他语言通过扩展可以支持PythonPL/Python、PerlPL/Perl等适合特定场景。日常开发里我的选型原则很简单只要函数体是一条SQL能搞定的用SQL语言只要有变量、条件、循环、异常处理就用PL/pgSQL至于C和Python一般情况下不用考虑。2. 函数创建语法逐段拆解别死记硬背2.1 完整的CREATE FUNCTION语法结构先看一段标准的创建语法CREATE OR REPLACE FUNCTION schema_name.function_name( param1 data_type, param2 data_type DEFAULT default_value ) RETURNS return_data_type LANGUAGE plpgsql [IMMUTABLE | STABLE | VOLATILE] AS $$ BEGIN -- 函数体逻辑 END; $$;这段语法里几个关键点逐个说透。CREATE OR REPLACE FUNCTION是标准的创建语句。加上OR REPLACE后如果函数已存在会先替换掉旧的实现这对于开发调试特别方便。不过要注意OR REPLACE不能改变函数原有的参数列表和返回类型只能替换函数体。想改签名只能DROP后重建。函数名建议带模式名schema_name比如public.calc_discount。不带模式名时PostgreSQL会按当前search_path配置去找。如果search_path设置不当很容易出现“函数存在但调用时报不存在”的诡异问题这点后面细说。参数列表里每个参数要写明数据类型比如numeric、integer、text、date。还可以给默认值调用的时候可以不传。RETURNS声明的返回类型决定了函数的输出形式可以是标量类型、复合类型、表结构等下一节详细展开。LANGUAGE前面提过了声明函数体的实现语言。写plpgsql还是sql取决于函数体复杂度。**AS $$ ... $$**是函数体。两个美元符之间放的就是函数体内容。为什么要用$$而不是单引号因为单引号在函数体内部经常用到比如字符串字面量如果用单引号包函数体内部单引号得一个个转义极其痛苦。$$符号可以避免这个麻烦。2.2 参数模式IN、OUT、INOUT到底怎么用PostgreSQL函数的参数默认是IN模式也就是输入参数。但除了IN还有OUT和INOUT理解它们对设计函数很重要。IN输入只进不出。函数内部可以使用这个参数的值但函数外部拿不到它的最终值。这是最常用的模式。OUT输出只出不进。调用方无法给它传值它是在函数内部被赋值然后作为返回值的一部分输出。当函数需要返回多个值时可以用OUT参数替代复杂的复合类型。INOUT输入输出既能传值进去又能在函数内部修改后传出来。相当于一个“读写双向”的通道。实际例子更直观。看下面这个函数同时用了OUT参数返回多个值CREATE OR REPLACE FUNCTION get_order_stats( customer_id_in integer, total_orders OUT integer, total_amount OUT numeric ) LANGUAGE plpgsql AS $$ BEGIN SELECT count(*), COALESCE(sum(amount), 0) INTO total_orders, total_amount FROM orders WHERE customer_id customer_id_in; END; $$;这个写法省去了显式的RETURNS声明因为OUT参数已经告诉数据库返回结构是什么。调用时直接SELECT * FROM get_order_stats(1024);返回两列结果。2.3 返回值类型的多种写法你大概率会用到RETURNS子句支持的类型非常多我挑实际工作里最高频的几种标量类型RETURNS integer、RETURNS numeric、RETURNS text等。函数要么返回一个具体的标量值要么返回NULL。VOID表示函数没有返回值。如果函数主要目的是执行操作比如数据清理可以用RETURNS void。SETOF 类型返回一个集合。比如RETURNS SETOF integer表示返回一组整数。TABLE(...)返回一张临时表结构。这是写报表函数最常用的方式可以在函数里定义返回的列名和类型非常灵活。复合类型返回一张表中定义的行类型或者自定义的复合类型。这里最值得关注的是RETURNS TABLE。举个典型场景你要写一个函数输入月份返回该月每天的订单量和销售额。用RETURNS TABLE可以这样声明CREATE OR REPLACE FUNCTION daily_sales_report( target_month date ) RETURNS TABLE(sale_date date, order_count bigint, total_amount numeric) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT order_date::date AS sale_date, count(*) AS order_count, COALESCE(sum(amount), 0) AS total_amount FROM orders WHERE order_date date_trunc(month, target_month) AND order_date date_trunc(month, target_month) interval 1 month GROUP BY order_date::date ORDER BY order_date::date; END; $$;调用后得到的就是一张三列的表。RETURN QUERY关键字的含义是把后面这条查询的结果作为函数返回值集合的一部分返回。RETURNS TABLE函数体内可以有多个RETURN QUERY结果集会自动拼接。2.4 稳定性标记VOLATILE、STABLE、IMMUTABLE是怎么回事创建函数时如果不声明稳定性PostgreSQL默认按VOLATILE处理。这三个词描述的是“函数结果的可预测程度”理解它们对你的查询性能和索引使用影响很大。IMMUTABLE不可变的只要输入参数相同返回值永远相同不依赖任何外部状态。比如计算两个字符串拼接结果。这类函数可以被优化器简化甚至能用于创建表达式索引。STABLE稳定的在一次SQL语句执行过程中相同输入返回相同结果但不保证跨语句一致。典型的例子是读配置表的函数同一事务内值不变。VOLATILE易变的即使参数相同每次调用都可能返回不同结果。比如now()、random()这类函数每次调用都可能变。PostgreSQL对VOLATILE函数的调用次数不做优化保证。这个标记不是随便写的。如果你把一个IMMUTABLE的函数标记成VOLATILE最直接的损失是无法用它创建表达式索引优化器也少了执行计划优化空间。反过来如果函数实际会读取数据库表你却标成IMMUTABLE那查询计划可能会缓存错误结果引发数据不一致。这是很隐蔽的坑我后面在常见问题里详细说。3. 实操演练从零开始写三个不同类型的函数3.1 准备工作确认环境与基础连接动手前先确保你本地环境可用。连接到PostgreSQL之后先确认版本SELECT version();建议使用PostgreSQL 12以上版本14、15、16我个人都用过本文的语法在这些版本上均适用。客户端方面psql命令行、pgAdmin、DBeaver都可以。为了演示方便我先准备两张基础表CREATE TABLE IF NOT EXISTS customers ( customer_id integer PRIMARY KEY, customer_name text NOT NULL, level text DEFAULT normal ); CREATE TABLE IF NOT EXISTS orders ( order_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers(customer_id), order_date date NOT NULL, amount numeric(10,2) NOT NULL );再插入几条测试数据INSERT INTO customers VALUES (1, 张伟, normal), (2, 李娜, vip), (3, 王强, normal); INSERT INTO orders VALUES (101, 1, 2025-01-05, 2800.00), (102, 2, 2025-01-08, 5800.00), (103, 1, 2025-02-12, 3200.00), (104, 3, 2025-02-18, 1500.00), (105, 2, 2025-03-02, 7600.00);3.2 第一个函数SQL语言函数计算折扣金额这是个很常见的业务场景根据客户等级和订单金额计算实际应付金额。先写一个最简单的SQL语言函数CREATE OR REPLACE FUNCTION public.calc_discount_price( p_amount numeric, p_level text ) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT CASE WHEN p_level vip THEN p_amount * 0.85 WHEN p_level normal THEN p_amount * 0.95 ELSE p_amount END; $$;函数体只有一条CASE表达式所以用LANGUAGE sql完全够用不需要引入PL/pgSQL的开销。测试一下SELECT order_id, amount, public.calc_discount_price(amount, vip) AS discounted_amount FROM orders;输出结果里 vim客户 102和105订单打了85折其他订单打95折。这个函数标记为IMMUTABLE是合理的因为输出只由输入参数决定不查表、不依赖时间等变量。这里有个细节为什么函数名要带public.前缀如果你不确定当前search_path包含哪个schema带上前缀能避免解析歧义。生产环境里我习惯所有自定义函数都显式写明schema。3.3 第二个函数PL/pgSQL函数带变量和条件控制接着写一个带变量、条件判断和字符串格式化的函数。需求是传入一个客户ID统计该客户的总订单数、总金额并返回一句包含统计结果的文字描述。CREATE OR REPLACE FUNCTION public.get_customer_summary( p_customer_id integer ) RETURNS text LANGUAGE plpgsql STABLE AS $$ DECLARE v_order_count integer; v_total_amount numeric(10,2); v_customer_name text; BEGIN -- 查询客户名称查不到则直接返回提示 SELECT customer_name INTO v_customer_name FROM customers WHERE customer_id p_customer_id; IF NOT FOUND THEN RETURN 客户不存在: || p_customer_id; END IF; -- 统计客户订单 SELECT count(*), COALESCE(SUM(amount), 0) INTO v_order_count, v_total_amount FROM orders WHERE customer_id p_customer_id; RETURN format( 客户 %s (ID: %s) 共有 %s 笔订单累计消费 %s 元, v_customer_name, p_customer_id, v_order_count, v_total_amount ); END; $$;这个函数用了DECLARE段声明了三个局部变量用了IF NOT FOUND判断SELECT INTO是否命中了记录还用了format函数做字符串模板拼接。写完后测试SELECT public.get_customer_summary(1); SELECT public.get_customer_summary(99);第一个返回“客户 张伟 (ID: 1) 共有 2 笔订单累计消费 6000.00 元”第二个返回“客户不存在: 99”。这里的STABLE标记是合适的因为函数内会查询customers和orders表但同一SQL语句执行期间读到的数据在语句级快照下是一致的不会造成不一致问题。IF NOT FOUND这个语法是PL/pgSQL里非常实用的小技巧专门配合SELECT INTO使用省去单独再查一次的开销。3.4 第三个函数返回结果集的复杂报表函数第三个案例更贴近日常报表开发。需求是输入一个日期范围返回一张按“周”汇总的销售报表列包括周起始日、订单总数、订单总金额、同比上周增长比例。周同比计算需要在函数内部对日期做处理还要做自连接查询用RETURNS TABLE来输出结果。CREATE OR REPLACE FUNCTION public.weekly_sales_summary( p_start_date date, p_end_date date ) RETURNS TABLE(week_start date, order_count bigint, total_amount numeric, growth_rate numeric) LANGUAGE plpgsql STABLE AS $$ BEGIN RETURN QUERY WITH weekly AS ( SELECT date_trunc(week, order_date)::date AS week_start, count(*) AS cnt, COALESCE(SUM(amount), 0) AS amt FROM orders WHERE order_date BETWEEN p_start_date AND p_end_date GROUP BY date_trunc(week, order_date) ) SELECT w.week_start, w.cnt, w.amt, ROUND( (w.amt - LAG(w.amt) OVER (ORDER BY w.week_start)) / NULLIF(LAG(w.amt) OVER (ORDER BY w.week_start), 0) * 100, 2 ) AS growth FROM weekly w ORDER BY w.week_start; END; $$;调用方式SELECT * FROM public.weekly_sales_summary(2025-01-01, 2025-03-31);执行后会返回几行结果每一行是一周的汇总。这里用到LAG窗口函数计算上一周金额NULLIF防止除零错误并把结果保留两位小数。这种返回结果集的函数最大的好处是调用方完全不需要关心函数内部怎么写SQL拿到结果直接用。报表场景里非常香。4. 调用函数不只是SELECT这一条路4.1 最基础的调用方式SELECT语句里调用最常用的方式就是把函数放在SELECT列表里和普通内置函数用法一致SELECT order_id, amount, public.calc_discount_price(amount, customer_level) AS final_amount FROM orders;也可以把函数放在WHERE子句中过滤数据SELECT * FROM orders WHERE public.calc_discount_price(amount, vip) 5000;还可以在ORDER BY里用函数排序。只要函数在SQL表达式合法的地方出现都可以调用。4.2 在INSERT、UPDATE、DELETE语句中调用函数函数不只在SELECT中生效。在INSERT语句里可以用函数的返回值作为插入值。比如生成订单号或计算默认折扣INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES ( 106, 2, CURRENT_DATE, public.calc_discount_price(8000, vip) );更实用的场景是在UPDATE里用函数统一修改数据。比如要按客户等级重新校准所有订单的“实付金额”字段一条UPDATE就搞定了UPDATE orders SET final_amount public.calc_discount_price(amount, c.level) FROM customers c WHERE orders.customer_id c.customer_id;4.3 在JOIN连接和视图里使用函数函数可以参与JOIN。例如定义了一个返回所有VIP客户ID集合的函数然后和订单表做关联查询SELECT o.* FROM orders o JOIN public.get_vip_customer_ids() v ON v.customer_id o.customer_id;函数还可以封装在视图里对应用层只暴露视图名称。这样如果底层表结构或计算逻辑调整只需要改函数定义视图和应用层都无需改动。4.4 在触发器中使用函数数据的自动守卫PostgreSQL的触发器必须绑定一个返回trigger类型的函数这也是函数的一个重要调用场景。看这个例子每当向orders表插入新记录时自动校验订单金额是否大于0否则抛出异常。CREATE OR REPLACE FUNCTION public.check_order_amount() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.amount 0 THEN RAISE EXCEPTION 订单金额必须大于0当前值: %, NEW.amount; END IF; RETURN NEW; END; $$; CREATE TRIGGER trg_check_order_amount BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION public.check_order_amount();之后如果向orders插入负金额直接报错。这个模式在数据质量管控上非常有用效率也比应用层判断高只要数据库层把住关口所有入口都能守住。4.5 函数内部互相调用别重复造轮子函数体内也可以调用其他自定义函数。还是用前面定义的calc_discount_price函数假设又要写一个“批量更新折扣价”的函数直接内部调用CREATE OR REPLACE FUNCTION public.refresh_order_discounts() RETURNS void LANGUAGE plpgsql VOLATILE AS $$ BEGIN UPDATE orders SET final_amount public.calc_discount_price(amount, c.level) FROM customers c WHERE orders.customer_id c.customer_id; END; $$;这让逻辑复用变得非常简单。一个复杂的函数可以拆成多个小函数每个小函数负责一件事再组合成更大的功能。5. 常见问题与排查技巧实录5.1 函数明明存在为什么报“function does not exist”这是新手最常遇到的问题。错误信息类似ERROR: function public.get_customer_summary(integer) does not exist排查分三步走。第一步检查schema和search_path。当前search_path里如果没包含函数所在的schema执行SELECT get_customer_summary(1)就会报错。解决办法是带上完整schema前缀调用或者调整search_pathSET search_path TO public, pg_catalog;第二步检查参数类型是否完全匹配。函数定义为BIGINT参数你用INTEGER去调用有时候PostgreSQL不会自动转类型也会报错。第三步确认函数所属的database是否当前连接的库。跨数据库调用是不行的PostgreSQL不支持跨库直接访问函数。5.2 OR REPLACE 和参数类型不一致的坑CREATE OR REPLACE FUNCTION在替换时要求参数类型和返回类型必须和原函数完全一致。如果只改了参数类型或返回类型直接执行会报错ERROR: cannot change name of input parameter ERROR: cannot change return type of existing function这时只能DROP旧函数再重新CREATEDROP FUNCTION IF EXISTS public.get_customer_summary(integer); CREATE OR REPLACE FUNCTION ...要特别小心如果这个函数被其他视图、触发器或其他函数依赖DROP会连带报依赖错误。可以用CASCADE强制删除但会连带删除依赖对象生产环境务必谨慎。5.3 权限问题函数创建好了别人却看不到PostgreSQL默认对函数执行权限是授予PUBLIC的但如果你在特殊环境里收紧了权限可能会出现别人无法执行函数的情况。需要显式授权GRANT EXECUTE ON FUNCTION public.calc_discount_price(numeric, text) TO app_user;如果函数放在非public的schema里还要赋予用户该schema的USAGE权限。权限排查时用下面的命令查看当前函数的权限SELECT proname, proacl FROM pg_proc WHERE proname calc_discount_price;5.4 稳定性标记标错导致的结果异常这是个非常隐蔽的坑。假如你写了一个函数读取配置表却错误地标记成IMMUTABLECREATE OR REPLACE FUNCTION public.get_discount_rate() RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT rate FROM discount_config WHERE id 1; $$;IMMUTABLE意味着“输出只由输入决定”但函数体查表表内容变了结果就该变。PostgreSQL优化器会把这类函数的结果视为常量可能在一个查询会话中直接缓存结果导致配置更新后函数返回值迟迟不刷新。这种问题最难排查因为单条SELECT执行时结果是对的放到大查询里结果就错了。所以稳定性标记一定要实事求是只做纯计算、不查表、不依赖时间的才标IMMUTABLE查询表但依赖语句级快照的标STABLE其他有副作用或每次结果都可能变的标VOLATILE。5.5 函数体内SQL性能低查数据慢怎么办函数体内的SQL如果执行计划不佳同样需要优化。一个常见误区是在函数里循环逐条查表比如用FOR循环逐行处理再UPDATE。这种写法在数据量小的时候没问题数据量一大性能直线下降。排查方法是把函数体内的SQL单独拿出来用EXPLAIN ANALYZE看执行计划。PL/pgSQL函数默认是黑盒但你可以临时把SQL复制出来分析。另外如果函数涉及集合操作优先用一条SQL完成不要用循环逐条操作。函数里循环不是不能用而是要知道循环里每一条SQL都是一次数据库交互几十万数据循环几万次就是灾难。5.6 调试技巧RAISE NOTICE和临时表PL/pgSQL函数常用的调试手段是RAISE NOTICE它可以把中间变量值打印到客户端日志CREATE OR REPLACE FUNCTION public.debug_demo(p_id integer) RETURNS numeric LANGUAGE plpgsql AS $$ DECLARE v_amount numeric; BEGIN SELECT amount INTO v_amount FROM orders WHERE order_id p_id; RAISE NOTICE 查询到的金额是 %, v_amount; RETURN v_amount; END; $$;执行时psql会显示NOTICE信息。复杂函数调试时我还会在函数里临时把中间结果INSERT到一张临时表逐步查看哪一步数据不对。定位后记得移除调试代码。6. 一点性能与维护经验函数不是写完就完事了。结合我自己的经验还有几点想强调。函数创建后建议养成写注释的习惯COMMENT ON FUNCTION public.calc_discount_price(numeric, text) IS 按客户等级计算折扣后金额vip打85折normal打95折;用COMMENT ON FUNCTION记录函数用途、参数含义、修改历史。时间久了函数数量一多没有注释的函数就是埋雷。查询函数列表时也可以用SELECT p.proname, pg_get_function_arguments(p.oid) AS args, obj_description(p.oid) AS comment FROM pg_proc p WHERE p.pronamespace public::regnamespace AND p.prokind f;还有一点是函数版本管理。生产环境的函数脚本最好纳入版本管理每次修改保留变更记录别直接在数据库里改完就不再同步。我实际踩过的坑是开发环境测试好的函数上线时直接在服务器上手动改结果漏改了一个版本导致线上逻辑和开发环境不一致排查了很久。在索引优化方面IMMUTABLE函数有一个独到用途可以为表达式创建索引。比如你经常按客户小写姓名查询CREATE INDEX idx_customers_name_lower ON customers (lower(customer_name));前提是lower()函数是IMMUTABLE的。自定义函数如果标记正确同样支持这种用法。这是STABLE和VOLATILE函数做不到的。最后再分享一个小技巧函数设计时尽量保持“小、专、纯”。一个函数只做一件事输入输出尽量明确副作用越少越好。大而全的函数看着方便但维护起来是噩梦。等函数数量多了你会发现这些规则能帮你节省大量排查问题的时间。