PostgreSQL - 函数

PostgreSQL - 用户定义函数

PostgreSQL 中的用户定义函数（UDFs）是可重用的代码块，用于执行特定操作。它们存储在数据库中，可以像内置函数一样从 SQL 查询中调用。这允许您封装复杂的逻辑、减少代码重复并创建强大、模块化的数据库应用程序。

PostgreSQL 支持用各种语言编写的函数，其中 PL/pgSQL（过程语言/PostgreSQL）是最常见的。它还区分 FUNCTION（必须返回一个值）和 PROCEDURE（PostgreSQL 11 中添加，不返回任何值）。

创建函数的语法

在 PL/pgSQL 中创建函数的基本语法是：

CREATE [OR REPLACE] FUNCTION function_name(argument_list)
RETURNS return_datatype AS $$
-- The $$ symbols are 'dollar quotes', a modern way to write string literals
-- without needing to escape single quotes inside the function body.
-- $$ 符号是“美元引号”，一种现代编写字符串文字的方式，
-- 无需在函数体内部转义单引号。
DECLARE
    -- variable declarations here
    -- 变量声明在此处
BEGIN
    -- function logic here
    -- 函数逻辑在此处

    RETURN return_value; -- Must match the return_datatype
    -- 必须与返回数据类型匹配
END;
$$ LANGUAGE plpgsql;

关键组成部分：

CREATE OR REPLACE：一个方便的选项，允许您创建函数或在函数已存在时更新它，避免了 DROP 和 CREATE 的序列操作。
argument_list：逗号分隔的参数列表，每个参数都有名称和数据类型（例如，user_id INT, new_email TEXT）。
RETURNS return_datatype：指定函数将返回的值的数据类型（例如，INT、TEXT、BOOLEAN，甚至 TABLE(...)）。
DECLARE：可选部分，用于声明函数内部使用的局部变量。
BEGIN...END;：包含函数可执行部分的块。
LANGUAGE plpgsql：指定函数是用 PL/pgSQL 语言编写的。

示例 1：一个不带参数的简单函数

让我们创建一个函数，返回 employees 表中的员工总数。

-- Assuming the 'employees' table from the GROUP BY tutorial exists.
-- 假设 GROUP BY 教程中的 'employees' 表存在。

CREATE OR REPLACE FUNCTION get_total_employee_count()
RETURNS INTEGER AS $$
DECLARE
    total_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO total_count FROM employees;
    RETURN total_count;
END;
$$ LANGUAGE plpgsql;

要调用此函数，请在 SELECT 语句中使用它：

SELECT get_total_employee_count();

-- Result:
-- 结果：
 get_total_employee_count
--------------------------
                        6
(1 row)

示例 2：带参数的函数

一个更实用的函数可能需要参数。让我们创建一个返回特定部门平均工资的函数。

CREATE OR REPLACE FUNCTION get_avg_salary_for_department(dept_name TEXT)
RETURNS NUMERIC AS $$
DECLARE
    avg_salary NUMERIC;
BEGIN
    SELECT AVG(salary)
    INTO avg_salary
    FROM employees
    WHERE department = dept_name;

    RETURN avg_salary;
END;
$$ LANGUAGE plpgsql;

现在，您可以传入不同的部门名称来调用它：

SELECT get_avg_salary_for_department('Engineering');

-- Result:
-- 结果：
 get_avg_salary_for_department
-------------------------------
                  87500.000000

SELECT get_avg_salary_for_department('HR');

-- Result:
-- 结果：
 get_avg_salary_for_department
-------------------------------
                  61000.000000

安全最佳实践：`SECURITY INVOKER` 与 `SECURITY DEFINER`

默认情况下，函数以调用它们的用户权限运行（SECURITY INVOKER，即调用者安全）。这通常是最安全的选项。但是，有时您需要函数以创建它的用户权限运行（SECURITY DEFINER，即定义者安全）。

使用 SECURITY DEFINER 时务必极其谨慎。它非常强大，允许受限用户对他们通常无法访问的表执行特定的、受控的操作。一个常见的错误是未能在 SECURITY DEFINER 函数中正确清理输入，这可能导致权限升级攻击。

CREATE FUNCTION update_user_log()
RETURNS trigger AS $$
BEGIN
    -- This function might need to write to a log table that the calling user
    -- does not have direct access to.
    -- 此函数可能需要写入调用用户无直接访问权限的日志表。
    INSERT INTO audit_log(message) VALUES ('User updated profile.'); -- 用户更新了个人资料。
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

专业提示： 使用 SECURITY DEFINER 时，请务必在函数开头设置一个安全的 search_path 以防止搜索路径劫持：SET search_path = pg_catalog, public;