CREATE RULE

Function

CREATE RULE defines a new rewrite rule.

Precautions

• To define or modify rules of a table, you must be the owner of the table.
• If multiple rules of the same type are defined for a table, trigger them in alphabetical order of the rule name.
• A RETURNING clause can be added to the INSERT, UPDATE, or DELETE rule of a view. If a rule is triggered by INSERT RETURNING, UPDATE RETURNING, or DELETE RETURNING, these clauses will be used for calculating the output. If a rule is triggered by a command without RETURNING, the RETURNING clause of the rule will be ignored. Currently, only the INSTEAD rule without conditions is allowed to contain a RETURNING clause. Additionally, only one RETURNING clause can exist among all rules of the same event. This can make sure that only one RETURNING clause is used for calculating the output. If no RETURNING clause exist in any valid rules, the RETURNING query on the view will be rejected.

Syntax

CreateRule ::= CREATE [ OR REPLACE ] RULE name AS ON event
    TO table_name [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command '...' ) }

Event is one of the following:

   SELECT
   INSERT
   DELETE
   UPDATE

Parameter Description

• name

  Specifies the name of the rule to be created. This must be distinct from the name of any other rule for the same table.

  Value range: a string that meets the identifier naming rule and contains at most 63 characters.

• table_name

  Specifies the name of the table or view the rule applies to (optionally schema-qualified).

• condition

  Specifies any SQL conditional expression (returning boolean), which determines whether to perform the rule actually. The condition expression may not refer to any tables except new and old, and may not contain aggregate functions.

• INSTEAD

  Specifies that the initial event is replaced using this command.

• ALSO

  Specifies that this command should be run after the initial event is performed. If both ALSO and INSTEAD are not defined, ALSO is the default value.

• command

  Specifies the command for rule action. The valid command is one of SELECT, INSERT, UPDATE, and DELETE.

Example

CREATE RULE "_RETURN" AS
    ON SELECT TO t1
    DO INSTEAD
        SELECT * FROM t2;