到目前为止，我们与数据库的交互方式都是从外部客户端（如 psql 或应用程序）发送 SQL 语句，数据库执行后返回结果。这种方式对于大多数场景都工作得很好。

但有时，我们会遇到一些复杂的业务逻辑，它可能需要多条 SQL 查询、条件判断、循环等操作才能完成。如果把这些逻辑都放在应用程序端，可能会导致：

网络延迟：应用程序和数据库之间需要多次网络往返，每次往返都有延迟。
代码重复：多个不同的应用程序或服务可能都需要实现相同的业务逻辑。
权限管理复杂：为了完成这个业务，你可能需要给应用用户授予底层多个表的权限，增加了安全风险。

为了解决这些问题，PostgreSQL 允许我们直接在数据库服务器内部创建和执行自定义函数 (Functions) 和存储过程 (Procedures)。我们可以用多种语言来编写它们，其中最常用、最强大的就是 PostgreSQL 的过程语言——PL/pgSQL。

PL/pgSQL 是一种功能完备的、块结构的编程语言。它让你可以在数据库里使用变量、条件判断 (IF...THEN...ELSE)、循环 (LOOP, FOR, WHILE) 等编程元素，并将它们与 SQL 查询无缝地结合起来。

23.1 创建自定义函数 (CREATE FUNCTION)

函数 (Function) 通常用于执行计算并返回一个值。这个返回值可以是一个单一的值（如 INTEGER, TEXT），也可以是一组记录（像一张表）。

基本语法:

CREATE OR REPLACE FUNCTION function_name(parameter_list)
RETURNS return_type AS $
DECLARE
    -- 变量声明区
BEGIN
    -- 函数体，包含 PL/pgSQL 和 SQL 语句
    RETURN return_value;
END;
$ LANGUAGE plpgsql;

CREATE OR REPLACE: 如果同名函数已存在，就替换它。这在开发时很方便。
function_name(parameter_list): 函数名和参数列表。参数格式为 param_name param_type。
RETURNS return_type: 定义函数返回值的类型。
$ ... $: 这是“美元符引用 (Dollar Quoting)”，是 PostgreSQL 中定义函数体字符串的一种推荐方式。你也可以用传统的单引号 '...'，但如果函数体内部本身就包含单引号，处理起来会很麻烦。
DECLARE ... BEGIN ... END;: 这是 PL/pgSQL 的块结构。DECLARE 部分用于声明变量。
LANGUAGE plpgsql: 指明我们使用的语言是 PL/pgSQL。

场景 1：一个简单的计算函数
创建一个函数，计算两个数的和。

CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $
BEGIN
    RETURN a + b;
END;
$ LANGUAGE plpgsql;

如何使用它？
就像使用内置函数一样，在 SELECT 语句中调用。

SELECT add_numbers(5, 10);
--  add_numbers
-- -------------
--           15

场景 2：一个更实用的数据库函数
创建一个函数，根据用户 ID 获取该用户的总订单金额。

CREATE OR REPLACE FUNCTION get_user_total_order_amount(p_user_id INT)
RETURNS NUMERIC AS $
DECLARE
    total_amount NUMERIC;
BEGIN
    -- 执行 SQL 查询，并将结果存入变量中
    SELECT SUM(amount) INTO total_amount
    FROM orders
    WHERE user_id = p_user_id;

    -- 如果用户没有订单，SUM 会返回 NULL，我们处理一下
    IF total_amount IS NULL THEN
        RETURN 0;
    END IF;

    RETURN total_amount;
END;
$ LANGUAGE plpgsql;

p_user_id: 给参数加一个前缀（如 p_）以区分表字段，是一个好习惯。
SELECT ... INTO ...: 这是将查询结果赋值给变量的标准方式。

使用它:

SELECT get_user_total_order_amount(1);

23.2 存储过程 (CREATE PROCEDURE)

函数和存储过程非常相似，主要区别在于：

函数：主要目的是返回值，通常不能在函数内部控制事务（比如 COMMIT 或 ROLLBACK）。
存储过程 (Procedure)：主要目的是执行一系列操作，它不返回值。但它可以在内部控制事务。

存储过程是在 PostgreSQL 11 中才正式引入的。在之前的版本，人们通常用返回 VOID 的函数来模拟它。

场景：我们想创建一个存储过程来执行之前那个经典的银行转账操作。

CREATE OR REPLACE PROCEDURE transfer_money(
    from_account VARCHAR,
    to_account VARCHAR,
    amount NUMERIC
) AS $
BEGIN
    -- A 账户扣款
    UPDATE accounts
    SET balance = balance - amount
    WHERE user_name = from_account;

    -- B 账户加款
    UPDATE accounts
    SET balance = balance + amount
    WHERE user_name = to_account;

    -- 在存储过程内部，我们可以提交事务
    COMMIT;
END;
$ LANGUAGE plpgsql;

如何使用它？
使用 CALL 命令来调用存储过程。

CALL transfer_money('用户A', '用户B', 100.00);

23.3 触发器 (TRIGGER)

触发器是一种更特殊的数据库对象。它是一个与特定表上的特定事件（INSERT, UPDATE, DELETE）相关联的特殊函数。当这些事件发生时，触发器函数会自动被数据库触发执行。

触发器非常强大，可以用来实现复杂的审计日志、数据校验、自动更新其他表等功能。

创建一个触发器需要两步：

创建一个触发器函数 (Trigger Function)。这个函数比较特殊，它不接受普通参数，并且必须返回 TRIGGER 类型。
创建一个触发器 (Trigger)，将这个函数绑定到指定的表和事件上。

场景：我们想创建一个“审计日志”。每当 users 表中的某条记录被修改时，我们都想在另一张 audit_log 表中记录下谁、在何时、修改了什么。

第一步：创建审计日志表

CREATE TABLE audit_log (
    log_id SERIAL PRIMARY KEY,
    table_name TEXT,
    user_id_changed INT,
    change_timestamp TIMESTAMPTZ,
    action TEXT
);

第二步：创建触发器函数
在触发器函数内部，有一些特殊的变量可用，比如：

NEW: 一行伪记录，包含了 INSERT 或 UPDATE 之后的新数据。
OLD: 一行伪记录，包含了 UPDATE 或 DELETE 之前的旧数据。
TG_OP: 一个字符串，表示触发这个函数的操作 ('INSERT', 'UPDATE', 'DELETE')。

CREATE OR REPLACE FUNCTION log_user_change()
RETURNS TRIGGER AS $
BEGIN
    -- 如果是 UPDATE 操作
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_log (table_name, user_id_changed, change_timestamp, action)
        VALUES ('users', OLD.user_id, now(), 'UPDATED');
        RETURN NEW; -- 对于行级 UPDATE/INSERT 触发器，必须返回 NEW
    -- 如果是 DELETE 操作
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_log (table_name, user_id_changed, change_timestamp, action)
        VALUES ('users', OLD.user_id, now(), 'DELETED');
        RETURN OLD; -- 对于行级 DELETE 触发器，必须返回 OLD
    END IF;
    RETURN NULL; -- 对于其他情况，不执行任何操作
END;
$ LANGUAGE plpgsql;

第三步：创建触发器，将函数绑定到表上

CREATE TRIGGER users_audit_trigger
-- 在 UPDATE 或 DELETE 操作之后，对每一行执行
AFTER UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_change();

AFTER: 表示在操作完成后触发。也可以是 BEFORE。
FOR EACH ROW: 表示这是一个“行级触发器”，即每影响一行，函数就执行一次。还有一个 FOR EACH STATEMENT（语句级触发器）。

现在，试试看！

UPDATE users SET age = age + 1 WHERE user_id = 1;
SELECT * FROM audit_log;

你会发现在 audit_log 表中自动多出了一条记录！

本章小结

你已经掌握了在数据库内部编写和执行代码的强大能力！

我们学会了创建和使用自定义函数 (FUNCTION)，来封装计算逻辑并返回值。
了解了存储过程 (PROCEDURE) 与函数的区别，以及如何用它来执行一系列操作和控制事务。
掌握了触发器 (TRIGGER) 这个强大的自动化工具，它能在特定数据事件发生时自动执行逻辑。
初步认识了 PL/pgSQL 语言的基本语法，包括变量、条件判断和特殊的触发器变量。

将部分业务逻辑下沉到数据库中，可以极大地提升性能、增强数据安全性和代码复用性。但也要避免过度使用，以免让数据库变得过于“重”，难以维护。

在下一章，我们将学习一项至关重要的 DBA 技能——备份与恢复。我们将学习如何保护我们的数据，以防硬件故障、人为误操作等灾难的发生。我们下一章见！

顾乔芝士网

持续更新的前后端开发技术栈

第 23 章:函数与存储过程 (PL/pgSQL) - PostgreSQL入门

23.1 创建自定义函数 (CREATE FUNCTION)

23.2 存储过程 (CREATE PROCEDURE)

23.3 触发器 (TRIGGER)