PostgreSQL函数的创建与使用指南

PostgreSQL函数的创建与使用指南

目录

• 函数概述
• 创建函数语法
• 函数参数详解
• 返回值类型
• 函数体结构
• 函数调用方法
• 示例函数
• 函数管理操作
• 使用注意事项

函数概述

PostgreSQL函数（也称为存储过程）是存储在数据库中的可重用代码块，具有以下特点：

• 封装复杂业务逻辑
• 提高代码复用性
• 减少网络传输开销
• 支持多种语言编写（SQL, PL/pgSQL, Python等）
• 增强数据安全性

创建函数语法

CREATE [OR REPLACE] FUNCTION function_name ([parameter_list])
    RETURNS return_type 
    LANGUAGE plpgsql
    AS $$
DECLARE
    -- 变量声明
BEGIN
    -- 函数逻辑
    RETURN value;
END;
$$;

函数参数详解

返回值类型

PostgreSQL函数支持多种返回类型：

• 基础类型：INTEGER, TEXT, DATE等
• 复合类型：表行类型
• 结果集：SETOF record
• 表：TABLE (column_list)
• 无返回值：VOID

函数体结构

DECLARE
    counter INTEGER := 0;  -- 变量声明
    user_name VARCHAR;
BEGIN
    -- 逻辑代码
    SELECT name INTO user_name FROM users WHERE id = user_id;
    
    -- 条件判断
    IF user_name IS NULL THEN
        RAISE EXCEPTION 'User not found';
    END IF;
    
    -- 循环处理
    FOR i IN 1..10 LOOP
        counter := counter + i;
    END LOOP;
    
    RETURN counter;  -- 返回值
EXCEPTION
    WHEN others THEN  -- 异常处理
        RETURN -1;
END;

函数调用方法

基本调用

SELECT calculate_salary(1001);

命名参数调用

SELECT create_user(
    name => 'John Doe', 
    email => 'john@example.com'
);

在DML语句中使用

INSERT INTO logs (message)
VALUES (generate_log('System started'));

示例函数

1. 基础函数：计算阶乘

CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    result INTEGER := 1;
    i INTEGER;
BEGIN
    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT factorial(5);  -- 返回120

2. 返回结果集：获取员工信息

CREATE FUNCTION get_employees(dept_id INT)
RETURNS TABLE (
    emp_id INT,
    emp_name VARCHAR,
    salary NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, salary
    FROM employees
    WHERE department_id = dept_id;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_employees(3);

3. 带默认参数：用户注册

CREATE FUNCTION register_user(
    username VARCHAR,
    password VARCHAR,
    is_active BOOLEAN DEFAULT true
) RETURNS INT AS $$
DECLARE
    user_id INT;
BEGIN
    INSERT INTO users (username, password, active)
    VALUES (username, password, is_active)
    RETURNING id INTO user_id;
    
    RETURN user_id;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT register_user('alice', 'secure123');

函数管理操作

查看函数定义

-- 查看所有函数
SELECT proname FROM pg_proc;

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

修改函数

-- 使用CREATE OR REPLACE更新函数体
CREATE OR REPLACE FUNCTION function_name(...) 
AS $$ ... $$;

-- 修改函数属性
ALTER FUNCTION function_name(...) 
RENAME TO new_name;

ALTER FUNCTION function_name(...) 
OWNER TO new_owner;

删除函数

DROP FUNCTION [IF EXISTS] function_name([parameter_types]);

函数权限管理

-- 授予执行权限
GRANT EXECUTE ON FUNCTION function_name([types]) TO role_name;

-- 撤销权限
REVOKE EXECUTE ON FUNCTION function_name([types]) FROM role_name;

使用注意事项

1. 性能优化：

   • 避免在函数中进行大量数据操作
   • 对查询使用适当的索引
   • 使用RETURN QUERY高效返回结果集

2. 事务控制：

   • 函数在事务中执行，不能包含COMMIT或ROLLBACK
   • 使用EXCEPTION块进行错误处理

3. 参数处理：

   • 明确指定参数模式(IN/OUT/INOUT)
   • 避免修改输入参数值

4. 版本控制：

   • 使用CREATE OR REPLACE更新函数
   • 维护函数变更脚本

5. 安全性：

   • 使用SECURITY DEFINER谨慎授权
   • 防止SQL注入，参数化所有输入值
   • 避免在函数中执行动态SQL

6. 调试技巧：

   RAISE NOTICE 'Variable value: %', var_name;  -- 输出调试信息
   

7. 最佳实践：

   • 保持函数功能单一
   • 限制函数长度（建议<100行）
   • 添加注释说明函数功能
   • 为复杂函数编写单元测试