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

Commit3908d6a

Browse files
committed
Support PlaceHolderVars in MERGE actions.
preprocess_targetlist thought PHVs couldn't appear here.It was mistaken, as per report from Önder Kalacı.Surveying other pull_var_clause calls, I noted no similar errors,but I did notice that qual_is_pushdown_safe's assertion about!contain_window_function was pointless, because the followingpull_var_clause call would complain about them anyway. In HEADonly, remove the redundant Assert and improve the commentary.Discussion:https://postgr.es/m/CACawEhUuum-gC_2S3sXLTcsk7bUSPSHOD+g1ZpfKaDK-KKPPWA@mail.gmail.com
1 parent69b6032 commit3908d6a

File tree

3 files changed

+36
-7
lines changed

3 files changed

+36
-7
lines changed

‎src/backend/optimizer/prep/preptlist.c‎

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -155,17 +155,15 @@ preprocess_targetlist(PlannerInfo *root)
155155
extract_update_targetlist_colnos(action->targetList);
156156

157157
/*
158-
* Add resjunk entries for any Vars used in each action's
159-
* targetlist and WHEN condition that belong to relations other
160-
* than target. Note that aggregates, window functions and
161-
* placeholder vars are not possible anywhere in MERGE's WHEN
162-
* clauses. (PHVs may be added later, but they don't concern us
163-
* here.)
158+
* Add resjunk entries for any Vars and PlaceHolderVars used in
159+
* each action's targetlist and WHEN condition that belong to
160+
* relations other than the target. We don't expect to see any
161+
* aggregates or window functions here.
164162
*/
165163
vars=pull_var_clause((Node*)
166164
list_concat_copy((List*)action->qual,
167165
action->targetList),
168-
0);
166+
PVC_INCLUDE_PLACEHOLDERS);
169167
foreach(l2,vars)
170168
{
171169
Var*var= (Var*)lfirst(l2);

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1902,6 +1902,27 @@ SELECT * FROM cj_target;
19021902
2 | 320 | initial source2 300
19031903
(4 rows)
19041904

1905+
-- try it with an outer join and PlaceHolderVar
1906+
MERGE INTO cj_target t
1907+
USING (SELECT *, 'join input'::text AS phv FROM cj_source1) fj
1908+
FULL JOIN cj_source2 fj2 ON fj.scat = fj2.sid2 * 10
1909+
ON t.tid = fj.scat
1910+
WHEN NOT MATCHED THEN
1911+
INSERT (tid, balance, val) VALUES (fj.scat, fj.delta, fj.phv);
1912+
SELECT * FROM cj_target;
1913+
tid | balance | val
1914+
-----+---------+----------------------------------
1915+
3 | 400 | initial source2 updated by merge
1916+
1 | 220 | initial source2 200
1917+
1 | 110 | initial source2 200
1918+
2 | 320 | initial source2 300
1919+
10 | 100 | join input
1920+
10 | 400 | join input
1921+
20 | 200 | join input
1922+
20 | 300 | join input
1923+
| |
1924+
(9 rows)
1925+
19051926
ALTER TABLE cj_source1 RENAME COLUMN sid1 TO sid;
19061927
ALTER TABLE cj_source2 RENAME COLUMN sid2 TO sid;
19071928
TRUNCATE cj_target;

‎src/test/regress/sql/merge.sql‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1225,6 +1225,16 @@ WHEN MATCHED THEN
12251225

12261226
SELECT*FROM cj_target;
12271227

1228+
-- try it with an outer join and PlaceHolderVar
1229+
MERGE INTO cj_target t
1230+
USING (SELECT*,'join input'::textAS phvFROM cj_source1) fj
1231+
FULLJOIN cj_source2 fj2ONfj.scat=fj2.sid2*10
1232+
ONt.tid=fj.scat
1233+
WHEN NOT MATCHED THEN
1234+
INSERT (tid, balance, val)VALUES (fj.scat,fj.delta,fj.phv);
1235+
1236+
SELECT*FROM cj_target;
1237+
12281238
ALTERTABLE cj_source1 RENAME COLUMN sid1 TO sid;
12291239
ALTERTABLE cj_source2 RENAME COLUMN sid2 TO sid;
12301240

0 commit comments

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