<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.43 2004/03/04 14:32:12 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.44 2004/03/09 19:30:21 neilc Exp $

PostgreSQL documentation

-->

<synopsis>

CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>

TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]

DO [ INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }

DO [ALSO |INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }

</synopsis>

</refsynopsisdiv>

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 <literal>INSTEAD</literal>

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.

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 <xref linkend="rules">.

</para>

<term><replaceable class="parameter">event</replaceable></term>

<listitem>

<para>

Theeven is one of <literal>SELECT</literal>,

Theevent is one of <literal>SELECT</literal>,

<literal>INSERT</literal>, <literal>UPDATE</literal>, or

<literal>DELETE</literal>.

</para>

<term><replaceable class="parameter">condition</replaceable></term>

<listitem>

<para>

Any SQL conditional expression (returning <type>boolean</type>).

The condition expression may not refer to any tables except

<literal>NEW</literal> and <literal>OLD</literal>, and may not

contain aggregate functions.

Any<acronym>SQL</acronym> conditional expression (returning

<type>boolean</type>).The condition expression may not refer

to any tables except<literal>NEW</> and <literal>OLD</>, and

may notcontain aggregate functions.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

<literal>INSTEAD</literal> indicates that the commands should be

executed <emphasis>instead</> of the original command, not in

addition to the original command.

executed <emphasis>instead of</> the original command.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><option>ALSO</option></term>

<listitem>

<para>

<literal>ALSO</literal> indicates that the commands should be

executed <emphasis>in addition to</emphasis> the original

command.

</para>

<para>

If neither <literal>ALSO</literal> nor

<literal>INSTEAD</literal> is specified, <literal>ALSO</literal>

is the default.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

The command or commands that make up the rule action. Valid

commands are <literal>SELECT</literal>,

<literal>INSERT</literal>, <literal>UPDATE</literal>,

<literal>DELETE</literal>, or <literal>NOTIFY</literal>.

commands are <command>SELECT</command>,

<command>INSERT</command>, <command>UPDATE</command>,

<command>DELETE</command>, or <command>NOTIFY</command>.

</para>

</listitem>

</varlistentry>

issued even if there are not any rows that the rule should apply

to. For example, in

<programlisting>

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

CREATE RULE notify_me AS ON UPDATE TO mytable DOALSONOTIFY mytable;

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

</programlisting>

one <command>NOTIFY</command> event will be sent during the

<command>UPDATE</command>, whether or not there are any rowswith

<literal>id = 42</literal>. This is an implementation restriction

that may be fixed in future releases.

<command>UPDATE</command>, whether or not there are any rowsthat

match the condition<literal>id = 42</literal>. This is an

implementation restrictionthat may be fixed in future releases.

</para>

</refsect1>

<para>

<command>CREATE RULE</command> is a

<productname>PostgreSQL</productname> language extension, as is the

entirerules system.

entirequery rewrite system.

</para>

</refsect1>

</refentry>