PL/pgSQL 与触发器 · 让数据库自己干活 · PostgreSQL 特性详解

PL/pgSQL 是 PostgreSQL 内置的过程语言（Oracle PL/SQL 风格），用它写函数、存储过程和触发器函数。触发器在 INSERT/UPDATE/DELETE 前后自动调用函数：自动维护 updated_at、写审计日志、维护派生字段、跨表约束——把这些逻辑下沉到数据库，应用怎么写都绕不过去。

PL/pgSQL 与触发器 · 让数据库自己干活

到目前为止，数据库在我们眼里还是个「被动」的角色：你发 SQL，它返数据。但 PostgreSQL 能主动干活——用 PL/pgSQL 把逻辑写进数据库，再用触发器让它在数据变动时自动执行。有些一致性规则，与其指望每个调用方的代码都记得做，不如焊死在数据库层，谁也绕不过。

PL/pgSQL：数据库里的过程语言

PL/pgSQL 是 PostgreSQL 默认自带的过程化语言，语法是 Oracle PL/SQL 的风格（变量、IF、LOOP、异常处理）。它能写三种东西：函数、存储过程、触发器函数。

先看自定义函数——把常用逻辑封装成可复用的单元：

CREATE FUNCTION discounted(price numeric, rate numeric DEFAULT 0.10)
RETURNS numeric AS $$
BEGIN
  RETURN round(price * (1 - rate), 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT discounted(100);        -- 90.00(默认打 9 折)
SELECT discounted(100, 0.25);  -- 75.00

注意那个 DEFAULT 0.10——PostgreSQL 的函数支持默认参数、具名参数、甚至同名不同参的重载，表达力接近一门小语言。

函数 vs 存储过程：能不能控制事务

PG 11 起区分了函数(FUNCTION) 和存储过程(PROCEDURE)，核心差别是事务控制：

函数：运行在调用者的事务里，不能自己 COMMIT / ROLLBACK。
存储过程：用 CALL 调用，内部可以 COMMIT / ROLLBACK，适合「分批处理、每批提交」这种长任务。

CREATE PROCEDURE restock() LANGUAGE plpgsql AS $$
BEGIN
  UPDATE products SET price = price * 1.1 WHERE name = 'Juice';
  COMMIT;   -- 存储过程里可以提交
  UPDATE products SET price = price * 1.1 WHERE name = 'Milk';
  COMMIT;
END; $$;

CALL restock();   -- Juice: 16.5, Milk: 8.8

触发器：数据一变，自动执行

触发器(Trigger)让函数在表的 INSERT / UPDATE / DELETE 前后自动触发。它能：

时机：BEFORE（改之前）、AFTER（改之后）、INSTEAD OF（用于视图）。
粒度：FOR EACH ROW（每行触发）、FOR EACH STATEMENT（每语句一次）。
条件：WHEN (...) 只在满足条件时触发。
访问新旧行：NEW（新值）、OLD（旧值）。

用途一：自动维护 updated_at——BEFORE UPDATE 里把时间戳塞进 NEW：

CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();   -- 改的是「即将写入」的新行
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_touch BEFORE UPDATE ON items
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
-- 之后任何 UPDATE,updated_at 自动刷新,应用层一行都不用写

用途二：自动写审计日志——AFTER 触发，把 OLD / NEW 记进日志表：

op=INSERT  before=∅   after=∅
op=UPDATE  before=20  after=22    ← 谁把 20 改成了 22,自动留痕
op=DELETE  before=∅   after=∅

BEFORE 触发器改 NEW 能影响即将写入的值（所以 updated_at 用 BEFORE）；AFTER 触发器改 NEW 没用（已经写完了），它适合做「基于最终结果的副作用」——写日志、发通知、更新汇总表。选错时机是新手常见坑：想改值就 BEFORE，想响应结果就 AFTER。

一把双刃剑：别把业务全塞进触发器

触发器很强，但强到容易滥用。它的隐患是**「隐式」**——逻辑藏在数据库里，应用代码看不见。一次简单的 UPDATE 背后可能悄悄触发一串连锁反应，排查问题时让人摸不着头脑。

经验法则：用触发器做「数据完整性 / 审计 / 派生字段」这类和业务解耦、且必须保证的横切逻辑（updated_at、审计日志、维护 tsvector）；别用它承载核心业务流程（下单减库存、算优惠这种）——那些放应用层，可见、可测、可版本管理。触发器是「兜底的安全网」，不是「藏起来的业务逻辑」。

横向对比

能力	PostgreSQL	MySQL	Oracle	SQL Server
过程语言	PL/pgSQL + PL/Python/Perl/JS 等多语言	存储过程（单一）	PL/SQL（标杆）	T-SQL
触发器函数	独立函数，可复用	触发器内联，不可复用	有	有
存储过程事务控制	PG 11+ 有	有	有	有
`INSTEAD OF` 触发器	有（视图可写）	❌ 无	有	有

PostgreSQL 有个独特优势：过程语言可插拔。除了 PL/pgSQL，还能装 PL/Python、PL/Perl、PL/v8(JavaScript)——用你顺手的语言写数据库函数。MySQL 的存储过程相对弱、触发器逻辑内联不能复用，是它和 PG 差距较明显的一块。

应用场景

自动时间戳：updated_at / created_at 用 BEFORE 触发器维护，免得每个写路径都手动带。
审计留痕：关键表的增删改用 AFTER 触发器写审计表，满足合规要求。
派生 / 汇总维护：子表变动时自动更新父表的计数 / 汇总（注意：简单派生列优先用生成列）。
复杂校验：跨表、跨行的完整性约束，CHECK 表达不了的用触发器兜底。
批处理任务：存储过程 + CALL，分批提交处理大数据量迁移 / 清洗。

小结与下一章

PL/pgSQL 让数据库从「被动应答」变成「能主动执行逻辑」，触发器则让这些逻辑在数据变动时自动生效。用对地方（完整性、审计、派生），它是可靠的安全网；用过头（核心业务），它会变成难以追踪的隐藏逻辑。分寸感很重要。

触发器能在库内响应数据变化，但如果想让库外的应用也实时知道「数据变了」呢？下一章的 LISTEN/NOTIFY 让 PostgreSQL 直接当一个轻量消息总线。👉 LISTEN/NOTIFY：数据库自带的发布订阅。

参考文献

[1] PostgreSQL 官方文档.「Chapter 43. PL/pgSQL.」https://www.postgresql.org/docs/current/plpgsql.html —— 过程语言语法、函数、异常处理。
[2] PostgreSQL 官方文档.「Chapter 39. Triggers.」https://www.postgresql.org/docs/current/triggers.html —— 触发器时机、粒度、NEW/OLD。
[3] PostgreSQL 官方文档.「CREATE PROCEDURE.」https://www.postgresql.org/docs/current/sql-createprocedure.html —— 存储过程与事务控制。

PL/pgSQL 与触发器 · 让数据库自己干活

本页目录