On Wed, 25 Oct 2023 at 02:07, Merlin Moncure <mmonc...@gmail.com> wrote:
>
> On Tue, Oct 24, 2023 at 2:11 PM Jeff Davis <pg...@j-davis.com> wrote:
>>
>> Can we revisit the idea of a per-WHEN RETURNING clause? The returning
>> clauses could be treated kind of like a UNION, which makes sense
>> because it really is a union of different results (the returned tuples
>> from an INSERT are different than the returned tuples from a DELETE).
>> You can just add constants to the target lists to distinguish which
>> WHEN clause they came from.
>>
> Yeah. Side benefit, the 'action_number' felt really out of place, and that
> neatly might solve it. It doesn't match tg_op, for example. With the
> current approach, return a text, or an enum? Why doesn't it match concepts
> that are pretty well established elsewhere? SQL has a pretty good track
> record for not inventing weird numbers with no real meaning (sadly, not so
> much the developers). Having said that, pg_merge_action() doesn't feel too
> bad if the syntax issues can be worked out.
>
I've been playing around a little with per-action RETURNING lists, and
attached is a working proof-of-concept (no docs yet).
The implementation is simplified a little by not needing special merge
support functions, but overall this approach introduces a little more
complexity, which is perhaps not surprising.
One fiddly part is resolving the shift/reduce conflicts in the
grammar. Specifically, on seeing "RETURNING expr when ...", there is
ambiguity over whether the "when" is a column alias or the start of
the next merge action. I've resolved that by assigning a slightly
higher precedence to an expression without an alias, so WHEN is
assumed to not be an alias. It seems pretty ugly though (in terms of
having to duplicate so much code), and I'd be interested to know if
there's a neater way to do it.
>From a usability perspective, I'm still somewhat sceptical about this
approach. It's a much more verbose syntax, and it gets quite tedious
having to repeat the RETURNING list for every action, and keep them in
sync. I also note that other database vendors seem to have opted for
the single RETURNING list approach (not that we necessarily need to
copy them).
The patch enforces the rule that if any action has a RETURNING list,
they all must have a RETURNING list. Not doing that leads to the
number of rows returned not matching the command tag, or the number of
rows modified, which I think would just lead to confusion. Also, it
would likely be a source of easy-to-overlook mistakes. One such
mistake would be assuming that a RETURNING list at the very end
applied to all actions, though it would also be easy to accidentally
omit a RETURNING list in the middle of the command.
Having said that, I wonder if it would make sense to also support
having a RETURNING list at the very end, if there are no other
RETURNING lists. If we see that, we could automatically apply it to
all actions, which I think would be much more convenient in situations
where you don't care about the action executed, and just want the
results from the table. That would go a long way towards addressing my
usability concerns.
Regards,
Dean
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
new file mode 100644
index c5d7d78..7977873
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -283,12 +283,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 c66a047..1145aaf
--- 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 f6c3432..f0e75c3
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -613,16 +613,16 @@ ExecInitPartitionInfo(ModifyTableState *
* build the returningList for partitions within the planner, but simple
* translation of varattnos will suffice. This only occurs for the INSERT
* case or in the case of UPDATE tuple routing where we didn't find a
- * result rel to reuse.
+ * result rel to reuse. We skip this for MERGE, since it uses per-action
+ * RETURNING lists, which are handled below.
*/
- if (node && node->returningLists != NIL)
+ if (node && node->returningLists != NIL && node->operation != CMD_MERGE)
{
TupleTableSlot *slot;
ExprContext *econtext;
List *returningList;
/* See the comment above for WCO lists. */
- /* (except no RETURNING support for MERGE yet) */
Assert((node->operation == CMD_INSERT &&
list_length(node->returningLists) == 1 &&
list_length(node->resultRelations) == 1) ||
@@ -959,6 +959,25 @@ ExecInitPartitionInfo(ModifyTableState *
&found_whole_row);
action_state->mas_whenqual =
ExecInitQual((List *) action->qual, &mtstate->ps);
+
+ /* Build a projection for the action's RETURNING list, if any */
+ if (action->returningList)
+ {
+ /* found_whole_row intentionally ignored. */
+ action->returningList = (List *)
+ map_variable_attnos((Node *) action->returningList,
+ firstVarno, 0,
+ part_attmap,
+ RelationGetForm(partrel)->reltype,
+ &found_whole_row);
+
+ action_state->mas_proj_returning =
+ ExecBuildProjectionInfo(action->returningList,
+ econtext,
+ mtstate->ps.ps_ResultTupleSlot,
+ &mtstate->ps,
+ RelationGetDescr(partrel));
+ }
}
}
MemoryContextSwitchTo(oldcxt);
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
new file mode 100644
index bace252..68cb4fa
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -1660,8 +1660,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
@@ -1679,7 +1679,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;
@@ -1693,7 +1694,7 @@ check_sql_fn_retval(List *queryTreeLists
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
errmsg("return type mismatch in function declared to return %s",
format_type_be(rettype)),
- errdetail("Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.")));
+ errdetail("Function's final statement must be SELECT or INSERT/UPDATE/DELETE/MERGE RETURNING.")));
return false; /* keep compiler quiet */
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 299c2c7..b8a3500
--- 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"
@@ -90,6 +89,7 @@ typedef struct ModifyTableContext
/* MERGE specific */
MergeActionState *relaction; /* MERGE action in progress */
+ int relaction_idx; /* its position in the list */
/*
* Information about the changes that were made concurrently to a tuple
@@ -153,13 +153,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);
/*
@@ -779,6 +780,19 @@ ExecInsert(ModifyTableContext *context,
slot = ExecPrepareTupleRouting(mtstate, estate, proute,
resultRelInfo, slot,
&partRelInfo);
+
+ /*
+ * If we're running MERGE, update the context's merge action to the
+ * corresponding action for this partition, so that we have the
+ * correct projection for RETURNING below.
+ */
+ if (mtstate->operation == CMD_MERGE)
+ context->relaction = (MergeActionState *)
+ list_nth(context->relaction->mas_action->matched ?
+ partRelInfo->ri_matchedMergeAction :
+ partRelInfo->ri_notMatchedMergeAction,
+ context->relaction_idx);
+
resultRelInfo = partRelInfo;
}
@@ -1193,7 +1207,12 @@ ExecInsert(ModifyTableContext *context,
if (resultRelInfo->ri_WithCheckOptions != NIL)
ExecWithCheckOptions(WCO_VIEW_CHECK, resultRelInfo, slot, estate);
- /* Process RETURNING if present */
+ /*
+ * Process RETURNING if present. If we're running MERGE, be sure to use
+ * the projection for the current merge action's RETURNING list, if any.
+ */
+ if (mtstate->operation == CMD_MERGE)
+ resultRelInfo->ri_projectReturning = context->relaction->mas_proj_returning;
if (resultRelInfo->ri_projectReturning)
result = ExecProcessReturning(resultRelInfo, slot, planSlot);
@@ -2712,6 +2731,7 @@ static TupleTableSlot *
ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, bool canSetTag)
{
+ TupleTableSlot *rslot = NULL;
bool matched;
/*-----
@@ -2759,18 +2779,18 @@ 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()
- * returned "false", indicating the previously MATCHED tuple no longer
- * matches.
+ * Deal with the NOT MATCHED case (either a NOT MATCHED tuple from the
+ * join, or a previously MATCHED tuple for which ExecMergeMatched() set
+ * "matched" to false, indicating that it no longer matches).
*/
if (!matched)
- ExecMergeNotMatched(context, resultRelInfo, canSetTag);
+ rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag);
- /* No RETURNING support yet */
- return NULL;
+ return rslot;
}
/*
@@ -2780,8 +2800,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.
*
@@ -2790,16 +2810,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;
- TupleTableSlot *newslot;
+ TupleTableSlot *rslot = NULL;
+ TupleTableSlot *newslot = NULL;
EState *estate = context->estate;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
bool isNull;
@@ -2810,7 +2831,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
@@ -2890,15 +2914,38 @@ lmerge_matched:
newslot = ExecProject(relaction->mas_proj);
context->relaction = relaction;
+ context->relaction_idx = foreach_current_index(l);
if (!ExecUpdatePrologue(context, resultRelInfo,
tupleid, NULL, newslot, &result))
{
if (result == TM_Ok)
- return true; /* "do nothing" */
+ {
+ *matched = true;
+ return NULL; /* "do nothing" */
+ }
break; /* concurrent update/delete */
}
result = ExecUpdateAct(context, resultRelInfo, tupleid, NULL,
newslot, false, &updateCxt);
+
+ /*
+ * If ExecUpdateAct reports that a cross-partition update was
+ * done, then the RETURNING tuple (if any) has been projected
+ * and there's nothing else for us to do.
+ *
+ * XXX: Pre-existing bug here? For a cross-partition update,
+ * this should not be calling ExecUpdateEpilogue(). Row update
+ * triggers should not be fired, etc.
+ */
+ if (updateCxt.crossPartUpdate)
+ {
+ *matched = true;
+ mtstate->mt_merge_updated += 1;
+ if (canSetTag)
+ (estate->es_processed)++;
+ return context->cpUpdateReturningSlot;
+ }
+
if (result == TM_Ok && updateCxt.updated)
{
ExecUpdateEpilogue(context, &updateCxt, resultRelInfo,
@@ -2909,11 +2956,15 @@ lmerge_matched:
case CMD_DELETE:
context->relaction = relaction;
+ context->relaction_idx = foreach_current_index(l);
if (!ExecDeletePrologue(context, resultRelInfo, tupleid,
NULL, NULL, &result))
{
if (result == TM_Ok)
- return true; /* "do nothing" */
+ {
+ *matched = true;
+ return NULL; /* "do nothing" */
+ }
break; /* concurrent update/delete */
}
result = ExecDeleteAct(context, resultRelInfo, tupleid, false);
@@ -2969,7 +3020,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:
{
@@ -3015,13 +3067,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
@@ -3056,7 +3114,8 @@ lmerge_matched:
* tuple already deleted; tell caller to run NOT
* MATCHED actions
*/
- return false;
+ *matched = false;
+ return NULL;
case TM_SelfModified:
@@ -3076,13 +3135,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;
}
}
@@ -3095,6 +3155,36 @@ lmerge_matched:
}
/*
+ * Process RETURNING if present, using the action's RETURNING list.
+ * Using the root relation's action is correct here, since we know
+ * that this wasn't a cross-partition update at this point.
+ */
+ if (relaction->mas_proj_returning)
+ {
+ resultRelInfo->ri_projectReturning = relaction->mas_proj_returning;
+
+ 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");
+ }
+ }
+
+ /*
* We've activated one of the WHEN clauses, so we don't search
* further. This is required behaviour, not an optimization.
*/
@@ -3104,19 +3194,22 @@ 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;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
List *actionStates = NIL;
+ TupleTableSlot *rslot = NULL;
ListCell *l;
/*
@@ -3167,9 +3260,10 @@ ExecMergeNotMatched(ModifyTableContext *
*/
newslot = ExecProject(action->mas_proj);
context->relaction = action;
+ context->relaction_idx = foreach_current_index(l);
- (void) ExecInsert(context, mtstate->rootResultRelInfo, newslot,
- canSetTag, NULL, NULL);
+ rslot = ExecInsert(context, mtstate->rootResultRelInfo, newslot,
+ canSetTag, NULL, NULL);
mtstate->mt_merge_inserted += 1;
break;
case CMD_NOTHING:
@@ -3185,6 +3279,8 @@ ExecMergeNotMatched(ModifyTableContext *
*/
break;
}
+
+ return rslot;
}
/*
@@ -3334,6 +3430,15 @@ ExecInitMerge(ModifyTableState *mtstate,
elog(ERROR, "unknown operation");
break;
}
+
+ /* Build a projection for the action's RETURNING list, if any */
+ if (action->returningList)
+ action_state->mas_proj_returning =
+ ExecBuildProjectionInfo(action->returningList,
+ econtext,
+ mtstate->ps.ps_ResultTupleSlot,
+ &mtstate->ps,
+ relationDesc);
}
}
}
@@ -3647,8 +3752,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");
@@ -3725,8 +3839,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");
@@ -4192,18 +4315,23 @@ ExecInitModifyTable(ModifyTable *node, E
econtext = mtstate->ps.ps_ExprContext;
/*
- * Build a projection for each result rel.
+ * Build a projection for each result rel. We skip this for MERGE,
+ * since it uses per-action RETURNING lists, which each have their own
+ * projections, built in ExecInitMerge().
*/
- resultRelInfo = mtstate->resultRelInfo;
- foreach(l, node->returningLists)
+ if (operation != CMD_MERGE)
{
- List *rlist = (List *) lfirst(l);
+ resultRelInfo = mtstate->resultRelInfo;
+ foreach(l, node->returningLists)
+ {
+ List *rlist = (List *) lfirst(l);
- resultRelInfo->ri_returningList = rlist;
- resultRelInfo->ri_projectReturning =
- ExecBuildProjectionInfo(rlist, econtext, slot, &mtstate->ps,
- resultRelInfo->ri_RelationDesc->rd_att);
- resultRelInfo++;
+ resultRelInfo->ri_returningList = rlist;
+ resultRelInfo->ri_projectReturning =
+ ExecBuildProjectionInfo(rlist, econtext, slot, &mtstate->ps,
+ resultRelInfo->ri_RelationDesc->rd_att);
+ resultRelInfo++;
+ }
}
}
else
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
new file mode 100644
index 3397568..b9f443c
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -2033,6 +2033,8 @@ SPI_result_code_string(int code)
return "SPI_OK_TD_REGISTER";
case SPI_OK_MERGE:
return "SPI_OK_MERGE";
+ case SPI_OK_MERGE_RETURNING:
+ return "SPI_OK_MERGE_RETURNING";
}
/* Unrecognized code ... return something useful ... */
sprintf(buf, "Unrecognized SPI code %d", code);
@@ -2886,7 +2888,10 @@ _SPI_pquery(QueryDesc *queryDesc, bool f
res = SPI_OK_UPDATE;
break;
case CMD_MERGE:
- res = SPI_OK_MERGE;
+ if (queryDesc->plannedstmt->hasReturning)
+ res = SPI_OK_MERGE_RETURNING;
+ else
+ res = SPI_OK_MERGE;
break;
default:
return SPI_ERROR_OPUNKNOWN;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
new file mode 100644
index c03f4f2..5ca63ae
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2440,6 +2440,8 @@ expression_tree_walker_impl(Node *node,
return true;
if (WALK(action->targetList))
return true;
+ if (WALK(action->returningList))
+ return true;
}
break;
case T_PartitionPruneStepOp:
@@ -3421,6 +3423,7 @@ expression_tree_mutator_impl(Node *node,
FLATCOPY(newnode, action, MergeAction);
MUTATE(newnode->qual, action->qual, Node *);
MUTATE(newnode->targetList, action->targetList, List *);
+ MUTATE(newnode->returningList, action->returningList, List *);
return (Node *) newnode;
}
@@ -4061,6 +4064,8 @@ raw_expression_tree_walker_impl(Node *no
return true;
if (WALK(mergeWhenClause->values))
return true;
+ if (WALK(mergeWhenClause->returningList))
+ return true;
}
break;
case T_SelectStmt:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
new file mode 100644
index a8cea5e..07d4f20
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -908,6 +908,11 @@ subquery_planner(PlannerGlobal *glob, Qu
preprocess_expression(root,
(Node *) action->qual,
EXPRKIND_QUAL);
+
+ action->returningList = (List *)
+ preprocess_expression(root,
+ (Node *) action->returningList,
+ EXPRKIND_TARGET);
}
root->append_rel_list = (List *)
@@ -1884,6 +1889,11 @@ grouping_planner(PlannerInfo *root, doub
(Node *) action->targetList,
this_result_rel,
top_result_rel);
+ leaf_action->returningList = (List *)
+ adjust_appendrel_attrs_multilevel(root,
+ (Node *) action->returningList,
+ this_result_rel,
+ top_result_rel);
if (leaf_action->commandType == CMD_UPDATE)
leaf_action->updateColnos =
adjust_inherited_attnums_multilevel(root,
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
new file mode 100644
index fc37095..000e204
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -1192,6 +1192,15 @@ set_plan_refs(PlannerInfo *root, Plan *p
rtoffset,
NRM_EQUAL,
NUM_EXEC_QUAL(plan));
+
+ /* Fix returningList too. */
+ action->returningList =
+ set_returning_clause_references(root,
+ action->returningList,
+ subplan,
+ resultrel,
+ rtoffset);
+
}
}
}
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
new file mode 100644
index 73ff407..ca2b176
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2153,6 +2153,8 @@ perform_pullup_replace_vars(PlannerInfo
action->qual = pullup_replace_vars(action->qual, rvcontext);
action->targetList = (List *)
pullup_replace_vars((Node *) action->targetList, rvcontext);
+ action->returningList = (List *)
+ pullup_replace_vars((Node *) action->returningList, rvcontext);
}
}
replace_vars_in_jointree((Node *) parse->jointree, rvcontext);
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
new file mode 100644
index 9d46488..d209371
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -144,6 +144,7 @@ preprocess_targetlist(PlannerInfo *root)
foreach(l, parse->mergeActionList)
{
MergeAction *action = (MergeAction *) lfirst(l);
+ List *exprs;
List *vars;
ListCell *l2;
@@ -156,14 +157,14 @@ preprocess_targetlist(PlannerInfo *root)
/*
* Add resjunk entries for any Vars and PlaceHolderVars used in
- * each action's targetlist and WHEN condition that belong to
- * relations other than the target. We don't expect to see any
- * aggregates or window functions here.
+ * each action's targetlist, WHEN condition, and returningList
+ * that belong to relations other than the target. We don't
+ * expect to see any aggregates or window functions here.
*/
- vars = pull_var_clause((Node *)
- list_concat_copy((List *) action->qual,
- action->targetList),
- PVC_INCLUDE_PLACEHOLDERS);
+ exprs = list_concat_copy((List *) action->qual, action->targetList);
+ exprs = list_concat(exprs, action->returningList);
+ vars = pull_var_clause((Node *) exprs, PVC_INCLUDE_PLACEHOLDERS);
+
foreach(l2, vars)
{
Var *var = (Var *) lfirst(l2);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
new file mode 100644
index 7a1dfb6..a7021d2
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -74,7 +74,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,
@@ -2539,9 +2538,9 @@ transformUpdateTargetList(ParseState *ps
/*
* transformReturningList -
- * handle a RETURNING clause in INSERT/UPDATE/DELETE
+ * handle a RETURNING clause in INSERT/UPDATE/DELETE/MERGE
*/
-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 c224df4..b4ae1c5
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -438,7 +438,7 @@ static Node *makeRecursiveViewSelect(cha
any_operator expr_list attrs
distinct_clause opt_distinct_clause
target_list opt_target_list insert_column_list set_target_list
- merge_values_clause
+ merge_values_clause merge_returning_clause merge_target_list
set_clause_list set_clause
def_list operator_def_list indirection opt_indirection
reloption_list TriggerFuncArgs opclass_item_list opclass_drop_list
@@ -550,7 +550,7 @@ static Node *makeRecursiveViewSelect(cha
%type <range> extended_relation_expr
%type <range> relation_expr_opt_alias
%type <node> tablesample_clause opt_repeatable_clause
-%type <target> target_el set_target insert_column_item
+%type <target> target_el set_target insert_column_item merge_target_el
%type <str> generic_option_name
%type <node> generic_option_arg
@@ -835,7 +835,8 @@ static Node *makeRecursiveViewSelect(cha
* the same as non-keywords, reducing the risk of unwanted precedence effects.
*
* We need to do this for PARTITION, RANGE, ROWS, and GROUPS to support
- * opt_existing_window_name (see comment there).
+ * opt_existing_window_name (see comment there), and for WHEN to support
+ * merge_target_el.
*
* The frame_bound productions UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
* are even messier: since UNBOUNDED is an unreserved keyword (per spec!),
@@ -852,7 +853,7 @@ static Node *makeRecursiveViewSelect(cha
* Using the same precedence as IDENT seems right for the reasons given above.
*/
%nonassoc UNBOUNDED /* ideally would have same precedence as IDENT */
-%nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP
+%nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP WHEN
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
@@ -12379,6 +12380,7 @@ merge_when_clause:
m->matched = true;
m->commandType = CMD_NOTHING;
m->condition = $3;
+ m->location = @1;
$$ = (Node *) m;
}
@@ -12389,6 +12391,7 @@ merge_when_clause:
m->matched = false;
m->commandType = CMD_NOTHING;
m->condition = $4;
+ m->location = @1;
$$ = (Node *) m;
}
@@ -12400,75 +12403,89 @@ opt_merge_when_condition:
;
merge_update:
- UPDATE SET set_clause_list
+ UPDATE SET set_clause_list merge_returning_clause
{
MergeWhenClause *n = makeNode(MergeWhenClause);
n->commandType = CMD_UPDATE;
n->override = OVERRIDING_NOT_SET;
n->targetList = $3;
n->values = NIL;
+ n->returningList = $4;
+ n->location = @1;
$$ = n;
}
;
merge_delete:
- DELETE_P
+ DELETE_P merge_returning_clause
{
MergeWhenClause *n = makeNode(MergeWhenClause);
n->commandType = CMD_DELETE;
n->override = OVERRIDING_NOT_SET;
n->targetList = NIL;
n->values = NIL;
+ n->returningList = $2;
+ n->location = @1;
$$ = n;
}
;
merge_insert:
- INSERT merge_values_clause
+ INSERT merge_values_clause merge_returning_clause
{
MergeWhenClause *n = makeNode(MergeWhenClause);
n->commandType = CMD_INSERT;
n->override = OVERRIDING_NOT_SET;
n->targetList = NIL;
n->values = $2;
+ n->returningList = $3;
+ n->location = @1;
$$ = n;
}
- | INSERT OVERRIDING override_kind VALUE_P merge_values_clause
+ | INSERT OVERRIDING override_kind VALUE_P merge_values_clause merge_returning_clause
{
MergeWhenClause *n = makeNode(MergeWhenClause);
n->commandType = CMD_INSERT;
n->override = $3;
n->targetList = NIL;
n->values = $5;
+ n->returningList = $6;
+ n->location = @1;
$$ = n;
}
- | INSERT '(' insert_column_list ')' merge_values_clause
+ | INSERT '(' insert_column_list ')' merge_values_clause merge_returning_clause
{
MergeWhenClause *n = makeNode(MergeWhenClause);
n->commandType = CMD_INSERT;
n->override = OVERRIDING_NOT_SET;
n->targetList = $3;
n->values = $5;
+ n->returningList = $6;
+ n->location = @1;
$$ = n;
}
- | INSERT '(' insert_column_list ')' OVERRIDING override_kind VALUE_P merge_values_clause
+ | INSERT '(' insert_column_list ')' OVERRIDING override_kind VALUE_P merge_values_clause merge_returning_clause
{
MergeWhenClause *n = makeNode(MergeWhenClause);
n->commandType = CMD_INSERT;
n->override = $6;
n->targetList = $3;
n->values = $8;
+ n->returningList = $9;
+ n->location = @1;
$$ = n;
}
- | INSERT DEFAULT VALUES
+ | INSERT DEFAULT VALUES merge_returning_clause
{
MergeWhenClause *n = makeNode(MergeWhenClause);
n->commandType = CMD_INSERT;
n->override = OVERRIDING_NOT_SET;
n->targetList = NIL;
n->values = NIL;
+ n->returningList = $4;
+ n->location = @1;
$$ = n;
}
;
@@ -12480,6 +12497,64 @@ merge_values_clause:
}
;
+merge_returning_clause:
+ RETURNING merge_target_list { $$ = $2; }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
+
+merge_target_list:
+ merge_target_el { $$ = list_make1($1); }
+ | merge_target_list ',' merge_target_el { $$ = lappend($1, $3); }
+ ;
+
+/*
+ * Given "RETURNING expr when ...", we have to decide without looking any
+ * further ahead whether the "when" is an alias or the start of the next WHEN
+ * clause. We resolve the shift/reduce conflict by giving the first
+ * merge_target_el production a slightly higher precedence than the WHEN token
+ * has, causing the parser to prefer to reduce, in effect assuming that the
+ * WHEN is not an alias.
+ */
+merge_target_el:
+ a_expr %prec Op
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = NULL;
+ $$->indirection = NIL;
+ $$->val = (Node *) $1;
+ $$->location = @1;
+ }
+ | a_expr BareColLabel
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $2;
+ $$->indirection = NIL;
+ $$->val = (Node *) $1;
+ $$->location = @1;
+ }
+ | a_expr AS ColLabel
+ {
+ $$ = makeNode(ResTarget);
+ $$->name = $3;
+ $$->indirection = NIL;
+ $$->val = (Node *) $1;
+ $$->location = @1;
+ }
+ | '*'
+ {
+ ColumnRef *n = makeNode(ColumnRef);
+
+ n->fields = list_make1(makeNode(A_Star));
+ n->location = @1;
+
+ $$ = makeNode(ResTarget);
+ $$->name = NULL;
+ $$->indirection = NIL;
+ $$->val = (Node *) n;
+ $$->location = @1;
+ }
+ ;
+
/*****************************************************************************
*
* QUERY:
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
new file mode 100644
index 6992a78..5cad6d8
--- 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 91b1156..367857a
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -18,11 +18,13 @@
#include "access/sysattr.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
#include "parser/analyze.h"
#include "parser/parse_collate.h"
#include "parser/parsetree.h"
#include "parser/parser.h"
#include "parser/parse_clause.h"
+#include "parser/parse_coerce.h"
#include "parser/parse_cte.h"
#include "parser/parse_expr.h"
#include "parser/parse_merge.h"
@@ -103,6 +105,8 @@ transformMergeStmt(ParseState *pstate, M
List *mergeActionList;
Node *joinExpr;
ParseNamespaceItem *nsitem;
+ int returning_list_length = -1;
+ List **returning_colexprs = NULL;
/* There can't be any outer WITH to worry about */
Assert(pstate->p_ctenamespace == NIL);
@@ -385,14 +389,146 @@ transformMergeStmt(ParseState *pstate, M
elog(ERROR, "unknown action in MERGE WHEN clause");
}
+ /*
+ * Transform the RETURNING list, if any. All action RETURNING lists
+ * must be the same length, *after* transformation (which might expand
+ * '*' into multiple items). At this stage, we prevent resolving any
+ * unknown-type outputs as TEXT, allowing us to resolve such outputs
+ * using select_common_type() below.
+ */
+ if (mergeWhenClause->returningList)
+ {
+ bool save_resolve_unknowns = pstate->p_resolve_unknowns;
+ int i;
+ ListCell *lc2;
+
+ /* The RETURNING list can see both target and source relations */
+ setNamespaceVisibilityForRTE(pstate->p_namespace,
+ rt_fetch(qry->resultRelation,
+ pstate->p_rtable),
+ true, true);
+
+ setNamespaceVisibilityForRTE(pstate->p_namespace,
+ rt_fetch(sourceRTI,
+ pstate->p_rtable),
+ true, true);
+
+ pstate->p_resolve_unknowns = false;
+
+ action->returningList = transformReturningList(pstate,
+ mergeWhenClause->returningList);
+
+ pstate->p_resolve_unknowns = save_resolve_unknowns;
+
+ if (returning_list_length < 0)
+ {
+ /* Remember post-transformation length of first list */
+ returning_list_length = list_length(action->returningList);
+ /* and allocate array for per-column lists */
+ returning_colexprs = (List **) palloc0(returning_list_length * sizeof(List *));
+ }
+ else if (list_length(action->returningList) != returning_list_length)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("RETURNING lists must all be the same length"),
+ parser_errposition(pstate,
+ exprLocation((Node *) action->returningList)));
+
+ /* Build per-column RETURNING expression lists */
+ i = 0;
+ foreach(lc2, action->returningList)
+ {
+ TargetEntry *tle = lfirst_node(TargetEntry, lc2);
+
+ returning_colexprs[i] = lappend(returning_colexprs[i], tle->expr);
+ i++;
+ }
+ }
+ else
+ action->returningList = NIL;
+
mergeActionList = lappend(mergeActionList, action);
}
- qry->mergeActionList = mergeActionList;
+ /*
+ * Enforce the rule that if any action has a RETURNING list, all must
+ * have (except for DO NOTHING actions). We do this using a second pass
+ * over the merge actions to generate a more useful error message.
+ */
+ if (returning_list_length > 0)
+ {
+ ListCell *l2;
- /* RETURNING could potentially be added in the future, but not in SQL std */
+ forboth(l, stmt->mergeWhenClauses, l2, mergeActionList)
+ {
+ MergeWhenClause *mergeWhenClause = lfirst_node(MergeWhenClause, l);
+ MergeAction *action = lfirst_node(MergeAction, l2);
+
+ if (action->commandType != CMD_NOTHING && !action->returningList)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("missing RETURNING list for MERGE action"),
+ errhint("To enable RETURNING for MERGE, add a RETURNING list to every INSERT/UPDATE/DELETE action."),
+ parser_errposition(pstate, mergeWhenClause->location));
+ }
+ }
+
+ /*
+ * Resolve the common types of the columns of any RETURNING lists, and
+ * coerce everything to those types.
+ *
+ * Then identify the common typmod and common collation, if any, of each
+ * column, and build a top-level RETURNING list for the Query node, for
+ * use in outer queries. This RETURNING list is never evaluated, and just
+ * contains dummy expressions, to hold the common type, typmod, and
+ * collation information returned by this query.
+ */
qry->returningList = NULL;
+ for (int i = 0; i < returning_list_length; i++)
+ {
+ TargetEntry *first_tle = NULL;
+ Oid coltype;
+ int32 coltypmod;
+ Oid colcoll;
+ Const *con;
+ TargetEntry *new_tle;
+
+ coltype = select_common_type(pstate, returning_colexprs[i],
+ "RETURNING", NULL);
+
+ foreach(l, mergeActionList)
+ {
+ MergeAction *action = lfirst_node(MergeAction, l);
+
+ if (action->returningList)
+ {
+ TargetEntry *tle;
+
+ tle = list_nth_node(TargetEntry, action->returningList, i);
+ tle->expr = (Expr *) coerce_to_common_type(pstate,
+ (Node *) tle->expr,
+ coltype,
+ "RETURNING");
+ if (first_tle == NULL)
+ first_tle = tle;
+ }
+ }
+
+ coltypmod = select_common_typmod(pstate, returning_colexprs[i], coltype);
+ colcoll = select_common_collation(pstate, returning_colexprs[i], true);
+
+ con = makeNullConst(coltype, coltypmod, colcoll);
+ new_tle = makeTargetEntry((Expr *) con,
+ first_tle->resno,
+ pstrdup(first_tle->resname),
+ first_tle->resjunk);
+
+ qry->returningList = lappend(qry->returningList, new_tle);
+ }
+
+ qry->mergeActionList = mergeActionList;
+
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 864ea9b..cc19041
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -2346,9 +2346,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 41a3623..3786a7c
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3615,9 +3615,9 @@ RewriteQuery(Query *parsetree, List *rew
ListCell *lc1;
/*
- * First, recursively process any insert/update/delete statements in WITH
- * clauses. (We have to do this first because the WITH clauses may get
- * copied into rule actions below.)
+ * First, recursively process any insert/update/delete/merge statements in
+ * WITH clauses. (We have to do this first because the WITH clauses may
+ * get copied into rule actions below.)
*/
foreach(lc1, parsetree->cteList)
{
@@ -3642,7 +3642,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/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
new file mode 100644
index b1620e4..966dcf0
--- a/src/backend/rewrite/rowsecurity.c
+++ b/src/backend/rewrite/rowsecurity.c
@@ -395,10 +395,10 @@ get_row_security_policies(Query *root, R
* on the final action we take.
*
* We already fetched the SELECT policies above, to check existing rows,
- * but we must also check that new rows created by UPDATE actions are
- * visible, if SELECT rights are required for this relation. We don't do
- * this for INSERT actions, since an INSERT command would only do this
- * check if it had a RETURNING list, and MERGE does not support RETURNING.
+ * but we must also check that new rows created by INSERT/UPDATE actions
+ * are visible, if SELECT rights are required. For INSERT actions, we only
+ * do this if RETURNING is specified, to be consistent with a plain INSERT
+ * command, which can only require SELECT rights when RETURNING is used.
*
* We don't push the UPDATE/DELETE USING quals to the RTE because we don't
* really want to apply them while scanning the relation since we don't
@@ -420,6 +420,8 @@ get_row_security_policies(Query *root, R
List *merge_delete_restrictive_policies;
List *merge_insert_permissive_policies;
List *merge_insert_restrictive_policies;
+ List *merge_select_permissive_policies = NIL;
+ List *merge_select_restrictive_policies = NIL;
/*
* Fetch the UPDATE policies and set them up to execute on the
@@ -457,9 +459,6 @@ get_row_security_policies(Query *root, R
*/
if (perminfo->requiredPerms & ACL_SELECT)
{
- List *merge_select_permissive_policies;
- List *merge_select_restrictive_policies;
-
get_policies_for_relation(rel, CMD_SELECT, user_id,
&merge_select_permissive_policies,
&merge_select_restrictive_policies);
@@ -508,6 +507,21 @@ get_row_security_policies(Query *root, R
withCheckOptions,
hasSubLinks,
false);
+
+ /*
+ * Add ALL/SELECT policies as WCO_RLS_INSERT_CHECK WCOs, to ensure
+ * that the inserted row is visible when executing an INSERT action,
+ * if RETURNING is specified and SELECT rights are required for this
+ * relation.
+ */
+ if (perminfo->requiredPerms & ACL_SELECT && root->returningList)
+ add_with_check_options(rel, rt_index,
+ WCO_RLS_INSERT_CHECK,
+ merge_select_permissive_policies,
+ merge_select_restrictive_policies,
+ withCheckOptions,
+ hasSubLinks,
+ true);
}
table_close(rel, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
new file mode 100644
index e3ccf6c..6f3dc89
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -2142,11 +2142,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/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
new file mode 100644
index ed7f40f..b99118c
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7195,10 +7195,16 @@ get_merge_query_def(Query *query, depars
appendStringInfoString(buf, "DELETE");
else if (action->commandType == CMD_NOTHING)
appendStringInfoString(buf, "DO NOTHING");
- }
- /* No RETURNING support in MERGE yet */
- Assert(query->returningList == NIL);
+ /* Add RETURNING if present */
+ if (action->returningList)
+ {
+ appendContextKeyword(context, " RETURNING",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 4);
+ get_target_list(action->returningList, context, NULL,
+ colNamesVisible);
+ }
+ }
}
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
new file mode 100644
index daabf6f..6b63214
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -983,13 +983,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/executor/spi.h b/src/include/executor/spi.h
new file mode 100644
index d1de139..ed8787d
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -97,6 +97,7 @@ typedef struct _SPI_plan *SPIPlanPtr;
#define SPI_OK_REL_UNREGISTER 16
#define SPI_OK_TD_REGISTER 17
#define SPI_OK_MERGE 18
+#define SPI_OK_MERGE_RETURNING 19
#define SPI_OPT_NONATOMIC (1 << 0)
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
new file mode 100644
index 5d7f17d..6c27fee
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -419,6 +419,8 @@ typedef struct MergeActionState
ProjectionInfo *mas_proj; /* projection of the action's targetlist for
* this rel */
ExprState *mas_whenqual; /* WHEN [NOT] MATCHED AND conditions */
+ /* projection of the action's returningList for this rel */
+ ProjectionInfo *mas_proj_returning;
} MergeActionState;
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index cf7e790..13bc017
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1679,6 +1679,8 @@ typedef struct MergeWhenClause
List *targetList; /* INSERT/UPDATE targetlist */
/* the following members are only used in INSERT actions */
List *values; /* VALUES to INSERT, or NULL */
+ List *returningList; /* list of expressions to return */
+ int location; /* token location, or -1 if unknown */
} MergeWhenClause;
/*
@@ -1696,6 +1698,7 @@ typedef struct MergeAction
List *targetList; /* the target list (of TargetEntry) */
/* target attribute numbers of an UPDATE */
List *updateColnos pg_node_attr(query_jumble_ignore);
+ List *returningList; /* list of expressions to return */
} MergeAction;
/*
diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
new file mode 100644
index c96483a..14e6877
--- 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/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
new file mode 100644
index 4b76f76..fb6289c
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4267,9 +4267,9 @@ exec_stmt_execsql(PLpgSQL_execstate *est
/*
* If we have INTO, then we only need one row back ... but if we have INTO
* STRICT or extra check too_many_rows, ask for two rows, so that we can
- * verify the statement returns only one. INSERT/UPDATE/DELETE are always
- * treated strictly. Without INTO, just run the statement to completion
- * (tcount = 0).
+ * verify the statement returns only one. INSERT/UPDATE/DELETE/MERGE are
+ * always treated strictly. Without INTO, just run the statement to
+ * completion (tcount = 0).
*
* We could just ask for two rows always when using INTO, but there are
* some cases where demanding the extra row costs significant time, eg by
@@ -4307,10 +4307,11 @@ exec_stmt_execsql(PLpgSQL_execstate *est
case SPI_OK_INSERT:
case SPI_OK_UPDATE:
case SPI_OK_DELETE:
+ case SPI_OK_MERGE:
case SPI_OK_INSERT_RETURNING:
case SPI_OK_UPDATE_RETURNING:
case SPI_OK_DELETE_RETURNING:
- case SPI_OK_MERGE:
+ case SPI_OK_MERGE_RETURNING:
Assert(stmt->mod_stmt);
exec_set_found(estate, (SPI_processed != 0));
break;
@@ -4489,10 +4490,11 @@ exec_stmt_dynexecute(PLpgSQL_execstate *
case SPI_OK_INSERT:
case SPI_OK_UPDATE:
case SPI_OK_DELETE:
+ case SPI_OK_MERGE:
case SPI_OK_INSERT_RETURNING:
case SPI_OK_UPDATE_RETURNING:
case SPI_OK_DELETE_RETURNING:
- case SPI_OK_MERGE:
+ case SPI_OK_MERGE_RETURNING:
case SPI_OK_UTILITY:
case SPI_OK_REWRITTEN:
break;
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
new file mode 100644
index e8f9d7b..0c9f81e
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -2456,6 +2456,7 @@ pltcl_process_SPI_result(Tcl_Interp *int
case SPI_OK_INSERT_RETURNING:
case SPI_OK_DELETE_RETURNING:
case SPI_OK_UPDATE_RETURNING:
+ case SPI_OK_MERGE_RETURNING:
/*
* Process the tuples we got
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 28a6d0b..8dafe10
--- 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;
@@ -1256,7 +1256,7 @@ BEGIN;
MERGE INTO sq_target
USING v
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = balance + delta
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
@@ -1271,7 +1271,7 @@ INSERT INTO sq_source (sid, balance, del
MERGE INTO sq_target t
USING v
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
@@ -1295,7 +1295,7 @@ WITH targq AS (
MERGE INTO sq_target t
USING v
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
@@ -1304,20 +1304,183 @@ WHEN MATCHED AND tid < 2 THEN
ROLLBACK;
-- RETURNING
BEGIN;
-INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
MERGE INTO sq_target t
-USING v
+USING sq_source s
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
+ RETURNING 1 AS clause_number, 'upd' AS merge_action, t.*,
+ 'Added '||delta||' to balance' AS description
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 2, 'ins', t.*, 'Inserted '||t
WHEN MATCHED AND tid < 2 THEN
DELETE
-RETURNING *;
-ERROR: syntax error at or near "RETURNING"
-LINE 10: RETURNING *;
- ^
+ RETURNING 3, 'del', t.*, 'Removed '||t;
+ clause_number | merge_action | tid | balance | description
+---------------+--------------+-----+---------+---------------------
+ 3 | del | 1 | 100 | Removed (1,100)
+ 1 | upd | 2 | 220 | Added 20 to balance
+ 2 | ins | 4 | 40 | Inserted (4,40)
+(3 rows)
+
+ROLLBACK;
+-- RETURNING in CTEs
+CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text);
+INSERT INTO sq_target_merge_log VALUES (1, 'Original value');
+BEGIN;
+WITH m AS (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ RETURNING 1 AS clause_number, 'UPDATE' AS merge_action, t.*,
+ 'Added '||delta||' to balance' AS description
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 2, 'INSERT', t.*, 'Inserted '||t AS colname_ignored
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 3, 'DELETE', t.*, 'Removed '||t
+), m2 AS (
+ MERGE INTO sq_target_merge_log l
+ USING m
+ ON l.tid = m.tid
+ WHEN MATCHED THEN
+ UPDATE SET last_change = description
+ RETURNING merge_action, 'UPDATE' AS merge_log_action, l.*
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (m.tid, description)
+ RETURNING merge_action, 'INSERT', l.*
+)
+SELECT * FROM m2;
+ merge_action | merge_log_action | tid | last_change
+--------------+------------------+-----+---------------------
+ DELETE | UPDATE | 1 | Removed (1,100)
+ UPDATE | INSERT | 2 | Added 20 to balance
+ INSERT | INSERT | 4 | Inserted (4,40)
+(3 rows)
+
+SELECT * FROM sq_target_merge_log ORDER BY tid;
+ tid | last_change
+-----+---------------------
+ 1 | Removed (1,100)
+ 2 | Added 20 to balance
+ 4 | Inserted (4,40)
+(3 rows)
+
+ROLLBACK;
+-- COPY (MERGE ... RETURNING) TO ...
+BEGIN;
+COPY (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*
+) TO stdout;
+DELETE 1 100
+UPDATE 2 220
+INSERT 4 40
+ROLLBACK;
+-- SQL function with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT action text, OUT tid int, OUT new_balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m;
+ action | tid | new_balance
+--------+-----+-------------
+ DELETE | 1 | 100
+ UPDATE | 3 | 320
+ INSERT | 4 | 110
+(3 rows)
+
+ROLLBACK;
+-- SQL SRF with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_sq_source_into_sq_target()
+RETURNS TABLE (action text, tid int, balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*;
+$$;
+SELECT * FROM merge_sq_source_into_sq_target();
+ action | tid | balance
+--------+-----+---------
+ DELETE | 1 | 100
+ UPDATE | 2 | 220
+ INSERT | 4 | 40
+(3 rows)
+
+ROLLBACK;
+-- PL/pgSQL function with MERGE ... RETURNING ... INTO
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT r_action text, OUT r_tid int, OUT r_balance int)
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*
+ INTO r_action, r_tid, r_balance;
+END;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m;
+ r_action | r_tid | r_balance
+----------+-------+-----------
+ DELETE | 1 | 100
+ UPDATE | 3 | 320
+ INSERT | 4 | 110
+(3 rows)
+
ROLLBACK;
-- EXPLAIN
CREATE TABLE ex_mtarget (a int, b int)
@@ -1514,7 +1677,7 @@ SELECT * FROM sq_target WHERE tid = 1;
(1 row)
ROLLBACK;
-DROP TABLE sq_target, sq_source CASCADE;
+DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE;
NOTICE: drop cascades to view v
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
@@ -1640,6 +1803,32 @@ SELECT * FROM pa_target ORDER BY tid;
(14 rows)
ROLLBACK;
+-- update partition key to partition not initially scanned
+BEGIN;
+MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid AND t.tid = 1
+ WHEN MATCHED THEN
+ UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+ RETURNING t.*;
+ tid | balance | val
+-----+---------+--------------------------
+ 2 | 110 | initial updated by merge
+(1 row)
+
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance | val
+-----+---------+--------------------------
+ 2 | 110 | initial updated by merge
+ 3 | 300 | initial
+ 5 | 500 | initial
+ 7 | 700 | initial
+ 9 | 900 | initial
+ 11 | 1100 | initial
+ 13 | 1300 | initial
+(7 rows)
+
+ROLLBACK;
DROP TABLE pa_target CASCADE;
-- The target table is partitioned in the same way, but this time by attaching
-- partitions which have columns in different order, dropped columns etc.
@@ -1730,8 +1919,28 @@ MERGE INTO pa_target t
ON t.tid = s.sid
WHEN MATCHED THEN
UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+ RETURNING 'UPDATE' AS merge_action, t.*
WHEN NOT MATCHED THEN
- INSERT VALUES (sid, delta, 'inserted by merge');
+ INSERT VALUES (sid, delta, 'inserted by merge')
+ RETURNING 'INSERT', t.*;
+ merge_action | tid | balance | val
+--------------+-----+---------+--------------------------
+ UPDATE | 2 | 110 | initial updated by merge
+ INSERT | 2 | 20 | inserted by merge
+ UPDATE | 4 | 330 | initial updated by merge
+ INSERT | 4 | 40 | inserted by merge
+ UPDATE | 6 | 550 | initial updated by merge
+ INSERT | 6 | 60 | inserted by merge
+ UPDATE | 8 | 770 | initial updated by merge
+ INSERT | 8 | 80 | inserted by merge
+ UPDATE | 10 | 990 | initial updated by merge
+ INSERT | 10 | 100 | inserted by merge
+ UPDATE | 12 | 1210 | initial updated by merge
+ INSERT | 12 | 120 | inserted by merge
+ UPDATE | 14 | 1430 | initial updated by merge
+ INSERT | 14 | 140 | inserted by merge
+(14 rows)
+
SELECT * FROM pa_target ORDER BY tid;
tid | balance | val
-----+---------+--------------------------
@@ -1797,8 +2006,23 @@ MERGE INTO pa_target t
ON t.tid = s.sid
WHEN MATCHED THEN
UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+ RETURNING 'UPDATE' AS merge_action, t.*
WHEN NOT MATCHED THEN
- INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
+ INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge')
+ RETURNING 'INSERT' AS merge_action, t.*;
+ merge_action | logts | tid | balance | val
+--------------+--------------------------+-----+---------+--------------------------
+ UPDATE | Tue Jan 31 00:00:00 2017 | 1 | 110 | initial updated by merge
+ UPDATE | Tue Feb 28 00:00:00 2017 | 2 | 220 | initial updated by merge
+ INSERT | Sun Jan 15 00:00:00 2017 | 3 | 30 | inserted by merge
+ UPDATE | Tue Jan 31 00:00:00 2017 | 4 | 440 | initial updated by merge
+ UPDATE | Tue Feb 28 00:00:00 2017 | 5 | 550 | initial updated by merge
+ INSERT | Sun Jan 15 00:00:00 2017 | 6 | 60 | inserted by merge
+ UPDATE | Tue Jan 31 00:00:00 2017 | 7 | 770 | initial updated by merge
+ UPDATE | Tue Feb 28 00:00:00 2017 | 8 | 880 | initial updated by merge
+ INSERT | Sun Jan 15 00:00:00 2017 | 9 | 90 | inserted by merge
+(9 rows)
+
SELECT * FROM pa_target ORDER BY tid;
logts | tid | balance | val
--------------------------+-----+---------+--------------------------
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
new file mode 100644
index 6988128..d0c385d
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2302,6 +2302,37 @@ WHEN MATCHED THEN
UPDATE SET dnotes = dnotes || ' notes added by merge8 '
WHEN NOT MATCHED THEN
INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga');
+SELECT * FROM document WHERE did = 13;
+ did | cid | dlevel | dauthor | dtitle | dnotes
+-----+-----+--------+---------+--------+--------
+(0 rows)
+
+-- but not OK if RETURNING is used
+MERGE INTO document d
+USING (SELECT 14 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+ UPDATE SET dnotes = dnotes || ' notes added by merge9 '
+ RETURNING *
+WHEN NOT MATCHED THEN
+ INSERT VALUES (14, 44, 1, 'regress_rls_bob', 'new manga')
+ RETURNING *;
+ERROR: new row violates row-level security policy for table "document"
+-- but OK if new row is visible
+MERGE INTO document d
+USING (SELECT 14 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+ UPDATE SET dnotes = dnotes || ' notes added by merge10 '
+ RETURNING *
+WHEN NOT MATCHED THEN
+ INSERT VALUES (14, 11, 1, 'regress_rls_bob', 'new novel')
+ RETURNING *;
+ sdid | did | cid | dlevel | dauthor | dtitle | dnotes
+------+-----+-----+--------+-----------------+-----------+--------
+ 14 | 14 | 11 | 1 | regress_rls_bob | new novel |
+(1 row)
+
RESET SESSION AUTHORIZATION;
-- drop the restrictive SELECT policy so that we can look at the
-- final state of the table
@@ -2325,7 +2356,8 @@ SELECT * FROM document;
12 | 11 | 1 | regress_rls_bob | another novel |
1 | 11 | 1 | regress_rls_bob | my first novel | notes added by merge2 notes added by merge3 notes added by merge4 notes added by merge7
13 | 44 | 1 | regress_rls_bob | new manga |
-(15 rows)
+ 14 | 11 | 1 | regress_rls_bob | new novel |
+(16 rows)
--
-- ROLE/GROUP
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
new file mode 100644
index 2c60400..e8b1b0d
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3597,7 +3597,8 @@ MERGE INTO rule_merge2 t USING (SELECT 1
-- test deparsing
CREATE TABLE sf_target(id int, data text, filling int[]);
CREATE FUNCTION merge_sf_test()
- RETURNS void
+ RETURNS TABLE(merge_action text, when_clause int,
+ a int, b text, id int, data text, filling int[])
LANGUAGE sql
BEGIN ATOMIC
MERGE INTO sf_target t
@@ -3606,39 +3607,48 @@ BEGIN ATOMIC
WHEN MATCHED
AND (s.a + t.id) = 42
THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b)
+ RETURNING 'UPDATE', 1, *
WHEN NOT MATCHED
AND (s.b IS NOT NULL)
THEN INSERT (data, id)
VALUES (s.b, s.a)
+ RETURNING 'INSERT', 2, *
WHEN MATCHED
AND length(s.b || t.data) > 10
THEN UPDATE SET data = s.b
+ RETURNING 'UPDATE', 3, *
WHEN MATCHED
AND s.a > 200
THEN UPDATE SET filling[s.a] = t.id
+ RETURNING 'UPDATE', 4, *
WHEN MATCHED
AND s.a > 100
THEN DELETE
+ RETURNING 'DELETE', 5, *
WHEN MATCHED
THEN DO NOTHING
WHEN NOT MATCHED
AND s.a > 200
THEN INSERT DEFAULT VALUES
+ RETURNING 'INSERT', 6, *
WHEN NOT MATCHED
AND s.a > 100
THEN INSERT (id, data) OVERRIDING USER VALUE
VALUES (s.a, DEFAULT)
+ RETURNING 'INSERT', 7, *
WHEN NOT MATCHED
AND s.a > 0
THEN INSERT
VALUES (s.a, s.b, DEFAULT)
+ RETURNING 'INSERT', 8, *
WHEN NOT MATCHED
THEN INSERT (filling[1], id)
- VALUES (s.a, s.a);
+ VALUES (s.a, s.a)
+ RETURNING 'INSERT', 9, *;
END;
\sf merge_sf_test
CREATE OR REPLACE FUNCTION public.merge_sf_test()
- RETURNS void
+ RETURNS TABLE(merge_action text, when_clause integer, a integer, b text, id integer, data text, filling integer[])
LANGUAGE sql
BEGIN ATOMIC
MERGE INTO sf_target t
@@ -3647,35 +3657,98 @@ BEGIN ATOMIC
WHEN MATCHED
AND ((s.a + t.id) = 42)
THEN UPDATE SET data = (repeat(t.data, s.a) || s.b), id = length(s.b)
+ RETURNING 'UPDATE'::text,
+ 1,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN NOT MATCHED
AND (s.b IS NOT NULL)
THEN INSERT (data, id)
VALUES (s.b, s.a)
+ RETURNING 'INSERT'::text,
+ 2,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN MATCHED
AND (length((s.b || t.data)) > 10)
THEN UPDATE SET data = s.b
+ RETURNING 'UPDATE'::text,
+ 3,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN MATCHED
AND (s.a > 200)
THEN UPDATE SET filling[s.a] = t.id
+ RETURNING 'UPDATE'::text,
+ 4,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN MATCHED
AND (s.a > 100)
THEN DELETE
+ RETURNING 'DELETE'::text,
+ 5,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN MATCHED
THEN DO NOTHING
WHEN NOT MATCHED
AND (s.a > 200)
THEN INSERT DEFAULT VALUES
+ RETURNING 'INSERT'::text,
+ 6,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN NOT MATCHED
AND (s.a > 100)
THEN INSERT (id, data) OVERRIDING USER VALUE
VALUES (s.a, DEFAULT)
+ RETURNING 'INSERT'::text,
+ 7,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN NOT MATCHED
AND (s.a > 0)
THEN INSERT (id, data, filling)
VALUES (s.a, s.b, DEFAULT)
+ RETURNING 'INSERT'::text,
+ 8,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling
WHEN NOT MATCHED
THEN INSERT (filling[1], id)
- VALUES (s.a, s.a);
+ VALUES (s.a, s.a)
+ RETURNING 'INSERT'::text,
+ 9,
+ s.a,
+ s.b,
+ t.id,
+ t.data,
+ t.filling;
END
DROP FUNCTION merge_sf_test;
DROP TABLE sf_target;
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 4cf6db9..32a70e7
--- 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
@@ -816,7 +816,7 @@ BEGIN;
MERGE INTO sq_target
USING v
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = balance + delta
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
@@ -829,7 +829,7 @@ INSERT INTO sq_source (sid, balance, del
MERGE INTO sq_target t
USING v
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
@@ -847,7 +847,7 @@ WITH targq AS (
MERGE INTO sq_target t
USING v
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
@@ -857,17 +857,143 @@ ROLLBACK;
-- RETURNING
BEGIN;
-INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
MERGE INTO sq_target t
-USING v
+USING sq_source s
ON tid = sid
-WHEN MATCHED AND tid > 2 THEN
+WHEN MATCHED AND tid >= 2 THEN
UPDATE SET balance = t.balance + delta
+ RETURNING 1 AS clause_number, 'upd' AS merge_action, t.*,
+ 'Added '||delta||' to balance' AS description
WHEN NOT MATCHED THEN
INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 2, 'ins', t.*, 'Inserted '||t
WHEN MATCHED AND tid < 2 THEN
DELETE
-RETURNING *;
+ RETURNING 3, 'del', t.*, 'Removed '||t;
+ROLLBACK;
+
+-- RETURNING in CTEs
+CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text);
+INSERT INTO sq_target_merge_log VALUES (1, 'Original value');
+BEGIN;
+WITH m AS (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ RETURNING 1 AS clause_number, 'UPDATE' AS merge_action, t.*,
+ 'Added '||delta||' to balance' AS description
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 2, 'INSERT', t.*, 'Inserted '||t AS colname_ignored
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 3, 'DELETE', t.*, 'Removed '||t
+), m2 AS (
+ MERGE INTO sq_target_merge_log l
+ USING m
+ ON l.tid = m.tid
+ WHEN MATCHED THEN
+ UPDATE SET last_change = description
+ RETURNING merge_action, 'UPDATE' AS merge_log_action, l.*
+ WHEN NOT MATCHED THEN
+ INSERT VALUES (m.tid, description)
+ RETURNING merge_action, 'INSERT', l.*
+)
+SELECT * FROM m2;
+SELECT * FROM sq_target_merge_log ORDER BY tid;
+ROLLBACK;
+
+-- COPY (MERGE ... RETURNING) TO ...
+BEGIN;
+COPY (
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*
+) TO stdout;
+ROLLBACK;
+
+-- SQL function with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT action text, OUT tid int, OUT new_balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m;
+ROLLBACK;
+
+-- SQL SRF with MERGE ... RETURNING
+BEGIN;
+CREATE FUNCTION merge_sq_source_into_sq_target()
+RETURNS TABLE (action text, tid int, balance int)
+LANGUAGE sql AS
+$$
+ MERGE INTO sq_target t
+ USING sq_source s
+ ON tid = sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (balance + delta, sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*;
+$$;
+SELECT * FROM merge_sq_source_into_sq_target();
+ROLLBACK;
+
+-- PL/pgSQL function with MERGE ... RETURNING ... INTO
+BEGIN;
+CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int,
+ OUT r_action text, OUT r_tid int, OUT r_balance int)
+LANGUAGE plpgsql AS
+$$
+BEGIN
+ MERGE INTO sq_target t
+ USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta)
+ ON tid = v.sid
+ WHEN MATCHED AND tid >= 2 THEN
+ UPDATE SET balance = t.balance + v.delta
+ RETURNING 'UPDATE', t.*
+ WHEN NOT MATCHED THEN
+ INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid)
+ RETURNING 'INSERT', t.*
+ WHEN MATCHED AND tid < 2 THEN
+ DELETE
+ RETURNING 'DELETE', t.*
+ INTO r_action, r_tid, r_balance;
+END;
+$$;
+SELECT m.*
+FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta),
+LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m;
ROLLBACK;
-- EXPLAIN
@@ -966,7 +1092,7 @@ WHEN MATCHED THEN
SELECT * FROM sq_target WHERE tid = 1;
ROLLBACK;
-DROP TABLE sq_target, sq_source CASCADE;
+DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE;
CREATE TABLE pa_target (tid integer, balance float, val text)
PARTITION BY LIST (tid);
@@ -1033,6 +1159,17 @@ SELECT merge_func();
SELECT * FROM pa_target ORDER BY tid;
ROLLBACK;
+-- update partition key to partition not initially scanned
+BEGIN;
+MERGE INTO pa_target t
+ USING pa_source s
+ ON t.tid = s.sid AND t.tid = 1
+ WHEN MATCHED THEN
+ UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+ RETURNING t.*;
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
DROP TABLE pa_target CASCADE;
-- The target table is partitioned in the same way, but this time by attaching
@@ -1090,8 +1227,10 @@ MERGE INTO pa_target t
ON t.tid = s.sid
WHEN MATCHED THEN
UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+ RETURNING 'UPDATE' AS merge_action, t.*
WHEN NOT MATCHED THEN
- INSERT VALUES (sid, delta, 'inserted by merge');
+ INSERT VALUES (sid, delta, 'inserted by merge')
+ RETURNING 'INSERT', t.*;
SELECT * FROM pa_target ORDER BY tid;
ROLLBACK;
@@ -1144,8 +1283,10 @@ MERGE INTO pa_target t
ON t.tid = s.sid
WHEN MATCHED THEN
UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+ RETURNING 'UPDATE' AS merge_action, t.*
WHEN NOT MATCHED THEN
- INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
+ INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge')
+ RETURNING 'INSERT' AS merge_action, t.*;
SELECT * FROM pa_target ORDER BY tid;
ROLLBACK;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
new file mode 100644
index dec7340..8c093ee
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -992,6 +992,29 @@ WHEN MATCHED THEN
UPDATE SET dnotes = dnotes || ' notes added by merge8 '
WHEN NOT MATCHED THEN
INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga');
+SELECT * FROM document WHERE did = 13;
+
+-- but not OK if RETURNING is used
+MERGE INTO document d
+USING (SELECT 14 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+ UPDATE SET dnotes = dnotes || ' notes added by merge9 '
+ RETURNING *
+WHEN NOT MATCHED THEN
+ INSERT VALUES (14, 44, 1, 'regress_rls_bob', 'new manga')
+ RETURNING *;
+
+-- but OK if new row is visible
+MERGE INTO document d
+USING (SELECT 14 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+ UPDATE SET dnotes = dnotes || ' notes added by merge10 '
+ RETURNING *
+WHEN NOT MATCHED THEN
+ INSERT VALUES (14, 11, 1, 'regress_rls_bob', 'new novel')
+ RETURNING *;
RESET SESSION AUTHORIZATION;
-- drop the restrictive SELECT policy so that we can look at the
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
new file mode 100644
index 8b7e255..92d2ccd
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1281,7 +1281,8 @@ MERGE INTO rule_merge2 t USING (SELECT 1
CREATE TABLE sf_target(id int, data text, filling int[]);
CREATE FUNCTION merge_sf_test()
- RETURNS void
+ RETURNS TABLE(merge_action text, when_clause int,
+ a int, b text, id int, data text, filling int[])
LANGUAGE sql
BEGIN ATOMIC
MERGE INTO sf_target t
@@ -1290,35 +1291,44 @@ BEGIN ATOMIC
WHEN MATCHED
AND (s.a + t.id) = 42
THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b)
+ RETURNING 'UPDATE', 1, *
WHEN NOT MATCHED
AND (s.b IS NOT NULL)
THEN INSERT (data, id)
VALUES (s.b, s.a)
+ RETURNING 'INSERT', 2, *
WHEN MATCHED
AND length(s.b || t.data) > 10
THEN UPDATE SET data = s.b
+ RETURNING 'UPDATE', 3, *
WHEN MATCHED
AND s.a > 200
THEN UPDATE SET filling[s.a] = t.id
+ RETURNING 'UPDATE', 4, *
WHEN MATCHED
AND s.a > 100
THEN DELETE
+ RETURNING 'DELETE', 5, *
WHEN MATCHED
THEN DO NOTHING
WHEN NOT MATCHED
AND s.a > 200
THEN INSERT DEFAULT VALUES
+ RETURNING 'INSERT', 6, *
WHEN NOT MATCHED
AND s.a > 100
THEN INSERT (id, data) OVERRIDING USER VALUE
VALUES (s.a, DEFAULT)
+ RETURNING 'INSERT', 7, *
WHEN NOT MATCHED
AND s.a > 0
THEN INSERT
VALUES (s.a, s.b, DEFAULT)
+ RETURNING 'INSERT', 8, *
WHEN NOT MATCHED
THEN INSERT (filling[1], id)
- VALUES (s.a, s.a);
+ VALUES (s.a, s.a)
+ RETURNING 'INSERT', 9, *;
END;
\sf merge_sf_test
