diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
| commit | 6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch) | |
| tree | 657d8194422a5daccecfd42d654b8a245ef7b4c8 /doc/src/sgml/html/rules-views.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-13-6eb9c5a5657d1fe77b55cc261450f3538d35a94d.tar.xz postgresql-13-6eb9c5a5657d1fe77b55cc261450f3538d35a94d.zip | |
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/rules-views.html')
| -rw-r--r-- | doc/src/sgml/html/rules-views.html | 501 |
1 files changed, 501 insertions, 0 deletions
diff --git a/doc/src/sgml/html/rules-views.html b/doc/src/sgml/html/rules-views.html new file mode 100644 index 0000000..5bc1327 --- /dev/null +++ b/doc/src/sgml/html/rules-views.html @@ -0,0 +1,501 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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>40.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 V1.79.1" /><link rel="prev" href="querytree.html" title="40.1. The Query Tree" /><link rel="next" href="rules-materializedviews.html" title="40.3. Materialized Views" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">40.2. Views and the Rule System</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="querytree.html" title="40.1. The Query Tree">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><th width="60%" align="center">Chapter 40. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-materializedviews.html" title="40.3. Materialized Views">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="RULES-VIEWS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">40.2. Views and the Rule System</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="rules-views.html#RULES-SELECT">40.2.1. How <code class="command">SELECT</code> Rules Work</a></span></dt><dt><span class="sect2"><a href="rules-views.html#id-1.8.6.7.6">40.2.2. View Rules in Non-<code class="command">SELECT</code> Statements</a></span></dt><dt><span class="sect2"><a href="rules-views.html#id-1.8.6.7.7">40.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">40.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> + Views in <span class="productname">PostgreSQL</span> are implemented + using the rule system. In fact, there is essentially no difference + between: + +</p><pre class="programlisting"> +CREATE VIEW myview AS SELECT * FROM mytab; +</pre><p> + + compared against the two commands: + +</p><pre class="programlisting"> +CREATE TABLE myview (<em class="replaceable"><code>same column list as mytab</code></em>); +CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD + SELECT * FROM mytab; +</pre><p> + + because this is exactly what the <code class="command">CREATE VIEW</code> + command does internally. This has some side effects. One of them + is that the information about a view in the + <span class="productname">PostgreSQL</span> system catalogs is exactly + the same as it is for a table. So for the parser, there is + absolutely no difference between a table and a view. They are the + same thing: relations. +</p><div class="sect2" id="RULES-SELECT"><div class="titlepage"><div><div><h3 class="title">40.2.1. How <code class="command">SELECT</code> Rules Work</h3></div></div></div><a id="id-1.8.6.7.5.2" class="indexterm"></a><p> + Rules <code class="literal">ON SELECT</code> are applied to all queries as the last step, even + if the command given is an <code class="command">INSERT</code>, + <code class="command">UPDATE</code> or <code class="command">DELETE</code>. And they + have different semantics from rules on the other command types in that they modify the + query tree in place instead of creating a new one. So + <code class="command">SELECT</code> rules are described first. +</p><p> + Currently, there can be only one action in an <code class="literal">ON SELECT</code> rule, and it must + be an unconditional <code class="command">SELECT</code> action that is <code class="literal">INSTEAD</code>. This restriction was + required to make rules safe enough to open them for ordinary users, and + it restricts <code class="literal">ON SELECT</code> rules to act like views. +</p><p> + The examples for this chapter are two join views that do some + calculations and some more views using them in turn. One of the + two first views is customized later by adding rules for + <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and + <code class="command">DELETE</code> operations so that the final result will + be a view that behaves like a real table with some magic + functionality. This is not such a simple example to start from and + this makes things harder to get into. But it's better to have one + example that covers all the points discussed step by step rather + than having many different ones that might mix up in mind. +</p><p> + The real tables we need in the first two rule system descriptions + are these: + +</p><pre class="programlisting"> +CREATE TABLE shoe_data ( + shoename text, -- primary key + sh_avail integer, -- available number of pairs + slcolor text, -- preferred shoelace color + slminlen real, -- minimum shoelace length + slmaxlen real, -- maximum shoelace length + slunit text -- length unit +); + +CREATE TABLE shoelace_data ( + sl_name text, -- primary key + sl_avail integer, -- available number of pairs + sl_color text, -- shoelace color + sl_len real, -- shoelace length + sl_unit text -- length unit +); + +CREATE TABLE unit ( + un_name text, -- primary key + un_fact real -- factor to transform to cm +); +</pre><p> + + As you can see, they represent shoe-store data. +</p><p> + The views are created as: + +</p><pre class="programlisting"> +CREATE VIEW shoe AS + SELECT sh.shoename, + sh.sh_avail, + sh.slcolor, + sh.slminlen, + sh.slminlen * un.un_fact AS slminlen_cm, + sh.slmaxlen, + sh.slmaxlen * un.un_fact AS slmaxlen_cm, + sh.slunit + FROM shoe_data sh, unit un + WHERE sh.slunit = un.un_name; + +CREATE VIEW shoelace AS + SELECT s.sl_name, + s.sl_avail, + s.sl_color, + s.sl_len, + s.sl_unit, + s.sl_len * u.un_fact AS sl_len_cm + FROM shoelace_data s, unit u + WHERE s.sl_unit = u.un_name; + +CREATE VIEW shoe_ready AS + SELECT rsh.shoename, + rsh.sh_avail, + rsl.sl_name, + rsl.sl_avail, + least(rsh.sh_avail, rsl.sl_avail) AS total_avail + FROM shoe rsh, shoelace rsl + WHERE rsl.sl_color = rsh.slcolor + AND rsl.sl_len_cm >= rsh.slminlen_cm + AND rsl.sl_len_cm <= rsh.slmaxlen_cm; +</pre><p> + + The <code class="command">CREATE VIEW</code> command for the + <code class="literal">shoelace</code> view (which is the simplest one we + have) will create a relation <code class="literal">shoelace</code> and an entry in + <code class="structname">pg_rewrite</code> that tells that there is a + rewrite rule that must be applied whenever the relation <code class="literal">shoelace</code> + is referenced in a query's range table. The rule has no rule + qualification (discussed later, with the non-<code class="command">SELECT</code> rules, since + <code class="command">SELECT</code> rules currently cannot have them) and it is <code class="literal">INSTEAD</code>. Note + that rule qualifications are not the same as query qualifications. + The action of our rule has a query qualification. + The action of the rule is one query tree that is a copy of the + <code class="command">SELECT</code> statement in the view creation command. +</p><div class="note"><h3 class="title">Note</h3><p> + The two extra range + table entries for <code class="literal">NEW</code> and <code class="literal">OLD</code> that you can see in + the <code class="structname">pg_rewrite</code> entry aren't of interest + for <code class="command">SELECT</code> rules. + </p></div><p> + Now we populate <code class="literal">unit</code>, <code class="literal">shoe_data</code> + and <code class="literal">shoelace_data</code> and run a simple query on a view: + +</p><pre class="programlisting"> +INSERT INTO unit VALUES ('cm', 1.0); +INSERT INTO unit VALUES ('m', 100.0); +INSERT INTO unit VALUES ('inch', 2.54); + +INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm'); +INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch'); +INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); +INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); + +INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm'); +INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm'); +INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch'); +INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch'); +INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm'); +INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm'); +INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm'); +INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch'); + +SELECT * FROM shoelace; + + sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm +-----------+----------+----------+--------+---------+----------- + sl1 | 5 | black | 80 | cm | 80 + sl2 | 6 | black | 100 | cm | 100 + sl7 | 7 | brown | 60 | cm | 60 + sl3 | 0 | black | 35 | inch | 88.9 + sl4 | 8 | black | 40 | inch | 101.6 + sl8 | 1 | brown | 40 | inch | 101.6 + sl5 | 4 | brown | 1 | m | 100 + sl6 | 0 | brown | 0.9 | m | 90 +(8 rows) +</pre><p> + </p><p> + This is the simplest <code class="command">SELECT</code> you can do on our + views, so we take this opportunity to explain the basics of view + rules. The <code class="literal">SELECT * FROM shoelace</code> was + interpreted by the parser and produced the query tree: + +</p><pre class="programlisting"> +SELECT shoelace.sl_name, shoelace.sl_avail, + shoelace.sl_color, shoelace.sl_len, + shoelace.sl_unit, shoelace.sl_len_cm + FROM shoelace shoelace; +</pre><p> + + and this is given to the rule system. The rule system walks through the + range table and checks if there are rules + for any relation. When processing the range table entry for + <code class="literal">shoelace</code> (the only one up to now) it finds the + <code class="literal">_RETURN</code> rule with the query tree: + +</p><pre class="programlisting"> +SELECT s.sl_name, s.sl_avail, + s.sl_color, s.sl_len, s.sl_unit, + s.sl_len * u.un_fact AS sl_len_cm + FROM shoelace old, shoelace new, + shoelace_data s, unit u + WHERE s.sl_unit = u.un_name; +</pre><p> +</p><p> + To expand the view, the rewriter simply creates a subquery range-table + entry containing the rule's action query tree, and substitutes this + range table entry for the original one that referenced the view. The + resulting rewritten query tree is almost the same as if you had typed: + +</p><pre class="programlisting"> +SELECT shoelace.sl_name, shoelace.sl_avail, + shoelace.sl_color, shoelace.sl_len, + shoelace.sl_unit, shoelace.sl_len_cm + FROM (SELECT s.sl_name, + s.sl_avail, + s.sl_color, + s.sl_len, + s.sl_unit, + s.sl_len * u.un_fact AS sl_len_cm + FROM shoelace_data s, unit u + WHERE s.sl_unit = u.un_name) shoelace; +</pre><p> + + There is one difference however: the subquery's range table has two + extra entries <code class="literal">shoelace old</code> and <code class="literal">shoelace new</code>. These entries don't + participate directly in the query, since they aren't referenced by + the subquery's join tree or target list. The rewriter uses them + to store the access privilege check information that was originally present + in the range-table entry that referenced the view. In this way, the + executor will still check that the user has proper privileges to access + the view, even though there's no direct use of the view in the rewritten + query. +</p><p> + That was the first rule applied. The rule system will continue checking + the remaining range-table entries in the top query (in this example there + are no more), and it will recursively check the range-table entries in + the added subquery to see if any of them reference views. (But it + won't expand <code class="literal">old</code> or <code class="literal">new</code> — otherwise we'd have infinite recursion!) + In this example, there are no rewrite rules for <code class="literal">shoelace_data</code> or <code class="literal">unit</code>, + so rewriting is complete and the above is the final result given to + the planner. +</p><p> + Now we want to write a query that finds out for which shoes currently in the store + we have the matching shoelaces (color and length) and where the + total number of exactly matching pairs is greater than or equal to two. + +</p><pre class="programlisting"> +SELECT * FROM shoe_ready WHERE total_avail >= 2; + + shoename | sh_avail | sl_name | sl_avail | total_avail +----------+----------+---------+----------+------------- + sh1 | 2 | sl1 | 5 | 2 + sh3 | 4 | sl7 | 7 | 4 +(2 rows) +</pre><p> +</p><p> + The output of the parser this time is the query tree: + +</p><pre class="programlisting"> +SELECT shoe_ready.shoename, shoe_ready.sh_avail, + shoe_ready.sl_name, shoe_ready.sl_avail, + shoe_ready.total_avail + FROM shoe_ready shoe_ready + WHERE shoe_ready.total_avail >= 2; +</pre><p> + + The first rule applied will be the one for the + <code class="literal">shoe_ready</code> view and it results in the + query tree: + +</p><pre class="programlisting"> +SELECT shoe_ready.shoename, shoe_ready.sh_avail, + shoe_ready.sl_name, shoe_ready.sl_avail, + shoe_ready.total_avail + FROM (SELECT rsh.shoename, + rsh.sh_avail, + rsl.sl_name, + rsl.sl_avail, + least(rsh.sh_avail, rsl.sl_avail) AS total_avail + FROM shoe rsh, shoelace rsl + WHERE rsl.sl_color = rsh.slcolor + AND rsl.sl_len_cm >= rsh.slminlen_cm + AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready + WHERE shoe_ready.total_avail >= 2; +</pre><p> + + Similarly, the rules for <code class="literal">shoe</code> and + <code class="literal">shoelace</code> are substituted into the range table of + the subquery, leading to a three-level final query tree: + +</p><pre class="programlisting"> +SELECT shoe_ready.shoename, shoe_ready.sh_avail, + shoe_ready.sl_name, shoe_ready.sl_avail, + shoe_ready.total_avail + FROM (SELECT rsh.shoename, + rsh.sh_avail, + rsl.sl_name, + rsl.sl_avail, + least(rsh.sh_avail, rsl.sl_avail) AS total_avail + FROM (SELECT sh.shoename, + sh.sh_avail, + sh.slcolor, + sh.slminlen, + sh.slminlen * un.un_fact AS slminlen_cm, + sh.slmaxlen, + sh.slmaxlen * un.un_fact AS slmaxlen_cm, + sh.slunit + FROM shoe_data sh, unit un + WHERE sh.slunit = un.un_name) rsh, + (SELECT s.sl_name, + s.sl_avail, + s.sl_color, + s.sl_len, + s.sl_unit, + s.sl_len * u.un_fact AS sl_len_cm + FROM shoelace_data s, unit u + WHERE s.sl_unit = u.un_name) rsl + WHERE rsl.sl_color = rsh.slcolor + AND rsl.sl_len_cm >= rsh.slminlen_cm + AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready + WHERE shoe_ready.total_avail > 2; +</pre><p> + </p><p> + This might look inefficient, but the planner will collapse this into a + single-level query tree by <span class="quote">“<span class="quote">pulling up</span>”</span> the subqueries, + and then it will plan the joins just as if we'd written them out + manually. So collapsing the query tree is an optimization that the + rewrite system doesn't have to concern itself with. + </p></div><div class="sect2" id="id-1.8.6.7.6"><div class="titlepage"><div><div><h3 class="title">40.2.2. View Rules in Non-<code class="command">SELECT</code> Statements</h3></div></div></div><p> + Two details of the query tree aren't touched in the description of + view rules above. These are the command type and the result relation. + In fact, the command type is not needed by view rules, but the result + relation may affect the way in which the query rewriter works, because + special care needs to be taken if the result relation is a view. +</p><p> + There are only a few differences between a query tree for a + <code class="command">SELECT</code> and one for any other + command. Obviously, they have a different command type and for a + command other than a <code class="command">SELECT</code>, the result + relation points to the range-table entry where the result should + go. Everything else is absolutely the same. So having two tables + <code class="literal">t1</code> and <code class="literal">t2</code> with columns <code class="literal">a</code> and + <code class="literal">b</code>, the query trees for the two statements: + +</p><pre class="programlisting"> +SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a; + +UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a; +</pre><p> + + are nearly identical. In particular: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + The range tables contain entries for the tables <code class="literal">t1</code> and <code class="literal">t2</code>. + </p></li><li class="listitem"><p> + The target lists contain one variable that points to column + <code class="literal">b</code> of the range table entry for table <code class="literal">t2</code>. + </p></li><li class="listitem"><p> + The qualification expressions compare the columns <code class="literal">a</code> of both + range-table entries for equality. + </p></li><li class="listitem"><p> + The join trees show a simple join between <code class="literal">t1</code> and <code class="literal">t2</code>. + </p></li></ul></div><p> + </p><p> + The consequence is, that both query trees result in similar + execution plans: They are both joins over the two tables. For the + <code class="command">UPDATE</code> the missing columns from <code class="literal">t1</code> are added to + the target list by the planner and the final query tree will read + as: + +</p><pre class="programlisting"> +UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a; +</pre><p> + + and thus the executor run over the join will produce exactly the + same result set as: + +</p><pre class="programlisting"> +SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a; +</pre><p> + + But there is a little problem in + <code class="command">UPDATE</code>: the part of the executor plan that does + the join does not care what the results from the join are + meant for. It just produces a result set of rows. The fact that + one is a <code class="command">SELECT</code> command and the other is an + <code class="command">UPDATE</code> is handled higher up in the executor, where + it knows that this is an <code class="command">UPDATE</code>, and it knows that + this result should go into table <code class="literal">t1</code>. But which of the rows + that are there has to be replaced by the new row? +</p><p> + To resolve this problem, another entry is added to the target list + in <code class="command">UPDATE</code> (and also in + <code class="command">DELETE</code>) statements: the current tuple ID + (<acronym class="acronym">CTID</acronym>).<a id="id-1.8.6.7.6.5.4" class="indexterm"></a> + This is a system column containing the + file block number and position in the block for the row. Knowing + the table, the <acronym class="acronym">CTID</acronym> can be used to retrieve the + original row of <code class="literal">t1</code> to be updated. After adding the + <acronym class="acronym">CTID</acronym> to the target list, the query actually looks like: + +</p><pre class="programlisting"> +SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; +</pre><p> + + Now another detail of <span class="productname">PostgreSQL</span> enters + the stage. Old table rows aren't overwritten, and this + is why <code class="command">ROLLBACK</code> is fast. In an <code class="command">UPDATE</code>, + the new result row is inserted into the table (after stripping the + <acronym class="acronym">CTID</acronym>) and in the row header of the old row, which the + <acronym class="acronym">CTID</acronym> pointed to, the <code class="literal">cmax</code> and + <code class="literal">xmax</code> entries are set to the current command counter + and current transaction ID. Thus the old row is hidden, and after + the transaction commits the vacuum cleaner can eventually remove + the dead row. +</p><p> + Knowing all that, we can simply apply view rules in absolutely + the same way to any command. There is no difference. +</p></div><div class="sect2" id="id-1.8.6.7.7"><div class="titlepage"><div><div><h3 class="title">40.2.3. The Power of Views in <span class="productname">PostgreSQL</span></h3></div></div></div><p> + The above demonstrates how the rule system incorporates view + definitions into the original query tree. In the second example, a + simple <code class="command">SELECT</code> from one view created a final + query tree that is a join of 4 tables (<code class="literal">unit</code> was used twice with + different names). +</p><p> + The benefit of implementing views with the rule system is + that the planner has all + the information about which tables have to be scanned plus the + relationships between these tables plus the restrictive + qualifications from the views plus the qualifications from + the original query + in one single query tree. And this is still the situation + when the original query is already a join over views. + The planner has to decide which is + the best path to execute the query, and the more information + the planner has, the better this decision can be. And + the rule system as implemented in <span class="productname">PostgreSQL</span> + ensures that this is all information available about the query + up to that point. +</p></div><div class="sect2" id="RULES-VIEWS-UPDATE"><div class="titlepage"><div><div><h3 class="title">40.2.4. Updating a View</h3></div></div></div><p> + What happens if a view is named as the target relation for an + <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or + <code class="command">DELETE</code>? Doing the substitutions + described above would give a query tree in which the result + relation points at a subquery range-table entry, which will not + work. There are several ways in which <span class="productname">PostgreSQL</span> + can support the appearance of updating a view, however. +</p><p> + If the subquery selects from a single base relation and is simple + enough, the rewriter can automatically replace the subquery with the + underlying base relation so that the <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, or <code class="command">DELETE</code> is applied to + the base relation in the appropriate way. Views that are + <span class="quote">“<span class="quote">simple enough</span>”</span> for this are called <em class="firstterm">automatically + updatable</em>. For detailed information on the kinds of view that can + be automatically updated, see <a class="xref" href="sql-createview.html" title="CREATE VIEW"><span class="refentrytitle">CREATE VIEW</span></a>. +</p><p> + Alternatively, the operation may be handled by a user-provided + <code class="literal">INSTEAD OF</code> trigger on the view. + Rewriting works slightly differently + in this case. For <code class="command">INSERT</code>, the rewriter does + nothing at all with the view, leaving it as the result relation + for the query. For <code class="command">UPDATE</code> and + <code class="command">DELETE</code>, it's still necessary to expand the + view query to produce the <span class="quote">“<span class="quote">old</span>”</span> rows that the command will + attempt to update or delete. So the view is expanded as normal, + but another unexpanded range-table entry is added to the query + to represent the view in its capacity as the result relation. +</p><p> + The problem that now arises is how to identify the rows to be + updated in the view. Recall that when the result relation + is a table, a special <acronym class="acronym">CTID</acronym> entry is added to the target + list to identify the physical locations of the rows to be updated. + This does not work if the result relation is a view, because a view + does not have any <acronym class="acronym">CTID</acronym>, since its rows do not have + actual physical locations. Instead, for an <code class="command">UPDATE</code> + or <code class="command">DELETE</code> operation, a special <code class="literal">wholerow</code> + entry is added to the target list, which expands to include all + columns from the view. The executor uses this value to supply the + <span class="quote">“<span class="quote">old</span>”</span> row to the <code class="literal">INSTEAD OF</code> trigger. It is + up to the trigger to work out what to update based on the old and + new row values. +</p><p> + Another possibility is for the user to define <code class="literal">INSTEAD</code> + rules that specify substitute actions for <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, and <code class="command">DELETE</code> commands on + a view. These rules will rewrite the command, typically into a command + that updates one or more tables, rather than views. That is the topic + of <a class="xref" href="rules-update.html" title="40.4. Rules on INSERT, UPDATE, and DELETE">Section 40.4</a>. +</p><p> + Note that rules are evaluated first, rewriting the original query + before it is planned and executed. Therefore, if a view has + <code class="literal">INSTEAD OF</code> triggers as well as rules on <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, or <code class="command">DELETE</code>, then the rules will be + evaluated first, and depending on the result, the triggers may not be + used at all. +</p><p> + Automatic rewriting of an <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, or <code class="command">DELETE</code> query on a + simple view is always tried last. Therefore, if a view has rules or + triggers, they will override the default behavior of automatically + updatable views. +</p><p> + If there are no <code class="literal">INSTEAD</code> rules or <code class="literal">INSTEAD OF</code> + triggers for the view, and the rewriter cannot automatically rewrite + the query as an update on the underlying base relation, an error will + be thrown because the executor cannot update a view as such. +</p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="querytree.html" title="40.1. The Query Tree">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-materializedviews.html" title="40.3. Materialized Views">Next</a></td></tr><tr><td width="40%" align="left" valign="top">40.1. The Query Tree </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 40.3. Materialized Views</td></tr></table></div></body></html>
\ No newline at end of file |