Create Rule

Synopsis

CREATE RULE name AS ON event TO object [ WHERE condition ] DO [ INSTEAD ] action
action ::= NOTHING | query | ( query [; ...] ) | [ query [; ...] ]

Parameters

name

The name of the new rule you are creating.

event

The event that triggers the rule. This parameter should be one of: SELECT, UPDATE, DELETE, or INSERT.

object

The name of a table, or the fully qualified name of a table column (e.g., table_name.column_name).

condition

A SQL condition evaluating to a value of type boolean, which specifies when this rule should be used. This statement should not refer to a table; the only exception to this is that the condition may refer to the special new and old relations, which represent the existing rows, and any new row data provided, respectively.

INSTEAD

The INSTEAD keyword; when used, the action is executed instead of the specified event. Otherwise, the action executes before the event does.

action

The query (or queries) that define the action to perform when the rule is triggered, and the condition is met. The query (or queries) can be any valid SELECT, INSERT, UPDATE, DELETE, or NOTIFY statements. Supply multiple queries by surrounding them in parentheses.

You may alternatively use the NOTHING keyword instead of a query. NOTHING will perform no action, and is only useful if you also specify the INSTEAD keyword.

Within the condition and action values, you are able to use the special new and old relations to access column values from both the referenced object, and from the data triggering the rule.

The new relation is available in an INSERT or UPDATE rule, containing the column values being inserted or updated, while the old relation is available in a SELECT, UPDATE, or DELETE rule, containing the row data being selected, updated, or deleted.

Results

CREATE: The message returned when a rule is successfully created.

Description

Use CREATE RULE to create a rule. Rules allow you to define alternate actions to be taken upon table and class inserts, updates, and deletions. You can also use the PostgreSQL rule system to implement table views.

When SELECT, INSERT, DELETE, or UPDATE is issued, the rules for that event are examined in an unspecified order. If a WHERE clause has been specified by the rule, it is checked; if the specified condition is met, the rule’s specified action is performed. If you specified INSTEAD when creating the rule, the action will be taken instead of the event; otherwise the action will be performed before the query is processed (the event itself). Be careful not to create what are known as circular rules; these are rules that reference other rules that in turn reference the original rule.

Note

When using ON SELECT rules, you must pass the INSTEAD parameter. Essentially, this means that ON SELECT must always implement table views.

Examples

The following example shows the definition of a rule named sync_stock_with_editions that updates the stock table’s isbn column automatically when the editions table is modified:

booktown=# CREATE RULE sync_stock_with_editions AS
booktown-#             ON UPDATE TO editions
booktown-#             DO UPDATE stock SET isbn = new.isbn
booktown-#                             WHERE isbn = old.isbn;
CREATE