PostgreSQLのルールの構文の習得 2024年4月19日 Robert Gravelle

PostgreSQLのルールは、クエリ実行を制御し、データベース内でデータ操作を実行するための強力なメカニズムを提供します。ルールの構文と使用法を理解することは、その機能を効果的に活用するために不可欠です。先週の記事では、PostgreSQLのルールの仕組みとトリガーとの違いについて説明しました。今日のフォローアップでは、無料の "dvdrental"サンプルデータベースを使用したより実用的な例とともに、その構文を詳しく説明します。

PostgreSQLのルールの構造

PostgreSQLのルールは、その動作を定義するいくつかの主要なコンポーネントで構成されています：

CREATE RULEステートメント: ルールを作成するためには、CREATE RULE ステートメントに続いてルール名とルール定義を使用します。
ルールイベント: ルールは、SELECT、INSERT、UPDATE、DELETE、または組み合わせ (ALL)など、様々なイベントによってトリガーできます。
ルールアクション: アクションは、ルールがトリガーされた時に何が起こるかを指定します。SELECT、INSERT、UPDATE、DELETEなどのSQL ステートメント、またはカスタムアクションにすることができます。
ルール条件: 条件はオプションであり、特定の基準が満たされた場合にのみルールをトリガーできます。条件は 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 のテーブルデザイナーを使用して、上記のテーブルを作成することもできます。それは次のとおりです：

次に、新しい顧客が追加される度に監査テーブルにレコードを挿入するルールを作成します：


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ルールは次のとおりです：

例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ルールは次のとおりです：

先週の記事の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ルールを次に示します：

まとめ

PostgreSQLのルールは、複雑なロジックを実装し、データベース内でデータの整合性を強化するための多用途のツールセットを提供します。挿入の監査、更新の制限、データ変換などの様々な例を調べることで、開発者はルールを適用して様々な要件に効果的に対処できる方法を深く理解できます。PostgreSQLの柔軟なルールシステムを使用すると、開発者はデータの一貫性と信頼性を確保しながら、特定のビジネスニーズに合わせてデータベースの動作をカスタマイズできます。

Navicatブログ

フィードエントリ