Skip to content

Commit 07f1264

Browse files
tglsfdctglsfdc
committed
Allow WITH clauses to be attached to INSERT, UPDATE, DELETE statements.
This is not the hoped-for facility of using INSERT/UPDATE/DELETE inside a WITH, but rather the other way around. It seems useful in its own right anyway. Note: catversion bumped because, although the contents of stored rules might look compatible, there's actually a subtle semantic change. A single Query containing a WITH and INSERT...VALUES now represents writing the WITH before the INSERT, not before the VALUES. While it's not clear that that matters to anyone, it seems like a good idea to have it cited in the git history for catversion.h. Original patch by Marko Tiikkaja, with updating and cleanup by Hitoshi Harada.
1 parent 6ab42ae commit 07f1264

File tree

14 files changed

+333
-44
lines changed

14 files changed

+333
-44
lines changed

‎doc/src/sgml/queries.sgml

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -472,7 +472,7 @@ FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r
472472
(1 row)
473473
</screen>
474474
This is because a restriction placed in the <literal>ON</>
475-
clause is processed <emphasis>before</> the join, while
475+
clause is processed <emphasis>before</> the join, while
476476
a restriction placed in the <literal>WHERE</> clause is processed
477477
<emphasis>after</> the join.
478478
</para>
@@ -1139,7 +1139,7 @@ SELECT a "value", b + c AS sum FROM ...
11391139
<para>
11401140
The naming of output columns here is different from that done in
11411141
the <literal>FROM</> clause (see <xref
1142-
linkend="queries-table-aliases">). It is possible
1142+
linkend="queries-table-aliases">). It is possible
11431143
to rename the same column twice, but the name assigned in
11441144
the select list is the one that will be passed on.
11451145
</para>
@@ -1539,7 +1539,7 @@ SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
15391539

15401540
<para>
15411541
<literal>WITH</> provides a way to write subqueries for use in a larger
1542-
<literal>SELECT</> query. The subqueries can be thought of as defining
1542+
query. The subqueries can be thought of as defining
15431543
temporary tables that exist just for this query. One use of this feature
15441544
is to break down complicated queries into simpler parts. An example is:
15451545

@@ -1791,12 +1791,20 @@ SELECT n FROM t LIMIT 100;
17911791
However, the other side of this coin is that the optimizer is less able to
17921792
push restrictions from the parent query down into a <literal>WITH</> query
17931793
than an ordinary sub-query. The <literal>WITH</> query will generally be
1794-
evaluated as stated, without suppression of rows that the parent query
1794+
evaluated as written, without suppression of rows that the parent query
17951795
might discard afterwards. (But, as mentioned above, evaluation might stop
17961796
early if the reference(s) to the query demand only a limited number of
17971797
rows.)
17981798
</para>
17991799

1800+
<para>
1801+
The examples above only show <literal>WITH</> being used with
1802+
<command>SELECT</>, but it can be attached in the same way to
1803+
<command>INSERT</>, <command>UPDATE</>, or <command>DELETE</>.
1804+
In each case it effectively provides temporary table(s) that can
1805+
be referred to in the main command.
1806+
</para>
1807+
18001808
</sect1>
18011809

18021810
</chapter>

‎doc/src/sgml/ref/delete.sgml

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24+
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
2425
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2526
[ USING <replaceable class="PARAMETER">using_list</replaceable> ]
2627
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
@@ -83,6 +84,18 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
8384
<title>Parameters</title>
8485

8586
<variablelist>
87+
<varlistentry>
88+
<term><replaceable class="parameter">with_query</replaceable></term>
89+
<listitem>
90+
<para>
91+
The <literal>WITH</literal> clause allows you to specify one or more
92+
subqueries that can be referenced by name in the <command>DELETE</>
93+
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
94+
for details.
95+
</para>
96+
</listitem>
97+
</varlistentry>
98+
8699
<varlistentry>
87100
<term><literal>ONLY</></term>
88101
<listitem>
@@ -272,7 +285,8 @@ DELETE FROM tasks WHERE CURRENT OF c_tasks;
272285
<para>
273286
This command conforms to the <acronym>SQL</acronym> standard, except
274287
that the <literal>USING</literal> and <literal>RETURNING</> clauses
275-
are <productname>PostgreSQL</productname> extensions.
288+
are <productname>PostgreSQL</productname> extensions, as is the ability
289+
to use <literal>WITH</> with <command>DELETE</>.
276290
</para>
277291
</refsect1>
278292
</refentry>

‎doc/src/sgml/ref/insert.sgml

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24+
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
2425
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
2526
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
2627
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -84,6 +85,26 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
8485
<title>Parameters</title>
8586

8687
<variablelist>
88+
<varlistentry>
89+
<term><replaceable class="parameter">with_query</replaceable></term>
90+
<listitem>
91+
<para>
92+
The <literal>WITH</literal> clause allows you to specify one or more
93+
subqueries that can be referenced by name in the <command>INSERT</>
94+
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
95+
for details.
96+
</para>
97+
<para>
98+
It is possible for the <replaceable class="parameter">query</replaceable>
99+
(<command>SELECT</command> statement)
100+
to also contain a <literal>WITH</literal> clause. In such a case both
101+
sets of <replaceable>with_query</replaceable> can be referenced within
102+
the <replaceable class="parameter">query</replaceable>, but the
103+
second one takes precedence since it is more closely nested.
104+
</para>
105+
</listitem>
106+
</varlistentry>
107+
87108
<varlistentry>
88109
<term><replaceable class="PARAMETER">table</replaceable></term>
89110
<listitem>
@@ -287,7 +308,9 @@ INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
287308
<para>
288309
<command>INSERT</command> conforms to the SQL standard, except that
289310
the <literal>RETURNING</> clause is a
290-
<productname>PostgreSQL</productname> extension. Also, the case in
311+
<productname>PostgreSQL</productname> extension, as is the ability
312+
to use <literal>WITH</> with <command>INSERT</>.
313+
Also, the case in
291314
which a column name list is omitted, but not all the columns are
292315
filled from the <literal>VALUES</> clause or <replaceable>query</>,
293316
is disallowed by the standard.

‎doc/src/sgml/ref/update.sgml

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24+
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
2425
UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2526
SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
2627
( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
@@ -79,6 +80,18 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
7980
<title>Parameters</title>
8081

8182
<variablelist>
83+
<varlistentry>
84+
<term><replaceable class="parameter">with_query</replaceable></term>
85+
<listitem>
86+
<para>
87+
The <literal>WITH</literal> clause allows you to specify one or more
88+
subqueries that can be referenced by name in the <command>UPDATE</>
89+
query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
90+
for details.
91+
</para>
92+
</listitem>
93+
</varlistentry>
94+
8295
<varlistentry>
8396
<term><replaceable class="PARAMETER">table</replaceable></term>
8497
<listitem>
@@ -345,7 +358,8 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
345358
<para>
346359
This command conforms to the <acronym>SQL</acronym> standard, except
347360
that the <literal>FROM</literal> and <literal>RETURNING</> clauses
348-
are <productname>PostgreSQL</productname> extensions.
361+
are <productname>PostgreSQL</productname> extensions, as is the ability
362+
to use <literal>WITH</> with <command>UPDATE</>.
349363
</para>
350364

351365
<para>

‎src/backend/nodes/copyfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2313,6 +2313,7 @@ _copyInsertStmt(InsertStmt *from)
23132313
COPY_NODE_FIELD(cols);
23142314
COPY_NODE_FIELD(selectStmt);
23152315
COPY_NODE_FIELD(returningList);
2316+
COPY_NODE_FIELD(withClause);
23162317

23172318
return newnode;
23182319
}
@@ -2326,6 +2327,7 @@ _copyDeleteStmt(DeleteStmt *from)
23262327
COPY_NODE_FIELD(usingClause);
23272328
COPY_NODE_FIELD(whereClause);
23282329
COPY_NODE_FIELD(returningList);
2330+
COPY_NODE_FIELD(withClause);
23292331

23302332
return newnode;
23312333
}
@@ -2340,6 +2342,7 @@ _copyUpdateStmt(UpdateStmt *from)
23402342
COPY_NODE_FIELD(whereClause);
23412343
COPY_NODE_FIELD(fromClause);
23422344
COPY_NODE_FIELD(returningList);
2345+
COPY_NODE_FIELD(withClause);
23432346

23442347
return newnode;
23452348
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -890,6 +890,7 @@ _equalInsertStmt(InsertStmt *a, InsertStmt *b)
890890
COMPARE_NODE_FIELD(cols);
891891
COMPARE_NODE_FIELD(selectStmt);
892892
COMPARE_NODE_FIELD(returningList);
893+
COMPARE_NODE_FIELD(withClause);
893894

894895
return true;
895896
}
@@ -901,6 +902,7 @@ _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
901902
COMPARE_NODE_FIELD(usingClause);
902903
COMPARE_NODE_FIELD(whereClause);
903904
COMPARE_NODE_FIELD(returningList);
905+
COMPARE_NODE_FIELD(withClause);
904906

905907
return true;
906908
}
@@ -913,6 +915,7 @@ _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
913915
COMPARE_NODE_FIELD(whereClause);
914916
COMPARE_NODE_FIELD(fromClause);
915917
COMPARE_NODE_FIELD(returningList);
918+
COMPARE_NODE_FIELD(withClause);
916919

917920
return true;
918921
}

‎src/backend/parser/analyze.c

Lines changed: 24 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -283,6 +283,13 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
283283

284284
qry->commandType = CMD_DELETE;
285285

286+
/* process the WITH clause independently of all else */
287+
if (stmt->withClause)
288+
{
289+
qry->hasRecursive = stmt->withClause->recursive;
290+
qry->cteList = transformWithClause(pstate, stmt->withClause);
291+
}
292+
286293
/* set up range table with just the result rel */
287294
qry->resultRelation = setTargetTable(pstate, stmt->relation,
288295
interpretInhOption(stmt->relation->inhOpt),
@@ -340,9 +347,19 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
340347
ListCell *attnos;
341348
ListCell *lc;
342349

350+
/* There can't be any outer WITH to worry about */
351+
Assert(pstate->p_ctenamespace == NIL);
352+
343353
qry->commandType = CMD_INSERT;
344354
pstate->p_is_insert = true;
345355

356+
/* process the WITH clause independently of all else */
357+
if (stmt->withClause)
358+
{
359+
qry->hasRecursive = stmt->withClause->recursive;
360+
qry->cteList = transformWithClause(pstate, stmt->withClause);
361+
}
362+
346363
/*
347364
* We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
348365
* VALUES list, or general SELECT input. We special-case VALUES, both for
@@ -376,8 +393,6 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
376393
pstate->p_relnamespace = NIL;
377394
sub_varnamespace = pstate->p_varnamespace;
378395
pstate->p_varnamespace = NIL;
379-
/* There can't be any outer WITH to worry about */
380-
Assert(pstate->p_ctenamespace == NIL);
381396
}
382397
else
383398
{
@@ -518,13 +533,6 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
518533
List *exprsLists = NIL;
519534
int sublist_length = -1;
520535

521-
/* process the WITH clause */
522-
if (selectStmt->withClause)
523-
{
524-
qry->hasRecursive = selectStmt->withClause->recursive;
525-
qry->cteList = transformWithClause(pstate, selectStmt->withClause);
526-
}
527-
528536
foreach(lc, selectStmt->valuesLists)
529537
{
530538
List *sublist = (List *) lfirst(lc);
@@ -618,13 +626,6 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
618626

619627
Assert(list_length(valuesLists) == 1);
620628

621-
/* process the WITH clause */
622-
if (selectStmt->withClause)
623-
{
624-
qry->hasRecursive = selectStmt->withClause->recursive;
625-
qry->cteList = transformWithClause(pstate, selectStmt->withClause);
626-
}
627-
628629
/* Do basic expression transformation (same as a ROW() expr) */
629630
exprList = transformExpressionList(pstate,
630631
(List *) linitial(valuesLists));
@@ -1794,6 +1795,13 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
17941795
qry->commandType = CMD_UPDATE;
17951796
pstate->p_is_update = true;
17961797

1798+
/* process the WITH clause independently of all else */
1799+
if (stmt->withClause)
1800+
{
1801+
qry->hasRecursive = stmt->withClause->recursive;
1802+
qry->cteList = transformWithClause(pstate, stmt->withClause);
1803+
}
1804+
17971805
qry->resultRelation = setTargetTable(pstate, stmt->relation,
17981806
interpretInhOption(stmt->relation->inhOpt),
17991807
true,

‎src/backend/parser/gram.y

Lines changed: 24 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -433,7 +433,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
433433
%type <boolean> xml_whitespace_option
434434

435435
%type <node> common_table_expr
436-
%type <with> with_clause
436+
%type <with> with_clause opt_with_clause
437437
%type <list> cte_list
438438

439439
%type <list> window_clause window_definition_list opt_partition_clause
@@ -7269,11 +7269,12 @@ DeallocateStmt: DEALLOCATE name
72697269
*****************************************************************************/
72707270

72717271
InsertStmt:
7272-
INSERT INTO qualified_name insert_rest returning_clause
7272+
opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
72737273
{
7274-
$4->relation = $3;
7275-
$4->returningList = $5;
7276-
$$ = (Node *) $4;
7274+
$5->relation = $4;
7275+
$5->returningList = $6;
7276+
$5->withClause = $1;
7277+
$$ = (Node *) $5;
72777278
}
72787279
;
72797280

@@ -7329,14 +7330,15 @@ returning_clause:
73297330
*
73307331
*****************************************************************************/
73317332

7332-
DeleteStmt: DELETE_P FROM relation_expr_opt_alias
7333+
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
73337334
using_clause where_or_current_clause returning_clause
73347335
{
73357336
DeleteStmt *n = makeNode(DeleteStmt);
7336-
n->relation = $3;
7337-
n->usingClause = $4;
7338-
n->whereClause = $5;
7339-
n->returningList = $6;
7337+
n->relation = $4;
7338+
n->usingClause = $5;
7339+
n->whereClause = $6;
7340+
n->returningList = $7;
7341+
n->withClause = $1;
73407342
$$ = (Node *)n;
73417343
}
73427344
;
@@ -7391,18 +7393,19 @@ opt_nowait: NOWAIT { $$ = TRUE; }
73917393
*
73927394
*****************************************************************************/
73937395

7394-
UpdateStmt: UPDATE relation_expr_opt_alias
7396+
UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
73957397
SET set_clause_list
73967398
from_clause
73977399
where_or_current_clause
73987400
returning_clause
73997401
{
74007402
UpdateStmt *n = makeNode(UpdateStmt);
7401-
n->relation = $2;
7402-
n->targetList = $4;
7403-
n->fromClause = $5;
7404-
n->whereClause = $6;
7405-
n->returningList = $7;
7403+
n->relation = $3;
7404+
n->targetList = $5;
7405+
n->fromClause = $6;
7406+
n->whereClause = $7;
7407+
n->returningList = $8;
7408+
n->withClause = $1;
74067409
$$ = (Node *)n;
74077410
}
74087411
;
@@ -7744,6 +7747,11 @@ common_table_expr: name opt_name_list AS select_with_parens
77447747
}
77457748
;
77467749

7750+
opt_with_clause:
7751+
with_clause { $$ = $1; }
7752+
| /*EMPTY*/ { $$ = NULL; }
7753+
;
7754+
77477755
into_clause:
77487756
INTO OptTempTableName
77497757
{

0 commit comments

Comments
 (0)