Navicat 部落格

掌握 PostgreSQL 規則文法 2024 年 4 月 19 日,由 Robert Gravelle 撰寫

PostgreSQL 規則(Rule)提供了一個強大的機制,用於在資料庫內控制查詢執行和強制資料操作。了解規則的語法和用法對於有效利地用它們的功能至關重要。在上週的文章中,我們探討了 PostgreSQL 規則的工作原理以及它們與觸發器的區別。今天的後續內容將詳細介紹規則的語法,並使用免費的 「dvdrental」範例資料庫 提供更多實際範例。

PostgreSQL 規則的剖析

PostgreSQL 規則是由幾個關鍵元件組成,這些元件定義了它們的行為:

  • CREATE RULE 陳述式:建立規則時,我們使用 CREATE RULE 陳述式,後面跟著規則名稱和規則定義。
  • 規則事件: :規則可以由各種事件觸發,包括 SELECTINSERTUPDATEDELETE 或它們的組合(全部)。
  • 規則動作: 指定觸發規則時應該作出的動作。它可以是 SQL 陳述式,例如 SELECTINSERTUPDATEDELETE 或自訂的動作。
  • 規則條件:條件的使用是可選的,它們允許規則僅在符合特定條件時才觸發。它們是使用 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;

如果需要更多空間輸入完整的「位置」或「定義」陳述式,你可以按一下文字方塊旁邊的省略號按鈕 [...],這將開啟一個更大的文字區域,你可以在其中檢視和撰寫完整的陳述式。以下是 Navicat 中的 transform_phone_number 規則,顯示了完整的定義:

transform_phone_number_rule (63K)

總結

PostgreSQL 規則提供了一套多功能工具集,用於在資料庫中實現複雜的邏輯並強制資料完整性。透過探索稽核插入作業、限制更新作業和資料轉換等不同的範例,開發人員可以更深入地了解如何有效應用規則來滿足各種需求。借助 PostgreSQL 的靈活規則系統,開發人員可以根據具體的業務需求定制資料庫行為,同時確保資料的一致性和可靠性。

Navicat 文章
頻道記錄
分享
部落格封存檔