一、什么是 PL/pgSQL？
1.1 基本定义
PL/pgSQL 是 PostgreSQL 数据库专属的可加载过程化语言（Procedural Language/PostgreSQL），简单来说：
- 它不是独立编程语言，而是构建在 SQL 之上的“增强版”——既保留 SQL 对数据库的操作能力，又补充了循环、条件判断等过程化控制结构；
- 可用于编写函数、存储过程、触发器，实现纯 SQL 难以完成的复杂逻辑；
- 完全继承 PostgreSQL 的所有特性：支持用户自定义类型、函数、运算符，还能被数据库服务器标记为“可信”。
1.2 为什么要用 PL/pgSQL？
SQL 是数据库的通用查询语言，优点是简单、可移植，但存在明显短板：每条 SQL 都需客户端与服务器单独通信。
比如客户端要完成“计算→查询→再计算”的逻辑，需经历：客户端发送查询1 → 服务器处理 → 返回结果 → 客户端本地计算 → 发送查询2 → 服务器处理 → 返回结果
这个过程会产生大量进程间/网络通信开销，而 PL/pgSQL 能把整套逻辑“打包”在服务器端执行，核心优势如下：
- 消除客户端与服务器的多余往返请求；
- 避免传输无意义的中间结果；
- 减少重复的 SQL 解析操作；
- 完全兼容 SQL 的所有数据类型、运算符和函数。
1.3 环境说明
PostgreSQL 9.0 及以上版本默认安装 PL/pgSQL，它本质是“可加载模块”，若需极致安全，管理员可手动卸载，但日常开发中无需额外配置即可使用。
二、PL/pgSQL 的核心结构
2.1 函数的创建方式
PL/pgSQL 代码主要以“函数”形式存在，通过 CREATE FUNCTION 命令向数据库注册，基础语法如下：
CREATE FUNCTION 函数名(参数1 类型, 参数2 类型, ...)
RETURNS 返回值类型
AS '函数体文本'
LANGUAGE plpgsql;
关键细节：函数体的书写规范
对 CREATE FUNCTION 来说，函数体只是普通字符串，推荐使用*美元符引用（$$）** 包裹，而非单引号（'）—— 单引号包裹时，函数体内的单引号/反斜杠需双写转义，极易出错。 | 写法 | 示例 | 优缺点 | |--------------|-------------------------------|----------------------------| | 单引号 | AS 'SELECT ''hello'';' | 需转义，易出错 | | 美元符引用 | AS $$SELECT 'hello';$$ | 无需转义，简洁易懂（推荐） | **最简示例**：创建一个计算销售税的函数 ```sql CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
-- 核心逻辑：计算 6% 的销售税
RETURN subtotal 0.06;
END;
$$ LANGUAGE plpgsql; -- 调用函数 SELECT sales_tax(100.00); -- 输出：6.0 ``` ### 2.2 块结构化语法（PL/pgSQL 的核心） PL/pgSQL 是“块结构化语言”，函数体必须是一个完整的代码块，块的完整语法如下： ```plpgsql [ <<标签名>> ] -- 可选，用于嵌套块标识 [ DECLARE -- 变量声明段：所有变量必须在此声明（除循环变量外） 变量名 类型 [ CONSTANT ] [ NOT NULL ] [ DEFAULT | := 初始值 ]; ] BEGIN -- 执行段：核心逻辑（SQL 语句、过程化控制语句） 执行语句; -- 每条语句以分号结尾 END [ 标签名 ]; -- 嵌套块的 END 后必须加分号，函数体最终 END 可省略 ``` #### 语法拆解（新手必看） 1. **标签（label）**：可选，主要用于嵌套块中标识代码块，或配合 `EXIT`/`CONTINUE` 控制流程，示例： ```plpgsql <> BEGIN <> BEGIN EXIT outer_block; -- 直接退出外层块 END inner_block; -- 嵌套块 END 后必须加分号 END outer_block; -- 函数体最终 END 可省略分号 ``` 2. **声明段（DECLARE）**： - 所有变量必须在此声明（唯一例外：`FOR` 循环变量自动声明）； - 支持 `CONSTANT`（常量，初始化后不可修改）、`NOT NULL`（非空约束）； - 初始值可用 `DEFAULT`、`:=`、`=` 三种方式赋值（推荐 `:=`）。 声明示例： ```plpgsql DECLARE user_id integer; -- 普通整数变量（初始值 NULL） quantity integer DEFAULT 32; -- 带默认值的整数 url varchar := 'http://mysite.com'; -- 字符串变量 transaction_time CONSTANT timestamp := now(); -- 常量（当前时间） age integer NOT NULL DEFAULT 18; -- 非空变量（必须指定默认值） ``` 3. **执行段（BEGIN...END）**： - 存放函数的核心逻辑，支持 SQL 语句、条件判断、循环等； - 每条语句必须以分号结尾； - 可通过 `RETURN` 返回结果（函数专用）。 ## 三、变量与类型：PL/pgSQL 的数据载体 ### 3.1 基础变量声明 变量是存储临时数据的容器，PL/pgSQL 支持所有 PostgreSQL 内置数据类型（`integer`、`varchar`、`numeric`、`timestamp` 等），声明语法： ```plpgsql 变量名 数据类型 [ 约束/默认值 ]; ``` #### 关键规则 - 变量默认值：未指定时为 `NULL`，且**每次进入代码块时重新计算**（而非函数调用时只算一次）； 示例：`current_time timestamp := now();` —— 每次调用函数，该变量都会获取最新时间，而非函数创建时的时间。 - 变量可引用同块中已声明的变量： ```plpgsql DECLARE x integer := 1; y integer := x + 1; -- y 初始值为 2 ``` ### 3.2 灵活的类型复用：%TYPE 与 %ROWTYPE 手动写数据类型易出错，且后续表结构变更时需同步修改函数，PL/pgSQL 提供两种“类型复用”语法，适配性更强。 #### 3.2.1 %TYPE：复制列/变量的类型 语法：`变量名 表名.列名%TYPE` 或 `变量名 已声明变量%TYPE` 作用：自动继承目标列/变量的类型，无需手动指定，示例： ```plpgsql -- 继承 users 表 user_id 列的类型 user_id users.user_id%TYPE; -- 继承已声明变量的类型 temp_id user_id%TYPE; -- 声明数组类型（复用列类型） user_ids users.user_id%TYPE[]; -- 存储多个 user_id 的数组 ``` **优势**：若后续 `users.user_id` 类型从 `integer` 改为 `bigint`，函数无需修改即可适配。 #### 3.2.2 %ROWTYPE：复制表的行类型 语法：`变量名 表名%ROWTYPE;` 作用：声明一个“行变量”，可存储整张表的一行数据，通过 `变量名.字段名` 访问字段，示例： ```sql -- 定义函数：拼接两张表的字段 CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE; -- 声明 table2 行类型变量
BEGIN
-- 将 table2 的一行数据存入 t2_row
SELECT INTO t2_row FROM table2 WHERE id = t_row.id;
-- 访问行变量的字段
RETURN t_row.name || t2_row.address || t_row.phone;
END;
$$ LANGUAGE plpgsql; -- 调用函数（传入 table1 的整行数据） SELECT merge_fields(t.*) FROM table1 t WHERE id = 1; ``` ### 3.3 动态结构：RECORD 记录类型 语法：`变量名 RECORD;` 作用：无固定结构的“动态行变量”，赋值时自动适配查询结果的结构，示例： ```plpgsql DECLARE arow RECORD; -- 声明记录变量 BEGIN -- 赋值：自动适配 users 表的行结构 SELECT id, name INTO arow FROM users WHERE id = 1; -- 访问字段（赋值后才能访问，否则报错） RAISE NOTICE '用户名：%', arow.name; END; ``` #### 注意事项 - `RECORD` 不是真正的数据类型，只是占位符； - 首次赋值前无任何结构，访问字段会触发运行时错误； - 每次赋值可适配不同的行结构（灵活但需注意类型匹配）。 ### 3.4 别名（ALIAS）：给变量起“小名” 语法：`新名称 ALIAS FOR 原名称;` 作用：为变量/参数重命名，主要用于： 1. 简化函数参数的 `$n` 写法（见 3.5 节）； 2. 覆盖触发器中的预定义变量（`NEW`/`OLD`），示例： ```plpgsql DECLARE prior ALIAS FOR old; -- 为 old 起别名 prior updated ALIAS FOR new; -- 为 new 起别名 updated ``` **注意**：别名与原变量指向同一数据，过度使用易混淆，仅推荐用于上述两种场景。 ## 四、函数参数与返回值：数据的输入输出 ### 4.1 函数参数：接收外部输入 参数是函数的“输入口”，调用函数时传入的数据会通过参数传递到函数内部，PL/pgSQL 中参数有两种引用方式： #### 方式1：命名参数（推荐） 创建函数时直接为参数命名，直观易懂，示例： ```sql CREATE FUNCTION sum_num(a int, b int) RETURNS int AS $$
BEGIN
RETURN a + b; -- 直接使用参数名
END;
$$ LANGUAGE plpgsql; -- 调用 SELECT sum_num(2, 3); -- 输出：5 ``` #### 方式2：$n 标识符（默认） 参数默认以 `$1`、`$2`、`$3`... 命名（按声明顺序），可通过 `ALIAS` 起别名，示例： ```sql -- 无参数名，使用 $1/$2 引用 CREATE FUNCTION sum_num(int, int) RETURNS int AS $$
DECLARE
num1 ALIAS FOR $1; -- 为 $1 起别名 num1
num2 ALIAS FOR $2; -- 为 $2 起别名 num2
BEGIN
RETURN num1 + num2;
END;
$$ LANGUAGE plpgsql; ``` ### 4.2 返回值：多种返回方式 PL/pgSQL 函数支持灵活的返回方式，满足不同场景需求。 #### 4.2.1 基础返回：RETURNS 单个值 最常用的方式，声明返回单个数据类型，通过 `RETURN 数值` 返回结果，示例： ```sql -- 返回单个字符串 CREATE FUNCTION get_user_name(user_id int) RETURNS varchar AS $$
BEGIN
RETURN (SELECT name FROM users WHERE id = user_id);
END;
$$ LANGUAGE plpgsql; ``` #### 4.2.2 多值返回：OUT 输出参数 需返回多个值时，用 `OUT` 声明输出参数（替代 `RETURNS`），示例： ```sql -- 计算两数的和与积 CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
sum := x + y; -- 为输出参数赋值
prod := x y;
END;
$$ LANGUAGE plpgsql; -- 调用（无需指定输出参数） SELECT * FROM sum_n_product(2, 4); -- 输出： -- sum | prod -- -----+------ -- 6 | 8 ``` **规则**： - 输出参数初始值为 `NULL`，需在函数内赋值； - 可省略 `RETURNS`（保留则需写 `RETURNS record`）； - 存储过程（PROCEDURE）也支持 `OUT` 参数，但调用时需指定所有参数（输出参数可传 `NULL`）。 #### 4.2.3 结果集返回：RETURNS TABLE/SETOF 需返回多行数据时，用 `RETURNS TABLE`（推荐）或 `RETURNS SETOF`，配合 `RETURN QUERY` 返回查询结果，示例： ```sql -- 返回指定商品的销售数据（多行） CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
-- 返回查询结果集
RETURN QUERY
SELECT s.quantity, s.quantity s.price
FROM sales AS s WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql; -- 调用（像查询表一样使用） SELECT * FROM extended_sales(1001); ``` #### 4.2.4 无返回值：RETURNS void 若函数仅执行操作（如插入/更新数据），无需返回结果，声明为 `RETURNS void`，示例： ```sql -- 插入用户数据，无返回值 CREATE FUNCTION insert_user(name varchar, age int) RETURNS void AS $$
BEGIN
INSERT INTO users (name, age) VALUES (name, age);
END;
$$ LANGUAGE plpgsql; -- 调用 SELECT insert_user('张三', 25); ``` ### 4.3 灵活适配：多态类型 PL/pgSQL 支持“多态类型”，让函数适配任意数据类型（如同时支持整数、小数加法），核心类型： - `anyelement`：任意基础类型（需手动保证输入类型一致）； - `anycompatible`：任意兼容类型（自动提升为通用类型，推荐）。 示例：支持任意类型的三数求和函数 ```sql CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible) RETURNS anycompatible AS $$
BEGIN
RETURN v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql; -- 调用：自动适配整数+小数 SELECT add_three_values(1, 2, 4.7); -- 输出：7.7 ``` ## 五、排序规则：字符比较的“规则” PL/pgSQL 变量的字符比较（如 `a < b`）受“排序规则（COLLATE）”影响，核心规则： 1. 函数参数的排序规则会自动继承给本地变量； 2. 可通过 `COLLATE` 显式指定排序规则； 3. 常用排序规则：`C`（按字符编码排序）、`en_US`（美式英语）、`zh_CN`（中文）。 示例：强制使用 C 排序规则比较字符串 ```sql CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
-- 强制使用 C 排序规则
RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql; ``` ## 六、完整示例：综合运用所有知识点 ```sql -- 创建函数：计算用户订单总金额（含折扣） CREATE FUNCTION calc_order_total(user_id int, discount numeric DEFAULT 0.1) RETURNS numeric AS $$
DECLARE
-- 声明变量：复用表列类型
order_row orders%ROWTYPE;
total_amount numeric := 0; -- 总金额（初始值 0）
discount_rate CONSTANT numeric := discount; -- 折扣率（常量）
BEGIN
-- 遍历用户所有订单
FOR order_row IN SELECT FROM orders WHERE user_id = $1 LOOP
-- 累加订单金额（原价 - 折扣）
total_amount := total_amount + (order_row.amount * (1 - discount_rate));
END LOOP;
-- 返回总金额（保留2位小数）
RETURN round(total_amount, 2);
END;
$$ LANGUAGE plpgsql; -- 调用函数：计算ID为1的用户订单总金额（折扣10%） SELECT calc_order_total(1); ``` ## 核心知识点总结 1. PL/pgSQL 是 PostgreSQL 的过程化语言，核心优势是减少客户端-服务器通信开销，兼容所有 SQL 特性； 2. 函数是 PL/pgSQL 的核心载体，采用块结构化语法（DECLARE 声明变量，BEGIN...END 写逻辑）； 3. 变量声明支持 `%TYPE`（复用列类型）、`%ROWTYPE`（复用行类型），适配表结构变更； 4. 返回值灵活：单值用 `RETURNS`，多值用 `OUT`，结果集用 `RETURNS TABLE`，无返回值用 `void`； 5. 美元符引用（$$）是书写函数体的最佳方式，避免单引号转义错误。
下一篇教程将讲解 PL/pgSQL 的流程控制（条件判断、循环、异常处理），帮助你实现更复杂的业务逻辑。
