Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
queries-with.html564 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>7.8. WITH Queries (Common Table Expressions)</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="queries-values.html" title="7.7. VALUES Lists" /><link rel="next" href="datatype.html" title="Chapter 8. Data Types" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">7.8. <code class="literal">WITH</code> Queries (Common Table Expressions)</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="queries-values.html" title="7.7. VALUES Lists">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><th width="60%" align="center">Chapter 7. Queries</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="datatype.html" title="Chapter 8. Data Types">Next</a></td></tr></table><hr /></div><div class="sect1" id="QUERIES-WITH"><div class="titlepage"><div><div><h2 class="title" style="clear: both">7.8. <code class="literal">WITH</code> Queries (Common Table Expressions) <a href="#QUERIES-WITH" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-SELECT">7.8.1. <code class="command">SELECT</code> in <code class="literal">WITH</code></a></span></dt><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-RECURSIVE">7.8.2. Recursive Queries</a></span></dt><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION">7.8.3. Common Table Expression Materialization</a></span></dt><dt><span class="sect2"><a href="queries-with.html#QUERIES-WITH-MODIFYING">7.8.4. Data-Modifying Statements in <code class="literal">WITH</code></a></span></dt></dl></div><a id="id-1.5.6.12.2" class="indexterm"></a><a id="id-1.5.6.12.3" class="indexterm"></a><p>3   <code class="literal">WITH</code> provides a way to write auxiliary statements for use in a4   larger query.  These statements, which are often referred to as Common5   Table Expressions or <acronym class="acronym">CTE</acronym>s, can be thought of as defining6   temporary tables that exist just for one query.  Each auxiliary statement7   in a <code class="literal">WITH</code> clause can be a <code class="command">SELECT</code>,8   <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>; and the9   <code class="literal">WITH</code> clause itself is attached to a primary statement that can10   be a <code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,11   <code class="command">DELETE</code>, or <code class="command">MERGE</code>.12  </p><div class="sect2" id="QUERIES-WITH-SELECT"><div class="titlepage"><div><div><h3 class="title">7.8.1. <code class="command">SELECT</code> in <code class="literal">WITH</code> <a href="#QUERIES-WITH-SELECT" class="id_link">#</a></h3></div></div></div><p>13   The basic value of <code class="command">SELECT</code> in <code class="literal">WITH</code> is to14   break down complicated queries into simpler parts.  An example is:15 16</p><pre class="programlisting">17WITH regional_sales AS (18    SELECT region, SUM(amount) AS total_sales19    FROM orders20    GROUP BY region21), top_regions AS (22    SELECT region23    FROM regional_sales24    WHERE total_sales &gt; (SELECT SUM(total_sales)/10 FROM regional_sales)25)26SELECT region,27       product,28       SUM(quantity) AS product_units,29       SUM(amount) AS product_sales30FROM orders31WHERE region IN (SELECT region FROM top_regions)32GROUP BY region, product;33</pre><p>34 35   which displays per-product sales totals in only the top sales regions.36   The <code class="literal">WITH</code> clause defines two auxiliary statements named37   <code class="structname">regional_sales</code> and <code class="structname">top_regions</code>,38   where the output of <code class="structname">regional_sales</code> is used in39   <code class="structname">top_regions</code> and the output of <code class="structname">top_regions</code>40   is used in the primary <code class="command">SELECT</code> query.41   This example could have been written without <code class="literal">WITH</code>,42   but we'd have needed two levels of nested sub-<code class="command">SELECT</code>s.  It's a bit43   easier to follow this way.44  </p></div><div class="sect2" id="QUERIES-WITH-RECURSIVE"><div class="titlepage"><div><div><h3 class="title">7.8.2. Recursive Queries <a href="#QUERIES-WITH-RECURSIVE" class="id_link">#</a></h3></div></div></div><p>45   <a id="id-1.5.6.12.6.2.1" class="indexterm"></a>46   The optional <code class="literal">RECURSIVE</code> modifier changes <code class="literal">WITH</code>47   from a mere syntactic convenience into a feature that accomplishes48   things not otherwise possible in standard SQL.  Using49   <code class="literal">RECURSIVE</code>, a <code class="literal">WITH</code> query can refer to its own50   output.  A very simple example is this query to sum the integers from 151   through 100:52 53</p><pre class="programlisting">54WITH RECURSIVE t(n) AS (55    VALUES (1)56  UNION ALL57    SELECT n+1 FROM t WHERE n &lt; 10058)59SELECT sum(n) FROM t;60</pre><p>61 62   The general form of a recursive <code class="literal">WITH</code> query is always a63   <em class="firstterm">non-recursive term</em>, then <code class="literal">UNION</code> (or64   <code class="literal">UNION ALL</code>), then a65   <em class="firstterm">recursive term</em>, where only the recursive term can contain66   a reference to the query's own output.  Such a query is executed as67   follows:68  </p><div class="procedure" id="id-1.5.6.12.6.3"><p class="title"><strong>Recursive Query Evaluation</strong></p><ol class="procedure" type="1"><li class="step"><p>69     Evaluate the non-recursive term.  For <code class="literal">UNION</code> (but not70     <code class="literal">UNION ALL</code>), discard duplicate rows.  Include all remaining71     rows in the result of the recursive query, and also place them in a72     temporary <em class="firstterm">working table</em>.73    </p></li><li class="step"><p>74     So long as the working table is not empty, repeat these steps:75    </p><ol type="a" class="substeps"><li class="step"><p>76       Evaluate the recursive term, substituting the current contents of77       the working table for the recursive self-reference.78       For <code class="literal">UNION</code> (but not <code class="literal">UNION ALL</code>), discard79       duplicate rows and rows that duplicate any previous result row.80       Include all remaining rows in the result of the recursive query, and81       also place them in a temporary <em class="firstterm">intermediate table</em>.82      </p></li><li class="step"><p>83       Replace the contents of the working table with the contents of the84       intermediate table, then empty the intermediate table.85      </p></li></ol></li></ol></div><div class="note"><h3 class="title">Note</h3><p>86    While <code class="literal">RECURSIVE</code> allows queries to be specified87    recursively, internally such queries are evaluated iteratively.88   </p></div><p>89   In the example above, the working table has just a single row in each step,90   and it takes on the values from 1 through 100 in successive steps.  In91   the 100th step, there is no output because of the <code class="literal">WHERE</code>92   clause, and so the query terminates.93  </p><p>94   Recursive queries are typically used to deal with hierarchical or95   tree-structured data.  A useful example is this query to find all the96   direct and indirect sub-parts of a product, given only a table that97   shows immediate inclusions:98 99</p><pre class="programlisting">100WITH RECURSIVE included_parts(sub_part, part, quantity) AS (101    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'102  UNION ALL103    SELECT p.sub_part, p.part, p.quantity * pr.quantity104    FROM included_parts pr, parts p105    WHERE p.part = pr.sub_part106)107SELECT sub_part, SUM(quantity) as total_quantity108FROM included_parts109GROUP BY sub_part110</pre><p>111  </p><div class="sect3" id="QUERIES-WITH-SEARCH"><div class="titlepage"><div><div><h4 class="title">7.8.2.1. Search Order <a href="#QUERIES-WITH-SEARCH" class="id_link">#</a></h4></div></div></div><p>112    When computing a tree traversal using a recursive query, you might want to113    order the results in either depth-first or breadth-first order.  This can114    be done by computing an ordering column alongside the other data columns115    and using that to sort the results at the end.  Note that this does not116    actually control in which order the query evaluation visits the rows; that117    is as always in SQL implementation-dependent.  This approach merely118    provides a convenient way to order the results afterwards.119   </p><p>120    To create a depth-first order, we compute for each result row an array of121    rows that we have visited so far.  For example, consider the following122    query that searches a table <code class="structname">tree</code> using a123    <code class="structfield">link</code> field:124 125</p><pre class="programlisting">126WITH RECURSIVE search_tree(id, link, data) AS (127    SELECT t.id, t.link, t.data128    FROM tree t129  UNION ALL130    SELECT t.id, t.link, t.data131    FROM tree t, search_tree st132    WHERE t.id = st.link133)134SELECT * FROM search_tree;135</pre><p>136 137    To add depth-first ordering information, you can write this:138 139</p><pre class="programlisting">140WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>path</strong></span>) AS (141    SELECT t.id, t.link, t.data, <span class="emphasis"><strong>ARRAY[t.id]</strong></span>142    FROM tree t143  UNION ALL144    SELECT t.id, t.link, t.data, <span class="emphasis"><strong>path || t.id</strong></span>145    FROM tree t, search_tree st146    WHERE t.id = st.link147)148SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY path</strong></span>;149</pre><p>150   </p><p>151    In the general case where more than one field needs to be used to identify152    a row, use an array of rows.  For example, if we needed to track fields153    <code class="structfield">f1</code> and <code class="structfield">f2</code>:154 155</p><pre class="programlisting">156WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>path</strong></span>) AS (157    SELECT t.id, t.link, t.data, <span class="emphasis"><strong>ARRAY[ROW(t.f1, t.f2)]</strong></span>158    FROM tree t159  UNION ALL160    SELECT t.id, t.link, t.data, <span class="emphasis"><strong>path || ROW(t.f1, t.f2)</strong></span>161    FROM tree t, search_tree st162    WHERE t.id = st.link163)164SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY path</strong></span>;165</pre><p>166   </p><div class="tip"><h3 class="title">Tip</h3><p>167     Omit the <code class="literal">ROW()</code> syntax in the common case where only one168     field needs to be tracked.  This allows a simple array rather than a169     composite-type array to be used, gaining efficiency.170    </p></div><p>171    To create a breadth-first order, you can add a column that tracks the depth172    of the search, for example:173 174</p><pre class="programlisting">175WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>depth</strong></span>) AS (176    SELECT t.id, t.link, t.data, <span class="emphasis"><strong>0</strong></span>177    FROM tree t178  UNION ALL179    SELECT t.id, t.link, t.data, <span class="emphasis"><strong>depth + 1</strong></span>180    FROM tree t, search_tree st181    WHERE t.id = st.link182)183SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY depth</strong></span>;184</pre><p>185 186    To get a stable sort, add data columns as secondary sorting columns.187   </p><div class="tip"><h3 class="title">Tip</h3><p>188     The recursive query evaluation algorithm produces its output in189     breadth-first search order.  However, this is an implementation detail and190     it is perhaps unsound to rely on it.  The order of the rows within each191     level is certainly undefined, so some explicit ordering might be desired192     in any case.193    </p></div><p>194    There is built-in syntax to compute a depth- or breadth-first sort column.195    For example:196 197</p><pre class="programlisting">198WITH RECURSIVE search_tree(id, link, data) AS (199    SELECT t.id, t.link, t.data200    FROM tree t201  UNION ALL202    SELECT t.id, t.link, t.data203    FROM tree t, search_tree st204    WHERE t.id = st.link205) <span class="emphasis"><strong>SEARCH DEPTH FIRST BY id SET ordercol</strong></span>206SELECT * FROM search_tree ORDER BY ordercol;207 208WITH RECURSIVE search_tree(id, link, data) AS (209    SELECT t.id, t.link, t.data210    FROM tree t211  UNION ALL212    SELECT t.id, t.link, t.data213    FROM tree t, search_tree st214    WHERE t.id = st.link215) <span class="emphasis"><strong>SEARCH BREADTH FIRST BY id SET ordercol</strong></span>216SELECT * FROM search_tree ORDER BY ordercol;217</pre><p>218    This syntax is internally expanded to something similar to the above219    hand-written forms.  The <code class="literal">SEARCH</code> clause specifies whether220    depth- or breadth first search is wanted, the list of columns to track for221    sorting, and a column name that will contain the result data that can be222    used for sorting.  That column will implicitly be added to the output rows223    of the CTE.224   </p></div><div class="sect3" id="QUERIES-WITH-CYCLE"><div class="titlepage"><div><div><h4 class="title">7.8.2.2. Cycle Detection <a href="#QUERIES-WITH-CYCLE" class="id_link">#</a></h4></div></div></div><p>225   When working with recursive queries it is important to be sure that226   the recursive part of the query will eventually return no tuples,227   or else the query will loop indefinitely.  Sometimes, using228   <code class="literal">UNION</code> instead of <code class="literal">UNION ALL</code> can accomplish this229   by discarding rows that duplicate previous output rows.  However, often a230   cycle does not involve output rows that are completely duplicate: it may be231   necessary to check just one or a few fields to see if the same point has232   been reached before.  The standard method for handling such situations is233   to compute an array of the already-visited values.  For example, consider again234   the following query that searches a table <code class="structname">graph</code> using a235   <code class="structfield">link</code> field:236 237</p><pre class="programlisting">238WITH RECURSIVE search_graph(id, link, data, depth) AS (239    SELECT g.id, g.link, g.data, 0240    FROM graph g241  UNION ALL242    SELECT g.id, g.link, g.data, sg.depth + 1243    FROM graph g, search_graph sg244    WHERE g.id = sg.link245)246SELECT * FROM search_graph;247</pre><p>248 249   This query will loop if the <code class="structfield">link</code> relationships contain250   cycles.  Because we require a <span class="quote">“<span class="quote">depth</span>”</span> output, just changing251   <code class="literal">UNION ALL</code> to <code class="literal">UNION</code> would not eliminate the looping.252   Instead we need to recognize whether we have reached the same row again253   while following a particular path of links.  We add two columns254   <code class="structfield">is_cycle</code> and <code class="structfield">path</code> to the loop-prone query:255 256</p><pre class="programlisting">257WITH RECURSIVE search_graph(id, link, data, depth, <span class="emphasis"><strong>is_cycle, path</strong></span>) AS (258    SELECT g.id, g.link, g.data, 0,259      <span class="emphasis"><strong>false,260      ARRAY[g.id]</strong></span>261    FROM graph g262  UNION ALL263    SELECT g.id, g.link, g.data, sg.depth + 1,264      <span class="emphasis"><strong>g.id = ANY(path),265      path || g.id</strong></span>266    FROM graph g, search_graph sg267    WHERE g.id = sg.link <span class="emphasis"><strong>AND NOT is_cycle</strong></span>268)269SELECT * FROM search_graph;270</pre><p>271 272   Aside from preventing cycles, the array value is often useful in its own273   right as representing the <span class="quote">“<span class="quote">path</span>”</span> taken to reach any particular row.274  </p><p>275   In the general case where more than one field needs to be checked to276   recognize a cycle, use an array of rows.  For example, if we needed to277   compare fields <code class="structfield">f1</code> and <code class="structfield">f2</code>:278 279</p><pre class="programlisting">280WITH RECURSIVE search_graph(id, link, data, depth, <span class="emphasis"><strong>is_cycle, path</strong></span>) AS (281    SELECT g.id, g.link, g.data, 0,282      <span class="emphasis"><strong>false,283      ARRAY[ROW(g.f1, g.f2)]</strong></span>284    FROM graph g285  UNION ALL286    SELECT g.id, g.link, g.data, sg.depth + 1,287      <span class="emphasis"><strong>ROW(g.f1, g.f2) = ANY(path),288      path || ROW(g.f1, g.f2)</strong></span>289    FROM graph g, search_graph sg290    WHERE g.id = sg.link <span class="emphasis"><strong>AND NOT is_cycle</strong></span>291)292SELECT * FROM search_graph;293</pre><p>294  </p><div class="tip"><h3 class="title">Tip</h3><p>295    Omit the <code class="literal">ROW()</code> syntax in the common case where only one field296    needs to be checked to recognize a cycle.  This allows a simple array297    rather than a composite-type array to be used, gaining efficiency.298   </p></div><p>299   There is built-in syntax to simplify cycle detection.  The above query can300   also be written like this:301</p><pre class="programlisting">302WITH RECURSIVE search_graph(id, link, data, depth) AS (303    SELECT g.id, g.link, g.data, 1304    FROM graph g305  UNION ALL306    SELECT g.id, g.link, g.data, sg.depth + 1307    FROM graph g, search_graph sg308    WHERE g.id = sg.link309) <span class="emphasis"><strong>CYCLE id SET is_cycle USING path</strong></span>310SELECT * FROM search_graph;311</pre><p>312   and it will be internally rewritten to the above form.  The313   <code class="literal">CYCLE</code> clause specifies first the list of columns to314   track for cycle detection, then a column name that will show whether a315   cycle has been detected, and finally the name of another column that will track the316   path.  The cycle and path columns will implicitly be added to the output317   rows of the CTE.318  </p><div class="tip"><h3 class="title">Tip</h3><p>319    The cycle path column is computed in the same way as the depth-first320    ordering column show in the previous section.  A query can have both a321    <code class="literal">SEARCH</code> and a <code class="literal">CYCLE</code> clause, but a322    depth-first search specification and a cycle detection specification would323    create redundant computations, so it's more efficient to just use the324    <code class="literal">CYCLE</code> clause and order by the path column.  If325    breadth-first ordering is wanted, then specifying both326    <code class="literal">SEARCH</code> and <code class="literal">CYCLE</code> can be useful.327   </p></div><p>328   A helpful trick for testing queries329   when you are not certain if they might loop is to place a <code class="literal">LIMIT</code>330   in the parent query.  For example, this query would loop forever without331   the <code class="literal">LIMIT</code>:332 333</p><pre class="programlisting">334WITH RECURSIVE t(n) AS (335    SELECT 1336  UNION ALL337    SELECT n+1 FROM t338)339SELECT n FROM t <span class="emphasis"><strong>LIMIT 100</strong></span>;340</pre><p>341 342   This works because <span class="productname">PostgreSQL</span>'s implementation343   evaluates only as many rows of a <code class="literal">WITH</code> query as are actually344   fetched by the parent query.  Using this trick in production is not345   recommended, because other systems might work differently.  Also, it346   usually won't work if you make the outer query sort the recursive query's347   results or join them to some other table, because in such cases the348   outer query will usually try to fetch all of the <code class="literal">WITH</code> query's349   output anyway.350  </p></div></div><div class="sect2" id="QUERIES-WITH-CTE-MATERIALIZATION"><div class="titlepage"><div><div><h3 class="title">7.8.3. Common Table Expression Materialization <a href="#QUERIES-WITH-CTE-MATERIALIZATION" class="id_link">#</a></h3></div></div></div><p>351   A useful property of <code class="literal">WITH</code> queries is that they are352   normally evaluated only once per execution of the parent query, even if353   they are referred to more than once by the parent query or354   sibling <code class="literal">WITH</code> queries.355   Thus, expensive calculations that are needed in multiple places can be356   placed within a <code class="literal">WITH</code> query to avoid redundant work.  Another357   possible application is to prevent unwanted multiple evaluations of358   functions with side-effects.359   However, the other side of this coin is that the optimizer is not able to360   push restrictions from the parent query down into a multiply-referenced361   <code class="literal">WITH</code> query, since that might affect all uses of the362   <code class="literal">WITH</code> query's output when it should affect only one.363   The multiply-referenced <code class="literal">WITH</code> query will be364   evaluated as written, without suppression of rows that the parent query365   might discard afterwards.  (But, as mentioned above, evaluation might stop366   early if the reference(s) to the query demand only a limited number of367   rows.)368  </p><p>369   However, if a <code class="literal">WITH</code> query is non-recursive and370   side-effect-free (that is, it is a <code class="literal">SELECT</code> containing371   no volatile functions) then it can be folded into the parent query,372   allowing joint optimization of the two query levels.  By default, this373   happens if the parent query references the <code class="literal">WITH</code> query374   just once, but not if it references the <code class="literal">WITH</code> query375   more than once.  You can override that decision by376   specifying <code class="literal">MATERIALIZED</code> to force separate calculation377   of the <code class="literal">WITH</code> query, or by specifying <code class="literal">NOT378   MATERIALIZED</code> to force it to be merged into the parent query.379   The latter choice risks duplicate computation of380   the <code class="literal">WITH</code> query, but it can still give a net savings if381   each usage of the <code class="literal">WITH</code> query needs only a small part382   of the <code class="literal">WITH</code> query's full output.383  </p><p>384   A simple example of these rules is385</p><pre class="programlisting">386WITH w AS (387    SELECT * FROM big_table388)389SELECT * FROM w WHERE key = 123;390</pre><p>391   This <code class="literal">WITH</code> query will be folded, producing the same392   execution plan as393</p><pre class="programlisting">394SELECT * FROM big_table WHERE key = 123;395</pre><p>396   In particular, if there's an index on <code class="structfield">key</code>,397   it will probably be used to fetch just the rows having <code class="literal">key =398   123</code>.  On the other hand, in399</p><pre class="programlisting">400WITH w AS (401    SELECT * FROM big_table402)403SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref404WHERE w2.key = 123;405</pre><p>406   the <code class="literal">WITH</code> query will be materialized, producing a407   temporary copy of <code class="structname">big_table</code> that is then408   joined with itself — without benefit of any index.  This query409   will be executed much more efficiently if written as410</p><pre class="programlisting">411WITH w AS NOT MATERIALIZED (412    SELECT * FROM big_table413)414SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref415WHERE w2.key = 123;416</pre><p>417   so that the parent query's restrictions can be applied directly418   to scans of <code class="structname">big_table</code>.419  </p><p>420   An example where <code class="literal">NOT MATERIALIZED</code> could be421   undesirable is422</p><pre class="programlisting">423WITH w AS (424    SELECT key, very_expensive_function(val) as f FROM some_table425)426SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;427</pre><p>428   Here, materialization of the <code class="literal">WITH</code> query ensures429   that <code class="function">very_expensive_function</code> is evaluated only430   once per table row, not twice.431  </p><p>432   The examples above only show <code class="literal">WITH</code> being used with433   <code class="command">SELECT</code>, but it can be attached in the same way to434   <code class="command">INSERT</code>, <code class="command">UPDATE</code>,435   <code class="command">DELETE</code>, or <code class="command">MERGE</code>.436   In each case it effectively provides temporary table(s) that can437   be referred to in the main command.438  </p></div><div class="sect2" id="QUERIES-WITH-MODIFYING"><div class="titlepage"><div><div><h3 class="title">7.8.4. Data-Modifying Statements in <code class="literal">WITH</code> <a href="#QUERIES-WITH-MODIFYING" class="id_link">#</a></h3></div></div></div><p>439    You can use most data-modifying statements (<code class="command">INSERT</code>,440    <code class="command">UPDATE</code>, or <code class="command">DELETE</code>, but not441    <code class="command">MERGE</code>) in <code class="literal">WITH</code>.  This442    allows you to perform several different operations in the same query.443    An example is:444 445</p><pre class="programlisting">446WITH moved_rows AS (447    DELETE FROM products448    WHERE449        "date" &gt;= '2010-10-01' AND450        "date" &lt; '2010-11-01'451    RETURNING *452)453INSERT INTO products_log454SELECT * FROM moved_rows;455</pre><p>456 457    This query effectively moves rows from <code class="structname">products</code> to458    <code class="structname">products_log</code>.  The <code class="command">DELETE</code> in <code class="literal">WITH</code>459    deletes the specified rows from <code class="structname">products</code>, returning their460    contents by means of its <code class="literal">RETURNING</code> clause; and then the461    primary query reads that output and inserts it into462    <code class="structname">products_log</code>.463   </p><p>464    A fine point of the above example is that the <code class="literal">WITH</code> clause is465    attached to the <code class="command">INSERT</code>, not the sub-<code class="command">SELECT</code> within466    the <code class="command">INSERT</code>.  This is necessary because data-modifying467    statements are only allowed in <code class="literal">WITH</code> clauses that are attached468    to the top-level statement.  However, normal <code class="literal">WITH</code> visibility469    rules apply, so it is possible to refer to the <code class="literal">WITH</code>470    statement's output from the sub-<code class="command">SELECT</code>.471   </p><p>472    Data-modifying statements in <code class="literal">WITH</code> usually have473    <code class="literal">RETURNING</code> clauses (see <a class="xref" href="dml-returning.html" title="6.4. Returning Data from Modified Rows">Section 6.4</a>),474    as shown in the example above.475    It is the output of the <code class="literal">RETURNING</code> clause, <span class="emphasis"><em>not</em></span> the476    target table of the data-modifying statement, that forms the temporary477    table that can be referred to by the rest of the query.  If a478    data-modifying statement in <code class="literal">WITH</code> lacks a <code class="literal">RETURNING</code>479    clause, then it forms no temporary table and cannot be referred to in480    the rest of the query.  Such a statement will be executed nonetheless.481    A not-particularly-useful example is:482 483</p><pre class="programlisting">484WITH t AS (485    DELETE FROM foo486)487DELETE FROM bar;488</pre><p>489 490    This example would remove all rows from tables <code class="structname">foo</code> and491    <code class="structname">bar</code>.  The number of affected rows reported to the client492    would only include rows removed from <code class="structname">bar</code>.493   </p><p>494    Recursive self-references in data-modifying statements are not495    allowed.  In some cases it is possible to work around this limitation by496    referring to the output of a recursive <code class="literal">WITH</code>, for example:497 498</p><pre class="programlisting">499WITH RECURSIVE included_parts(sub_part, part) AS (500    SELECT sub_part, part FROM parts WHERE part = 'our_product'501  UNION ALL502    SELECT p.sub_part, p.part503    FROM included_parts pr, parts p504    WHERE p.part = pr.sub_part505)506DELETE FROM parts507  WHERE part IN (SELECT part FROM included_parts);508</pre><p>509 510    This query would remove all direct and indirect subparts of a product.511   </p><p>512    Data-modifying statements in <code class="literal">WITH</code> are executed exactly once,513    and always to completion, independently of whether the primary query514    reads all (or indeed any) of their output.  Notice that this is different515    from the rule for <code class="command">SELECT</code> in <code class="literal">WITH</code>: as stated in the516    previous section, execution of a <code class="command">SELECT</code> is carried only as far517    as the primary query demands its output.518   </p><p>519    The sub-statements in <code class="literal">WITH</code> are executed concurrently with520    each other and with the main query.  Therefore, when using data-modifying521    statements in <code class="literal">WITH</code>, the order in which the specified updates522    actually happen is unpredictable.  All the statements are executed with523    the same <em class="firstterm">snapshot</em> (see <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>), so they524    cannot <span class="quote">“<span class="quote">see</span>”</span> one another's effects on the target tables.  This525    alleviates the effects of the unpredictability of the actual order of row526    updates, and means that <code class="literal">RETURNING</code> data is the only way to527    communicate changes between different <code class="literal">WITH</code> sub-statements and528    the main query.  An example of this is that in529 530</p><pre class="programlisting">531WITH t AS (532    UPDATE products SET price = price * 1.05533    RETURNING *534)535SELECT * FROM products;536</pre><p>537 538    the outer <code class="command">SELECT</code> would return the original prices before the539    action of the <code class="command">UPDATE</code>, while in540 541</p><pre class="programlisting">542WITH t AS (543    UPDATE products SET price = price * 1.05544    RETURNING *545)546SELECT * FROM t;547</pre><p>548 549    the outer <code class="command">SELECT</code> would return the updated data.550   </p><p>551    Trying to update the same row twice in a single statement is not552    supported.  Only one of the modifications takes place, but it is not easy553    (and sometimes not possible) to reliably predict which one.  This also554    applies to deleting a row that was already updated in the same statement:555    only the update is performed.  Therefore you should generally avoid trying556    to modify a single row twice in a single statement.  In particular avoid557    writing <code class="literal">WITH</code> sub-statements that could affect the same rows558    changed by the main statement or a sibling sub-statement.  The effects559    of such a statement will not be predictable.560   </p><p>561    At present, any table used as the target of a data-modifying statement in562    <code class="literal">WITH</code> must not have a conditional rule, nor an <code class="literal">ALSO</code>563    rule, nor an <code class="literal">INSTEAD</code> rule that expands to multiple statements.564   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="queries-values.html" title="7.7. VALUES Lists">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype.html" title="Chapter 8. Data Types">Next</a></td></tr><tr><td width="40%" align="left" valign="top">7.7. <code class="literal">VALUES</code> Lists </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"> Chapter 8. Data Types</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai