1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
|
<?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>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.2 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>
<code class="literal">WITH</code> provides a way to write auxiliary statements for use in a
larger query. These statements, which are often referred to as Common
Table Expressions or <acronym class="acronym">CTE</acronym>s, can be thought of as defining
temporary tables that exist just for one query. Each auxiliary statement
in a <code class="literal">WITH</code> clause can be a <code class="command">SELECT</code>,
<code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>; and the
<code class="literal">WITH</code> clause itself is attached to a primary statement that can
be a <code class="command">SELECT</code>, <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
<code class="command">DELETE</code>, or <code class="command">MERGE</code>.
</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>
The basic value of <code class="command">SELECT</code> in <code class="literal">WITH</code> is to
break down complicated queries into simpler parts. An example is:
</p><pre class="programlisting">
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_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
</pre><p>
which displays per-product sales totals in only the top sales regions.
The <code class="literal">WITH</code> clause defines two auxiliary statements named
<code class="structname">regional_sales</code> and <code class="structname">top_regions</code>,
where the output of <code class="structname">regional_sales</code> is used in
<code class="structname">top_regions</code> and the output of <code class="structname">top_regions</code>
is used in the primary <code class="command">SELECT</code> query.
This example could have been written without <code class="literal">WITH</code>,
but we'd have needed two levels of nested sub-<code class="command">SELECT</code>s. It's a bit
easier to follow this way.
</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>
<a id="id-1.5.6.12.6.2.1" class="indexterm"></a>
The optional <code class="literal">RECURSIVE</code> modifier changes <code class="literal">WITH</code>
from a mere syntactic convenience into a feature that accomplishes
things not otherwise possible in standard SQL. Using
<code class="literal">RECURSIVE</code>, a <code class="literal">WITH</code> query can refer to its own
output. A very simple example is this query to sum the integers from 1
through 100:
</p><pre class="programlisting">
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
</pre><p>
The general form of a recursive <code class="literal">WITH</code> query is always a
<em class="firstterm">non-recursive term</em>, then <code class="literal">UNION</code> (or
<code class="literal">UNION ALL</code>), then a
<em class="firstterm">recursive term</em>, where only the recursive term can contain
a reference to the query's own output. Such a query is executed as
follows:
</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>
Evaluate the non-recursive term. For <code class="literal">UNION</code> (but not
<code class="literal">UNION ALL</code>), discard duplicate rows. Include all remaining
rows in the result of the recursive query, and also place them in a
temporary <em class="firstterm">working table</em>.
</p></li><li class="step"><p>
So long as the working table is not empty, repeat these steps:
</p><ol type="a" class="substeps"><li class="step"><p>
Evaluate the recursive term, substituting the current contents of
the working table for the recursive self-reference.
For <code class="literal">UNION</code> (but not <code class="literal">UNION ALL</code>), discard
duplicate rows and rows that duplicate any previous result row.
Include all remaining rows in the result of the recursive query, and
also place them in a temporary <em class="firstterm">intermediate table</em>.
</p></li><li class="step"><p>
Replace the contents of the working table with the contents of the
intermediate table, then empty the intermediate table.
</p></li></ol></li></ol></div><div class="note"><h3 class="title">Note</h3><p>
While <code class="literal">RECURSIVE</code> allows queries to be specified
recursively, internally such queries are evaluated iteratively.
</p></div><p>
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 the <code class="literal">WHERE</code>
clause, and so the query terminates.
</p><p>
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:
</p><pre class="programlisting">
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_quantity
FROM included_parts
GROUP BY sub_part
</pre><p>
</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>
When computing a tree traversal using a recursive query, you might want to
order the results in either depth-first or breadth-first order. This can
be done by computing an ordering column alongside the other data columns
and using that to sort the results at the end. Note that this does not
actually control in which order the query evaluation visits the rows; that
is as always in SQL implementation-dependent. This approach merely
provides a convenient way to order the results afterwards.
</p><p>
To create a depth-first order, we compute for each result row an array of
rows that we have visited so far. For example, consider the following
query that searches a table <code class="structname">tree</code> using a
<code class="structfield">link</code> field:
</p><pre class="programlisting">
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;
</pre><p>
To add depth-first ordering information, you can write this:
</p><pre class="programlisting">
WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>path</strong></span>) AS (
SELECT t.id, t.link, t.data, <span class="emphasis"><strong>ARRAY[t.id]</strong></span>
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data, <span class="emphasis"><strong>path || t.id</strong></span>
FROM tree t, search_tree st
WHERE t.id = st.link
)
SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY path</strong></span>;
</pre><p>
</p><p>
In the general case where more than one field needs to be used to identify
a row, use an array of rows. For example, if we needed to track fields
<code class="structfield">f1</code> and <code class="structfield">f2</code>:
</p><pre class="programlisting">
WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>path</strong></span>) AS (
SELECT t.id, t.link, t.data, <span class="emphasis"><strong>ARRAY[ROW(t.f1, t.f2)]</strong></span>
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data, <span class="emphasis"><strong>path || ROW(t.f1, t.f2)</strong></span>
FROM tree t, search_tree st
WHERE t.id = st.link
)
SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY path</strong></span>;
</pre><p>
</p><div class="tip"><h3 class="title">Tip</h3><p>
Omit the <code class="literal">ROW()</code> syntax in the common case where only one
field needs to be tracked. This allows a simple array rather than a
composite-type array to be used, gaining efficiency.
</p></div><p>
To create a breadth-first order, you can add a column that tracks the depth
of the search, for example:
</p><pre class="programlisting">
WITH RECURSIVE search_tree(id, link, data, <span class="emphasis"><strong>depth</strong></span>) AS (
SELECT t.id, t.link, t.data, <span class="emphasis"><strong>0</strong></span>
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data, <span class="emphasis"><strong>depth + 1</strong></span>
FROM tree t, search_tree st
WHERE t.id = st.link
)
SELECT * FROM search_tree <span class="emphasis"><strong>ORDER BY depth</strong></span>;
</pre><p>
To get a stable sort, add data columns as secondary sorting columns.
</p><div class="tip"><h3 class="title">Tip</h3><p>
The recursive query evaluation algorithm produces its output in
breadth-first search order. However, this is an implementation detail and
it is perhaps unsound to rely on it. The order of the rows within each
level is certainly undefined, so some explicit ordering might be desired
in any case.
</p></div><p>
There is built-in syntax to compute a depth- or breadth-first sort column.
For example:
</p><pre class="programlisting">
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
) <span class="emphasis"><strong>SEARCH DEPTH FIRST BY id SET ordercol</strong></span>
SELECT * 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
) <span class="emphasis"><strong>SEARCH BREADTH FIRST BY id SET ordercol</strong></span>
SELECT * FROM search_tree ORDER BY ordercol;
</pre><p>
This syntax is internally expanded to something similar to the above
hand-written forms. The <code class="literal">SEARCH</code> clause specifies whether
depth- or breadth first search is wanted, the list of columns to track for
sorting, and a column name that will contain the result data that can be
used for sorting. That column will implicitly be added to the output rows
of the CTE.
</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>
When working with recursive queries it is important to be sure that
the recursive part of the query will eventually return no tuples,
or else the query will loop indefinitely. Sometimes, using
<code class="literal">UNION</code> instead of <code class="literal">UNION ALL</code> can accomplish this
by discarding rows that duplicate previous output rows. However, often a
cycle does not involve output rows that are completely duplicate: it may be
necessary to check just one or a few fields to see if the same point has
been reached before. The standard method for handling such situations is
to compute an array of the already-visited values. For example, consider again
the following query that searches a table <code class="structname">graph</code> using a
<code class="structfield">link</code> field:
</p><pre class="programlisting">
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;
</pre><p>
This query will loop if the <code class="structfield">link</code> relationships contain
cycles. Because we require a <span class="quote">“<span class="quote">depth</span>”</span> output, just changing
<code class="literal">UNION ALL</code> to <code class="literal">UNION</code> would not eliminate the looping.
Instead we need to recognize whether we have reached the same row again
while following a particular path of links. We add two columns
<code class="structfield">is_cycle</code> and <code class="structfield">path</code> to the loop-prone query:
</p><pre class="programlisting">
WITH RECURSIVE search_graph(id, link, data, depth, <span class="emphasis"><strong>is_cycle, path</strong></span>) AS (
SELECT g.id, g.link, g.data, 0,
<span class="emphasis"><strong>false,
ARRAY[g.id]</strong></span>
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1,
<span class="emphasis"><strong>g.id = ANY(path),
path || g.id</strong></span>
FROM graph g, search_graph sg
WHERE g.id = sg.link <span class="emphasis"><strong>AND NOT is_cycle</strong></span>
)
SELECT * FROM search_graph;
</pre><p>
Aside from preventing cycles, the array value is often useful in its own
right as representing the <span class="quote">“<span class="quote">path</span>”</span> taken to reach any particular row.
</p><p>
In the general case where more than one field needs to be checked to
recognize a cycle, use an array of rows. For example, if we needed to
compare fields <code class="structfield">f1</code> and <code class="structfield">f2</code>:
</p><pre class="programlisting">
WITH RECURSIVE search_graph(id, link, data, depth, <span class="emphasis"><strong>is_cycle, path</strong></span>) AS (
SELECT g.id, g.link, g.data, 0,
<span class="emphasis"><strong>false,
ARRAY[ROW(g.f1, g.f2)]</strong></span>
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1,
<span class="emphasis"><strong>ROW(g.f1, g.f2) = ANY(path),
path || ROW(g.f1, g.f2)</strong></span>
FROM graph g, search_graph sg
WHERE g.id = sg.link <span class="emphasis"><strong>AND NOT is_cycle</strong></span>
)
SELECT * FROM search_graph;
</pre><p>
</p><div class="tip"><h3 class="title">Tip</h3><p>
Omit the <code class="literal">ROW()</code> syntax in the common case where only one field
needs to be checked to recognize a cycle. This allows a simple array
rather than a composite-type array to be used, gaining efficiency.
</p></div><p>
There is built-in syntax to simplify cycle detection. The above query can
also be written like this:
</p><pre class="programlisting">
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
) <span class="emphasis"><strong>CYCLE id SET is_cycle USING path</strong></span>
SELECT * FROM search_graph;
</pre><p>
and it will be internally rewritten to the above form. The
<code class="literal">CYCLE</code> clause specifies first the list of columns to
track for cycle detection, then a column name that will show whether a
cycle has been detected, and finally the name of another column that will track the
path. The cycle and path columns will implicitly be added to the output
rows of the CTE.
</p><div class="tip"><h3 class="title">Tip</h3><p>
The cycle path column is computed in the same way as the depth-first
ordering column show in the previous section. A query can have both a
<code class="literal">SEARCH</code> and a <code class="literal">CYCLE</code> clause, but a
depth-first search specification and a cycle detection specification would
create redundant computations, so it's more efficient to just use the
<code class="literal">CYCLE</code> clause and order by the path column. If
breadth-first ordering is wanted, then specifying both
<code class="literal">SEARCH</code> and <code class="literal">CYCLE</code> can be useful.
</p></div><p>
A helpful trick for testing queries
when you are not certain if they might loop is to place a <code class="literal">LIMIT</code>
in the parent query. For example, this query would loop forever without
the <code class="literal">LIMIT</code>:
</p><pre class="programlisting">
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t <span class="emphasis"><strong>LIMIT 100</strong></span>;
</pre><p>
This works because <span class="productname">PostgreSQL</span>'s implementation
evaluates only as many rows of a <code class="literal">WITH</code> query as are actually
fetched by the parent query. Using this trick in production is not
recommended, because other systems might work differently. Also, it
usually won't work if you make the outer query sort the recursive query's
results or join them to some other table, because in such cases the
outer query will usually try to fetch all of the <code class="literal">WITH</code> query's
output anyway.
</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>
A useful property of <code class="literal">WITH</code> 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
sibling <code class="literal">WITH</code> queries.
Thus, expensive calculations that are needed in multiple places can be
placed within a <code class="literal">WITH</code> 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-referenced
<code class="literal">WITH</code> query, since that might affect all uses of the
<code class="literal">WITH</code> query's output when it should affect only one.
The multiply-referenced <code class="literal">WITH</code> 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.)
</p><p>
However, if a <code class="literal">WITH</code> query is non-recursive and
side-effect-free (that is, it is a <code class="literal">SELECT</code> 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 the <code class="literal">WITH</code> query
just once, but not if it references the <code class="literal">WITH</code> query
more than once. You can override that decision by
specifying <code class="literal">MATERIALIZED</code> to force separate calculation
of the <code class="literal">WITH</code> query, or by specifying <code class="literal">NOT
MATERIALIZED</code> to force it to be merged into the parent query.
The latter choice risks duplicate computation of
the <code class="literal">WITH</code> query, but it can still give a net savings if
each usage of the <code class="literal">WITH</code> query needs only a small part
of the <code class="literal">WITH</code> query's full output.
</p><p>
A simple example of these rules is
</p><pre class="programlisting">
WITH w AS (
SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;
</pre><p>
This <code class="literal">WITH</code> query will be folded, producing the same
execution plan as
</p><pre class="programlisting">
SELECT * FROM big_table WHERE key = 123;
</pre><p>
In particular, if there's an index on <code class="structfield">key</code>,
it will probably be used to fetch just the rows having <code class="literal">key =
123</code>. On the other hand, in
</p><pre class="programlisting">
WITH w AS (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
</pre><p>
the <code class="literal">WITH</code> query will be materialized, producing a
temporary copy of <code class="structname">big_table</code> that is then
joined with itself — without benefit of any index. This query
will be executed much more efficiently if written as
</p><pre class="programlisting">
WITH w AS NOT MATERIALIZED (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
</pre><p>
so that the parent query's restrictions can be applied directly
to scans of <code class="structname">big_table</code>.
</p><p>
An example where <code class="literal">NOT MATERIALIZED</code> could be
undesirable is
</p><pre class="programlisting">
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;
</pre><p>
Here, materialization of the <code class="literal">WITH</code> query ensures
that <code class="function">very_expensive_function</code> is evaluated only
once per table row, not twice.
</p><p>
The examples above only show <code class="literal">WITH</code> being used with
<code class="command">SELECT</code>, but it can be attached in the same way to
<code class="command">INSERT</code>, <code class="command">UPDATE</code>,
<code class="command">DELETE</code>, or <code class="command">MERGE</code>.
In each case it effectively provides temporary table(s) that can
be referred to in the main command.
</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>
You can use most data-modifying statements (<code class="command">INSERT</code>,
<code class="command">UPDATE</code>, or <code class="command">DELETE</code>, but not
<code class="command">MERGE</code>) in <code class="literal">WITH</code>. This
allows you to perform several different operations in the same query.
An example is:
</p><pre class="programlisting">
WITH moved_rows AS (
DELETE FROM products
WHERE
"date" >= '2010-10-01' AND
"date" < '2010-11-01'
RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
</pre><p>
This query effectively moves rows from <code class="structname">products</code> to
<code class="structname">products_log</code>. The <code class="command">DELETE</code> in <code class="literal">WITH</code>
deletes the specified rows from <code class="structname">products</code>, returning their
contents by means of its <code class="literal">RETURNING</code> clause; and then the
primary query reads that output and inserts it into
<code class="structname">products_log</code>.
</p><p>
A fine point of the above example is that the <code class="literal">WITH</code> clause is
attached to the <code class="command">INSERT</code>, not the sub-<code class="command">SELECT</code> within
the <code class="command">INSERT</code>. This is necessary because data-modifying
statements are only allowed in <code class="literal">WITH</code> clauses that are attached
to the top-level statement. However, normal <code class="literal">WITH</code> visibility
rules apply, so it is possible to refer to the <code class="literal">WITH</code>
statement's output from the sub-<code class="command">SELECT</code>.
</p><p>
Data-modifying statements in <code class="literal">WITH</code> usually have
<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>),
as shown in the example above.
It is the output of the <code class="literal">RETURNING</code> clause, <span class="emphasis"><em>not</em></span> 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 in <code class="literal">WITH</code> lacks a <code class="literal">RETURNING</code>
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:
</p><pre class="programlisting">
WITH t AS (
DELETE FROM foo
)
DELETE FROM bar;
</pre><p>
This example would remove all rows from tables <code class="structname">foo</code> and
<code class="structname">bar</code>. The number of affected rows reported to the client
would only include rows removed from <code class="structname">bar</code>.
</p><p>
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 recursive <code class="literal">WITH</code>, for example:
</p><pre class="programlisting">
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);
</pre><p>
This query would remove all direct and indirect subparts of a product.
</p><p>
Data-modifying statements in <code class="literal">WITH</code> 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 for <code class="command">SELECT</code> in <code class="literal">WITH</code>: as stated in the
previous section, execution of a <code class="command">SELECT</code> is carried only as far
as the primary query demands its output.
</p><p>
The sub-statements in <code class="literal">WITH</code> are executed concurrently with
each other and with the main query. Therefore, when using data-modifying
statements in <code class="literal">WITH</code>, the order in which the specified updates
actually happen is unpredictable. All the statements are executed with
the same <em class="firstterm">snapshot</em> (see <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>), so they
cannot <span class="quote">“<span class="quote">see</span>”</span> one another's effects on the target tables. This
alleviates the effects of the unpredictability of the actual order of row
updates, and means that <code class="literal">RETURNING</code> data is the only way to
communicate changes between different <code class="literal">WITH</code> sub-statements and
the main query. An example of this is that in
</p><pre class="programlisting">
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;
</pre><p>
the outer <code class="command">SELECT</code> would return the original prices before the
action of the <code class="command">UPDATE</code>, while in
</p><pre class="programlisting">
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM t;
</pre><p>
the outer <code class="command">SELECT</code> would return the updated data.
</p><p>
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
writing <code class="literal">WITH</code> 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.
</p><p>
At present, any table used as the target of a data-modifying statement in
<code class="literal">WITH</code> must not have a conditional rule, nor an <code class="literal">ALSO</code>
rule, nor an <code class="literal">INSTEAD</code> rule that expands to multiple statements.
</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.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 8. Data Types</td></tr></table></div></body></html>
|
