GaussDB数据库SQL系列：自定义函数深度解析与实战应用
GaussDB数据库SQL系列：自定义函数深度解析与实战应用
一、自定义函数的核心价值
1.1 业务逻辑内聚化
封装复杂计算：将重复性业务规则封装为可复用组件（如订单折扣计算）
提升代码可维护性：避免SQL脚本碎片化（集中管理税率计算规则）
增强安全性：通过函数控制敏感数据访问（脱敏函数封装）
1.2 性能优化利器
预编译执行计划：减少重复解析开销（比动态SQL快3-5倍）
支持并行执行：GaussDB自动分解函数并行化处理
减少网络传输：在数据库端完成数据处理（避免中间结果集传输）
二、函数类型全解析
- 标量函数（Scalar Function）
-- 创建字符串处理函数（兼容Oracle语法）
CREATE OR REPLACE FUNCTION fn_format_phone(
p_phone VARCHAR(20)
) RETURNS VARCHAR(20) AS $$
BEGIN
RETURN REGEXP_REPLACE(p_phone, '(\d{3})(\d{4})(\d{4})', '\1****\3');
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT fn_format_phone('13812345678'); -- 输出138****5678
- 表值函数（Table Function）
-- 创建动态参数表函数
CREATE OR REPLACE FUNCTION fn_get_active_users(
p_start_date DATE DEFAULT CURRENT_DATE - INTERVAL '30 days'
) RETURNS TABLE (
user_id INT,
login_count INT
) AS $$
BEGIN
RETURN QUERY
SELECT u.id, COUNT(l.id)
FROM users u
LEFT JOIN logins l ON u.id = l.user_id
AND l.login_time >= p_start_date
GROUP BY u.id;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT * FROM fn_get_active_users('2023-01-01');
- 聚合函数（Aggregate Function）
-- 创建自定义统计函数
CREATE AGGREGATE udaf_calculate_median(NUMERIC) (
SFUNC = array_append,
STYPE = numeric[],
FINALFUNC = median_final
);
-- 辅助函数实现
CREATE OR REPLACE FUNCTION median_final(arr numeric[])
RETURNS numeric AS $$
DECLARE
sorted_arr numeric[];
len INT;
BEGIN
IF array_length(arr, 1) < 1 THEN
RETURN NULL;
END IF;
sorted_arr := sort_asc(arr);
len := array_length(sorted_arr, 1);
IF len % 2 = 1 THEN
RETURN sorted_arr[(len+1)/2];
ELSE
RETURN (sorted_arr[len/2] + sorted_arr[len/2 + 1])/2;
END IF;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT udaf_calculate_median(price) FROM products;
三、高级开发技巧
- 递归函数实现层级查询
-- 组织架构递归遍历
CREATE OR REPLACE FUNCTION fn_get_org_hierarchy(
p_org_id INT,
p_level INT DEFAULT 0
) RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY
SELECT id, name, manager_id, p_level
FROM organizations
WHERE id = p_org_id
UNION ALL
SELECT o.id, o.name, o.manager_id, r.p_level + 1
FROM organizations o
JOIN fn_get_org_hierarchy(r.id, r.p_level) r
ON o.manager_id = r.id;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT * FROM fn_get_org_hierarchy(1001) AS (id INT, name VARCHAR, manager_id INT, level INT);
- JSON数据处理函数
-- 创建JSON验证函数
CREATE OR REPLACE FUNCTION fn_is_valid_json(p_data TEXT)
RETURNS BOOLEAN AS $$
BEGIN
PERFORM p_data::JSON;
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
-- 创建JSON路径查询函数
CREATE OR REPLACE FUNCTION fn_json_extract(
p_json JSONB,
p_path TEXT
) RETURNS TEXT AS $$
BEGIN
RETURN jsonb_extract_path_text(p_json, VARIADIC string_to_array(p_path, '.'));
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT fn_json_extract('{"user":{"name":"John"}}', 'user.name'); -- 输出John
四、性能优化秘籍
- 执行计划优化
-- 查看函数执行计划
EXPLAIN ANALYZE
SELECT calculate_discount(total_amount) FROM orders;
-- 强制并行执行提示
SET max_parallel_workers_per_gather = 4;
- 内存管理优化
-- 设置函数内存限制
CREATE FUNCTION fn_process_data()
RETURNS VOID AS $$
...
$$ LANGUAGE plpgsql
SET work_mem = '512MB';
-- 批量处理优化
CREATE FUNCTION fn_batch_insert()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO target_table
SELECT * FROM new_batch
WHERE MOD(NEW.id, 1000) = 0; -- 分批次提交
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
- 存储过程加速
-- 使用UNLOGGED表加速临时计算
CREATE UNLOGGED TABLE temp_results (...);
-- 禁用触发器加速批量操作
ALTER TABLE products DISABLE TRIGGER ALL;
-- 执行批量函数调用
SELECT bulk_process_products();
ALTER TABLE products ENABLE TRIGGER ALL;
五、典型应用场景
- 数据清洗场景
-- 创建数据质量检测函数
CREATE OR REPLACE FUNCTION fn_check_data_quality()
RETURNS TABLE (
table_name VARCHAR,
invalid_count INT
) AS $$
BEGIN
RETURN QUERY
EXECUTE format('
SELECT ''orders''::VARCHAR,
COUNT(*) FILTER (WHERE status NOT IN (''paid'', ''shipped''))
FROM orders')
UNION ALL
EXECUTE format('
SELECT ''users''::VARCHAR,
COUNT(*) FILTER (WHERE phone ~ ''^\d{11}$'')
FROM users');
END;
$$ LANGUAGE plpgsql;
- 业务规则封装
-- 创建动态定价函数
CREATE OR REPLACE FUNCTION fn_calculate_price(
p_base_price NUMERIC,
p_member_level INT,
p_discount_rate NUMERIC DEFAULT 1.0
) RETURNS NUMERIC AS $$
BEGIN
RETURN p_base_price *
CASE p_member_level
WHEN 1 THEN 0.95
WHEN 2 THEN 0.90
ELSE 1.0
END *
p_discount_rate;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT product_name, fn_calculate_price(price, member_level)
FROM orders;
总结、最佳实践建议
- 开发规范
命名约定：fn_业务功能 / udf_数据功能
版本控制：SQL脚本Git化管理（含版本号注释）
权限隔离：最小权限原则（禁止创建超级用户函数） - 性能基准
函数类型 单次调用耗时 并行度支持 内存消耗
标量函数 <1ms 支持 低
表值函数 10-50ms 部分支持 中
聚合函数 50-200ms 支持 高 - 监控体系
-- 创建函数性能监控表
CREATE TABLE function_monitor (
function_name VARCHAR(128),
call_count BIGINT,
total_time MS,
max_time MS,
avg_time MS
);
-- 自动记录执行统计
CREATE OR REPLACE FUNCTION fn_log_performance()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO function_monitor
VALUES (TG_NAME, 1, EXTRACT(EPOCH FROM AGE(clock_timestamp(), statement_timestamp())), 0, 0)
ON CONFLICT (function_name)
DO UPDATE SET
call_count = function_monitor.call_count + 1,
total_time = function_monitor.total_time + EXTRACT(EPOCH FROM AGE(clock_timestamp(), statement_timestamp())),
max_time = GREATEST(function_monitor.max_time, EXTRACT(EPOCH FROM AGE(clock_timestamp(), statement_timestamp()))),
avg_time = (function_monitor.total_time + EXTRACT(EPOCH FROM AGE(clock_timestamp(), statement_timestamp()))) / (function_monitor.call_count + 1);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
GaussDB自定义函数是构建现代化数据平台的三大支柱技术之一（存储过程、触发器、函数），其核心价值在于：
业务逻辑数据库化：实现真正的"数据库即服务"
计算能力下沉：减少应用层与数据库间网络开销
复杂度封装：让SQL保持声明式本质
浙公网安备 33010602011771号