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

Commitf200b96

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 parent56e869a commitf200b96

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);
@@ -5488,6 +5490,10 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace,
54885490
get_delete_query_def(query,&context,colNamesVisible);
54895491
break;
54905492

5493+
caseCMD_MERGE:
5494+
get_merge_query_def(query,&context,colNamesVisible);
5495+
break;
5496+
54915497
caseCMD_NOTHING:
54925498
appendStringInfoString(buf,"NOTHING");
54935499
break;
@@ -7083,6 +7089,128 @@ get_delete_query_def(Query *query, deparse_context *context,
70837089
}
70847090

70857091

7092+
/* ----------
7093+
* get_merge_query_def- Parse back a MERGE parsetree
7094+
* ----------
7095+
*/
7096+
staticvoid
7097+
get_merge_query_def(Query*query,deparse_context*context,
7098+
boolcolNamesVisible)
7099+
{
7100+
StringInfobuf=context->buf;
7101+
RangeTblEntry*rte;
7102+
ListCell*lc;
7103+
7104+
/* Insert the WITH clause if given */
7105+
get_with_clause(query,context);
7106+
7107+
/*
7108+
* Start the query with MERGE INTO relname
7109+
*/
7110+
rte=rt_fetch(query->resultRelation,query->rtable);
7111+
Assert(rte->rtekind==RTE_RELATION);
7112+
if (PRETTY_INDENT(context))
7113+
{
7114+
appendStringInfoChar(buf,' ');
7115+
context->indentLevel+=PRETTYINDENT_STD;
7116+
}
7117+
appendStringInfo(buf,"MERGE INTO %s%s",
7118+
only_marker(rte),
7119+
generate_relation_name(rte->relid,NIL));
7120+
7121+
/* Print the relation alias, if needed */
7122+
get_rte_alias(rte,query->resultRelation, false,context);
7123+
7124+
/* Print the source relation and join clause */
7125+
get_from_clause(query," USING ",context);
7126+
appendContextKeyword(context," ON ",
7127+
-PRETTYINDENT_STD,PRETTYINDENT_STD,2);
7128+
get_rule_expr(query->jointree->quals,context, false);
7129+
7130+
/* Print each merge action */
7131+
foreach(lc,query->mergeActionList)
7132+
{
7133+
MergeAction*action=lfirst_node(MergeAction,lc);
7134+
7135+
appendContextKeyword(context," WHEN ",
7136+
-PRETTYINDENT_STD,PRETTYINDENT_STD,2);
7137+
appendStringInfo(buf,"%sMATCHED",action->matched ?"" :"NOT ");
7138+
7139+
if (action->qual)
7140+
{
7141+
appendContextKeyword(context," AND ",
7142+
-PRETTYINDENT_STD,PRETTYINDENT_STD,3);
7143+
get_rule_expr(action->qual,context, false);
7144+
}
7145+
appendContextKeyword(context," THEN ",
7146+
-PRETTYINDENT_STD,PRETTYINDENT_STD,3);
7147+
7148+
if (action->commandType==CMD_INSERT)
7149+
{
7150+
/* This generally matches get_insert_query_def() */
7151+
List*strippedexprs=NIL;
7152+
constchar*sep="";
7153+
ListCell*lc2;
7154+
7155+
appendStringInfoString(buf,"INSERT");
7156+
7157+
if (action->targetList)
7158+
appendStringInfoString(buf," (");
7159+
foreach(lc2,action->targetList)
7160+
{
7161+
TargetEntry*tle= (TargetEntry*)lfirst(lc2);
7162+
7163+
Assert(!tle->resjunk);
7164+
7165+
appendStringInfoString(buf,sep);
7166+
sep=", ";
7167+
7168+
appendStringInfoString(buf,
7169+
quote_identifier(get_attname(rte->relid,
7170+
tle->resno,
7171+
false)));
7172+
strippedexprs=lappend(strippedexprs,
7173+
processIndirection((Node*)tle->expr,
7174+
context));
7175+
}
7176+
if (action->targetList)
7177+
appendStringInfoChar(buf,')');
7178+
7179+
if (action->override)
7180+
{
7181+
if (action->override==OVERRIDING_SYSTEM_VALUE)
7182+
appendStringInfoString(buf," OVERRIDING SYSTEM VALUE");
7183+
elseif (action->override==OVERRIDING_USER_VALUE)
7184+
appendStringInfoString(buf," OVERRIDING USER VALUE");
7185+
}
7186+
7187+
if (strippedexprs)
7188+
{
7189+
appendContextKeyword(context," VALUES (",
7190+
-PRETTYINDENT_STD,PRETTYINDENT_STD,4);
7191+
get_rule_list_toplevel(strippedexprs,context, false);
7192+
appendStringInfoChar(buf,')');
7193+
}
7194+
else
7195+
appendStringInfoString(buf," DEFAULT VALUES");
7196+
}
7197+
elseif (action->commandType==CMD_UPDATE)
7198+
{
7199+
appendStringInfoString(buf,"UPDATE SET ");
7200+
get_update_query_targetlist_def(query,action->targetList,
7201+
context,rte);
7202+
}
7203+
elseif (action->commandType==CMD_DELETE)
7204+
appendStringInfoString(buf,"DELETE");
7205+
elseif (action->commandType==CMD_NOTHING)
7206+
appendStringInfoString(buf,"DO NOTHING");
7207+
}
7208+
7209+
/* No RETURNING support in MERGE yet */
7210+
Assert(query->returningList==NIL);
7211+
}
7212+
7213+
70867214
/* ----------
70877215
* get_utility_query_def- Parse back a UTILITY parsetree
70887216
* ----------

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

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

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

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

1299+
-- test deparsing
1300+
CREATETABLEsf_target(idint, datatext, fillingint[]);
1301+
1302+
CREATEFUNCTIONmerge_sf_test()
1303+
RETURNS void
1304+
LANGUAGE sql
1305+
BEGIN ATOMIC
1306+
MERGE INTO sf_target t
1307+
USING rule_merge1 s
1308+
ON (s.a=t.id)
1309+
WHEN MATCHED
1310+
AND (s.a+t.id)=42
1311+
THENUPDATESET data= repeat(t.data,s.a)||s.b, id= length(s.b)
1312+
WHEN NOT MATCHED
1313+
AND (s.bIS NOT NULL)
1314+
THEN INSERT (data, id)
1315+
VALUES (s.b,s.a)
1316+
WHEN MATCHED
1317+
AND length(s.b||t.data)>10
1318+
THENUPDATESET data=s.b
1319+
WHEN MATCHED
1320+
ANDs.a>200
1321+
THENUPDATESET filling[s.a]=t.id
1322+
WHEN MATCHED
1323+
ANDs.a>100
1324+
THENDELETE
1325+
WHEN MATCHED
1326+
THEN DO NOTHING
1327+
WHEN NOT MATCHED
1328+
ANDs.a>200
1329+
THEN INSERT DEFAULTVALUES
1330+
WHEN NOT MATCHED
1331+
ANDs.a>100
1332+
THEN INSERT (id, data) OVERRIDING USER VALUE
1333+
VALUES (s.a, DEFAULT)
1334+
WHEN NOT MATCHED
1335+
ANDs.a>0
1336+
THEN INSERT
1337+
VALUES (s.a,s.b, DEFAULT)
1338+
WHEN NOT MATCHED
1339+
THEN INSERT (filling[1], id)
1340+
VALUES (s.a,s.a);
1341+
END;
1342+
1343+
\sf merge_sf_test
1344+
1345+
DROPFUNCTION merge_sf_test;
1346+
DROPTABLE sf_target;
1347+
12991348
--
13001349
-- Test enabling/disabling
13011350
--

0 commit comments

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