CREATE RULE

Description

Defines a new rewriting 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 query. 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).

Syntax

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

Parameters

• name

  Name of the created rule. It must be unique among all the rules for the same table.

  Value range: a string, which complies with the naming convention. A value can contain 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 advised not to use numeric types such as int as conditions, 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 a command is used to replace the initial event.

• ALSO

  Specifies that a command should be executed after the initial event. If neither ALSO nor INSTEAD is specified, ALSO is the default value.

• command

  Specifies the command that composes the rule action. A valid command is one of the SELECT, INSERT, UPDATE, and DELETE statements.

Examples

-- Create the tbl_rule1 and tbl_rule2 tables for creating rules.
gaussdb=# CREATE TABLE tbl_rule1(c1 int,c2 int,c3 int, c4 int);
gaussdb=# CREATE TABLE tbl_rule2(c1 int,c2 int);

-- Create rule rule_test and use ALSO to specify that commands are executed after the initial event is executed.
gaussdb=# CREATE RULE rule_test AS 
    ON INSERT TO tbl_rule1 
    DO ALSO INSERT INTO tbl_rule2 VALUES (new.c1, new.c2);

-- Insert data into the tbl_rule1 table and view data in the two tables.
gaussdb=# INSERT INTO tbl_rule1 VALUES(1,11,111,1111), (2,22,222,2222);
gaussdb=# SELECT * FROM tbl_rule1;
 c1 | c2 | c3  |  c4  
----+----+-----+------
  1 | 11 | 111 | 1111
  2 | 22 | 222 | 2222
(2 rows)
gaussdb=# SELECT * FROM tbl_rule2;
 c1 | c2 
----+----
  1 | 11
  2 | 22
(2 rows)

-- Drop the rule.
gaussdb=# DROP RULE rule_test ON tbl_rule1;

-- Drop the table.
gaussdb=# DROP TABLE tbl_rule1;
gaussdb=# DROP TABLE tbl_rule2;