On 2023-May-05, Tom Lane wrote:
> (I'd guess that decompiling the WHEN clause would take a nontrivial
> amount of new code, so maybe fixing it on such short notice is
> impractical. But ugh.)
Here's a first attempt. I mostly just copied code from the insert and
update support routines. There's a couple of things missing still, but
I'm not sure I'll get to it tonight. I only tested to the extent of
what's in the new regression test.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
<Schwern> It does it in a really, really complicated way
<crab> why does it need to be complicated?
<Schwern> Because it's MakeMaker.
>From a89d46592ba4d113f57a890b63ef123d8470db45 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Fri, 5 May 2023 20:40:38 +0200
Subject: [PATCH] add ruleutils.c support for MERGE
---
src/backend/utils/adt/ruleutils.c | 119 ++++++++++++++++++++++++++++
src/test/regress/expected/merge.out | 68 ++++++++++++++++
src/test/regress/sql/merge.sql | 38 +++++++++
3 files changed, 225 insertions(+)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 461735e84f0..851488dd6d3 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;
@@ -7043,6 +7049,119 @@ 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);
+
+ get_from_clause(query, " USING ", context);
+ appendContextKeyword(context, " ON ",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
+ get_rule_expr(query->jointree->quals, context, false);
+
+ appendStringInfoChar(buf, '\n'); /* FIXME proper way to do this? */
+
+ /* FIXME missing: OVERRIDING clause */
+ foreach(lc, query->mergeActionList)
+ {
+ MergeAction *action = lfirst_node(MergeAction, lc);
+
+ appendStringInfo(buf, "WHEN %sMATCHED", action->matched ? "" : "NOT ");
+
+ if (action->qual)
+ {
+ appendContextKeyword(context, " AND ",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
+ get_rule_expr(action->qual, context, false);
+ }
+ appendContextKeyword(context, " THEN ", -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
+
+ if (action->commandType == CMD_INSERT)
+ {
+ ListCell *lc2;
+ char *sep = "";
+ List *strippedexprs = NIL;
+
+ appendStringInfoString(buf, "INSERT ");
+
+ /*
+ * This matches what get_insert_query_def does for the VALUES
+ * clause
+ */
+ if (action->targetList)
+ appendStringInfoChar(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)
+ appendStringInfoString(buf, ") ");
+ if (strippedexprs)
+ {
+ appendContextKeyword(context, "VALUES (",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
+ 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 ");
+
+ appendStringInfoChar(buf, '\n');
+ }
+
+ /* No RETURNING support yet */
+ Assert(query->returningList == NIL);
+}
/* ----------
* get_utility_query_def - Parse back a UTILITY parsetree
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 133d42117c0..2e71dda4228 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1319,6 +1319,74 @@ ERROR: syntax error at or near "RETURNING"
LINE 10: RETURNING *;
^
ROLLBACK;
+-- Verify that ruleutils can properly deparse
+CREATE TABLE sf_target(id int, data text, filling int[]);
+CREATE TABLE sf_source(id int, data text);
+CREATE OR REPLACE FUNCTION public.merge_sf_test()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ MERGE INTO sf_target
+ USING sf_source s
+ ON (s.id = sf_target.id)
+WHEN MATCHED
+ AND ((s.id + sf_target.id) = 42)
+ THEN UPDATE SET data = (repeat(sf_target.data, s.id) || s.data), id = length(s.data)
+WHEN NOT MATCHED
+ AND (s.data IS NOT NULL)
+ THEN INSERT (data, id)
+ VALUES (s.data, s.id)
+WHEN MATCHED
+ AND (length((s.data || sf_target.data)) > 10)
+ THEN UPDATE SET data = s.data
+WHEN MATCHED
+ THEN UPDATE SET filling[s.id] = sf_target.id
+WHEN NOT MATCHED
+ AND (s.id > 200)
+ THEN INSERT DEFAULT VALUES
+WHEN NOT MATCHED
+ AND (s.id > 100)
+ THEN INSERT (id, data)
+ VALUES (s.id, DEFAULT)
+WHEN NOT MATCHED
+ THEN INSERT (filling[1], id)
+ VALUES (s.id, s.id)
+;
+END;
+\sf+ merge_sf_test
+ CREATE OR REPLACE FUNCTION public.merge_sf_test()
+ RETURNS void
+ LANGUAGE sql
+1 BEGIN ATOMIC
+2 MERGE INTO sf_target
+3 USING sf_source s
+4 ON (s.id = sf_target.id)
+5 WHEN MATCHED
+6 AND ((s.id + sf_target.id) = 42)
+7 THEN UPDATE SET data = (repeat(sf_target.data, s.id) || s.data), id = length(s.data)
+8 WHEN NOT MATCHED
+9 AND (s.data IS NOT NULL)
+10 THEN INSERT (data, id)
+11 VALUES (s.data, s.id)
+12 WHEN MATCHED
+13 AND (length((s.data || sf_target.data)) > 10)
+14 THEN UPDATE SET data = s.data
+15 WHEN MATCHED
+16 THEN UPDATE SET filling[s.id] = sf_target.id
+17 WHEN NOT MATCHED
+18 AND (s.id > 200)
+19 THEN INSERT DEFAULT VALUES
+20 WHEN NOT MATCHED
+21 AND (s.id > 100)
+22 THEN INSERT (id, data)
+23 VALUES (s.id, DEFAULT)
+24 WHEN NOT MATCHED
+25 THEN INSERT (filling[1], id)
+26 VALUES (s.id, s.id)
+27 ;
+28 END
+DROP FUNCTION merge_sf_test;
+DROP TABLE sf_target, sf_source;
-- EXPLAIN
CREATE TABLE ex_mtarget (a int, b int)
WITH (autovacuum_enabled=off);
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
index 4cf6db908b5..41662089905 100644
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -870,6 +870,44 @@ WHEN MATCHED AND tid < 2 THEN
RETURNING *;
ROLLBACK;
+-- Verify that ruleutils can properly deparse
+CREATE TABLE sf_target(id int, data text, filling int[]);
+CREATE TABLE sf_source(id int, data text);
+CREATE OR REPLACE FUNCTION public.merge_sf_test()
+ RETURNS void
+ LANGUAGE sql
+BEGIN ATOMIC
+ MERGE INTO sf_target
+ USING sf_source s
+ ON (s.id = sf_target.id)
+WHEN MATCHED
+ AND ((s.id + sf_target.id) = 42)
+ THEN UPDATE SET data = (repeat(sf_target.data, s.id) || s.data), id = length(s.data)
+WHEN NOT MATCHED
+ AND (s.data IS NOT NULL)
+ THEN INSERT (data, id)
+ VALUES (s.data, s.id)
+WHEN MATCHED
+ AND (length((s.data || sf_target.data)) > 10)
+ THEN UPDATE SET data = s.data
+WHEN MATCHED
+ THEN UPDATE SET filling[s.id] = sf_target.id
+WHEN NOT MATCHED
+ AND (s.id > 200)
+ THEN INSERT DEFAULT VALUES
+WHEN NOT MATCHED
+ AND (s.id > 100)
+ THEN INSERT (id, data)
+ VALUES (s.id, DEFAULT)
+WHEN NOT MATCHED
+ THEN INSERT (filling[1], id)
+ VALUES (s.id, s.id)
+;
+END;
+\sf+ merge_sf_test
+DROP FUNCTION merge_sf_test;
+DROP TABLE sf_target, sf_source;
+
-- EXPLAIN
CREATE TABLE ex_mtarget (a int, b int)
WITH (autovacuum_enabled=off);
--
2.39.2
