Index for Section RULE

Alphabetical listing for C

Bottom of page

CREATE
NAME
  CREATE RULE - define a new rewrite rule

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

DESCRIPTION
  CREATE RULE defines a new rule applying to a specified table or view.
  CREATE OR REPLACE RULE will either create a new rule, or replace an
  existing rule of the same name for the same table.

  The PostgreSQL rule system allows one to define an alternate action to be
  performed on insertions, updates, or deletions in database tables. Roughly
  speaking, a rule causes additional commands to be executed when a given
  command on a given table is executed. Alternatively, an INSTEAD rule can
  replace a given command by another, or cause a command not to be executed
  at all. Rules are used to implement table views as well. It is important to
  realize that a rule is really a command transformation mechanism, or
  command macro. The transformation happens before the execution of the
  commands starts.  If you actually want an operation that fires
  independently for each physical row, you probably want to use a trigger,
  not a rule.  More information about the rules system is in in the
  documentation.

  Presently, ON SELECT rules must be unconditional INSTEAD rules and must
  have actions that consist of a single SELECT command. Thus, an ON SELECT
  rule effectively turns the table into a view, whose visible contents are
  the rows returned by the rule's SELECT command rather than whatever had
  been stored in the table (if anything). It is considered better style to
  write a CREATE VIEW command than to create a real table and define an ON
  SELECT rule for it.

  You can create the illusion of an updatable view by defining ON INSERT, ON
  UPDATE, and ON DELETE rules (or any subset of those that's sufficient for
  your purposes) to replace update actions on the view with appropriate
  updates on other tables. If you want to support INSERT RETURNING and so on,
  then be sure to put a suitable RETURNING clause into each of these rules.

  There is a catch if you try to use conditional rules for view updates:
  there must be an unconditional INSTEAD rule for each action you wish to
  allow on the view. If the rule is conditional, or is not INSTEAD, then the
  system will still reject attempts to perform the update action, because it
  thinks it might end up trying to perform the action on the dummy table of
  the view in some cases. If you want to handle all the useful cases in
  conditional rules, add an unconditional DO INSTEAD NOTHING rule to ensure
  that the system understands it will never be called on to update the dummy
  table.  Then make the conditional rules non-INSTEAD; in the cases where
  they are applied, they add to the default INSTEAD NOTHING action. (This
  method does not currently work to support RETURNING queries, however.)

PARAMETERS
  name The name of a rule to create. This must be distinct from the name of
       any other rule for the same table. Multiple rules on the same table
       and same event type are applied in alphabetical name order.

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

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

  condition
       Any SQL conditional expression (returning boolean). The condition
       expression may not refer to any tables except NEW and OLD, and may not
       contain aggregate functions.

  INSTEAD
       INSTEAD indicates that the commands should be executed instead of the
       original command.

  ALSO ALSO indicates that the commands should be executed in addition to the
       original command.

       If neither ALSO nor INSTEAD is specified, ALSO is the default.

  command
       The command or commands that make up the rule action. Valid commands
       are SELECT, INSERT, UPDATE, DELETE, or NOTIFY.

  Within condition and command, the special table names NEW and OLD may be
  used to refer to values in the referenced table.  NEW is valid in ON INSERT
  and ON UPDATE rules to refer to the new row being inserted or updated. OLD
  is valid in ON UPDATE and ON DELETE rules to refer to the existing row
  being updated or deleted.

NOTES
  You must be the owner of a table to create or change rules for it.

  In a rule for INSERT, UPDATE, or DELETE on a view, you can add a RETURNING
  clause that emits the view's columns. This clause will be used to compute
  the outputs if the rule is triggered by an INSERT RETURNING, UPDATE
  RETURNING, or DELETE RETURNING command respectively. When the rule is
  triggered by a command without RETURNING, the rule's RETURNING clause will
  be ignored. The current implementation allows only unconditional INSTEAD
  rules to contain RETURNING; furthermore there can be at most one RETURNING
  clause among all the rules for the same event. (This ensures that there is
  only one candidate RETURNING clause to be used to compute the results.)
  RETURNING queries on the view will be rejected if there is no RETURNING
  clause in any available rule.

  It is very important to take care to avoid circular rules. For example,
  though each of the following two rule definitions are accepted by
  PostgreSQL, the SELECT command would cause PostgreSQL to report an error
  because of recursive expansion of a rule:

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

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

  SELECT * FROM t1;

  Presently, if a rule action contains a NOTIFY command, the NOTIFY command
  will be executed unconditionally, that is, the NOTIFY will be issued even
  if there are not any rows that the rule should apply to. For example, in

  CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;

  UPDATE mytable SET name = 'foo' WHERE id = 42;

  one NOTIFY event will be sent during the UPDATE, whether or not there are
  any rows that match the condition id = 42. This is an implementation
  restriction that may be fixed in future releases.

COMPATIBILITY
  CREATE RULE is a PostgreSQL language extension, as is the entire query
  rewrite system.

Index for Section RULE

Alphabetical listing for C

Top of page