使用 PL/pgSQL 开发 UDF

1. 功能概述

PL/pgSQL 是数据库内置的一个可加载的过程化编程语言。它专为 SQL 而设计，在其基础上添加了过程化编程的核心功能，如变量声明、流程控制和错误处理。

重要限制说明：当前版本不支持任何形式的数据库表访问操作（包括但不限于 SELECT, INSERT, UPDATE, DELETE, SELECT...INTO 等）。PL/pgSQL 目前仅可用于进行纯粹的计算逻辑处理、流程控制和参数操作。

核心价值：

逻辑封装：将复杂的计算逻辑封装为可重用的代码单元。
流程控制：提供条件判断、循环等结构化编程能力。
数据转换：对输入参数进行计算和转换后返回结果。
代码模块化：提高代码的可维护性和重用性。

注：使用 PL/pgSQL 需要首先绑定 UDF Server，并保持 UDF Server 处于运行状态。

2. 核心特性与语法要点

2.1 变量声明 (DECLARE)

DECLARE
    -- 基本类型
    counter INTEGER := 0;
    total_amount NUMERIC(10, 2);
    user_name VARCHAR(50) := 'guest';
    is_valid BOOLEAN DEFAULT false;
    current_time TIMESTAMP := NOW();
 
    -- 数组类型
    scores INTEGER[] := ARRAY[90, 85, 78, 92];
    names TEXT[] := ARRAY['Alice', 'Bob', 'Charlie'];
 
    -- 记录类型
    user_record RECORD;
    complex_data JSONB;

2.2 丰富的流程控制示例

复杂条件判断

-- 多条件复合判断
IF age >= 18 AND has_id = true AND (membership_level > 1 OR is_vip = true) THEN
    access_granted := true;
    welcome_message := '尊贵的会员，欢迎光临！';
ELSIF age >= 18 AND has_id = true THEN
    access_granted := true;
    welcome_message := '欢迎光临！';
ELSE
    access_granted := false;
    welcome_message := '抱歉，您不符合入场条件';
END IF;

数组循环处理

-- 遍历数组并处理
DECLARE
    numbers INTEGER[] := ARRAY[1, 2, 3, 4, 5];
    squared_numbers INTEGER[];
    total INTEGER := 0;
BEGIN
    FOR i IN 1..array_length(numbers, 1) LOOP
        squared_numbers[i] := numbers[i] * numbers[i];
        total := total + numbers[i];
    END LOOP;
 
    -- 计算平均值
    avg_value := total / array_length(numbers, 1);
END;

嵌套循环示例

-- 生成乘法表
FOR i IN 1..9 LOOP
    FOR j IN 1..9 LOOP
        result := i * j;
        -- 可以存储到数组或直接处理
    END LOOP;
END LOOP;

2.3 高级计算与数据处理

数学计算函数

CREATE OR REPLACE FUNCTION calculate_compound_interest(
    principal NUMERIC,
    rate NUMERIC,
    years INTEGER
) RETURNS NUMERIC AS $$
DECLARE
    amount NUMERIC;
BEGIN
    amount := principal * POWER(1 + rate/100, years);
    RETURN ROUND(amount, 2);
END;
$$ LANGUAGE plpgsql;

字符串处理函数

CREATE OR REPLACE FUNCTION format_phone_number(
    raw_phone TEXT
) RETURNS TEXT AS $$
BEGIN
    -- 移除所有非数字字符
    raw_phone := REGEXP_REPLACE(raw_phone, '[^0-9]', '', 'g');
 
    -- 格式化为标准手机号格式
    IF LENGTH(raw_phone) = 11 THEN
        RETURN SUBSTRING(raw_phone FROM 1 FOR 3) || '-' ||
               SUBSTRING(raw_phone FROM 4 FOR 4) || '-' ||
               SUBSTRING(raw_phone FROM 8 FOR 4);
    ELSE
        RETURN raw_phone;
    END IF;
END;
$$ LANGUAGE plpgsql;

日期时间处理

CREATE OR REPLACE FUNCTION get_business_days(
    start_date DATE,
    end_date DATE
) RETURNS INTEGER AS $$
DECLARE
    curr_date DATE;
    business_days INTEGER := 0;
BEGIN
    curr_date := start_date;
 
    WHILE curr_date <= end_date LOOP
        -- 排除周末 (0=周日, 6=周六)
        IF EXTRACT(DOW FROM curr_date) NOT IN (0, 6) THEN
            business_days := business_days + 1;
        END IF;
        curr_date := curr_date + 1;
    END LOOP;
 
    RETURN business_days;
END;
$$ LANGUAGE plpgsql;

2.4 复杂数据验证

多规则数据验证

CREATE OR REPLACE FUNCTION validate_user_input(
    username TEXT,
    email TEXT,
    age INTEGER
) RETURNS JSONB AS $$
DECLARE
    validation_result JSONB := '{"valid": true, "errors": []}';
    error_message TEXT;
BEGIN
    -- 用户名验证
    IF LENGTH(username) < 3 THEN
        error_message := '用户名至少需要3个字符';
        validation_result := jsonb_set(validation_result, '{valid}', 'false');
        validation_result := jsonb_set(validation_result, '{errors}',
            (validation_result->'errors') || to_jsonb(error_message));
    END IF;
 
    -- 邮箱验证
    IF email !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$' THEN
        error_message := '邮箱格式不正确';
        validation_result := jsonb_set(validation_result, '{valid}', 'false');
        validation_result := jsonb_set(validation_result, '{errors}',
            (validation_result->'errors') || to_jsonb(error_message));
    END IF;
 
    -- 年龄验证
    IF age < 18 OR age > 120 THEN
        error_message := '年龄必须在18-120岁之间';
        validation_result := jsonb_set(validation_result, '{valid}', 'false');
        validation_result := jsonb_set(validation_result, '{errors}',
            (validation_result->'errors') || to_jsonb(error_message));
    END IF;
 
    RETURN validation_result;
END;
$$ LANGUAGE plpgsql;

3. 实用示例集合

示例1：密码强度验证

CREATE OR REPLACE FUNCTION check_password_strength(
    password TEXT
) RETURNS TEXT AS $$
DECLARE
    strength_score INTEGER := 0;
BEGIN
    -- 长度检查
    IF LENGTH(password) >= 8 THEN strength_score := strength_score + 1; END IF;
    IF LENGTH(password) >= 12 THEN strength_score := strength_score + 1; END IF;
 
    -- 复杂度检查
    IF password ~ '[A-Z]' THEN strength_score := strength_score + 1; END IF; -- 大写字母
    IF password ~ '[a-z]' THEN strength_score := strength_score + 1; END IF; -- 小写字母
    IF password ~ '[0-9]' THEN strength_score := strength_score + 1; END IF; -- 数字
    IF password ~ '[^A-Za-z0-9]' THEN strength_score := strength_score + 1; END IF; -- 特殊字符
 
    -- 返回强度等级
    RETURN CASE strength_score
        WHEN 0 THEN '极弱'
        WHEN 1 THEN '弱'
        WHEN 2 THEN '中'
        WHEN 3 THEN '强'
        ELSE '极强'
    END;
END;
$$ LANGUAGE plpgsql;

示例2：数据加密函数

CREATE OR REPLACE FUNCTION simple_encrypt(
    input_text TEXT,
    shift_key INTEGER
) RETURNS TEXT AS $$
DECLARE
    encrypted_text TEXT := '';
    i INTEGER;
    char_code INTEGER;
BEGIN
    FOR i IN 1..LENGTH(input_text) LOOP
        char_code := ASCII(SUBSTRING(input_text FROM i FOR 1));
        -- 简单的凯撒密码加密
        encrypted_text := encrypted_text || CHR((char_code + shift_key) % 256);
    END LOOP;
 
    RETURN encrypted_text;
END;
$$ LANGUAGE plpgsql;

示例3：配置解析器

CREATE OR REPLACE FUNCTION parse_config(
    config_string TEXT
) RETURNS JSONB AS $$
DECLARE
    config_json JSONB := '{}';
    key_value_pair TEXT[];
    lines TEXT[];
    line TEXT;
BEGIN
    -- 按行分割配置字符串
    lines := STRING_TO_ARRAY(config_string, E'\n');
 
    FOREACH line IN ARRAY lines LOOP
        -- 解析键值对（忽略注释和空行）
        IF line != '' AND line NOT LIKE '#%' AND POSITION('=' IN line) > 0 THEN
            key_value_pair := STRING_TO_ARRAY(line, '=');
            config_json := jsonb_set(config_json, ARRAY[TRIM(key_value_pair[1])],
                to_jsonb(TRIM(key_value_pair[2])));
        END IF;
    END LOOP;
 
    RETURN config_json;
END;
$$ LANGUAGE plpgsql;

示例4：数学统计函数

CREATE OR REPLACE FUNCTION calculate_statistics(
    numbers NUMERIC[]
) RETURNS JSONB AS $$
DECLARE
    stats JSONB;
    total NUMERIC := 0;
    min_val NUMERIC;
    max_val NUMERIC;
    i INTEGER;
BEGIN
    IF array_length(numbers, 1) IS NULL THEN
        RETURN '{"count": 0, "error": "空数组"}'::JSONB;
    END IF;
 
    -- 初始化
    min_val := numbers[1];
    max_val := numbers[1];
 
    -- 计算统计量
    FOR i IN 1..array_length(numbers, 1) LOOP
        total := total + numbers[i];
        IF numbers[i] < min_val THEN min_val := numbers[i]; END IF;
        IF numbers[i] > max_val THEN max_val := numbers[i]; END IF;
    END LOOP;
 
    stats := jsonb_build_object(
        'count', array_length(numbers, 1),
        'sum', total,
        'average', total / array_length(numbers, 1),
        'min', min_val,
        'max', max_val,
        'range', max_val - min_val
    );
 
    RETURN stats;
END;
$$ LANGUAGE plpgsql;

使用 PL/Python 开发 UDF 诊断调优