2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.44 2004/03/09 19:30:21 neilc Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATERULE">
8 <refentrytitle id="sql-createrule-title">CREATE RULE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>CREATE RULE</refname>
14 <refpurpose>define a new rewrite rule</refpurpose>
17 <indexterm zone="sql-createrule">
18 <primary>CREATE RULE</primary>
23 CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
24 TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
25 DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }
30 <title>Description</title>
33 <command>CREATE RULE</command> defines a new rule applying to a specified
35 <command>CREATE OR REPLACE RULE</command> will either create a
36 new rule, or replace an existing rule of the same name for the same
41 The <productname>PostgreSQL</productname> rule system allows one to
42 define an alternate action to be performed on insertions, updates,
43 or deletions in database tables. Roughly speaking, a rule causes
44 additional commands to be executed when a given command on a given
45 table is executed. Alternatively, an <literal>INSTEAD</literal>
46 rule can replace a given command by another, or cause a command
47 not to be executed at all. Rules are used to implement table
48 views as well. It is important to realize that a rule is really
49 a command transformation mechanism, or command macro. The
50 transformation happens before the execution of the commands starts.
51 If you actually want an operation that fires independently for each
52 physical row, you probably want to use a trigger, not a rule.
53 More information about the rules system is in <xref linkend="rules">.
57 Presently, <literal>ON SELECT</literal> rules must be unconditional
58 <literal>INSTEAD</literal> rules and must have actions that consist
59 of a single <command>SELECT</command> command. Thus, an
60 <literal>ON SELECT</literal> rule effectively turns the table into
61 a view, whose visible contents are the rows returned by the rule's
62 <command>SELECT</command> command rather than whatever had been
63 stored in the table (if anything). It is considered better style
64 to write a <command>CREATE VIEW</command> command than to create a
65 real table and define an <literal>ON SELECT</literal> rule for it.
69 You can create the illusion of an updatable view by defining
70 <literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and
71 <literal>ON DELETE</literal> rules (or any subset of those that's
72 sufficient for your purposes) to replace update actions on the view
73 with appropriate updates on other tables.
77 There is a catch if you try to use conditional rules for view
78 updates: there <emphasis>must</> be an unconditional
79 <literal>INSTEAD</literal> rule for each action you wish to allow
80 on the view. If the rule is conditional, or is not
81 <literal>INSTEAD</literal>, then the system will still reject
82 attempts to perform the update action, because it thinks it might
83 end up trying to perform the action on the dummy table of the view
84 in some cases. If you want to handle all the useful cases in
85 conditional rules, you can; just add an unconditional <literal>DO
86 INSTEAD NOTHING</literal> rule to ensure that the system
87 understands it will never be called on to update the dummy table.
88 Then make the conditional rules not <literal>INSTEAD</literal>; in
89 the cases where they are applied, they add to the default
90 <literal>INSTEAD NOTHING</literal> action.
95 <title>Parameters</title>
99 <term><replaceable class="parameter">name</replaceable></term>
102 The name of a rule to create. This must be distinct from the
103 name of any other rule for the same table. Multiple rules on
104 the same table and same event type are applied in alphabetical
111 <term><replaceable class="parameter">event</replaceable></term>
114 The event is one of <literal>SELECT</literal>,
115 <literal>INSERT</literal>, <literal>UPDATE</literal>, or
116 <literal>DELETE</literal>.
122 <term><replaceable class="parameter">table</replaceable></term>
125 The name (optionally schema-qualified) of the table or view the
132 <term><replaceable class="parameter">condition</replaceable></term>
135 Any <acronym>SQL</acronym> conditional expression (returning
136 <type>boolean</type>). The condition expression may not refer
137 to any tables except <literal>NEW</> and <literal>OLD</>, and
138 may not contain aggregate functions.
144 <term><option>INSTEAD</option></term>
147 <literal>INSTEAD</literal> indicates that the commands should be
148 executed <emphasis>instead of</> the original command.
154 <term><option>ALSO</option></term>
157 <literal>ALSO</literal> indicates that the commands should be
158 executed <emphasis>in addition to</emphasis> the original
163 If neither <literal>ALSO</literal> nor
164 <literal>INSTEAD</literal> is specified, <literal>ALSO</literal>
171 <term><replaceable class="parameter">command</replaceable></term>
174 The command or commands that make up the rule action. Valid
175 commands are <command>SELECT</command>,
176 <command>INSERT</command>, <command>UPDATE</command>,
177 <command>DELETE</command>, or <command>NOTIFY</command>.
184 Within <replaceable class="parameter">condition</replaceable> and
185 <replaceable class="parameter">command</replaceable>, the special
186 table names <literal>NEW</literal> and <literal>OLD</literal> may
187 be used to refer to values in the referenced table.
188 <literal>NEW</literal> is valid in <literal>ON INSERT</literal> and
189 <literal>ON UPDATE</literal> rules to refer to the new row being
190 inserted or updated. <literal>OLD</literal> is valid in
191 <literal>ON UPDATE</literal> and <literal>ON DELETE</literal> rules
192 to refer to the existing row being updated or deleted.
200 You must have the privilege <literal>RULE</literal> on a table to
201 be allowed to define a rule on it.
205 It is very important to take care to avoid circular rules. For
206 example, though each of the following two rule definitions are
207 accepted by <productname>PostgreSQL</productname>, the
208 <command>SELECT</command> command would cause
209 <productname>PostgreSQL</productname> to report an error because
210 the query cycled too many times:
213 CREATE RULE "_RETURN" AS
218 CREATE RULE "_RETURN" AS
228 Presently, if a rule action contains a <command>NOTIFY</command>
229 command, the <command>NOTIFY</command> command will be executed
230 unconditionally, that is, the <command>NOTIFY</command> will be
231 issued even if there are not any rows that the rule should apply
234 CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;
236 UPDATE mytable SET name = 'foo' WHERE id = 42;
238 one <command>NOTIFY</command> event will be sent during the
239 <command>UPDATE</command>, whether or not there are any rows that
240 match the condition <literal>id = 42</literal>. This is an
241 implementation restriction that may be fixed in future releases.
246 <title>Compatibility</title>
249 <command>CREATE RULE</command> is a
250 <productname>PostgreSQL</productname> language extension, as is the
251 entire query rewrite system.
256 <!-- Keep this comment at the end of the file
261 sgml-minimize-attributes:nil
262 sgml-always-quote-attributes:t
265 sgml-parent-document:nil
266 sgml-default-dtd-file:"../reference.ced"
267 sgml-exposed-tags:nil
268 sgml-local-catalogs:"/usr/lib/sgml/catalog"
269 sgml-local-ecat-files:nil