★ FEATURED ARTICLE 1. PL/pgSQL函数参数基础解析在PostgreSQL数据库开发中PL/pgSQL是最常用的存储过程语言。函数参数作为数据交换的接口直接影响着代码的灵活性和可维护性。与普通SQL函数不同PL/pgSQL函数支持完整的参数传递机制包括输入参数、输出参数以及输入输出参数。1.1 参数类型分类PL/pgSQL函数参数主要分为三类IN参数输入参数函数接收的只读参数函数内部不能修改其值OUT参数输出参数函数执行后返回的结果参数INOUT参数兼具输入输出特性的参数典型函数定义语法示例CREATE OR REPLACE FUNCTION calculate_stats( IN user_id integer, OUT avg_score numeric, INOUT counter integer DEFAULT 0 )1.2 参数传递方式PL/pgSQL支持两种参数传递模式按值传递默认适用于基本数据类型函数内操作不影响原始值按引用传递使用%TYPE声明时复杂类型会隐式按引用传递注意对于大型JSON或数组参数按引用传递可以显著提升性能2. 输入参数深度应用2.1 基本输入参数配置输入参数是函数接收外部数据的通道支持多种配置方式-- 显式声明IN模式 CREATE FUNCTION fn1(IN p1 integer) ... -- 隐式声明默认即为IN CREATE FUNCTION fn2(p1 integer) ... -- 带默认值的参数 CREATE FUNCTION fn3(p1 integer DEFAULT 10) ...2.2 高级输入特性参数别名在函数体内为参数指定替代名称CREATE FUNCTION fn4(integer) RETURNS integer AS $$ DECLARE input ALIAS FOR $1; BEGIN RETURN input * 2; END; $$ LANGUAGE plpgsql;多态参数使用ANY类型实现泛型参数CREATE FUNCTION fn5(IN val ANYELEMENT) ...参数校验可在函数开始处添加验证逻辑IF user_id 0 THEN RAISE EXCEPTION Invalid user ID; END IF;3. 输出参数实战技巧3.1 基础输出参数输出参数通过OUT关键字声明实际构成函数的返回值结构CREATE FUNCTION get_user_stats( user_id integer, OUT login_count integer, OUT last_active timestamptz ) AS $$ BEGIN SELECT count, last_login INTO login_count, last_active FROM user_activities WHERE id user_id; END; $$ LANGUAGE plpgsql;3.2 多返回值处理通过多个OUT参数实现类似元组返回的效果CREATE FUNCTION divide( dividend numeric, divisor numeric, OUT quotient numeric, OUT remainder numeric ) AS $$ BEGIN quotient : FLOOR(dividend / divisor); remainder : MOD(dividend, divisor); END; $$ LANGUAGE plpgsql; -- 调用方式 SELECT * FROM divide(15, 4);3.3 动态输出类型使用RETURNS TABLE实现灵活的输出结构CREATE FUNCTION get_recent_orders( user_id integer, days integer ) RETURNS TABLE ( order_id bigint, order_date timestamptz, total_amount numeric ) AS $$ BEGIN RETURN QUERY SELECT id, created_at, amount FROM orders WHERE user_id $1 AND created_at NOW() - (days || days)::interval; END; $$ LANGUAGE plpgsql;4. 输入输出参数综合应用4.1 INOUT参数特性INOUT参数同时具备输入输出功能适合需要修改传递参数值的场景CREATE FUNCTION increment_counter( INOUT counter integer, IN step integer DEFAULT 1 ) AS $$ BEGIN counter : counter step; END; $$ LANGUAGE plpgsql; -- 调用示例 DO $$ DECLARE my_counter integer : 5; BEGIN RAISE NOTICE Before: %, my_counter; PERFORM increment_counter(my_counter, 3); RAISE NOTICE After: %, my_counter; -- 输出8 END; $$;4.2 参数模式转换技巧通过函数重载实现不同参数模式的灵活组合-- 版本1纯输入参数 CREATE FUNCTION process_data(IN data jsonb) ... -- 版本2带输出参数 CREATE FUNCTION process_data( IN data jsonb, OUT status integer, OUT message text ) ...5. 参数处理最佳实践5.1 性能优化策略参数顺序优化将常用参数放在前面可选参数置后批量处理模式使用数组参数替代多次函数调用CREATE FUNCTION batch_update(IN ids integer[]) ...延迟加载对大型参数使用LATERAL实现按需加载5.2 错误处理模式参数空值处理CREATE FUNCTION safe_divide( a numeric, b numeric, OUT result numeric ) AS $$ BEGIN IF b 0 THEN result : NULL; ELSE result : a / b; END IF; END; $$ LANGUAGE plpgsql;参数验证模板CREATE FUNCTION validated_function( user_id integer, OUT result jsonb ) AS $$ DECLARE user_exists boolean; BEGIN SELECT EXISTS(SELECT 1 FROM users WHERE id user_id) INTO user_exists; IF NOT user_exists THEN RAISE EXCEPTION User not found; END IF; -- 正常处理逻辑 END; $$ LANGUAGE plpgsql;5.3 调试技巧参数日志记录CREATE FUNCTION debug_function(IN param1 text) AS $$ BEGIN RAISE NOTICE Input parameter value: %, param1; -- 函数逻辑 END; $$ LANGUAGE plpgsql;参数追踪模板CREATE OR REPLACE FUNCTION traced_function(IN val integer) RETURNS integer AS $$ DECLARE original_val integer : val; BEGIN -- 处理逻辑 RETURN val; EXCEPTION WHEN OTHERS THEN RAISE NOTICE Error occurred with input: %, original_val; RAISE; END; $$ LANGUAGE plpgsql;6. 高级参数模式6.1 可变参数函数使用VARIADIC实现类似其他语言中的可变参数CREATE FUNCTION concat_text( VARIADIC texts text[], OUT result text ) AS $$ BEGIN SELECT string_agg(t, ) INTO result FROM unnest(texts) AS t; END; $$ LANGUAGE plpgsql; -- 调用示例 SELECT concat_text(Hello, world, !); -- 返回Hello world !6.2 参数依赖注入通过参数默认值实现依赖注入模式CREATE FUNCTION get_report( report_date date DEFAULT CURRENT_DATE, data_source regclass DEFAULT public.sales_data::regclass ) AS $$ BEGIN EXECUTE format(SELECT * FROM %s WHERE date %L, data_source, report_date); -- ... END; $$ LANGUAGE plpgsql;6.3 参数化游标将参数与游标结合实现动态查询CREATE FUNCTION get_filtered_users( status_filter text DEFAULT active ) RETURNS SETOF users AS $$ DECLARE user_cursor CURSOR(param_status text) FOR SELECT * FROM users WHERE status param_status; BEGIN FOR user_rec IN user_cursor(status_filter) LOOP RETURN NEXT user_rec; END LOOP; RETURN; END; $$ LANGUAGE plpgsql;7. 参数与事务控制7.1 参数化事务隔离级别通过参数控制事务行为CREATE FUNCTION critical_operation( IN retry_count integer DEFAULT 3, IN isolation_level text DEFAULT REPEATABLE READ ) RETURNS boolean AS $$ DECLARE success boolean : false; i integer : 0; BEGIN WHILE i retry_count AND NOT success LOOP BEGIN EXECUTE SET TRANSACTION ISOLATION LEVEL || isolation_level; -- 操作逻辑 success : true; EXCEPTION WHEN OTHERS THEN i : i 1; CONTINUE; END; END LOOP; RETURN success; END; $$ LANGUAGE plpgsql;7.2 参数化锁策略根据输入参数选择不同的锁机制CREATE FUNCTION update_with_lock( IN record_id integer, IN lock_mode text DEFAULT FOR UPDATE ) RETURNS void AS $$ BEGIN EXECUTE format(SELECT 1 FROM records WHERE id %s %s, record_id, lock_mode); -- 更新操作 END; $$ LANGUAGE plpgsql;8. 参数安全实践8.1 SQL注入防护参数化查询模板CREATE FUNCTION safe_lookup( IN table_name text, IN key_column text, IN key_value text ) RETURNS SETOF json AS $$ BEGIN RETURN QUERY EXECUTE format( SELECT to_json(row) FROM %I AS row WHERE %I $1, table_name, key_column) USING key_value; END; $$ LANGUAGE plpgsql;类型严格校验CREATE FUNCTION typed_parameter( IN user_input text ) RETURNS void AS $$ DECLARE validated_input integer; BEGIN validated_input : user_input::integer; -- 处理逻辑 EXCEPTION WHEN invalid_text_representation THEN RAISE EXCEPTION Invalid integer input; END; $$ LANGUAGE plpgsql;8.2 敏感参数处理参数加密存储CREATE FUNCTION store_secure( IN secret text, OUT token text ) AS $$ BEGIN INSERT INTO secrets (encrypted_data) VALUES (pgp_sym_encrypt(secret, encryption_key)) RETURNING id::text INTO token; END; $$ LANGUAGE plpgsql;参数访问控制CREATE FUNCTION restricted_access( IN requested_data text, IN requester_id integer ) RETURNS text AS $$ DECLARE has_access boolean; BEGIN SELECT EXISTS( SELECT 1 FROM permissions WHERE user_id requester_id AND data_type requested_data ) INTO has_access; IF NOT has_access THEN RETURN NULL; END IF; -- 返回数据逻辑 END; $$ LANGUAGE plpgsql;9. 参数与函数重载9.1 基于参数类型的重载PostgreSQL支持基于参数类型和数量的函数重载-- 版本1处理整数 CREATE FUNCTION process(value integer) ... -- 版本2处理文本 CREATE FUNCTION process(value text) ... -- 版本3处理带选项的整数 CREATE FUNCTION process(value integer, options jsonb) ...9.2 参数默认值与重载通过不同默认值实现类似重载的效果CREATE FUNCTION flexible_query( start_date date DEFAULT NULL, end_date date DEFAULT NULL, category_id integer DEFAULT NULL ) RETURNS SETOF results AS $$ BEGIN RETURN QUERY SELECT * FROM data WHERE (start_date IS NULL OR created_at start_date) AND (end_date IS NULL OR created_at end_date) AND (category_id IS NULL OR category category_id); END; $$ LANGUAGE plpgsql;10. 参数与触发器10.1 触发器函数参数触发器函数通过特殊参数访问上下文信息CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$ BEGIN IF TG_OP INSERT THEN INSERT INTO audit_log VALUES (NEW.id, CREATE, NOW()); ELSIF TG_OP UPDATE THEN INSERT INTO audit_log VALUES (NEW.id, UPDATE, NOW()); ELSIF TG_OP DELETE THEN INSERT INTO audit_log VALUES (OLD.id, DELETE, NOW()); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;10.2 参数化触发器通过TG_ARGV访问触发器参数CREATE FUNCTION conditional_trigger() RETURNS TRIGGER AS $$ DECLARE should_log boolean : TG_ARGV[0]::boolean; BEGIN IF should_log THEN -- 记录日志逻辑 END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- 触发器创建示例 CREATE TRIGGER log_trigger AFTER INSERT ON orders FOR EACH ROW EXECUTE FUNCTION conditional_trigger(true);
阅读完成 · 觉得有帮助？
