Apr 19, 2024 by Robert Gravelle

PostgreSQL rules offer a powerful mechanism for controlling query execution and enforcing data manipulation within the database. Understanding the syntax and usage of rules is essential for harnessing their capabilities effectively. In last week's article, we explored how PostgreSQL rules work and how they differ from triggers. Today's follow-up will cover their syntax in detail with more practical examples using the free "dvdrental" sample database.

Anatomy of PostgreSQL Rules

PostgreSQL rules consist of several key components that define their behavior:

• CREATE RULE Statement: To create a rule, we use the CREATE RULE statement followed by a rule name and the rule definition.
• Rule Event: Rules can be triggered by various events, including SELECT, INSERT, UPDATE, DELETE, or a combination (ALL).
• Rule Action: The action specifies what should happen when the rule is triggered. It can be an SQL statement such as SELECT, INSERT, UPDATE, DELETE, or a custom action.
• Rule Condition: Conditions are optional and allow rules to be triggered only when certain criteria are met. They are specified using a WHERE clause.

Practical Examples Using the "dvdrental" Sample Database

Example 1: Auditing Inserts

Suppose we want to log all insertions into the "customer" table for auditing purposes. First we'll need a table to store the audit data:

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

We can also create the above table using Navicat for PostgreSQL 16's Table Designer. Here's what that looks like:

Now we'll create a rule that inserts a record into an audit table whenever a new customer is added:

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);

In Navicat, you'll find the rules for a given table on the "Rules" tab of the Table Designer. Here is the log_customer_insert rule:

Example 2: Restricting Updates

Let's say we want to prevent updates to the rental return date once it has been set. We can create a rule that blocks any attempts to update the return date column after it has been initially set:

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;

Here is the prevent_return_date_update rule in Navicat:

You may recognize the enforce_min_rental_duration rule from last week's article.

Example 3: Data Transformation

Suppose we want to transform the format of phone numbers stored in the "address" table from international format to local format. We can create a rule that automatically updates phone numbers whenever a new address is inserted:

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;

Need more space to enter the complete Where or Definition statement? Clicking the ellipsis [...] button beside the text box opens a large text area where you can view and compose the full statement. Here is the transform_phone_number rule in Navicat that shows the full Definition:

Conclusion

PostgreSQL rules offer a versatile toolset for implementing complex logic and enforcing data integrity within the database. By exploring diverse examples like auditing inserts, restricting updates, and data transformation, developers can gain a deeper understanding of how rules can be applied to address various requirements effectively. With PostgreSQL's flexible rule system, developers can tailor database behavior to meet specific business needs while ensuring data consistency and reliability.