PL以及函数和触发器

PL/pgSQL

PL/pgSQL (Procedural Language/PostgreSQL) 是 PostgreSQL 自带的 过程式语言，用于在数据库中编写函数、存储过程、触发器等逻辑。它让 SQL 具备“编程能力”——支持 变量、条件判断、循环、异常捕获 等控制流。

SQL被PostgreSQL和大多数其他关系数据库用作查询语言。它是可移植的并且容易学习,但是每一个SQL语句必须由数据库服务器单独执行。这意味着你的客户端应用必须发送每一个查询到数据库服务器、等待它被处理、接收并处理结果、做一些计算，然后发送更多查询给服务器。如果你的客户端和数据库服务器不在同一台机器上，所有这些会引起进程间通信并且将带来网络负担。而通过PL/pgSQL，你可以将一整块计算和一系列查询分组在数据库服务器内部，这样就有了一种过程语言的能力并且使 SQL 更易用，同时减少了很多的客户端/服务器通信开销，能够带来可观的性能提升。

语法结构

一个典型的 PL/pgSQL 函数结构如下：

CREATE OR REPLACE FUNCTION 函数名(参数名 类型, ...)
RETURNS 返回类型 AS $$
DECLARE
    -- 声明变量
    total_count INTEGER := 0;
BEGIN
    -- 主体部分
    SELECT COUNT(*) INTO total_count FROM employees WHERE salary > 5000;

    -- 条件判断
    IF total_count > 10 THEN
        RAISE NOTICE 'High salary employees: %', total_count;
    ELSE
        RAISE NOTICE 'Few high salary employees';
    END IF;

    RETURN total_count;
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'Error occurred: %', SQLERRM;
        RETURN -1;
END;
$$ LANGUAGE plpgsql;

声明变量

DECLARE
    myvar INTEGER := 10;
    emp_name TEXT;
    emp_record RECORD;

变量赋值

emp_name := 'Tom';

条件语句

IF condition THEN
    -- 语句
ELSIF other_condition THEN
    -- 语句
ELSE
    -- 语句
END IF;

循环

FOR循环：

FOR i IN 1..10 LOOP
    RAISE NOTICE 'Number: %', i;
END LOOP;

查询循环:

 emp_rec IN SELECT id, name FROM employees LOOP
    RAISE NOTICE 'Employee: %, %', emp_rec.id, emp_rec.name;
END LOOP;

WHILE循环:

WHILE total < 100 LOOP
    total := total + 10;
END LOOP;

异常处理

BEGIN
    SELECT 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero';
END;

---

函数和存储过程

在 PostgreSQL 中，函数和过程封装了数据库中可重用的逻辑，以提升性能并维护数据结构。

注意函数和过程的代码都放在 AS $$ ....$$之中。

函数

函数是 PostgreSQL 中最常用的可编程对象，它是可复用的 SQL 逻辑块，函数返回一个值或一个表，接受输入参数，在 SQL 查询、触发器、存储过程、甚至视图中都能调用。其定义方式为CREATE FUNCTION。

## 创建函数
CREATE FUNCTION add_two(a INT, b INT) RETURNS INT 
AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

## 函数调用
SELECT add_two(3, 5);

过程

过程是 PostgreSQL 11 中引入的，它不返回值，但可以显式控制事务（提交/回滚），例如COMMIT和ROLLBACK，其定义方式为CREATE PROCEDURE，并使用CALL语句调用。主要区别在于函数必须返回值并集成到 SQL 查询中，而过程则专注于执行操作和管理事务。

过程用于执行动作，而非计算结果。
通常用于批处理、数据迁移、管理任务。

## 创建过程
CREATE PROCEDURE delete_user(uid INT)
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM users WHERE id = uid;
    COMMIT;     ##可以显式COMMIT
END;
$$;

## 过程调用
CALL delete_user(100);

二者对比

项目	函数（Function）	过程（Procedure）
是否有返回值	必须有（可为 void）	可无返回值
调用方式	SELECT func()	CALL proc()
事务控制	不能显式 commit/rollback	可显式 commit/rollback
使用场景	查询、计算、返回数据	批量修改、管理任务

触发器

触发器是一种事件驱动机制。当某张表发生 INSERT、UPDATE、DELETE、TRUNCATE 等操作时，会自动调用一个触发器函数。

触发器函数其实也是 PL/pgSQL 函数，只是返回类型是 TRIGGER。

定义语法

## 定义触发器函数
CREATE OR REPLACE FUNCTION trigger_func_name()
RETURNS TRIGGER AS $$
BEGIN
    -- 操作逻辑
    RETURN NEW;  -- 对INSERT/UPDATE 返回NEW，对DELETE返回OLD
END;
$$ LANGUAGE plpgsql;

##定义触发器
CREATE TRIGGER 触发器名
{BEFORE|AFTER|INSTEAD OF} {INSERT|UPDATE|DELETE}
ON 表名
FOR EACH ROW
EXECUTE FUNCTION trigger_func_name();

示例

自动更新时间戳：

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

CREATE TRIGGER set_update_time
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

每当 employees 表的行被更新时，updated_at 字段自动更新为当前时间。

---

递归CET

递归 CTE 是 CTE(通用表表达式)的一种特殊形式，用于查询层级结构数据（树/图）。
它的特点是：子查询自己调用自己（递归定义），PostgreSQL 会自动不断重复执行，直到没有新行产生。

语法结构

WITH RECURSIVE cte_name AS (
    --  锚点查询（初始层）
    SELECT ... FROM ... WHERE ...  -- 基础数据

    UNION ALL   ## 合并所有层级结果（ALL 保留重复）

    --  递归部分（自引用）
    SELECT ... FROM cte_name JOIN ... ON ...
)
SELECT * FROM cte_name;

示例

假设有一张表 employees：

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	David	2
5	Erin	2
6	Frank	4

现在要求查询 Alice 管理下的所有下属（不限层级）
查询语句：

WITH RECURSIVE emp_hierarchy AS (
    -- 锚点层：找到起始经理
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE name = 'Alice'

    UNION ALL

    -- 递归层：找下属
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.id     ##emp_hierarchy h 可以理解为当前递归已经找到的“上级员工”
)

SELECT * FROM emp_hierarchy;

从 Alice 出发，层层向下找到所有员工，并记录层级深度，递归查询会持续执行 JOIN，直到没有新的子节点。查询结果如下：

id	name	manager_id	level
1	Alice	NULL	1
2	Bob	1	2
3	Carol	1	2
4	David	2	3
5	Erin	2	3
6	Frank	4	4

---

窗口函数

窗口函数 是一种在 SQL 查询中“跨多行计算但不合并行”的函数。
它不会像 GROUP BY 那样聚合结果到单行，而是保留每行，并能在每行上计算“窗口”内的统计或排序信息。

基本语法

function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [ROWS BETWEEN frame_start AND frame_end]
)

• over:标识这是一个窗口函数
• PARTITION BY：定义分区，即窗口划分（类似 GROUP BY，但不折叠行）。
• ORDER BY：定义窗口内行的逻辑顺序（影响排名、累积等）。
• ROWS BETWEEN：定义窗口的行范围（例如前 2 行到当前行），常用的类型：ROWS, RANGE, GROUPS。

常见窗口函数与用途

• 排名类：ROW_NUMBER(), RANK(), DENSE_RANK()。
• 偏移类：LAG(expr, offset, default), LEAD(expr, offset, default)。
• 窗口聚合：SUM(), AVG(), COUNT() 等可作为窗口函数使用。
• FIRST_VALUE / LAST_VALUE：获取窗口内首/末值。

FRAME 子句详解（关键）

• 语法（示例）：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 或 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
• ROWS：基于物理行数，例如“前 2 行到当前行”。
• RANGE：基于值范围（对 ORDER BY 列的值相等或范围判断），在含有相同 ORDER BY 值时区别明显。
• 默认行为：如果指定了 ORDER BY 但未指定 frame_clause，PostgreSQL 默认的窗口帧通常是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

窗口函数 与 GROUP BY 的对比

核心差别

• 行为：GROUP BY 会把多行“压缩”成每个分组一行；OVER 会保留所有原始行，并为每行附加窗口计算结果。
• 用途：GROUP BY 用于生成分组级统计（报告/汇总）；OVER 用于在明细行上做分析（排名、累加、分区统计等）。
• 在 SELECT 中的限制：使用 GROUP BY 时，SELECT 中出现的非聚合列必须出现在 GROUP BY 子句中；而 OVER 可以在SELECT中与普通列并存。

执行顺序相关

• 查询执行顺序（与 GROUP BY, HAVING, OVER 相关）通常是：
  FROM -> WHERE -> GROUP BY -> HAVING -> SELECT (包括窗口函数计算) -> ORDER BY -> LIMIT。
• 换句话说，窗口函数（OVER）在分组/聚合之后计算，因此可以访问分组聚合的结果，但不能出现在 WHERE 或 GROUP BY 或 HAVING 子句中。

示例数据（employees）

name	department	salary
Alice	HR	5000
Bob	HR	6000
Carol	IT	7000
David	IT	8000
Eve	Sales	4000
Frank	Sales	4500

示例 A — 使用 GROUP BY（部门级平均工资）

SELECT department, AVG(salary) AS dept_avg, COUNT(*) AS cnt
FROM employees
GROUP BY department
ORDER BY department;

结果：

department	dept_avg	cnt
HR	5500	2
IT	7500	2
Sales	4250	2

说明：每个部门只有一行（分组级别）。

示例 B — 使用窗口函数（在每一行展示部门平均工资）

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       COUNT(*) OVER (PARTITION BY department) AS dept_count
FROM employees
ORDER BY department, salary;

结果：

name	department	salary	dept_avg	dept_count
Alice	HR	5000	5500	2
Bob	HR	6000	5500	2
Carol	IT	7000	7500	2
David	IT	8000	7500	2
Eve	Sales	4000	4250	2
Frank	Sales	4500	4250	2

说明：明细行保留，同时每行多出分组统计列。

什么时候用哪一个？

• 只需要分组汇总（行折叠） → 使用 GROUP BY（更语义清晰，通常更高效）。
• 需要按明细行显示并且同时显示分组统计 → 使用 OVER（保留明细并做分析）。

性能提示

• GROUP BY 常用 Hash Aggregate 或 Sort+Aggregate，可利用合适索引或并行聚合提升性能。
• 窗口函数通常需要对 PARTITION BY/ORDER BY 列进行排序；对于大表，排序开销与内存（work_mem）有关。
• 如果想在窗口内做累积（running sum），可以考虑先按 partition/order 排序再使用 ROWS 窗口框架来提高可预测性。

---