Practical PostgreSQL

Create Rule

Defines a new rule on a table.

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 ...