印刻数据库

外观

PostgreSQL 函数创建与管理 ​

函数创建基础 ​

1. CREATE FUNCTION 语法 ​

PostgreSQL 创建函数的基本语法：

sql
-- 创建函数的基本语法结构
CREATE [OR REPLACE] FUNCTION 函数名 (
    [参数名 参数类型 [DEFAULT 默认值]] [, ...]
) 
RETURNS 返回值类型
[LANGUAGE 语言名称] -- 如 SQL, plpgsql 等
[VOLATILITY {IMMUTABLE | STABLE | VOLATILE}] -- 函数稳定性
[CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT] -- NULL 处理
[SECURITY {DEFINER | INVOKER}] -- 执行权限
[COST 执行成本] -- 估计的执行成本
[ROWS 结果行数] -- 估计的结果行数
AS $$
-- 函数体内容
$$;

2. 简单 SQL 函数示例 ​

sql
-- 创建一个简单的 SQL 函数，用于计算两个整数的和
CREATE OR REPLACE FUNCTION add_numbers(第一个数 INTEGER, 第二个数 INTEGER)
RETURNS INTEGER -- 返回整数类型
LANGUAGE SQL -- 使用 SQL 语言
IMMUTABLE -- 函数结果仅依赖输入参数
RETURNS NULL ON NULL INPUT -- 任何输入为 NULL 则返回 NULL
AS $$
    SELECT 第一个数 + 第二个数; -- 函数体：返回两个数的和
$$;

-- 调用函数示例
SELECT add_numbers(10, 20);  -- 正常调用，返回 30
SELECT add_numbers(NULL, 20);  -- 包含 NULL 输入，返回 NULL

3. PL/pgSQL 函数示例 ​

sql
-- 创建一个 PL/pgSQL 函数，根据员工 ID 获取员工详细信息
CREATE OR REPLACE FUNCTION get_employee_by_id(员工ID INTEGER)
-- 定义返回的表结构
RETURNS TABLE(
    员工编号 INTEGER,
    名 TEXT,
    姓 TEXT,
    电子邮箱 TEXT
)
LANGUAGE plpgsql -- 使用 PL/pgSQL 语言
STABLE -- 函数在事务内结果稳定
AS $$
BEGIN
    -- 使用 RETURN QUERY 返回查询结果
    RETURN QUERY
    SELECT 
        e.employee_id AS 员工编号,
        e.first_name AS 名,
        e.last_name AS 姓,
        e.email AS 电子邮箱
    FROM employees e
    WHERE e.employee_id = 员工ID;
END;
$$;

-- 调用返回表类型的函数
SELECT * FROM get_employee_by_id(100);

函数参数与返回值 ​

1. 参数类型 ​

PostgreSQL 函数支持多种参数类型，包括基本类型、数组类型、复合类型等：

sql
-- 基本数据类型参数示例
CREATE OR REPLACE FUNCTION 基本类型示例(
    整数参数 INTEGER,
    文本参数 TEXT,
    布尔参数 BOOLEAN,
    数值参数 NUMERIC(10, 2),
    日期参数 DATE,
    时间戳参数 TIMESTAMP
)
RETURNS TEXT -- 返回文本类型结果
LANGUAGE plpgsql
AS $$
BEGIN
    -- 格式化输出所有参数值
    RETURN format('整数: %s, 文本: %s, 布尔: %s, 数值: %s, 日期: %s, 时间戳: %s',
        整数参数, 文本参数, 布尔参数, 数值参数, 日期参数, 时间戳参数);
END;
$$;

-- 数组类型参数示例
CREATE OR REPLACE FUNCTION 数组求和(数值数组 INTEGER[])
RETURNS INTEGER -- 返回整数类型结果
LANGUAGE SQL
IMMUTABLE -- 函数结果仅依赖输入参数
AS $$
    SELECT SUM(unnest(数值数组)); -- 展开数组并求和
$$;

-- 调用数组参数函数示例
SELECT 数组求和(ARRAY[1, 2, 3, 4, 5]);  -- 返回 15

-- 复合类型参数示例
CREATE OR REPLACE FUNCTION 员工信息(员工记录 employees)
RETURNS TEXT -- 返回文本类型结果
LANGUAGE plpgsql
AS $$
BEGIN
    -- 格式化输出员工信息
    RETURN format('员工: %s %s, 薪资: %s',
        员工记录.first_name, 员工记录.last_name, 员工记录.salary);
END;
$$;

-- 调用复合类型参数函数示例
SELECT 员工信息(e) FROM employees e WHERE e.employee_id = 100;

2. 返回值类型 ​

函数可以返回多种类型的值，包括基本类型、表类型、集合类型、复合类型和记录类型：

sql
-- 返回基本类型示例
CREATE OR REPLACE FUNCTION 返回基本类型() RETURNS INTEGER
LANGUAGE SQL AS $$ SELECT 42; $$;

-- 返回表类型示例
CREATE OR REPLACE FUNCTION 返回表类型()
RETURNS TABLE(编号 INTEGER, 姓名 TEXT)
LANGUAGE SQL
AS $$
    SELECT employee_id AS 编号, first_name AS 姓名 FROM employees LIMIT 5;
$$;

-- 返回集合类型示例
CREATE OR REPLACE FUNCTION 返回集合类型()
RETURNS SETOF INTEGER
LANGUAGE SQL
AS $$
    SELECT employee_id FROM employees LIMIT 5;
$$;

-- 返回复合类型示例
CREATE OR REPLACE FUNCTION 返回复合类型()
RETURNS employees -- 返回 employees 表的复合类型
LANGUAGE SQL
AS $$
    SELECT * FROM employees WHERE employee_id = 100;
$$;

-- 返回记录类型示例
CREATE OR REPLACE FUNCTION 返回记录类型()
RETURNS RECORD -- 返回自定义记录类型
LANGUAGE plpgsql
AS $$
DECLARE
    结果记录 RECORD;
BEGIN
    SELECT employee_id, first_name INTO 结果记录
    FROM employees WHERE employee_id = 100;
    RETURN 结果记录;
END;
$$;

-- 调用返回记录类型的函数示例
SELECT * FROM 返回记录类型() AS (编号 INTEGER, 姓名 TEXT);

3. OUT 参数 ​

使用 OUT 参数可以从函数中返回多个值，无需定义复杂的返回类型：

sql
-- 使用 OUT 参数返回多个值示例
CREATE OR REPLACE FUNCTION 获取员工信息(
    员工ID INTEGER,
    OUT 名 TEXT,
    OUT 姓 TEXT,
    OUT 薪资 NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 将查询结果赋值给 OUT 参数
    SELECT first_name, last_name, salary
    INTO 名, 姓, 薪资
    FROM employees
    WHERE employee_id = 员工ID;
END;
$$;

-- 调用带 OUT 参数的函数示例
SELECT 获取员工信息(100);  -- 返回单行结果
-- 或者使用表方式调用
SELECT * FROM 获取员工信息(100);  -- 返回带列名的结果

函数安全性设置 ​

1. SECURITY DEFINER 与 SECURITY INVOKER ​

PostgreSQL 函数有两种执行权限模式：

sql
-- 创建 SECURITY DEFINER 函数（以函数所有者权限执行）
CREATE OR REPLACE FUNCTION 定义者权限函数()
RETURNS TABLE(表名 TEXT, 所有者 TEXT)
LANGUAGE SQL
SECURITY DEFINER -- 以函数所有者权限执行
SET search_path = public -- 设置搜索路径
AS $$
    SELECT tablename, tableowner FROM pg_tables;
$$;

-- 创建 SECURITY INVOKER 函数（以调用者权限执行）
CREATE OR REPLACE FUNCTION 调用者权限函数()
RETURNS TABLE(表名 TEXT, 所有者 TEXT)
LANGUAGE SQL
SECURITY INVOKER -- 以函数调用者权限执行
AS $$
    SELECT tablename, tableowner FROM pg_tables;
$$;

-- 为普通用户授予执行权限
GRANT EXECUTE ON FUNCTION 定义者权限函数() TO 测试用户;
GRANT EXECUTE ON FUNCTION 调用者权限函数() TO 测试用户;

2. 设置函数权限 ​

sql
-- 授予执行权限给特定用户
GRANT EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) TO 测试用户;

-- 授予执行权限给所有用户
GRANT EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) TO PUBLIC;

-- 撤销执行权限
REVOKE EXECUTE ON FUNCTION add_numbers(INTEGER, INTEGER) FROM 测试用户;

-- 查看函数权限
SELECT 
    nspname AS 架构名,
    proname AS 函数名,
    pg_get_functiondef(p.oid) AS 函数定义,
    relacl AS 权限
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE proname = 'add_numbers';

函数管理操作 ​

1. 修改函数 ​

可以使用 CREATE OR REPLACE FUNCTION 语句修改现有函数：

sql
-- 修改现有函数（保留相同的参数签名）
CREATE OR REPLACE FUNCTION add_numbers(第一个数 INTEGER, 第二个数 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT
AS $$
    SELECT 第一个数 + 第二个数 + 0;  -- 示例修改：增加了一个无意义的 +0
$$;

-- 修改函数的 volatility 属性
ALTER FUNCTION add_numbers(INTEGER, INTEGER) SET VOLATILITY IMMUTABLE;

-- 修改函数的搜索路径
ALTER FUNCTION add_numbers(INTEGER, INTEGER) SET search_path = public, pg_catalog;

-- 重命名函数
ALTER FUNCTION add_numbers(INTEGER, INTEGER) RENAME TO 整数相加;

-- 更改函数所有者
ALTER FUNCTION 整数相加(INTEGER, INTEGER) OWNER TO 新所有者;

-- 更改函数所属架构
ALTER FUNCTION 整数相加(INTEGER, INTEGER) SET SCHEMA 新架构;

2. 删除函数 ​

使用 DROP FUNCTION 语句删除不再需要的函数：

sql
-- 删除特定签名的函数
DROP FUNCTION IF EXISTS 整数相加(INTEGER, INTEGER);

-- 删除所有同名函数（如果有重载）
DROP FUNCTION IF EXISTS 整数相加 CASCADE;

-- 删除函数及其依赖对象
DROP FUNCTION IF EXISTS get_employee_by_id(INTEGER) CASCADE;

3. 查看函数信息 ​

可以通过多种方式查看函数的详细信息：

sql
-- 在 psql 命令行中查看函数列表
\df  -- 查看所有函数
\df add_numbers  -- 查看特定函数

-- 查看函数完整定义
SELECT pg_get_functiondef('add_numbers(INTEGER, INTEGER)'::regprocedure);

-- 查询系统表获取函数详细信息
SELECT 
    n.nspname AS 架构名,
    p.proname AS 函数名,
    pg_get_function_arguments(p.oid) AS 参数列表,
    pg_get_function_result(p.oid) AS 返回类型,
    l.lanname AS 语言,
    p.provolatile AS 稳定性,
    p.prosecdef AS 是否定义者权限,
    p.pronargs AS 参数数量
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE p.proname LIKE 'add%';

-- 查看函数的依赖关系
SELECT 
    dependent_ns.nspname AS 依赖架构,
    dependent_view.relname AS 依赖对象,
    source_ns.nspname AS 源架构,
    source_table.relname AS 源对象,
    pg_describe_object(classid, objid, objsubid) AS 依赖类型
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE source_ns.nspname = 'public' AND source_table.relname = 'your_function';

函数性能优化 ​

1. 设置正确的稳定性属性 ​

函数的稳定性属性（Volatility）会影响 PostgreSQL 的查询优化，应根据实际情况设置：

sql
-- IMMUTABLE：结果仅依赖输入参数，相同输入总是返回相同结果
CREATE OR REPLACE FUNCTION 不可变函数(输入值 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE -- 不可变函数
AS $$ SELECT 输入值 * 2; $$;

-- STABLE：在事务内相同输入返回相同结果，不同事务可能不同
CREATE OR REPLACE FUNCTION 稳定函数()
RETURNS TIMESTAMP
LANGUAGE SQL
STABLE -- 稳定函数
AS $$ SELECT CURRENT_TIMESTAMP; $$;

-- VOLATILE：每次调用可能返回不同结果（默认值）
CREATE OR REPLACE FUNCTION 可变函数()
RETURNS INTEGER
LANGUAGE plpgsql
VOLATILE -- 可变函数
AS $$
BEGIN
    -- 返回当前活动连接数，每次调用可能不同
    RETURN (SELECT COUNT(*) FROM pg_stat_activity);
END;
$$;

2. 优化函数体实现 ​

避免在函数中使用低效的实现方式，如不必要的循环：

sql
-- 优化前：使用循环处理
CREATE OR REPLACE FUNCTION 优化前函数(限制数 INTEGER)
RETURNS TABLE(编号 INTEGER, 名称 TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    计数器 INTEGER := 1;
BEGIN
    -- 使用 WHILE 循环生成结果
    WHILE 计数器 <= 限制数 LOOP
        编号 := 计数器;
        名称 := '项目 ' || 计数器;
        RETURN NEXT;  -- 返回当前行
        计数器 := 计数器 + 1;
    END LOOP;
END;
$$;

-- 优化后：使用 RETURN QUERY 和 generate_series
CREATE OR REPLACE FUNCTION 优化后函数(限制数 INTEGER)
RETURNS TABLE(编号 INTEGER, 名称 TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 使用 RETURN QUERY 直接返回查询结果
    RETURN QUERY
    SELECT 
        g.id AS 编号,
        '项目 ' || g.id AS 名称
    FROM generate_series(1, 限制数) AS g(id);
END;
$$;

3. 选择合适的函数语言 ​

根据函数逻辑复杂度选择合适的语言：

sql
-- 对于简单逻辑，使用 SQL 语言更高效
CREATE OR REPLACE FUNCTION SQL版本(输入值 INTEGER)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT 输入值 * 2 + 1; -- 简单计算，SQL 足够
$$;

-- 对于复杂逻辑，使用 PL/pgSQL 更合适
CREATE OR REPLACE FUNCTION PLpgSQL版本(输入值 INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    结果 INTEGER;
BEGIN
    -- 包含条件判断，适合使用 PL/pgSQL
    IF 输入值 < 0 THEN
        结果 := ABS(输入值) * 2 + 1;
    ELSE
        结果 := 输入值 * 2 + 1;
    END IF;
    RETURN 结果;
END;
$$;

函数重载 ​

PostgreSQL 支持函数重载，即可以创建同名但参数列表不同的多个函数：

sql
-- 重载函数示例 1：计算总价（无折扣）
CREATE OR REPLACE FUNCTION 计算总价(单价 NUMERIC, 数量 INTEGER)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT 单价 * 数量; -- 直接计算总价
$$;

-- 重载函数示例 2：计算总价（带折扣）
CREATE OR REPLACE FUNCTION 计算总价(单价 NUMERIC, 数量 INTEGER, 折扣 NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT 单价 * 数量 * (1 - 折扣 / 100); -- 计算折扣后总价
$$;

-- 调用不同的重载函数
SELECT 计算总价(10.50, 2);  -- 调用第一个函数，返回 21.00
SELECT 计算总价(10.50, 2, 10);  -- 调用第二个函数，返回 18.90（10% 折扣）

-- 查看所有重载函数
SELECT 
    proname AS 函数名,
    pg_get_function_arguments(oid) AS 参数列表,
    pg_get_function_result(oid) AS 返回类型
FROM pg_proc
WHERE proname = '计算总价';

函数最佳实践 ​

1. 命名规范 ​

  • 使用描述性的函数名，如 get_employee_by_id 而不是 f1
  • 函数名使用小写，单词之间用下划线分隔
  • 参数名使用 p_ 前缀，如 p_employee_id
  • 变量名使用 v_ 前缀，如 v_counter
  • 返回表的列名使用清晰的描述性名称

2. 安全性最佳实践 ​

  • 限制 SECURITY DEFINER 函数的使用，仅在必要时使用
  • 为 SECURITY DEFINER 函数设置明确的 search_path
  • 不要在 SECURITY DEFINER 函数中使用动态 SQL，或确保正确转义
  • 仅授予函数执行权限，不要授予不必要的权限
  • 定期审计 SECURITY DEFINER 函数

3. 性能最佳实践 ​

  • 为函数设置正确的 VOLATILITY 属性
  • 对于简单逻辑，优先使用 LANGUAGE SQL
  • 避免在循环中执行 DML 操作
  • 使用 RETURN QUERY 替代 RETURN NEXT 循环
  • 为函数依赖的表创建合适的索引
  • 避免在函数中使用 SELECT *，只选择必要的列

4. 可维护性最佳实践 ​

  • 编写清晰的注释，解释函数用途、参数和返回值
  • 保持函数简短，每个函数只做一件事
  • 使用 CREATE OR REPLACE FUNCTION 便于更新
  • 为函数编写单元测试
  • 记录函数的变更历史
  • 使用版本控制管理函数定义

常见问题（FAQ） ​

Q1：如何查看函数的执行计划？ ​

A1：使用 EXPLAIN ANALYZE 查看函数执行计划：

sql
-- 查看函数执行计划
EXPLAIN ANALYZE SELECT * FROM get_employee_by_id(100);

-- 查看函数内部查询的执行计划（需要修改函数）
CREATE OR REPLACE FUNCTION get_employee_by_id(p_employee_id INTEGER)
RETURNS TABLE(
    employee_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    email TEXT
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    -- 添加 EXPLAIN 语句（仅用于调试）
    RAISE NOTICE '执行计划: %', 
        (EXPLAIN (FORMAT JSON) SELECT * FROM employees WHERE employee_id = p_employee_id)::TEXT;
    
    RETURN QUERY
    SELECT 
        e.employee_id,
        e.first_name,
        e.last_name,
        e.email
    FROM employees e
    WHERE e.employee_id = p_employee_id;
END;
$$;

Q2：如何调试 PL/pgSQL 函数？ ​

A2：调试 PL/pgSQL 函数的方法：

  1. 使用 RAISE 语句输出调试信息：

    sql
    RAISE NOTICE '变量值: %', v_variable;
RAISE LOG '执行到这里';

  2. 使用 pgAdmin 调试器：pgAdmin 提供了图形化的调试界面

  3. 查看函数调用日志：使用 pg_stat_statements 查看函数执行统计

  4. 使用自动跟踪：配置 auto_explain 扩展记录慢查询

Q3：如何处理函数中的异常？ ​

A3：在 PL/pgSQL 函数中使用异常处理：

sql
CREATE OR REPLACE FUNCTION f_exception_handling(p_value INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- 尝试执行可能出错的操作
    IF p_value = 0 THEN
        RAISE division_by_zero;
    END IF;
    
    RETURN 100 / p_value;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE '除数不能为零';
        RETURN NULL;
    
    WHEN OTHERS THEN
        RAISE NOTICE '发生错误: %', SQLERRM;
        RETURN NULL;
END;
$$;

Q4：如何创建递归函数？ ​

A4：创建递归函数示例：

sql
-- 创建一个递归函数计算阶乘
CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    IF n < 0 THEN
        RAISE EXCEPTION '阶乘不能为负数';
    ELSIF n = 0 OR n = 1 THEN
        RETURN 1;
    ELSE
        RETURN n * factorial(n - 1);
    END IF;
END;
$$;

-- 调用递归函数
SELECT factorial(5);  -- 返回 120

Q5：如何创建聚合函数？ ​

A5：创建自定义聚合函数：

sql
-- 创建一个自定义聚合函数，计算字符串连接
CREATE AGGREGATE string_agg_custom(
    basetype TEXT,
    sfunc TEXT,
    stype TEXT,
    initcond TEXT
);

-- 或者使用已有的聚合函数
CREATE OR REPLACE AGGREGATE array_agg_custom(anyelement)
(  
    sfunc = array_append,  -- 状态转换函数
    stype = anyarray,      -- 状态类型
    initcond = '{}'        -- 初始状态
);

-- 使用自定义聚合函数
SELECT department_id, array_agg_custom(employee_id) AS employee_ids
FROM employees
GROUP BY department_id;

Q6：如何使用动态 SQL？ ​

A6：在 PL/pgSQL 函数中使用动态 SQL：

sql
-- 创建一个使用动态 SQL 的函数
CREATE OR REPLACE FUNCTION f_dynamic_sql(p_table_name TEXT, p_column_name TEXT, p_value INTEGER)
RETURNS TABLE(result_id INTEGER, result_name TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_query TEXT;
BEGIN
    -- 构建动态 SQL 查询
    v_query := format(
        'SELECT %I, %I FROM %I WHERE %I = $1',
        'id', p_column_name, p_table_name, 'id'
    );
    
    -- 执行动态 SQL
    RETURN QUERY EXECUTE v_query USING p_value;
END;
$$;

-- 调用动态 SQL 函数
SELECT * FROM f_dynamic_sql('employees', 'first_name', 100);