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.2. Views and the Rule System</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="querytree.html" title="41.1. The Query Tree" /><link rel="next" href="rules-materializedviews.html" title="41.3. Materialized Views" /></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.2. Views and the Rule System</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="querytree.html" title="41.1. The Query Tree">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-materializedviews.html" title="41.3. Materialized Views">Next</a></td></tr></table><hr /></div><div class="sect1" id="RULES-VIEWS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">41.2. Views and the Rule System <a href="#RULES-VIEWS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="rules-views.html#RULES-SELECT">41.2.1. How <code class="command">SELECT</code> Rules Work</a></span></dt><dt><span class="sect2"><a href="rules-views.html#RULES-VIEWS-NON-SELECT">41.2.2. View Rules in Non-<code class="command">SELECT</code> Statements</a></span></dt><dt><span class="sect2"><a href="rules-views.html#RULES-VIEWS-POWER">41.2.3. The Power of Views in <span class="productname">PostgreSQL</span></a></span></dt><dt><span class="sect2"><a href="rules-views.html#RULES-VIEWS-UPDATE">41.2.4. Updating a View</a></span></dt></dl></div><a id="id-1.8.6.7.2" class="indexterm"></a><a id="id-1.8.6.7.3" class="indexterm"></a><p>3 Views in <span class="productname">PostgreSQL</span> are implemented4 using the rule system. A view is basically an empty table (having no5 actual storage) with an <code class="literal">ON SELECT DO INSTEAD</code> rule.6 Conventionally, that rule is named <code class="literal">_RETURN</code>.7 So a view like8 9</p><pre class="programlisting">10CREATE VIEW myview AS SELECT * FROM mytab;11</pre><p>12 13 is very nearly the same thing as14 15</p><pre class="programlisting">16CREATE TABLE myview (<em class="replaceable"><code>same column list as mytab</code></em>);17CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD18 SELECT * FROM mytab;19</pre><p>20 21 although you can't actually write that, because tables are not22 allowed to have <code class="literal">ON SELECT</code> rules.23</p><p>24 A view can also have other kinds of <code class="literal">DO INSTEAD</code>25 rules, allowing <code class="command">INSERT</code>, <code class="command">UPDATE</code>,26 or <code class="command">DELETE</code> commands to be performed on the view27 despite its lack of underlying storage.28 This is discussed further below, in29 <a class="xref" href="rules-views.html#RULES-VIEWS-UPDATE" title="41.2.4. Updating a View">Section 41.2.4</a>.30</p><div class="sect2" id="RULES-SELECT"><div class="titlepage"><div><div><h3 class="title">41.2.1. How <code class="command">SELECT</code> Rules Work <a href="#RULES-SELECT" class="id_link">#</a></h3></div></div></div><a id="id-1.8.6.7.6.2" class="indexterm"></a><p>31 Rules <code class="literal">ON SELECT</code> are applied to all queries as the last step, even32 if the command given is an <code class="command">INSERT</code>,33 <code class="command">UPDATE</code> or <code class="command">DELETE</code>. And they34 have different semantics from rules on the other command types in that they modify the35 query tree in place instead of creating a new one. So36 <code class="command">SELECT</code> rules are described first.37</p><p>38 Currently, there can be only one action in an <code class="literal">ON SELECT</code> rule, and it must39 be an unconditional <code class="command">SELECT</code> action that is <code class="literal">INSTEAD</code>. This restriction was40 required to make rules safe enough to open them for ordinary users, and41 it restricts <code class="literal">ON SELECT</code> rules to act like views.42</p><p>43 The examples for this chapter are two join views that do some44 calculations and some more views using them in turn. One of the45 two first views is customized later by adding rules for46 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and47 <code class="command">DELETE</code> operations so that the final result will48 be a view that behaves like a real table with some magic49 functionality. This is not such a simple example to start from and50 this makes things harder to get into. But it's better to have one51 example that covers all the points discussed step by step rather52 than having many different ones that might mix up in mind.53</p><p>54 The real tables we need in the first two rule system descriptions55 are these:56 57</p><pre class="programlisting">58CREATE TABLE shoe_data (59 shoename text, -- primary key60 sh_avail integer, -- available number of pairs61 slcolor text, -- preferred shoelace color62 slminlen real, -- minimum shoelace length63 slmaxlen real, -- maximum shoelace length64 slunit text -- length unit65);66 67CREATE TABLE shoelace_data (68 sl_name text, -- primary key69 sl_avail integer, -- available number of pairs70 sl_color text, -- shoelace color71 sl_len real, -- shoelace length72 sl_unit text -- length unit73);74 75CREATE TABLE unit (76 un_name text, -- primary key77 un_fact real -- factor to transform to cm78);79</pre><p>80 81 As you can see, they represent shoe-store data.82</p><p>83 The views are created as:84 85</p><pre class="programlisting">86CREATE VIEW shoe AS87 SELECT sh.shoename,88 sh.sh_avail,89 sh.slcolor,90 sh.slminlen,91 sh.slminlen * un.un_fact AS slminlen_cm,92 sh.slmaxlen,93 sh.slmaxlen * un.un_fact AS slmaxlen_cm,94 sh.slunit95 FROM shoe_data sh, unit un96 WHERE sh.slunit = un.un_name;97 98CREATE VIEW shoelace AS99 SELECT s.sl_name,100 s.sl_avail,101 s.sl_color,102 s.sl_len,103 s.sl_unit,104 s.sl_len * u.un_fact AS sl_len_cm105 FROM shoelace_data s, unit u106 WHERE s.sl_unit = u.un_name;107 108CREATE VIEW shoe_ready AS109 SELECT rsh.shoename,110 rsh.sh_avail,111 rsl.sl_name,112 rsl.sl_avail,113 least(rsh.sh_avail, rsl.sl_avail) AS total_avail114 FROM shoe rsh, shoelace rsl115 WHERE rsl.sl_color = rsh.slcolor116 AND rsl.sl_len_cm >= rsh.slminlen_cm117 AND rsl.sl_len_cm <= rsh.slmaxlen_cm;118</pre><p>119 120 The <code class="command">CREATE VIEW</code> command for the121 <code class="literal">shoelace</code> view (which is the simplest one we122 have) will create a relation <code class="literal">shoelace</code> and an entry in123 <code class="structname">pg_rewrite</code> that tells that there is a124 rewrite rule that must be applied whenever the relation <code class="literal">shoelace</code>125 is referenced in a query's range table. The rule has no rule126 qualification (discussed later, with the non-<code class="command">SELECT</code> rules, since127 <code class="command">SELECT</code> rules currently cannot have them) and it is <code class="literal">INSTEAD</code>. Note128 that rule qualifications are not the same as query qualifications.129 The action of our rule has a query qualification.130 The action of the rule is one query tree that is a copy of the131 <code class="command">SELECT</code> statement in the view creation command.132</p><div class="note"><h3 class="title">Note</h3><p>133 The two extra range134 table entries for <code class="literal">NEW</code> and <code class="literal">OLD</code> that you can see in135 the <code class="structname">pg_rewrite</code> entry aren't of interest136 for <code class="command">SELECT</code> rules.137 </p></div><p>138 Now we populate <code class="literal">unit</code>, <code class="literal">shoe_data</code>139 and <code class="literal">shoelace_data</code> and run a simple query on a view:140 141</p><pre class="programlisting">142INSERT INTO unit VALUES ('cm', 1.0);143INSERT INTO unit VALUES ('m', 100.0);144INSERT INTO unit VALUES ('inch', 2.54);145 146INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');147INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');148INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');149INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');150 151INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');152INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');153INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');154INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');155INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');156INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');157INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');158INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');159 160SELECT * FROM shoelace;161 162 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm163-----------+----------+----------+--------+---------+-----------164 sl1 | 5 | black | 80 | cm | 80165 sl2 | 6 | black | 100 | cm | 100166 sl7 | 7 | brown | 60 | cm | 60167 sl3 | 0 | black | 35 | inch | 88.9168 sl4 | 8 | black | 40 | inch | 101.6169 sl8 | 1 | brown | 40 | inch | 101.6170 sl5 | 4 | brown | 1 | m | 100171 sl6 | 0 | brown | 0.9 | m | 90172(8 rows)173</pre><p>174 </p><p>175 This is the simplest <code class="command">SELECT</code> you can do on our176 views, so we take this opportunity to explain the basics of view177 rules. The <code class="literal">SELECT * FROM shoelace</code> was178 interpreted by the parser and produced the query tree:179 180</p><pre class="programlisting">181SELECT shoelace.sl_name, shoelace.sl_avail,182 shoelace.sl_color, shoelace.sl_len,183 shoelace.sl_unit, shoelace.sl_len_cm184 FROM shoelace shoelace;185</pre><p>186 187 and this is given to the rule system. The rule system walks through the188 range table and checks if there are rules189 for any relation. When processing the range table entry for190 <code class="literal">shoelace</code> (the only one up to now) it finds the191 <code class="literal">_RETURN</code> rule with the query tree:192 193</p><pre class="programlisting">194SELECT s.sl_name, s.sl_avail,195 s.sl_color, s.sl_len, s.sl_unit,196 s.sl_len * u.un_fact AS sl_len_cm197 FROM shoelace old, shoelace new,198 shoelace_data s, unit u199 WHERE s.sl_unit = u.un_name;200</pre><p>201</p><p>202 To expand the view, the rewriter simply creates a subquery range-table203 entry containing the rule's action query tree, and substitutes this204 range table entry for the original one that referenced the view. The205 resulting rewritten query tree is almost the same as if you had typed:206 207</p><pre class="programlisting">208SELECT shoelace.sl_name, shoelace.sl_avail,209 shoelace.sl_color, shoelace.sl_len,210 shoelace.sl_unit, shoelace.sl_len_cm211 FROM (SELECT s.sl_name,212 s.sl_avail,213 s.sl_color,214 s.sl_len,215 s.sl_unit,216 s.sl_len * u.un_fact AS sl_len_cm217 FROM shoelace_data s, unit u218 WHERE s.sl_unit = u.un_name) shoelace;219</pre><p>220 221 There is one difference however: the subquery's range table has two222 extra entries <code class="literal">shoelace old</code> and <code class="literal">shoelace new</code>. These entries don't223 participate directly in the query, since they aren't referenced by224 the subquery's join tree or target list. The rewriter uses them225 to store the access privilege check information that was originally present226 in the range-table entry that referenced the view. In this way, the227 executor will still check that the user has proper privileges to access228 the view, even though there's no direct use of the view in the rewritten229 query.230</p><p>231 That was the first rule applied. The rule system will continue checking232 the remaining range-table entries in the top query (in this example there233 are no more), and it will recursively check the range-table entries in234 the added subquery to see if any of them reference views. (But it235 won't expand <code class="literal">old</code> or <code class="literal">new</code> — otherwise we'd have infinite recursion!)236 In this example, there are no rewrite rules for <code class="literal">shoelace_data</code> or <code class="literal">unit</code>,237 so rewriting is complete and the above is the final result given to238 the planner.239</p><p>240 Now we want to write a query that finds out for which shoes currently in the store241 we have the matching shoelaces (color and length) and where the242 total number of exactly matching pairs is greater than or equal to two.243 244</p><pre class="programlisting">245SELECT * FROM shoe_ready WHERE total_avail >= 2;246 247 shoename | sh_avail | sl_name | sl_avail | total_avail248----------+----------+---------+----------+-------------249 sh1 | 2 | sl1 | 5 | 2250 sh3 | 4 | sl7 | 7 | 4251(2 rows)252</pre><p>253</p><p>254 The output of the parser this time is the query tree:255 256</p><pre class="programlisting">257SELECT shoe_ready.shoename, shoe_ready.sh_avail,258 shoe_ready.sl_name, shoe_ready.sl_avail,259 shoe_ready.total_avail260 FROM shoe_ready shoe_ready261 WHERE shoe_ready.total_avail >= 2;262</pre><p>263 264 The first rule applied will be the one for the265 <code class="literal">shoe_ready</code> view and it results in the266 query tree:267 268</p><pre class="programlisting">269SELECT shoe_ready.shoename, shoe_ready.sh_avail,270 shoe_ready.sl_name, shoe_ready.sl_avail,271 shoe_ready.total_avail272 FROM (SELECT rsh.shoename,273 rsh.sh_avail,274 rsl.sl_name,275 rsl.sl_avail,276 least(rsh.sh_avail, rsl.sl_avail) AS total_avail277 FROM shoe rsh, shoelace rsl278 WHERE rsl.sl_color = rsh.slcolor279 AND rsl.sl_len_cm >= rsh.slminlen_cm280 AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready281 WHERE shoe_ready.total_avail >= 2;282</pre><p>283 284 Similarly, the rules for <code class="literal">shoe</code> and285 <code class="literal">shoelace</code> are substituted into the range table of286 the subquery, leading to a three-level final query tree:287 288</p><pre class="programlisting">289SELECT shoe_ready.shoename, shoe_ready.sh_avail,290 shoe_ready.sl_name, shoe_ready.sl_avail,291 shoe_ready.total_avail292 FROM (SELECT rsh.shoename,293 rsh.sh_avail,294 rsl.sl_name,295 rsl.sl_avail,296 least(rsh.sh_avail, rsl.sl_avail) AS total_avail297 FROM (SELECT sh.shoename,298 sh.sh_avail,299 sh.slcolor,300 sh.slminlen,301 sh.slminlen * un.un_fact AS slminlen_cm,302 sh.slmaxlen,303 sh.slmaxlen * un.un_fact AS slmaxlen_cm,304 sh.slunit305 FROM shoe_data sh, unit un306 WHERE sh.slunit = un.un_name) rsh,307 (SELECT s.sl_name,308 s.sl_avail,309 s.sl_color,310 s.sl_len,311 s.sl_unit,312 s.sl_len * u.un_fact AS sl_len_cm313 FROM shoelace_data s, unit u314 WHERE s.sl_unit = u.un_name) rsl315 WHERE rsl.sl_color = rsh.slcolor316 AND rsl.sl_len_cm >= rsh.slminlen_cm317 AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready318 WHERE shoe_ready.total_avail > 2;319</pre><p>320 </p><p>321 This might look inefficient, but the planner will collapse this into a322 single-level query tree by <span class="quote">“<span class="quote">pulling up</span>”</span> the subqueries,323 and then it will plan the joins just as if we'd written them out324 manually. So collapsing the query tree is an optimization that the325 rewrite system doesn't have to concern itself with.326 </p></div><div class="sect2" id="RULES-VIEWS-NON-SELECT"><div class="titlepage"><div><div><h3 class="title">41.2.2. View Rules in Non-<code class="command">SELECT</code> Statements <a href="#RULES-VIEWS-NON-SELECT" class="id_link">#</a></h3></div></div></div><p>327 Two details of the query tree aren't touched in the description of328 view rules above. These are the command type and the result relation.329 In fact, the command type is not needed by view rules, but the result330 relation may affect the way in which the query rewriter works, because331 special care needs to be taken if the result relation is a view.332</p><p>333 There are only a few differences between a query tree for a334 <code class="command">SELECT</code> and one for any other335 command. Obviously, they have a different command type and for a336 command other than a <code class="command">SELECT</code>, the result337 relation points to the range-table entry where the result should338 go. Everything else is absolutely the same. So having two tables339 <code class="literal">t1</code> and <code class="literal">t2</code> with columns <code class="literal">a</code> and340 <code class="literal">b</code>, the query trees for the two statements:341 342</p><pre class="programlisting">343SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;344 345UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;346</pre><p>347 348 are nearly identical. In particular:349 350 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>351 The range tables contain entries for the tables <code class="literal">t1</code> and <code class="literal">t2</code>.352 </p></li><li class="listitem"><p>353 The target lists contain one variable that points to column354 <code class="literal">b</code> of the range table entry for table <code class="literal">t2</code>.355 </p></li><li class="listitem"><p>356 The qualification expressions compare the columns <code class="literal">a</code> of both357 range-table entries for equality.358 </p></li><li class="listitem"><p>359 The join trees show a simple join between <code class="literal">t1</code> and <code class="literal">t2</code>.360 </p></li></ul></div><p>361 </p><p>362 The consequence is, that both query trees result in similar363 execution plans: They are both joins over the two tables. For the364 <code class="command">UPDATE</code> the missing columns from <code class="literal">t1</code> are added to365 the target list by the planner and the final query tree will read366 as:367 368</p><pre class="programlisting">369UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;370</pre><p>371 372 and thus the executor run over the join will produce exactly the373 same result set as:374 375</p><pre class="programlisting">376SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;377</pre><p>378 379 But there is a little problem in380 <code class="command">UPDATE</code>: the part of the executor plan that does381 the join does not care what the results from the join are382 meant for. It just produces a result set of rows. The fact that383 one is a <code class="command">SELECT</code> command and the other is an384 <code class="command">UPDATE</code> is handled higher up in the executor, where385 it knows that this is an <code class="command">UPDATE</code>, and it knows that386 this result should go into table <code class="literal">t1</code>. But which of the rows387 that are there has to be replaced by the new row?388</p><p>389 To resolve this problem, another entry is added to the target list390 in <code class="command">UPDATE</code> (and also in391 <code class="command">DELETE</code>) statements: the current tuple ID392 (<acronym class="acronym">CTID</acronym>).<a id="id-1.8.6.7.7.5.4" class="indexterm"></a>393 This is a system column containing the394 file block number and position in the block for the row. Knowing395 the table, the <acronym class="acronym">CTID</acronym> can be used to retrieve the396 original row of <code class="literal">t1</code> to be updated. After adding the397 <acronym class="acronym">CTID</acronym> to the target list, the query actually looks like:398 399</p><pre class="programlisting">400SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;401</pre><p>402 403 Now another detail of <span class="productname">PostgreSQL</span> enters404 the stage. Old table rows aren't overwritten, and this405 is why <code class="command">ROLLBACK</code> is fast. In an <code class="command">UPDATE</code>,406 the new result row is inserted into the table (after stripping the407 <acronym class="acronym">CTID</acronym>) and in the row header of the old row, which the408 <acronym class="acronym">CTID</acronym> pointed to, the <code class="literal">cmax</code> and409 <code class="literal">xmax</code> entries are set to the current command counter410 and current transaction ID. Thus the old row is hidden, and after411 the transaction commits the vacuum cleaner can eventually remove412 the dead row.413</p><p>414 Knowing all that, we can simply apply view rules in absolutely415 the same way to any command. There is no difference.416</p></div><div class="sect2" id="RULES-VIEWS-POWER"><div class="titlepage"><div><div><h3 class="title">41.2.3. The Power of Views in <span class="productname">PostgreSQL</span> <a href="#RULES-VIEWS-POWER" class="id_link">#</a></h3></div></div></div><p>417 The above demonstrates how the rule system incorporates view418 definitions into the original query tree. In the second example, a419 simple <code class="command">SELECT</code> from one view created a final420 query tree that is a join of 4 tables (<code class="literal">unit</code> was used twice with421 different names).422</p><p>423 The benefit of implementing views with the rule system is424 that the planner has all425 the information about which tables have to be scanned plus the426 relationships between these tables plus the restrictive427 qualifications from the views plus the qualifications from428 the original query429 in one single query tree. And this is still the situation430 when the original query is already a join over views.431 The planner has to decide which is432 the best path to execute the query, and the more information433 the planner has, the better this decision can be. And434 the rule system as implemented in <span class="productname">PostgreSQL</span>435 ensures that this is all information available about the query436 up to that point.437</p></div><div class="sect2" id="RULES-VIEWS-UPDATE"><div class="titlepage"><div><div><h3 class="title">41.2.4. Updating a View <a href="#RULES-VIEWS-UPDATE" class="id_link">#</a></h3></div></div></div><p>438 What happens if a view is named as the target relation for an439 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or440 <code class="command">DELETE</code>? Doing the substitutions441 described above would give a query tree in which the result442 relation points at a subquery range-table entry, which will not443 work. There are several ways in which <span class="productname">PostgreSQL</span>444 can support the appearance of updating a view, however.445 In order of user-experienced complexity those are: automatically substitute446 in the underlying table for the view, execute a user-defined trigger,447 or rewrite the query per a user-defined rule.448 These options are discussed below.449</p><p>450 If the subquery selects from a single base relation and is simple451 enough, the rewriter can automatically replace the subquery with the452 underlying base relation so that the <code class="command">INSERT</code>,453 <code class="command">UPDATE</code>, or <code class="command">DELETE</code> is applied to454 the base relation in the appropriate way. Views that are455 <span class="quote">“<span class="quote">simple enough</span>”</span> for this are called <em class="firstterm">automatically456 updatable</em>. For detailed information on the kinds of view that can457 be automatically updated, see <a class="xref" href="sql-createview.html" title="CREATE VIEW"><span class="refentrytitle">CREATE VIEW</span></a>.458</p><p>459 Alternatively, the operation may be handled by a user-provided460 <code class="literal">INSTEAD OF</code> trigger on the view461 (see <a class="xref" href="sql-createtrigger.html" title="CREATE TRIGGER"><span class="refentrytitle">CREATE TRIGGER</span></a>).462 Rewriting works slightly differently463 in this case. For <code class="command">INSERT</code>, the rewriter does464 nothing at all with the view, leaving it as the result relation465 for the query. For <code class="command">UPDATE</code> and466 <code class="command">DELETE</code>, it's still necessary to expand the467 view query to produce the <span class="quote">“<span class="quote">old</span>”</span> rows that the command will468 attempt to update or delete. So the view is expanded as normal,469 but another unexpanded range-table entry is added to the query470 to represent the view in its capacity as the result relation.471</p><p>472 The problem that now arises is how to identify the rows to be473 updated in the view. Recall that when the result relation474 is a table, a special <acronym class="acronym">CTID</acronym> entry is added to the target475 list to identify the physical locations of the rows to be updated.476 This does not work if the result relation is a view, because a view477 does not have any <acronym class="acronym">CTID</acronym>, since its rows do not have478 actual physical locations. Instead, for an <code class="command">UPDATE</code>479 or <code class="command">DELETE</code> operation, a special <code class="literal">wholerow</code>480 entry is added to the target list, which expands to include all481 columns from the view. The executor uses this value to supply the482 <span class="quote">“<span class="quote">old</span>”</span> row to the <code class="literal">INSTEAD OF</code> trigger. It is483 up to the trigger to work out what to update based on the old and484 new row values.485</p><p>486 Another possibility is for the user to define <code class="literal">INSTEAD</code>487 rules that specify substitute actions for <code class="command">INSERT</code>,488 <code class="command">UPDATE</code>, and <code class="command">DELETE</code> commands on489 a view. These rules will rewrite the command, typically into a command490 that updates one or more tables, rather than views. That is the topic491 of <a class="xref" href="rules-update.html" title="41.4. Rules on INSERT, UPDATE, and DELETE">Section 41.4</a>.492</p><p>493 Note that rules are evaluated first, rewriting the original query494 before it is planned and executed. Therefore, if a view has495 <code class="literal">INSTEAD OF</code> triggers as well as rules on <code class="command">INSERT</code>,496 <code class="command">UPDATE</code>, or <code class="command">DELETE</code>, then the rules will be497 evaluated first, and depending on the result, the triggers may not be498 used at all.499</p><p>500 Automatic rewriting of an <code class="command">INSERT</code>,501 <code class="command">UPDATE</code>, or <code class="command">DELETE</code> query on a502 simple view is always tried last. Therefore, if a view has rules or503 triggers, they will override the default behavior of automatically504 updatable views.505</p><p>506 If there are no <code class="literal">INSTEAD</code> rules or <code class="literal">INSTEAD OF</code>507 triggers for the view, and the rewriter cannot automatically rewrite508 the query as an update on the underlying base relation, an error will509 be thrown because the executor cannot update a view as such.510</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="querytree.html" title="41.1. The Query Tree">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-materializedviews.html" title="41.3. Materialized Views">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.1. The Query Tree </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.3. Materialized Views</td></tr></table></div></body></html>