CREATE RULE

Name

CREATE RULE  --  Defines a new rule on a table.

Synopsis

CREATE RULE name AS ON event
    TO object [ WHERE condition ]
    DO [ INSTEAD ]  action
   
    The possible options for action are:

    NOTHING
    |
    query
    |
    (query; query...)
    |
    [query;query...]
    

Parameters

name

This parameter takes the name of the new rule you are creating.

event

This parameter should be one of: SELECT, UPDATE, DELETE , or INSERT.

object

The object parameter accepts one of two things: table or table.column. As of the most current implementation, only tableis supported.

condition

This parameter accepts any boolean SQL statement which does not refer to a table; an exception is that the statement may refer to the new and old references to values in the table object.

query

Use this parameter to specify a query (or queries) that make up the action. The query (or queries) can be any valid SELECT, INSERT, UPDATE, DELETE , or NOTIFY statement. Supply multiple queries by surrounding them in parentheses; or, you may specify "NOTHING" instead of a query. This will perform no action (only useful if you also pass the INSTEAD clause).

Notenew and old
 

When specifying the condition and action , you are allowed to use new and old to access values from the referenced table, object. You can use new in an ON INSERT or ON UPDATE rule, while old can be used in an ON SELECT, ON UPDATE< or ON DELETE rule. The special table names new and old may be used within the condition and action to refer to values in the referenced table (the object). The word new in an ON INSERT or ON UPDATE rule refers to the new row being inserted or updated. The word old in an ON SELECT, ON UPDATE, or ON DELETE rule refers to the existing row being selected, updated, or deleted.

Results

CREATE

This message is displayed if the 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 a 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 event passes the supplied condition, 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 it will be performed before the query is processed. 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.

NoteON SELECT
 

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

Examples

The following example demonstrates the definition of a rule that will update a stock amount when another product is changed.

CREATE RULE alt_select AS
    ON SELECT TO inventory
    DO INSTEAD 
      SELECT * FROM new_inventory;