Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
rules-views.html510 linesDownload Raw Back to 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>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 &gt;= rsh.slminlen_cm117       AND rsl.sl_len_cm &lt;= 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 &gt;= 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 &gt;= 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 &gt;= rsh.slminlen_cm280           AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm) shoe_ready281 WHERE shoe_ready.total_avail &gt;= 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 &gt;= rsh.slminlen_cm317           AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm) shoe_ready318 WHERE shoe_ready.total_avail &gt; 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>
codekingpro/portable-devtools · Team Ai