begriffs open source - ai-pg/blob - full-docs/src/sgml/html/rules-update.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>39.4. Rules on INSERT, UPDATE, and DELETE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="rules-materializedviews.html" title="39.3. Materialized Views" /><link rel="next" href="rules-privileges.html" title="39.5. Rules and Privileges" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">39.4. Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-materializedviews.html" title="39.3. Materialized Views">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 39. The Rule System">Up</a></td><th width="60%" align="center">Chapter 39. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-privileges.html" title="39.5. Rules and Privileges">Next</a></td></tr></table><hr /></div><div class="sect1" id="RULES-UPDATE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">39.4. Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code> <a href="#RULES-UPDATE" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="rules-update.html#RULES-UPDATE-HOW">39.4.1. How Update Rules Work</a></span></dt><dt><span class="sect2"><a href="rules-update.html#RULES-UPDATE-VIEWS">39.4.2. Cooperation with Views</a></span></dt></dl></div><a id="id-1.8.6.9.2" class="indexterm"></a><a id="id-1.8.6.9.3" class="indexterm"></a><a id="id-1.8.6.9.4" class="indexterm"></a><p>
   3     Rules that are defined on <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
   4     and <code class="command">DELETE</code> are significantly different from the view rules
   5     described in the previous sections. First, their <code class="command">CREATE
   6     RULE</code> command allows more:
   7
   8     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
   9             They are allowed to have no action.
  10         </p></li><li class="listitem"><p>
  11             They can have multiple actions.
  12         </p></li><li class="listitem"><p>
  13             They can be <code class="literal">INSTEAD</code> or <code class="literal">ALSO</code> (the default).
  14         </p></li><li class="listitem"><p>
  15             The pseudorelations <code class="literal">NEW</code> and <code class="literal">OLD</code> become useful.
  16         </p></li><li class="listitem"><p>
  17             They can have rule qualifications.
  18         </p></li></ul></div><p>
  19
  20     Second, they don't modify the query tree in place. Instead they
  21     create zero or more new query trees and can throw away the
  22     original one.
  23 </p><div class="caution"><h3 class="title">Caution</h3><p>
  24   In many cases, tasks that could be performed by rules
  25   on <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> are better done
  26   with triggers.  Triggers are notationally a bit more complicated, but their
  27   semantics are much simpler to understand.  Rules tend to have surprising
  28   results when the original query contains volatile functions: volatile
  29   functions may get executed more times than expected in the process of
  30   carrying out the rules.
  31  </p><p>
  32   Also, there are some cases that are not supported by these types of rules at
  33   all, notably including <code class="literal">WITH</code> clauses in the original query and
  34   multiple-assignment sub-<code class="literal">SELECT</code>s in the <code class="literal">SET</code> list
  35   of <code class="command">UPDATE</code> queries.  This is because copying these constructs
  36   into a rule query would result in multiple evaluations of the sub-query,
  37   contrary to the express intent of the query's author.
  38  </p></div><div class="sect2" id="RULES-UPDATE-HOW"><div class="titlepage"><div><div><h3 class="title">39.4.1. How Update Rules Work <a href="#RULES-UPDATE-HOW" class="id_link">#</a></h3></div></div></div><p>
  39     Keep the syntax:
  40
  41 </p><pre class="programlisting">
  42 CREATE [ OR REPLACE ] RULE <em class="replaceable"><code>name</code></em> AS ON <em class="replaceable"><code>event</code></em>
  43     TO <em class="replaceable"><code>table</code></em> [ WHERE <em class="replaceable"><code>condition</code></em> ]
  44     DO [ ALSO | INSTEAD ] { NOTHING | <em class="replaceable"><code>command</code></em> | ( <em class="replaceable"><code>command</code></em> ; <em class="replaceable"><code>command</code></em> ... ) }
  45 </pre><p>
  46
  47     in mind.
  48     In the following, <em class="firstterm">update rules</em> means rules that are defined
  49     on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>.
  50 </p><p>
  51     Update rules get applied by the rule system when the result
  52     relation and the command type of a query tree are equal to the
  53     object and event given in the <code class="command">CREATE RULE</code> command.
  54     For update rules, the rule system creates a list of query trees.
  55     Initially the query-tree list is empty.
  56     There can be zero (<code class="literal">NOTHING</code> key word), one, or multiple actions.
  57     To simplify, we will look at a rule with one action. This rule
  58     can have a qualification or not and it can be <code class="literal">INSTEAD</code> or
  59     <code class="literal">ALSO</code> (the default).
  60 </p><p>
  61     What is a rule qualification? It is a restriction that tells
  62     when the actions of the rule should be done and when not. This
  63     qualification can only reference the pseudorelations <code class="literal">NEW</code> and/or <code class="literal">OLD</code>,
  64     which basically represent the relation that was given as object (but with a
  65     special meaning).
  66 </p><p>
  67     So we have three cases that produce the following query trees for
  68     a one-action rule.
  69
  70     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">No qualification, with either <code class="literal">ALSO</code> or
  71       <code class="literal">INSTEAD</code></span></dt><dd><p>
  72         the query tree from the rule action with the original query
  73         tree's qualification added
  74        </p></dd><dt><span class="term">Qualification given and <code class="literal">ALSO</code></span></dt><dd><p>
  75         the query tree from the rule action with the rule
  76         qualification and the original query tree's qualification
  77         added
  78        </p></dd><dt><span class="term">Qualification given and <code class="literal">INSTEAD</code></span></dt><dd><p>
  79         the query tree from the rule action with the rule
  80         qualification and the original query tree's qualification; and
  81         the original query tree with the negated rule qualification
  82         added
  83        </p></dd></dl></div><p>
  84
  85     Finally, if the rule is <code class="literal">ALSO</code>, the unchanged original query tree is
  86     added to the list. Since only qualified <code class="literal">INSTEAD</code> rules already add the
  87     original query tree, we end up with either one or two output query trees
  88     for a rule with one action.
  89 </p><p>
  90     For <code class="literal">ON INSERT</code> rules, the original query (if not suppressed by <code class="literal">INSTEAD</code>)
  91     is done before any actions added by rules.  This allows the actions to
  92     see the inserted row(s).  But for <code class="literal">ON UPDATE</code> and <code class="literal">ON
  93     DELETE</code> rules, the original query is done after the actions added by rules.
  94     This ensures that the actions can see the to-be-updated or to-be-deleted
  95     rows; otherwise, the actions might do nothing because they find no rows
  96     matching their qualifications.
  97 </p><p>
  98     The query trees generated from rule actions are thrown into the
  99     rewrite system again, and maybe more rules get applied resulting
 100     in additional or fewer query trees.
 101     So a rule's actions must have either a different
 102     command type or a different result relation than the rule itself is
 103     on, otherwise this recursive process will end up in an infinite loop.
 104     (Recursive expansion of a rule will be detected and reported as an
 105     error.)
 106 </p><p>
 107     The query trees found in the actions of the
 108     <code class="structname">pg_rewrite</code> system catalog are only
 109     templates. Since they can reference the range-table entries for
 110     <code class="literal">NEW</code> and <code class="literal">OLD</code>, some substitutions have to be made before they can be
 111     used. For any reference to <code class="literal">NEW</code>, the target list of the original
 112     query is searched for a corresponding entry. If found, that
 113     entry's expression replaces the reference. Otherwise, <code class="literal">NEW</code> means the
 114     same as <code class="literal">OLD</code> (for an <code class="command">UPDATE</code>) or is replaced by
 115     a null value (for an <code class="command">INSERT</code>). Any reference to <code class="literal">OLD</code> is
 116     replaced by a reference to the range-table entry that is the
 117     result relation.
 118 </p><p>
 119     After the system is done applying update rules, it applies view rules to the
 120     produced query tree(s).  Views cannot insert new update actions so
 121     there is no need to apply update rules to the output of view rewriting.
 122 </p><div class="sect3" id="RULES-UPDATE-HOW-FIRST"><div class="titlepage"><div><div><h4 class="title">39.4.1.1. A First Rule Step by Step <a href="#RULES-UPDATE-HOW-FIRST" class="id_link">#</a></h4></div></div></div><p>
 123     Say we want to trace changes to the <code class="literal">sl_avail</code> column in the
 124     <code class="literal">shoelace_data</code> relation. So we set up a log table
 125     and a rule that conditionally writes a log entry when an
 126     <code class="command">UPDATE</code> is performed on
 127     <code class="literal">shoelace_data</code>.
 128
 129 </p><pre class="programlisting">
 130 CREATE TABLE shoelace_log (
 131     sl_name    text,          -- shoelace changed
 132     sl_avail   integer,       -- new available value
 133     log_who    text,          -- who did it
 134     log_when   timestamp      -- when
 135 );
 136
 137 CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
 138     WHERE NEW.sl_avail &lt;&gt; OLD.sl_avail
 139     DO INSERT INTO shoelace_log VALUES (
 140                                     NEW.sl_name,
 141                                     NEW.sl_avail,
 142                                     current_user,
 143                                     current_timestamp
 144                                 );
 145 </pre><p>
 146 </p><p>
 147     Now someone does:
 148
 149 </p><pre class="programlisting">
 150 UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
 151 </pre><p>
 152
 153     and we look at the log table:
 154
 155 </p><pre class="programlisting">
 156 SELECT * FROM shoelace_log;
 157
 158  sl_name | sl_avail | log_who | log_when
 159 ---------+----------+---------+----------------------------------
 160  sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST
 161 (1 row)
 162 </pre><p>
 163    </p><p>
 164     That's what we expected. What happened in the background is the following.
 165     The parser created the query tree:
 166
 167 </p><pre class="programlisting">
 168 UPDATE shoelace_data SET sl_avail = 6
 169   FROM shoelace_data shoelace_data
 170  WHERE shoelace_data.sl_name = 'sl7';
 171 </pre><p>
 172
 173     There is a rule <code class="literal">log_shoelace</code> that is <code class="literal">ON UPDATE</code> with the rule
 174     qualification expression:
 175
 176 </p><pre class="programlisting">
 177 NEW.sl_avail &lt;&gt; OLD.sl_avail
 178 </pre><p>
 179
 180     and the action:
 181
 182 </p><pre class="programlisting">
 183 INSERT INTO shoelace_log VALUES (
 184        new.sl_name, new.sl_avail,
 185        current_user, current_timestamp )
 186   FROM shoelace_data new, shoelace_data old;
 187 </pre><p>
 188
 189     (This looks a little strange since you cannot normally write
 190     <code class="literal">INSERT ... VALUES ... FROM</code>.  The <code class="literal">FROM</code>
 191     clause here is just to indicate that there are range-table entries
 192     in the query tree for <code class="literal">new</code> and <code class="literal">old</code>.
 193     These are needed so that they can be referenced by variables in
 194     the <code class="command">INSERT</code> command's query tree.)
 195 </p><p>
 196     The rule is a qualified <code class="literal">ALSO</code> rule, so the rule system
 197     has to return two query trees: the modified rule action and the original
 198     query tree. In step 1, the range table of the original query is
 199     incorporated into the rule's action query tree. This results in:
 200
 201 </p><pre class="programlisting">
 202 INSERT INTO shoelace_log VALUES (
 203        new.sl_name, new.sl_avail,
 204        current_user, current_timestamp )
 205   FROM shoelace_data new, shoelace_data old,
 206        <span class="emphasis"><strong>shoelace_data shoelace_data</strong></span>;
 207 </pre><p>
 208
 209     In step 2, the rule qualification is added to it, so the result set
 210     is restricted to rows where <code class="literal">sl_avail</code> changes:
 211
 212 </p><pre class="programlisting">
 213 INSERT INTO shoelace_log VALUES (
 214        new.sl_name, new.sl_avail,
 215        current_user, current_timestamp )
 216   FROM shoelace_data new, shoelace_data old,
 217        shoelace_data shoelace_data
 218  <span class="emphasis"><strong>WHERE new.sl_avail &lt;&gt; old.sl_avail</strong></span>;
 219 </pre><p>
 220
 221     (This looks even stranger, since <code class="literal">INSERT ... VALUES</code> doesn't have
 222     a <code class="literal">WHERE</code> clause either, but the planner and executor will have no
 223     difficulty with it.  They need to support this same functionality
 224     anyway for <code class="literal">INSERT ... SELECT</code>.)
 225    </p><p>
 226     In step 3, the original query tree's qualification is added,
 227     restricting the result set further to only the rows that would have been touched
 228     by the original query:
 229
 230 </p><pre class="programlisting">
 231 INSERT INTO shoelace_log VALUES (
 232        new.sl_name, new.sl_avail,
 233        current_user, current_timestamp )
 234   FROM shoelace_data new, shoelace_data old,
 235        shoelace_data shoelace_data
 236  WHERE new.sl_avail &lt;&gt; old.sl_avail
 237    <span class="emphasis"><strong>AND shoelace_data.sl_name = 'sl7'</strong></span>;
 238 </pre><p>
 239    </p><p>
 240     Step 4 replaces references to <code class="literal">NEW</code> by the target list entries from the
 241     original query tree or by the matching variable references
 242     from the result relation:
 243
 244 </p><pre class="programlisting">
 245 INSERT INTO shoelace_log VALUES (
 246        <span class="emphasis"><strong>shoelace_data.sl_name</strong></span>, <span class="emphasis"><strong>6</strong></span>,
 247        current_user, current_timestamp )
 248   FROM shoelace_data new, shoelace_data old,
 249        shoelace_data shoelace_data
 250  WHERE <span class="emphasis"><strong>6</strong></span> &lt;&gt; old.sl_avail
 251    AND shoelace_data.sl_name = 'sl7';
 252 </pre><p>
 253
 254    </p><p>
 255     Step 5 changes <code class="literal">OLD</code> references into result relation references:
 256
 257 </p><pre class="programlisting">
 258 INSERT INTO shoelace_log VALUES (
 259        shoelace_data.sl_name, 6,
 260        current_user, current_timestamp )
 261   FROM shoelace_data new, shoelace_data old,
 262        shoelace_data shoelace_data
 263  WHERE 6 &lt;&gt; <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span>
 264    AND shoelace_data.sl_name = 'sl7';
 265 </pre><p>
 266    </p><p>
 267     That's it.  Since the rule is <code class="literal">ALSO</code>, we also output the
 268     original query tree.  In short, the output from the rule system
 269     is a list of two query trees that correspond to these statements:
 270
 271 </p><pre class="programlisting">
 272 INSERT INTO shoelace_log VALUES (
 273        shoelace_data.sl_name, 6,
 274        current_user, current_timestamp )
 275   FROM shoelace_data
 276  WHERE 6 &lt;&gt; shoelace_data.sl_avail
 277    AND shoelace_data.sl_name = 'sl7';
 278
 279 UPDATE shoelace_data SET sl_avail = 6
 280  WHERE sl_name = 'sl7';
 281 </pre><p>
 282
 283     These are executed in this order, and that is exactly what
 284     the rule was meant to do.
 285    </p><p>
 286     The substitutions and the added qualifications
 287     ensure that, if the original query would be, say:
 288
 289 </p><pre class="programlisting">
 290 UPDATE shoelace_data SET sl_color = 'green'
 291  WHERE sl_name = 'sl7';
 292 </pre><p>
 293
 294     no log entry would get written.  In that case, the original query
 295     tree does not contain a target list entry for
 296     <code class="literal">sl_avail</code>, so <code class="literal">NEW.sl_avail</code> will get
 297     replaced by <code class="literal">shoelace_data.sl_avail</code>.  Thus, the extra
 298     command generated by the rule is:
 299
 300 </p><pre class="programlisting">
 301 INSERT INTO shoelace_log VALUES (
 302        shoelace_data.sl_name, <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span>,
 303        current_user, current_timestamp )
 304   FROM shoelace_data
 305  WHERE <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span> &lt;&gt; shoelace_data.sl_avail
 306    AND shoelace_data.sl_name = 'sl7';
 307 </pre><p>
 308
 309     and that qualification will never be true.
 310    </p><p>
 311     It will also work if the original query modifies multiple rows. So
 312     if someone issued the command:
 313
 314 </p><pre class="programlisting">
 315 UPDATE shoelace_data SET sl_avail = 0
 316  WHERE sl_color = 'black';
 317 </pre><p>
 318
 319     four rows in fact get updated (<code class="literal">sl1</code>, <code class="literal">sl2</code>, <code class="literal">sl3</code>, and <code class="literal">sl4</code>).
 320     But <code class="literal">sl3</code> already has <code class="literal">sl_avail = 0</code>.   In this case, the original
 321     query trees qualification is different and that results
 322     in the extra query tree:
 323
 324 </p><pre class="programlisting">
 325 INSERT INTO shoelace_log
 326 SELECT shoelace_data.sl_name, 0,
 327        current_user, current_timestamp
 328   FROM shoelace_data
 329  WHERE 0 &lt;&gt; shoelace_data.sl_avail
 330    AND <span class="emphasis"><strong>shoelace_data.sl_color = 'black'</strong></span>;
 331 </pre><p>
 332
 333     being generated by the rule.  This query tree will surely insert
 334     three new log entries. And that's absolutely correct.
 335 </p><p>
 336     Here we can see why it is important that the original query tree
 337     is executed last.  If the <code class="command">UPDATE</code> had been
 338     executed first, all the rows would have already been set to zero, so the
 339     logging <code class="command">INSERT</code> would not find any row where
 340     <code class="literal">0 &lt;&gt; shoelace_data.sl_avail</code>.
 341 </p></div></div><div class="sect2" id="RULES-UPDATE-VIEWS"><div class="titlepage"><div><div><h3 class="title">39.4.2. Cooperation with Views <a href="#RULES-UPDATE-VIEWS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.6.9.8.2" class="indexterm"></a><p>
 342     A simple way to protect view relations from the mentioned
 343     possibility that someone can try to run <code class="command">INSERT</code>,
 344     <code class="command">UPDATE</code>, or <code class="command">DELETE</code> on them is
 345     to let those query trees get thrown away.  So we could create the rules:
 346
 347 </p><pre class="programlisting">
 348 CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
 349     DO INSTEAD NOTHING;
 350 CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
 351     DO INSTEAD NOTHING;
 352 CREATE RULE shoe_del_protect AS ON DELETE TO shoe
 353     DO INSTEAD NOTHING;
 354 </pre><p>
 355
 356     If someone now tries to do any of these operations on the view
 357     relation <code class="literal">shoe</code>, the rule system will
 358     apply these rules. Since the rules have
 359     no actions and are <code class="literal">INSTEAD</code>, the resulting list of
 360     query trees will be empty and the whole query will become
 361     nothing because there is nothing left to be optimized or
 362     executed after the rule system is done with it.
 363 </p><p>
 364     A more sophisticated way to use the rule system is to
 365     create rules that rewrite the query tree into one that
 366     does the right operation on the real tables. To do that
 367     on the <code class="literal">shoelace</code> view, we create
 368     the following rules:
 369
 370 </p><pre class="programlisting">
 371 CREATE RULE shoelace_ins AS ON INSERT TO shoelace
 372     DO INSTEAD
 373     INSERT INTO shoelace_data VALUES (
 374            NEW.sl_name,
 375            NEW.sl_avail,
 376            NEW.sl_color,
 377            NEW.sl_len,
 378            NEW.sl_unit
 379     );
 380
 381 CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
 382     DO INSTEAD
 383     UPDATE shoelace_data
 384        SET sl_name = NEW.sl_name,
 385            sl_avail = NEW.sl_avail,
 386            sl_color = NEW.sl_color,
 387            sl_len = NEW.sl_len,
 388            sl_unit = NEW.sl_unit
 389      WHERE sl_name = OLD.sl_name;
 390
 391 CREATE RULE shoelace_del AS ON DELETE TO shoelace
 392     DO INSTEAD
 393     DELETE FROM shoelace_data
 394      WHERE sl_name = OLD.sl_name;
 395 </pre><p>
 396    </p><p>
 397     If you want to support <code class="literal">RETURNING</code> queries on the view,
 398     you need to make the rules include <code class="literal">RETURNING</code> clauses that
 399     compute the view rows.  This is usually pretty trivial for views on a
 400     single table, but it's a bit tedious for join views such as
 401     <code class="literal">shoelace</code>.  An example for the insert case is:
 402
 403 </p><pre class="programlisting">
 404 CREATE RULE shoelace_ins AS ON INSERT TO shoelace
 405     DO INSTEAD
 406     INSERT INTO shoelace_data VALUES (
 407            NEW.sl_name,
 408            NEW.sl_avail,
 409            NEW.sl_color,
 410            NEW.sl_len,
 411            NEW.sl_unit
 412     )
 413     RETURNING
 414            shoelace_data.*,
 415            (SELECT shoelace_data.sl_len * u.un_fact
 416             FROM unit u WHERE shoelace_data.sl_unit = u.un_name);
 417 </pre><p>
 418
 419     Note that this one rule supports both <code class="command">INSERT</code> and
 420     <code class="command">INSERT RETURNING</code> queries on the view — the
 421     <code class="literal">RETURNING</code> clause is simply ignored for <code class="command">INSERT</code>.
 422    </p><p>
 423     Note that in the <code class="literal">RETURNING</code> clause of a rule,
 424     <code class="literal">OLD</code> and <code class="literal">NEW</code> refer to the
 425     pseudorelations added as extra range table entries to the rewritten
 426     query, rather than old/new rows in the result relation.  Thus, for
 427     example, in a rule supporting <code class="command">UPDATE</code> queries on this
 428     view, if the <code class="literal">RETURNING</code> clause contained
 429     <code class="literal">old.sl_name</code>, the old name would always be returned,
 430     regardless of whether the <code class="literal">RETURNING</code> clause in the
 431     query on the view specified <code class="literal">OLD</code> or <code class="literal">NEW</code>,
 432     which might be confusing.  To avoid this confusion, and support returning
 433     old and new values in queries on the view, the <code class="literal">RETURNING</code>
 434     clause in the rule definition should refer to entries from the result
 435     relation such as <code class="literal">shoelace_data.sl_name</code>, without
 436     specifying <code class="literal">OLD</code> or <code class="literal">NEW</code>.
 437    </p><p>
 438     Now assume that once in a while, a pack of shoelaces arrives at
 439     the shop and a big parts list along with it.  But you don't want
 440     to manually update the <code class="literal">shoelace</code> view every
 441     time.  Instead we set up two little tables: one where you can
 442     insert the items from the part list, and one with a special
 443     trick. The creation commands for these are:
 444
 445 </p><pre class="programlisting">
 446 CREATE TABLE shoelace_arrive (
 447     arr_name    text,
 448     arr_quant   integer
 449 );
 450
 451 CREATE TABLE shoelace_ok (
 452     ok_name     text,
 453     ok_quant    integer
 454 );
 455
 456 CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
 457     DO INSTEAD
 458     UPDATE shoelace
 459        SET sl_avail = sl_avail + NEW.ok_quant
 460      WHERE sl_name = NEW.ok_name;
 461 </pre><p>
 462
 463     Now you can fill the table <code class="literal">shoelace_arrive</code> with
 464     the data from the parts list:
 465
 466 </p><pre class="programlisting">
 467 SELECT * FROM shoelace_arrive;
 468
 469  arr_name | arr_quant
 470 ----------+-----------
 471  sl3      |        10
 472  sl6      |        20
 473  sl8      |        20
 474 (3 rows)
 475 </pre><p>
 476
 477     Take a quick look at the current data:
 478
 479 </p><pre class="programlisting">
 480 SELECT * FROM shoelace;
 481
 482  sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
 483 ----------+----------+----------+--------+---------+-----------
 484  sl1      |        5 | black    |     80 | cm      |        80
 485  sl2      |        6 | black    |    100 | cm      |       100
 486  sl7      |        6 | brown    |     60 | cm      |        60
 487  sl3      |        0 | black    |     35 | inch    |      88.9
 488  sl4      |        8 | black    |     40 | inch    |     101.6
 489  sl8      |        1 | brown    |     40 | inch    |     101.6
 490  sl5      |        4 | brown    |      1 | m       |       100
 491  sl6      |        0 | brown    |    0.9 | m       |        90
 492 (8 rows)
 493 </pre><p>
 494
 495     Now move the arrived shoelaces in:
 496
 497 </p><pre class="programlisting">
 498 INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
 499 </pre><p>
 500
 501     and check the results:
 502
 503 </p><pre class="programlisting">
 504 SELECT * FROM shoelace ORDER BY sl_name;
 505
 506  sl_name  | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
 507 ----------+----------+----------+--------+---------+-----------
 508  sl1      |        5 | black    |     80 | cm      |        80
 509  sl2      |        6 | black    |    100 | cm      |       100
 510  sl7      |        6 | brown    |     60 | cm      |        60
 511  sl4      |        8 | black    |     40 | inch    |     101.6
 512  sl3      |       10 | black    |     35 | inch    |      88.9
 513  sl8      |       21 | brown    |     40 | inch    |     101.6
 514  sl5      |        4 | brown    |      1 | m       |       100
 515  sl6      |       20 | brown    |    0.9 | m       |        90
 516 (8 rows)
 517
 518 SELECT * FROM shoelace_log;
 519
 520  sl_name | sl_avail | log_who| log_when
 521 ---------+----------+--------+----------------------------------
 522  sl7     |        6 | Al     | Tue Oct 20 19:14:45 1998 MET DST
 523  sl3     |       10 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 524  sl6     |       20 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 525  sl8     |       21 | Al     | Tue Oct 20 19:25:16 1998 MET DST
 526 (4 rows)
 527 </pre><p>
 528    </p><p>
 529     It's a long way from the one <code class="literal">INSERT ... SELECT</code>
 530     to these results. And the description of the query-tree
 531     transformation will be the last in this chapter.  First, there is
 532     the parser's output:
 533
 534 </p><pre class="programlisting">
 535 INSERT INTO shoelace_ok
 536 SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant
 537   FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;
 538 </pre><p>
 539
 540     Now the first rule <code class="literal">shoelace_ok_ins</code> is applied and turns this
 541     into:
 542
 543 </p><pre class="programlisting">
 544 UPDATE shoelace
 545    SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant
 546   FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
 547        shoelace_ok old, shoelace_ok new,
 548        shoelace shoelace
 549  WHERE shoelace.sl_name = shoelace_arrive.arr_name;
 550 </pre><p>
 551
 552     and throws away the original <code class="command">INSERT</code> on
 553     <code class="literal">shoelace_ok</code>.  This rewritten query is passed to
 554     the rule system again, and the second applied rule
 555     <code class="literal">shoelace_upd</code> produces:
 556
 557 </p><pre class="programlisting">
 558 UPDATE shoelace_data
 559    SET sl_name = shoelace.sl_name,
 560        sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,
 561        sl_color = shoelace.sl_color,
 562        sl_len = shoelace.sl_len,
 563        sl_unit = shoelace.sl_unit
 564   FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
 565        shoelace_ok old, shoelace_ok new,
 566        shoelace shoelace, shoelace old,
 567        shoelace new, shoelace_data shoelace_data
 568  WHERE shoelace.sl_name = shoelace_arrive.arr_name
 569    AND shoelace_data.sl_name = shoelace.sl_name;
 570 </pre><p>
 571
 572     Again it's an <code class="literal">INSTEAD</code> rule and the previous query tree is trashed.
 573     Note that this query still uses the view <code class="literal">shoelace</code>.
 574     But the rule system isn't finished with this step, so it continues
 575     and applies the <code class="literal">_RETURN</code> rule on it, and we get:
 576
 577 </p><pre class="programlisting">
 578 UPDATE shoelace_data
 579    SET sl_name = s.sl_name,
 580        sl_avail = s.sl_avail + shoelace_arrive.arr_quant,
 581        sl_color = s.sl_color,
 582        sl_len = s.sl_len,
 583        sl_unit = s.sl_unit
 584   FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
 585        shoelace_ok old, shoelace_ok new,
 586        shoelace shoelace, shoelace old,
 587        shoelace new, shoelace_data shoelace_data,
 588        shoelace old, shoelace new,
 589        shoelace_data s, unit u
 590  WHERE s.sl_name = shoelace_arrive.arr_name
 591    AND shoelace_data.sl_name = s.sl_name;
 592 </pre><p>
 593
 594     Finally, the rule <code class="literal">log_shoelace</code> gets applied,
 595     producing the extra query tree:
 596
 597 </p><pre class="programlisting">
 598 INSERT INTO shoelace_log
 599 SELECT s.sl_name,
 600        s.sl_avail + shoelace_arrive.arr_quant,
 601        current_user,
 602        current_timestamp
 603   FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
 604        shoelace_ok old, shoelace_ok new,
 605        shoelace shoelace, shoelace old,
 606        shoelace new, shoelace_data shoelace_data,
 607        shoelace old, shoelace new,
 608        shoelace_data s, unit u,
 609        shoelace_data old, shoelace_data new
 610        shoelace_log shoelace_log
 611  WHERE s.sl_name = shoelace_arrive.arr_name
 612    AND shoelace_data.sl_name = s.sl_name
 613    AND (s.sl_avail + shoelace_arrive.arr_quant) &lt;&gt; s.sl_avail;
 614 </pre><p>
 615
 616     After that the rule system runs out of rules and returns the
 617     generated query trees.
 618    </p><p>
 619     So we end up with two final query trees that are equivalent to the
 620     <acronym class="acronym">SQL</acronym> statements:
 621
 622 </p><pre class="programlisting">
 623 INSERT INTO shoelace_log
 624 SELECT s.sl_name,
 625        s.sl_avail + shoelace_arrive.arr_quant,
 626        current_user,
 627        current_timestamp
 628   FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,
 629        shoelace_data s
 630  WHERE s.sl_name = shoelace_arrive.arr_name
 631    AND shoelace_data.sl_name = s.sl_name
 632    AND s.sl_avail + shoelace_arrive.arr_quant &lt;&gt; s.sl_avail;
 633
 634 UPDATE shoelace_data
 635    SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant
 636   FROM shoelace_arrive shoelace_arrive,
 637        shoelace_data shoelace_data,
 638        shoelace_data s
 639  WHERE s.sl_name = shoelace_arrive.sl_name
 640    AND shoelace_data.sl_name = s.sl_name;
 641 </pre><p>
 642
 643     The result is that data coming from one relation inserted into another,
 644     changed into updates on a third, changed into updating
 645     a fourth plus logging that final update in a fifth
 646     gets reduced into two queries.
 647 </p><p>
 648     There is a little detail that's a bit ugly. Looking at the two
 649     queries, it turns out that the <code class="literal">shoelace_data</code>
 650     relation appears twice in the range table where it could
 651     definitely be reduced to one. The planner does not handle it and
 652     so the execution plan for the rule systems output of the
 653     <code class="command">INSERT</code> will be
 654
 655 </p><pre class="literallayout">
 656 Nested Loop
 657   -&gt;  Merge Join
 658         -&gt;  Seq Scan
 659               -&gt;  Sort
 660                     -&gt;  Seq Scan on s
 661         -&gt;  Seq Scan
 662               -&gt;  Sort
 663                     -&gt;  Seq Scan on shoelace_arrive
 664   -&gt;  Seq Scan on shoelace_data
 665 </pre><p>
 666
 667     while omitting the extra range table entry would result in a
 668
 669 </p><pre class="literallayout">
 670 Merge Join
 671   -&gt;  Seq Scan
 672         -&gt;  Sort
 673               -&gt;  Seq Scan on s
 674   -&gt;  Seq Scan
 675         -&gt;  Sort
 676               -&gt;  Seq Scan on shoelace_arrive
 677 </pre><p>
 678
 679     which produces exactly the same entries in the log table.  Thus,
 680     the rule system caused one extra scan on the table
 681     <code class="literal">shoelace_data</code> that is absolutely not
 682     necessary. And the same redundant scan is done once more in the
 683     <code class="command">UPDATE</code>. But it was a really hard job to make
 684     that all possible at all.
 685 </p><p>
 686     Now we make a final demonstration of the
 687     <span class="productname">PostgreSQL</span> rule system and its power.
 688     Say you add some shoelaces with extraordinary colors to your
 689     database:
 690
 691 </p><pre class="programlisting">
 692 INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
 693 INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
 694 </pre><p>
 695
 696     We would like to make a view to check which
 697     <code class="literal">shoelace</code> entries do not fit any shoe in color.
 698     The view for this is:
 699
 700 </p><pre class="programlisting">
 701 CREATE VIEW shoelace_mismatch AS
 702     SELECT * FROM shoelace WHERE NOT EXISTS
 703         (SELECT shoename FROM shoe WHERE slcolor = sl_color);
 704 </pre><p>
 705
 706     Its output is:
 707
 708 </p><pre class="programlisting">
 709 SELECT * FROM shoelace_mismatch;
 710
 711  sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
 712 ---------+----------+----------+--------+---------+-----------
 713  sl9     |        0 | pink     |     35 | inch    |      88.9
 714  sl10    |     1000 | magenta  |     40 | inch    |     101.6
 715 </pre><p>
 716    </p><p>
 717     Now we want to set it up so that mismatching shoelaces that are
 718     not in stock are deleted from the database.
 719     To make it a little harder for <span class="productname">PostgreSQL</span>,
 720     we don't delete it directly. Instead we create one more view:
 721
 722 </p><pre class="programlisting">
 723 CREATE VIEW shoelace_can_delete AS
 724     SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;
 725 </pre><p>
 726
 727     and do it this way:
 728
 729 </p><pre class="programlisting">
 730 DELETE FROM shoelace WHERE EXISTS
 731     (SELECT * FROM shoelace_can_delete
 732              WHERE sl_name = shoelace.sl_name);
 733 </pre><p>
 734
 735     The results are:
 736
 737 </p><pre class="programlisting">
 738 SELECT * FROM shoelace;
 739
 740  sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
 741 ---------+----------+----------+--------+---------+-----------
 742  sl1     |        5 | black    |     80 | cm      |        80
 743  sl2     |        6 | black    |    100 | cm      |       100
 744  sl7     |        6 | brown    |     60 | cm      |        60
 745  sl4     |        8 | black    |     40 | inch    |     101.6
 746  sl3     |       10 | black    |     35 | inch    |      88.9
 747  sl8     |       21 | brown    |     40 | inch    |     101.6
 748  sl10    |     1000 | magenta  |     40 | inch    |     101.6
 749  sl5     |        4 | brown    |      1 | m       |       100
 750  sl6     |       20 | brown    |    0.9 | m       |        90
 751 (9 rows)
 752 </pre><p>
 753    </p><p>
 754     A <code class="command">DELETE</code> on a view, with a subquery qualification that
 755     in total uses 4 nesting/joined views, where one of them
 756     itself has a subquery qualification containing a view
 757     and where calculated view columns are used,
 758     gets rewritten into
 759     one single query tree that deletes the requested data
 760     from a real table.
 761 </p><p>
 762     There are probably only a few situations out in the real world
 763     where such a construct is necessary. But it makes you feel
 764     comfortable that it works.
 765 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules-materializedviews.html" title="39.3. Materialized Views">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 39. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-privileges.html" title="39.5. Rules and Privileges">Next</a></td></tr><tr><td width="40%" align="left" valign="top">39.3. Materialized Views </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 39.5. Rules and Privileges</td></tr></table></div></body></html>