Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitaa3faa3

Browse files
WITH support in MERGE
Author: Peter GeogheganRecursive support removed, no testsDocs added by me
1 parent83454e3 commitaa3faa3

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 actionis an extension to the <acronym>SQL</acronym> standard.
613+
TheWITH clause andDO NOTHING actionare 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
returnnewnode;
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
caseT_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-
MERGEINTOrelation_expr_opt_alias
11108+
opt_with_clauseMERGEINTOrelation_expr_opt_alias
1110911109
USINGtable_ref
1111011110
ONa_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
typedefstructMergeAction

‎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
DROPTABLE withz;
864864

865+
-- WITH referenced by MERGE statement
866+
CREATETABLEmASSELECT iAS k, (i||' v')::text vFROM generate_series(1,16,3) i;
867+
ALTERTABLE m ADD UNIQUE (k);
868+
869+
WITH RECURSIVE cte_basicAS (SELECT1 a,'cte_basic val' b)
870+
MERGE INTO m USING (select0 k,'merge source SubPlan' v) oONm.k=o.k
871+
WHEN MATCHED THENUPDATESET v= (SELECT b||' merge update'FROM cte_basicWHEREcte_basic.a=m.kLIMIT1)
872+
WHEN NOT MATCHED THEN INSERTVALUES(o.k,o.v);
873+
874+
-- Basic:
875+
WITH cte_basicAS (SELECT1 a,'cte_basic val' b)
876+
MERGE INTO m USING (select0 k,'merge source SubPlan' v) oONm.k=o.k
877+
WHEN MATCHED THENUPDATESET v= (SELECT b||' merge update'FROM cte_basicWHEREcte_basic.a=m.kLIMIT1)
878+
WHEN NOT MATCHED THEN INSERTVALUES(o.k,o.v);
879+
-- Examine
880+
SELECT*FROM mwhere k=0;
881+
882+
-- See EXPLAIN output for same query:
883+
EXPLAIN (VERBOSE, COSTS OFF)
884+
WITH cte_basicAS (SELECT1 a,'cte_basic val' b)
885+
MERGE INTO m USING (select0 k,'merge source SubPlan' v) oONm.k=o.k
886+
WHEN MATCHED THENUPDATESET v= (SELECT b||' merge update'FROM cte_basicWHEREcte_basic.a=m.kLIMIT1)
887+
WHEN NOT MATCHED THEN INSERTVALUES(o.k,o.v);
888+
889+
-- InitPlan
890+
WITH cte_initAS (SELECT1 a,'cte_init val' b)
891+
MERGE INTO m USING (select1 k,'merge source InitPlan' v) oONm.k=o.k
892+
WHEN MATCHED THENUPDATESET v= (SELECT b||' merge update'FROM cte_initWHERE a=1LIMIT1)
893+
WHEN NOT MATCHED THEN INSERTVALUES(o.k,o.v);
894+
-- Examine
895+
SELECT*FROM mwhere k=1;
896+
897+
-- See EXPLAIN output for same query:
898+
EXPLAIN (VERBOSE, COSTS OFF)
899+
WITH cte_initAS (SELECT1 a,'cte_init val' b)
900+
MERGE INTO m USING (select1 k,'merge source InitPlan' v) oONm.k=o.k
901+
WHEN MATCHED THENUPDATESET v= (SELECT b||' merge update'FROM cte_initWHERE a=1LIMIT1)
902+
WHEN NOT MATCHED THEN INSERTVALUES(o.k,o.v);
903+
904+
-- MERGE source comes from CTE:
905+
WITH merge_source_cteAS (SELECT15 a,'merge_source_cte val' b)
906+
MERGE INTO m USING (select*from merge_source_cte) oONm.k=o.a
907+
WHEN MATCHED THENUPDATESET v= (SELECT b|| merge_source_cte.*::text||' merge update'FROM merge_source_cteWHERE a=15)
908+
WHEN NOT MATCHED THEN INSERTVALUES(o.a,o.b|| (SELECT merge_source_cte.*::text||' merge insert'FROM merge_source_cte));
909+
-- Examine
910+
SELECT*FROM mwhere k=15;
911+
912+
-- See EXPLAIN output for same query:
913+
EXPLAIN (VERBOSE, COSTS OFF)
914+
WITH merge_source_cteAS (SELECT15 a,'merge_source_cte val' b)
915+
MERGE INTO m USING (select*from merge_source_cte) oONm.k=o.a
916+
WHEN MATCHED THENUPDATESET v= (SELECT b|| merge_source_cte.*::text||' merge update'FROM merge_source_cteWHERE a=15)
917+
WHEN NOT MATCHED THEN INSERTVALUES(o.a,o.b|| (SELECT merge_source_cte.*::text||' merge insert'FROM merge_source_cte));
918+
919+
DROPTABLE m;
920+
865921
-- check that run to completion happens in proper ordering
866922

867923
TRUNCATE TABLE y;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp