Skip to content

Commit aa3faa3

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
WITH support in MERGE
Author: Peter Geoghegan Recursive support removed, no tests Docs added by me
1 parent 83454e3 commit aa3faa3

File tree

10 files changed

+232
-9
lines changed

10 files changed

+232
-9
lines changed

‎doc/src/sgml/ref/merge.sgml

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@ PostgreSQL documentation
1818

1919
<refsynopsisdiv>
2020
<synopsis>
21+
[ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ]
2122
MERGE INTO <replaceable class="parameter">target_table_name</replaceable> [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
2223
USING <replaceable class="parameter">data_source</replaceable>
2324
ON <replaceable class="parameter">join_condition</replaceable>
@@ -391,6 +392,18 @@ DELETE
391392
</listitem>
392393
</varlistentry>
393394

395+
<varlistentry>
396+
<term><replaceable class="parameter">with_query</replaceable></term>
397+
<listitem>
398+
<para>
399+
The <literal>WITH</literal> clause allows you to specify one or more
400+
subqueries that can be referenced by name in the <command>MERGE</command>
401+
query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/>
402+
for details.
403+
</para>
404+
</listitem>
405+
</varlistentry>
406+
394407
</variablelist>
395408
</refsect1>
396409

@@ -597,7 +610,7 @@ WHEN MATCHED THEN
597610
This command conforms to the <acronym>SQL</acronym> standard.
598611
</para>
599612
<para>
600-
The DO NOTHING action is an extension to the <acronym>SQL</acronym> standard.
613+
The WITH clause and DO NOTHING action are extensions to the <acronym>SQL</acronym> standard.
601614
</para>
602615
</refsect1>
603616
</refentry>

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3055,6 +3055,7 @@ _copyMergeStmt(const MergeStmt *from)
30553055
COPY_NODE_FIELD(source_relation);
30563056
COPY_NODE_FIELD(join_condition);
30573057
COPY_NODE_FIELD(mergeActionList);
3058+
COPY_NODE_FIELD(withClause);
30583059

30593060
return newnode;
30603061
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1051,6 +1051,7 @@ _equalMergeStmt(const MergeStmt *a, const MergeStmt *b)
10511051
COMPARE_NODE_FIELD(source_relation);
10521052
COMPARE_NODE_FIELD(join_condition);
10531053
COMPARE_NODE_FIELD(mergeActionList);
1054+
COMPARE_NODE_FIELD(withClause);
10541055

10551056
return true;
10561057
}

‎src/backend/nodes/nodeFuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3446,6 +3446,8 @@ raw_expression_tree_walker(Node *node,
34463446
return true;
34473447
if (walker(stmt->mergeActionList, context))
34483448
return true;
3449+
if (walker(stmt->withClause, context))
3450+
return true;
34493451
}
34503452
break;
34513453
case T_MergeAction:

‎src/backend/parser/gram.y

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -11105,17 +11105,18 @@ set_target_list:
1110511105
*****************************************************************************/
1110611106

1110711107
MergeStmt:
11108-
MERGE INTO relation_expr_opt_alias
11108+
opt_with_clause MERGE INTO relation_expr_opt_alias
1110911109
USING table_ref
1111011110
ON a_expr
1111111111
merge_when_list
1111211112
{
1111311113
MergeStmt *m = makeNode(MergeStmt);
1111411114

11115-
m->relation = $3;
11116-
m->source_relation = $5;
11117-
m->join_condition = $7;
11118-
m->mergeActionList = $8;
11115+
m->withClause = $1;
11116+
m->relation = $4;
11117+
m->source_relation = $6;
11118+
m->join_condition = $8;
11119+
m->mergeActionList = $9;
1111911120

1112011121
$$ = (Node *)m;
1112111122
}

‎src/backend/parser/parse_merge.c

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include "parser/parsetree.h"
2525
#include "parser/parser.h"
2626
#include "parser/parse_clause.h"
27+
#include "parser/parse_cte.h"
2728
#include "parser/parse_merge.h"
2829
#include "parser/parse_relation.h"
2930
#include "parser/parse_target.h"
@@ -202,6 +203,19 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
202203
Assert(pstate->p_ctenamespace == NIL);
203204

204205
qry->commandType = CMD_MERGE;
206+
qry->hasRecursive = false;
207+
208+
/* process the WITH clause independently of all else */
209+
if (stmt->withClause)
210+
{
211+
if (stmt->withClause->recursive)
212+
ereport(ERROR,
213+
(errcode(ERRCODE_SYNTAX_ERROR),
214+
errmsg("WITH RECURSIVE is not supported for MERGE statement")));
215+
216+
qry->cteList = transformWithClause(pstate, stmt->withClause);
217+
qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
218+
}
205219

206220
/*
207221
* Check WHEN clauses for permissions and sanity

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1519,6 +1519,7 @@ typedef struct MergeStmt
15191519
Node *source_relation; /* source relation */
15201520
Node *join_condition; /* join condition between source and target */
15211521
List *mergeActionList; /* list of MergeAction(s) */
1522+
WithClause *withClause; /* WITH clause */
15221523
} MergeStmt;
15231524

15241525
typedef struct MergeAction

‎src/test/regress/expected/merge.out

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1210,9 +1210,6 @@ WHEN NOT MATCHED THEN
12101210
WHEN MATCHED AND tid < 2 THEN
12111211
DELETE
12121212
;
1213-
ERROR: syntax error at or near "MERGE"
1214-
LINE 4: MERGE INTO sq_target t
1215-
^
12161213
ROLLBACK;
12171214
-- RETURNING
12181215
BEGIN;

‎src/test/regress/expected/with.out

Lines changed: 137 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1904,6 +1904,143 @@ RETURNING k, v;
19041904
(0 rows)
19051905

19061906
DROP TABLE withz;
1907+
-- WITH referenced by MERGE statement
1908+
CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
1909+
ALTER TABLE m ADD UNIQUE (k);
1910+
WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
1911+
MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
1912+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
1913+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
1914+
ERROR: WITH RECURSIVE is not supported for MERGE statement
1915+
-- Basic:
1916+
WITH cte_basic AS (SELECT 1 a, 'cte_basic val' b)
1917+
MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
1918+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
1919+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
1920+
-- Examine
1921+
SELECT * FROM m where k = 0;
1922+
k | v
1923+
---+----------------------
1924+
0 | merge source SubPlan
1925+
(1 row)
1926+
1927+
-- See EXPLAIN output for same query:
1928+
EXPLAIN (VERBOSE, COSTS OFF)
1929+
WITH cte_basic AS (SELECT 1 a, 'cte_basic val' b)
1930+
MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
1931+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
1932+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
1933+
QUERY PLAN
1934+
-------------------------------------------------------------------
1935+
Merge on public.m
1936+
CTE cte_basic
1937+
-> Result
1938+
Output: 1, 'cte_basic val'::text
1939+
-> Hash Right Join
1940+
Output: o.k, o.v, o.*, m_1.ctid
1941+
Hash Cond: (m_1.k = o.k)
1942+
-> Seq Scan on public.m m_1
1943+
Output: m_1.ctid, m_1.k
1944+
-> Hash
1945+
Output: o.k, o.v, o.*
1946+
-> Subquery Scan on o
1947+
Output: o.k, o.v, o.*
1948+
-> Result
1949+
Output: 0, 'merge source SubPlan'::text
1950+
SubPlan 2
1951+
-> Limit
1952+
Output: ((cte_basic.b || ' merge update'::text))
1953+
-> CTE Scan on cte_basic
1954+
Output: (cte_basic.b || ' merge update'::text)
1955+
Filter: (cte_basic.a = m.k)
1956+
(21 rows)
1957+
1958+
-- InitPlan
1959+
WITH cte_init AS (SELECT 1 a, 'cte_init val' b)
1960+
MERGE INTO m USING (select 1 k, 'merge source InitPlan' v) o ON m.k=o.k
1961+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
1962+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
1963+
-- Examine
1964+
SELECT * FROM m where k = 1;
1965+
k | v
1966+
---+---------------------------
1967+
1 | cte_init val merge update
1968+
(1 row)
1969+
1970+
-- See EXPLAIN output for same query:
1971+
EXPLAIN (VERBOSE, COSTS OFF)
1972+
WITH cte_init AS (SELECT 1 a, 'cte_init val' b)
1973+
MERGE INTO m USING (select 1 k, 'merge source InitPlan' v) o ON m.k=o.k
1974+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
1975+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
1976+
QUERY PLAN
1977+
--------------------------------------------------------------------
1978+
Merge on public.m
1979+
CTE cte_init
1980+
-> Result
1981+
Output: 1, 'cte_init val'::text
1982+
InitPlan 2 (returns $1)
1983+
-> Limit
1984+
Output: ((cte_init.b || ' merge update'::text))
1985+
-> CTE Scan on cte_init
1986+
Output: (cte_init.b || ' merge update'::text)
1987+
Filter: (cte_init.a = 1)
1988+
-> Hash Right Join
1989+
Output: o.k, o.v, o.*, m_1.ctid
1990+
Hash Cond: (m_1.k = o.k)
1991+
-> Seq Scan on public.m m_1
1992+
Output: m_1.ctid, m_1.k
1993+
-> Hash
1994+
Output: o.k, o.v, o.*
1995+
-> Subquery Scan on o
1996+
Output: o.k, o.v, o.*
1997+
-> Result
1998+
Output: 1, 'merge source InitPlan'::text
1999+
(21 rows)
2000+
2001+
-- MERGE source comes from CTE:
2002+
WITH merge_source_cte AS (SELECT 15 a, 'merge_source_cte val' b)
2003+
MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
2004+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
2005+
WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
2006+
-- Examine
2007+
SELECT * FROM m where k = 15;
2008+
k | v
2009+
----+--------------------------------------------------------------
2010+
15 | merge_source_cte val(15,"merge_source_cte val") merge insert
2011+
(1 row)
2012+
2013+
-- See EXPLAIN output for same query:
2014+
EXPLAIN (VERBOSE, COSTS OFF)
2015+
WITH merge_source_cte AS (SELECT 15 a, 'merge_source_cte val' b)
2016+
MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
2017+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
2018+
WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
2019+
QUERY PLAN
2020+
---------------------------------------------------------------------------------------------------------------
2021+
Merge on public.m
2022+
CTE merge_source_cte
2023+
-> Result
2024+
Output: 15, 'merge_source_cte val'::text
2025+
InitPlan 2 (returns $1)
2026+
-> CTE Scan on merge_source_cte merge_source_cte_1
2027+
Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text) || ' merge update'::text)
2028+
Filter: (merge_source_cte_1.a = 15)
2029+
InitPlan 3 (returns $2)
2030+
-> CTE Scan on merge_source_cte merge_source_cte_2
2031+
Output: ((merge_source_cte_2.*)::text || ' merge insert'::text)
2032+
-> Hash Right Join
2033+
Output: merge_source_cte.a, merge_source_cte.b, ROW(merge_source_cte.a, merge_source_cte.b), m_1.ctid
2034+
Hash Cond: (m_1.k = merge_source_cte.a)
2035+
-> Seq Scan on public.m m_1
2036+
Output: m_1.ctid, m_1.k
2037+
-> Hash
2038+
Output: merge_source_cte.a, merge_source_cte.b
2039+
-> CTE Scan on merge_source_cte
2040+
Output: merge_source_cte.a, merge_source_cte.b
2041+
(20 rows)
2042+
2043+
DROP TABLE m;
19072044
-- check that run to completion happens in proper ordering
19082045
TRUNCATE TABLE y;
19092046
INSERT INTO y SELECT generate_series(1, 3);

‎src/test/regress/sql/with.sql

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -862,6 +862,62 @@ RETURNING k, v;
862862

863863
DROP TABLE withz;
864864

865+
-- WITH referenced by MERGE statement
866+
CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
867+
ALTER TABLE m ADD UNIQUE (k);
868+
869+
WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
870+
MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
871+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
872+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
873+
874+
-- Basic:
875+
WITH cte_basic AS (SELECT 1 a, 'cte_basic val' b)
876+
MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
877+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
878+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
879+
-- Examine
880+
SELECT * FROM m where k = 0;
881+
882+
-- See EXPLAIN output for same query:
883+
EXPLAIN (VERBOSE, COSTS OFF)
884+
WITH cte_basic AS (SELECT 1 a, 'cte_basic val' b)
885+
MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
886+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
887+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
888+
889+
-- InitPlan
890+
WITH cte_init AS (SELECT 1 a, 'cte_init val' b)
891+
MERGE INTO m USING (select 1 k, 'merge source InitPlan' v) o ON m.k=o.k
892+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
893+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
894+
-- Examine
895+
SELECT * FROM m where k = 1;
896+
897+
-- See EXPLAIN output for same query:
898+
EXPLAIN (VERBOSE, COSTS OFF)
899+
WITH cte_init AS (SELECT 1 a, 'cte_init val' b)
900+
MERGE INTO m USING (select 1 k, 'merge source InitPlan' v) o ON m.k=o.k
901+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
902+
WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
903+
904+
-- MERGE source comes from CTE:
905+
WITH merge_source_cte AS (SELECT 15 a, 'merge_source_cte val' b)
906+
MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
907+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
908+
WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
909+
-- Examine
910+
SELECT * FROM m where k = 15;
911+
912+
-- See EXPLAIN output for same query:
913+
EXPLAIN (VERBOSE, COSTS OFF)
914+
WITH merge_source_cte AS (SELECT 15 a, 'merge_source_cte val' b)
915+
MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
916+
WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
917+
WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
918+
919+
DROP TABLE m;
920+
865921
-- check that run to completion happens in proper ordering
866922

867923
TRUNCATE TABLE y;

0 commit comments

Comments
 (0)