前言

在数据库开发中，经常会遇到重复执行的SQL逻辑（如数据校验、复杂计算、批量操作等）。如果每次都编写完整的SQL语句，不仅繁琐，还会导致代码冗余、维护困难。PostgreSQL 提供了函数（Function）与存储过程（Procedure）两种方式，允许开发者将复杂逻辑封装成可调用的代码块，实现代码复用与逻辑隔离。

PostgreSQL 支持多种编程语言编写函数/存储过程，其中 PL/pgSQL（PostgreSQL 自带的 procedural language）是最常用、最便捷的一种，它结合了 SQL 的强大功能和 procedural 语言的流程控制能力，无需额外安装即可使用。本文将重点讲解 PL/pgSQL 的基础语法，并通过实战示例，带你快速上手函数与存储过程的开发。

一、函数与存储过程的核心区别

在 PostgreSQL 中，函数（Function）和存储过程（Procedure）虽然都用于封装逻辑，但存在本质区别，很多开发者容易混淆，这里先明确两者的核心差异，避免后续使用出错。

对比维度	函数（Function）	存储过程（Procedure）
返回值	必须有返回值（可以是单个值、集合或表）	可以没有返回值，也可以通过 OUT/INOUT 参数返回多个值
调用方式	使用 SELECT 语句调用（如 SELECT 函数名(参数)）	使用 CALL 语句调用（如 CALL 存储过程名(参数)）
事务控制	不能直接包含事务控制语句（BEGIN/COMMIT/ROLLBACK），继承调用者的事务	可以包含事务控制语句，能独立控制事务的提交与回滚
适用场景	用于数据查询、计算，可嵌入 SELECT、WHERE 等子句中	用于批量操作、事务性处理（如批量插入、更新、删除）

简单总结：函数侧重“查询与计算”，存储过程侧重“执行操作与事务控制”。根据实际需求选择合适的方式，能让代码更简洁、更高效。

二、PL/pgSQL 基础语法

PL/pgSQL 是一种块结构语言，所有逻辑都封装在块（BLOCK）中，每个块包含声明部分、执行部分，可选异常处理部分。掌握基础语法是编写函数/存储过程的前提。

2.1 基本块结构

-- PL/pgSQL 块结构模板
[DECLARE
    -- 声明部分：定义变量、常量、游标等
    变量名 数据类型 [:= 初始值];
BEGIN
    -- 执行部分：编写SQL语句和流程控制逻辑
    执行语句;
EXCEPTION
    -- 异常处理部分：捕获并处理执行过程中的异常
    WHEN 异常类型 THEN
        异常处理语句;
END;]

说明：

• DECLARE 部分：可选，用于定义变量、常量、游标、自定义类型等，变量初始值可通过:= 赋值。

• BEGIN...END 部分：必选，核心执行逻辑，可包含 SQL 语句、流程控制（IF、LOOP、FOR 等）。

• EXCEPTION 部分：可选，用于捕获执行过程中的异常（如除以零、主键冲突等），避免程序中断。

2.2 变量声明与赋值

变量声明是 PL/pgSQL 中最基础的操作，常用数据类型与 PostgreSQL 一致（如 int4、varchar、date、boolean 等），赋值方式有两种：:= 和 SELECT INTO。

-- 示例：变量声明与赋值
DECLARE
    v_id int4 := 1; -- 声明整数变量v_id，初始值为1
    v_name varchar(50); -- 声明字符串变量v_name
    v_create_time date;
BEGIN
    -- 方式1：直接赋值
    v_name := 'PostgreSQL';
    
    -- 方式2：通过SELECT INTO赋值（从表中查询数据赋值给变量）
    SELECT create_time INTO v_create_time FROM users WHERE id = v_id;
END;

2.3 流程控制语句

PL/pgSQL 提供了常见的流程控制语句，用于实现复杂逻辑的分支和循环，核心包括 IF 条件判断、LOOP 循环、FOR 循环。

2.3.1 IF 条件判断

-- 示例：IF-ELSE 条件判断
DECLARE
    v_score int4 := 85;
    v_grade varchar(10);
BEGIN
    IF v_score >= 90 THEN
        v_grade := '优秀';
    ELSIF v_score >= 80 THEN
        v_grade := '良好';
    ELSIF v_score >= 60 THEN
        v_grade := '及格';
    ELSE
        v_grade := '不及格';
    END IF;
    -- 输出结果（使用RAISE NOTICE打印日志）
    RAISE NOTICE '成绩等级：%', v_grade;
END;

2.3.2 LOOP 循环（无限循环）

LOOP 用于实现无限循环，需通过 EXIT 语句手动退出循环，适用于不确定循环次数的场景。

-- 示例：LOOP 循环（计算1到10的和）
DECLARE
    v_sum int4 := 0;
    v_i int4 := 1;
BEGIN
    LOOP
        v_sum := v_sum + v_i;
        v_i := v_i + 1;
        -- 退出条件：v_i大于10时退出
        EXIT WHEN v_i > 10;
    END LOOP;
    RAISE NOTICE '1到10的和：%', v_sum;
END;

2.3.3 FOR 循环（确定循环次数）

FOR 循环适用于已知循环次数的场景，可直接遍历数字范围或查询结果集。

-- 示例1：遍历数字范围（1到5）
BEGIN
    FOR v_i IN 1..5 LOOP
        RAISE NOTICE '当前数字：%', v_i;
    END LOOP;
END;

-- 示例2：遍历查询结果集（遍历users表的id和name）
DECLARE
    v_id int4;
    v_name varchar(50);
BEGIN
    FOR v_id, v_name IN SELECT id, name FROM users LIMIT 5 LOOP
        RAISE NOTICE '用户ID：%，用户名：%', v_id, v_name;
    END LOOP;
END;

2.4 异常处理

在执行 SQL 语句时，可能会出现异常（如主键冲突、除以零、数据类型不匹配等），通过 EXCEPTION 部分捕获异常，可避免程序中断，并进行自定义处理。

-- 示例：异常处理（捕获除以零异常）
DECLARE
    v_a int4 := 10;
    v_b int4 := 0;
    v_result int4;
BEGIN
    v_result := v_a / v_b; -- 会触发除以零异常
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE '异常：除数不能为零！';
        v_result := 0; -- 异常处理后，给变量赋值默认值
END;
RAISE NOTICE '计算结果：%', v_result;

常用异常类型：division_by_zero（除以零）、unique_violation（主键/唯一约束冲突）、check_violation（检查约束冲突）、no_data_found（查询无结果）。

三、PostgreSQL 函数（Function）实战

函数的核心是“有返回值”，可用于查询数据、计算结果，支持多种返回类型（单个值、集合、表）。下面通过不同场景的示例，讲解函数的创建与调用。

3.1 基本函数（返回单个值）

场景：创建一个函数，根据用户ID查询用户名，返回单个字符串。

-- 创建函数：根据用户ID查询用户名
CREATE OR REPLACE FUNCTION get_username_by_id(p_user_id int4)
RETURNS varchar(50) -- 声明返回值类型
LANGUAGE plpgsql -- 指定使用PL/pgSQL语言
AS $$ -- 函数体开始标记（$$可替换为其他符号，避免与SQL中的引号冲突）
DECLARE
    v_username varchar(50);
BEGIN
    -- 从users表查询用户名，赋值给变量
    SELECT name INTO v_username FROM users WHERE id = p_user_id;
    -- 返回结果
    RETURN v_username;
EXCEPTION
    -- 捕获“无数据”异常，返回空值
    WHEN no_data_found THEN
        RETURN NULL;
END;
$$; -- 函数体结束标记

-- 调用函数（使用SELECT语句）
SELECT get_username_by_id(1); -- 传入用户ID=1，返回对应的用户名
SELECT get_username_by_id(999); -- 传入不存在的ID，返回NULL

说明：CREATE OR REPLACE FUNCTION 表示“如果函数存在则替换，不存在则创建”，避免重复创建报错。

3.2 带参数默认值的函数

场景：创建一个函数，计算两个整数的和，其中第二个参数有默认值（默认值为0）。

-- 创建带默认值的函数
CREATE OR REPLACE FUNCTION add_two_numbers(p_num1 int4, p_num2 int4 := 0)
RETURNS int4
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_num1 + p_num2;
END;
$$;

-- 调用方式1：传入两个参数
SELECT add_two_numbers(10, 20); -- 返回30

-- 调用方式2：只传入第一个参数（第二个参数使用默认值0）
SELECT add_two_numbers(10); -- 返回10

3.3 返回集合/表的函数

场景：创建一个函数，查询指定年龄段的用户列表，返回多个用户的ID、姓名、年龄，即返回一个表。

-- 方式1：使用RETURNS TABLE声明返回表结构
CREATE OR REPLACE FUNCTION get_users_by_age(p_min_age int4, p_max_age int4)
RETURNS TABLE(
    user_id int4,
    user_name varchar(50),
    user_age int4
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 返回查询结果集（与RETURNS TABLE的结构对应）
    RETURN QUERY
    SELECT id, name, age FROM users 
    WHERE age BETWEEN p_min_age AND p_max_age;
END;
$$;

-- 调用函数（返回表，可直接作为查询表使用）
SELECT * FROM get_users_by_age(18, 30);

-- 方式2：使用RETURNS SETOF声明返回集合（与表结构一致）
CREATE OR REPLACE FUNCTION get_users_by_gender(p_gender varchar(10))
RETURNS SETOF users -- 返回users表的所有字段
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM users WHERE gender = p_gender;
END;
$$;

-- 调用函数
SELECT * FROM get_users_by_gender('男') LIMIT 5;

说明：RETURN QUERY 用于返回查询结果集，适用于返回多个行的场景。

四、PostgreSQL 存储过程（Procedure）实战

存储过程的核心是“执行操作”，可包含事务控制，适合批量处理、复杂业务逻辑（如批量插入、数据迁移、事务性更新）。下面通过实战示例讲解存储过程的创建与调用。

4.1 基本存储过程（无返回值）

场景：创建一个存储过程，批量插入10条测试数据到users表，无需返回值。

-- 创建存储过程
CREATE OR REPLACE PROCEDURE batch_insert_users()
LANGUAGE plpgsql
AS $$
DECLARE
    v_i int4 := 1;
BEGIN
    -- 批量插入数据，使用LOOP循环
    LOOP
        INSERT INTO users(name, age, gender, create_time)
        VALUES (
            '测试用户' || v_i, -- 用户名：测试用户1、测试用户2...
            18 + (v_i % 20), -- 年龄：18-37之间
            CASE WHEN v_i % 2 = 0 THEN '女' ELSE '男' END, -- 性别：奇偶区分
            CURRENT_DATE -- 创建时间：当前日期
        );
        v_i := v_i + 1;
        EXIT WHEN v_i > 10; -- 插入10条后退出
    END LOOP;
    -- 提交事务（存储过程可直接控制事务）
    COMMIT;
    RAISE NOTICE '批量插入完成，共插入10条数据';
END;
$$;

-- 调用存储过程（使用CALL语句）
CALL batch_insert_users();

4.2 带OUT参数的存储过程（返回多个值）

场景：创建一个存储过程，统计users表的总人数、男性人数、女性人数，通过OUT参数返回结果。

-- 创建带OUT参数的存储过程
CREATE OR REPLACE PROCEDURE count_users(
    OUT total_count int4, -- 总人数
    OUT male_count int4, -- 男性人数
    OUT female_count int4 -- 女性人数
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 统计总人数
    SELECT COUNT(*) INTO total_count FROM users;
    -- 统计男性人数
    SELECT COUNT(*) INTO male_count FROM users WHERE gender = '男';
    -- 统计女性人数
    SELECT COUNT(*) INTO female_count FROM users WHERE gender = '女';
END;
$$;

-- 调用存储过程（接收OUT参数返回的值）
CALL count_users(:total, :male, :female); -- 方式1：使用变量接收
SELECT * FROM count_users(); -- 方式2：直接查询返回结果

4.3 带事务控制的存储过程

场景：创建一个存储过程，实现“转账”功能（从一个用户账户扣除金额，给另一个用户账户增加金额），包含事务控制，确保转账原子性（要么都成功，要么都失败）。

-- 假设存在account表（账户表），结构如下：
-- CREATE TABLE account(id int4 PRIMARY KEY, user_id int4, balance numeric(10,2));

-- 创建转账存储过程
CREATE OR REPLACE PROCEDURE transfer_money(
    p_from_user_id int4, -- 转出用户ID
    p_to_user_id int4, -- 转入用户ID
    p_amount numeric(10,2) -- 转账金额
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_from_balance numeric(10,2); -- 转出用户当前余额
BEGIN
    -- 1. 查询转出用户的余额
    SELECT balance INTO v_from_balance FROM account WHERE user_id = p_from_user_id;
    
    -- 2. 校验余额是否充足
    IF v_from_balance < p_amount THEN
        RAISE EXCEPTION '余额不足，当前余额：%，转账金额：%', v_from_balance, p_amount;
    END IF;
    
    -- 3. 执行转账操作（扣减转出用户余额，增加转入用户余额）
    UPDATE account SET balance = balance - p_amount WHERE user_id = p_from_user_id;
    UPDATE account SET balance = balance + p_amount WHERE user_id = p_to_user_id;
    
    -- 4. 提交事务
    COMMIT;
    RAISE NOTICE '转账成功！转出用户ID：%，转入用户ID：%，转账金额：%', p_from_user_id, p_to_user_id, p_amount;

EXCEPTION
    -- 捕获异常，回滚事务
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE '转账失败！原因：%', SQLERRM; -- SQLERRM：获取异常信息
END;
$$;

-- 调用转账存储过程
CALL transfer_money(1, 2, 100.00); -- 从用户1转账100元到用户2
CALL transfer_money(1, 2, 10000.00); -- 余额不足，转账失败，事务回滚

说明：SQLERRM 是 PL/pgSQL 的内置变量，用于获取当前异常的描述信息，方便排查问题。

五、函数与存储过程的管理

创建函数/存储过程后，需要掌握查看、修改、删除等管理操作，确保代码的可维护性。

5.1 查看函数/存储过程

-- 查看所有函数
\df

-- 查看指定函数的详细信息（包括语法、返回值、参数等）
\df+ 函数名

-- 查看所有存储过程
\dp

-- 查看指定存储过程的详细信息
\dp+ 存储过程名

-- 使用SQL查询系统表，查看函数信息
SELECT proname, proargtypes, prorettype 
FROM pg_proc 
WHERE proname = 'get_username_by_id';

5.2 修改函数/存储过程

修改函数/存储过程，使用 CREATE OR REPLACE 语句，与创建语句类似，只需修改函数体或参数即可。

-- 修改函数：修改get_username_by_id的返回值长度（从50改为100）
CREATE OR REPLACE FUNCTION get_username_by_id(p_user_id int4)
RETURNS varchar(100) -- 修改返回值长度
LANGUAGE plpgsql
AS $$
DECLARE
    v_username varchar(100);
BEGIN
    SELECT name INTO v_username FROM users WHERE id = p_user_id;
    RETURN v_username;
EXCEPTION
    WHEN no_data_found THEN
        RETURN NULL;
END;
$$;

5.3 删除函数/存储过程

-- 删除函数（需指定参数类型，避免同名不同参数的函数被误删）
DROP FUNCTION IF EXISTS get_username_by_id(int4);

-- 删除存储过程
DROP PROCEDURE IF EXISTS batch_insert_users;

-- 批量删除多个函数/存储过程
DROP FUNCTION IF EXISTS add_two_numbers(int4, int4), get_users_by_age(int4, int4);

说明：如果函数有重载（同名但参数不同），删除时必须指定参数类型，否则会报错。

六、实战注意事项

在实际开发中，使用 PL/pgSQL 编写函数/存储过程时，需要注意以下几点，避免踩坑：

1. 避免过度封装：简单的SQL查询（如单表查询）无需封装成函数，直接编写SQL更高效；只有重复执行、逻辑复杂的代码才适合封装。

2. 注意参数类型匹配：调用函数/存储过程时，传入的参数类型必须与声明的参数类型一致，否则会触发类型转换异常。

3. 慎用事务控制：函数中不能直接使用 COMMIT/ROLLBACK，需依赖调用者的事务；存储过程中使用事务控制时，要确保逻辑严谨，避免出现事务泄漏。

4. 优化性能：避免在循环中执行大量 INSERT/UPDATE/DELETE 操作（可使用批量操作替代）；对于大数据量的查询，可使用游标分页处理，避免内存溢出。

5. 添加异常处理：对于可能出现异常的场景（如数据不存在、约束冲突），必须添加异常处理，提高程序的健壮性。

总结

PL/pgSQL 是 PostgreSQL 中最常用的 procedural 语言，函数与存储过程则是 PL/pgSQL 的核心应用。函数侧重查询与计算，可嵌入 SQL 语句中使用；存储过程侧重执行操作与事务控制，适合批量处理和复杂业务逻辑。

通过本文的学习，你应该掌握了 PL/pgSQL 的基础语法、函数与存储过程的创建、调用及管理方法，并能通过实战示例解决实际开发中的问题。合理使用函数与存储过程，能有效提升代码复用性、简化复杂逻辑，让数据库开发更高效。