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

Commit41e2c52

Browse files
committed
Add ruleutils support for decompiling MERGE commands.
This was overlooked when MERGE was added, but it's essentialsupport for MERGE in new-style SQL functions.Alvaro HerreraDiscussion:https://postgr.es/m/3579737.1683293801@sss.pgh.pa.us
1 parent58f5edf commit41e2c52

File tree

3 files changed

+262
-0
lines changed

3 files changed

+262
-0
lines changed

‎src/backend/utils/adt/ruleutils.c

Lines changed: 128 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -411,6 +411,8 @@ static void get_update_query_targetlist_def(Query *query, List *targetList,
411411
RangeTblEntry*rte);
412412
staticvoidget_delete_query_def(Query*query,deparse_context*context,
413413
boolcolNamesVisible);
414+
staticvoidget_merge_query_def(Query*query,deparse_context*context,
415+
boolcolNamesVisible);
414416
staticvoidget_utility_query_def(Query*query,deparse_context*context);
415417
staticvoidget_basic_select_query(Query*query,deparse_context*context,
416418
TupleDescresultDesc,boolcolNamesVisible);
@@ -5448,6 +5450,10 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace,
54485450
get_delete_query_def(query,&context,colNamesVisible);
54495451
break;
54505452

5453+
caseCMD_MERGE:
5454+
get_merge_query_def(query,&context,colNamesVisible);
5455+
break;
5456+
54515457
caseCMD_NOTHING:
54525458
appendStringInfoString(buf,"NOTHING");
54535459
break;
@@ -7044,6 +7050,128 @@ get_delete_query_def(Query *query, deparse_context *context,
70447050
}
70457051

70467052

7053+
/* ----------
7054+
* get_merge_query_def- Parse back a MERGE parsetree
7055+
* ----------
7056+
*/
7057+
staticvoid
7058+
get_merge_query_def(Query*query,deparse_context*context,
7059+
boolcolNamesVisible)
7060+
{
7061+
StringInfobuf=context->buf;
7062+
RangeTblEntry*rte;
7063+
ListCell*lc;
7064+
7065+
/* Insert the WITH clause if given */
7066+
get_with_clause(query,context);
7067+
7068+
/*
7069+
* Start the query with MERGE INTO relname
7070+
*/
7071+
rte=rt_fetch(query->resultRelation,query->rtable);
7072+
Assert(rte->rtekind==RTE_RELATION);
7073+
if (PRETTY_INDENT(context))
7074+
{
7075+
appendStringInfoChar(buf,' ');
7076+
context->indentLevel+=PRETTYINDENT_STD;
7077+
}
7078+
appendStringInfo(buf,"MERGE INTO %s%s",
7079+
only_marker(rte),
7080+
generate_relation_name(rte->relid,NIL));
7081+
7082+
/* Print the relation alias, if needed */
7083+
get_rte_alias(rte,query->resultRelation, false,context);
7084+
7085+
/* Print the source relation and join clause */
7086+
get_from_clause(query," USING ",context);
7087+
appendContextKeyword(context," ON ",
7088+
-PRETTYINDENT_STD,PRETTYINDENT_STD,2);
7089+
get_rule_expr(query->jointree->quals,context, false);
7090+
7091+
/* Print each merge action */
7092+
foreach(lc,query->mergeActionList)
7093+
{
7094+
MergeAction*action=lfirst_node(MergeAction,lc);
7095+
7096+
appendContextKeyword(context," WHEN ",
7097+
-PRETTYINDENT_STD,PRETTYINDENT_STD,2);
7098+
appendStringInfo(buf,"%sMATCHED",action->matched ?"" :"NOT ");
7099+
7100+
if (action->qual)
7101+
{
7102+
appendContextKeyword(context," AND ",
7103+
-PRETTYINDENT_STD,PRETTYINDENT_STD,3);
7104+
get_rule_expr(action->qual,context, false);
7105+
}
7106+
appendContextKeyword(context," THEN ",
7107+
-PRETTYINDENT_STD,PRETTYINDENT_STD,3);
7108+
7109+
if (action->commandType==CMD_INSERT)
7110+
{
7111+
/* This generally matches get_insert_query_def() */
7112+
List*strippedexprs=NIL;
7113+
constchar*sep="";
7114+
ListCell*lc2;
7115+
7116+
appendStringInfoString(buf,"INSERT");
7117+
7118+
if (action->targetList)
7119+
appendStringInfoString(buf," (");
7120+
foreach(lc2,action->targetList)
7121+
{
7122+
TargetEntry*tle= (TargetEntry*)lfirst(lc2);
7123+
7124+
Assert(!tle->resjunk);
7125+
7126+
appendStringInfoString(buf,sep);
7127+
sep=", ";
7128+
7129+
appendStringInfoString(buf,
7130+
quote_identifier(get_attname(rte->relid,
7131+
tle->resno,
7132+
false)));
7133+
strippedexprs=lappend(strippedexprs,
7134+
processIndirection((Node*)tle->expr,
7135+
context));
7136+
}
7137+
if (action->targetList)
7138+
appendStringInfoChar(buf,')');
7139+
7140+
if (action->override)
7141+
{
7142+
if (action->override==OVERRIDING_SYSTEM_VALUE)
7143+
appendStringInfoString(buf," OVERRIDING SYSTEM VALUE");
7144+
elseif (action->override==OVERRIDING_USER_VALUE)
7145+
appendStringInfoString(buf," OVERRIDING USER VALUE");
7146+
}
7147+
7148+
if (strippedexprs)
7149+
{
7150+
appendContextKeyword(context," VALUES (",
7151+
-PRETTYINDENT_STD,PRETTYINDENT_STD,4);
7152+
get_rule_list_toplevel(strippedexprs,context, false);
7153+
appendStringInfoChar(buf,')');
7154+
}
7155+
else
7156+
appendStringInfoString(buf," DEFAULT VALUES");
7157+
}
7158+
elseif (action->commandType==CMD_UPDATE)
7159+
{
7160+
appendStringInfoString(buf,"UPDATE SET ");
7161+
get_update_query_targetlist_def(query,action->targetList,
7162+
context,rte);
7163+
}
7164+
elseif (action->commandType==CMD_DELETE)
7165+
appendStringInfoString(buf,"DELETE");
7166+
elseif (action->commandType==CMD_NOTHING)
7167+
appendStringInfoString(buf,"DO NOTHING");
7168+
}
7169+
7170+
/* No RETURNING support in MERGE yet */
7171+
Assert(query->returningList==NIL);
7172+
}
7173+
7174+
70477175
/* ----------
70487176
* get_utility_query_def- Parse back a UTILITY parsetree
70497177
* ----------

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

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3585,6 +3585,91 @@ MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s
35853585
DELETE
35863586
WHEN NOT MATCHED THEN
35873587
INSERT VALUES (s.a, '');
3588+
-- test deparsing
3589+
CREATE TABLE sf_target(id int, data text, filling int[]);
3590+
CREATE FUNCTION merge_sf_test()
3591+
RETURNS void
3592+
LANGUAGE sql
3593+
BEGIN ATOMIC
3594+
MERGE INTO sf_target t
3595+
USING rule_merge1 s
3596+
ON (s.a = t.id)
3597+
WHEN MATCHED
3598+
AND (s.a + t.id) = 42
3599+
THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b)
3600+
WHEN NOT MATCHED
3601+
AND (s.b IS NOT NULL)
3602+
THEN INSERT (data, id)
3603+
VALUES (s.b, s.a)
3604+
WHEN MATCHED
3605+
AND length(s.b || t.data) > 10
3606+
THEN UPDATE SET data = s.b
3607+
WHEN MATCHED
3608+
AND s.a > 200
3609+
THEN UPDATE SET filling[s.a] = t.id
3610+
WHEN MATCHED
3611+
AND s.a > 100
3612+
THEN DELETE
3613+
WHEN MATCHED
3614+
THEN DO NOTHING
3615+
WHEN NOT MATCHED
3616+
AND s.a > 200
3617+
THEN INSERT DEFAULT VALUES
3618+
WHEN NOT MATCHED
3619+
AND s.a > 100
3620+
THEN INSERT (id, data) OVERRIDING USER VALUE
3621+
VALUES (s.a, DEFAULT)
3622+
WHEN NOT MATCHED
3623+
AND s.a > 0
3624+
THEN INSERT
3625+
VALUES (s.a, s.b, DEFAULT)
3626+
WHEN NOT MATCHED
3627+
THEN INSERT (filling[1], id)
3628+
VALUES (s.a, s.a);
3629+
END;
3630+
\sf merge_sf_test
3631+
CREATE OR REPLACE FUNCTION public.merge_sf_test()
3632+
RETURNS void
3633+
LANGUAGE sql
3634+
BEGIN ATOMIC
3635+
MERGE INTO sf_target t
3636+
USING rule_merge1 s
3637+
ON (s.a = t.id)
3638+
WHEN MATCHED
3639+
AND ((s.a + t.id) = 42)
3640+
THEN UPDATE SET data = (repeat(t.data, s.a) || s.b), id = length(s.b)
3641+
WHEN NOT MATCHED
3642+
AND (s.b IS NOT NULL)
3643+
THEN INSERT (data, id)
3644+
VALUES (s.b, s.a)
3645+
WHEN MATCHED
3646+
AND (length((s.b || t.data)) > 10)
3647+
THEN UPDATE SET data = s.b
3648+
WHEN MATCHED
3649+
AND (s.a > 200)
3650+
THEN UPDATE SET filling[s.a] = t.id
3651+
WHEN MATCHED
3652+
AND (s.a > 100)
3653+
THEN DELETE
3654+
WHEN MATCHED
3655+
THEN DO NOTHING
3656+
WHEN NOT MATCHED
3657+
AND (s.a > 200)
3658+
THEN INSERT DEFAULT VALUES
3659+
WHEN NOT MATCHED
3660+
AND (s.a > 100)
3661+
THEN INSERT (id, data) OVERRIDING USER VALUE
3662+
VALUES (s.a, DEFAULT)
3663+
WHEN NOT MATCHED
3664+
AND (s.a > 0)
3665+
THEN INSERT (id, data, filling)
3666+
VALUES (s.a, s.b, DEFAULT)
3667+
WHEN NOT MATCHED
3668+
THEN INSERT (filling[1], id)
3669+
VALUES (s.a, s.a);
3670+
END
3671+
DROP FUNCTION merge_sf_test;
3672+
DROP TABLE sf_target;
35883673
--
35893674
-- Test enabling/disabling
35903675
--

‎src/test/regress/sql/rules.sql

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1277,6 +1277,55 @@ MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s
12771277
WHEN NOT MATCHED THEN
12781278
INSERTVALUES (s.a,'');
12791279

1280+
-- test deparsing
1281+
CREATETABLEsf_target(idint, datatext, fillingint[]);
1282+
1283+
CREATEFUNCTIONmerge_sf_test()
1284+
RETURNS void
1285+
LANGUAGE sql
1286+
BEGIN ATOMIC
1287+
MERGE INTO sf_target t
1288+
USING rule_merge1 s
1289+
ON (s.a=t.id)
1290+
WHEN MATCHED
1291+
AND (s.a+t.id)=42
1292+
THENUPDATESET data= repeat(t.data,s.a)||s.b, id= length(s.b)
1293+
WHEN NOT MATCHED
1294+
AND (s.bIS NOT NULL)
1295+
THEN INSERT (data, id)
1296+
VALUES (s.b,s.a)
1297+
WHEN MATCHED
1298+
AND length(s.b||t.data)>10
1299+
THENUPDATESET data=s.b
1300+
WHEN MATCHED
1301+
ANDs.a>200
1302+
THENUPDATESET filling[s.a]=t.id
1303+
WHEN MATCHED
1304+
ANDs.a>100
1305+
THENDELETE
1306+
WHEN MATCHED
1307+
THEN DO NOTHING
1308+
WHEN NOT MATCHED
1309+
ANDs.a>200
1310+
THEN INSERT DEFAULTVALUES
1311+
WHEN NOT MATCHED
1312+
ANDs.a>100
1313+
THEN INSERT (id, data) OVERRIDING USER VALUE
1314+
VALUES (s.a, DEFAULT)
1315+
WHEN NOT MATCHED
1316+
ANDs.a>0
1317+
THEN INSERT
1318+
VALUES (s.a,s.b, DEFAULT)
1319+
WHEN NOT MATCHED
1320+
THEN INSERT (filling[1], id)
1321+
VALUES (s.a,s.a);
1322+
END;
1323+
1324+
\sf merge_sf_test
1325+
1326+
DROPFUNCTION merge_sf_test;
1327+
DROPTABLE sf_target;
1328+
12801329
--
12811330
-- Test enabling/disabling
12821331
--

0 commit comments

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