Movatterモバイル変換

7.8.1.SELECT inWITH

7.8.2. Recursive Queries

7.8.3. Common Table Expression Materialization

7.8.4. Data-Modifying Statements inWITH

The basic value ofSELECT inWITH is to break down complicated queries into simpler parts. An example is:

WITH regional_sales AS (    SELECT region, SUM(amount) AS total_sales    FROM orders    GROUP BY region), top_regions AS (    SELECT region    FROM regional_sales    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region,       product,       SUM(quantity) AS product_units,       SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

which displays per-product sales totals in only the top sales regions. TheWITH clause defines two auxiliary statements namedregional_sales andtop_regions, where the output ofregional_sales is used intop_regions and the output oftop_regions is used in the primarySELECT query. This example could have been written withoutWITH, but we'd have needed two levels of nested sub-SELECTs. It's a bit easier to follow this way.

The optionalRECURSIVE modifier changesWITH from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. UsingRECURSIVE, aWITH query can refer to its own output. A very simple example is this query to sum the integers from 1 through 100:

WITH RECURSIVE t(n) AS (    VALUES (1)  UNION ALL    SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

The general form of a recursiveWITH query is always anon-recursive term, thenUNION (orUNION ALL), then arecursive term, where only the recursive term can contain a reference to the query's own output. Such a query is executed as follows:

In the example above, the working table has just a single row in each step, and it takes on the values from 1 through 100 in successive steps. In the 100th step, there is no output because of theWHERE clause, and so the query terminates.

Recursive queries are typically used to deal with hierarchical or tree-structured data. A useful example is this query to find all the direct and indirect sub-parts of a product, given only a table that shows immediate inclusions:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'  UNION ALL    SELECT p.sub_part, p.part, p.quantity * pr.quantity    FROM included_parts pr, parts p    WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

WITH RECURSIVE search_tree(id, link, data) AS (    SELECT t.id, t.link, t.data    FROM tree t  UNION ALL    SELECT t.id, t.link, t.data    FROM tree t, search_tree st    WHERE t.id = st.link)SELECT * FROM search_tree;

WITH RECURSIVE search_tree(id, link, data,path) AS (    SELECT t.id, t.link, t.data,ARRAY[t.id]    FROM tree t  UNION ALL    SELECT t.id, t.link, t.data,path || t.id    FROM tree t, search_tree st    WHERE t.id = st.link)SELECT * FROM search_treeORDER BY path;

WITH RECURSIVE search_tree(id, link, data,path) AS (    SELECT t.id, t.link, t.data,ARRAY[ROW(t.f1, t.f2)]    FROM tree t  UNION ALL    SELECT t.id, t.link, t.data,path || ROW(t.f1, t.f2)    FROM tree t, search_tree st    WHERE t.id = st.link)SELECT * FROM search_treeORDER BY path;

WITH RECURSIVE search_tree(id, link, data,depth) AS (    SELECT t.id, t.link, t.data,0    FROM tree t  UNION ALL    SELECT t.id, t.link, t.data,depth + 1    FROM tree t, search_tree st    WHERE t.id = st.link)SELECT * FROM search_treeORDER BY depth;

WITH RECURSIVE search_tree(id, link, data) AS (    SELECT t.id, t.link, t.data    FROM tree t  UNION ALL    SELECT t.id, t.link, t.data    FROM tree t, search_tree st    WHERE t.id = st.link)SEARCH DEPTH FIRST BY id SET ordercolSELECT * FROM search_tree ORDER BY ordercol;WITH RECURSIVE search_tree(id, link, data) AS (    SELECT t.id, t.link, t.data    FROM tree t  UNION ALL    SELECT t.id, t.link, t.data    FROM tree t, search_tree st    WHERE t.id = st.link)SEARCH BREADTH FIRST BY id SET ordercolSELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_graph(id, link, data, depth) AS (    SELECT g.id, g.link, g.data, 0    FROM graph g  UNION ALL    SELECT g.id, g.link, g.data, sg.depth + 1    FROM graph g, search_graph sg    WHERE g.id = sg.link)SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth,is_cycle, path) AS (    SELECT g.id, g.link, g.data, 0,false,      ARRAY[g.id]    FROM graph g  UNION ALL    SELECT g.id, g.link, g.data, sg.depth + 1,g.id = ANY(path),      path || g.id    FROM graph g, search_graph sg    WHERE g.id = sg.linkAND NOT is_cycle)SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth,is_cycle, path) AS (    SELECT g.id, g.link, g.data, 0,false,      ARRAY[ROW(g.f1, g.f2)]    FROM graph g  UNION ALL    SELECT g.id, g.link, g.data, sg.depth + 1,ROW(g.f1, g.f2) = ANY(path),      path || ROW(g.f1, g.f2)    FROM graph g, search_graph sg    WHERE g.id = sg.linkAND NOT is_cycle)SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth) AS (    SELECT g.id, g.link, g.data, 1    FROM graph g  UNION ALL    SELECT g.id, g.link, g.data, sg.depth + 1    FROM graph g, search_graph sg    WHERE g.id = sg.link)CYCLE id SET is_cycle USING pathSELECT * FROM search_graph;

WITH RECURSIVE t(n) AS (    SELECT 1  UNION ALL    SELECT n+1 FROM t)SELECT n FROM tLIMIT 100;

A useful property ofWITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or siblingWITH queries. Thus, expensive calculations that are needed in multiple places can be placed within aWITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is not able to push restrictions from the parent query down into a multiply-referencedWITH query, since that might affect all uses of theWITH query's output when it should affect only one. The multiply-referencedWITH query will be evaluated as written, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)

However, if aWITH query is non-recursive and side-effect-free (that is, it is aSELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references theWITH query just once, but not if it references theWITH query more than once. You can override that decision by specifyingMATERIALIZED to force separate calculation of theWITH query, or by specifyingNOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of theWITH query, but it can still give a net savings if each usage of theWITH query needs only a small part of theWITH query's full output.

A simple example of these rules is

WITH w AS (    SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

ThisWITH query will be folded, producing the same execution plan as

SELECT * FROM big_table WHERE key = 123;

In particular, if there's an index onkey, it will probably be used to fetch just the rows havingkey = 123. On the other hand, in

WITH w AS (    SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

theWITH query will be materialized, producing a temporary copy ofbig_table that is then joined with itself — without benefit of any index. This query will be executed much more efficiently if written as

WITH w AS NOT MATERIALIZED (    SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

so that the parent query's restrictions can be applied directly to scans ofbig_table.

An example whereNOT MATERIALIZED could be undesirable is

WITH w AS (    SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Here, materialization of theWITH query ensures thatvery_expensive_function is evaluated only once per table row, not twice.

The examples above only showWITH being used withSELECT, but it can be attached in the same way toINSERT,UPDATE,DELETE, orMERGE. In each case it effectively provides temporary table(s) that can be referred to in the main command.

You can use data-modifying statements (INSERT,UPDATE,DELETE, orMERGE) inWITH. This allows you to perform several different operations in the same query. An example is:

WITH moved_rows AS (    DELETE FROM products    WHERE        "date" >= '2010-10-01' AND        "date" < '2010-11-01'    RETURNING *)INSERT INTO products_logSELECT * FROM moved_rows;

This query effectively moves rows fromproducts toproducts_log. TheDELETE inWITH deletes the specified rows fromproducts, returning their contents by means of itsRETURNING clause; and then the primary query reads that output and inserts it intoproducts_log.

A fine point of the above example is that theWITH clause is attached to theINSERT, not the sub-SELECT within theINSERT. This is necessary because data-modifying statements are only allowed inWITH clauses that are attached to the top-level statement. However, normalWITH visibility rules apply, so it is possible to refer to theWITH statement's output from the sub-SELECT.

Data-modifying statements inWITH usually haveRETURNING clauses (seeSection 6.4), as shown in the example above. It is the output of theRETURNING clause,not the target table of the data-modifying statement, that forms the temporary table that can be referred to by the rest of the query. If a data-modifying statement inWITH lacks aRETURNING clause, then it forms no temporary table and cannot be referred to in the rest of the query. Such a statement will be executed nonetheless. A not-particularly-useful example is:

WITH t AS (    DELETE FROM foo)DELETE FROM bar;

This example would remove all rows from tablesfoo andbar. The number of affected rows reported to the client would only include rows removed frombar.

Recursive self-references in data-modifying statements are not allowed. In some cases it is possible to work around this limitation by referring to the output of a recursiveWITH, for example:

WITH RECURSIVE included_parts(sub_part, part) AS (    SELECT sub_part, part FROM parts WHERE part = 'our_product'  UNION ALL    SELECT p.sub_part, p.part    FROM included_parts pr, parts p    WHERE p.part = pr.sub_part)DELETE FROM parts  WHERE part IN (SELECT part FROM included_parts);

This query would remove all direct and indirect subparts of a product.

Data-modifying statements inWITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule forSELECT inWITH: as stated in the previous section, execution of aSELECT is carried only as far as the primary query demands its output.

The sub-statements inWITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements inWITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the samesnapshot (seeChapter 13), so they cannot“see” one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means thatRETURNING data is the only way to communicate changes between differentWITH sub-statements and the main query. An example of this is that in

WITH t AS (    UPDATE products SET price = price * 1.05    RETURNING *)SELECT * FROM products;

the outerSELECT would return the original prices before the action of theUPDATE, while in

WITH t AS (    UPDATE products SET price = price * 1.05    RETURNING *)SELECT * FROM t;

the outerSELECT would return the updated data.

Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same statement: only the update is performed. Therefore you should generally avoid trying to modify a single row twice in a single statement. In particular avoid writingWITH sub-statements that could affect the same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not be predictable.

At present, any table used as the target of a data-modifying statement inWITH must not have a conditional rule, nor anALSO rule, nor anINSTEAD rule that expands to multiple statements.