codekingpro/portable-devtools
115k
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>41.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="41.3. Materialized Views" /><link rel="next" href="rules-privileges.html" title="41.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">41.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="41.3. Materialized Views">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><th width="60%" align="center">Chapter 41. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-privileges.html" title="41.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">41.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">41.4.1. How Update Rules Work</a></span></dt><dt><span class="sect2"><a href="rules-update.html#RULES-UPDATE-VIEWS">41.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 rules5 described in the previous sections. First, their <code class="command">CREATE6 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 they21 create zero or more new query trees and can throw away the22 original one.23</p><div class="caution"><h3 class="title">Caution</h3><p>24 In many cases, tasks that could be performed by rules25 on <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> are better done26 with triggers. Triggers are notationally a bit more complicated, but their27 semantics are much simpler to understand. Rules tend to have surprising28 results when the original query contains volatile functions: volatile29 functions may get executed more times than expected in the process of30 carrying out the rules.31 </p><p>32 Also, there are some cases that are not supported by these types of rules at33 all, notably including <code class="literal">WITH</code> clauses in the original query and34 multiple-assignment sub-<code class="literal">SELECT</code>s in the <code class="literal">SET</code> list35 of <code class="command">UPDATE</code> queries. This is because copying these constructs36 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">41.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">42CREATE [ 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 defined49 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 result52 relation and the command type of a query tree are equal to the53 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 rule58 can have a qualification or not and it can be <code class="literal">INSTEAD</code> or59 <code class="literal">ALSO</code> (the default).60</p><p>61 What is a rule qualification? It is a restriction that tells62 when the actions of the rule should be done and when not. This63 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 a65 special meaning).66</p><p>67 So we have three cases that produce the following query trees for68 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> or71 <code class="literal">INSTEAD</code></span></dt><dd><p>72 the query tree from the rule action with the original query73 tree's qualification added74 </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 rule76 qualification and the original query tree's qualification77 added78 </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 rule80 qualification and the original query tree's qualification; and81 the original query tree with the negated rule qualification82 added83 </p></dd></dl></div><p>84 85 Finally, if the rule is <code class="literal">ALSO</code>, the unchanged original query tree is86 added to the list. Since only qualified <code class="literal">INSTEAD</code> rules already add the87 original query tree, we end up with either one or two output query trees88 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 to92 see the inserted row(s). But for <code class="literal">ON UPDATE</code> and <code class="literal">ON93 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-deleted95 rows; otherwise, the actions might do nothing because they find no rows96 matching their qualifications.97</p><p>98 The query trees generated from rule actions are thrown into the99 rewrite system again, and maybe more rules get applied resulting100 in additional or fewer query trees.101 So a rule's actions must have either a different102 command type or a different result relation than the rule itself is103 on, otherwise this recursive process will end up in an infinite loop.104 (Recursive expansion of a rule will be detected and reported as an105 error.)106</p><p>107 The query trees found in the actions of the108 <code class="structname">pg_rewrite</code> system catalog are only109 templates. Since they can reference the range-table entries for110 <code class="literal">NEW</code> and <code class="literal">OLD</code>, some substitutions have to be made before they can be111 used. For any reference to <code class="literal">NEW</code>, the target list of the original112 query is searched for a corresponding entry. If found, that113 entry's expression replaces the reference. Otherwise, <code class="literal">NEW</code> means the114 same as <code class="literal">OLD</code> (for an <code class="command">UPDATE</code>) or is replaced by115 a null value (for an <code class="command">INSERT</code>). Any reference to <code class="literal">OLD</code> is116 replaced by a reference to the range-table entry that is the117 result relation.118</p><p>119 After the system is done applying update rules, it applies view rules to the120 produced query tree(s). Views cannot insert new update actions so121 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">41.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 the124 <code class="literal">shoelace_data</code> relation. So we set up a log table125 and a rule that conditionally writes a log entry when an126 <code class="command">UPDATE</code> is performed on127 <code class="literal">shoelace_data</code>.128 129</p><pre class="programlisting">130CREATE TABLE shoelace_log (131 sl_name text, -- shoelace changed132 sl_avail integer, -- new available value133 log_who text, -- who did it134 log_when timestamp -- when135);136 137CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data138 WHERE NEW.sl_avail <> OLD.sl_avail139 DO INSERT INTO shoelace_log VALUES (140 NEW.sl_name,141 NEW.sl_avail,142 current_user,143 current_timestamp144 );145</pre><p>146</p><p>147 Now someone does:148 149</p><pre class="programlisting">150UPDATE 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">156SELECT * FROM shoelace_log;157 158 sl_name | sl_avail | log_who | log_when159---------+----------+---------+----------------------------------160 sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST161(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">168UPDATE shoelace_data SET sl_avail = 6169 FROM shoelace_data shoelace_data170 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 rule174 qualification expression:175 176</p><pre class="programlisting">177NEW.sl_avail <> OLD.sl_avail178</pre><p>179 180 and the action:181 182</p><pre class="programlisting">183INSERT 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 write190 <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 entries192 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 in194 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 system197 has to return two query trees: the modified rule action and the original198 query tree. In step 1, the range table of the original query is199 incorporated into the rule's action query tree. This results in:200 201</p><pre class="programlisting">202INSERT 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 set210 is restricted to rows where <code class="literal">sl_avail</code> changes:211 212</p><pre class="programlisting">213INSERT 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_data218 <span class="emphasis"><strong>WHERE new.sl_avail <> old.sl_avail</strong></span>;219</pre><p>220 221 (This looks even stranger, since <code class="literal">INSERT ... VALUES</code> doesn't have222 a <code class="literal">WHERE</code> clause either, but the planner and executor will have no223 difficulty with it. They need to support this same functionality224 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 touched228 by the original query:229 230</p><pre class="programlisting">231INSERT 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_data236 WHERE new.sl_avail <> old.sl_avail237 <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 the241 original query tree or by the matching variable references242 from the result relation:243 244</p><pre class="programlisting">245INSERT 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_data250 WHERE <span class="emphasis"><strong>6</strong></span> <> old.sl_avail251 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">258INSERT 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_data263 WHERE 6 <> <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 the268 original query tree. In short, the output from the rule system269 is a list of two query trees that correspond to these statements:270 271</p><pre class="programlisting">272INSERT INTO shoelace_log VALUES (273 shoelace_data.sl_name, 6,274 current_user, current_timestamp )275 FROM shoelace_data276 WHERE 6 <> shoelace_data.sl_avail277 AND shoelace_data.sl_name = 'sl7';278 279UPDATE shoelace_data SET sl_avail = 6280 WHERE sl_name = 'sl7';281</pre><p>282 283 These are executed in this order, and that is exactly what284 the rule was meant to do.285 </p><p>286 The substitutions and the added qualifications287 ensure that, if the original query would be, say:288 289</p><pre class="programlisting">290UPDATE 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 query295 tree does not contain a target list entry for296 <code class="literal">sl_avail</code>, so <code class="literal">NEW.sl_avail</code> will get297 replaced by <code class="literal">shoelace_data.sl_avail</code>. Thus, the extra298 command generated by the rule is:299 300</p><pre class="programlisting">301INSERT 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_data305 WHERE <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span> <> shoelace_data.sl_avail306 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. So312 if someone issued the command:313 314</p><pre class="programlisting">315UPDATE shoelace_data SET sl_avail = 0316 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 original321 query trees qualification is different and that results322 in the extra query tree:323 324</p><pre class="programlisting">325INSERT INTO shoelace_log326SELECT shoelace_data.sl_name, 0,327 current_user, current_timestamp328 FROM shoelace_data329 WHERE 0 <> shoelace_data.sl_avail330 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 insert334 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 tree337 is executed last. If the <code class="command">UPDATE</code> had been338 executed first, all the rows would have already been set to zero, so the339 logging <code class="command">INSERT</code> would not find any row where340 <code class="literal">0 <> shoelace_data.sl_avail</code>.341</p></div></div><div class="sect2" id="RULES-UPDATE-VIEWS"><div class="titlepage"><div><div><h3 class="title">41.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 mentioned343 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 is345 to let those query trees get thrown away. So we could create the rules:346 347</p><pre class="programlisting">348CREATE RULE shoe_ins_protect AS ON INSERT TO shoe349 DO INSTEAD NOTHING;350CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe351 DO INSTEAD NOTHING;352CREATE RULE shoe_del_protect AS ON DELETE TO shoe353 DO INSTEAD NOTHING;354</pre><p>355 356 If someone now tries to do any of these operations on the view357 relation <code class="literal">shoe</code>, the rule system will358 apply these rules. Since the rules have359 no actions and are <code class="literal">INSTEAD</code>, the resulting list of360 query trees will be empty and the whole query will become361 nothing because there is nothing left to be optimized or362 executed after the rule system is done with it.363</p><p>364 A more sophisticated way to use the rule system is to365 create rules that rewrite the query tree into one that366 does the right operation on the real tables. To do that367 on the <code class="literal">shoelace</code> view, we create368 the following rules:369 370</p><pre class="programlisting">371CREATE RULE shoelace_ins AS ON INSERT TO shoelace372 DO INSTEAD373 INSERT INTO shoelace_data VALUES (374 NEW.sl_name,375 NEW.sl_avail,376 NEW.sl_color,377 NEW.sl_len,378 NEW.sl_unit379 );380 381CREATE RULE shoelace_upd AS ON UPDATE TO shoelace382 DO INSTEAD383 UPDATE shoelace_data384 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_unit389 WHERE sl_name = OLD.sl_name;390 391CREATE RULE shoelace_del AS ON DELETE TO shoelace392 DO INSTEAD393 DELETE FROM shoelace_data394 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 that399 compute the view rows. This is usually pretty trivial for views on a400 single table, but it's a bit tedious for join views such as401 <code class="literal">shoelace</code>. An example for the insert case is:402 403</p><pre class="programlisting">404CREATE RULE shoelace_ins AS ON INSERT TO shoelace405 DO INSTEAD406 INSERT INTO shoelace_data VALUES (407 NEW.sl_name,408 NEW.sl_avail,409 NEW.sl_color,410 NEW.sl_len,411 NEW.sl_unit412 )413 RETURNING414 shoelace_data.*,415 (SELECT shoelace_data.sl_len * u.un_fact416 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> and420 <code class="command">INSERT RETURNING</code> queries on the view — the421 <code class="literal">RETURNING</code> clause is simply ignored for <code class="command">INSERT</code>.422 </p><p>423 Now assume that once in a while, a pack of shoelaces arrives at424 the shop and a big parts list along with it. But you don't want425 to manually update the <code class="literal">shoelace</code> view every426 time. Instead we set up two little tables: one where you can427 insert the items from the part list, and one with a special428 trick. The creation commands for these are:429 430</p><pre class="programlisting">431CREATE TABLE shoelace_arrive (432 arr_name text,433 arr_quant integer434);435 436CREATE TABLE shoelace_ok (437 ok_name text,438 ok_quant integer439);440 441CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok442 DO INSTEAD443 UPDATE shoelace444 SET sl_avail = sl_avail + NEW.ok_quant445 WHERE sl_name = NEW.ok_name;446</pre><p>447 448 Now you can fill the table <code class="literal">shoelace_arrive</code> with449 the data from the parts list:450 451</p><pre class="programlisting">452SELECT * FROM shoelace_arrive;453 454 arr_name | arr_quant455----------+-----------456 sl3 | 10457 sl6 | 20458 sl8 | 20459(3 rows)460</pre><p>461 462 Take a quick look at the current data:463 464</p><pre class="programlisting">465SELECT * FROM shoelace;466 467 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm468----------+----------+----------+--------+---------+-----------469 sl1 | 5 | black | 80 | cm | 80470 sl2 | 6 | black | 100 | cm | 100471 sl7 | 6 | brown | 60 | cm | 60472 sl3 | 0 | black | 35 | inch | 88.9473 sl4 | 8 | black | 40 | inch | 101.6474 sl8 | 1 | brown | 40 | inch | 101.6475 sl5 | 4 | brown | 1 | m | 100476 sl6 | 0 | brown | 0.9 | m | 90477(8 rows)478</pre><p>479 480 Now move the arrived shoelaces in:481 482</p><pre class="programlisting">483INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;484</pre><p>485 486 and check the results:487 488</p><pre class="programlisting">489SELECT * FROM shoelace ORDER BY sl_name;490 491 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm492----------+----------+----------+--------+---------+-----------493 sl1 | 5 | black | 80 | cm | 80494 sl2 | 6 | black | 100 | cm | 100495 sl7 | 6 | brown | 60 | cm | 60496 sl4 | 8 | black | 40 | inch | 101.6497 sl3 | 10 | black | 35 | inch | 88.9498 sl8 | 21 | brown | 40 | inch | 101.6499 sl5 | 4 | brown | 1 | m | 100500 sl6 | 20 | brown | 0.9 | m | 90501(8 rows)502 503SELECT * FROM shoelace_log;504 505 sl_name | sl_avail | log_who| log_when506---------+----------+--------+----------------------------------507 sl7 | 6 | Al | Tue Oct 20 19:14:45 1998 MET DST508 sl3 | 10 | Al | Tue Oct 20 19:25:16 1998 MET DST509 sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST510 sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST511(4 rows)512</pre><p>513 </p><p>514 It's a long way from the one <code class="literal">INSERT ... SELECT</code>515 to these results. And the description of the query-tree516 transformation will be the last in this chapter. First, there is517 the parser's output:518 519</p><pre class="programlisting">520INSERT INTO shoelace_ok521SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant522 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;523</pre><p>524 525 Now the first rule <code class="literal">shoelace_ok_ins</code> is applied and turns this526 into:527 528</p><pre class="programlisting">529UPDATE shoelace530 SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant531 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,532 shoelace_ok old, shoelace_ok new,533 shoelace shoelace534 WHERE shoelace.sl_name = shoelace_arrive.arr_name;535</pre><p>536 537 and throws away the original <code class="command">INSERT</code> on538 <code class="literal">shoelace_ok</code>. This rewritten query is passed to539 the rule system again, and the second applied rule540 <code class="literal">shoelace_upd</code> produces:541 542</p><pre class="programlisting">543UPDATE shoelace_data544 SET sl_name = shoelace.sl_name,545 sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,546 sl_color = shoelace.sl_color,547 sl_len = shoelace.sl_len,548 sl_unit = shoelace.sl_unit549 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,550 shoelace_ok old, shoelace_ok new,551 shoelace shoelace, shoelace old,552 shoelace new, shoelace_data shoelace_data553 WHERE shoelace.sl_name = shoelace_arrive.arr_name554 AND shoelace_data.sl_name = shoelace.sl_name;555</pre><p>556 557 Again it's an <code class="literal">INSTEAD</code> rule and the previous query tree is trashed.558 Note that this query still uses the view <code class="literal">shoelace</code>.559 But the rule system isn't finished with this step, so it continues560 and applies the <code class="literal">_RETURN</code> rule on it, and we get:561 562</p><pre class="programlisting">563UPDATE shoelace_data564 SET sl_name = s.sl_name,565 sl_avail = s.sl_avail + shoelace_arrive.arr_quant,566 sl_color = s.sl_color,567 sl_len = s.sl_len,568 sl_unit = s.sl_unit569 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,570 shoelace_ok old, shoelace_ok new,571 shoelace shoelace, shoelace old,572 shoelace new, shoelace_data shoelace_data,573 shoelace old, shoelace new,574 shoelace_data s, unit u575 WHERE s.sl_name = shoelace_arrive.arr_name576 AND shoelace_data.sl_name = s.sl_name;577</pre><p>578 579 Finally, the rule <code class="literal">log_shoelace</code> gets applied,580 producing the extra query tree:581 582</p><pre class="programlisting">583INSERT INTO shoelace_log584SELECT s.sl_name,585 s.sl_avail + shoelace_arrive.arr_quant,586 current_user,587 current_timestamp588 FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,589 shoelace_ok old, shoelace_ok new,590 shoelace shoelace, shoelace old,591 shoelace new, shoelace_data shoelace_data,592 shoelace old, shoelace new,593 shoelace_data s, unit u,594 shoelace_data old, shoelace_data new595 shoelace_log shoelace_log596 WHERE s.sl_name = shoelace_arrive.arr_name597 AND shoelace_data.sl_name = s.sl_name598 AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail;599</pre><p>600 601 After that the rule system runs out of rules and returns the602 generated query trees.603 </p><p>604 So we end up with two final query trees that are equivalent to the605 <acronym class="acronym">SQL</acronym> statements:606 607</p><pre class="programlisting">608INSERT INTO shoelace_log609SELECT s.sl_name,610 s.sl_avail + shoelace_arrive.arr_quant,611 current_user,612 current_timestamp613 FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,614 shoelace_data s615 WHERE s.sl_name = shoelace_arrive.arr_name616 AND shoelace_data.sl_name = s.sl_name617 AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail;618 619UPDATE shoelace_data620 SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant621 FROM shoelace_arrive shoelace_arrive,622 shoelace_data shoelace_data,623 shoelace_data s624 WHERE s.sl_name = shoelace_arrive.sl_name625 AND shoelace_data.sl_name = s.sl_name;626</pre><p>627 628 The result is that data coming from one relation inserted into another,629 changed into updates on a third, changed into updating630 a fourth plus logging that final update in a fifth631 gets reduced into two queries.632</p><p>633 There is a little detail that's a bit ugly. Looking at the two634 queries, it turns out that the <code class="literal">shoelace_data</code>635 relation appears twice in the range table where it could636 definitely be reduced to one. The planner does not handle it and637 so the execution plan for the rule systems output of the638 <code class="command">INSERT</code> will be639 640</p><pre class="literallayout">641Nested Loop642 -> Merge Join643 -> Seq Scan644 -> Sort645 -> Seq Scan on s646 -> Seq Scan647 -> Sort648 -> Seq Scan on shoelace_arrive649 -> Seq Scan on shoelace_data650</pre><p>651 652 while omitting the extra range table entry would result in a653 654</p><pre class="literallayout">655Merge Join656 -> Seq Scan657 -> Sort658 -> Seq Scan on s659 -> Seq Scan660 -> Sort661 -> Seq Scan on shoelace_arrive662</pre><p>663 664 which produces exactly the same entries in the log table. Thus,665 the rule system caused one extra scan on the table666 <code class="literal">shoelace_data</code> that is absolutely not667 necessary. And the same redundant scan is done once more in the668 <code class="command">UPDATE</code>. But it was a really hard job to make669 that all possible at all.670</p><p>671 Now we make a final demonstration of the672 <span class="productname">PostgreSQL</span> rule system and its power.673 Say you add some shoelaces with extraordinary colors to your674 database:675 676</p><pre class="programlisting">677INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);678INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);679</pre><p>680 681 We would like to make a view to check which682 <code class="literal">shoelace</code> entries do not fit any shoe in color.683 The view for this is:684 685</p><pre class="programlisting">686CREATE VIEW shoelace_mismatch AS687 SELECT * FROM shoelace WHERE NOT EXISTS688 (SELECT shoename FROM shoe WHERE slcolor = sl_color);689</pre><p>690 691 Its output is:692 693</p><pre class="programlisting">694SELECT * FROM shoelace_mismatch;695 696 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm697---------+----------+----------+--------+---------+-----------698 sl9 | 0 | pink | 35 | inch | 88.9699 sl10 | 1000 | magenta | 40 | inch | 101.6700</pre><p>701 </p><p>702 Now we want to set it up so that mismatching shoelaces that are703 not in stock are deleted from the database.704 To make it a little harder for <span class="productname">PostgreSQL</span>,705 we don't delete it directly. Instead we create one more view:706 707</p><pre class="programlisting">708CREATE VIEW shoelace_can_delete AS709 SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;710</pre><p>711 712 and do it this way:713 714</p><pre class="programlisting">715DELETE FROM shoelace WHERE EXISTS716 (SELECT * FROM shoelace_can_delete717 WHERE sl_name = shoelace.sl_name);718</pre><p>719 720 The results are:721 722</p><pre class="programlisting">723SELECT * FROM shoelace;724 725 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm726---------+----------+----------+--------+---------+-----------727 sl1 | 5 | black | 80 | cm | 80728 sl2 | 6 | black | 100 | cm | 100729 sl7 | 6 | brown | 60 | cm | 60730 sl4 | 8 | black | 40 | inch | 101.6731 sl3 | 10 | black | 35 | inch | 88.9732 sl8 | 21 | brown | 40 | inch | 101.6733 sl10 | 1000 | magenta | 40 | inch | 101.6734 sl5 | 4 | brown | 1 | m | 100735 sl6 | 20 | brown | 0.9 | m | 90736(9 rows)737</pre><p>738 </p><p>739 A <code class="command">DELETE</code> on a view, with a subquery qualification that740 in total uses 4 nesting/joined views, where one of them741 itself has a subquery qualification containing a view742 and where calculated view columns are used,743 gets rewritten into744 one single query tree that deletes the requested data745 from a real table.746</p><p>747 There are probably only a few situations out in the real world748 where such a construct is necessary. But it makes you feel749 comfortable that it works.750</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="41.3. Materialized Views">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-privileges.html" title="41.5. Rules and Privileges">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.3. Materialized Views </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.5. Rules and Privileges</td></tr></table></div></body></html>