Debian Codegraph @

doc/src/sgml/ref/create_rule.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_rule.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createrule">
7 <indexterm zone="sql-createrule">
8 <primary>CREATE RULE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE RULE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE RULE</refname>
19 <refpurpose>define a new rewrite rule</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
25 TO <replaceable class="parameter">table_name</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
26 DO [ ALSO | INSTEAD ] { NOTHING | <replaceable class="parameter">command</replaceable> | ( <replaceable class="parameter">command</replaceable> ; <replaceable class="parameter">command</replaceable> ... ) }
27
28 <phrase>where <replaceable class="parameter">event</replaceable> can be one of:</phrase>
29
30 SELECT | INSERT | UPDATE | DELETE
31 </synopsis>
32 </refsynopsisdiv>
33
34 <refsect1>
35 <title>Description</title>
36
37 <para>
38 <command>CREATE RULE</command> defines a new rule applying to a specified
39 table or view.
40 <command>CREATE OR REPLACE RULE</command> will either create a
41 new rule, or replace an existing rule of the same name for the same
42 table.
43 </para>
44
45 <para>
46 The <productname>PostgreSQL</productname> rule system allows one to
47 define an alternative action to be performed on insertions, updates,
48 or deletions in database tables. Roughly speaking, a rule causes
49 additional commands to be executed when a given command on a given
50 table is executed. Alternatively, an <literal>INSTEAD</literal>
51 rule can replace a given command by another, or cause a command
52 not to be executed at all. Rules are used to implement SQL
53 views as well. It is important to realize that a rule is really
54 a command transformation mechanism, or command macro. The
55 transformation happens before the execution of the command starts.
56 If you actually want an operation that fires independently for each
57 physical row, you probably want to use a trigger, not a rule.
58 More information about the rules system is in <xref linkend="rules"/>.
59 </para>
60
61 <para>
62 Presently, <literal>ON SELECT</literal> rules can only be attached
63 to views. Such a rule must be named <literal>"_RETURN"</literal>,
64 must be an unconditional <literal>INSTEAD</literal> rule, and must have
65 an action that consists of a single <command>SELECT</command> command.
66 This command defines the visible contents of the view. (The view
67 itself is basically a dummy table with no storage.) It's best to
68 regard such a rule as an implementation detail. While a view can be
69 redefined via <literal>CREATE OR REPLACE RULE "_RETURN" AS
70 ...</literal>, it's better style to use <literal>CREATE OR REPLACE
71 VIEW</literal>.
72 </para>
73
74 <para>
75 You can create the illusion of an updatable view by defining
76 <literal>ON INSERT</literal>, <literal>ON UPDATE</literal>, and
77 <literal>ON DELETE</literal> rules (or any subset of those that's
78 sufficient for your purposes) to replace update actions on the view
79 with appropriate updates on other tables. If you want to support
80 <command>INSERT RETURNING</command> and so on, then be sure to put a suitable
81 <literal>RETURNING</literal> clause into each of these rules.
82 </para>
83
84 <para>
85 There is a catch if you try to use conditional rules for complex view
86 updates: there <emphasis>must</emphasis> be an unconditional
87 <literal>INSTEAD</literal> rule for each action you wish to allow
88 on the view. If the rule is conditional, or is not
89 <literal>INSTEAD</literal>, then the system will still reject
90 attempts to perform the update action, because it thinks it might
91 end up trying to perform the action on the dummy table of the view
92 in some cases. If you want to handle all the useful cases in
93 conditional rules, add an unconditional <literal>DO
94 INSTEAD NOTHING</literal> rule to ensure that the system
95 understands it will never be called on to update the dummy table.
96 Then make the conditional rules non-<literal>INSTEAD</literal>; in
97 the cases where they are applied, they add to the default
98 <literal>INSTEAD NOTHING</literal> action. (This method does not
99 currently work to support <literal>RETURNING</literal> queries, however.)
100 </para>
101
102 <note>
103 <para>
104 A view that is simple enough to be automatically updatable (see <xref
105 linkend="sql-createview"/>) does not require a user-created rule in
106 order to be updatable. While you can create an explicit rule anyway,
107 the automatic update transformation will generally outperform an
108 explicit rule.
109 </para>
110
111 <para>
112 Another alternative worth considering is to use <literal>INSTEAD OF</literal>
113 triggers (see <xref linkend="sql-createtrigger"/>) in place of rules.
114 </para>
115 </note>
116 </refsect1>
117
118 <refsect1>
119 <title>Parameters</title>
120
121 <variablelist>
122 <varlistentry>
123 <term><replaceable class="parameter">name</replaceable></term>
124 <listitem>
125 <para>
126 The name of a rule to create. This must be distinct from the
127 name of any other rule for the same table. Multiple rules on
128 the same table and same event type are applied in alphabetical
129 name order.
130 </para>
131 </listitem>
132 </varlistentry>
133
134 <varlistentry>
135 <term><replaceable class="parameter">event</replaceable></term>
136 <listitem>
137 <para>
138 The event is one of <literal>SELECT</literal>,
139 <literal>INSERT</literal>, <literal>UPDATE</literal>, or
140 <literal>DELETE</literal>. Note that an
141 <command>INSERT</command> containing an <literal>ON
142 CONFLICT</literal> clause cannot be used on tables that have
143 either <literal>INSERT</literal> or <literal>UPDATE</literal>
144 rules. Consider using an updatable view instead.
145 </para>
146 </listitem>
147 </varlistentry>
148
149 <varlistentry>
150 <term><replaceable class="parameter">table_name</replaceable></term>
151 <listitem>
152 <para>
153 The name (optionally schema-qualified) of the table or view the
154 rule applies to.
155 </para>
156 </listitem>
157 </varlistentry>
158
159 <varlistentry>
160 <term><replaceable class="parameter">condition</replaceable></term>
161 <listitem>
162 <para>
163 Any <acronym>SQL</acronym> conditional expression (returning
164 <type>boolean</type>). The condition expression cannot refer
165 to any tables except <literal>NEW</literal> and <literal>OLD</literal>, and
166 cannot contain aggregate functions.
167 </para>
168 </listitem>
169 </varlistentry>
170
171 <varlistentry>
172 <term><option>INSTEAD</option></term>
173 <listitem>
174 <para><literal>INSTEAD</literal> indicates that the commands should be
175 executed <emphasis>instead of</emphasis> the original command.
176 </para>
177 </listitem>
178 </varlistentry>
179
180 <varlistentry>
181 <term><option>ALSO</option></term>
182 <listitem>
183 <para><literal>ALSO</literal> indicates that the commands should be
184 executed <emphasis>in addition to</emphasis> the original
185 command.
186 </para>
187
188 <para>
189 If neither <literal>ALSO</literal> nor
190 <literal>INSTEAD</literal> is specified, <literal>ALSO</literal>
191 is the default.
192 </para>
193 </listitem>
194 </varlistentry>
195
196 <varlistentry>
197 <term><replaceable class="parameter">command</replaceable></term>
198 <listitem>
199 <para>
200 The command or commands that make up the rule action. Valid
201 commands are <command>SELECT</command>,
202 <command>INSERT</command>, <command>UPDATE</command>,
203 <command>DELETE</command>, or <command>NOTIFY</command>.
204 </para>
205 </listitem>
206 </varlistentry>
207 </variablelist>
208
209 <para>
210 Within <replaceable class="parameter">condition</replaceable> and
211 <replaceable class="parameter">command</replaceable>, the special
212 table names <literal>NEW</literal> and <literal>OLD</literal> can
213 be used to refer to values in the referenced table.
214 <literal>NEW</literal> is valid in <literal>ON INSERT</literal> and
215 <literal>ON UPDATE</literal> rules to refer to the new row being
216 inserted or updated. <literal>OLD</literal> is valid in
217 <literal>ON UPDATE</literal> and <literal>ON DELETE</literal> rules
218 to refer to the existing row being updated or deleted.
219 </para>
220 </refsect1>
221
222 <refsect1>
223 <title>Notes</title>
224
225 <para>
226 You must be the owner of a table to create or change rules for it.
227 </para>
228
229 <para>
230 In a rule for <literal>INSERT</literal>, <literal>UPDATE</literal>, or
231 <literal>DELETE</literal> on a view, you can add a <literal>RETURNING</literal>
232 clause that emits the view's columns. This clause will be used to compute
233 the outputs if the rule is triggered by an <command>INSERT RETURNING</command>,
234 <command>UPDATE RETURNING</command>, or <command>DELETE RETURNING</command> command
235 respectively. When the rule is triggered by a command without
236 <literal>RETURNING</literal>, the rule's <literal>RETURNING</literal> clause will be
237 ignored. The current implementation allows only unconditional
238 <literal>INSTEAD</literal> rules to contain <literal>RETURNING</literal>; furthermore
239 there can be at most one <literal>RETURNING</literal> clause among all the rules
240 for the same event. (This ensures that there is only one candidate
241 <literal>RETURNING</literal> clause to be used to compute the results.)
242 <literal>RETURNING</literal> queries on the view will be rejected if
243 there is no <literal>RETURNING</literal> clause in any available rule.
244 </para>
245
246 <para>
247 It is very important to take care to avoid circular rules. For
248 example, though each of the following two rule definitions are
249 accepted by <productname>PostgreSQL</productname>, the
250 <command>SELECT</command> command would cause
251 <productname>PostgreSQL</productname> to report an error because
252 of recursive expansion of a rule:
253
254 <programlisting>
255 CREATE RULE "_RETURN" AS
256 ON SELECT TO t1
257 DO INSTEAD
258 SELECT * FROM t2;
259
260 CREATE RULE "_RETURN" AS
261 ON SELECT TO t2
262 DO INSTEAD
263 SELECT * FROM t1;
264
265 SELECT * FROM t1;
266 </programlisting>
267 </para>
268
269 <para>
270 Presently, if a rule action contains a <command>NOTIFY</command>
271 command, the <command>NOTIFY</command> command will be executed
272 unconditionally, that is, the <command>NOTIFY</command> will be
273 issued even if there are not any rows that the rule should apply
274 to. For example, in:
275 <programlisting>
276 CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;
277
278 UPDATE mytable SET name = 'foo' WHERE id = 42;
279 </programlisting>
280 one <command>NOTIFY</command> event will be sent during the
281 <command>UPDATE</command>, whether or not there are any rows that
282 match the condition <literal>id = 42</literal>. This is an
283 implementation restriction that might be fixed in future releases.
284 </para>
285 </refsect1>
286
287 <refsect1>
288 <title>Compatibility</title>
289
290 <para>
291 <command>CREATE RULE</command> is a
292 <productname>PostgreSQL</productname> language extension, as is the
293 entire query rewrite system.
294 </para>
295 </refsect1>
296
297 <refsect1>
298 <title>See Also</title>
299
300 <simplelist type="inline">
301 <member><xref linkend="sql-alterrule"/></member>
302 <member><xref linkend="sql-droprule"/></member>
303 </simplelist>
304 </refsect1>
305
306 </refentry>