PL/pgSQL的主要功能

好啦，让我们深入聊聊为什么 PL/pgSQL 对开发者和数据库管理员来说是个超强又不可替代的工具。在这节课里，我们会聊 PL/pgSQL 的优点、它独特的功能，还会举例说明这些功能在实际生活中怎么用才最爽。

为了明白为啥我们需要 PL/pgSQL，想象一下，如果你在一个只能用 SQL 写所有编程任务的世界。比如，你要统计每个院系的学生数量，你得写个超级复杂的 SQL 查询，然后还得在客户端处理结果。这效率也太低了吧？这时候 PL/pgSQL 就登场了，它支持变量、循环、条件判断和错误处理，简直神器！

用 PL/pgSQL 的好处：

1. 服务器端逻辑：PL/pgSQL 让所有逻辑都在服务器上跑，减少了服务器和客户端之间的数据传输，网络延迟也跟着降了。
2. 性能：PL/pgSQL 的函数会被编译并存储在数据库里，执行速度比一堆单独 SQL 查询快多了。
3. 任务自动化：用 PL/pgSQL 可以自动化各种日常操作，比如数据更新、日志记录或者信息完整性校验。
4. 业务逻辑：PL/pgSQL 能实现复杂的业务逻辑，比如各种计算、校验或者生成分析报告。
5. 方便又易读：PL/pgSQL 的代码很容易结构化、拆分成函数和优化，维护起来超方便。

PL/pgSQL 的应用场景

现在我们来看看 PL/pgSQL 到底能用在哪些地方，以及它怎么解决实际问题。

1. 自动化日常操作

PL/pgSQL 可以自动化重复性任务。比如，你需要每天更新某些数据，或者定期跑分析。写个 PL/pgSQL 函数，再配合任务调度器（比如 pg_cron），就能定时自动执行啦。

例子：自动更新状态

CREATE FUNCTION update_student_status() RETURNS VOID AS $$
BEGIN
    UPDATE students
    SET status = '不活跃'
    WHERE last_login < NOW() - INTERVAL '1 year';
    RAISE NOTICE '学生状态已更新。';
END;
$$ LANGUAGE plpgsql;

比如这个函数，会自动把一年没登录系统的学生状态设为 “不活跃”。

2. 生成报表

PL/pgSQL 超适合做分析报表，需要从多张表聚合、合并数据。你可以写存储过程自动生成报表，还能把结果存到专门的表里。

例子：按院系统计学生数量的报表

CREATE FUNCTION generate_faculty_report() RETURNS TABLE (faculty_id INT, student_count INT) AS $$
BEGIN
    RETURN QUERY
    SELECT faculty_id, COUNT(*)
    FROM students
    GROUP BY faculty_id;
END;
$$ LANGUAGE plpgsql;

调用这个函数后，你就能拿到所有院系的统计数据啦。

3. 表变更日志记录

日志记录就是把数据库表的数据变更写下来。PL/pgSQL 可以很高效地搞定，比如用触发器。

日志记录函数例子

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO change_logs(table_name, operation, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(OLD), ROW_TO_JSON(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这个函数会把 change_logs 表里记录下被改动的表名、操作类型（比如 INSERT、UPDATE、DELETE），还有旧数据和新数据。

4. 实现复杂算法

用 PL/pgSQL 你可以写出超出普通 SQL 能力范围的算法。比如成本计算、业务规则校验或者自动生成 ID。

例子：生成唯一标识符

CREATE FUNCTION generate_unique_id() RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT('UID-', EXTRACT(EPOCH FROM NOW()), '-', RANDOM()::TEXT);
END;
$$ LANGUAGE plpgsql;

这个函数会生成一个唯一 ID，里面有当前时间戳和随机数。

5. 和触发器一起用

触发器和 PL/pgSQL 是绝配。比如你要自动更新关联数据，用 PL/pgSQL 函数配合触发器就完美了。

例子：学生删除触发器

CREATE FUNCTION handle_delete_students() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM enrollments WHERE student_id = OLD.id;
    RAISE NOTICE '已删除学生 % 的选课记录。', OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

用这个函数，比如你从 students 表删掉学生时，会自动把 enrollments 表里对应的选课记录也删掉。

6. 错误处理

遇到复杂任务时，错误处理就特别重要了。PL/pgSQL 提供了 EXCEPTION 块，可以捕获和处理错误。

例子：错误处理

CREATE FUNCTION insert_student(name TEXT, faculty_id INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO students(name, faculty_id) VALUES (name, faculty_id);
EXCEPTION
    WHEN FOREIGN_KEY_VIOLATION THEN
        RAISE NOTICE '院系ID % 不存在！', faculty_id;
END;
$$ LANGUAGE plpgsql;

这里如果插入了一个数据库里没有的院系 ID，就会弹出警告，而不是直接报错崩溃。

PL/pgSQL 能搞定的复杂任务例子

给你点灵感，这里有几个 PL/pgSQL 能轻松搞定的任务：

1. 网店自动更新折扣 每天自动更新那些促销快到期商品的折扣。

2. 数据校验和修复 检查表里有没有重复记录，有的话自动删掉。

3. 快速切换配置 可以一键切换系统参数，比如切换应用运行模式。

IT 世界里的真实例子

全球有上百万家公司在用 PL/pgSQL。比如：

• 网店 用函数算税、自动更新折扣、生成销售报表。
• 银行 用 PL/pgSQL 处理每天成千上万的操作，从利息计算到信用评分校验。
• 社交网络 实现复杂的数据处理算法，比如推荐好友。

PL/pgSQL 就像是 PostgreSQL 程序员的瑞士军刀。不仅让数据库操作变简单，还能搞定那些用普通 SQL 很难甚至搞不定的任务。最重要的是——PL/pgSQL 学起来很容易，谁都能用它成为数据库高手！