CREATE RULE

Function

CREATE RULE defines a new rewrite rule.

Precautions

• To define or modify rules for a table, you must be the owner of the table.
• If multiple rules of the same type are defined for the same table, the rules are triggered one by one by name in alphabetical order.
• In the view, the RETURNING clause can be added to the INSERT, UPDATE, and DELETE rules to return columns by view. If a rule is triggered by the INSERT RETURNING, UPDATE RETURNING, or DELETE RETURNING command, these clauses are used to calculate the output result. If a rule is triggered by a command without RETURNING, the RETURNING clause of the rule is ignored. Currently, only unconditional INSTEAD rules can contain the RETURNING clause, and only one RETURNING clause can exist in all rules of one event. This ensures that only one RETURNING clause can be used for result calculation. If the RETURNING clause does not exist in any valid rule, the RETURNING query in this view will be rejected.
• Currently, ON SELECT rules must be unconditional INSTEAD rules and must have actions consisting of a single SELECT command. Therefore, an ON SELECT rule actually turns a table into a view whose visible content is the content returned by the SELECT command of the rule, rather than the content in the table (if any).
• You are not advised to use column-store tables in rules, especially for write operations. The architecture implementation and transaction processing of column-store tables are greatly different from those of row-store tables. Therefore, the rule performance of column-store tables is different from that of row-store tables.

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, which complies with the identifier naming convention and contains a maximum of 63 characters.

• event

  One of the SELECT, INSERT, UPDATE, and DELETE events.

• table_name

  Name (optionally schema-qualified) of the table or view to which the rule applies.

• condition

  SQL condition expression that returns a Boolean value, which determines whether to execute the rule. Expressions cannot reference any table except NEW and OLD, and cannot have aggregate functions. You are not advised to use numeric types such as int for condition, because such types can be implicitly converted to bool values (non-zero values are implicitly converted to true and 0 is implicitly converted to false), which may cause unexpected results.

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