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 ] target_table_name [ * ] [ [ AS ] target_alias ] USING data_source ON join_condition when_clause [...] +[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] + [ WITH WHEN CLAUSE [ [ AS ] col_alias ] ] ] where data_source is: 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 opt_conf_expr %type opt_on_conflict %type merge_insert merge_update merge_delete +%type merge_returning_clause %type merge_when_clause opt_merge_when_condition %type 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);