来为您介绍几种常见的 PostgreSQL 函数写法：

1. 基本标量函数（返回单个值）

sql

-- 计算两个数的和
CREATE OR REPLACE FUNCTION add_numbers(
    a INTEGER,
    b INTEGER
)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT add_numbers(10, 5);  -- 返回 15

2. 返回表的函数

sql

-- 返回员工信息的函数
CREATE OR REPLACE FUNCTION get_employees_by_dept(
    dept_id INTEGER
)
RETURNS TABLE (
    employee_id INTEGER,
    employee_name VARCHAR(100),
    salary DECIMAL(10,2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT e.id, e.name, e.salary
    FROM employees e
    WHERE e.department_id = dept_id;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM get_employees_by_dept(1);

3. 带有条件逻辑的函数

sql

-- 根据销售额计算奖金
CREATE OR REPLACE FUNCTION calculate_bonus(
    sales_amount DECIMAL,
    performance_rating INTEGER DEFAULT 3
)
RETURNS DECIMAL AS $$
DECLARE
    bonus DECIMAL;
BEGIN
    IF sales_amount > 100000 THEN
        bonus := sales_amount * 0.15;
    ELSIF sales_amount > 50000 THEN
        bonus := sales_amount * 0.10;
    ELSE
        bonus := sales_amount * 0.05;
    END IF;
    
    -- 根据绩效调整
    IF performance_rating > 4 THEN
        bonus := bonus * 1.2;
    ELSIF performance_rating < 2 THEN
        bonus := bonus * 0.8;
    END IF;
    
    RETURN bonus;
END;
$$ LANGUAGE plpgsql;

4. 带有异常处理的函数

sql

-- 安全地更新员工工资
CREATE OR REPLACE FUNCTION update_salary(
    emp_id INTEGER,
    new_salary DECIMAL
)
RETURNS VOID AS $$
BEGIN
    -- 检查薪水是否为正数
    IF new_salary <= 0 THEN
        RAISE EXCEPTION 'Salary must be positive';
    END IF;
    
    -- 更新薪水
    UPDATE employees
    SET salary = new_salary
    WHERE id = emp_id;
    
    -- 检查是否更新成功
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Employee with ID % not found', emp_id;
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        -- 记录错误并重新抛出
        RAISE NOTICE 'Error updating salary: %', SQLERRM;
        RAISE;
END;
$$ LANGUAGE plpgsql;

5. 递归函数

sql

-- 计算阶乘
CREATE OR REPLACE FUNCTION factorial(
    n INTEGER
)
RETURNS BIGINT AS $$
BEGIN
    IF n <= 1 THEN
        RETURN 1;
    ELSE
        RETURN n * factorial(n - 1);
    END IF;
END;
$$ LANGUAGE plpgsql;

6. 使用 SQL 语言的简单函数

sql

-- 使用纯 SQL（更高效）
CREATE OR REPLACE FUNCTION get_employee_count(
    dept_id INTEGER
)
RETURNS INTEGER AS $$
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = dept_id;
$$ LANGUAGE SQL;

7. 带有默认值和参数的函数

sql

-- 创建用户账户
CREATE OR REPLACE FUNCTION create_user_account(
    username VARCHAR(50),
    email VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(50) DEFAULT CURRENT_USER
)
RETURNS INTEGER AS $$
DECLARE
    new_id INTEGER;
BEGIN
    INSERT INTO users (username, email, is_active, created_by, created_at)
    VALUES (username, email, is_active, created_by, CURRENT_TIMESTAMP)
    RETURNING id INTO new_id;
    
    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

函数管理命令

sql

-- 查看所有函数
\df

-- 查看函数定义
\df+ function_name

-- 删除函数
DROP FUNCTION function_name(parameter_types);

-- 修改函数所有者
ALTER FUNCTION function_name(parameter_types) OWNER TO new_owner;

最佳实践建议

1. 使用适当的语言：

   • 简单计算用 LANGUAGE SQL

   • 复杂逻辑用 LANGUAGE plpgsql

2. 命名规范：

   • 使用小写和下划线

   • 明确表达函数用途

3. 参数验证：

   • 始终验证输入参数

   • 提供有意义的错误消息

4. 性能考虑：

   • 避免在循环中执行 SQL

   • 考虑使用 RETURNS TABLE 代替多个 RETURN NEXT

您需要什么具体类型的函数？我可以为您编写更符合需求的示例。

string参数

我来为您介绍 PostgreSQL 中处理字符串参数的函数写法：

1. 基本字符串处理函数

sql

-- 连接两个字符串
CREATE OR REPLACE FUNCTION concat_strings(
    str1 VARCHAR,
    str2 VARCHAR
)
RETURNS VARCHAR AS $$
BEGIN
    RETURN str1 || ' ' || str2;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT concat_strings('Hello', 'World');  -- 返回 'Hello World'

2. 字符串分割和提取

sql

-- 提取电子邮件用户名部分
CREATE OR REPLACE FUNCTION extract_username(
    email VARCHAR
)
RETURNS VARCHAR AS $$
DECLARE
    username VARCHAR;
BEGIN
    -- 检查电子邮件格式
    IF email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format: %', email;
    END IF;
    
    -- 提取 @ 之前的部分
    username := split_part(email, '@', 1);
    RETURN username;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT extract_username('john.doe@example.com');  -- 返回 'john.doe'

3. 字符串搜索和替换

sql

-- 安全地替换文本中的敏感信息
CREATE OR REPLACE FUNCTION mask_sensitive_info(
    input_text TEXT,
    pattern_to_mask VARCHAR DEFAULT '\d{4}-\d{4}-\d{4}-\d{4}'  -- 信用卡模式
)
RETURNS TEXT AS $$
DECLARE
    masked_text TEXT;
BEGIN
    -- 使用正则表达式替换敏感信息
    masked_text := regexp_replace(
        input_text,
        pattern_to_mask,
        '****-****-****-****',
        'g'
    );
    
    RETURN masked_text;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT mask_sensitive_info('My card is 1234-5678-9012-3456');
-- 返回 'My card is ****-****-****-****'

4. JSON 字符串处理

sql

-- 从 JSON 字符串中提取特定字段
CREATE OR REPLACE FUNCTION extract_json_value(
    json_str TEXT,
    field_path VARCHAR
)
RETURNS TEXT AS $$
DECLARE
    result TEXT;
BEGIN
    -- 尝试解析 JSON 并提取值
    BEGIN
        result := (json_str::jsonb)#>>string_to_array(field_path, '.');
    EXCEPTION
        WHEN invalid_text_representation THEN
            RAISE EXCEPTION 'Invalid JSON string: %', json_str;
    END;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT extract_json_value(
    '{"user": {"name": "Alice", "age": 30}}',
    'user.name'
);  -- 返回 'Alice'

5. CSV 字符串处理

sql

-- 解析 CSV 字符串为表格
CREATE OR REPLACE FUNCTION parse_csv_to_table(
    csv_data TEXT,
    delimiter CHAR(1) DEFAULT ','
)
RETURNS TABLE (
    row_number INTEGER,
    column1 TEXT,
    column2 TEXT,
    column3 TEXT
) AS $$
DECLARE
    lines TEXT[];
    line TEXT;
    parts TEXT[];
    i INTEGER := 0;
BEGIN
    -- 按行分割
    lines := string_to_array(csv_data, E'\n');
    
    FOREACH line IN ARRAY lines
    LOOP
        i := i + 1;
        
        -- 跳过空行
        IF line IS NULL OR TRIM(line) = '' THEN
            CONTINUE;
        END IF;
        
        -- 分割列
        parts := string_to_array(line, delimiter);
        
        -- 返回结果
        row_number := i;
        column1 := COALESCE(parts[1], '');
        column2 := COALESCE(parts[2], '');
        column3 := COALESCE(parts[3], '');
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM parse_csv_to_table('id,name,age
1,Alice,30
2,Bob,25');

6. 字符串验证函数

sql

-- 验证密码强度
CREATE OR REPLACE FUNCTION validate_password_strength(
    password VARCHAR
)
RETURNS BOOLEAN AS $$
BEGIN
    -- 至少8个字符
    IF LENGTH(password) < 8 THEN
        RETURN FALSE;
    END IF;
    
    -- 包含大写字母
    IF password !~ '[A-Z]' THEN
        RETURN FALSE;
    END IF;
    
    -- 包含小写字母
    IF password !~ '[a-z]' THEN
        RETURN FALSE;
    END IF;
    
    -- 包含数字
    IF password !~ '\d' THEN
        RETURN FALSE;
    END IF;
    
    -- 包含特殊字符
    IF password !~ '[!@#$%^&*(),.?":{}|<>]' THEN
        RETURN FALSE;
    END IF;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

7. 字符串格式化函数

sql

-- 格式化电话号码
CREATE OR REPLACE FUNCTION format_phone_number(
    phone TEXT
)
RETURNS TEXT AS $$
DECLARE
    cleaned_phone TEXT;
BEGIN
    -- 移除非数字字符
    cleaned_phone := regexp_replace(phone, '\D', '', 'g');
    
    -- 根据不同长度格式化
    CASE LENGTH(cleaned_phone)
        WHEN 10 THEN  -- 中国手机号
            RETURN format('+86 %s %s %s',
                SUBSTRING(cleaned_phone FROM 1 FOR 3),
                SUBSTRING(cleaned_phone FROM 4 FOR 4),
                SUBSTRING(cleaned_phone FROM 8 FOR 4));
        WHEN 11 THEN  -- 带国家码的手机号
            RETURN format('+%s %s %s %s',
                SUBSTRING(cleaned_phone FROM 1 FOR 2),
                SUBSTRING(cleaned_phone FROM 3 FOR 3),
                SUBSTRING(cleaned_phone FROM 6 FOR 4),
                SUBSTRING(cleaned_phone FROM 10 FOR 4));
        ELSE
            RETURN phone;  -- 无法识别格式，返回原样
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT format_phone_number('13800138000');  -- 返回 '+86 138 0013 8000'

8. URL 处理函数

sql

-- 解析 URL 参数
CREATE OR REPLACE FUNCTION get_url_parameter(
    url TEXT,
    param_name VARCHAR
)
RETURNS TEXT AS $$
DECLARE
    param_value TEXT;
    query_string TEXT;
    params TEXT[];
    param_pair TEXT[];
BEGIN
    -- 提取查询字符串
    query_string := SUBSTRING(url FROM '\?(.*)$');
    
    IF query_string IS NULL THEN
        RETURN NULL;
    END IF;
    
    -- 分割参数
    params := string_to_array(query_string, '&');
    
    FOREACH param_pair SLICE 1 IN ARRAY params
    LOOP
        param_pair := string_to_array(param_pair[1], '=');
        
        IF param_pair[1] = param_name THEN
            -- URL 解码
            param_value := REPLACE(param_pair[2], '+', ' ');
            param_value := REPLACE(param_value, '%20', ' ');
            -- 可以添加更多解码逻辑
            RETURN param_value;
        END IF;
    END LOOP;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

9. 字符串相似度比较

sql

-- 计算字符串相似度（使用 pg_trgm 扩展）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE OR REPLACE FUNCTION calculate_string_similarity(
    str1 VARCHAR,
    str2 VARCHAR
)
RETURNS FLOAT AS $$
BEGIN
    RETURN similarity(str1, str2);
END;
$$ LANGUAGE plpgsql;

-- 查找相似字符串
CREATE OR REPLACE FUNCTION find_similar_strings(
    search_str VARCHAR,
    threshold FLOAT DEFAULT 0.3
)
RETURNS TABLE (
    original_string VARCHAR,
    similarity_score FLOAT
) AS $$
BEGIN
    RETURN QUERY
    SELECT s.text, similarity(s.text, search_str) as score
    FROM strings_table s
    WHERE similarity(s.text, search_str) > threshold
    ORDER BY score DESC;
END;
$$ LANGUAGE plpgsql;

10. 字符串加密/哈希函数

sql

-- 使用 pgcrypto 扩展进行哈希
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION hash_password(
    password TEXT,
    salt TEXT DEFAULT gen_salt('bf', 8)  -- 自动生成盐值
)
RETURNS TEXT AS $$
BEGIN
    RETURN crypt(password, salt);
END;
$$ LANGUAGE plpgsql;

-- 验证密码
CREATE OR REPLACE FUNCTION verify_password(
    password TEXT,
    hashed_password TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN hashed_password = crypt(password, hashed_password);
END;
$$ LANGUAGE plpgsql;

字符串函数常用技巧

sql

-- 1. 处理 NULL 值
CREATE OR REPLACE FUNCTION safe_concat(
    str1 TEXT,
    str2 TEXT
)
RETURNS TEXT AS $$
BEGIN
    RETURN COALESCE(str1, '') || COALESCE(str2, '');
END;
$$ LANGUAGE plpgsql;

-- 2. 多语言支持（长度计算）
CREATE OR REPLACE FUNCTION character_count(
    text_content TEXT
)
RETURNS INTEGER AS $$
BEGIN
    -- 计算字符数（考虑多字节字符）
    RETURN LENGTH(text_content);
END;
$$ LANGUAGE plpgsql;

-- 3. 模板字符串填充
CREATE OR REPLACE FUNCTION fill_template(
    template TEXT,
    replacements JSONB
)
RETURNS TEXT AS $$
DECLARE
    result TEXT := template;
    key TEXT;
    value TEXT;
BEGIN
    FOR key, value IN SELECT * FROM jsonb_each_text(replacements)
    LOOP
        result := REPLACE(result, '{{' || key || '}}', value);
    END LOOP;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

性能优化建议

1. 使用 VARCHAR(n) 而非 TEXT：如果知道最大长度，使用有长度限制的类型

2. 避免字符串连接循环：使用 string_agg 或数组处理

3. 合理使用索引：为频繁搜索的字符串列创建索引

4. 考虑全文搜索：对于复杂搜索需求，使用 tsvector/tsquery

您需要处理什么具体的字符串场景？我可以为您提供更针对性的函数示例。