I've been thinking about adding RETURNING support to MERGE in order to
let the user see what changed.
I considered allowing a separate RETURNING list at the end of each
action, but rapidly dismissed that idea. Firstly, it introduces
shift/reduce conflicts to the grammar. These can be resolved by making
the "AS" before column aliases non-optional, but that's pretty ugly,
and there may be a better way. More serious drawbacks are that this
syntax is much more cumbersome for the end user, having to repeat the
RETURNING clause several times, and the implementation is likely to be
pretty complex, so I didn't pursue it.
A much simpler approach is to just have a single RETURNING list at the
end of the command. That's much easier to implement, and easier for
the end user. The main drawback is that it's impossible for the user
to work out from the values returned which action was actually taken,
and I think that's a pretty essential piece of information (at least
it seems pretty limiting to me, not being able to work that out).
So playing around with it (and inspired by the WITH ORDINALITY syntax
for SRFs), I had the idea of allowing "WITH WHEN CLAUSE" at the end of
the returning list, which adds an integer column to the list, whose
value is set to the index of the when clause executed, as in the
attached very rough patch.
So, quoting an example from the tests, this allows things like:
WITH t AS (
MERGE INTO sq_target t USING v ON tid = sid
WHEN MATCHED AND tid > 2 THEN UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid)
WHEN MATCHED AND tid < 2 THEN DELETE
RETURNING t.* WITH WHEN CLAUSE
)
SELECT CASE when_clause
WHEN 1 THEN 'UPDATE'
WHEN 2 THEN 'INSERT'
WHEN 3 THEN 'DELETE'
END, *
FROM t;
case | tid | balance | when_clause
--------+-----+---------+-------------
INSERT | -1 | -11 | 2
DELETE | 1 | 100 | 3
(2 rows)
1 row is returned for each merge action executed (other than DO
NOTHING actions), and as usual, the values represent old target values
for DELETE actions, and new target values for INSERT/UPDATE actions.
It's also possible to return the source values, and a bare "*" in the
returning list expands to all the source columns, followed by all the
target columns.
The name of the added column, if included, can be changed by
specifying "WITH WHEN CLAUSE [AS] col_alias". I chose the syntax "WHEN
CLAUSE" and "when_clause" as the default column name because those
match the existing terminology used in the docs.
Anyway, this feels like a good point to stop playing around and get
feedback on whether this seems useful, or if anyone has other ideas.
Regards,
Dean
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
new file mode 100644
index 0995fe0..4fc0a65
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -25,6 +25,8 @@ PostgreSQL documentation
MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable>
<replaceable class="parameter">when_clause</replaceable> [...]
+[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...]
+ [ WITH WHEN CLAUSE [ [ AS ] <replaceable class="parameter">col_alias</replaceable> ] ] ]
<phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
new file mode 100644
index e34f583..aa3cca0
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -274,12 +274,6 @@ DoCopy(ParseState *pstate, const CopyStm
{
Assert(stmt->query);
- /* MERGE is allowed by parser, but unimplemented. Reject for now */
- if (IsA(stmt->query, MergeStmt))
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("MERGE not supported in COPY"));
-
query = makeNode(RawStmt);
query->stmt = stmt->query;
query->stmt_location = stmt_location;
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
new file mode 100644
index 8043b4e..e02d7d0
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -510,7 +510,8 @@ BeginCopyTo(ParseState *pstate,
{
Assert(query->commandType == CMD_INSERT ||
query->commandType == CMD_UPDATE ||
- query->commandType == CMD_DELETE);
+ query->commandType == CMD_DELETE ||
+ query->commandType == CMD_MERGE);
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
new file mode 100644
index 651ad24..3391269
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -612,6 +612,9 @@ ExecInitPartitionInfo(ModifyTableState *
* case or in the case of UPDATE tuple routing where we didn't find a
* result rel to reuse.
*/
+
+ /* XXX: What about the MERGE case ??? */
+
if (node && node->returningLists != NIL)
{
TupleTableSlot *slot;
@@ -877,6 +880,7 @@ ExecInitPartitionInfo(ModifyTableState *
List *firstMergeActionList = linitial(node->mergeActionLists);
ListCell *lc;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
+ int action_idx = 1;
if (part_attmap == NULL)
part_attmap =
@@ -897,6 +901,7 @@ ExecInitPartitionInfo(ModifyTableState *
/* Generate the action's state for this relation */
action_state = makeNode(MergeActionState);
action_state->mas_action = action;
+ action_state->mas_action_idx = action_idx++;
/* And put the action in the appropriate list */
if (action->matched)
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
new file mode 100644
index 50e06ec..7e4717a
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -1665,8 +1665,8 @@ check_sql_fn_retval(List *queryTreeLists
/*
* If it's a plain SELECT, it returns whatever the targetlist says.
- * Otherwise, if it's INSERT/UPDATE/DELETE with RETURNING, it returns
- * that. Otherwise, the function return type must be VOID.
+ * Otherwise, if it's INSERT/UPDATE/DELETE/MERGE with RETURNING, it
+ * returns that. Otherwise, the function return type must be VOID.
*
* Note: eventually replace this test with QueryReturnsTuples? We'd need
* a more general method of determining the output type, though. Also, it
@@ -1684,7 +1684,8 @@ check_sql_fn_retval(List *queryTreeLists
else if (parse &&
(parse->commandType == CMD_INSERT ||
parse->commandType == CMD_UPDATE ||
- parse->commandType == CMD_DELETE) &&
+ parse->commandType == CMD_DELETE ||
+ parse->commandType == CMD_MERGE) &&
parse->returningList)
{
tlist = parse->returningList;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 687a542..64f6531
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -36,8 +36,7 @@
* RETURNING tuple after completing each row insert, update, or delete.
* It must be called again to continue the operation. Without RETURNING,
* we just loop within the node until all the work is done, then
- * return NULL. This avoids useless call/return overhead. (MERGE does
- * not support RETURNING.)
+ * return NULL. This avoids useless call/return overhead.
*/
#include "postgres.h"
@@ -172,13 +171,14 @@ static TupleTableSlot *ExecMerge(ModifyT
ItemPointer tupleid,
bool canSetTag);
static void ExecInitMerge(ModifyTableState *mtstate, EState *estate);
-static bool ExecMergeMatched(ModifyTableContext *context,
- ResultRelInfo *resultRelInfo,
- ItemPointer tupleid,
- bool canSetTag);
-static void ExecMergeNotMatched(ModifyTableContext *context,
- ResultRelInfo *resultRelInfo,
- bool canSetTag);
+static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid,
+ bool canSetTag,
+ bool *matched);
+static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
+ bool canSetTag);
static TupleTableSlot *mergeGetUpdateNewTuple(ResultRelInfo *relinfo,
TupleTableSlot *planSlot,
TupleTableSlot *oldSlot,
@@ -2692,6 +2692,7 @@ static TupleTableSlot *
ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, bool canSetTag)
{
+ TupleTableSlot *rslot = NULL;
bool matched;
/*-----
@@ -2739,7 +2740,8 @@ ExecMerge(ModifyTableContext *context, R
*/
matched = tupleid != NULL;
if (matched)
- matched = ExecMergeMatched(context, resultRelInfo, tupleid, canSetTag);
+ rslot = ExecMergeMatched(context, resultRelInfo, tupleid, canSetTag,
+ &matched);
/*
* Either we were dealing with a NOT MATCHED tuple or ExecMergeMatched()
@@ -2747,10 +2749,9 @@ ExecMerge(ModifyTableContext *context, R
* matches.
*/
if (!matched)
- ExecMergeNotMatched(context, resultRelInfo, canSetTag);
+ rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag);
- /* No RETURNING support yet */
- return NULL;
+ return rslot;
}
/*
@@ -2760,8 +2761,8 @@ ExecMerge(ModifyTableContext *context, R
* We start from the first WHEN MATCHED action and check if the WHEN quals
* pass, if any. If the WHEN quals for the first action do not pass, we
* check the second, then the third and so on. If we reach to the end, no
- * action is taken and we return true, indicating that no further action is
- * required for this tuple.
+ * action is taken and "matched" is set to true, indicating that no further
+ * action is required for this tuple.
*
* If we do find a qualifying action, then we attempt to execute the action.
*
@@ -2770,15 +2771,17 @@ ExecMerge(ModifyTableContext *context, R
* with individual actions are evaluated by this routine via ExecQual, while
* EvalPlanQual checks for the join quals. If EvalPlanQual tells us that the
* updated tuple still passes the join quals, then we restart from the first
- * action to look for a qualifying action. Otherwise, we return false --
- * meaning that a NOT MATCHED action must now be executed for the current
- * source tuple.
+ * action to look for a qualifying action. Otherwise, "matched" is set to
+ * false -- meaning that a NOT MATCHED action must now be executed for the
+ * current source tuple.
*/
-static bool
+static TupleTableSlot *
ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
- ItemPointer tupleid, bool canSetTag)
+ ItemPointer tupleid, bool canSetTag, bool *matched)
{
ModifyTableState *mtstate = context->mtstate;
+ ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
+ TupleTableSlot *rslot = NULL;
TupleTableSlot *newslot;
EState *estate = context->estate;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
@@ -2790,7 +2793,10 @@ ExecMergeMatched(ModifyTableContext *con
* If there are no WHEN MATCHED actions, we are done.
*/
if (resultRelInfo->ri_matchedMergeAction == NIL)
- return true;
+ {
+ *matched = true;
+ return NULL;
+ }
/*
* Make tuple and any needed join variables available to ExecQual and
@@ -2952,7 +2958,8 @@ lmerge_matched:
* If the tuple was already deleted, return to let caller
* handle it under NOT MATCHED clauses.
*/
- return false;
+ *matched = false;
+ return NULL;
case TM_Updated:
{
@@ -3020,13 +3027,19 @@ lmerge_matched:
* NOT MATCHED actions.
*/
if (TupIsNull(epqslot))
- return false;
+ {
+ *matched = false;
+ return NULL;
+ }
(void) ExecGetJunkAttribute(epqslot,
resultRelInfo->ri_RowIdAttNo,
&isNull);
if (isNull)
- return false;
+ {
+ *matched = false;
+ return NULL;
+ }
/*
* When a tuple was updated and migrated to
@@ -3061,7 +3074,8 @@ lmerge_matched:
* tuple already deleted; tell caller to run NOT
* MATCHED actions
*/
- return false;
+ *matched = false;
+ return NULL;
case TM_SelfModified:
@@ -3081,13 +3095,14 @@ lmerge_matched:
(errcode(ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION),
errmsg("tuple to be updated or deleted was already modified by an operation triggered by the current command"),
errhint("Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows.")));
- return false;
+ *matched = false;
+ return NULL;
default:
/* see table_tuple_lock call in ExecDelete() */
elog(ERROR, "unexpected table_tuple_lock status: %u",
result);
- return false;
+ return NULL;
}
}
@@ -3099,6 +3114,39 @@ lmerge_matched:
break;
}
+ /* Process RETURNING if present */
+ if (resultRelInfo->ri_projectReturning)
+ {
+ switch (commandType)
+ {
+ case CMD_UPDATE:
+ rslot = ExecProcessReturning(resultRelInfo, newslot,
+ context->planSlot);
+ break;
+
+ case CMD_DELETE:
+ rslot = ExecProcessReturning(resultRelInfo,
+ resultRelInfo->ri_oldTupleSlot,
+ context->planSlot);
+ break;
+
+ case CMD_NOTHING:
+ break;
+
+ default:
+ elog(ERROR, "unknown action in MERGE WHEN MATCHED clause");
+ }
+ }
+
+ /* Set RETURNING WITH WHEN CLAUSE result, if required */
+ if (rslot && node->hasReturningWhenClause)
+ {
+ int attnum = rslot->tts_tupleDescriptor->natts - 1;
+
+ rslot->tts_values[attnum] = Int32GetDatum(relaction->mas_action_idx);
+ rslot->tts_isnull[attnum] = false;
+ }
+
/*
* We've activated one of the WHEN clauses, so we don't search
* further. This is required behaviour, not an optimization.
@@ -3109,19 +3157,23 @@ lmerge_matched:
/*
* Successfully executed an action or no qualifying action was found.
*/
- return true;
+ *matched = true;
+
+ return rslot;
}
/*
* Execute the first qualifying NOT MATCHED action.
*/
-static void
+static TupleTableSlot *
ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
bool canSetTag)
{
ModifyTableState *mtstate = context->mtstate;
+ ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
List *actionStates = NIL;
+ TupleTableSlot *rslot = NULL;
ListCell *l;
/*
@@ -3173,9 +3225,18 @@ ExecMergeNotMatched(ModifyTableContext *
newslot = ExecProject(action->mas_proj);
context->relaction = action;
- (void) ExecInsert(context, mtstate->rootResultRelInfo, newslot,
- canSetTag, NULL, NULL);
+ rslot = ExecInsert(context, mtstate->rootResultRelInfo,
+ newslot, canSetTag, NULL, NULL);
mtstate->mt_merge_inserted += 1;
+
+ /* Set RETURNING WITH WHEN CLAUSE result, if requested */
+ if (rslot && node->hasReturningWhenClause)
+ {
+ int attnum = rslot->tts_tupleDescriptor->natts - 1;
+
+ rslot->tts_values[attnum] = Int32GetDatum(action->mas_action_idx);
+ rslot->tts_isnull[attnum] = false;
+ }
break;
case CMD_NOTHING:
/* Do nothing */
@@ -3190,6 +3251,8 @@ ExecMergeNotMatched(ModifyTableContext *
*/
break;
}
+
+ return rslot;
}
/*
@@ -3227,6 +3290,7 @@ ExecInitMerge(ModifyTableState *mtstate,
List *mergeActionList = lfirst(lc);
TupleDesc relationDesc;
ListCell *l;
+ int action_idx;
resultRelInfo = mtstate->resultRelInfo + i;
i++;
@@ -3236,6 +3300,7 @@ ExecInitMerge(ModifyTableState *mtstate,
if (unlikely(!resultRelInfo->ri_projectNewInfoValid))
ExecInitMergeTupleSlots(mtstate, resultRelInfo);
+ action_idx = 1;
foreach(l, mergeActionList)
{
MergeAction *action = (MergeAction *) lfirst(l);
@@ -3250,6 +3315,7 @@ ExecInitMerge(ModifyTableState *mtstate,
*/
action_state = makeNode(MergeActionState);
action_state->mas_action = action;
+ action_state->mas_action_idx = action_idx++;
action_state->mas_whenqual = ExecInitQual((List *) action->qual,
&mtstate->ps);
@@ -3671,8 +3737,17 @@ ExecModifyTable(PlanState *pstate)
{
EvalPlanQualSetSlot(&node->mt_epqstate, context.planSlot);
- ExecMerge(&context, node->resultRelInfo, NULL, node->canSetTag);
- continue; /* no RETURNING support yet */
+ slot = ExecMerge(&context, node->resultRelInfo, NULL,
+ node->canSetTag);
+
+ /*
+ * If we got a RETURNING result, return it to the caller.
+ * We'll continue the work on next call.
+ */
+ if (slot)
+ return slot;
+
+ continue; /* continue with the next tuple */
}
elog(ERROR, "tableoid is NULL");
@@ -3749,8 +3824,17 @@ ExecModifyTable(PlanState *pstate)
{
EvalPlanQualSetSlot(&node->mt_epqstate, context.planSlot);
- ExecMerge(&context, node->resultRelInfo, NULL, node->canSetTag);
- continue; /* no RETURNING support yet */
+ slot = ExecMerge(&context, node->resultRelInfo, NULL,
+ node->canSetTag);
+
+ /*
+ * If we got a RETURNING result, return it to the
+ * caller. We'll continue the work on next call.
+ */
+ if (slot)
+ return slot;
+
+ continue; /* continue with the next tuple */
}
elog(ERROR, "ctid is NULL");
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
new file mode 100644
index aa24958..90b7fdc
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -7056,6 +7056,7 @@ make_modifytable(PlannerInfo *root, Plan
node->rowMarks = rowMarks;
node->mergeActionLists = mergeActionLists;
node->epqParam = epqParam;
+ node->hasReturningWhenClause = root->parse->hasReturningWhenClause;
/*
* For each result relation that is a foreign table, allow the FDW to
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
new file mode 100644
index 5b90974..50285a2
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -73,7 +73,6 @@ static void determineRecursiveColTypes(P
Node *larg, List *nrtargetlist);
static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt);
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
-static List *transformReturningList(ParseState *pstate, List *returningList);
static Query *transformPLAssignStmt(ParseState *pstate,
PLAssignStmt *stmt);
static Query *transformDeclareCursorStmt(ParseState *pstate,
@@ -2501,7 +2500,7 @@ transformUpdateTargetList(ParseState *ps
* transformReturningList -
* handle a RETURNING clause in INSERT/UPDATE/DELETE
*/
-static List *
+List *
transformReturningList(ParseState *pstate, List *returningList)
{
List *rlist;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index a013838..cf8599a
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -151,6 +151,13 @@ typedef struct KeyActions
KeyAction *deleteAction;
} KeyActions;
+/* Private struct for the result of merge_returning_clause */
+typedef struct MergeReturningClause
+{
+ List *returningList;
+ char *returningWhenClause;
+} MergeReturningClause;
+
/* ConstraintAttributeSpec yields an integer bitmask of these flags: */
#define CAS_NOT_DEFERRABLE 0x01
#define CAS_DEFERRABLE 0x02
@@ -274,6 +281,7 @@ static Node *makeRecursiveViewSelect(cha
SetQuantifier setquantifier;
struct GroupClause *groupclause;
MergeWhenClause *mergewhen;
+ struct MergeReturningClause *mergereturning;
struct KeyActions *keyactions;
struct KeyAction *keyaction;
}
@@ -511,6 +519,7 @@ static Node *makeRecursiveViewSelect(cha
%type <infer> opt_conf_expr
%type <onconflict> opt_on_conflict
%type <mergewhen> merge_insert merge_update merge_delete
+%type <mergereturning> merge_returning_clause
%type <node> merge_when_clause opt_merge_when_condition
%type <list> merge_when_list
@@ -677,7 +686,7 @@ static Node *makeRecursiveViewSelect(cha
BOOLEAN_P BOTH BREADTH BY
CACHE CALL CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P
- CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
+ CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLAUSE CLOSE
CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS COMMIT
COMMITTED COMPRESSION CONCURRENTLY CONFIGURATION CONFLICT
CONNECTION CONSTRAINT CONSTRAINTS CONTENT_P CONTINUE_P CONVERSION_P COPY
@@ -12241,6 +12250,7 @@ MergeStmt:
USING table_ref
ON a_expr
merge_when_list
+ merge_returning_clause
{
MergeStmt *m = makeNode(MergeStmt);
@@ -12249,6 +12259,8 @@ MergeStmt:
m->sourceRelation = $6;
m->joinCondition = $8;
m->mergeWhenClauses = $9;
+ m->returningList = $10 ? $10->returningList : NIL;
+ m->returningWhenClause = $10 ? $10->returningWhenClause : NULL;
$$ = (Node *) m;
}
@@ -12389,6 +12401,45 @@ merge_values_clause:
}
;
+merge_returning_clause:
+ RETURNING target_list WITH WHEN CLAUSE AS ColLabel
+ {
+ MergeReturningClause *n = palloc(sizeof(MergeReturningClause));
+
+ n->returningList = $2;
+ n->returningWhenClause = $7;
+ $$ = n;
+ }
+ | RETURNING target_list WITH WHEN CLAUSE ColLabel
+ {
+ MergeReturningClause *n = palloc(sizeof(MergeReturningClause));
+
+ n->returningList = $2;
+ n->returningWhenClause = $6;
+ $$ = n;
+ }
+ | RETURNING target_list WITH WHEN CLAUSE
+ {
+ MergeReturningClause *n = palloc(sizeof(MergeReturningClause));
+
+ n->returningList = $2;
+ n->returningWhenClause = pstrdup("when_clause");
+ $$ = n;
+ }
+ | RETURNING target_list
+ {
+ MergeReturningClause *n = palloc(sizeof(MergeReturningClause));
+
+ n->returningList = $2;
+ n->returningWhenClause = NULL;
+ $$ = n;
+ }
+ | /* EMPTY */
+ {
+ $$ = NULL;
+ }
+ ;
+
/*****************************************************************************
*
* QUERY:
@@ -16744,6 +16795,7 @@ unreserved_keyword:
| CHARACTERISTICS
| CHECKPOINT
| CLASS
+ | CLAUSE
| CLOSE
| CLUSTER
| COLUMNS
@@ -17269,6 +17321,7 @@ bare_label_keyword:
| CHECK
| CHECKPOINT
| CLASS
+ | CLAUSE
| CLOSE
| CLUSTER
| COALESCE
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
new file mode 100644
index c5b1a49..ad3c525
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -126,13 +126,6 @@ transformWithClause(ParseState *pstate,
CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
ListCell *rest;
- /* MERGE is allowed by parser, but unimplemented. Reject for now */
- if (IsA(cte->ctequery, MergeStmt))
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("MERGE not supported in WITH query"),
- parser_errposition(pstate, cte->location));
-
for_each_cell(rest, withClause->ctes, lnext(withClause->ctes, lc))
{
CommonTableExpr *cte2 = (CommonTableExpr *) lfirst(rest);
@@ -153,7 +146,8 @@ transformWithClause(ParseState *pstate,
/* must be a data-modifying statement */
Assert(IsA(cte->ctequery, InsertStmt) ||
IsA(cte->ctequery, UpdateStmt) ||
- IsA(cte->ctequery, DeleteStmt));
+ IsA(cte->ctequery, DeleteStmt) ||
+ IsA(cte->ctequery, MergeStmt));
pstate->p_hasModifyingCTE = true;
}
diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c
new file mode 100644
index 611dfce..0ba114c
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -233,6 +233,34 @@ transformMergeStmt(ParseState *pstate, M
*/
qry->jointree = makeFromExpr(pstate->p_joinlist, joinExpr);
+ /* Transform the RETURNING list, if any */
+ qry->returningList = transformReturningList(pstate, stmt->returningList);
+
+ /* Add integer column for RETURNING ... WITH WHEN CLAUSE, if specified */
+ if (stmt->returningWhenClause)
+ {
+ Expr *null_expr;
+ TargetEntry *tle;
+
+ null_expr = (Expr *) makeConst(INT4OID,
+ -1,
+ InvalidOid,
+ sizeof(int32),
+ (Datum) 0,
+ true,
+ true);
+
+ tle = makeTargetEntry(null_expr,
+ list_length(qry->returningList) + 1,
+ stmt->returningWhenClause,
+ false);
+
+ qry->returningList = lappend(qry->returningList, tle);
+ qry->hasReturningWhenClause = true;
+ }
+ else
+ qry->hasReturningWhenClause = false;
+
/*
* We now have a good query shape, so now look at the WHEN conditions and
* action targetlists.
@@ -390,9 +418,6 @@ transformMergeStmt(ParseState *pstate, M
qry->mergeActionList = mergeActionList;
- /* RETURNING could potentially be added in the future, but not in SQL std */
- qry->returningList = NULL;
-
qry->hasTargetSRFs = false;
qry->hasSubLinks = pstate->p_hasSubLinks;
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
new file mode 100644
index 5389a0e..162a305
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -2313,9 +2313,10 @@ addRangeTableEntryForCTE(ParseState *pst
cte->cterefcount++;
/*
- * We throw error if the CTE is INSERT/UPDATE/DELETE without RETURNING.
- * This won't get checked in case of a self-reference, but that's OK
- * because data-modifying CTEs aren't allowed to be recursive anyhow.
+ * We throw error if the CTE is INSERT/UPDATE/DELETE/MERGE without
+ * RETURNING. This won't get checked in case of a self-reference, but
+ * that's OK because data-modifying CTEs aren't allowed to be recursive
+ * anyhow.
*/
if (IsA(cte->ctequery, Query))
{
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
new file mode 100644
index 1960dad..ac5a0c7
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3640,7 +3640,8 @@ RewriteQuery(Query *parsetree, List *rew
if (!(ctequery->commandType == CMD_SELECT ||
ctequery->commandType == CMD_UPDATE ||
ctequery->commandType == CMD_INSERT ||
- ctequery->commandType == CMD_DELETE))
+ ctequery->commandType == CMD_DELETE ||
+ ctequery->commandType == CMD_MERGE))
{
/*
* Currently it could only be NOTIFY; this error message will
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
new file mode 100644
index c7d9d96..d393f51
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -2131,11 +2131,10 @@ QueryReturnsTuples(Query *parsetree)
case CMD_SELECT:
/* returns tuples */
return true;
- case CMD_MERGE:
- return false;
case CMD_INSERT:
case CMD_UPDATE:
case CMD_DELETE:
+ case CMD_MERGE:
/* the forms with RETURNING return tuples */
if (parsetree->returningList)
return true;
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
new file mode 100644
index 38f9b10..024e75a
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -962,13 +962,17 @@ PrintQueryResult(PGresult *result, bool
else
success = true;
- /* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
+ /*
+ * If it's INSERT/UPDATE/DELETE/MERGE RETURNING, also print
+ * status.
+ */
if (last || pset.show_all_results)
{
cmdstatus = PQcmdStatus(result);
if (strncmp(cmdstatus, "INSERT", 6) == 0 ||
strncmp(cmdstatus, "UPDATE", 6) == 0 ||
- strncmp(cmdstatus, "DELETE", 6) == 0)
+ strncmp(cmdstatus, "DELETE", 6) == 0 ||
+ strncmp(cmdstatus, "MERGE", 5) == 0)
PrintQueryStatus(result, printStatusFout);
}
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
new file mode 100644
index 20f4c8b..3a25c93
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -415,6 +415,7 @@ typedef struct MergeActionState
NodeTag type;
MergeAction *mas_action; /* associated MergeAction node */
+ int mas_action_idx; /* 1-based index of MergeAction node */
ProjectionInfo *mas_proj; /* projection of the action's targetlist for
* this rel */
ExprState *mas_whenqual; /* WHEN [NOT] MATCHED AND conditions */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index cfeca96..c8fc76c
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -160,6 +160,7 @@ typedef struct Query
List *mergeActionList; /* list of actions for MERGE (only) */
bool mergeUseOuterJoin; /* whether to use outer join */
+ bool hasReturningWhenClause; /* has RETURNING WITH WHEN CLAUSE */
List *targetList; /* target list (of TargetEntry) */
@@ -1713,6 +1714,9 @@ typedef struct MergeStmt
Node *sourceRelation; /* source relation */
Node *joinCondition; /* join condition between source and target */
List *mergeWhenClauses; /* list of MergeWhenClause(es) */
+ List *returningList; /* list of expressions to return */
+ char *returningWhenClause; /* RETURNING ... WITH WHEN CLAUSE
+ * column alias */
WithClause *withClause; /* WITH clause */
} MergeStmt;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
new file mode 100644
index c1234fc..c8c8b97
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -254,6 +254,7 @@ typedef struct ModifyTable
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
List *mergeActionLists; /* per-target-table lists of actions for
* MERGE */
+ bool hasReturningWhenClause; /* has RETURNING WITH WHEN CLAUSE */
} ModifyTable;
struct PartitionPruneInfo; /* forward reference to struct below */
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
new file mode 100644
index c97be6e..34cc1ec
--- a/src/include/parser/analyze.h
+++ b/src/include/parser/analyze.h
@@ -44,6 +44,7 @@ extern List *transformInsertRow(ParseSta
bool strip_indirection);
extern List *transformUpdateTargetList(ParseState *pstate,
List *origTlist);
+extern List *transformReturningList(ParseState *pstate, List *returningList);
extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree);
extern Query *transformStmt(ParseState *pstate, Node *parseTree);
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
new file mode 100644
index bb36213..e3860fa
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -79,6 +79,7 @@ PG_KEYWORD("characteristics", CHARACTERI
PG_KEYWORD("check", CHECK, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("checkpoint", CHECKPOINT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("class", CLASS, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("clause", CLAUSE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("close", CLOSE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("cluster", CLUSTER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("coalesce", COALESCE, COL_NAME_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 6c8a18f..0adf7c1
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -123,20 +123,20 @@ ON tid = tid
WHEN MATCHED THEN DO NOTHING;
ERROR: name "target" specified more than once
DETAIL: The name is used both as MERGE target table and data source.
--- used in a CTE
+-- used in a CTE without RETURNING
WITH foo AS (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
) SELECT * FROM foo;
-ERROR: MERGE not supported in WITH query
-LINE 1: WITH foo AS (
- ^
--- used in COPY
+ERROR: WITH query "foo" does not have a RETURNING clause
+LINE 4: ) SELECT * FROM foo;
+ ^
+-- used in COPY without RETURNING
COPY (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
) TO stdout;
-ERROR: MERGE not supported in COPY
+ERROR: COPY query must have a RETURNING clause
-- unsupported relation types
-- view
CREATE VIEW tv AS SELECT * FROM target;
@@ -1299,11 +1299,69 @@ WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
WHEN MATCHED AND tid < 2 THEN
DELETE
-RETURNING *;
-ERROR: syntax error at or near "RETURNING"
-LINE 10: RETURNING *;
- ^
+RETURNING t.*;
+ tid | balance
+-----+---------
+ -1 | -11
+ 1 | 100
+(2 rows)
+
+ROLLBACK;
+-- RETURNING ... WITH WHEN CLAUSE
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+WITH t AS (
+ MERGE INTO sq_target t
+ USING v
+ ON tid = sid
+ WHEN MATCHED AND tid > 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING t.* WITH WHEN CLAUSE
+)
+SELECT CASE when_clause
+ WHEN 1 THEN 'UPDATE'
+ WHEN 2 THEN 'INSERT'
+ WHEN 3 THEN 'DELETE'
+ END, *
+FROM t;
+ case | tid | balance | when_clause
+--------+-----+---------+-------------
+ INSERT | -1 | -11 | 2
+ DELETE | 1 | 100 | 3
+(2 rows)
+
ROLLBACK;
+-- RETURNING ... WITH WHEN CLAUSE [AS] alias
+MERGE INTO sq_target t
+USING sq_source s
+ON tid = sid
+WHEN MATCHED AND tid = 1 THEN
+ DO NOTHING
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + delta
+RETURNING t.* WITH WHEN CLAUSE AS wc;
+ tid | balance | wc
+-----+---------+----
+ 2 | 220 | 2
+(1 row)
+
+MERGE INTO sq_target t
+USING sq_source s
+ON tid = sid
+WHEN MATCHED AND tid = 2 THEN
+ UPDATE SET balance = t.balance - delta
+WHEN MATCHED THEN
+ DO NOTHING
+RETURNING t.* WITH WHEN CLAUSE wc;
+ tid | balance | wc
+-----+---------+----
+ 2 | 200 | 1
+(1 row)
+
-- 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
new file mode 100644
index 98fe104..42f2483
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -86,12 +86,12 @@ MERGE INTO target
USING target
ON tid = tid
WHEN MATCHED THEN DO NOTHING;
--- used in a CTE
+-- used in a CTE without RETURNING
WITH foo AS (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
) SELECT * FROM foo;
--- used in COPY
+-- used in COPY without RETURNING
COPY (
MERGE INTO target USING source ON (true)
WHEN MATCHED THEN DELETE
@@ -854,9 +854,51 @@ WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
WHEN MATCHED AND tid < 2 THEN
DELETE
-RETURNING *;
+RETURNING t.*;
+ROLLBACK;
+
+-- RETURNING ... WITH WHEN CLAUSE
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+WITH t AS (
+ MERGE INTO sq_target t
+ USING v
+ ON tid = sid
+ WHEN MATCHED AND tid > 2 THEN
+ UPDATE SET balance = t.balance + delta
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING t.* WITH WHEN CLAUSE
+)
+SELECT CASE when_clause
+ WHEN 1 THEN 'UPDATE'
+ WHEN 2 THEN 'INSERT'
+ WHEN 3 THEN 'DELETE'
+ END, *
+FROM t;
ROLLBACK;
+-- RETURNING ... WITH WHEN CLAUSE [AS] alias
+MERGE INTO sq_target t
+USING sq_source s
+ON tid = sid
+WHEN MATCHED AND tid = 1 THEN
+ DO NOTHING
+WHEN MATCHED THEN
+ UPDATE SET balance = t.balance + delta
+RETURNING t.* WITH WHEN CLAUSE AS wc;
+
+MERGE INTO sq_target t
+USING sq_source s
+ON tid = sid
+WHEN MATCHED AND tid = 2 THEN
+ UPDATE SET balance = t.balance - delta
+WHEN MATCHED THEN
+ DO NOTHING
+RETURNING t.* WITH WHEN CLAUSE wc;
+
-- EXPLAIN
CREATE TABLE ex_mtarget (a int, b int)
WITH (autovacuum_enabled=off);
