CREATE RULE

Define new rewrite rules.

Summary

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

describe

CREATE RULE defines new rules applied to a specified table or view. CREATE OR REPLACE RULE creates a new rule, or replaces an existing rule with the same name for the same table.

The YMatrix database rules system allows you to define another operation that is performed on inserts, updates, or deletes in database tables. When a given command on a given table is executed, the rule will result in the execution of additional or alternative commands. The INSTEAD rule can replace the given command with another command, or cause the command to be executed at all. Rules can also be used to implement SQL views. It is important to realize that rules are actually command conversion mechanisms or command macros. The conversion occurs before the command execution begins. It won't run independently for each physical row like a trigger.

The ON SELECT rule must be an unconditional INSTEAD rule and must have operations composed of a single SELECT command. Therefore, the ON SELECT rule effectively turns the table into a view, and its visible content is the row returned by the rule's SELECT command, rather than the content stored in the table (if any). Writing the CREATE VIEW command is considered a better style than creating a real table and defining an ON SELECT rule for it.

You can create an illusion that updates views by defining ON INSERT, ON UPDATE, and ON DELETE rules to replace updates on views with appropriate updates on other tables. If you want to support INSERT RETURNING, etc., make sure to put the appropriate RETURNING clause in each rule.

If you try to use conditional rules for view updates, there is a trap: To allow every operation performed on the view, you must have an unconditional INSTEAD rule. If the rule is conditional, or is not INSTEAD, the system will still refuse attempts to perform update operations because it believes that in some cases it may end up trying to perform an operation on the virtual table of the view. If you want to handle all useful cases in the conditional rule, add an unconditional DO INSTEAD NOTHING rule to make sure the system understands that it will never be called to update the virtual table. Then set the conditional rules to non-INSTEAD; in case they are applied, they are added to the default INSTEAD NOTHING operation. (However, this method is not currently applicable to supporting RETURNING queries.)

Parameters

name

The name of the rule to be created. This name must be different from the name of any other rule of the same table. Multiple rules on the same table and the same event type are applied in alphabetical names.

event

This event is one of SELECT, INSERT, UPDATE or DELETE.

table_name

The name of the table or view to which the rule applies (can be specified by the schema).

condition

Any SQL conditional expression (returns boolean value). Conditional expressions may not refer to any table except NEW and OLD and may not contain aggregate functions. NEW and OLD reference values in the reference table. NEW is valid in ON INSERT and ON UPDATE rules to refer to new rows to be inserted or updated. OLD is valid in ON UPDATE and ON DELETE rules to reference existing rows to be updated or deleted.

INSTEAD

INSTEAD NOTHING indicates the command to be executed instead of the original command.

ALSO

ALSO indicates that commands should be executed in addition to the original command. If ALSO or INSTEAD is not specified, the default is ALSO.

Command

One or more commands that make up rule operations. Valid commands are SELECT, INSERT, UPDATE or DELETE. Special table names NEW and OLD can be used to reference values in reference tables. NEW is valid in ON INSERT and ON UPDATE rules to refer to new rows to be inserted or updated. OLD is valid in ON UPDATE and ON DELETE rules to reference existing rows to be updated or deleted.

Note

You must be the owner of the table to create or change the rules for that table.

It is very important to pay attention to avoiding loop rules. The recursive rule is not verified at the time of the rule creation, but an error will be reported when executed.

Example

Create a rule that when a user tries to insert rows into the parent table rank of the partition, insert rows into child table b2001:

CREATE RULE b2001 AS ON INSERT TO rank WHERE gender='M' and
year='2001' DO INSTEAD INSERT INTO b2001 VALUES (NEW.id,
NEW.rank, NEW.year, NEW.gender, NEW.count);

compatibility

CREATE RULE is an extension of the YMatrix database language, and the same is true for the entire query rewrite system.