create_rule.sgml 10 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.52 2007/11/07 12:24:23 petere Exp $
3
PostgreSQL documentation
4 5
-->

6 7
<refentry id="SQL-CREATERULE">
 <refmeta>
8
  <refentrytitle id="sql-createrule-title">CREATE RULE</refentrytitle>
9 10
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
11

12
 <refnamediv>
13 14
  <refname>CREATE RULE</refname>
  <refpurpose>define a new rewrite rule</refpurpose>
15
 </refnamediv>
16

Peter Eisentraut's avatar
Peter Eisentraut committed
17 18 19 20
 <indexterm zone="sql-createrule">
  <primary>CREATE RULE</primary>
 </indexterm>

21
 <refsynopsisdiv>
22
<synopsis>
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> ... ) }
26
</synopsis>
27 28
 </refsynopsisdiv>

29 30
 <refsect1>
  <title>Description</title>
31

32 33 34 35 36 37 38 39
  <para>
   <command>CREATE RULE</command> defines a new rule applying to a specified
   table or view.
   <command>CREATE OR REPLACE RULE</command> will either create a
   new rule, or replace an existing rule of the same name for the same
   table.
  </para>

40
  <para>
41
   The <productname>PostgreSQL</productname> rule system allows one to
42
   define an alternative action to be performed on insertions, updates,
43 44
   or deletions in database tables.  Roughly speaking, a rule causes
   additional commands to be executed when a given command on a given
45
   table is executed.  Alternatively, an <literal>INSTEAD</literal>
46 47 48 49 50 51 52
   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.
53
   More information about the rules system is in <xref linkend="rules">.
54
  </para>
55
  
56
  <para>
57 58 59 60 61 62 63 64 65
   Presently, <literal>ON SELECT</literal> rules must be unconditional
   <literal>INSTEAD</literal> rules and must have actions that consist
   of a single <command>SELECT</command> command.  Thus, an
   <literal>ON SELECT</literal> rule effectively turns the table into
   a view, whose visible contents are the rows returned by the rule's
   <command>SELECT</command> command rather than whatever had been
   stored in the table (if anything).  It is considered better style
   to write a <command>CREATE VIEW</command> command than to create a
   real table and define an <literal>ON SELECT</literal> rule for it.
66
  </para>
67

68
  <para>
69 70 71 72
   You can create the illusion of an updatable view by defining
   <literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and
   <literal>ON DELETE</literal> rules (or any subset of those that's
   sufficient for your purposes) to replace update actions on the view
73 74 75
   with appropriate updates on other tables.  If you want to support
   <command>INSERT RETURNING</> and so on, then be sure to put a suitable
   <literal>RETURNING</> clause into each of these rules.
76 77
  </para>

78
  <para>
79 80 81 82 83 84 85 86
   There is a catch if you try to use conditional rules for view
   updates: there <emphasis>must</> be an unconditional
   <literal>INSTEAD</literal> rule for each action you wish to allow
   on the view.  If the rule is conditional, or is not
   <literal>INSTEAD</literal>, 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
87
   conditional rules, add an unconditional <literal>DO
88 89
   INSTEAD NOTHING</literal> rule to ensure that the system
   understands it will never be called on to update the dummy table.
90
   Then make the conditional rules non-<literal>INSTEAD</literal>; in
91
   the cases where they are applied, they add to the default
92 93
   <literal>INSTEAD NOTHING</literal> action.  (This method does not
   currently work to support <literal>RETURNING</> queries, however.)
94
  </para>
95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><replaceable class="parameter">name</replaceable></term>
    <listitem>
     <para>
      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.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">event</replaceable></term>
    <listitem>
     <para>
117
      The event is one of <literal>SELECT</literal>,
118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
      <literal>INSERT</literal>, <literal>UPDATE</literal>, or
      <literal>DELETE</literal>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">table</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of the table or view the
      rule applies to.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">condition</replaceable></term>
    <listitem>
     <para>
138
      Any <acronym>SQL</acronym> conditional expression (returning
139
      <type>boolean</type>).  The condition expression cannot refer
140
      to any tables except <literal>NEW</> and <literal>OLD</>, and
141
      cannot contain aggregate functions.
142 143 144 145
     </para>
    </listitem>
   </varlistentry>

146 147 148 149 150
   <varlistentry>
    <term><option>INSTEAD</option></term>
    <listitem>
     <para>
      <literal>INSTEAD</literal> indicates that the commands should be
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168
      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.
169 170 171 172
     </para>
    </listitem>
   </varlistentry>

173 174 175 176 177
   <varlistentry>
    <term><replaceable class="parameter">command</replaceable></term>
    <listitem>
     <para>
      The command or commands that make up the rule action.  Valid
178 179 180
      commands are <command>SELECT</command>,
      <command>INSERT</command>, <command>UPDATE</command>,
      <command>DELETE</command>, or <command>NOTIFY</command>.
181 182 183 184
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
185 186

  <para>
187 188
   Within <replaceable class="parameter">condition</replaceable> and
   <replaceable class="parameter">command</replaceable>, the special
189
   table names <literal>NEW</literal> and <literal>OLD</literal> can
190 191 192 193 194 195
   be used to refer to values in the referenced table.
   <literal>NEW</literal> is valid in <literal>ON INSERT</literal> and
   <literal>ON UPDATE</literal> rules to refer to the new row being
   inserted or updated.  <literal>OLD</literal> is valid in
   <literal>ON UPDATE</literal> and <literal>ON DELETE</literal> rules
   to refer to the existing row being updated or deleted.
196
  </para>
197
 </refsect1>
198

199 200
 <refsect1>
  <title>Notes</title>
201

202
  <para>
203
   You must be the owner of a table to create or change rules for it.
204
  </para>
205

206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222
  <para>
   In a rule for <literal>INSERT</literal>, <literal>UPDATE</literal>, or
   <literal>DELETE</literal> on a view, you can add a <literal>RETURNING</>
   clause that emits the view's columns.  This clause will be used to compute
   the outputs if the rule is triggered by an <command>INSERT RETURNING</>,
   <command>UPDATE RETURNING</>, or <command>DELETE RETURNING</> command
   respectively.  When the rule is triggered by a command without
   <literal>RETURNING</>, the rule's <literal>RETURNING</> clause will be
   ignored.  The current implementation allows only unconditional
   <literal>INSTEAD</> rules to contain <literal>RETURNING</>; furthermore
   there can be at most one <literal>RETURNING</> clause among all the rules
   for the same event.  (This ensures that there is only one candidate
   <literal>RETURNING</> clause to be used to compute the results.)
   <literal>RETURNING</> queries on the view will be rejected if
   there is no <literal>RETURNING</> clause in any available rule.
  </para>

223 224 225 226 227 228
  <para>
   It is very important to take care to avoid circular rules.  For
   example, though each of the following two rule definitions are
   accepted by <productname>PostgreSQL</productname>, the
   <command>SELECT</command> command would cause
   <productname>PostgreSQL</productname> to report an error because
229
   of recursive expansion of a rule:
230

231
<programlisting>
232
CREATE RULE "_RETURN" AS
233
    ON SELECT TO t1
Bruce Momjian's avatar
Bruce Momjian committed
234
    DO INSTEAD 
235
        SELECT * FROM t2;
236

237
CREATE RULE "_RETURN" AS
238
    ON SELECT TO t2
Bruce Momjian's avatar
Bruce Momjian committed
239
    DO INSTEAD 
240
        SELECT * FROM t1;
241

242
SELECT * FROM t1;
243
</programlisting>
244
  </para>
245

246 247 248 249 250
  <para>
   Presently, if a rule action contains a <command>NOTIFY</command>
   command, the <command>NOTIFY</command> command will be executed
   unconditionally, that is, the <command>NOTIFY</command> will be
   issued even if there are not any rows that the rule should apply
251
   to.  For example, in:
252
<programlisting>
253
CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;
254 255

UPDATE mytable SET name = 'foo' WHERE id = 42;
256 257
</programlisting>
   one <command>NOTIFY</command> event will be sent during the
258 259
   <command>UPDATE</command>, whether or not there are any rows that
   match the condition <literal>id = 42</literal>.  This is an
260
   implementation restriction that might be fixed in future releases.
261
  </para>
262
 </refsect1>
263

264 265
 <refsect1>
  <title>Compatibility</title>
266

267 268 269
  <para>
   <command>CREATE RULE</command> is a
   <productname>PostgreSQL</productname> language extension, as is the
270
   entire query rewrite system.
271
  </para>
272
 </refsect1>
273
</refentry>