From 41e2c52fd6ebed6eff4184f68048813cc8886ec1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 7 May 2023 11:01:15 -0400 Subject: Add ruleutils support for decompiling MERGE commands. This was overlooked when MERGE was added, but it's essential support for MERGE in new-style SQL functions. Alvaro Herrera Discussion: https://postgr.es/m/3579737.1683293801@sss.pgh.pa.us --- src/backend/utils/adt/ruleutils.c | 128 ++++++++++++++++++++++++++++++++++++ src/test/regress/expected/rules.out | 85 ++++++++++++++++++++++++ src/test/regress/sql/rules.sql | 49 ++++++++++++++ 3 files changed, 262 insertions(+) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 461735e84f..60f9d08d5d 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -411,6 +411,8 @@ static void get_update_query_targetlist_def(Query *query, List *targetList, RangeTblEntry *rte); static void get_delete_query_def(Query *query, deparse_context *context, bool colNamesVisible); +static void get_merge_query_def(Query *query, deparse_context *context, + bool colNamesVisible); static void get_utility_query_def(Query *query, deparse_context *context); static void get_basic_select_query(Query *query, deparse_context *context, TupleDesc resultDesc, bool colNamesVisible); @@ -5448,6 +5450,10 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace, get_delete_query_def(query, &context, colNamesVisible); break; + case CMD_MERGE: + get_merge_query_def(query, &context, colNamesVisible); + break; + case CMD_NOTHING: appendStringInfoString(buf, "NOTHING"); break; @@ -7044,6 +7050,128 @@ get_delete_query_def(Query *query, deparse_context *context, } +/* ---------- + * get_merge_query_def - Parse back a MERGE parsetree + * ---------- + */ +static void +get_merge_query_def(Query *query, deparse_context *context, + bool colNamesVisible) +{ + StringInfo buf = context->buf; + RangeTblEntry *rte; + ListCell *lc; + + /* Insert the WITH clause if given */ + get_with_clause(query, context); + + /* + * Start the query with MERGE INTO relname + */ + rte = rt_fetch(query->resultRelation, query->rtable); + Assert(rte->rtekind == RTE_RELATION); + if (PRETTY_INDENT(context)) + { + appendStringInfoChar(buf, ' '); + context->indentLevel += PRETTYINDENT_STD; + } + appendStringInfo(buf, "MERGE INTO %s%s", + only_marker(rte), + generate_relation_name(rte->relid, NIL)); + + /* Print the relation alias, if needed */ + get_rte_alias(rte, query->resultRelation, false, context); + + /* Print the source relation and join clause */ + get_from_clause(query, " USING ", context); + appendContextKeyword(context, " ON ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); + get_rule_expr(query->jointree->quals, context, false); + + /* Print each merge action */ + foreach(lc, query->mergeActionList) + { + MergeAction *action = lfirst_node(MergeAction, lc); + + appendContextKeyword(context, " WHEN ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); + appendStringInfo(buf, "%sMATCHED", action->matched ? "" : "NOT "); + + if (action->qual) + { + appendContextKeyword(context, " AND ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 3); + get_rule_expr(action->qual, context, false); + } + appendContextKeyword(context, " THEN ", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 3); + + if (action->commandType == CMD_INSERT) + { + /* This generally matches get_insert_query_def() */ + List *strippedexprs = NIL; + const char *sep = ""; + ListCell *lc2; + + appendStringInfoString(buf, "INSERT"); + + if (action->targetList) + appendStringInfoString(buf, " ("); + foreach(lc2, action->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc2); + + Assert(!tle->resjunk); + + appendStringInfoString(buf, sep); + sep = ", "; + + appendStringInfoString(buf, + quote_identifier(get_attname(rte->relid, + tle->resno, + false))); + strippedexprs = lappend(strippedexprs, + processIndirection((Node *) tle->expr, + context)); + } + if (action->targetList) + appendStringInfoChar(buf, ')'); + + if (action->override) + { + if (action->override == OVERRIDING_SYSTEM_VALUE) + appendStringInfoString(buf, " OVERRIDING SYSTEM VALUE"); + else if (action->override == OVERRIDING_USER_VALUE) + appendStringInfoString(buf, " OVERRIDING USER VALUE"); + } + + if (strippedexprs) + { + appendContextKeyword(context, " VALUES (", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 4); + get_rule_list_toplevel(strippedexprs, context, false); + appendStringInfoChar(buf, ')'); + } + else + appendStringInfoString(buf, " DEFAULT VALUES"); + } + else if (action->commandType == CMD_UPDATE) + { + appendStringInfoString(buf, "UPDATE SET "); + get_update_query_targetlist_def(query, action->targetList, + context, rte); + } + else if (action->commandType == CMD_DELETE) + appendStringInfoString(buf, "DELETE"); + else if (action->commandType == CMD_NOTHING) + appendStringInfoString(buf, "DO NOTHING"); + } + + /* No RETURNING support in MERGE yet */ + Assert(query->returningList == NIL); +} + + /* ---------- * get_utility_query_def - Parse back a UTILITY parsetree * ---------- diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 69957687fe..764b827372 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3585,6 +3585,91 @@ MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s DELETE WHEN NOT MATCHED THEN INSERT VALUES (s.a, ''); +-- test deparsing +CREATE TABLE sf_target(id int, data text, filling int[]); +CREATE FUNCTION merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) +WHEN MATCHED + AND (s.a + t.id) = 42 + THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b) +WHEN NOT MATCHED + AND (s.b IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.b, s.a) +WHEN MATCHED + AND length(s.b || t.data) > 10 + THEN UPDATE SET data = s.b +WHEN MATCHED + AND s.a > 200 + THEN UPDATE SET filling[s.a] = t.id +WHEN MATCHED + AND s.a > 100 + THEN DELETE +WHEN MATCHED + THEN DO NOTHING +WHEN NOT MATCHED + AND s.a > 200 + THEN INSERT DEFAULT VALUES +WHEN NOT MATCHED + AND s.a > 100 + THEN INSERT (id, data) OVERRIDING USER VALUE + VALUES (s.a, DEFAULT) +WHEN NOT MATCHED + AND s.a > 0 + THEN INSERT + VALUES (s.a, s.b, DEFAULT) +WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.a, s.a); +END; +\sf merge_sf_test +CREATE OR REPLACE FUNCTION public.merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) + WHEN MATCHED + AND ((s.a + t.id) = 42) + THEN UPDATE SET data = (repeat(t.data, s.a) || s.b), id = length(s.b) + WHEN NOT MATCHED + AND (s.b IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.b, s.a) + WHEN MATCHED + AND (length((s.b || t.data)) > 10) + THEN UPDATE SET data = s.b + WHEN MATCHED + AND (s.a > 200) + THEN UPDATE SET filling[s.a] = t.id + WHEN MATCHED + AND (s.a > 100) + THEN DELETE + WHEN MATCHED + THEN DO NOTHING + WHEN NOT MATCHED + AND (s.a > 200) + THEN INSERT DEFAULT VALUES + WHEN NOT MATCHED + AND (s.a > 100) + THEN INSERT (id, data) OVERRIDING USER VALUE + VALUES (s.a, DEFAULT) + WHEN NOT MATCHED + AND (s.a > 0) + THEN INSERT (id, data, filling) + VALUES (s.a, s.b, DEFAULT) + WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.a, s.a); +END +DROP FUNCTION merge_sf_test; +DROP TABLE sf_target; -- -- Test enabling/disabling -- diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 4caab3434b..8b7e255dcd 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1277,6 +1277,55 @@ MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s WHEN NOT MATCHED THEN INSERT VALUES (s.a, ''); +-- test deparsing +CREATE TABLE sf_target(id int, data text, filling int[]); + +CREATE FUNCTION merge_sf_test() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) +WHEN MATCHED + AND (s.a + t.id) = 42 + THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b) +WHEN NOT MATCHED + AND (s.b IS NOT NULL) + THEN INSERT (data, id) + VALUES (s.b, s.a) +WHEN MATCHED + AND length(s.b || t.data) > 10 + THEN UPDATE SET data = s.b +WHEN MATCHED + AND s.a > 200 + THEN UPDATE SET filling[s.a] = t.id +WHEN MATCHED + AND s.a > 100 + THEN DELETE +WHEN MATCHED + THEN DO NOTHING +WHEN NOT MATCHED + AND s.a > 200 + THEN INSERT DEFAULT VALUES +WHEN NOT MATCHED + AND s.a > 100 + THEN INSERT (id, data) OVERRIDING USER VALUE + VALUES (s.a, DEFAULT) +WHEN NOT MATCHED + AND s.a > 0 + THEN INSERT + VALUES (s.a, s.b, DEFAULT) +WHEN NOT MATCHED + THEN INSERT (filling[1], id) + VALUES (s.a, s.a); +END; + +\sf merge_sf_test + +DROP FUNCTION merge_sf_test; +DROP TABLE sf_target; + -- -- Test enabling/disabling -- -- cgit v1.2.1