PL/pgSQL 与数据库内逻辑

PL/pgSQL 是 PG 默认过程语言，用于函数、触发器与批处理。DBA 需评估：哪些逻辑放库内、哪些留在 Spring 服务层。与 /roadmaps/postgresql-dba 存储过程节点对齐。

学习目标

编写简单函数与 RETURNS TABLE
理解触发器在审计、约束上的用途
掌握 DBA 视角的「库内逻辑」边界

核心概念

函数示例

CREATE OR REPLACE FUNCTION app.track_lesson_count(p_slug text)
RETURNS integer
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  cnt integer;
BEGIN
  SELECT count(*) INTO cnt
  FROM app.cf_lesson l
  JOIN app.cf_track t ON t.id = l.track_id
  WHERE t.slug = p_slug;
  RETURN cnt;
END;
$$;

SELECT app.track_lesson_count('postgresql-dba-essentials');

STABLE 表示同一事务内结果一致，利于优化器内联。

触发器（审计 updated_at）

CREATE OR REPLACE FUNCTION app.set_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_cf_track_updated
  BEFORE UPDATE ON app.cf_track
  FOR EACH ROW EXECUTE FUNCTION app.set_updated_at();

PG 15+ 用 EXECUTE FUNCTION；旧版为 EXECUTE PROCEDURE。

DBA 边界建议

适合库内	适合应用层
约束、审计字段	复杂业务规则
统计快照、批清理	外部 API 调用
迁移一次性脚本	领域模型与事务编排

过多 PL 逻辑增加 版本管理与测试 成本；与 Spring @Transactional 重复时优先应用层。

与 MySQL/Spring Boot 对照

项	PostgreSQL	MySQL 8
过程语言	PL/pgSQL（内置）	SQL + stored procedure
触发器	行/语句级，功能全	行级为主
调用方	`SELECT func()`	`CALL proc()`
Spring	少用 `@Select` 调函数	同，Flyway 部署 SQL

本站 MyBatis-Flex 以 SQL/Mapper 为主；PG 函数可用于 报表 SQL 简化，非核心业务路径。

练习

实现并对不存在 slug 返回 0

PL/pgSQL 与触发器