1. 从“为什么需要函数模板”说起在数据库开发里写存储过程或者函数很多时候感觉像在“重复造轮子”。比如你刚写完一个根据用户ID查询订单详情的函数业务那边又提需求要一个根据手机号查询用户信息的函数。你打开编辑器新建一个文件把之前的函数复制过来改改表名、参数名、查询条件再保存。过两天又要一个根据邮箱查账户的函数……这种场景是不是很熟悉代码结构高度相似只是核心的业务逻辑和表结构不同。每次复制粘贴不仅效率低下更可怕的是埋下了维护的噩梦当你发现之前那个通用查询逻辑有个边界条件没处理好或者想统一优化性能时你就得把所有复制出来的函数一个个找出来修改漏掉一个就可能引发线上问题。PostgreSQL 的函数模板或者说“可参数化的函数生成模式”就是为了解决这个问题而存在的。它不是一个像 C 那样的语言级模板特性而是一种基于 PostgreSQL 强大扩展能力特别是PL/pgSQL语言和元编程思想的最佳实践。核心思路是我们将那些结构固定、但部分细节如表名、字段名、查询条件可变的函数逻辑抽象成一个“模板函数”。然后通过动态 SQL 拼接、或者利用 PostgreSQL 的EXECUTE命令执行时绑定来生成最终可执行的函数体。简单说就是写一个“函数工厂”让它来批量生产我们需要的具体函数。这听起来可能有点抽象我举个更生活的例子。这就像做蛋糕。函数模板就是你的蛋糕配方和模具。配方模板逻辑规定了步骤打蛋、加面粉、搅拌、烘烤。模具传入的参数决定了蛋糕最终的形状是心形、圆形还是方形。你不需要为每一种形状都写一份全新的配方只需要换模具就行了。在数据库里“模具”就是表名、字段名这些元数据。所以当你看到“PostgreSql函数模板”这个标题时它指向的不是一个开箱即用的语法糖而是一套提升代码复用性、可维护性和开发效率的方法论。接下来我会带你从最简单的场景开始一步步拆解如何构建属于你自己的函数模板并分享我在实际项目中趟过的坑和总结的经验。2. 基础构建一个简单的动态查询模板让我们从一个最普遍的需求开始根据不同的字段查询单条记录。假设我们有一个users表id, name, email和一个products表id, name, price。业务方经常需要根据 ID 查详情。2.1 反面教材传统的复制粘贴模式通常我们会写两个这样的函数-- 查询用户 CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER) RETURNS SETOF users AS $$ BEGIN RETURN QUERY SELECT * FROM users WHERE id user_id; END; $$ LANGUAGE plpgsql; -- 查询产品 CREATE OR REPLACE FUNCTION get_product_by_id(product_id INTEGER) RETURNS SETOF products AS $$ BEGIN RETURN QUERY SELECT * FROM products WHERE id product_id; END; $$ LANGUAGE plpgsql;这两个函数除了表名和参数名逻辑完全一致。如果有几十张表就需要几十个几乎一样的函数。2.2 模板进化第一个通用查询模板我们可以创建一个通用的“根据ID查询”模板函数。这里的关键是使用EXECUTE执行动态 SQL并将表名和ID值作为参数传入。CREATE OR REPLACE FUNCTION get_record_by_id( target_table REGCLASS, -- 使用REGCLASS类型自动处理模式名和引号 target_id INTEGER ) RETURNS SETOF RECORD AS $$ DECLARE result_record RECORD; query_text TEXT; BEGIN -- 动态拼接查询语句。注意使用 format 和 %I 来安全地插入标识符表名 query_text : format(SELECT * FROM %s WHERE id $1, target_table); -- 使用 EXECUTE ... USING 来安全地传入参数值避免SQL注入 FOR result_record IN EXECUTE query_text USING target_id LOOP RETURN NEXT result_record; END LOOP; RETURN; END; $$ LANGUAGE plpgsql;使用方式-- 查询ID为1的用户 SELECT * FROM get_record_by_id(users, 1) AS (id INT, name TEXT, email TEXT); -- 查询ID为5的产品 SELECT * FROM get_record_by_id(products, 5) AS (id INT, name TEXT, price NUMERIC);注意这里有一个关键点函数返回类型是SETOF RECORD。这意味着PostgreSQL在编译时不知道返回的具体字段。所以在调用时必须使用AS子句显式地定义返回的列名和类型。这是动态返回类型的一个小代价。2.3 为什么这么设计—— 核心参数解析target_table REGCLASS 这里没有用普通的TEXT类型而是用了REGCLASS。这是一个 PostgreSQL 的对象标识符类型。它的好处是你传入users、public.users甚至myschema.users它都能正确识别并转换为带引号如果需要的完整标识符。format函数中的%I会安全地处理它避免了手工拼接字符串可能带来的 SQL 注入或标识符错误比如表名是关键字或包含大写。EXECUTE ... USING 这是动态 SQL 的黄金法则。永远不要用字符串连接的方式把变量值直接拼进 SQL如... WHERE id || target_id这会导致严重的 SQL 注入漏洞。USING子句将参数值安全地传递给动态语句就像预处理语句一样。RETURNS SETOF RECORDAS子句 这是实现“通用返回”的经典模式。虽然调用时稍显繁琐但它提供了最大的灵活性。你也可以定义返回一个固定的复合类型如某个公共的视图类型但这会降低模板的通用性。这个基础模板已经解决了“根据ID查任何表”的问题。但它还很初级比如只能按id字段查查询条件也是固定的等于。在实际业务中需求远不止于此。3. 进阶实战打造可配置的“万能”查询模板业务查询需求是复杂的多条件、模糊匹配、排序、分页。我们的模板也需要升级。目标是创建一个函数可以像构建器一样传入表名、查询条件、排序字段和分页参数返回对应的结果集。3.1 设计思路与参数定义我们需要更强大的参数结构。在 PostgreSQL 中我们可以使用JSON或JSONB类型来传递灵活的配置信息。CREATE OR REPLACE FUNCTION query_records_dynamic( -- 目标表 target_table REGCLASS, -- 查询条件JSONB格式例如{name: John, status: active} filter_conditions JSONB DEFAULT {}::JSONB, -- 排序规则JSONB数组例如[{field: created_at, order: DESC}] order_by_rules JSONB DEFAULT []::JSONB, -- 分页页码和每页大小 page_number INTEGER DEFAULT 1, page_size INTEGER DEFAULT 20 ) RETURNS TABLE( total_count BIGINT, -- 总记录数不考虑分页 page_data JSONB -- 当前页的数据以JSONB数组形式返回 ) AS $$ DECLARE base_query TEXT; count_query TEXT; data_query TEXT; where_clause TEXT : ; order_clause TEXT : ; offset_val INTEGER; filter_record RECORD; order_record RECORD; total_records BIGINT; result_data JSONB; BEGIN -- 1. 构建 WHERE 子句 IF filter_conditions ! {}::JSONB THEN SELECT string_agg(format(%I $%L, key, value), AND ) INTO where_clause FROM jsonb_each_text(filter_conditions); -- 注意这里简化了只处理等值条件。更复杂的需要解析操作符, LIKE等。 END IF; -- 2. 构建 ORDER BY 子句 IF order_by_rules ! []::JSONB THEN SELECT string_agg(format(%I %s, value-field, value-order), , ) INTO order_clause FROM jsonb_array_elements(order_by_rules) AS rule(value); END IF; -- 3. 计算 OFFSET offset_val : (page_number - 1) * page_size; -- 4. 构建查询总记录数的SQL base_query : format(FROM %s, target_table); count_query : SELECT COUNT(*) || base_query; IF where_clause ! THEN count_query : count_query || WHERE || where_clause; END IF; -- 5. 执行计数查询 EXECUTE count_query INTO total_records USING (SELECT array_agg(value) FROM jsonb_each_text(filter_conditions)); -- 6. 构建查询数据的SQL data_query : SELECT jsonb_agg(row_to_json(t)) FROM (SELECT * || base_query; IF where_clause ! THEN data_query : data_query || WHERE || where_clause; END IF; IF order_clause ! THEN data_query : data_query || ORDER BY || order_clause; END IF; data_query : data_query || format( LIMIT %s OFFSET %s) t, page_size, offset_val); -- 7. 执行数据查询 EXECUTE data_query INTO result_data USING (SELECT array_agg(value) FROM jsonb_each_text(filter_conditions)); -- 8. 返回结果 total_count : total_records; page_data : COALESCE(result_data, []::JSONB); -- 处理空结果 RETURN NEXT; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER;3.2 使用示例与解析-- 示例1查询 users 表中 name 为 Alice 且 status 为 active 的用户按创建时间倒序取第1页每页10条。 SELECT * FROM query_records_dynamic( users, {name: Alice, status: active}::JSONB, [{field: created_at, order: DESC}]::JSONB, 1, 10 ); -- 示例2单纯分页查询所有产品按价格升序。 SELECT * FROM query_records_dynamic( products, {}::JSONB, -- 无过滤条件 [{field: price, order: ASC}]::JSONB, 2, -- 第二页 5 -- 每页5条 );这个函数返回一个包含两列的表total_count和page_data。page_data是一个 JSONB 数组里面是当前页的所有行数据。这种设计的好处是调用方无需预先知道返回的列结构非常适合前端或API直接使用。3.3 深入拆解安全性与性能权衡安全性再次强调 我们仍然使用EXECUTE ... USING来传递条件值。但注意在构建WHERE子句时字段名key是通过%I格式化的这能防止 SQL 注入。值value是通过USING子句传入的数组传递的。jsonb_each_text将 JSONB 对象转换为键值对array_agg(value)将所有值聚合成一个数组这个数组的索引顺序与WHERE子句中$1, $2...的位置必须严格对应。这是一个需要小心维护的约定。SECURITY DEFINER 函数创建时使用了SECURITY DEFINER。这意味着函数将以创建它的用户的权限执行而不是调用者的权限。这是一个双刃剑。好处是简化了权限管理调用者只需要有执行函数的权限而无需直接访问底层表。坏处是必须非常小心确保函数内部的 SQL 是安全的否则可能成为权限提升的漏洞。在生产环境中需要严格审计这类函数。性能考量 动态 SQL 的缺点是每次执行都需要解析和规划执行计划无法享受静态 SQL 的预编译优势。对于高频、简单的查询如根据主键查询使用专用函数性能更好。这个模板更适合用于中低频、条件多变的复杂查询场景或者作为管理后台的通用查询接口。功能局限性 上面的WHERE子句只实现了等值条件。真实的业务需要LIKE、、、IN、BETWEEN等。一个更完善的模板需要设计更复杂的filter_conditions结构例如{ and: [ {field: name, op: like, value: %John%}, {field: age, op: , value: 18}, {or: [ {field: status, op: , value: active}, {field: vip_level, op: , value: 3} ]} ] }这需要编写更复杂的递归或循环逻辑来解析和拼接 SQL 片段代码量会急剧增加。你需要根据业务复杂度的实际需要来决定模板的“万能”程度。4. 模板的另一种形态使用CREATE OR REPLACE FUNCTION动态生成函数前面的模板是在“运行时”动态生成 SQL。还有一种思路是在“部署时”或“需要时”动态生成具体的函数实体。这更像传统意义上的“代码生成”。4.1 场景为每个表生成标准的 CRUD 函数假设我们想为每个业务表自动生成四个标准函数insert_[table],select_[table]_by_id,update_[table],delete_[table]_by_id。我们可以写一个“生成器函数”。CREATE OR REPLACE FUNCTION generate_crud_functions(target_table REGCLASS) RETURNS VOID AS $$ DECLARE table_name TEXT; schema_name TEXT; full_table_name TEXT; pk_column TEXT; -- 假设主键列名为id columns_list TEXT; insert_placeholders TEXT; update_set_clause TEXT; BEGIN -- 获取模式名和表名 SELECT n.nspname, c.relname INTO schema_name, table_name FROM pg_class c JOIN pg_namespace n ON c.relnamespace n.oid WHERE c.oid target_table; full_table_name : format(%I.%I, schema_name, table_name); -- 简化处理假设主键为id。实际中应从information_schema中查询。 pk_column : id; -- 假设我们为所有列生成函数实际中可能需要排除某些列 -- 这里仅为演示获取列名列表的逻辑比较复杂通常需要查询pg_attribute -- 我们用一个静态例子代替 columns_list : id, name, email, created_at; -- 应动态获取 insert_placeholders : $1, $2, $3, $4; -- 应动态生成 update_set_clause : name $2, email $3, created_at $4; -- 应动态生成排除主键 -- 动态创建 SELECT 函数 EXECUTE format( CREATE OR REPLACE FUNCTION %I_select_by_id(IN row_id INTEGER) RETURNS SETOF %s AS $$ BEGIN RETURN QUERY SELECT * FROM %s WHERE %I row_id; END; $$ LANGUAGE plpgsql SECURITY DEFINER; , table_name, full_table_name, full_table_name, pk_column); -- 动态创建 INSERT 函数 (简化版需要根据列数动态匹配参数) EXECUTE format( CREATE OR REPLACE FUNCTION %I_insert(IN p_name TEXT, IN p_email TEXT) RETURNS INTEGER AS $$ DECLARE new_id INTEGER; BEGIN INSERT INTO %s (name, email) VALUES (p_name, p_email) RETURNING id INTO new_id; RETURN new_id; END; $$ LANGUAGE plpgsql SECURITY DEFINER; , table_name, full_table_name); -- 类似地可以生成 UPDATE 和 DELETE 函数... RAISE NOTICE CRUD functions for table %.% generated., schema_name, table_name; END; $$ LANGUAGE plpgsql;4.2 执行与效果-- 为 users 表生成CRUD函数 SELECT generate_crud_functions(users); -- 输出 NOTICE: CRUD functions for table public.users generated. -- 现在可以直接使用生成的函数 SELECT * FROM users_select_by_id(1); SELECT users_insert(Bob, bobexample.com);4.3 这种方式的利弊分析优点性能最优生成的函数是静态的PL/pgSQL函数查询计划可以被缓存性能与手写函数无异。接口清晰为每个表生成了具有明确名称和参数的函数调用方使用起来非常直观无需处理动态类型的AS子句或 JSON 参数。易于维护如果需要修改所有生成函数的某个通用逻辑比如添加审计日志只需要修改生成器函数并重新运行即可。缺点生成时机需要在表结构变更后手动或通过事件触发来重新生成函数不是完全实时的。系统复杂度数据库中会存在大量生成的函数对象管理上需要额外的命名规范比如前缀auto_和清理机制。灵活性一旦生成函数签名就固定了。如果查询条件变得非常复杂还是需要额外的动态查询函数。在实际项目中我通常会将两种方式结合使用使用“生成器模式”为核心业务表创建一套标准、高性能的简单 CRUD 函数。保留一个高度可配置的“动态查询模板函数”用于应对管理后台、报表系统等需要灵活过滤、排序、分页的场景。5. 避坑指南与高阶技巧基于函数模板的开发充满了“魔法”但也容易踩坑。下面是我总结的几个关键点和进阶技巧。5.1 必须警惕的 SQL 注入这是动态 SQL 的头号敌人。再强调一遍规则标识符表名、列名 使用format()函数的%I占位符或者quote_ident()函数。字面值/数据值永远使用EXECUTE ... USING子句传递或者使用%L但USING更安全。绝对禁止SELECT * FROM || table_name_var || WHERE id || user_input_var这种写法。5.2 处理返回类型的不确定性这是我们遇到的最大挑战。除了前面用的RETURNS SETOF RECORD还有几种方法返回 JSON/JSONB 如我们进阶模板所示这是最通用的方式尤其适合 API 层。返回TEXT 将结果集格式化为 CSV 或自定义格式的字符串。适用于数据导出。使用OUT参数和临时表 可以定义一个包含OUT参数为REF CURSOR的函数调用方用这个游标去获取数据。这更灵活但调用也更复杂。使用ANYELEMENT和ANYARRAY多态类型 这是 PostgreSQL 的高级特性允许函数处理多种数据类型。但对于完全未知结构的表用处有限。5.3 性能优化点计划缓存 纯动态 SQL (EXECUTE) 每次都会生成新的执行计划。如果查询模式固定只是参数值变化可以考虑使用PREPARE语句在会话生命周期内来缓存计划。但在函数内部PREPARE的作用域有限需谨慎使用。避免过度抽象 不要为了“万能”而让模板过于复杂。一个处理20种查询操作符的模板其解析和拼接开销可能远超其便利性。根据80%的常用场景来设计模板。索引失效 动态生成的WHERE子句必须考虑索引。确保拼接出来的条件能与表上的索引匹配。例如如果WHERE子句是lower(name) $1而索引是在name上这个索引就无法被使用。在模板设计时要提示调用者注意传入的字段和操作符。5.4 调试与日志调试动态生成的 SQL 非常困难。一定要在函数中加入详细的日志。RAISE NOTICE Generated count query: %, count_query; RAISE NOTICE Generated data query: %, data_query; RAISE NOTICE Parameters: %, param_array;在开发环境可以将RAISE NOTICE改为RAISE INFO或RAISE LOG并在 PostgreSQL 日志配置中设置log_statement all来捕获所有执行的 SQL这对排查问题至关重要。5.5 权限管理SECURITY DEFINER的陷阱使用SECURITY DEFINER时务必牢记函数创建者通常是超级用户或高权限用户应对函数内访问的对象拥有最小必要权限。考虑使用SET search_path ...在函数开头显式设置搜索路径避免受到调用者search_path的影响防止意外访问错误的对象。对于特别敏感的操作可能需要在函数内部进行额外的权限检查例如检查调用者是否拥有操作特定行的业务权限这超出了 SQL 层面可能需要结合应用逻辑。函数模板不是 PostgreSQL 的一个官方特性而是基于其强大扩展能力的一种高级设计模式。它用前期设计的复杂性换来了后期开发和维护的极大便利。启动一个项目时花点时间设计一套适合自己业务的数据访问层模板随着项目增长你会越来越体会到它的价值。它让数据库层的代码变得像乐高积木一样可组合、可复用而不是一堆杂乱无章的、高度相似的砖块。 
长河君 · 2026/8/28 4:23:54 · 阅读 1.2k PostgreSQL函数模板：动态SQL与代码生成提升数据库开发效率
1. 从“为什么需要函数模板”说起在数据库开发里写存储过程或者函数很多时候感觉像在“重复造轮子”。比如你刚写完一个根据用户ID查询订单详情的函数业务那边又提需求要一个根据手机号查询用户信息的函数。你打开编辑器新建一个文件把之前的函数复制过来改改表名、参数名、查询条件再保存。过两天又要一个根据邮箱查账户的函数……这种场景是不是很熟悉代码结构高度相似只是核心的业务逻辑和表结构不同。每次复制粘贴不仅效率低下更可怕的是埋下了维护的噩梦当你发现之前那个通用查询逻辑有个边界条件没处理好或者想统一优化性能时你就得把所有复制出来的函数一个个找出来修改漏掉一个就可能引发线上问题。PostgreSQL 的函数模板或者说“可参数化的函数生成模式”就是为了解决这个问题而存在的。它不是一个像 C 那样的语言级模板特性而是一种基于 PostgreSQL 强大扩展能力特别是PL/pgSQL语言和元编程思想的最佳实践。核心思路是我们将那些结构固定、但部分细节如表名、字段名、查询条件可变的函数逻辑抽象成一个“模板函数”。然后通过动态 SQL 拼接、或者利用 PostgreSQL 的EXECUTE命令执行时绑定来生成最终可执行的函数体。简单说就是写一个“函数工厂”让它来批量生产我们需要的具体函数。这听起来可能有点抽象我举个更生活的例子。这就像做蛋糕。函数模板就是你的蛋糕配方和模具。配方模板逻辑规定了步骤打蛋、加面粉、搅拌、烘烤。模具传入的参数决定了蛋糕最终的形状是心形、圆形还是方形。你不需要为每一种形状都写一份全新的配方只需要换模具就行了。在数据库里“模具”就是表名、字段名这些元数据。所以当你看到“PostgreSql函数模板”这个标题时它指向的不是一个开箱即用的语法糖而是一套提升代码复用性、可维护性和开发效率的方法论。接下来我会带你从最简单的场景开始一步步拆解如何构建属于你自己的函数模板并分享我在实际项目中趟过的坑和总结的经验。2. 基础构建一个简单的动态查询模板让我们从一个最普遍的需求开始根据不同的字段查询单条记录。假设我们有一个users表id, name, email和一个products表id, name, price。业务方经常需要根据 ID 查详情。2.1 反面教材传统的复制粘贴模式通常我们会写两个这样的函数-- 查询用户 CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER) RETURNS SETOF users AS $$ BEGIN RETURN QUERY SELECT * FROM users WHERE id user_id; END; $$ LANGUAGE plpgsql; -- 查询产品 CREATE OR REPLACE FUNCTION get_product_by_id(product_id INTEGER) RETURNS SETOF products AS $$ BEGIN RETURN QUERY SELECT * FROM products WHERE id product_id; END; $$ LANGUAGE plpgsql;这两个函数除了表名和参数名逻辑完全一致。如果有几十张表就需要几十个几乎一样的函数。2.2 模板进化第一个通用查询模板我们可以创建一个通用的“根据ID查询”模板函数。这里的关键是使用EXECUTE执行动态 SQL并将表名和ID值作为参数传入。CREATE OR REPLACE FUNCTION get_record_by_id( target_table REGCLASS, -- 使用REGCLASS类型自动处理模式名和引号 target_id INTEGER ) RETURNS SETOF RECORD AS $$ DECLARE result_record RECORD; query_text TEXT; BEGIN -- 动态拼接查询语句。注意使用 format 和 %I 来安全地插入标识符表名 query_text : format(SELECT * FROM %s WHERE id $1, target_table); -- 使用 EXECUTE ... USING 来安全地传入参数值避免SQL注入 FOR result_record IN EXECUTE query_text USING target_id LOOP RETURN NEXT result_record; END LOOP; RETURN; END; $$ LANGUAGE plpgsql;使用方式-- 查询ID为1的用户 SELECT * FROM get_record_by_id(users, 1) AS (id INT, name TEXT, email TEXT); -- 查询ID为5的产品 SELECT * FROM get_record_by_id(products, 5) AS (id INT, name TEXT, price NUMERIC);注意这里有一个关键点函数返回类型是SETOF RECORD。这意味着PostgreSQL在编译时不知道返回的具体字段。所以在调用时必须使用AS子句显式地定义返回的列名和类型。这是动态返回类型的一个小代价。2.3 为什么这么设计—— 核心参数解析target_table REGCLASS 这里没有用普通的TEXT类型而是用了REGCLASS。这是一个 PostgreSQL 的对象标识符类型。它的好处是你传入users、public.users甚至myschema.users它都能正确识别并转换为带引号如果需要的完整标识符。format函数中的%I会安全地处理它避免了手工拼接字符串可能带来的 SQL 注入或标识符错误比如表名是关键字或包含大写。EXECUTE ... USING 这是动态 SQL 的黄金法则。永远不要用字符串连接的方式把变量值直接拼进 SQL如... WHERE id || target_id这会导致严重的 SQL 注入漏洞。USING子句将参数值安全地传递给动态语句就像预处理语句一样。RETURNS SETOF RECORDAS子句 这是实现“通用返回”的经典模式。虽然调用时稍显繁琐但它提供了最大的灵活性。你也可以定义返回一个固定的复合类型如某个公共的视图类型但这会降低模板的通用性。这个基础模板已经解决了“根据ID查任何表”的问题。但它还很初级比如只能按id字段查查询条件也是固定的等于。在实际业务中需求远不止于此。3. 进阶实战打造可配置的“万能”查询模板业务查询需求是复杂的多条件、模糊匹配、排序、分页。我们的模板也需要升级。目标是创建一个函数可以像构建器一样传入表名、查询条件、排序字段和分页参数返回对应的结果集。3.1 设计思路与参数定义我们需要更强大的参数结构。在 PostgreSQL 中我们可以使用JSON或JSONB类型来传递灵活的配置信息。CREATE OR REPLACE FUNCTION query_records_dynamic( -- 目标表 target_table REGCLASS, -- 查询条件JSONB格式例如{name: John, status: active} filter_conditions JSONB DEFAULT {}::JSONB, -- 排序规则JSONB数组例如[{field: created_at, order: DESC}] order_by_rules JSONB DEFAULT []::JSONB, -- 分页页码和每页大小 page_number INTEGER DEFAULT 1, page_size INTEGER DEFAULT 20 ) RETURNS TABLE( total_count BIGINT, -- 总记录数不考虑分页 page_data JSONB -- 当前页的数据以JSONB数组形式返回 ) AS $$ DECLARE base_query TEXT; count_query TEXT; data_query TEXT; where_clause TEXT : ; order_clause TEXT : ; offset_val INTEGER; filter_record RECORD; order_record RECORD; total_records BIGINT; result_data JSONB; BEGIN -- 1. 构建 WHERE 子句 IF filter_conditions ! {}::JSONB THEN SELECT string_agg(format(%I $%L, key, value), AND ) INTO where_clause FROM jsonb_each_text(filter_conditions); -- 注意这里简化了只处理等值条件。更复杂的需要解析操作符, LIKE等。 END IF; -- 2. 构建 ORDER BY 子句 IF order_by_rules ! []::JSONB THEN SELECT string_agg(format(%I %s, value-field, value-order), , ) INTO order_clause FROM jsonb_array_elements(order_by_rules) AS rule(value); END IF; -- 3. 计算 OFFSET offset_val : (page_number - 1) * page_size; -- 4. 构建查询总记录数的SQL base_query : format(FROM %s, target_table); count_query : SELECT COUNT(*) || base_query; IF where_clause ! THEN count_query : count_query || WHERE || where_clause; END IF; -- 5. 执行计数查询 EXECUTE count_query INTO total_records USING (SELECT array_agg(value) FROM jsonb_each_text(filter_conditions)); -- 6. 构建查询数据的SQL data_query : SELECT jsonb_agg(row_to_json(t)) FROM (SELECT * || base_query; IF where_clause ! THEN data_query : data_query || WHERE || where_clause; END IF; IF order_clause ! THEN data_query : data_query || ORDER BY || order_clause; END IF; data_query : data_query || format( LIMIT %s OFFSET %s) t, page_size, offset_val); -- 7. 执行数据查询 EXECUTE data_query INTO result_data USING (SELECT array_agg(value) FROM jsonb_each_text(filter_conditions)); -- 8. 返回结果 total_count : total_records; page_data : COALESCE(result_data, []::JSONB); -- 处理空结果 RETURN NEXT; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER;3.2 使用示例与解析-- 示例1查询 users 表中 name 为 Alice 且 status 为 active 的用户按创建时间倒序取第1页每页10条。 SELECT * FROM query_records_dynamic( users, {name: Alice, status: active}::JSONB, [{field: created_at, order: DESC}]::JSONB, 1, 10 ); -- 示例2单纯分页查询所有产品按价格升序。 SELECT * FROM query_records_dynamic( products, {}::JSONB, -- 无过滤条件 [{field: price, order: ASC}]::JSONB, 2, -- 第二页 5 -- 每页5条 );这个函数返回一个包含两列的表total_count和page_data。page_data是一个 JSONB 数组里面是当前页的所有行数据。这种设计的好处是调用方无需预先知道返回的列结构非常适合前端或API直接使用。3.3 深入拆解安全性与性能权衡安全性再次强调 我们仍然使用EXECUTE ... USING来传递条件值。但注意在构建WHERE子句时字段名key是通过%I格式化的这能防止 SQL 注入。值value是通过USING子句传入的数组传递的。jsonb_each_text将 JSONB 对象转换为键值对array_agg(value)将所有值聚合成一个数组这个数组的索引顺序与WHERE子句中$1, $2...的位置必须严格对应。这是一个需要小心维护的约定。SECURITY DEFINER 函数创建时使用了SECURITY DEFINER。这意味着函数将以创建它的用户的权限执行而不是调用者的权限。这是一个双刃剑。好处是简化了权限管理调用者只需要有执行函数的权限而无需直接访问底层表。坏处是必须非常小心确保函数内部的 SQL 是安全的否则可能成为权限提升的漏洞。在生产环境中需要严格审计这类函数。性能考量 动态 SQL 的缺点是每次执行都需要解析和规划执行计划无法享受静态 SQL 的预编译优势。对于高频、简单的查询如根据主键查询使用专用函数性能更好。这个模板更适合用于中低频、条件多变的复杂查询场景或者作为管理后台的通用查询接口。功能局限性 上面的WHERE子句只实现了等值条件。真实的业务需要LIKE、、、IN、BETWEEN等。一个更完善的模板需要设计更复杂的filter_conditions结构例如{ and: [ {field: name, op: like, value: %John%}, {field: age, op: , value: 18}, {or: [ {field: status, op: , value: active}, {field: vip_level, op: , value: 3} ]} ] }这需要编写更复杂的递归或循环逻辑来解析和拼接 SQL 片段代码量会急剧增加。你需要根据业务复杂度的实际需要来决定模板的“万能”程度。4. 模板的另一种形态使用CREATE OR REPLACE FUNCTION动态生成函数前面的模板是在“运行时”动态生成 SQL。还有一种思路是在“部署时”或“需要时”动态生成具体的函数实体。这更像传统意义上的“代码生成”。4.1 场景为每个表生成标准的 CRUD 函数假设我们想为每个业务表自动生成四个标准函数insert_[table],select_[table]_by_id,update_[table],delete_[table]_by_id。我们可以写一个“生成器函数”。CREATE OR REPLACE FUNCTION generate_crud_functions(target_table REGCLASS) RETURNS VOID AS $$ DECLARE table_name TEXT; schema_name TEXT; full_table_name TEXT; pk_column TEXT; -- 假设主键列名为id columns_list TEXT; insert_placeholders TEXT; update_set_clause TEXT; BEGIN -- 获取模式名和表名 SELECT n.nspname, c.relname INTO schema_name, table_name FROM pg_class c JOIN pg_namespace n ON c.relnamespace n.oid WHERE c.oid target_table; full_table_name : format(%I.%I, schema_name, table_name); -- 简化处理假设主键为id。实际中应从information_schema中查询。 pk_column : id; -- 假设我们为所有列生成函数实际中可能需要排除某些列 -- 这里仅为演示获取列名列表的逻辑比较复杂通常需要查询pg_attribute -- 我们用一个静态例子代替 columns_list : id, name, email, created_at; -- 应动态获取 insert_placeholders : $1, $2, $3, $4; -- 应动态生成 update_set_clause : name $2, email $3, created_at $4; -- 应动态生成排除主键 -- 动态创建 SELECT 函数 EXECUTE format( CREATE OR REPLACE FUNCTION %I_select_by_id(IN row_id INTEGER) RETURNS SETOF %s AS $$ BEGIN RETURN QUERY SELECT * FROM %s WHERE %I row_id; END; $$ LANGUAGE plpgsql SECURITY DEFINER; , table_name, full_table_name, full_table_name, pk_column); -- 动态创建 INSERT 函数 (简化版需要根据列数动态匹配参数) EXECUTE format( CREATE OR REPLACE FUNCTION %I_insert(IN p_name TEXT, IN p_email TEXT) RETURNS INTEGER AS $$ DECLARE new_id INTEGER; BEGIN INSERT INTO %s (name, email) VALUES (p_name, p_email) RETURNING id INTO new_id; RETURN new_id; END; $$ LANGUAGE plpgsql SECURITY DEFINER; , table_name, full_table_name); -- 类似地可以生成 UPDATE 和 DELETE 函数... RAISE NOTICE CRUD functions for table %.% generated., schema_name, table_name; END; $$ LANGUAGE plpgsql;4.2 执行与效果-- 为 users 表生成CRUD函数 SELECT generate_crud_functions(users); -- 输出 NOTICE: CRUD functions for table public.users generated. -- 现在可以直接使用生成的函数 SELECT * FROM users_select_by_id(1); SELECT users_insert(Bob, bobexample.com);4.3 这种方式的利弊分析优点性能最优生成的函数是静态的PL/pgSQL函数查询计划可以被缓存性能与手写函数无异。接口清晰为每个表生成了具有明确名称和参数的函数调用方使用起来非常直观无需处理动态类型的AS子句或 JSON 参数。易于维护如果需要修改所有生成函数的某个通用逻辑比如添加审计日志只需要修改生成器函数并重新运行即可。缺点生成时机需要在表结构变更后手动或通过事件触发来重新生成函数不是完全实时的。系统复杂度数据库中会存在大量生成的函数对象管理上需要额外的命名规范比如前缀auto_和清理机制。灵活性一旦生成函数签名就固定了。如果查询条件变得非常复杂还是需要额外的动态查询函数。在实际项目中我通常会将两种方式结合使用使用“生成器模式”为核心业务表创建一套标准、高性能的简单 CRUD 函数。保留一个高度可配置的“动态查询模板函数”用于应对管理后台、报表系统等需要灵活过滤、排序、分页的场景。5. 避坑指南与高阶技巧基于函数模板的开发充满了“魔法”但也容易踩坑。下面是我总结的几个关键点和进阶技巧。5.1 必须警惕的 SQL 注入这是动态 SQL 的头号敌人。再强调一遍规则标识符表名、列名 使用format()函数的%I占位符或者quote_ident()函数。字面值/数据值永远使用EXECUTE ... USING子句传递或者使用%L但USING更安全。绝对禁止SELECT * FROM || table_name_var || WHERE id || user_input_var这种写法。5.2 处理返回类型的不确定性这是我们遇到的最大挑战。除了前面用的RETURNS SETOF RECORD还有几种方法返回 JSON/JSONB 如我们进阶模板所示这是最通用的方式尤其适合 API 层。返回TEXT 将结果集格式化为 CSV 或自定义格式的字符串。适用于数据导出。使用OUT参数和临时表 可以定义一个包含OUT参数为REF CURSOR的函数调用方用这个游标去获取数据。这更灵活但调用也更复杂。使用ANYELEMENT和ANYARRAY多态类型 这是 PostgreSQL 的高级特性允许函数处理多种数据类型。但对于完全未知结构的表用处有限。5.3 性能优化点计划缓存 纯动态 SQL (EXECUTE) 每次都会生成新的执行计划。如果查询模式固定只是参数值变化可以考虑使用PREPARE语句在会话生命周期内来缓存计划。但在函数内部PREPARE的作用域有限需谨慎使用。避免过度抽象 不要为了“万能”而让模板过于复杂。一个处理20种查询操作符的模板其解析和拼接开销可能远超其便利性。根据80%的常用场景来设计模板。索引失效 动态生成的WHERE子句必须考虑索引。确保拼接出来的条件能与表上的索引匹配。例如如果WHERE子句是lower(name) $1而索引是在name上这个索引就无法被使用。在模板设计时要提示调用者注意传入的字段和操作符。5.4 调试与日志调试动态生成的 SQL 非常困难。一定要在函数中加入详细的日志。RAISE NOTICE Generated count query: %, count_query; RAISE NOTICE Generated data query: %, data_query; RAISE NOTICE Parameters: %, param_array;在开发环境可以将RAISE NOTICE改为RAISE INFO或RAISE LOG并在 PostgreSQL 日志配置中设置log_statement all来捕获所有执行的 SQL这对排查问题至关重要。5.5 权限管理SECURITY DEFINER的陷阱使用SECURITY DEFINER时务必牢记函数创建者通常是超级用户或高权限用户应对函数内访问的对象拥有最小必要权限。考虑使用SET search_path ...在函数开头显式设置搜索路径避免受到调用者search_path的影响防止意外访问错误的对象。对于特别敏感的操作可能需要在函数内部进行额外的权限检查例如检查调用者是否拥有操作特定行的业务权限这超出了 SQL 层面可能需要结合应用逻辑。函数模板不是 PostgreSQL 的一个官方特性而是基于其强大扩展能力的一种高级设计模式。它用前期设计的复杂性换来了后期开发和维护的极大便利。启动一个项目时花点时间设计一套适合自己业务的数据访问层模板随着项目增长你会越来越体会到它的价值。它让数据库层的代码变得像乐高积木一样可组合、可复用而不是一堆杂乱无章的、高度相似的砖块。