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);
