PostgreSQL里的PL/pgSQL到底是啥？能让SQL从“说目标”变“讲步骤”？

PL/pgSQL 是什么？

PL/pgSQL 是 PostgreSQL 自带的过程化SQL语言（Procedural Language/PostgreSQL Structured Query Language），它将 SQL 的声明式语法（比如 SELECT、INSERT）与过程式控制结构（比如条件判断、循环、错误处理）结合，让你能编写更复杂的数据库逻辑——比如计算、批量操作、事务控制等。

简单来说，SQL 是“做什么”，PL/pgSQL 是“怎么一步步做”。比如：用 SQL 可以查“用户表有多少人”，用 PL/pgSQL 可以写一个函数，先查数量，再根据数量发送通知，最后返回结果。

PL/pgSQL 函数的基本结构

在 PostgreSQL 中，存储过程通常以函数（FUNCTION）或过程（PROCEDURE）的形式存在。我们先从最基础的函数讲起，它的核心结构是：

CREATE OR REPLACE FUNCTION 函数名(参数列表)
RETURNS 返回类型 AS $$  -- $$ 是字符串分隔符（代替单引号）
DECLARE
    -- 变量声明（可选）
BEGIN
    -- 逻辑代码（必须）
EXCEPTION
    -- 错误处理（可选）
END;
$$ LANGUAGE plpgsql;  -- 指定语言为PL/pgSQL

示例：一个简单的加法函数

我们写一个计算两个整数之和的函数：

-- 创建函数：计算两数之和
CREATE OR REPLACE FUNCTION add_two_numbers(
    a INT,  -- 输入参数：整数a
    b INT   -- 输入参数：整数b
)
RETURNS INT AS $$
BEGIN
    RETURN a + b;  -- 返回结果
END;
$$ LANGUAGE plpgsql;

-- 调用函数：用SELECT获取返回值
SELECT add_two_numbers(3, 5);  -- 结果：8

关键点：

• CREATE OR REPLACE：如果函数已存在，替换它（避免重复删除重建）；
• RETURNS INT：指定函数返回整数；
• $$ ... $$：是美元引号，用于包裹PL/pgSQL代码（避免与SQL中的单引号冲突）；
• 调用函数用SELECT（因为函数有返回值）。

变量与参数声明

PL/pgSQL 允许你声明变量（存储中间结果）和参数（接收外部输入），常见类型包括：

1. 参数类型

参数支持 IN（默认，输入）、OUT（输出）、INOUT（既输入又输出）三种模式：

-- 示例：计算两数之和与乘积（INOUT参数）
CREATE OR REPLACE FUNCTION calc_sum_product(
    IN a INT,
    IN b INT,
    OUT sum INT,
    OUT product INT
) AS $$
BEGIN
    sum := a + b;       -- 赋值给OUT参数sum
    product := a * b;   -- 赋值给OUT参数product
END;
$$ LANGUAGE plpgsql;

-- 调用：获取多个返回值
SELECT * FROM calc_sum_product(3, 5);  -- 结果：sum=8, product=15

2. 变量声明

用 DECLARE 块声明变量，支持：

• 基本类型（如 INT、TEXT、NUMERIC）；
• 表行类型（%ROWTYPE，对应表的一行数据）；
• 记录类型（RECORD，动态存储一行数据，无固定结构）；
• 引用类型（%TYPE，继承列或变量的类型）。

示例：

CREATE OR REPLACE FUNCTION get_user_info(
    user_id INT
) RETURNS TEXT AS $$
DECLARE
    v_user users%ROWTYPE;  -- 行类型变量：对应users表的一行
    v_age INT;             -- 基本类型变量
BEGIN
    -- 查询用户信息存入v_user
    SELECT * INTO v_user FROM users WHERE id = user_id;
    
    -- 计算年龄（假设users表有birth_year列）
    v_age := EXTRACT(YEAR FROM CURRENT_DATE) - v_user.birth_year;
    
    -- 返回用户信息
    RETURN 'User: ' || v_user.name || ', Age: ' || v_age;
END;
$$ LANGUAGE plpgsql;

关键点：

• %ROWTYPE：确保变量结构与表一致（表结构变化时，变量自动同步）；
• :=：是PL/pgSQL的赋值运算符（代替SQL的=）；
• ||：字符串拼接运算符（类似Java的+）。

基本语句与动态SQL

PL/pgSQL 的核心是执行SQL语句和处理结果，常见操作包括：

1. 直接执行SQL

如果SQL语句是固定的，可以直接写在BEGIN...END中：

-- 示例：插入用户并返回ID
CREATE OR REPLACE FUNCTION insert_user(
    user_name TEXT,
    user_age INT
) RETURNS INT AS $$
DECLARE
    new_user_id INT;
BEGIN
    INSERT INTO users (name, age)
    VALUES (user_name, user_age)
    RETURNING id INTO new_user_id;  -- 将插入的ID存入变量
    
    RETURN new_user_id;
END;
$$ LANGUAGE plpgsql;

关键点：RETURNING ... INTO：将INSERT/UPDATE/DELETE的结果存入变量。

2. 动态SQL（处理可变SQL）

如果SQL语句是动态的（比如表名或条件由参数决定），需要用 EXECUTE 语句，必须结合USING绑定参数（避免SQL注入）：

-- 示例：动态查询表的行数
CREATE OR REPLACE FUNCTION get_table_row_count(
    table_name TEXT
) RETURNS BIGINT AS $$
DECLARE
    row_count BIGINT;
BEGIN
    -- 动态执行SQL：用$1占位符，USING传递参数
    EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(table_name)
    INTO row_count;
    
    RETURN row_count;
END;
$$ LANGUAGE plpgsql;

-- 调用：查询users表的行数
SELECT get_table_row_count('users');  -- 结果：100（假设）

关键点：

• quote_ident(table_name)：将表名转义（避免表名包含特殊字符，比如user是关键字）；
• EXECUTE 'SQL' USING 参数：绑定参数（$1对应第一个参数，$2对应第二个，依此类推）；
• 动态SQL必须用EXECUTE，否则PL/pgSQL会提前解析SQL（导致表名未找到错误）。

控制结构：条件与循环

PL/pgSQL 支持过程式控制结构，让你能根据条件分支或重复执行代码。

1. 条件判断（IF...THEN...ELSE）

-- 示例：判断用户年龄阶段
CREATE OR REPLACE FUNCTION get_age_group(
    user_age INT
) RETURNS TEXT AS $$
BEGIN
    IF user_age < 18 THEN
        RETURN 'Minor';
    ELSIF user_age BETWEEN 18 AND 60 THEN
        RETURN 'Adult';
    ELSE
        RETURN 'Senior';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 调用：
SELECT get_age_group(25);  -- 结果：Adult

2. 循环（LOOP/WHILE/FOR）

(1) 简单循环（LOOP）：用EXIT退出循环：

-- 示例：计算1到n的和
CREATE OR REPLACE FUNCTION sum_from_1_to_n(
    n INT
) RETURNS INT AS $$
DECLARE
    total INT := 0;
    i INT := 1;
BEGIN
    LOOP
        total := total + i;
        i := i + 1;
        EXIT WHEN i > n;  -- 当i超过n时退出循环
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

(2) 遍历查询结果（FOR循环）：最常用的循环方式，直接遍历SQL查询的结果集：

-- 示例：批量更新用户年龄（增加1岁）
CREATE OR REPLACE FUNCTION increment_all_ages() RETURNS VOID AS $$
DECLARE
    user_rec users%ROWTYPE;  -- 行类型变量，存储每个用户的信息
BEGIN
    -- 遍历users表的所有记录
    FOR user_rec IN SELECT * FROM users LOOP
        UPDATE users
        SET age = user_rec.age + 1
        WHERE id = user_rec.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用（无返回值，用SELECT）：
SELECT increment_all_ages();

错误处理与事务管理

1. 错误捕获（EXCEPTION块）

用 EXCEPTION 块捕获并处理错误（比如除以零、主键冲突）：

-- 示例：处理除以零的错误
CREATE OR REPLACE FUNCTION safe_divide(
    numerator NUMERIC,
    denominator NUMERIC
) RETURNS NUMERIC AS $$
BEGIN
    RETURN numerator / denominator;
EXCEPTION
    WHEN division_by_zero THEN  -- 捕获除以零的错误
        RAISE NOTICE 'Cannot divide by zero! Returning 0.';  -- 发送通知
        RETURN 0;  -- 返回默认值
    WHEN OTHERS THEN  -- 捕获其他所有错误
        RAISE EXCEPTION 'Unexpected error: %', SQLERRM;  -- 重新抛出错误
END;
$$ LANGUAGE plpgsql;

-- 调用：
SELECT safe_divide(10, 0);  -- 结果：0，控制台输出NOTICE

关键点：

• RAISE NOTICE：输出调试信息（不会中断执行）；
• RAISE EXCEPTION：抛出错误（中断执行）；
• SQLERRM：返回错误信息（比如“division by zero”）。

2. 事务控制（过程PROCEDURE）

PostgreSQL 11 引入了过程（PROCEDURE），它与函数的核心区别是：

• 过程可以显式控制事务（执行COMMIT/ROLLBACK）；
• 过程用CALL命令调用（不是SELECT）；
• 过程可以没有返回值。

示例：批量插入用户并处理事务

-- 创建过程：批量插入用户，失败则回滚
CREATE OR REPLACE PROCEDURE batch_insert_users(
    user_list JSONB[]  -- 输入参数：JSON数组，每个元素是用户信息
) LANGUAGE plpgsql AS $$
BEGIN
    -- 开始事务（可选，PostgreSQL自动开启）
    FOR i IN 1..array_length(user_list, 1) LOOP
        INSERT INTO users (name, age)
        VALUES (
            user_list[i]->>'name',  -- 取JSON中的name字段
            (user_list[i]->>'age')::INT  -- 转换为整数
        );
    END LOOP;
    COMMIT;  -- 提交事务
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- 回滚事务
        RAISE EXCEPTION 'Batch insert failed: %', SQLERRM;  -- 抛出错误
END;
$$;

-- 调用过程：
CALL batch_insert_users(
    ARRAY[
        '{"name":"Alice","age":30}'::JSONB,
        '{"name":"Bob","age":25}'::JSONB
    ]
);

关键点：

• 过程用CREATE PROCEDURE创建；
• 输入参数JSONB[]：JSON数组（适合批量数据）；
• array_length(user_list, 1)：获取JSON数组的长度；
• EXCEPTION块中ROLLBACK：如果插入失败，回滚所有操作。

课后 Quiz

问题1：如何在PL/pgSQL中避免动态SQL的注入问题？

答案：使用EXECUTE语句结合USING子句绑定参数，而非直接拼接字符串。例如：

EXECUTE 'SELECT * FROM users WHERE name = $1' USING user_name;

$1是占位符，USING传递的参数会被PostgreSQL自动转义，彻底避免SQL注入。

问题2：PostgreSQL中的函数（FUNCTION）和过程（PROCEDURE）有什么核心区别？

答案：

1. 事务控制：过程可以显式执行COMMIT/ROLLBACK，函数不能（默认运行在事务块中）；
2. 调用方式：过程用CALL调用，函数用SELECT调用；
3. 返回值：函数必须有返回值（除非RETURNS VOID），过程可以没有；
4. 用途：过程适合批量操作或需要事务的任务（比如数据迁移），函数适合计算或返回结果（比如统计）。

常见报错与解决方案

1. 报错：ERROR: syntax error at or near "END"

原因：BEGIN...END块中的语句缺少分号，或END后面没有分号。
解决：检查每个语句末尾的分号，确保END;后面有分号。例如：

-- 错误写法：
BEGIN
    RETURN 1  -- 缺少分号
END  -- 缺少分号

-- 正确写法：
BEGIN
    RETURN 1;
END;

2. 报错：ERROR: column "v_name" does not exist

原因：变量名与表的列名冲突（PL/pgSQL优先解析为列名）。
解决：

• 给变量加前缀（比如v_name代替name）；
• 使用%ROWTYPE或%TYPE声明变量时，确保表结构正确；
• 在查询中用别名区分：SELECT name INTO v_name FROM users WHERE id = 1;。

3. 报错：ERROR: query has no destination for result data

原因：函数中执行了返回结果集的SELECT，但没有将结果存入变量或返回。
解决：

• 将结果存入变量：SELECT COUNT(*) INTO v_count FROM users;；
• 如果要返回结果集，定义函数为RETURNS TABLE(...)或RETURNS SETOF ...，并用RETURN QUERY返回结果：

  CREATE OR REPLACE FUNCTION get_all_users()
  RETURNS TABLE(name TEXT, age INT) AS $$
  BEGIN
      RETURN QUERY SELECT name, age FROM users;  -- 返回结果集
  END;
  $$ LANGUAGE plpgsql;
  

参考链接

1. PostgreSQL PL/pgSQL官方文档：www.postgresql.org/docs/17/plp…
2. PL/pgSQL函数与过程：www.postgresql.org/docs/17/sql…
3. PL/pgSQL错误处理：www.postgresql.org/docs/17/plp…

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长，阅读完整的文章：PostgreSQL里的PL/pgSQL到底是啥？能让SQL从“说目标”变“讲步骤”？