2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.34 2002/04/19 16:36:08 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATERULE">
8 <refentrytitle id="sql-createrule-title">CREATE RULE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
16 define a new rewrite rule
21 <date>2001-01-05</date>
24 CREATE RULE <replaceable class="parameter">name</replaceable> AS ON <replaceable class="parameter">event</replaceable>
25 TO <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
26 DO [ INSTEAD ] <replaceable class="parameter">action</replaceable>
28 where <replaceable class="PARAMETER">action</replaceable> can be:
32 <replaceable class="parameter">query</replaceable>
34 ( <replaceable class="parameter">query</replaceable> ; <replaceable class="parameter">query</replaceable> ... )
37 <refsect2 id="R2-SQL-CREATERULE-1">
39 <date>2001-01-05</date>
48 <term><replaceable class="parameter">name</replaceable></term>
51 The name of a rule to create. This must be distinct from the name
52 of any other rule for the same table.
57 <term><replaceable class="parameter">event</replaceable></term>
60 Event is one of <literal>SELECT</literal>,
61 <literal>UPDATE</literal>, <literal>DELETE</literal>
62 or <literal>INSERT</literal>.
67 <term><replaceable class="parameter">table</replaceable></term>
70 The name (optionally schema-qualified) of the table or view the rule
76 <term><replaceable class="parameter">condition</replaceable></term>
79 Any SQL conditional expression (returning <type>boolean</type>). The condition expression may not
80 refer to any tables except <literal>new</literal> and
81 <literal>old</literal>.
86 <term><replaceable class="parameter">query</replaceable></term>
89 The query or queries making up the
90 <replaceable class="PARAMETER">action</replaceable>
91 can be any SQL <literal>SELECT</literal>, <literal>INSERT</literal>,
92 <literal>UPDATE</literal>, <literal>DELETE</literal>, or
93 <literal>NOTIFY</literal> statement.
101 Within the <replaceable class="parameter">condition</replaceable>
102 and <replaceable class="PARAMETER">action</replaceable>, the special
103 table names <literal>new</literal> and <literal>old</literal> may be
104 used to refer to values in the referenced table.
105 <literal>new</literal> is valid in ON INSERT and ON UPDATE rules
106 to refer to the new row being inserted or updated.
107 <literal>old</literal> is valid in ON UPDATE and ON DELETE
108 rules to refer to the existing row being updated or deleted.
112 <refsect2 id="R2-SQL-CREATERULE-2">
114 <date>1998-09-11</date>
123 <term><computeroutput>
125 </computeroutput></term>
128 Message returned if the rule is successfully created.
137 <refsect1 id="R1-SQL-CREATERULE-1">
139 <date>1998-09-11</date>
146 The <productname>PostgreSQL</productname>
147 <firstterm>rule system</firstterm> allows one to define an
148 alternate action to be performed on inserts, updates, or deletions
149 from database tables. Rules are used to
150 implement table views as well.
154 The semantics of a rule is that at the time an individual instance (row)
156 accessed, inserted, updated, or deleted, there is an old instance (for
157 selects, updates and deletes) and a new instance (for inserts and
158 updates). All the rules for the given event type and the given target
159 table are examined successively (in order by name). If the
160 <replaceable class="parameter">condition</replaceable> specified in the
161 WHERE clause (if any) is true, the
162 <replaceable class="parameter">action</replaceable> part of the rule is
163 executed. The <replaceable class="parameter">action</replaceable> is
164 done instead of the original query if INSTEAD is specified; otherwise
165 it is done after the original query in the case of ON INSERT, or before
166 the original query in the case of ON UPDATE or ON DELETE.
167 Within both the <replaceable class="parameter">condition</replaceable>
168 and <replaceable class="parameter">action</replaceable>, values from
169 fields in the old instance and/or the new instance are substituted for
170 <literal>old.</literal><replaceable class="parameter">attribute-name</replaceable>
171 and <literal>new.</literal><replaceable class="parameter">attribute-name</replaceable>.
175 The <replaceable class="parameter">action</replaceable> part of the
176 rule can consist of one or more queries. To write multiple queries,
177 surround them with parentheses. Such queries will be performed in the
178 specified order. The <replaceable
179 class="parameter">action</replaceable> can also be NOTHING indicating
180 no action. Thus, a DO INSTEAD NOTHING rule suppresses the original
181 query from executing (when its condition is true); a DO NOTHING rule
186 The <replaceable class="parameter">action</replaceable> part of the rule
187 executes with the same command and transaction identifier as the user
188 command that caused activation.
192 It is important to realize that a rule is really a query transformation
193 mechanism, or query macro. The entire query is processed to convert it
194 into a series of queries that include the rule actions. This occurs
195 before evaluation of the query starts. So, conditional rules are
196 handled by adding the rule condition to the WHERE clause of the action(s)
197 derived from the rule. The above description of a rule as an operation
198 that executes for each row is thus somewhat misleading. If you actually
199 want an operation that fires independently for each physical row, you
200 probably want to use a trigger not a rule. Rules are most useful for
201 situations that call for transforming entire queries independently of
202 the specific data being handled.
205 <refsect2 id="R2-SQL-CREATERULE-3">
207 <date>2001-11-06</date>
213 Presently, ON SELECT rules must be unconditional INSTEAD rules and must
214 have actions that consist of a single SELECT query. Thus, an ON SELECT
215 rule effectively turns the table into a view, whose visible
216 contents are the rows returned by the rule's SELECT query rather than
217 whatever had been stored in the table (if anything). It is considered
218 better style to write a CREATE VIEW command than to create a real table
219 and define an ON SELECT rule for it.
223 <xref linkend="sql-createview"> creates a dummy table (with no underlying
224 storage) and associates an ON SELECT rule with it. The system will not
225 allow updates to the view, since it knows there is no real table there.
227 illusion of an updatable view by defining ON INSERT, ON UPDATE, and
228 ON DELETE rules (or any subset of those that's sufficient
229 for your purposes) to replace update actions on the view with
230 appropriate updates on other tables.
234 There is a catch if you try to use conditional
235 rules for view updates: there <emphasis>must</> be an unconditional
236 INSTEAD rule for each action you wish to allow on the view. If the
237 rule is conditional, or is not INSTEAD, then the system will still reject
238 attempts to perform the update action, because it thinks it might end up
239 trying to perform the action on the dummy table in some cases.
241 handle all the useful cases in conditional rules, you can; just add an
242 unconditional DO INSTEAD NOTHING rule to ensure that the system
243 understands it will never be called on to update the dummy table. Then
244 make the conditional rules non-INSTEAD; in the cases where they fire,
245 they add to the default INSTEAD NOTHING action.
249 <refsect2 id="R2-SQL-CREATERULE-4">
251 <date>2001-01-05</date>
257 You must have rule definition access to a table in order
258 to define a rule on it. Use <command>GRANT</command>
259 and <command>REVOKE</command> to change permissions.
263 It is very important to take care to avoid circular rules.
264 For example, though each
265 of the following two rule definitions are accepted by
266 <productname>PostgreSQL</productname>, the
267 select command will cause <productname>PostgreSQL</productname> to
268 report an error because the query cycled too many times:
271 CREATE RULE "_RETemp" AS
274 SELECT * FROM toyemp;
276 CREATE RULE "_RETtoyemp" AS
282 This attempt to select from <literal>EMP</literal> will cause
283 <productname>PostgreSQL</productname> to issue an error
284 because the queries cycled too many times:
292 Presently, if a rule contains a NOTIFY query, the NOTIFY will be executed
293 unconditionally --- that is, the NOTIFY will be issued even if there are
294 not any rows that the rule should apply to. For example, in
296 CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable;
298 UPDATE mytable SET name = 'foo' WHERE id = 42;
300 one NOTIFY event will be sent during the UPDATE, whether or not there
301 are any rows with id = 42. This is an implementation restriction that
302 may be fixed in future releases.
307 <refsect1 id="R1-SQL-CREATERULE-4">
312 <refsect2 id="R2-SQL-CREATERULE-5">
314 <date>1998-09-11</date>
321 <command>CREATE RULE</command> statement is a <productname>PostgreSQL</productname>
323 There is no <command>CREATE RULE</command> statement in <acronym>SQL92</acronym>.
329 <!-- Keep this comment at the end of the file
334 sgml-minimize-attributes:nil
335 sgml-always-quote-attributes:t
338 sgml-parent-document:nil
339 sgml-default-dtd-file:"../reference.ced"
340 sgml-exposed-tags:nil
341 sgml-local-catalogs:"/usr/lib/sgml/catalog"
342 sgml-local-ecat-files:nil