Navicat 干货 | 掌握 PostgreSQL 规则语法

PostgreSQL 规则提供了一种强大的机制，控制查询执行并在数据库内部实施数据操作。理解规则的语法和用法对于有效利用其功能至关重要。在上周的文章中，我们探讨了 PostgreSQL 规则的工作原理及其与触发器的区别。今天的文章将使用免费的 “dvdrental”示例数据库，通过更多实际例子详细介绍规则的语法。

PostgreSQL 规则的原子性

PostgreSQL 规则由多个关键组件组成，这些组件定义了它们的行为：

CREATE RULE 语句：要创建规则，我们使用 CREATE RULE 语句，后面跟上规则名称和规则定义。
规则事件：规则可以由各种事件触发，包括 SELECT、INSERT、UPDATE、DELETE 或组合（ALL）。
规则动作：当规则被触发时，动作指定应该发生什么。它可以是一个 SQL 语句，如 SELECT、INSERT、UPDATE、DELETE，或是一个自定义动作。
规则条件：条件是可选的，并允许规则仅在满足某些条件时触发。它们通过使用 WHERE 子句来指定。

使用“dvdrental”样本数据库的实例：

实例 1：审计插入操作

假设我们想要记录所有插入到“customer”表中的数据，以用于审计目的。首先，我们需要一个表来存储审计数据：

CREATE TABLE customer_audit (
    action_type VARCHAR(10),
    customer_id INT,
    audit_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

我们还可以使用 Navicat for PostgreSQL 16 的表设计器来创建上述表。下面是这个表的样子：

customer_audit_table_design (55K)

现在我们将创建一个规则，每当添加一个新客户时，该规则将在审计表中插入一条记录：


CREATE RULE log_customer_insert AS
    ON INSERT TO customer
    DO ALSO
        INSERT INTO customer_audit (action_type, customer_id)
        VALUES ('INSERT', NEW.customer_id);

在 Navicat 中，你可以在表设计器的“规则”选项卡下找到特定表的规则。这是 log_customer_insert 规则：

log_customer_insert_rule (46K)

实例 2：限制更新

假设我们想要防止在设置了租赁归还日期后对其进行更新。我们可以创建一个规则，该规则会阻止在最初设置归还日期列后任何尝试更新它的行为：

CREATE RULE prevent_return_date_update AS
    ON UPDATE TO rental
    WHERE OLD.return_date IS NOT NULL AND NEW.return_date IS DISTINCT FROM OLD.return_date
    DO INSTEAD NOTHING;

这是 Navicat 中的 prevent_return_date_update 规则：

prevent_return_date_update_rule (53K)

你可能还记得上周文章中提到的 enforce_min_rental_duration 规则。

实例 3：数据转换

假设我们想将“address”表中存储的电话号码格式从国际格式转换为本地格式。我们可以创建一条规则，该规则将在插入一个新地址时自动更新电话号码：

CREATE RULE transform_phone_number AS
    ON INSERT TO address
    DO ALSO
        UPDATE address
        SET phone = '+1-' || SUBSTRING(phone FROM 3)
        WHERE address_id = NEW.address_id;

需要在更多空间中输入完整的 Where 或 Definition 语句？点击文本框后面的省略号【…】按钮，将打开一个更大的文本区域，你可以在其中查看和编写完整的语句。下面是 Navicat 中的 transform_phone_number 规则，它显示了完整的定义语句

transform_phone_number_rule (63K)

结语

PostgreSQL 规则提供了一套多功能工具集，用于实现复杂的逻辑和确保数据库中的数据完整性。通过探索各种示例，如审计插入、限制更新和数据转换，开发人员可以更深入地了解如何有效地应用规则来满足各种需求。借助 PostgreSQL 灵活的规则系统，开发人员可以定制数据库的行为以满足特定的业务需求，同时确保数据的一致性和可靠性。