diff options
Diffstat (limited to 'src')
53 files changed, 2554 insertions, 372 deletions
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 7a800df8cab..8f28da4bf94 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -55,10 +55,15 @@ typedef struct ExprSetupInfo { - /* Highest attribute numbers fetched from inner/outer/scan tuple slots: */ + /* + * Highest attribute numbers fetched from inner/outer/scan/old/new tuple + * slots: + */ AttrNumber last_inner; AttrNumber last_outer; AttrNumber last_scan; + AttrNumber last_old; + AttrNumber last_new; /* MULTIEXPR SubPlan nodes appearing in the expression: */ List *multiexpr_subplans; } ExprSetupInfo; @@ -446,8 +451,25 @@ ExecBuildProjectionInfo(List *targetList, /* INDEX_VAR is handled by default case */ default: - /* get the tuple from the relation being scanned */ - scratch.opcode = EEOP_ASSIGN_SCAN_VAR; + + /* + * Get the tuple from the relation being scanned, or the + * old/new tuple slot, if old/new values were requested. + */ + switch (variable->varreturningtype) + { + case VAR_RETURNING_DEFAULT: + scratch.opcode = EEOP_ASSIGN_SCAN_VAR; + break; + case VAR_RETURNING_OLD: + scratch.opcode = EEOP_ASSIGN_OLD_VAR; + state->flags |= EEO_FLAG_HAS_OLD; + break; + case VAR_RETURNING_NEW: + scratch.opcode = EEOP_ASSIGN_NEW_VAR; + state->flags |= EEO_FLAG_HAS_NEW; + break; + } break; } @@ -535,7 +557,7 @@ ExecBuildUpdateProjection(List *targetList, int nAssignableCols; bool sawJunk; Bitmapset *assignedCols; - ExprSetupInfo deform = {0, 0, 0, NIL}; + ExprSetupInfo deform = {0, 0, 0, 0, 0, NIL}; ExprEvalStep scratch = {0}; int outerattnum; ListCell *lc, @@ -924,6 +946,7 @@ ExecInitExprRec(Expr *node, ExprState *state, /* system column */ scratch.d.var.attnum = variable->varattno; scratch.d.var.vartype = variable->vartype; + scratch.d.var.varreturningtype = variable->varreturningtype; switch (variable->varno) { case INNER_VAR: @@ -936,7 +959,20 @@ ExecInitExprRec(Expr *node, ExprState *state, /* INDEX_VAR is handled by default case */ default: - scratch.opcode = EEOP_SCAN_SYSVAR; + switch (variable->varreturningtype) + { + case VAR_RETURNING_DEFAULT: + scratch.opcode = EEOP_SCAN_SYSVAR; + break; + case VAR_RETURNING_OLD: + scratch.opcode = EEOP_OLD_SYSVAR; + state->flags |= EEO_FLAG_HAS_OLD; + break; + case VAR_RETURNING_NEW: + scratch.opcode = EEOP_NEW_SYSVAR; + state->flags |= EEO_FLAG_HAS_NEW; + break; + } break; } } @@ -945,6 +981,7 @@ ExecInitExprRec(Expr *node, ExprState *state, /* regular user column */ scratch.d.var.attnum = variable->varattno - 1; scratch.d.var.vartype = variable->vartype; + scratch.d.var.varreturningtype = variable->varreturningtype; switch (variable->varno) { case INNER_VAR: @@ -957,7 +994,20 @@ ExecInitExprRec(Expr *node, ExprState *state, /* INDEX_VAR is handled by default case */ default: - scratch.opcode = EEOP_SCAN_VAR; + switch (variable->varreturningtype) + { + case VAR_RETURNING_DEFAULT: + scratch.opcode = EEOP_SCAN_VAR; + break; + case VAR_RETURNING_OLD: + scratch.opcode = EEOP_OLD_VAR; + state->flags |= EEO_FLAG_HAS_OLD; + break; + case VAR_RETURNING_NEW: + scratch.opcode = EEOP_NEW_VAR; + state->flags |= EEO_FLAG_HAS_NEW; + break; + } break; } } @@ -2575,6 +2625,34 @@ ExecInitExprRec(Expr *node, ExprState *state, break; } + case T_ReturningExpr: + { + ReturningExpr *rexpr = (ReturningExpr *) node; + int retstep; + + /* Skip expression evaluation if OLD/NEW row doesn't exist */ + scratch.opcode = EEOP_RETURNINGEXPR; + scratch.d.returningexpr.nullflag = rexpr->retold ? + EEO_FLAG_OLD_IS_NULL : EEO_FLAG_NEW_IS_NULL; + scratch.d.returningexpr.jumpdone = -1; /* set below */ + ExprEvalPushStep(state, &scratch); + retstep = state->steps_len - 1; + + /* Steps to evaluate expression to return */ + ExecInitExprRec(rexpr->retexpr, state, resv, resnull); + + /* Jump target used if OLD/NEW row doesn't exist */ + state->steps[retstep].d.returningexpr.jumpdone = state->steps_len; + + /* Update ExprState flags */ + if (rexpr->retold) + state->flags |= EEO_FLAG_HAS_OLD; + else + state->flags |= EEO_FLAG_HAS_NEW; + + break; + } + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); @@ -2786,7 +2864,7 @@ ExecInitSubPlanExpr(SubPlan *subplan, static void ExecCreateExprSetupSteps(ExprState *state, Node *node) { - ExprSetupInfo info = {0, 0, 0, NIL}; + ExprSetupInfo info = {0, 0, 0, 0, 0, NIL}; /* Prescan to find out what we need. */ expr_setup_walker(node, &info); @@ -2809,8 +2887,8 @@ ExecPushExprSetupSteps(ExprState *state, ExprSetupInfo *info) scratch.resnull = NULL; /* - * Add steps deforming the ExprState's inner/outer/scan slots as much as - * required by any Vars appearing in the expression. + * Add steps deforming the ExprState's inner/outer/scan/old/new slots as + * much as required by any Vars appearing in the expression. */ if (info->last_inner > 0) { @@ -2842,6 +2920,26 @@ ExecPushExprSetupSteps(ExprState *state, ExprSetupInfo *info) if (ExecComputeSlotInfo(state, &scratch)) ExprEvalPushStep(state, &scratch); } + if (info->last_old > 0) + { + scratch.opcode = EEOP_OLD_FETCHSOME; + scratch.d.fetch.last_var = info->last_old; + scratch.d.fetch.fixed = false; + scratch.d.fetch.kind = NULL; + scratch.d.fetch.known_desc = NULL; + if (ExecComputeSlotInfo(state, &scratch)) + ExprEvalPushStep(state, &scratch); + } + if (info->last_new > 0) + { + scratch.opcode = EEOP_NEW_FETCHSOME; + scratch.d.fetch.last_var = info->last_new; + scratch.d.fetch.fixed = false; + scratch.d.fetch.kind = NULL; + scratch.d.fetch.known_desc = NULL; + if (ExecComputeSlotInfo(state, &scratch)) + ExprEvalPushStep(state, &scratch); + } /* * Add steps to execute any MULTIEXPR SubPlans appearing in the @@ -2888,7 +2986,18 @@ expr_setup_walker(Node *node, ExprSetupInfo *info) /* INDEX_VAR is handled by default case */ default: - info->last_scan = Max(info->last_scan, attnum); + switch (variable->varreturningtype) + { + case VAR_RETURNING_DEFAULT: + info->last_scan = Max(info->last_scan, attnum); + break; + case VAR_RETURNING_OLD: + info->last_old = Max(info->last_old, attnum); + break; + case VAR_RETURNING_NEW: + info->last_new = Max(info->last_new, attnum); + break; + } break; } return false; @@ -2926,6 +3035,11 @@ expr_setup_walker(Node *node, ExprSetupInfo *info) * evaluation of the expression will have the same type of slot, with an * equivalent descriptor. * + * EEOP_OLD_FETCHSOME and EEOP_NEW_FETCHSOME are used to process RETURNING, if + * OLD/NEW columns are referred to explicitly. In both cases, the tuple + * descriptor comes from the parent scan node, so we treat them the same as + * EEOP_SCAN_FETCHSOME. + * * Returns true if the deforming step is required, false otherwise. */ static bool @@ -2939,7 +3053,9 @@ ExecComputeSlotInfo(ExprState *state, ExprEvalStep *op) Assert(opcode == EEOP_INNER_FETCHSOME || opcode == EEOP_OUTER_FETCHSOME || - opcode == EEOP_SCAN_FETCHSOME); + opcode == EEOP_SCAN_FETCHSOME || + opcode == EEOP_OLD_FETCHSOME || + opcode == EEOP_NEW_FETCHSOME); if (op->d.fetch.known_desc != NULL) { @@ -2991,7 +3107,9 @@ ExecComputeSlotInfo(ExprState *state, ExprEvalStep *op) desc = ExecGetResultType(os); } } - else if (opcode == EEOP_SCAN_FETCHSOME) + else if (opcode == EEOP_SCAN_FETCHSOME || + opcode == EEOP_OLD_FETCHSOME || + opcode == EEOP_NEW_FETCHSOME) { desc = parent->scandesc; @@ -3039,6 +3157,12 @@ ExecInitWholeRowVar(ExprEvalStep *scratch, Var *variable, ExprState *state) scratch->d.wholerow.tupdesc = NULL; /* filled at runtime */ scratch->d.wholerow.junkFilter = NULL; + /* update ExprState flags if Var refers to OLD/NEW */ + if (variable->varreturningtype == VAR_RETURNING_OLD) + state->flags |= EEO_FLAG_HAS_OLD; + else if (variable->varreturningtype == VAR_RETURNING_NEW) + state->flags |= EEO_FLAG_HAS_NEW; + /* * If the input tuple came from a subquery, it might contain "resjunk" * columns (such as GROUP BY or ORDER BY columns), which we don't want to @@ -3541,7 +3665,7 @@ ExecBuildAggTrans(AggState *aggstate, AggStatePerPhase phase, PlanState *parent = &aggstate->ss.ps; ExprEvalStep scratch = {0}; bool isCombine = DO_AGGSPLIT_COMBINE(aggstate->aggsplit); - ExprSetupInfo deform = {0, 0, 0, NIL}; + ExprSetupInfo deform = {0, 0, 0, 0, 0, NIL}; state->expr = (Expr *) aggstate; state->parent = parent; @@ -4082,6 +4206,7 @@ ExecBuildHash32FromAttrs(TupleDesc desc, const TupleTableSlotOps *ops, scratch.resnull = &fcinfo->args[0].isnull; scratch.d.var.attnum = attnum; scratch.d.var.vartype = TupleDescAttr(desc, attnum)->atttypid; + scratch.d.var.varreturningtype = VAR_RETURNING_DEFAULT; ExprEvalPushStep(state, &scratch); @@ -4407,6 +4532,7 @@ ExecBuildGroupingEqual(TupleDesc ldesc, TupleDesc rdesc, scratch.opcode = EEOP_INNER_VAR; scratch.d.var.attnum = attno - 1; scratch.d.var.vartype = latt->atttypid; + scratch.d.var.varreturningtype = VAR_RETURNING_DEFAULT; scratch.resvalue = &fcinfo->args[0].value; scratch.resnull = &fcinfo->args[0].isnull; ExprEvalPushStep(state, &scratch); @@ -4415,6 +4541,7 @@ ExecBuildGroupingEqual(TupleDesc ldesc, TupleDesc rdesc, scratch.opcode = EEOP_OUTER_VAR; scratch.d.var.attnum = attno - 1; scratch.d.var.vartype = ratt->atttypid; + scratch.d.var.varreturningtype = VAR_RETURNING_DEFAULT; scratch.resvalue = &fcinfo->args[1].value; scratch.resnull = &fcinfo->args[1].isnull; ExprEvalPushStep(state, &scratch); @@ -4541,6 +4668,7 @@ ExecBuildParamSetEqual(TupleDesc desc, scratch.opcode = EEOP_INNER_VAR; scratch.d.var.attnum = attno; scratch.d.var.vartype = att->atttypid; + scratch.d.var.varreturningtype = VAR_RETURNING_DEFAULT; scratch.resvalue = &fcinfo->args[0].value; scratch.resnull = &fcinfo->args[0].isnull; ExprEvalPushStep(state, &scratch); @@ -4549,6 +4677,7 @@ ExecBuildParamSetEqual(TupleDesc desc, scratch.opcode = EEOP_OUTER_VAR; scratch.d.var.attnum = attno; scratch.d.var.vartype = att->atttypid; + scratch.d.var.varreturningtype = VAR_RETURNING_DEFAULT; scratch.resvalue = &fcinfo->args[1].value; scratch.resnull = &fcinfo->args[1].isnull; ExprEvalPushStep(state, &scratch); diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 7dfe17b0a86..1127e6f11eb 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -462,6 +462,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) TupleTableSlot *innerslot; TupleTableSlot *outerslot; TupleTableSlot *scanslot; + TupleTableSlot *oldslot; + TupleTableSlot *newslot; /* * This array has to be in the same order as enum ExprEvalOp. @@ -472,16 +474,24 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) &&CASE_EEOP_INNER_FETCHSOME, &&CASE_EEOP_OUTER_FETCHSOME, &&CASE_EEOP_SCAN_FETCHSOME, + &&CASE_EEOP_OLD_FETCHSOME, + &&CASE_EEOP_NEW_FETCHSOME, &&CASE_EEOP_INNER_VAR, &&CASE_EEOP_OUTER_VAR, &&CASE_EEOP_SCAN_VAR, + &&CASE_EEOP_OLD_VAR, + &&CASE_EEOP_NEW_VAR, &&CASE_EEOP_INNER_SYSVAR, &&CASE_EEOP_OUTER_SYSVAR, &&CASE_EEOP_SCAN_SYSVAR, + &&CASE_EEOP_OLD_SYSVAR, + &&CASE_EEOP_NEW_SYSVAR, &&CASE_EEOP_WHOLEROW, &&CASE_EEOP_ASSIGN_INNER_VAR, &&CASE_EEOP_ASSIGN_OUTER_VAR, &&CASE_EEOP_ASSIGN_SCAN_VAR, + &&CASE_EEOP_ASSIGN_OLD_VAR, + &&CASE_EEOP_ASSIGN_NEW_VAR, &&CASE_EEOP_ASSIGN_TMP, &&CASE_EEOP_ASSIGN_TMP_MAKE_RO, &&CASE_EEOP_CONST, @@ -523,6 +533,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) &&CASE_EEOP_SQLVALUEFUNCTION, &&CASE_EEOP_CURRENTOFEXPR, &&CASE_EEOP_NEXTVALUEEXPR, + &&CASE_EEOP_RETURNINGEXPR, &&CASE_EEOP_ARRAYEXPR, &&CASE_EEOP_ARRAYCOERCE, &&CASE_EEOP_ROW, @@ -591,6 +602,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) innerslot = econtext->ecxt_innertuple; outerslot = econtext->ecxt_outertuple; scanslot = econtext->ecxt_scantuple; + oldslot = econtext->ecxt_oldtuple; + newslot = econtext->ecxt_newtuple; #if defined(EEO_USE_COMPUTED_GOTO) EEO_DISPATCH(); @@ -630,6 +643,24 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_OLD_FETCHSOME) + { + CheckOpSlotCompatibility(op, oldslot); + + slot_getsomeattrs(oldslot, op->d.fetch.last_var); + + EEO_NEXT(); + } + + EEO_CASE(EEOP_NEW_FETCHSOME) + { + CheckOpSlotCompatibility(op, newslot); + + slot_getsomeattrs(newslot, op->d.fetch.last_var); + + EEO_NEXT(); + } + EEO_CASE(EEOP_INNER_VAR) { int attnum = op->d.var.attnum; @@ -673,6 +704,32 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_OLD_VAR) + { + int attnum = op->d.var.attnum; + + /* See EEOP_INNER_VAR comments */ + + Assert(attnum >= 0 && attnum < oldslot->tts_nvalid); + *op->resvalue = oldslot->tts_values[attnum]; + *op->resnull = oldslot->tts_isnull[attnum]; + + EEO_NEXT(); + } + + EEO_CASE(EEOP_NEW_VAR) + { + int attnum = op->d.var.attnum; + + /* See EEOP_INNER_VAR comments */ + + Assert(attnum >= 0 && attnum < newslot->tts_nvalid); + *op->resvalue = newslot->tts_values[attnum]; + *op->resnull = newslot->tts_isnull[attnum]; + + EEO_NEXT(); + } + EEO_CASE(EEOP_INNER_SYSVAR) { ExecEvalSysVar(state, op, econtext, innerslot); @@ -691,6 +748,18 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_OLD_SYSVAR) + { + ExecEvalSysVar(state, op, econtext, oldslot); + EEO_NEXT(); + } + + EEO_CASE(EEOP_NEW_SYSVAR) + { + ExecEvalSysVar(state, op, econtext, newslot); + EEO_NEXT(); + } + EEO_CASE(EEOP_WHOLEROW) { /* too complex for an inline implementation */ @@ -750,6 +819,40 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_ASSIGN_OLD_VAR) + { + int resultnum = op->d.assign_var.resultnum; + int attnum = op->d.assign_var.attnum; + + /* + * We do not need CheckVarSlotCompatibility here; that was taken + * care of at compilation time. But see EEOP_INNER_VAR comments. + */ + Assert(attnum >= 0 && attnum < oldslot->tts_nvalid); + Assert(resultnum >= 0 && resultnum < resultslot->tts_tupleDescriptor->natts); + resultslot->tts_values[resultnum] = oldslot->tts_values[attnum]; + resultslot->tts_isnull[resultnum] = oldslot->tts_isnull[attnum]; + + EEO_NEXT(); + } + + EEO_CASE(EEOP_ASSIGN_NEW_VAR) + { + int resultnum = op->d.assign_var.resultnum; + int attnum = op->d.assign_var.attnum; + + /* + * We do not need CheckVarSlotCompatibility here; that was taken + * care of at compilation time. But see EEOP_INNER_VAR comments. + */ + Assert(attnum >= 0 && attnum < newslot->tts_nvalid); + Assert(resultnum >= 0 && resultnum < resultslot->tts_tupleDescriptor->natts); + resultslot->tts_values[resultnum] = newslot->tts_values[attnum]; + resultslot->tts_isnull[resultnum] = newslot->tts_isnull[attnum]; + + EEO_NEXT(); + } + EEO_CASE(EEOP_ASSIGN_TMP) { int resultnum = op->d.assign_tmp.resultnum; @@ -1438,6 +1541,23 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_RETURNINGEXPR) + { + /* + * The next op actually evaluates the expression. If the OLD/NEW + * row doesn't exist, skip that and return NULL. + */ + if (state->flags & op->d.returningexpr.nullflag) + { + *op->resvalue = (Datum) 0; + *op->resnull = true; + + EEO_JUMP(op->d.returningexpr.jumpdone); + } + + EEO_NEXT(); + } + EEO_CASE(EEOP_ARRAYEXPR) { /* too complex for an inline implementation */ @@ -2119,10 +2239,14 @@ CheckExprStillValid(ExprState *state, ExprContext *econtext) TupleTableSlot *innerslot; TupleTableSlot *outerslot; TupleTableSlot *scanslot; + TupleTableSlot *oldslot; + TupleTableSlot *newslot; innerslot = econtext->ecxt_innertuple; outerslot = econtext->ecxt_outertuple; scanslot = econtext->ecxt_scantuple; + oldslot = econtext->ecxt_oldtuple; + newslot = econtext->ecxt_newtuple; for (int i = 0; i < state->steps_len; i++) { @@ -2153,6 +2277,22 @@ CheckExprStillValid(ExprState *state, ExprContext *econtext) CheckVarSlotCompatibility(scanslot, attnum + 1, op->d.var.vartype); break; } + + case EEOP_OLD_VAR: + { + int attnum = op->d.var.attnum; + + CheckVarSlotCompatibility(oldslot, attnum + 1, op->d.var.vartype); + break; + } + + case EEOP_NEW_VAR: + { + int attnum = op->d.var.attnum; + + CheckVarSlotCompatibility(newslot, attnum + 1, op->d.var.vartype); + break; + } default: break; } @@ -5113,7 +5253,7 @@ void ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext) { Var *variable = op->d.wholerow.var; - TupleTableSlot *slot; + TupleTableSlot *slot = NULL; TupleDesc output_tupdesc; MemoryContext oldcontext; HeapTupleHeader dtuple; @@ -5138,8 +5278,40 @@ ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext) /* INDEX_VAR is handled by default case */ default: - /* get the tuple from the relation being scanned */ - slot = econtext->ecxt_scantuple; + + /* + * Get the tuple from the relation being scanned. + * + * By default, this uses the "scan" tuple slot, but a wholerow Var + * in the RETURNING list may explicitly refer to OLD/NEW. If the + * OLD/NEW row doesn't exist, we just return NULL. + */ + switch (variable->varreturningtype) + { + case VAR_RETURNING_DEFAULT: + slot = econtext->ecxt_scantuple; + break; + + case VAR_RETURNING_OLD: + if (state->flags & EEO_FLAG_OLD_IS_NULL) + { + *op->resvalue = (Datum) 0; + *op->resnull = true; + return; + } + slot = econtext->ecxt_oldtuple; + break; + + case VAR_RETURNING_NEW: + if (state->flags & EEO_FLAG_NEW_IS_NULL) + { + *op->resvalue = (Datum) 0; + *op->resnull = true; + return; + } + slot = econtext->ecxt_newtuple; + break; + } break; } @@ -5342,6 +5514,17 @@ ExecEvalSysVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext, { Datum d; + /* OLD/NEW system attribute is NULL if OLD/NEW row is NULL */ + if ((op->d.var.varreturningtype == VAR_RETURNING_OLD && + state->flags & EEO_FLAG_OLD_IS_NULL) || + (op->d.var.varreturningtype == VAR_RETURNING_NEW && + state->flags & EEO_FLAG_NEW_IS_NULL)) + { + *op->resvalue = (Datum) 0; + *op->resnull = true; + return; + } + /* slot_getsysattr has sufficient defenses against bad attnums */ d = slot_getsysattr(slot, op->d.var.attnum, diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 2d28ec65fc4..fb8dba3ab2c 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1257,6 +1257,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_ReturningSlot = NULL; resultRelInfo->ri_TrigOldSlot = NULL; resultRelInfo->ri_TrigNewSlot = NULL; + resultRelInfo->ri_AllNullSlot = NULL; resultRelInfo->ri_MergeActions[MERGE_WHEN_MATCHED] = NIL; resultRelInfo->ri_MergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] = NIL; resultRelInfo->ri_MergeActions[MERGE_WHEN_NOT_MATCHED_BY_TARGET] = NIL; diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c index f71899463b8..7c539de5cf2 100644 --- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -1243,6 +1243,34 @@ ExecGetReturningSlot(EState *estate, ResultRelInfo *relInfo) } /* + * Return a relInfo's all-NULL tuple slot for processing returning tuples. + * + * Note: this slot is intentionally filled with NULLs in every column, and + * should be considered read-only --- the caller must not update it. + */ +TupleTableSlot * +ExecGetAllNullSlot(EState *estate, ResultRelInfo *relInfo) +{ + if (relInfo->ri_AllNullSlot == NULL) + { + Relation rel = relInfo->ri_RelationDesc; + MemoryContext oldcontext = MemoryContextSwitchTo(estate->es_query_cxt); + TupleTableSlot *slot; + + slot = ExecInitExtraTupleSlot(estate, + RelationGetDescr(rel), + table_slot_callbacks(rel)); + ExecStoreAllNullTuple(slot); + + relInfo->ri_AllNullSlot = slot; + + MemoryContextSwitchTo(oldcontext); + } + + return relInfo->ri_AllNullSlot; +} + +/* * Return the map needed to convert given child result relation's tuples to * the rowtype of the query's main target ("root") relation. Note that a * NULL result is valid and means that no conversion is needed. diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 1af8c9caf6c..bc82e035ba2 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -102,6 +102,13 @@ typedef struct ModifyTableContext TM_FailureData tmfd; /* + * The tuple deleted when doing a cross-partition UPDATE with a RETURNING + * clause that refers to OLD columns (converted to the root's tuple + * descriptor). + */ + TupleTableSlot *cpDeletedSlot; + + /* * The tuple projected by the INSERT's RETURNING clause, when doing a * cross-partition UPDATE */ @@ -243,34 +250,81 @@ ExecCheckPlanOutput(Relation resultRel, List *targetList) /* * ExecProcessReturning --- evaluate a RETURNING list * + * context: context for the ModifyTable operation * resultRelInfo: current result rel - * tupleSlot: slot holding tuple actually inserted/updated/deleted + * cmdType: operation/merge action performed (INSERT, UPDATE, or DELETE) + * oldSlot: slot holding old tuple deleted or updated + * newSlot: slot holding new tuple inserted or updated * planSlot: slot holding tuple returned by top subplan node * - * Note: If tupleSlot is NULL, the FDW should have already provided econtext's - * scan tuple. + * Note: If oldSlot and newSlot are NULL, the FDW should have already provided + * econtext's scan tuple and its old & new tuples are not needed (FDW direct- + * modify is disabled if the RETURNING list refers to any OLD/NEW values). * * Returns a slot holding the result tuple */ static TupleTableSlot * -ExecProcessReturning(ResultRelInfo *resultRelInfo, - TupleTableSlot *tupleSlot, +ExecProcessReturning(ModifyTableContext *context, + ResultRelInfo *resultRelInfo, + CmdType cmdType, + TupleTableSlot *oldSlot, + TupleTableSlot *newSlot, TupleTableSlot *planSlot) { + EState *estate = context->estate; ProjectionInfo *projectReturning = resultRelInfo->ri_projectReturning; ExprContext *econtext = projectReturning->pi_exprContext; /* Make tuple and any needed join variables available to ExecProject */ - if (tupleSlot) - econtext->ecxt_scantuple = tupleSlot; + switch (cmdType) + { + case CMD_INSERT: + case CMD_UPDATE: + /* return new tuple by default */ + if (newSlot) + econtext->ecxt_scantuple = newSlot; + break; + + case CMD_DELETE: + /* return old tuple by default */ + if (oldSlot) + econtext->ecxt_scantuple = oldSlot; + break; + + default: + elog(ERROR, "unrecognized commandType: %d", (int) cmdType); + } econtext->ecxt_outertuple = planSlot; + /* Make old/new tuples available to ExecProject, if required */ + if (oldSlot) + econtext->ecxt_oldtuple = oldSlot; + else if (projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD) + econtext->ecxt_oldtuple = ExecGetAllNullSlot(estate, resultRelInfo); + else + econtext->ecxt_oldtuple = NULL; /* No references to OLD columns */ + + if (newSlot) + econtext->ecxt_newtuple = newSlot; + else if (projectReturning->pi_state.flags & EEO_FLAG_HAS_NEW) + econtext->ecxt_newtuple = ExecGetAllNullSlot(estate, resultRelInfo); + else + econtext->ecxt_newtuple = NULL; /* No references to NEW columns */ + /* - * RETURNING expressions might reference the tableoid column, so - * reinitialize tts_tableOid before evaluating them. + * Tell ExecProject whether or not the OLD/NEW rows actually exist. This + * information is required to evaluate ReturningExpr nodes and also in + * ExecEvalSysVar() and ExecEvalWholeRowVar(). */ - econtext->ecxt_scantuple->tts_tableOid = - RelationGetRelid(resultRelInfo->ri_RelationDesc); + if (oldSlot == NULL) + projectReturning->pi_state.flags |= EEO_FLAG_OLD_IS_NULL; + else + projectReturning->pi_state.flags &= ~EEO_FLAG_OLD_IS_NULL; + + if (newSlot == NULL) + projectReturning->pi_state.flags |= EEO_FLAG_NEW_IS_NULL; + else + projectReturning->pi_state.flags &= ~EEO_FLAG_NEW_IS_NULL; /* Compute the RETURNING expressions */ return ExecProject(projectReturning); @@ -1204,7 +1258,56 @@ ExecInsert(ModifyTableContext *context, /* Process RETURNING if present */ if (resultRelInfo->ri_projectReturning) - result = ExecProcessReturning(resultRelInfo, slot, planSlot); + { + TupleTableSlot *oldSlot = NULL; + + /* + * If this is part of a cross-partition UPDATE, and the RETURNING list + * refers to any OLD columns, ExecDelete() will have saved the tuple + * deleted from the original partition, which we must use here to + * compute the OLD column values. Otherwise, all OLD column values + * will be NULL. + */ + if (context->cpDeletedSlot) + { + TupleConversionMap *tupconv_map; + + /* + * Convert the OLD tuple to the new partition's format/slot, if + * needed. Note that ExceDelete() already converted it to the + * root's partition's format/slot. + */ + oldSlot = context->cpDeletedSlot; + tupconv_map = ExecGetRootToChildMap(resultRelInfo, estate); + if (tupconv_map != NULL) + { + oldSlot = execute_attr_map_slot(tupconv_map->attrMap, + oldSlot, + ExecGetReturningSlot(estate, + resultRelInfo)); + + oldSlot->tts_tableOid = context->cpDeletedSlot->tts_tableOid; + ItemPointerCopy(&context->cpDeletedSlot->tts_tid, &oldSlot->tts_tid); + } + } + + result = ExecProcessReturning(context, resultRelInfo, CMD_INSERT, + oldSlot, slot, planSlot); + + /* + * For a cross-partition UPDATE, release the old tuple, first making + * sure that the result slot has a local copy of any pass-by-reference + * values. + */ + if (context->cpDeletedSlot) + { + ExecMaterializeSlot(result); + ExecClearTuple(oldSlot); + if (context->cpDeletedSlot != oldSlot) + ExecClearTuple(context->cpDeletedSlot); + context->cpDeletedSlot = NULL; + } + } if (inserted_tuple) *inserted_tuple = slot; @@ -1442,6 +1545,7 @@ ExecDelete(ModifyTableContext *context, Relation resultRelationDesc = resultRelInfo->ri_RelationDesc; TupleTableSlot *slot = NULL; TM_Result result; + bool saveOld; if (tupleDeleted) *tupleDeleted = false; @@ -1676,8 +1780,17 @@ ldelete: ExecDeleteEpilogue(context, resultRelInfo, tupleid, oldtuple, changingPart); - /* Process RETURNING if present and if requested */ - if (processReturning && resultRelInfo->ri_projectReturning) + /* + * Process RETURNING if present and if requested. + * + * If this is part of a cross-partition UPDATE, and the RETURNING list + * refers to any OLD column values, save the old tuple here for later + * processing of the RETURNING list by ExecInsert(). + */ + saveOld = changingPart && resultRelInfo->ri_projectReturning && + resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD; + + if (resultRelInfo->ri_projectReturning && (processReturning || saveOld)) { /* * We have to put the target tuple into a slot, which means first we @@ -1705,7 +1818,41 @@ ldelete: } } - rslot = ExecProcessReturning(resultRelInfo, slot, context->planSlot); + /* + * If required, save the old tuple for later processing of the + * RETURNING list by ExecInsert(). + */ + if (saveOld) + { + TupleConversionMap *tupconv_map; + + /* + * Convert the tuple into the root partition's format/slot, if + * needed. ExecInsert() will then convert it to the new + * partition's format/slot, if necessary. + */ + tupconv_map = ExecGetChildToRootMap(resultRelInfo); + if (tupconv_map != NULL) + { + ResultRelInfo *rootRelInfo = context->mtstate->rootResultRelInfo; + TupleTableSlot *oldSlot = slot; + + slot = execute_attr_map_slot(tupconv_map->attrMap, + slot, + ExecGetReturningSlot(estate, + rootRelInfo)); + + slot->tts_tableOid = oldSlot->tts_tableOid; + ItemPointerCopy(&oldSlot->tts_tid, &slot->tts_tid); + } + + context->cpDeletedSlot = slot; + + return NULL; + } + + rslot = ExecProcessReturning(context, resultRelInfo, CMD_DELETE, + slot, NULL, context->planSlot); /* * Before releasing the target tuple again, make sure rslot has a @@ -1758,6 +1905,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context, bool tuple_deleted; TupleTableSlot *epqslot = NULL; + context->cpDeletedSlot = NULL; context->cpUpdateReturningSlot = NULL; *retry_slot = NULL; @@ -2258,6 +2406,7 @@ ExecCrossPartitionUpdateForeignKey(ModifyTableContext *context, * the planSlot. oldtuple is passed to foreign table triggers; it is * NULL when the foreign table has no relevant triggers. * + * oldSlot contains the old tuple value. * slot contains the new tuple value to be stored. * planSlot is the output of the ModifyTable's subplan; we use it * to access values from other input tables (for RETURNING), @@ -2270,8 +2419,8 @@ ExecCrossPartitionUpdateForeignKey(ModifyTableContext *context, */ static TupleTableSlot * ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo, - ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *slot, - bool canSetTag) + ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *oldSlot, + TupleTableSlot *slot, bool canSetTag) { EState *estate = context->estate; Relation resultRelationDesc = resultRelInfo->ri_RelationDesc; @@ -2389,7 +2538,6 @@ redo_act: { TupleTableSlot *inputslot; TupleTableSlot *epqslot; - TupleTableSlot *oldSlot; if (IsolationUsesXactSnapshot()) ereport(ERROR, @@ -2504,7 +2652,8 @@ redo_act: /* Process RETURNING if present */ if (resultRelInfo->ri_projectReturning) - return ExecProcessReturning(resultRelInfo, slot, context->planSlot); + return ExecProcessReturning(context, resultRelInfo, CMD_UPDATE, + oldSlot, slot, context->planSlot); return NULL; } @@ -2724,16 +2873,23 @@ ExecOnConflictUpdate(ModifyTableContext *context, /* Execute UPDATE with projection */ *returning = ExecUpdate(context, resultRelInfo, - conflictTid, NULL, + conflictTid, NULL, existing, resultRelInfo->ri_onConflict->oc_ProjSlot, canSetTag); /* * Clear out existing tuple, as there might not be another conflict among * the next input rows. Don't want to hold resources till the end of the - * query. + * query. First though, make sure that the returning slot, if any, has a + * local copy of any OLD pass-by-reference values, if it refers to any OLD + * columns. */ + if (*returning != NULL && + resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD) + ExecMaterializeSlot(*returning); + ExecClearTuple(existing); + return true; } @@ -3338,13 +3494,20 @@ lmerge_matched: switch (commandType) { case CMD_UPDATE: - rslot = ExecProcessReturning(resultRelInfo, newslot, + rslot = ExecProcessReturning(context, + resultRelInfo, + CMD_UPDATE, + resultRelInfo->ri_oldTupleSlot, + newslot, context->planSlot); break; case CMD_DELETE: - rslot = ExecProcessReturning(resultRelInfo, + rslot = ExecProcessReturning(context, + resultRelInfo, + CMD_DELETE, resultRelInfo->ri_oldTupleSlot, + NULL, context->planSlot); break; @@ -3894,6 +4057,7 @@ ExecModifyTable(PlanState *pstate) if (node->mt_merge_pending_not_matched != NULL) { context.planSlot = node->mt_merge_pending_not_matched; + context.cpDeletedSlot = NULL; slot = ExecMergeNotMatched(&context, node->resultRelInfo, node->canSetTag); @@ -3913,6 +4077,7 @@ ExecModifyTable(PlanState *pstate) /* Fetch the next row from subplan */ context.planSlot = ExecProcNode(subplanstate); + context.cpDeletedSlot = NULL; /* No more tuples to process? */ if (TupIsNull(context.planSlot)) @@ -3980,9 +4145,15 @@ ExecModifyTable(PlanState *pstate) * A scan slot containing the data that was actually inserted, * updated or deleted has already been made available to * ExecProcessReturning by IterateDirectModify, so no need to - * provide it here. + * provide it here. The individual old and new slots are not + * needed, since direct-modify is disabled if the RETURNING list + * refers to OLD/NEW values. */ - slot = ExecProcessReturning(resultRelInfo, NULL, context.planSlot); + Assert((resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD) == 0 && + (resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_NEW) == 0); + + slot = ExecProcessReturning(&context, resultRelInfo, operation, + NULL, NULL, context.planSlot); return slot; } @@ -4172,7 +4343,7 @@ ExecModifyTable(PlanState *pstate) /* Now apply the update. */ slot = ExecUpdate(&context, resultRelInfo, tupleid, oldtuple, - slot, node->canSetTag); + oldSlot, slot, node->canSetTag); if (tuplock) UnlockTuple(resultRelInfo->ri_RelationDesc, tupleid, InplaceUpdateTupleLock); diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index b0119200dde..c1cf34f1034 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -105,6 +105,8 @@ llvm_compile_expr(ExprState *state) LLVMValueRef v_innerslot; LLVMValueRef v_outerslot; LLVMValueRef v_scanslot; + LLVMValueRef v_oldslot; + LLVMValueRef v_newslot; LLVMValueRef v_resultslot; /* nulls/values of slots */ @@ -114,6 +116,10 @@ llvm_compile_expr(ExprState *state) LLVMValueRef v_outernulls; LLVMValueRef v_scanvalues; LLVMValueRef v_scannulls; + LLVMValueRef v_oldvalues; + LLVMValueRef v_oldnulls; + LLVMValueRef v_newvalues; + LLVMValueRef v_newnulls; LLVMValueRef v_resultvalues; LLVMValueRef v_resultnulls; @@ -200,6 +206,16 @@ llvm_compile_expr(ExprState *state) v_econtext, FIELDNO_EXPRCONTEXT_OUTERTUPLE, "v_outerslot"); + v_oldslot = l_load_struct_gep(b, + StructExprContext, + v_econtext, + FIELDNO_EXPRCONTEXT_OLDTUPLE, + "v_oldslot"); + v_newslot = l_load_struct_gep(b, + StructExprContext, + v_econtext, + FIELDNO_EXPRCONTEXT_NEWTUPLE, + "v_newslot"); v_resultslot = l_load_struct_gep(b, StructExprState, v_state, @@ -237,6 +253,26 @@ llvm_compile_expr(ExprState *state) v_outerslot, FIELDNO_TUPLETABLESLOT_ISNULL, "v_outernulls"); + v_oldvalues = l_load_struct_gep(b, + StructTupleTableSlot, + v_oldslot, + FIELDNO_TUPLETABLESLOT_VALUES, + "v_oldvalues"); + v_oldnulls = l_load_struct_gep(b, + StructTupleTableSlot, + v_oldslot, + FIELDNO_TUPLETABLESLOT_ISNULL, + "v_oldnulls"); + v_newvalues = l_load_struct_gep(b, + StructTupleTableSlot, + v_newslot, + FIELDNO_TUPLETABLESLOT_VALUES, + "v_newvalues"); + v_newnulls = l_load_struct_gep(b, + StructTupleTableSlot, + v_newslot, + FIELDNO_TUPLETABLESLOT_ISNULL, + "v_newnulls"); v_resultvalues = l_load_struct_gep(b, StructTupleTableSlot, v_resultslot, @@ -302,6 +338,8 @@ llvm_compile_expr(ExprState *state) case EEOP_INNER_FETCHSOME: case EEOP_OUTER_FETCHSOME: case EEOP_SCAN_FETCHSOME: + case EEOP_OLD_FETCHSOME: + case EEOP_NEW_FETCHSOME: { TupleDesc desc = NULL; LLVMValueRef v_slot; @@ -326,8 +364,12 @@ llvm_compile_expr(ExprState *state) v_slot = v_innerslot; else if (opcode == EEOP_OUTER_FETCHSOME) v_slot = v_outerslot; - else + else if (opcode == EEOP_SCAN_FETCHSOME) v_slot = v_scanslot; + else if (opcode == EEOP_OLD_FETCHSOME) + v_slot = v_oldslot; + else + v_slot = v_newslot; /* * Check if all required attributes are available, or @@ -396,6 +438,8 @@ llvm_compile_expr(ExprState *state) case EEOP_INNER_VAR: case EEOP_OUTER_VAR: case EEOP_SCAN_VAR: + case EEOP_OLD_VAR: + case EEOP_NEW_VAR: { LLVMValueRef value, isnull; @@ -413,11 +457,21 @@ llvm_compile_expr(ExprState *state) v_values = v_outervalues; v_nulls = v_outernulls; } - else + else if (opcode == EEOP_SCAN_VAR) { v_values = v_scanvalues; v_nulls = v_scannulls; } + else if (opcode == EEOP_OLD_VAR) + { + v_values = v_oldvalues; + v_nulls = v_oldnulls; + } + else + { + v_values = v_newvalues; + v_nulls = v_newnulls; + } v_attnum = l_int32_const(lc, op->d.var.attnum); value = l_load_gep1(b, TypeSizeT, v_values, v_attnum, ""); @@ -432,6 +486,8 @@ llvm_compile_expr(ExprState *state) case EEOP_INNER_SYSVAR: case EEOP_OUTER_SYSVAR: case EEOP_SCAN_SYSVAR: + case EEOP_OLD_SYSVAR: + case EEOP_NEW_SYSVAR: { LLVMValueRef v_slot; @@ -439,8 +495,12 @@ llvm_compile_expr(ExprState *state) v_slot = v_innerslot; else if (opcode == EEOP_OUTER_SYSVAR) v_slot = v_outerslot; - else + else if (opcode == EEOP_SCAN_SYSVAR) v_slot = v_scanslot; + else if (opcode == EEOP_OLD_SYSVAR) + v_slot = v_oldslot; + else + v_slot = v_newslot; build_EvalXFunc(b, mod, "ExecEvalSysVar", v_state, op, v_econtext, v_slot); @@ -458,6 +518,8 @@ llvm_compile_expr(ExprState *state) case EEOP_ASSIGN_INNER_VAR: case EEOP_ASSIGN_OUTER_VAR: case EEOP_ASSIGN_SCAN_VAR: + case EEOP_ASSIGN_OLD_VAR: + case EEOP_ASSIGN_NEW_VAR: { LLVMValueRef v_value; LLVMValueRef v_isnull; @@ -478,11 +540,21 @@ llvm_compile_expr(ExprState *state) v_values = v_outervalues; v_nulls = v_outernulls; } - else + else if (opcode == EEOP_ASSIGN_SCAN_VAR) { v_values = v_scanvalues; v_nulls = v_scannulls; } + else if (opcode == EEOP_ASSIGN_OLD_VAR) + { + v_values = v_oldvalues; + v_nulls = v_oldnulls; + } + else + { + v_values = v_newvalues; + v_nulls = v_newnulls; + } /* load data */ v_attnum = l_int32_const(lc, op->d.assign_var.attnum); @@ -1654,6 +1726,45 @@ llvm_compile_expr(ExprState *state) LLVMBuildBr(b, opblocks[opno + 1]); break; + case EEOP_RETURNINGEXPR: + { + LLVMBasicBlockRef b_isnull; + LLVMValueRef v_flagsp; + LLVMValueRef v_flags; + LLVMValueRef v_nullflag; + + b_isnull = l_bb_before_v(opblocks[opno + 1], + "op.%d.row.isnull", opno); + + /* + * The next op actually evaluates the expression. If the + * OLD/NEW row doesn't exist, skip that and return NULL. + */ + v_flagsp = l_struct_gep(b, + StructExprState, + v_state, + FIELDNO_EXPRSTATE_FLAGS, + "v.state.flags"); + v_flags = l_load(b, TypeStorageBool, v_flagsp, ""); + + v_nullflag = l_int8_const(lc, op->d.returningexpr.nullflag); + + LLVMBuildCondBr(b, + LLVMBuildICmp(b, LLVMIntEQ, + LLVMBuildAnd(b, v_flags, + v_nullflag, ""), + l_sbool_const(0), ""), + opblocks[opno + 1], b_isnull); + + LLVMPositionBuilderAtEnd(b, b_isnull); + + LLVMBuildStore(b, l_sizet_const(0), v_resvaluep); + LLVMBuildStore(b, l_sbool_const(1), v_resnullp); + + LLVMBuildBr(b, opblocks[op->d.returningexpr.jumpdone]); + break; + } + case EEOP_ARRAYEXPR: build_EvalXFunc(b, mod, "ExecEvalArrayExpr", v_state, op); diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index b14d4d6adf4..007612563ca 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -80,12 +80,14 @@ makeVar(int varno, var->varlevelsup = varlevelsup; /* - * Only a few callers need to make Var nodes with non-null varnullingrels, - * or with varnosyn/varattnosyn different from varno/varattno. We don't - * provide separate arguments for them, but just initialize them to NULL - * and the given varno/varattno. This reduces code clutter and chance of - * error for most callers. + * Only a few callers need to make Var nodes with varreturningtype + * different from VAR_RETURNING_DEFAULT, non-null varnullingrels, or with + * varnosyn/varattnosyn different from varno/varattno. We don't provide + * separate arguments for them, but just initialize them to sensible + * default values. This reduces code clutter and chance of error for most + * callers. */ + var->varreturningtype = VAR_RETURNING_DEFAULT; var->varnullingrels = NULL; var->varnosyn = (Index) varno; var->varattnosyn = varattno; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index df779137c9d..7bc823507f1 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -278,6 +278,9 @@ exprType(const Node *expr) type = exprType((Node *) n->expr); } break; + case T_ReturningExpr: + type = exprType((Node *) ((const ReturningExpr *) expr)->retexpr); + break; case T_PlaceHolderVar: type = exprType((Node *) ((const PlaceHolderVar *) expr)->phexpr); break; @@ -529,6 +532,8 @@ exprTypmod(const Node *expr) return ((const CoerceToDomainValue *) expr)->typeMod; case T_SetToDefault: return ((const SetToDefault *) expr)->typeMod; + case T_ReturningExpr: + return exprTypmod((Node *) ((const ReturningExpr *) expr)->retexpr); case T_PlaceHolderVar: return exprTypmod((Node *) ((const PlaceHolderVar *) expr)->phexpr); default: @@ -1047,6 +1052,9 @@ exprCollation(const Node *expr) case T_InferenceElem: coll = exprCollation((Node *) ((const InferenceElem *) expr)->expr); break; + case T_ReturningExpr: + coll = exprCollation((Node *) ((const ReturningExpr *) expr)->retexpr); + break; case T_PlaceHolderVar: coll = exprCollation((Node *) ((const PlaceHolderVar *) expr)->phexpr); break; @@ -1110,7 +1118,7 @@ exprInputCollation(const Node *expr) * Assign collation information to an expression tree node. * * Note: since this is only used during parse analysis, we don't need to - * worry about subplans or PlaceHolderVars. + * worry about subplans, PlaceHolderVars, or ReturningExprs. */ void exprSetCollation(Node *expr, Oid collation) @@ -1624,6 +1632,9 @@ exprLocation(const Node *expr) case T_SetToDefault: loc = ((const SetToDefault *) expr)->location; break; + case T_ReturningExpr: + loc = exprLocation((Node *) ((const ReturningExpr *) expr)->retexpr); + break; case T_TargetEntry: /* just use argument's location */ loc = exprLocation((Node *) ((const TargetEntry *) expr)->expr); @@ -2613,6 +2624,8 @@ expression_tree_walker_impl(Node *node, return WALK(((PlaceHolderVar *) node)->phexpr); case T_InferenceElem: return WALK(((InferenceElem *) node)->expr); + case T_ReturningExpr: + return WALK(((ReturningExpr *) node)->retexpr); case T_AppendRelInfo: { AppendRelInfo *appinfo = (AppendRelInfo *) node; @@ -3454,6 +3467,16 @@ expression_tree_mutator_impl(Node *node, return (Node *) newnode; } break; + case T_ReturningExpr: + { + ReturningExpr *rexpr = (ReturningExpr *) node; + ReturningExpr *newnode; + + FLATCOPY(newnode, rexpr, ReturningExpr); + MUTATE(newnode->retexpr, rexpr->retexpr, Expr *); + return (Node *) newnode; + } + break; case T_TargetEntry: { TargetEntry *targetentry = (TargetEntry *) node; @@ -4005,6 +4028,7 @@ raw_expression_tree_walker_impl(Node *node, case T_A_Const: case T_A_Star: case T_MergeSupportFunc: + case T_ReturningOption: /* primitive node types with no subnodes */ break; case T_Alias: @@ -4233,7 +4257,7 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(stmt->onConflictClause)) return true; - if (WALK(stmt->returningList)) + if (WALK(stmt->returningClause)) return true; if (WALK(stmt->withClause)) return true; @@ -4249,7 +4273,7 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(stmt->whereClause)) return true; - if (WALK(stmt->returningList)) + if (WALK(stmt->returningClause)) return true; if (WALK(stmt->withClause)) return true; @@ -4267,7 +4291,7 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(stmt->fromClause)) return true; - if (WALK(stmt->returningList)) + if (WALK(stmt->returningClause)) return true; if (WALK(stmt->withClause)) return true; @@ -4285,7 +4309,7 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(stmt->mergeWhenClauses)) return true; - if (WALK(stmt->returningList)) + if (WALK(stmt->returningClause)) return true; if (WALK(stmt->withClause)) return true; @@ -4303,6 +4327,16 @@ raw_expression_tree_walker_impl(Node *node, return true; } break; + case T_ReturningClause: + { + ReturningClause *returning = (ReturningClause *) node; + + if (WALK(returning->options)) + return true; + if (WALK(returning->exprs)) + return true; + } + break; case T_SelectStmt: { SelectStmt *stmt = (SelectStmt *) node; diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 33645893912..1115ebeee29 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -3985,6 +3985,7 @@ subquery_push_qual(Query *subquery, RangeTblEntry *rte, Index rti, Node *qual) */ qual = ReplaceVarsFromTargetList(qual, rti, 0, rte, subquery->targetList, + subquery->resultRelation, REPLACEVARS_REPORT_ERROR, 0, &subquery->hasSubLinks); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 1caad5f3a61..1106cd85f0c 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -7121,6 +7121,8 @@ make_modifytable(PlannerInfo *root, Plan *subplan, int epqParam) { ModifyTable *node = makeNode(ModifyTable); + bool returning_old_or_new = false; + bool returning_old_or_new_valid = false; List *fdw_private_list; Bitmapset *direct_modify_plans; ListCell *lc; @@ -7185,6 +7187,8 @@ make_modifytable(PlannerInfo *root, Plan *subplan, } node->updateColnosLists = updateColnosLists; node->withCheckOptionLists = withCheckOptionLists; + node->returningOldAlias = root->parse->returningOldAlias; + node->returningNewAlias = root->parse->returningNewAlias; node->returningLists = returningLists; node->rowMarks = rowMarks; node->mergeActionLists = mergeActionLists; @@ -7265,7 +7269,8 @@ make_modifytable(PlannerInfo *root, Plan *subplan, * callback functions needed for that and (2) there are no local * structures that need to be run for each modified row: row-level * triggers on the foreign table, stored generated columns, WITH CHECK - * OPTIONs from parent views. + * OPTIONs from parent views, or Vars returning OLD/NEW in the + * RETURNING list. */ direct_modify = false; if (fdwroutine != NULL && @@ -7276,7 +7281,18 @@ make_modifytable(PlannerInfo *root, Plan *subplan, withCheckOptionLists == NIL && !has_row_triggers(root, rti, operation) && !has_stored_generated_columns(root, rti)) - direct_modify = fdwroutine->PlanDirectModify(root, node, rti, i); + { + /* returning_old_or_new is the same for all result relations */ + if (!returning_old_or_new_valid) + { + returning_old_or_new = + contain_vars_returning_old_or_new((Node *) + root->parse->returningList); + returning_old_or_new_valid = true; + } + if (!returning_old_or_new) + direct_modify = fdwroutine->PlanDirectModify(root, node, rti, i); + } if (direct_modify) direct_modify_plans = bms_add_member(direct_modify_plans, i); diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 81363589125..fff26555956 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -3070,6 +3070,21 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context) { Var *var = (Var *) node; + /* + * Verify that Vars with non-default varreturningtype only appear in + * the RETURNING list, and refer to the target relation. + */ + if (var->varreturningtype != VAR_RETURNING_DEFAULT) + { + if (context->inner_itlist != NULL || + context->outer_itlist == NULL || + context->acceptable_rel == 0) + elog(ERROR, "variable returning old/new found outside RETURNING list"); + if (var->varno != context->acceptable_rel) + elog(ERROR, "wrong varno %d (expected %d) for variable returning old/new", + var->varno, context->acceptable_rel); + } + /* Look for the var in the input tlists, first in the outer */ if (context->outer_itlist) { diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index eaaf8c1b49a..8230cbea3c3 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -354,17 +354,19 @@ build_subplan(PlannerInfo *root, Plan *plan, Path *path, Node *arg = pitem->item; /* - * The Var, PlaceHolderVar, Aggref or GroupingFunc has already been - * adjusted to have the correct varlevelsup, phlevelsup, or - * agglevelsup. + * The Var, PlaceHolderVar, Aggref, GroupingFunc, or ReturningExpr has + * already been adjusted to have the correct varlevelsup, phlevelsup, + * agglevelsup, or retlevelsup. * - * If it's a PlaceHolderVar, Aggref or GroupingFunc, its arguments - * might contain SubLinks, which have not yet been processed (see the - * comments for SS_replace_correlation_vars). Do that now. + * If it's a PlaceHolderVar, Aggref, GroupingFunc, or ReturningExpr, + * its arguments might contain SubLinks, which have not yet been + * processed (see the comments for SS_replace_correlation_vars). Do + * that now. */ if (IsA(arg, PlaceHolderVar) || IsA(arg, Aggref) || - IsA(arg, GroupingFunc)) + IsA(arg, GroupingFunc) || + IsA(arg, ReturningExpr)) arg = SS_process_sublinks(root, arg, false); splan->parParam = lappend_int(splan->parParam, pitem->paramId); @@ -1863,8 +1865,8 @@ convert_EXISTS_to_ANY(PlannerInfo *root, Query *subselect, /* * Replace correlation vars (uplevel vars) with Params. * - * Uplevel PlaceHolderVars, aggregates, GROUPING() expressions, and - * MergeSupportFuncs are replaced, too. + * Uplevel PlaceHolderVars, aggregates, GROUPING() expressions, + * MergeSupportFuncs, and ReturningExprs are replaced, too. * * Note: it is critical that this runs immediately after SS_process_sublinks. * Since we do not recurse into the arguments of uplevel PHVs and aggregates, @@ -1924,6 +1926,12 @@ replace_correlation_vars_mutator(Node *node, PlannerInfo *root) return (Node *) replace_outer_merge_support(root, (MergeSupportFunc *) node); } + if (IsA(node, ReturningExpr)) + { + if (((ReturningExpr *) node)->retlevelsup > 0) + return (Node *) replace_outer_returning(root, + (ReturningExpr *) node); + } return expression_tree_mutator(node, replace_correlation_vars_mutator, root); } @@ -1977,11 +1985,11 @@ process_sublinks_mutator(Node *node, process_sublinks_context *context) } /* - * Don't recurse into the arguments of an outer PHV, Aggref or - * GroupingFunc here. Any SubLinks in the arguments have to be dealt with - * at the outer query level; they'll be handled when build_subplan - * collects the PHV, Aggref or GroupingFunc into the arguments to be - * passed down to the current subplan. + * Don't recurse into the arguments of an outer PHV, Aggref, GroupingFunc, + * or ReturningExpr here. Any SubLinks in the arguments have to be dealt + * with at the outer query level; they'll be handled when build_subplan + * collects the PHV, Aggref, GroupingFunc, or ReturningExpr into the + * arguments to be passed down to the current subplan. */ if (IsA(node, PlaceHolderVar)) { @@ -1998,6 +2006,11 @@ process_sublinks_mutator(Node *node, process_sublinks_context *context) if (((GroupingFunc *) node)->agglevelsup > 0) return node; } + else if (IsA(node, ReturningExpr)) + { + if (((ReturningExpr *) node)->retlevelsup > 0) + return node; + } /* * We should never see a SubPlan expression in the input (since this is @@ -2110,7 +2123,9 @@ SS_identify_outer_params(PlannerInfo *root) outer_params = NULL; for (proot = root->parent_root; proot != NULL; proot = proot->parent_root) { - /* Include ordinary Var/PHV/Aggref/GroupingFunc params */ + /* + * Include ordinary Var/PHV/Aggref/GroupingFunc/ReturningExpr params. + */ foreach(l, proot->plan_params) { PlannerParamItem *pitem = (PlannerParamItem *) lfirst(l); diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 82775a3dd51..5d9225e9909 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -2539,7 +2539,8 @@ pullup_replace_vars_callback(Var *var, * expansion with varlevelsup = 0, and then adjust below if needed. */ expandRTE(rcon->target_rte, - var->varno, 0 /* not varlevelsup */ , var->location, + var->varno, 0 /* not varlevelsup */ , + var->varreturningtype, var->location, (var->vartype != RECORDOID), &colnames, &fields); /* Expand the generated per-field Vars, but don't insert PHVs there */ diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c index cece3a5be75..5b3dc0d8653 100644 --- a/src/backend/optimizer/util/appendinfo.c +++ b/src/backend/optimizer/util/appendinfo.c @@ -253,6 +253,13 @@ adjust_appendrel_attrs_mutator(Node *node, * all non-Var outputs of such subqueries, and then we could look up * the pre-existing PHV here. Or perhaps just wrap the translations * that way to begin with? + * + * If var->varreturningtype is not VAR_RETURNING_DEFAULT, then that + * also needs to be copied to the translated Var. That too would fail + * if the translation wasn't a Var, but that should never happen since + * a non-default var->varreturningtype is only used for Vars referring + * to the result relation, which should never be a flattened UNION ALL + * subquery. */ for (cnt = 0; cnt < nappinfos; cnt++) @@ -283,9 +290,17 @@ adjust_appendrel_attrs_mutator(Node *node, elog(ERROR, "attribute %d of relation \"%s\" does not exist", var->varattno, get_rel_name(appinfo->parent_reloid)); if (IsA(newnode, Var)) + { + ((Var *) newnode)->varreturningtype = var->varreturningtype; ((Var *) newnode)->varnullingrels = var->varnullingrels; - else if (var->varnullingrels != NULL) - elog(ERROR, "failed to apply nullingrels to a non-Var"); + } + else + { + if (var->varreturningtype != VAR_RETURNING_DEFAULT) + elog(ERROR, "failed to apply returningtype to a non-Var"); + if (var->varnullingrels != NULL) + elog(ERROR, "failed to apply nullingrels to a non-Var"); + } return newnode; } else if (var->varattno == 0) @@ -339,6 +354,8 @@ adjust_appendrel_attrs_mutator(Node *node, rowexpr->colnames = copyObject(rte->eref->colnames); rowexpr->location = -1; + if (var->varreturningtype != VAR_RETURNING_DEFAULT) + elog(ERROR, "failed to apply returningtype to a non-Var"); if (var->varnullingrels != NULL) elog(ERROR, "failed to apply nullingrels to a non-Var"); diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index de1f340cbe9..43dfecfb47f 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -1295,6 +1295,7 @@ contain_leaked_vars_walker(Node *node, void *context) case T_NullTest: case T_BooleanTest: case T_NextValueExpr: + case T_ReturningExpr: case T_List: /* @@ -3404,6 +3405,8 @@ eval_const_expressions_mutator(Node *node, fselect->resulttypmod, fselect->resultcollid, ((Var *) arg)->varlevelsup); + /* New Var has same OLD/NEW returning as old one */ + newvar->varreturningtype = ((Var *) arg)->varreturningtype; /* New Var is nullable by same rels as the old one */ newvar->varnullingrels = ((Var *) arg)->varnullingrels; return (Node *) newvar; diff --git a/src/backend/optimizer/util/paramassign.c b/src/backend/optimizer/util/paramassign.c index 8e089c27070..3bd3ce37c8f 100644 --- a/src/backend/optimizer/util/paramassign.c +++ b/src/backend/optimizer/util/paramassign.c @@ -91,6 +91,7 @@ assign_param_for_var(PlannerInfo *root, Var *var) pvar->vartype == var->vartype && pvar->vartypmod == var->vartypmod && pvar->varcollid == var->varcollid && + pvar->varreturningtype == var->varreturningtype && bms_equal(pvar->varnullingrels, var->varnullingrels)) return pitem->paramId; } @@ -359,6 +360,52 @@ replace_outer_merge_support(PlannerInfo *root, MergeSupportFunc *msf) } /* + * Generate a Param node to replace the given ReturningExpr expression which + * is expected to have retlevelsup > 0 (ie, it is not local). Record the need + * for the ReturningExpr in the proper upper-level root->plan_params. + */ +Param * +replace_outer_returning(PlannerInfo *root, ReturningExpr *rexpr) +{ + Param *retval; + PlannerParamItem *pitem; + Index levelsup; + Oid ptype = exprType((Node *) rexpr->retexpr); + + Assert(rexpr->retlevelsup > 0 && rexpr->retlevelsup < root->query_level); + + /* Find the query level the ReturningExpr belongs to */ + for (levelsup = rexpr->retlevelsup; levelsup > 0; levelsup--) + root = root->parent_root; + + /* + * It does not seem worthwhile to try to de-duplicate references to outer + * ReturningExprs. Just make a new slot every time. + */ + rexpr = copyObject(rexpr); + IncrementVarSublevelsUp((Node *) rexpr, -((int) rexpr->retlevelsup), 0); + Assert(rexpr->retlevelsup == 0); + + pitem = makeNode(PlannerParamItem); + pitem->item = (Node *) rexpr; + pitem->paramId = list_length(root->glob->paramExecTypes); + root->glob->paramExecTypes = lappend_oid(root->glob->paramExecTypes, + ptype); + + root->plan_params = lappend(root->plan_params, pitem); + + retval = makeNode(Param); + retval->paramkind = PARAM_EXEC; + retval->paramid = pitem->paramId; + retval->paramtype = ptype; + retval->paramtypmod = exprTypmod((Node *) rexpr->retexpr); + retval->paramcollid = exprCollation((Node *) rexpr->retexpr); + retval->location = exprLocation((Node *) rexpr->retexpr); + + return retval; +} + +/* * Generate a Param node to replace the given Var, * which is expected to come from some upper NestLoop plan node. * Record the need for the Var in root->curOuterParams. diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index f2d319101d3..71abb01f655 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -1857,8 +1857,8 @@ build_physical_tlist(PlannerInfo *root, RelOptInfo *rel) case RTE_NAMEDTUPLESTORE: case RTE_RESULT: /* Not all of these can have dropped cols, but share code anyway */ - expandRTE(rte, varno, 0, -1, true /* include dropped */ , - NULL, &colvars); + expandRTE(rte, varno, 0, VAR_RETURNING_DEFAULT, -1, + true /* include dropped */ , NULL, &colvars); foreach(l, colvars) { var = (Var *) lfirst(l); diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c index 367d080ccf9..8065237a189 100644 --- a/src/backend/optimizer/util/var.c +++ b/src/backend/optimizer/util/var.c @@ -76,6 +76,7 @@ static bool pull_varattnos_walker(Node *node, pull_varattnos_context *context); static bool pull_vars_walker(Node *node, pull_vars_context *context); static bool contain_var_clause_walker(Node *node, void *context); static bool contain_vars_of_level_walker(Node *node, int *sublevels_up); +static bool contain_vars_returning_old_or_new_walker(Node *node, void *context); static bool locate_var_of_level_walker(Node *node, locate_var_of_level_context *context); static bool pull_var_clause_walker(Node *node, @@ -493,6 +494,49 @@ contain_vars_of_level_walker(Node *node, int *sublevels_up) /* + * contain_vars_returning_old_or_new + * Recursively scan a clause to discover whether it contains any Var nodes + * (of the current query level) whose varreturningtype is VAR_RETURNING_OLD + * or VAR_RETURNING_NEW. + * + * Returns true if any found. + * + * Any ReturningExprs are also detected --- if an OLD/NEW Var was rewritten, + * we still regard this as a clause that returns OLD/NEW values. + * + * Does not examine subqueries, therefore must only be used after reduction + * of sublinks to subplans! + */ +bool +contain_vars_returning_old_or_new(Node *node) +{ + return contain_vars_returning_old_or_new_walker(node, NULL); +} + +static bool +contain_vars_returning_old_or_new_walker(Node *node, void *context) +{ + if (node == NULL) + return false; + if (IsA(node, Var)) + { + if (((Var *) node)->varlevelsup == 0 && + ((Var *) node)->varreturningtype != VAR_RETURNING_DEFAULT) + return true; /* abort the tree traversal and return true */ + return false; + } + if (IsA(node, ReturningExpr)) + { + if (((ReturningExpr *) node)->retlevelsup == 0) + return true; /* abort the tree traversal and return true */ + return false; + } + return expression_tree_walker(node, contain_vars_returning_old_or_new_walker, + context); +} + + +/* * locate_var_of_level * Find the parse location of any Var of the specified query level. * diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 561cf4d6a77..76f58b3aca3 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -641,8 +641,8 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) qual = transformWhereClause(pstate, stmt->whereClause, EXPR_KIND_WHERE, "WHERE"); - qry->returningList = transformReturningList(pstate, stmt->returningList, - EXPR_KIND_RETURNING); + transformReturningClause(pstate, qry, stmt->returningClause, + EXPR_KIND_RETURNING); /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; @@ -1054,7 +1054,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) * contain only the target relation, removing any entries added in a * sub-SELECT or VALUES list. */ - if (stmt->onConflictClause || stmt->returningList) + if (stmt->onConflictClause || stmt->returningClause) { pstate->p_namespace = NIL; addNSItemToQuery(pstate, pstate->p_target_nsitem, @@ -1067,10 +1067,9 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) stmt->onConflictClause); /* Process RETURNING, if any. */ - if (stmt->returningList) - qry->returningList = transformReturningList(pstate, - stmt->returningList, - EXPR_KIND_RETURNING); + if (stmt->returningClause) + transformReturningClause(pstate, qry, stmt->returningClause, + EXPR_KIND_RETURNING); /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; @@ -2548,8 +2547,8 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) qual = transformWhereClause(pstate, stmt->whereClause, EXPR_KIND_WHERE, "WHERE"); - qry->returningList = transformReturningList(pstate, stmt->returningList, - EXPR_KIND_RETURNING); + transformReturningClause(pstate, qry, stmt->returningClause, + EXPR_KIND_RETURNING); /* * Now we are done with SELECT-like processing, and can get on with @@ -2645,18 +2644,120 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist) } /* - * transformReturningList - + * addNSItemForReturning - + * add a ParseNamespaceItem for the OLD or NEW alias in RETURNING. + */ +static void +addNSItemForReturning(ParseState *pstate, const char *aliasname, + VarReturningType returning_type) +{ + List *colnames; + int numattrs; + ParseNamespaceColumn *nscolumns; + ParseNamespaceItem *nsitem; + + /* copy per-column data from the target relation */ + colnames = pstate->p_target_nsitem->p_rte->eref->colnames; + numattrs = list_length(colnames); + + nscolumns = (ParseNamespaceColumn *) + palloc(numattrs * sizeof(ParseNamespaceColumn)); + + memcpy(nscolumns, pstate->p_target_nsitem->p_nscolumns, + numattrs * sizeof(ParseNamespaceColumn)); + + /* mark all columns as returning OLD/NEW */ + for (int i = 0; i < numattrs; i++) + nscolumns[i].p_varreturningtype = returning_type; + + /* build the nsitem, copying most fields from the target relation */ + nsitem = (ParseNamespaceItem *) palloc(sizeof(ParseNamespaceItem)); + nsitem->p_names = makeAlias(aliasname, colnames); + nsitem->p_rte = pstate->p_target_nsitem->p_rte; + nsitem->p_rtindex = pstate->p_target_nsitem->p_rtindex; + nsitem->p_perminfo = pstate->p_target_nsitem->p_perminfo; + nsitem->p_nscolumns = nscolumns; + nsitem->p_returning_type = returning_type; + + /* add it to the query namespace as a table-only item */ + addNSItemToQuery(pstate, nsitem, false, true, false); +} + +/* + * transformReturningClause - * handle a RETURNING clause in INSERT/UPDATE/DELETE/MERGE */ -List * -transformReturningList(ParseState *pstate, List *returningList, - ParseExprKind exprKind) +void +transformReturningClause(ParseState *pstate, Query *qry, + ReturningClause *returningClause, + ParseExprKind exprKind) { - List *rlist; + int save_nslen = list_length(pstate->p_namespace); int save_next_resno; - if (returningList == NIL) - return NIL; /* nothing to do */ + if (returningClause == NULL) + return; /* nothing to do */ + + /* + * Scan RETURNING WITH(...) options for OLD/NEW alias names. Complain if + * there is any conflict with existing relations. + */ + foreach_node(ReturningOption, option, returningClause->options) + { + switch (option->option) + { + case RETURNING_OPTION_OLD: + if (qry->returningOldAlias != NULL) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + /* translator: %s is OLD or NEW */ + errmsg("%s cannot be specified multiple times", "OLD"), + parser_errposition(pstate, option->location)); + qry->returningOldAlias = option->value; + break; + + case RETURNING_OPTION_NEW: + if (qry->returningNewAlias != NULL) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + /* translator: %s is OLD or NEW */ + errmsg("%s cannot be specified multiple times", "NEW"), + parser_errposition(pstate, option->location)); + qry->returningNewAlias = option->value; + break; + + default: + elog(ERROR, "unrecognized returning option: %d", option->option); + } + + if (refnameNamespaceItem(pstate, NULL, option->value, -1, NULL) != NULL) + ereport(ERROR, + errcode(ERRCODE_DUPLICATE_ALIAS), + errmsg("table name \"%s\" specified more than once", + option->value), + parser_errposition(pstate, option->location)); + + addNSItemForReturning(pstate, option->value, + option->option == RETURNING_OPTION_OLD ? + VAR_RETURNING_OLD : VAR_RETURNING_NEW); + } + + /* + * If OLD/NEW alias names weren't explicitly specified, use "old"/"new" + * unless masked by existing relations. + */ + if (qry->returningOldAlias == NULL && + refnameNamespaceItem(pstate, NULL, "old", -1, NULL) == NULL) + { + qry->returningOldAlias = "old"; + addNSItemForReturning(pstate, "old", VAR_RETURNING_OLD); + } + if (qry->returningNewAlias == NULL && + refnameNamespaceItem(pstate, NULL, "new", -1, NULL) == NULL) + { + qry->returningNewAlias = "new"; + addNSItemForReturning(pstate, "new", VAR_RETURNING_NEW); + } /* * We need to assign resnos starting at one in the RETURNING list. Save @@ -2666,8 +2767,10 @@ transformReturningList(ParseState *pstate, List *returningList, save_next_resno = pstate->p_next_resno; pstate->p_next_resno = 1; - /* transform RETURNING identically to a SELECT targetlist */ - rlist = transformTargetList(pstate, returningList, exprKind); + /* transform RETURNING expressions identically to a SELECT targetlist */ + qry->returningList = transformTargetList(pstate, + returningClause->exprs, + exprKind); /* * Complain if the nonempty tlist expanded to nothing (which is possible @@ -2675,24 +2778,23 @@ transformReturningList(ParseState *pstate, List *returningList, * allow this, the parsed Query will look like it didn't have RETURNING, * with results that would probably surprise the user. */ - if (rlist == NIL) + if (qry->returningList == NIL) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("RETURNING must have at least one column"), parser_errposition(pstate, - exprLocation(linitial(returningList))))); + exprLocation(linitial(returningClause->exprs))))); /* mark column origins */ - markTargetListOrigins(pstate, rlist); + markTargetListOrigins(pstate, qry->returningList); /* resolve any still-unresolved output columns as being type text */ if (pstate->p_resolve_unknowns) - resolveTargetListUnknowns(pstate, rlist); + resolveTargetListUnknowns(pstate, qry->returningList); /* restore state */ + pstate->p_namespace = list_truncate(pstate->p_namespace, save_nslen); pstate->p_next_resno = save_next_resno; - - return rlist; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6079de70e09..d7f9c00c409 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -267,6 +267,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); MergeWhenClause *mergewhen; struct KeyActions *keyactions; struct KeyAction *keyaction; + ReturningClause *retclause; + ReturningOptionKind retoptionkind; } %type <node> stmt toplevel_stmt schema_stmt routine_body_stmt @@ -436,7 +438,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); opclass_purpose opt_opfamily transaction_mode_list_or_empty OptTableFuncElementList TableFuncElementList opt_type_modifiers prep_type_clause - execute_param_clause using_clause returning_clause + execute_param_clause using_clause + returning_with_clause returning_options opt_enum_val_list enum_val_list table_func_column_list create_generic_options alter_generic_options relation_expr_list dostmt_opt_list @@ -445,6 +448,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); vacuum_relation_list opt_vacuum_relation_list drop_option_list pub_obj_list +%type <retclause> returning_clause +%type <node> returning_option +%type <retoptionkind> returning_option_kind %type <node> opt_routine_body %type <groupclause> group_clause %type <list> group_by_list @@ -12202,7 +12208,7 @@ InsertStmt: { $5->relation = $4; $5->onConflictClause = $6; - $5->returningList = $7; + $5->returningClause = $7; $5->withClause = $1; $5->stmt_location = @$; $$ = (Node *) $5; @@ -12336,8 +12342,45 @@ opt_conf_expr: ; returning_clause: - RETURNING target_list { $$ = $2; } - | /* EMPTY */ { $$ = NIL; } + RETURNING returning_with_clause target_list + { + ReturningClause *n = makeNode(ReturningClause); + + n->options = $2; + n->exprs = $3; + $$ = n; + } + | /* EMPTY */ + { + $$ = NULL; + } + ; + +returning_with_clause: + WITH '(' returning_options ')' { $$ = $3; } + | /* EMPTY */ { $$ = NIL; } + ; + +returning_options: + returning_option { $$ = list_make1($1); } + | returning_options ',' returning_option { $$ = lappend($1, $3); } + ; + +returning_option: + returning_option_kind AS ColId + { + ReturningOption *n = makeNode(ReturningOption); + + n->option = $1; + n->value = $3; + n->location = @1; + $$ = (Node *) n; + } + ; + +returning_option_kind: + OLD { $$ = RETURNING_OPTION_OLD; } + | NEW { $$ = RETURNING_OPTION_NEW; } ; @@ -12356,7 +12399,7 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias n->relation = $4; n->usingClause = $5; n->whereClause = $6; - n->returningList = $7; + n->returningClause = $7; n->withClause = $1; n->stmt_location = @$; $$ = (Node *) n; @@ -12431,7 +12474,7 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias n->targetList = $5; n->fromClause = $6; n->whereClause = $7; - n->returningList = $8; + n->returningClause = $8; n->withClause = $1; n->stmt_location = @$; $$ = (Node *) n; @@ -12510,7 +12553,7 @@ MergeStmt: m->sourceRelation = $6; m->joinCondition = $8; m->mergeWhenClauses = $9; - m->returningList = $10; + m->returningClause = $10; m->stmt_location = @$; $$ = (Node *) m; diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 75a1bbfd896..2e64fcae7b2 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -1585,6 +1585,7 @@ transformFromClauseItem(ParseState *pstate, Node *n, jnsitem->p_cols_visible = true; jnsitem->p_lateral_only = false; jnsitem->p_lateral_ok = true; + jnsitem->p_returning_type = VAR_RETURNING_DEFAULT; /* Per SQL, we must check for alias conflicts */ checkNameSpaceConflicts(pstate, list_make1(jnsitem), my_namespace); my_namespace = lappend(my_namespace, jnsitem); @@ -1647,6 +1648,7 @@ buildVarFromNSColumn(ParseState *pstate, ParseNamespaceColumn *nscol) nscol->p_varcollid, 0); /* makeVar doesn't offer parameters for these, so set by hand: */ + var->varreturningtype = nscol->p_varreturningtype; var->varnosyn = nscol->p_varnosyn; var->varattnosyn = nscol->p_varattnosyn; diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 285a5c88d58..bad1df732ea 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -2619,6 +2619,13 @@ transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem, * point, there seems no harm in expanding it now rather than during * planning. * + * Note that if the nsitem is an OLD/NEW alias for the target RTE (as can + * appear in a RETURNING list), its alias won't match the target RTE's + * alias, but we still want to make a whole-row Var here rather than a + * RowExpr, for consistency with direct references to the target RTE, and + * so that any dropped columns are handled correctly. Thus we also check + * p_returning_type here. + * * Note that if the RTE is a function returning scalar, we create just a * plain reference to the function value, not a composite containing a * single column. This is pretty inconsistent at first sight, but it's @@ -2626,13 +2633,17 @@ transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem, * "rel.*" mean the same thing for composite relations, so why not for * scalar functions... */ - if (nsitem->p_names == nsitem->p_rte->eref) + if (nsitem->p_names == nsitem->p_rte->eref || + nsitem->p_returning_type != VAR_RETURNING_DEFAULT) { Var *result; result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex, sublevels_up, true); + /* mark Var for RETURNING OLD/NEW, as necessary */ + result->varreturningtype = nsitem->p_returning_type; + /* location is not filled in by makeWholeRowVar */ result->location = location; @@ -2655,9 +2666,8 @@ transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem, * are in the RTE. We needn't worry about marking the RTE for SELECT * access, as the common columns are surely so marked already. */ - expandRTE(nsitem->p_rte, nsitem->p_rtindex, - sublevels_up, location, false, - NULL, &fields); + expandRTE(nsitem->p_rte, nsitem->p_rtindex, sublevels_up, + nsitem->p_returning_type, location, false, NULL, &fields); rowexpr = makeNode(RowExpr); rowexpr->args = list_truncate(fields, list_length(nsitem->p_names->colnames)); diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c index f92bef99d59..51d7703eff7 100644 --- a/src/backend/parser/parse_merge.c +++ b/src/backend/parser/parse_merge.c @@ -247,8 +247,8 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) qry->jointree = makeFromExpr(pstate->p_joinlist, NULL); /* Transform the RETURNING list, if any */ - qry->returningList = transformReturningList(pstate, stmt->returningList, - EXPR_KIND_MERGE_RETURNING); + transformReturningClause(pstate, qry, stmt->returningClause, + EXPR_KIND_MERGE_RETURNING); /* * We now have a good query shape, so now look at the WHEN conditions and diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 92a04e35dff..679bf640c62 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -91,11 +91,13 @@ static void markRTEForSelectPriv(ParseState *pstate, int rtindex, AttrNumber col); static void expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up, + VarReturningType returning_type, int location, bool include_dropped, List **colnames, List **colvars); static void expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset, int rtindex, int sublevels_up, + VarReturningType returning_type, int location, bool include_dropped, List **colnames, List **colvars); static int specialAttNum(const char *attname); @@ -763,6 +765,9 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem, } var->location = location; + /* Mark Var for RETURNING OLD/NEW, as necessary */ + var->varreturningtype = nsitem->p_returning_type; + /* Mark Var if it's nulled by any outer joins */ markNullableIfNeeded(pstate, var); @@ -1336,6 +1341,7 @@ buildNSItemFromTupleDesc(RangeTblEntry *rte, Index rtindex, nsitem->p_cols_visible = true; nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; + nsitem->p_returning_type = VAR_RETURNING_DEFAULT; return nsitem; } @@ -1399,6 +1405,7 @@ buildNSItemFromLists(RangeTblEntry *rte, Index rtindex, nsitem->p_cols_visible = true; nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; + nsitem->p_returning_type = VAR_RETURNING_DEFAULT; return nsitem; } @@ -2300,6 +2307,7 @@ addRangeTableEntryForJoin(ParseState *pstate, nsitem->p_cols_visible = true; nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; + nsitem->p_returning_type = VAR_RETURNING_DEFAULT; return nsitem; } @@ -2720,9 +2728,10 @@ addNSItemToQuery(ParseState *pstate, ParseNamespaceItem *nsitem, * results. If include_dropped is true then empty strings and NULL constants * (not Vars!) are returned for dropped columns. * - * rtindex, sublevels_up, and location are the varno, varlevelsup, and location - * values to use in the created Vars. Ordinarily rtindex should match the - * actual position of the RTE in its rangetable. + * rtindex, sublevels_up, returning_type, and location are the varno, + * varlevelsup, varreturningtype, and location values to use in the created + * Vars. Ordinarily rtindex should match the actual position of the RTE in + * its rangetable. * * The output lists go into *colnames and *colvars. * If only one of the two kinds of output list is needed, pass NULL for the @@ -2730,6 +2739,7 @@ addNSItemToQuery(ParseState *pstate, ParseNamespaceItem *nsitem, */ void expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, + VarReturningType returning_type, int location, bool include_dropped, List **colnames, List **colvars) { @@ -2745,7 +2755,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, case RTE_RELATION: /* Ordinary relation RTE */ expandRelation(rte->relid, rte->eref, - rtindex, sublevels_up, location, + rtindex, sublevels_up, returning_type, location, include_dropped, colnames, colvars); break; case RTE_SUBQUERY: @@ -2792,6 +2802,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, exprTypmod((Node *) te->expr), exprCollation((Node *) te->expr), sublevels_up); + varnode->varreturningtype = returning_type; varnode->location = location; *colvars = lappend(*colvars, varnode); @@ -2829,7 +2840,8 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, Assert(tupdesc); expandTupleDesc(tupdesc, rte->eref, rtfunc->funccolcount, atts_done, - rtindex, sublevels_up, location, + rtindex, sublevels_up, + returning_type, location, include_dropped, colnames, colvars); } else if (functypclass == TYPEFUNC_SCALAR) @@ -2849,6 +2861,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, exprTypmod(rtfunc->funcexpr), exprCollation(rtfunc->funcexpr), sublevels_up); + varnode->varreturningtype = returning_type; varnode->location = location; *colvars = lappend(*colvars, varnode); @@ -2891,6 +2904,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, attrtypmod, attrcollation, sublevels_up); + varnode->varreturningtype = returning_type; varnode->location = location; *colvars = lappend(*colvars, varnode); } @@ -2920,6 +2934,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, InvalidOid, sublevels_up); + varnode->varreturningtype = returning_type; *colvars = lappend(*colvars, varnode); } } @@ -3002,6 +3017,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, exprTypmod(avar), exprCollation(avar), sublevels_up); + varnode->varreturningtype = returning_type; varnode->location = location; *colvars = lappend(*colvars, varnode); @@ -3057,6 +3073,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, varnode = makeVar(rtindex, varattno, coltype, coltypmod, colcoll, sublevels_up); + varnode->varreturningtype = returning_type; varnode->location = location; *colvars = lappend(*colvars, varnode); @@ -3089,6 +3106,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, */ static void expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up, + VarReturningType returning_type, int location, bool include_dropped, List **colnames, List **colvars) { @@ -3097,7 +3115,7 @@ expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up, /* Get the tupledesc and turn it over to expandTupleDesc */ rel = relation_open(relid, AccessShareLock); expandTupleDesc(rel->rd_att, eref, rel->rd_att->natts, 0, - rtindex, sublevels_up, + rtindex, sublevels_up, returning_type, location, include_dropped, colnames, colvars); relation_close(rel, AccessShareLock); @@ -3115,6 +3133,7 @@ expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up, static void expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset, int rtindex, int sublevels_up, + VarReturningType returning_type, int location, bool include_dropped, List **colnames, List **colvars) { @@ -3175,6 +3194,7 @@ expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset, attr->atttypid, attr->atttypmod, attr->attcollation, sublevels_up); + varnode->varreturningtype = returning_type; varnode->location = location; *colvars = lappend(*colvars, varnode); @@ -3227,6 +3247,7 @@ expandNSItemVars(ParseState *pstate, ParseNamespaceItem *nsitem, nscol->p_varcollid, sublevels_up); /* makeVar doesn't offer parameters for these, so set by hand: */ + var->varreturningtype = nscol->p_varreturningtype; var->varnosyn = nscol->p_varnosyn; var->varattnosyn = nscol->p_varattnosyn; var->location = location; diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 93915031be8..4aba0d9d4d5 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -1550,8 +1550,8 @@ expandRecordVariable(ParseState *pstate, Var *var, int levelsup) *lvar; int i; - expandRTE(rte, var->varno, 0, var->location, false, - &names, &vars); + expandRTE(rte, var->varno, 0, var->varreturningtype, + var->location, false, &names, &vars); tupleDesc = CreateTemplateTupleDesc(list_length(vars)); i = 1; diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 1a5dfd0aa47..b74f2acc327 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -641,6 +641,7 @@ rewriteRuleAction(Query *parsetree, 0, rt_fetch(new_varno, sub_action->rtable), parsetree->targetList, + sub_action->resultRelation, (event == CMD_UPDATE) ? REPLACEVARS_CHANGE_VARNO : REPLACEVARS_SUBSTITUTE_NULL, @@ -674,10 +675,15 @@ rewriteRuleAction(Query *parsetree, rt_fetch(parsetree->resultRelation, parsetree->rtable), rule_action->returningList, + rule_action->resultRelation, REPLACEVARS_REPORT_ERROR, 0, &rule_action->hasSubLinks); + /* use triggering query's aliases for OLD and NEW in RETURNING list */ + rule_action->returningOldAlias = parsetree->returningOldAlias; + rule_action->returningNewAlias = parsetree->returningNewAlias; + /* * There could have been some SubLinks in parsetree's returningList, * in which case we'd better mark the rule_action correctly. @@ -2358,6 +2364,7 @@ CopyAndAddInvertedQual(Query *parsetree, rt_fetch(rt_index, parsetree->rtable), parsetree->targetList, + parsetree->resultRelation, (event == CMD_UPDATE) ? REPLACEVARS_CHANGE_VARNO : REPLACEVARS_SUBSTITUTE_NULL, @@ -3582,6 +3589,7 @@ rewriteTargetView(Query *parsetree, Relation view) 0, view_rte, view_targetlist, + new_rt_index, REPLACEVARS_REPORT_ERROR, 0, NULL); @@ -3733,6 +3741,7 @@ rewriteTargetView(Query *parsetree, Relation view) 0, view_rte, tmp_tlist, + new_rt_index, REPLACEVARS_REPORT_ERROR, 0, &parsetree->hasSubLinks); diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c index 047396e390b..bca11500e9e 100644 --- a/src/backend/rewrite/rewriteManip.c +++ b/src/backend/rewrite/rewriteManip.c @@ -810,6 +810,14 @@ IncrementVarSublevelsUp_walker(Node *node, phv->phlevelsup += context->delta_sublevels_up; /* fall through to recurse into argument */ } + if (IsA(node, ReturningExpr)) + { + ReturningExpr *rexpr = (ReturningExpr *) node; + + if (rexpr->retlevelsup >= context->min_sublevels_up) + rexpr->retlevelsup += context->delta_sublevels_up; + /* fall through to recurse into argument */ + } if (IsA(node, RangeTblEntry)) { RangeTblEntry *rte = (RangeTblEntry *) node; @@ -875,6 +883,67 @@ IncrementVarSublevelsUp_rtable(List *rtable, int delta_sublevels_up, QTW_EXAMINE_RTES_BEFORE); } +/* + * SetVarReturningType - adjust Var nodes for a specified varreturningtype. + * + * Find all Var nodes referring to the specified result relation in the given + * expression and set their varreturningtype to the specified value. + * + * NOTE: although this has the form of a walker, we cheat and modify the + * Var nodes in-place. The given expression tree should have been copied + * earlier to ensure that no unwanted side-effects occur! + */ + +typedef struct +{ + int result_relation; + int sublevels_up; + VarReturningType returning_type; +} SetVarReturningType_context; + +static bool +SetVarReturningType_walker(Node *node, SetVarReturningType_context *context) +{ + if (node == NULL) + return false; + if (IsA(node, Var)) + { + Var *var = (Var *) node; + + if (var->varno == context->result_relation && + var->varlevelsup == context->sublevels_up) + var->varreturningtype = context->returning_type; + + return false; + } + + if (IsA(node, Query)) + { + /* Recurse into subselects */ + bool result; + + context->sublevels_up++; + result = query_tree_walker((Query *) node, SetVarReturningType_walker, + context, 0); + context->sublevels_up--; + return result; + } + return expression_tree_walker(node, SetVarReturningType_walker, context); +} + +static void +SetVarReturningType(Node *node, int result_relation, int sublevels_up, + VarReturningType returning_type) +{ + SetVarReturningType_context context; + + context.result_relation = result_relation; + context.sublevels_up = sublevels_up; + context.returning_type = returning_type; + + /* Expect to start with an expression */ + SetVarReturningType_walker(node, &context); +} /* * rangeTableEntry_used - detect whether an RTE is referenced somewhere @@ -1640,6 +1709,15 @@ map_variable_attnos(Node *node, * relation. This is needed to handle whole-row Vars referencing the target. * We expand such Vars into RowExpr constructs. * + * In addition, for INSERT/UPDATE/DELETE/MERGE queries, the caller must + * provide result_relation, the index of the result relation in the rewritten + * query. This is needed to handle OLD/NEW RETURNING list Vars referencing + * target_varno. When such Vars are expanded, their varreturningtype is + * copied onto any replacement Vars referencing result_relation. In addition, + * if the replacement expression from the targetlist is not simply a Var + * referencing result_relation, it is wrapped in a ReturningExpr node (causing + * the executor to return NULL if the OLD/NEW row doesn't exist). + * * outer_hasSubLinks works the same as for replace_rte_variables(). */ @@ -1647,6 +1725,7 @@ typedef struct { RangeTblEntry *target_rte; List *targetlist; + int result_relation; ReplaceVarsNoMatchOption nomatch_option; int nomatch_varno; } ReplaceVarsFromTargetList_context; @@ -1671,10 +1750,13 @@ ReplaceVarsFromTargetList_callback(Var *var, * dropped columns. If the var is RECORD (ie, this is a JOIN), then * omit dropped columns. In the latter case, attach column names to * the RowExpr for use of the executor and ruleutils.c. + * + * The varreturningtype is copied onto each individual field Var, so + * that it is handled correctly when we recurse. */ expandRTE(rcon->target_rte, - var->varno, var->varlevelsup, var->location, - (var->vartype != RECORDOID), + var->varno, var->varlevelsup, var->varreturningtype, + var->location, (var->vartype != RECORDOID), &colnames, &fields); /* Adjust the generated per-field Vars... */ fields = (List *) replace_rte_variables_mutator((Node *) fields, @@ -1686,6 +1768,18 @@ ReplaceVarsFromTargetList_callback(Var *var, rowexpr->colnames = (var->vartype == RECORDOID) ? colnames : NIL; rowexpr->location = var->location; + /* Wrap it in a ReturningExpr, if needed, per comments above */ + if (var->varreturningtype != VAR_RETURNING_DEFAULT) + { + ReturningExpr *rexpr = makeNode(ReturningExpr); + + rexpr->retlevelsup = var->varlevelsup; + rexpr->retold = (var->varreturningtype == VAR_RETURNING_OLD); + rexpr->retexpr = (Expr *) rowexpr; + + return (Node *) rexpr; + } + return (Node *) rowexpr; } @@ -1751,6 +1845,34 @@ ReplaceVarsFromTargetList_callback(Var *var, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("NEW variables in ON UPDATE rules cannot reference columns that are part of a multiple assignment in the subject UPDATE command"))); + /* Handle any OLD/NEW RETURNING list Vars */ + if (var->varreturningtype != VAR_RETURNING_DEFAULT) + { + /* + * Copy varreturningtype onto any Vars in the tlist item that + * refer to result_relation (which had better be non-zero). + */ + if (rcon->result_relation == 0) + elog(ERROR, "variable returning old/new found outside RETURNING list"); + + SetVarReturningType((Node *) newnode, rcon->result_relation, + var->varlevelsup, var->varreturningtype); + + /* Wrap it in a ReturningExpr, if needed, per comments above */ + if (!IsA(newnode, Var) || + ((Var *) newnode)->varno != rcon->result_relation || + ((Var *) newnode)->varlevelsup != var->varlevelsup) + { + ReturningExpr *rexpr = makeNode(ReturningExpr); + + rexpr->retlevelsup = var->varlevelsup; + rexpr->retold = (var->varreturningtype == VAR_RETURNING_OLD); + rexpr->retexpr = newnode; + + newnode = (Expr *) rexpr; + } + } + return (Node *) newnode; } } @@ -1760,6 +1882,7 @@ ReplaceVarsFromTargetList(Node *node, int target_varno, int sublevels_up, RangeTblEntry *target_rte, List *targetlist, + int result_relation, ReplaceVarsNoMatchOption nomatch_option, int nomatch_varno, bool *outer_hasSubLinks) @@ -1768,6 +1891,7 @@ ReplaceVarsFromTargetList(Node *node, context.target_rte = target_rte; context.targetlist = targetlist; + context.result_relation = result_relation; context.nomatch_option = nomatch_option; context.nomatch_varno = nomatch_varno; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 2a77f715fba..54dad975553 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -167,6 +167,8 @@ typedef struct List *subplans; /* List of Plan trees for SubPlans */ List *ctes; /* List of CommonTableExpr nodes */ AppendRelInfo **appendrels; /* Array of AppendRelInfo nodes, or NULL */ + char *ret_old_alias; /* alias for OLD in RETURNING list */ + char *ret_new_alias; /* alias for NEW in RETURNING list */ /* Workspace for column alias assignment: */ bool unique_using; /* Are we making USING names globally unique */ List *using_names; /* List of assigned names for USING columns */ @@ -426,6 +428,7 @@ static void get_merge_query_def(Query *query, deparse_context *context); static void get_utility_query_def(Query *query, deparse_context *context); static void get_basic_select_query(Query *query, deparse_context *context); static void get_target_list(List *targetList, deparse_context *context); +static void get_returning_clause(Query *query, deparse_context *context); static void get_setop_query(Node *setOp, Query *query, deparse_context *context); static Node *get_rule_sortgroupclause(Index ref, List *tlist, @@ -3804,6 +3807,10 @@ deparse_context_for_plan_tree(PlannedStmt *pstmt, List *rtable_names) * the most-closely-nested first. This is needed to resolve PARAM_EXEC * Params. Note we assume that all the Plan nodes share the same rtable. * + * For a ModifyTable plan, we might also need to resolve references to OLD/NEW + * variables in the RETURNING list, so we copy the alias names of the OLD and + * NEW rows from the ModifyTable plan node. + * * Once this function has been called, deparse_expression() can be called on * subsidiary expression(s) of the specified Plan node. To deparse * expressions of a different Plan node in the same Plan tree, re-call this @@ -3824,6 +3831,13 @@ set_deparse_context_plan(List *dpcontext, Plan *plan, List *ancestors) dpns->ancestors = ancestors; set_deparse_plan(dpns, plan); + /* For ModifyTable, set aliases for OLD and NEW in RETURNING */ + if (IsA(plan, ModifyTable)) + { + dpns->ret_old_alias = ((ModifyTable *) plan)->returningOldAlias; + dpns->ret_new_alias = ((ModifyTable *) plan)->returningNewAlias; + } + return dpcontext; } @@ -4021,6 +4035,8 @@ set_deparse_for_query(deparse_namespace *dpns, Query *query, dpns->subplans = NIL; dpns->ctes = query->cteList; dpns->appendrels = NULL; + dpns->ret_old_alias = query->returningOldAlias; + dpns->ret_new_alias = query->returningNewAlias; /* Assign a unique relation alias to each RTE */ set_rtable_names(dpns, parent_namespaces, NULL); @@ -4415,8 +4431,8 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, if (rte->rtekind == RTE_FUNCTION && rte->functions != NIL) { /* Since we're not creating Vars, rtindex etc. don't matter */ - expandRTE(rte, 1, 0, -1, true /* include dropped */ , - &colnames, NULL); + expandRTE(rte, 1, 0, VAR_RETURNING_DEFAULT, -1, + true /* include dropped */ , &colnames, NULL); } else colnames = rte->eref->colnames; @@ -6343,6 +6359,45 @@ get_target_list(List *targetList, deparse_context *context) } static void +get_returning_clause(Query *query, deparse_context *context) +{ + StringInfo buf = context->buf; + + if (query->returningList) + { + bool have_with = false; + + appendContextKeyword(context, " RETURNING", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); + + /* Add WITH (OLD/NEW) options, if they're not the defaults */ + if (query->returningOldAlias && strcmp(query->returningOldAlias, "old") != 0) + { + appendStringInfo(buf, " WITH (OLD AS %s", + quote_identifier(query->returningOldAlias)); + have_with = true; + } + if (query->returningNewAlias && strcmp(query->returningNewAlias, "new") != 0) + { + if (have_with) + appendStringInfo(buf, ", NEW AS %s", + quote_identifier(query->returningNewAlias)); + else + { + appendStringInfo(buf, " WITH (NEW AS %s", + quote_identifier(query->returningNewAlias)); + have_with = true; + } + } + if (have_with) + appendStringInfoChar(buf, ')'); + + /* Add the returning expressions themselves */ + get_target_list(query->returningList, context); + } +} + +static void get_setop_query(Node *setOp, Query *query, deparse_context *context) { StringInfo buf = context->buf; @@ -7022,11 +7077,7 @@ get_insert_query_def(Query *query, deparse_context *context) /* Add RETURNING if present */ if (query->returningList) - { - appendContextKeyword(context, " RETURNING", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); - get_target_list(query->returningList, context); - } + get_returning_clause(query, context); } @@ -7078,11 +7129,7 @@ get_update_query_def(Query *query, deparse_context *context) /* Add RETURNING if present */ if (query->returningList) - { - appendContextKeyword(context, " RETURNING", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); - get_target_list(query->returningList, context); - } + get_returning_clause(query, context); } @@ -7281,11 +7328,7 @@ get_delete_query_def(Query *query, deparse_context *context) /* Add RETURNING if present */ if (query->returningList) - { - appendContextKeyword(context, " RETURNING", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); - get_target_list(query->returningList, context); - } + get_returning_clause(query, context); } @@ -7444,11 +7487,7 @@ get_merge_query_def(Query *query, deparse_context *context) /* Add RETURNING if present */ if (query->returningList) - { - appendContextKeyword(context, " RETURNING", - -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); - get_target_list(query->returningList, context); - } + get_returning_clause(query, context); } @@ -7596,7 +7635,15 @@ get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context) } rte = rt_fetch(varno, dpns->rtable); - refname = (char *) list_nth(dpns->rtable_names, varno - 1); + + /* might be returning old/new column value */ + if (var->varreturningtype == VAR_RETURNING_OLD) + refname = dpns->ret_old_alias; + else if (var->varreturningtype == VAR_RETURNING_NEW) + refname = dpns->ret_new_alias; + else + refname = (char *) list_nth(dpns->rtable_names, varno - 1); + colinfo = deparse_columns_fetch(varno, dpns); attnum = varattno; } @@ -7710,7 +7757,8 @@ get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context) attname = get_rte_attribute_name(rte, attnum); } - need_prefix = (context->varprefix || attname == NULL); + need_prefix = (context->varprefix || attname == NULL || + var->varreturningtype != VAR_RETURNING_DEFAULT); /* * If we're considering a plain Var in an ORDER BY (but not GROUP BY) @@ -8807,6 +8855,9 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags) case T_ConvertRowtypeExpr: return isSimpleNode((Node *) ((ConvertRowtypeExpr *) node)->arg, node, prettyFlags); + case T_ReturningExpr: + return isSimpleNode((Node *) ((ReturningExpr *) node)->retexpr, + node, prettyFlags); case T_OpExpr: { @@ -10292,6 +10343,20 @@ get_rule_expr(Node *node, deparse_context *context, } break; + case T_ReturningExpr: + { + ReturningExpr *retExpr = (ReturningExpr *) node; + + /* + * We cannot see a ReturningExpr in rule deparsing, only while + * EXPLAINing a query plan (ReturningExpr nodes are only ever + * adding during query rewriting). Just display the expression + * returned (an expanded view column). + */ + get_rule_expr((Node *) retExpr->retexpr, context, showimplicit); + } + break; + case T_PartitionBoundSpec: { PartitionBoundSpec *spec = (PartitionBoundSpec *) node; diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 7c7133cd885..e5446845614 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202501401 +#define CATALOG_VERSION_NO 202501161 #endif diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index 1e42c131781..5371e344ecd 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -26,9 +26,9 @@ struct JsonConstructorExprState; /* Bits in ExprState->flags (see also execnodes.h for public flag bits): */ /* expression's interpreter has been initialized */ -#define EEO_FLAG_INTERPRETER_INITIALIZED (1 << 1) +#define EEO_FLAG_INTERPRETER_INITIALIZED (1 << 5) /* jump-threading is in use */ -#define EEO_FLAG_DIRECT_THREADED (1 << 2) +#define EEO_FLAG_DIRECT_THREADED (1 << 6) /* Typical API for out-of-line evaluation subroutines */ typedef void (*ExecEvalSubroutine) (ExprState *state, @@ -72,16 +72,22 @@ typedef enum ExprEvalOp EEOP_INNER_FETCHSOME, EEOP_OUTER_FETCHSOME, EEOP_SCAN_FETCHSOME, + EEOP_OLD_FETCHSOME, + EEOP_NEW_FETCHSOME, /* compute non-system Var value */ EEOP_INNER_VAR, EEOP_OUTER_VAR, EEOP_SCAN_VAR, + EEOP_OLD_VAR, + EEOP_NEW_VAR, /* compute system Var value */ EEOP_INNER_SYSVAR, EEOP_OUTER_SYSVAR, EEOP_SCAN_SYSVAR, + EEOP_OLD_SYSVAR, + EEOP_NEW_SYSVAR, /* compute wholerow Var */ EEOP_WHOLEROW, @@ -94,6 +100,8 @@ typedef enum ExprEvalOp EEOP_ASSIGN_INNER_VAR, EEOP_ASSIGN_OUTER_VAR, EEOP_ASSIGN_SCAN_VAR, + EEOP_ASSIGN_OLD_VAR, + EEOP_ASSIGN_NEW_VAR, /* assign ExprState's resvalue/resnull to a column of its resultslot */ EEOP_ASSIGN_TMP, @@ -178,6 +186,7 @@ typedef enum ExprEvalOp EEOP_SQLVALUEFUNCTION, EEOP_CURRENTOFEXPR, EEOP_NEXTVALUEEXPR, + EEOP_RETURNINGEXPR, EEOP_ARRAYEXPR, EEOP_ARRAYCOERCE, EEOP_ROW, @@ -301,7 +310,7 @@ typedef struct ExprEvalStep */ union { - /* for EEOP_INNER/OUTER/SCAN_FETCHSOME */ + /* for EEOP_INNER/OUTER/SCAN/OLD/NEW_FETCHSOME */ struct { /* attribute number up to which to fetch (inclusive) */ @@ -314,13 +323,14 @@ typedef struct ExprEvalStep const TupleTableSlotOps *kind; } fetch; - /* for EEOP_INNER/OUTER/SCAN_[SYS]VAR[_FIRST] */ + /* for EEOP_INNER/OUTER/SCAN/OLD/NEW_[SYS]VAR */ struct { /* attnum is attr number - 1 for regular VAR ... */ /* but it's just the normal (negative) attr number for SYSVAR */ int attnum; Oid vartype; /* type OID of variable */ + VarReturningType varreturningtype; /* return old/new/default */ } var; /* for EEOP_WHOLEROW */ @@ -349,6 +359,13 @@ typedef struct ExprEvalStep int resultnum; } assign_tmp; + /* for EEOP_RETURNINGEXPR */ + struct + { + uint8 nullflag; /* flag to test if OLD/NEW row is NULL */ + int jumpdone; /* jump here if OLD/NEW row is NULL */ + } returningexpr; + /* for EEOP_CONST */ struct { diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index f8a8d03e533..c7db6defd3e 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -629,6 +629,7 @@ extern int ExecCleanTargetListLength(List *targetlist); extern TupleTableSlot *ExecGetTriggerOldSlot(EState *estate, ResultRelInfo *relInfo); extern TupleTableSlot *ExecGetTriggerNewSlot(EState *estate, ResultRelInfo *relInfo); extern TupleTableSlot *ExecGetReturningSlot(EState *estate, ResultRelInfo *relInfo); +extern TupleTableSlot *ExecGetAllNullSlot(EState *estate, ResultRelInfo *relInfo); extern TupleConversionMap *ExecGetChildToRootMap(ResultRelInfo *resultRelInfo); extern TupleConversionMap *ExecGetRootToChildMap(ResultRelInfo *resultRelInfo, EState *estate); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index b3f7aa299f5..d0f2dca5928 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -74,11 +74,20 @@ typedef Datum (*ExprStateEvalFunc) (struct ExprState *expression, /* Bits in ExprState->flags (see also execExpr.h for private flag bits): */ /* expression is for use with ExecQual() */ #define EEO_FLAG_IS_QUAL (1 << 0) +/* expression refers to OLD table columns */ +#define EEO_FLAG_HAS_OLD (1 << 1) +/* expression refers to NEW table columns */ +#define EEO_FLAG_HAS_NEW (1 << 2) +/* OLD table row is NULL in RETURNING list */ +#define EEO_FLAG_OLD_IS_NULL (1 << 3) +/* NEW table row is NULL in RETURNING list */ +#define EEO_FLAG_NEW_IS_NULL (1 << 4) typedef struct ExprState { NodeTag type; +#define FIELDNO_EXPRSTATE_FLAGS 1 uint8 flags; /* bitmask of EEO_FLAG_* bits, see above */ /* @@ -290,6 +299,12 @@ typedef struct ExprContext #define FIELDNO_EXPRCONTEXT_DOMAINNULL 13 bool domainValue_isNull; + /* Tuples that OLD/NEW Var nodes in RETURNING may refer to */ +#define FIELDNO_EXPRCONTEXT_OLDTUPLE 14 + TupleTableSlot *ecxt_oldtuple; +#define FIELDNO_EXPRCONTEXT_NEWTUPLE 15 + TupleTableSlot *ecxt_newtuple; + /* Link to containing EState (NULL if a standalone ExprContext) */ struct EState *ecxt_estate; @@ -504,6 +519,7 @@ typedef struct ResultRelInfo TupleTableSlot *ri_ReturningSlot; /* for trigger output tuples */ TupleTableSlot *ri_TrigOldSlot; /* for a trigger's old tuple */ TupleTableSlot *ri_TrigNewSlot; /* for a trigger's new tuple */ + TupleTableSlot *ri_AllNullSlot; /* for RETURNING OLD/NEW */ /* FDW callback functions, if foreign table */ struct FdwRoutine *ri_FdwRoutine; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b191eaaecab..ffe155ee20e 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -197,6 +197,15 @@ typedef struct Query OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */ + /* + * The following three fields describe the contents of the RETURNING list + * for INSERT/UPDATE/DELETE/MERGE. returningOldAlias and returningNewAlias + * are the alias names for OLD and NEW, which may be user-supplied values, + * the defaults "old" and "new", or NULL (if the default "old"/"new" is + * already in use as the alias for some other relation). + */ + char *returningOldAlias pg_node_attr(query_jumble_ignore); + char *returningNewAlias pg_node_attr(query_jumble_ignore); List *returningList; /* return-values list (of TargetEntry) */ List *groupClause; /* a list of SortGroupClause's */ @@ -1727,6 +1736,41 @@ typedef struct MergeWhenClause } MergeWhenClause; /* + * ReturningOptionKind - + * Possible kinds of option in RETURNING WITH(...) list + * + * Currently, this is used only for specifying OLD/NEW aliases. + */ +typedef enum ReturningOptionKind +{ + RETURNING_OPTION_OLD, /* specify alias for OLD in RETURNING */ + RETURNING_OPTION_NEW, /* specify alias for NEW in RETURNING */ +} ReturningOptionKind; + +/* + * ReturningOption - + * An individual option in the RETURNING WITH(...) list + */ +typedef struct ReturningOption +{ + NodeTag type; + ReturningOptionKind option; /* specified option */ + char *value; /* option's value */ + ParseLoc location; /* token location, or -1 if unknown */ +} ReturningOption; + +/* + * ReturningClause - + * List of RETURNING expressions, together with any WITH(...) options + */ +typedef struct ReturningClause +{ + NodeTag type; + List *options; /* list of ReturningOption elements */ + List *exprs; /* list of expressions to return */ +} ReturningClause; + +/* * TriggerTransition - * representation of transition row or table naming clause * @@ -2043,7 +2087,7 @@ typedef struct InsertStmt List *cols; /* optional: names of the target columns */ Node *selectStmt; /* the source SELECT/VALUES, or NULL */ OnConflictClause *onConflictClause; /* ON CONFLICT clause */ - List *returningList; /* list of expressions to return */ + ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ OverridingKind override; /* OVERRIDING clause */ ParseLoc stmt_location; /* start location, or -1 if unknown */ @@ -2060,7 +2104,7 @@ typedef struct DeleteStmt RangeVar *relation; /* relation to delete from */ List *usingClause; /* optional using clause for more tables */ Node *whereClause; /* qualifications */ - List *returningList; /* list of expressions to return */ + ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ ParseLoc stmt_location; /* start location, or -1 if unknown */ ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ @@ -2077,7 +2121,7 @@ typedef struct UpdateStmt List *targetList; /* the target list (of ResTarget) */ Node *whereClause; /* qualifications */ List *fromClause; /* optional from clause for more tables */ - List *returningList; /* list of expressions to return */ + ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ ParseLoc stmt_location; /* start location, or -1 if unknown */ ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ @@ -2094,7 +2138,7 @@ 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 */ + ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ ParseLoc stmt_location; /* start location, or -1 if unknown */ ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index ef9ea7ee982..9e19cdd284d 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -238,6 +238,8 @@ typedef struct ModifyTable List *resultRelations; /* integer list of RT indexes */ List *updateColnosLists; /* per-target-table update_colnos lists */ List *withCheckOptionLists; /* per-target-table WCO lists */ + char *returningOldAlias; /* alias for OLD in RETURNING lists */ + char *returningNewAlias; /* alias for NEW in RETURNING lists */ List *returningLists; /* per-target-table RETURNING tlists */ List *fdwPrivLists; /* per-target-table FDW private data lists */ Bitmapset *fdwDirectModifyPlans; /* indices of FDW DM plans */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index cb09df9e745..59e7bb26bbd 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -223,6 +223,11 @@ typedef struct Expr * Note that it affects the meaning of all of varno, varnullingrels, and * varnosyn, all of which refer to the range table of that query level. * + * varreturningtype is used for Vars that refer to the target relation in the + * RETURNING list of data-modifying queries. The default behavior is to + * return old values for DELETE and new values for INSERT and UPDATE, but it + * is also possible to explicitly request old or new values. + * * In the parser, varnosyn and varattnosyn are either identical to * varno/varattno, or they specify the column's position in an aliased JOIN * RTE that hides the semantic referent RTE's refname. This is a syntactic @@ -244,6 +249,14 @@ typedef struct Expr #define PRS2_OLD_VARNO 1 #define PRS2_NEW_VARNO 2 +/* Returning behavior for Vars in RETURNING list */ +typedef enum VarReturningType +{ + VAR_RETURNING_DEFAULT, /* return OLD for DELETE, else return NEW */ + VAR_RETURNING_OLD, /* return OLD for DELETE/UPDATE, else NULL */ + VAR_RETURNING_NEW, /* return NEW for INSERT/UPDATE, else NULL */ +} VarReturningType; + typedef struct Var { Expr xpr; @@ -279,6 +292,9 @@ typedef struct Var */ Index varlevelsup; + /* returning type of this var (see above) */ + VarReturningType varreturningtype; + /* * varnosyn/varattnosyn are ignored for equality, because Vars with * different syntactic identifiers are semantically the same as long as @@ -2143,6 +2159,30 @@ typedef struct InferenceElem Oid inferopclass; /* OID of att opclass, or InvalidOid */ } InferenceElem; +/* + * ReturningExpr - return OLD/NEW.(expression) in RETURNING list + * + * This is used when updating an auto-updatable view and returning a view + * column that is not simply a Var referring to the base relation. In such + * cases, OLD/NEW.viewcol can expand to an arbitrary expression, but the + * result is required to be NULL if the OLD/NEW row doesn't exist. To handle + * this, the rewriter wraps the expanded expression in a ReturningExpr, which + * is equivalent to "CASE WHEN (OLD/NEW row exists) THEN (expr) ELSE NULL". + * + * A similar situation can arise when rewriting the RETURNING clause of a + * rule, which may also contain arbitrary expressions. + * + * ReturningExpr nodes never appear in a parsed Query --- they are only ever + * inserted by the rewriter. + */ +typedef struct ReturningExpr +{ + Expr xpr; + int retlevelsup; /* > 0 if it belongs to outer query */ + bool retold; /* true for OLD, false for NEW */ + Expr *retexpr; /* expression to be returned */ +} ReturningExpr; + /*-------------------- * TargetEntry - * a target entry (used in query target lists) diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h index 734c82a27db..bcf8ed645c2 100644 --- a/src/include/optimizer/optimizer.h +++ b/src/include/optimizer/optimizer.h @@ -199,6 +199,7 @@ extern void pull_varattnos(Node *node, Index varno, Bitmapset **varattnos); extern List *pull_vars_of_level(Node *node, int levelsup); extern bool contain_var_clause(Node *node); extern bool contain_vars_of_level(Node *node, int levelsup); +extern bool contain_vars_returning_old_or_new(Node *node); extern int locate_var_of_level(Node *node, int levelsup); extern List *pull_var_clause(Node *node, int flags); extern Node *flatten_join_alias_vars(PlannerInfo *root, Query *query, Node *node); diff --git a/src/include/optimizer/paramassign.h b/src/include/optimizer/paramassign.h index 15321ebabb8..59dcb1ff053 100644 --- a/src/include/optimizer/paramassign.h +++ b/src/include/optimizer/paramassign.h @@ -22,6 +22,8 @@ extern Param *replace_outer_agg(PlannerInfo *root, Aggref *agg); extern Param *replace_outer_grouping(PlannerInfo *root, GroupingFunc *grp); extern Param *replace_outer_merge_support(PlannerInfo *root, MergeSupportFunc *msf); +extern Param *replace_outer_returning(PlannerInfo *root, + ReturningExpr *rexpr); extern Param *replace_nestloop_param_var(PlannerInfo *root, Var *var); extern Param *replace_nestloop_param_placeholdervar(PlannerInfo *root, PlaceHolderVar *phv); diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index ac490912648..f1bd18c49f2 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -44,8 +44,9 @@ extern List *transformInsertRow(ParseState *pstate, List *exprlist, bool strip_indirection); extern List *transformUpdateTargetList(ParseState *pstate, List *origTlist); -extern List *transformReturningList(ParseState *pstate, List *returningList, - ParseExprKind exprKind); +extern void transformReturningClause(ParseState *pstate, Query *qry, + ReturningClause *returningClause, + ParseExprKind exprKind); extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree); extern Query *transformStmt(ParseState *pstate, Node *parseTree); diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 0de44d166f4..994284019fb 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -295,6 +295,11 @@ struct ParseState * of SQL:2008 requires us to do it this way. We also use p_lateral_ok to * forbid LATERAL references to an UPDATE/DELETE target table. * + * While processing the RETURNING clause, special namespace items are added to + * refer to the OLD and NEW state of the result relation. These namespace + * items have p_returning_type set appropriately, for use when creating Vars. + * For convenience, this information is duplicated on each namespace column. + * * At no time should a namespace list contain two entries that conflict * according to the rules in checkNameSpaceConflicts; but note that those * are more complicated than "must have different alias names", so in practice @@ -312,6 +317,7 @@ struct ParseNamespaceItem bool p_cols_visible; /* Column names visible as unqualified refs? */ bool p_lateral_only; /* Is only visible to LATERAL expressions? */ bool p_lateral_ok; /* If so, does join type allow use? */ + VarReturningType p_returning_type; /* Is OLD/NEW for use in RETURNING? */ }; /* @@ -342,6 +348,7 @@ struct ParseNamespaceColumn Oid p_vartype; /* pg_type OID */ int32 p_vartypmod; /* type modifier value */ Oid p_varcollid; /* OID of collation, or InvalidOid */ + VarReturningType p_varreturningtype; /* for RETURNING OLD/NEW */ Index p_varnosyn; /* rangetable index of syntactic referent */ AttrNumber p_varattnosyn; /* attribute number of syntactic referent */ bool p_dontexpand; /* not included in star expansion */ diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h index df6fd5550d7..3ece5cd4eef 100644 --- a/src/include/parser/parse_relation.h +++ b/src/include/parser/parse_relation.h @@ -114,6 +114,7 @@ extern void errorMissingRTE(ParseState *pstate, RangeVar *relation) pg_attribute extern void errorMissingColumn(ParseState *pstate, const char *relname, const char *colname, int location) pg_attribute_noreturn(); extern void expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, + VarReturningType returning_type, int location, bool include_dropped, List **colnames, List **colvars); extern List *expandNSItemVars(ParseState *pstate, ParseNamespaceItem *nsitem, diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h index 1070b93a9d3..512823033b9 100644 --- a/src/include/rewrite/rewriteManip.h +++ b/src/include/rewrite/rewriteManip.h @@ -89,6 +89,7 @@ extern Node *ReplaceVarsFromTargetList(Node *node, int target_varno, int sublevels_up, RangeTblEntry *target_rte, List *targetlist, + int result_relation, ReplaceVarsNoMatchOption nomatch_option, int nomatch_varno, bool *outer_hasSubLinks); diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl index ad9aec63cb7..f22ca213c21 100644 --- a/src/interfaces/ecpg/preproc/parse.pl +++ b/src/interfaces/ecpg/preproc/parse.pl @@ -105,8 +105,8 @@ my %replace_line = ( 'SHOW TRANSACTION ISOLATION LEVEL ecpg_into', 'VariableShowStmt SHOW SESSION AUTHORIZATION' => 'SHOW SESSION AUTHORIZATION ecpg_into', - 'returning_clause RETURNING target_list' => - 'RETURNING target_list opt_ecpg_into', + 'returning_clause RETURNING returning_with_clause target_list' => + 'RETURNING returning_with_clause target_list opt_ecpg_into', 'ExecuteStmt EXECUTE name execute_param_clause' => 'EXECUTE prepared_name execute_param_clause execute_rest', 'ExecuteStmt CREATE OptTemp TABLE create_as_target AS EXECUTE name execute_param_clause opt_with_data' diff --git a/src/test/isolation/expected/merge-update.out b/src/test/isolation/expected/merge-update.out index 3063c0c6ab1..677263d1ec1 100644 --- a/src/test/isolation/expected/merge-update.out +++ b/src/test/isolation/expected/merge-update.out @@ -40,12 +40,12 @@ step merge2a: UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; -merge_action|key|val -------------+---+------------------------------------------------------ -UPDATE | 3|setup1 updated by merge1 source not matched by merge2a -INSERT | 1|merge2a +merge_action|old |new |key|val +------------+------------------------------+------------------------------------------------------------+---+------------------------------------------------------ +UPDATE |(2,"setup1 updated by merge1")|(3,"setup1 updated by merge1 source not matched by merge2a")| 3|setup1 updated by merge1 source not matched by merge2a +INSERT | |(1,merge2a) | 1|merge2a (2 rows) step select2: SELECT * FROM target; @@ -98,14 +98,14 @@ step merge2a: UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; <waiting ...> step c1: COMMIT; step merge2a: <... completed> -merge_action|key|val -------------+---+------------------------------------------------------ -UPDATE | 3|setup1 updated by merge1 source not matched by merge2a -INSERT | 1|merge2a +merge_action|old |new |key|val +------------+------------------------------+------------------------------------------------------------+---+------------------------------------------------------ +UPDATE |(2,"setup1 updated by merge1")|(3,"setup1 updated by merge1 source not matched by merge2a")| 3|setup1 updated by merge1 source not matched by merge2a +INSERT | |(1,merge2a) | 1|merge2a (2 rows) step select2: SELECT * FROM target; @@ -137,13 +137,13 @@ step merge2a: UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; <waiting ...> step a1: ABORT; step merge2a: <... completed> -merge_action|key|val -------------+---+------------------------- -UPDATE | 2|setup1 updated by merge2a +merge_action|old |new |key|val +------------+----------+-------------------------------+---+------------------------- +UPDATE |(1,setup1)|(2,"setup1 updated by merge2a")| 2|setup1 updated by merge2a (1 row) step select2: SELECT * FROM target; @@ -234,14 +234,14 @@ step pa_merge2a: UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; <waiting ...> step c1: COMMIT; step pa_merge2a: <... completed> -merge_action|key|val -------------+---+-------------------------------------------------- -UPDATE | 2|initial updated by pa_merge1 updated by pa_merge2a -UPDATE | 3|initial source not matched by pa_merge2a +merge_action|old |new |key|val +------------+----------------------------------+--------------------------------------------------------+---+-------------------------------------------------- +UPDATE |(1,"initial updated by pa_merge1")|(2,"initial updated by pa_merge1 updated by pa_merge2a")| 2|initial updated by pa_merge1 updated by pa_merge2a +UPDATE |(2,initial) |(3,"initial source not matched by pa_merge2a") | 3|initial source not matched by pa_merge2a (2 rows) step pa_select2: SELECT * FROM pa_target; @@ -273,7 +273,7 @@ step pa_merge2a: UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; <waiting ...> step c1: COMMIT; step pa_merge2a: <... completed> @@ -303,13 +303,13 @@ step pa_merge2a: UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; -merge_action|key|val -------------+---+------------------------------------------------------------- -UPDATE | 3|initial source not matched by pa_merge2a -UPDATE | 3|initial updated by pa_merge2 source not matched by pa_merge2a -INSERT | 1|pa_merge2a +merge_action|old |new |key|val +------------+----------------------------------+-------------------------------------------------------------------+---+------------------------------------------------------------- +UPDATE |(2,initial) |(3,"initial source not matched by pa_merge2a") | 3|initial source not matched by pa_merge2a +UPDATE |(2,"initial updated by pa_merge2")|(3,"initial updated by pa_merge2 source not matched by pa_merge2a")| 3|initial updated by pa_merge2 source not matched by pa_merge2a +INSERT | |(1,pa_merge2a) | 1|pa_merge2a (3 rows) step pa_select2: SELECT * FROM pa_target; diff --git a/src/test/isolation/specs/merge-update.spec b/src/test/isolation/specs/merge-update.spec index a33dcdba537..c718ff646b2 100644 --- a/src/test/isolation/specs/merge-update.spec +++ b/src/test/isolation/specs/merge-update.spec @@ -95,7 +95,7 @@ step "merge2a" UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; } step "merge2b" { @@ -128,7 +128,7 @@ step "pa_merge2a" UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val WHEN NOT MATCHED BY SOURCE THEN UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; } # MERGE proceeds only if 'val' unchanged step "pa_merge2b_when" diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 28d85510636..05314ad4397 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -297,13 +297,13 @@ WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (s.sid, s.delta) -RETURNING merge_action(), t.*; - merge_action | tid | balance ---------------+-----+--------- - DELETE | 1 | 10 - DELETE | 2 | 20 - DELETE | 3 | 30 - INSERT | 4 | 40 +RETURNING merge_action(), old, new, t.*; + merge_action | old | new | tid | balance +--------------+--------+--------+-----+--------- + DELETE | (1,10) | | 1 | 10 + DELETE | (2,20) | | 2 | 20 + DELETE | (3,30) | | 3 | 30 + INSERT | | (4,40) | 4 | 40 (4 rows) SELECT * FROM target ORDER BY tid; @@ -994,7 +994,7 @@ WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN UPDATE SET balance = 0 WHEN NOT MATCHED BY SOURCE THEN DELETE -RETURNING merge_action(), t.*; +RETURNING merge_action(), old, new, t.*; NOTICE: BEFORE INSERT STATEMENT trigger NOTICE: BEFORE UPDATE STATEMENT trigger NOTICE: BEFORE DELETE STATEMENT trigger @@ -1009,12 +1009,12 @@ NOTICE: AFTER UPDATE ROW trigger row: (1,10) -> (1,0) NOTICE: AFTER DELETE STATEMENT trigger NOTICE: AFTER UPDATE STATEMENT trigger NOTICE: AFTER INSERT STATEMENT trigger - merge_action | tid | balance ---------------+-----+--------- - UPDATE | 3 | 10 - INSERT | 4 | 40 - DELETE | 2 | 20 - UPDATE | 1 | 0 + merge_action | old | new | tid | balance +--------------+--------+--------+-----+--------- + UPDATE | (3,30) | (3,10) | 3 | 10 + INSERT | | (4,40) | 4 | 40 + DELETE | (2,20) | | 2 | 20 + UPDATE | (1,10) | (1,0) | 1 | 0 (4 rows) SELECT * FROM target ORDER BY tid; @@ -1436,17 +1436,19 @@ WHEN MATCHED AND tid < 2 THEN DELETE RETURNING (SELECT abbrev FROM merge_actions WHERE action = merge_action()) AS action, - t.*, + old.tid AS old_tid, old.balance AS old_balance, + new.tid AS new_tid, new.balance AS new_balance, + (SELECT new.balance - old.balance AS delta_balance), t.*, CASE merge_action() WHEN 'INSERT' THEN 'Inserted '||t WHEN 'UPDATE' THEN 'Added '||delta||' to balance' WHEN 'DELETE' THEN 'Removed '||t END AS description; - action | tid | balance | description ---------+-----+---------+--------------------- - del | 1 | 100 | Removed (1,100) - upd | 2 | 220 | Added 20 to balance - ins | 4 | 40 | Inserted (4,40) + action | old_tid | old_balance | new_tid | new_balance | delta_balance | tid | balance | description +--------+---------+-------------+---------+-------------+---------------+-----+---------+--------------------- + del | 1 | 100 | | | | 1 | 100 | Removed (1,100) + upd | 2 | 200 | 2 | 220 | 20 | 2 | 220 | Added 20 to balance + ins | | | 4 | 40 | | 4 | 40 | Inserted (4,40) (3 rows) ROLLBACK; @@ -1473,7 +1475,7 @@ WITH m AS ( INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN DELETE - RETURNING merge_action() AS action, t.*, + RETURNING merge_action() AS action, old AS old_data, new AS new_data, t.*, CASE merge_action() WHEN 'INSERT' THEN 'Inserted '||t WHEN 'UPDATE' THEN 'Added '||delta||' to balance' @@ -1487,14 +1489,14 @@ WITH m AS ( UPDATE SET last_change = description WHEN NOT MATCHED THEN INSERT VALUES (m.tid, description) - RETURNING action, merge_action() AS log_action, l.* + RETURNING m.*, merge_action() AS log_action, old AS old_log, new AS new_log, l.* ) SELECT * FROM m2; - action | log_action | tid | last_change ---------+------------+-----+--------------------- - DELETE | UPDATE | 1 | Removed (1,100) - UPDATE | INSERT | 2 | Added 20 to balance - INSERT | INSERT | 4 | Inserted (4,40) + action | old_data | new_data | tid | balance | description | log_action | old_log | new_log | tid | last_change +--------+----------+----------+-----+---------+---------------------+------------+----------------------+---------------------------+-----+--------------------- + DELETE | (1,100) | | 1 | 100 | Removed (1,100) | UPDATE | (1,"Original value") | (1,"Removed (1,100)") | 1 | Removed (1,100) + UPDATE | (2,200) | (2,220) | 2 | 220 | Added 20 to balance | INSERT | | (2,"Added 20 to balance") | 2 | Added 20 to balance + INSERT | | (4,40) | 4 | 40 | Inserted (4,40) | INSERT | | (4,"Inserted (4,40)") | 4 | Inserted (4,40) (3 rows) SELECT * FROM sq_target_merge_log ORDER BY tid; @@ -1518,11 +1520,11 @@ COPY ( INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN DELETE - RETURNING merge_action(), t.* + RETURNING merge_action(), old.*, new.* ) TO stdout; -DELETE 1 100 -UPDATE 2 220 -INSERT 4 40 +DELETE 1 100 \N \N +UPDATE 2 200 2 220 +INSERT \N \N 4 40 ROLLBACK; -- SQL function with MERGE ... RETURNING BEGIN; @@ -2039,10 +2041,10 @@ MERGE INTO pa_target t 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 merge_action(), t.*; - merge_action | tid | balance | val ---------------+-----+---------+-------------------------- - UPDATE | 2 | 110 | initial updated by merge + RETURNING merge_action(), old, new, t.*; + merge_action | old | new | tid | balance | val +--------------+-----------------+------------------------------------+-----+---------+-------------------------- + UPDATE | (1,100,initial) | (2,110,"initial updated by merge") | 2 | 110 | initial updated by merge (1 row) SELECT * FROM pa_target ORDER BY tid; @@ -2324,18 +2326,18 @@ MERGE INTO pa_target t UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge') - RETURNING 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 + RETURNING merge_action(), old, new, t.*; + merge_action | old | new | logts | tid | balance | val +--------------+--------------------------------------------+---------------------------------------------------------------+--------------------------+-----+---------+-------------------------- + UPDATE | ("Tue Jan 31 00:00:00 2017",1,100,initial) | ("Tue Jan 31 00:00:00 2017",1,110,"initial updated by merge") | Tue Jan 31 00:00:00 2017 | 1 | 110 | initial updated by merge + UPDATE | ("Tue Feb 28 00:00:00 2017",2,200,initial) | ("Tue Feb 28 00:00:00 2017",2,220,"initial updated by merge") | 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") | Sun Jan 15 00:00:00 2017 | 3 | 30 | inserted by merge + UPDATE | ("Tue Jan 31 00:00:00 2017",4,400,initial) | ("Tue Jan 31 00:00:00 2017",4,440,"initial updated by merge") | Tue Jan 31 00:00:00 2017 | 4 | 440 | initial updated by merge + UPDATE | ("Tue Feb 28 00:00:00 2017",5,500,initial) | ("Tue Feb 28 00:00:00 2017",5,550,"initial updated by merge") | 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") | Sun Jan 15 00:00:00 2017 | 6 | 60 | inserted by merge + UPDATE | ("Tue Jan 31 00:00:00 2017",7,700,initial) | ("Tue Jan 31 00:00:00 2017",7,770,"initial updated by merge") | Tue Jan 31 00:00:00 2017 | 7 | 770 | initial updated by merge + UPDATE | ("Tue Feb 28 00:00:00 2017",8,800,initial) | ("Tue Feb 28 00:00:00 2017",8,880,"initial updated by merge") | 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") | Sun Jan 15 00:00:00 2017 | 9 | 90 | inserted by merge (9 rows) SELECT * FROM pa_target ORDER BY tid; diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out index cb51bb86876..6bd36a811fc 100644 --- a/src/test/regress/expected/returning.out +++ b/src/test/regress/expected/returning.out @@ -355,3 +355,566 @@ INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok 42 (1 row) +-- +-- Test RETURNING OLD/NEW. +-- +-- Start with new data, to ensure predictable TIDs. +-- +TRUNCATE foo; +INSERT INTO foo VALUES (1, 'xxx', 10, 20), (2, 'more', 42, 141), (3, 'zoo2', 57, 99); +-- Error cases +INSERT INTO foo DEFAULT VALUES RETURNING WITH (nonsuch AS something) *; +ERROR: syntax error at or near "nonsuch" +LINE 1: INSERT INTO foo DEFAULT VALUES RETURNING WITH (nonsuch AS so... + ^ +INSERT INTO foo DEFAULT VALUES RETURNING WITH (new AS foo) *; +ERROR: table name "foo" specified more than once +LINE 1: INSERT INTO foo DEFAULT VALUES RETURNING WITH (new AS foo) *... + ^ +INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS o, new AS n, old AS o) *; +ERROR: OLD cannot be specified multiple times +LINE 1: ...EFAULT VALUES RETURNING WITH (old AS o, new AS n, old AS o) ... + ^ +INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS o, new AS n, new AS n) *; +ERROR: NEW cannot be specified multiple times +LINE 1: ...EFAULT VALUES RETURNING WITH (old AS o, new AS n, new AS n) ... + ^ +INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS x, new AS x) *; +ERROR: table name "x" specified more than once +LINE 1: ...INTO foo DEFAULT VALUES RETURNING WITH (old AS x, new AS x) ... + ^ +-- INSERT has NEW, but not OLD +EXPLAIN (verbose, costs off) +INSERT INTO foo VALUES (4) + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Insert on pg_temp.foo + Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo.f1, foo.f2, foo.f3, foo.f4 + -> Result + Output: 4, NULL::text, 42, '99'::bigint +(4 rows) + +INSERT INTO foo VALUES (4) + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | f1 | f2 | f3 | f4 | tableoid | ctid | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4 +----------+------+----+----+----+----+----------+-------+----+----+----+----+----+----+----+---- + | | | | | | foo | (0,4) | 4 | | 42 | 99 | 4 | | 42 | 99 +(1 row) + +-- INSERT ... ON CONFLICT ... UPDATE has OLD and NEW +CREATE UNIQUE INDEX foo_f1_idx ON foo (f1); +EXPLAIN (verbose, costs off) +INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok') + ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1 + RETURNING WITH (OLD AS o, NEW AS n) + o.tableoid::regclass, o.ctid, o.*, + n.tableoid::regclass, n.ctid, n.*, *; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------- + Insert on pg_temp.foo + Output: (o.tableoid)::regclass, o.ctid, o.f1, o.f2, o.f3, o.f4, (n.tableoid)::regclass, n.ctid, n.f1, n.f2, n.f3, n.f4, foo.f1, foo.f2, foo.f3, foo.f4 + Conflict Resolution: UPDATE + Conflict Arbiter Indexes: foo_f1_idx + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1, "*VALUES*".column2, 42, '99'::bigint +(6 rows) + +INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok') + ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1 + RETURNING WITH (OLD AS o, NEW AS n) + o.tableoid::regclass, o.ctid, o.*, + n.tableoid::regclass, n.ctid, n.*, *; + tableoid | ctid | f1 | f2 | f3 | f4 | tableoid | ctid | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4 +----------+-------+----+----+----+----+----------+-------+----+------------+----+----+----+------------+----+---- + foo | (0,4) | 4 | | 42 | 99 | foo | (0,5) | 4 | conflicted | -1 | 99 | 4 | conflicted | -1 | 99 + | | | | | | foo | (0,6) | 5 | ok | 42 | 99 | 5 | ok | 42 | 99 +(2 rows) + +-- UPDATE has OLD and NEW +EXPLAIN (verbose, costs off) +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, old, + new.tableoid::regclass, new.ctid, new.*, new, + old.f4::text||'->'||new.f4::text AS change; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Update on pg_temp.foo + Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, old.*, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, new.*, (((old.f4)::text || '->'::text) || (new.f4)::text) + Update on pg_temp.foo foo_1 + -> Result + Output: '100'::bigint, foo_1.tableoid, foo_1.ctid + -> Seq Scan on pg_temp.foo foo_1 + Output: foo_1.tableoid, foo_1.ctid + Filter: (foo_1.f1 = 5) +(8 rows) + +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, old, + new.tableoid::regclass, new.ctid, new.*, new, + old.f4::text||'->'||new.f4::text AS change; + tableoid | ctid | f1 | f2 | f3 | f4 | old | tableoid | ctid | f1 | f2 | f3 | f4 | new | change +----------+-------+----+----+----+----+--------------+----------+-------+----+----+----+-----+---------------+--------- + foo | (0,6) | 5 | ok | 42 | 99 | (5,ok,42,99) | foo | (0,7) | 5 | ok | 42 | 100 | (5,ok,42,100) | 99->100 +(1 row) + +-- DELETE has OLD, but not NEW +EXPLAIN (verbose, costs off) +DELETE FROM foo WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + Delete on pg_temp.foo + Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4 + Delete on pg_temp.foo foo_1 + -> Seq Scan on pg_temp.foo foo_1 + Output: foo_1.tableoid, foo_1.ctid + Filter: (foo_1.f1 = 5) +(6 rows) + +DELETE FROM foo WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | f1 | f2 | f3 | f4 | tableoid | ctid | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4 +----------+-------+----+----+----+-----+----------+------+----+----+----+----+----+----+----+----- + foo | (0,7) | 5 | ok | 42 | 100 | | | | | | | 5 | ok | 42 | 100 +(1 row) + +-- RETURNING OLD and NEW from subquery +EXPLAIN (verbose, costs off) +INSERT INTO foo VALUES (5, 'subquery test') + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + QUERY PLAN +--------------------------------------------------------------- + Insert on pg_temp.foo + Output: (SubPlan 1), (SubPlan 2) + -> Result + Output: 5, 'subquery test'::text, 42, '99'::bigint + SubPlan 1 + -> Aggregate + Output: max((old.f4 + x.x)) + -> Function Scan on pg_catalog.generate_series x + Output: x.x + Function Call: generate_series(1, 10) + SubPlan 2 + -> Aggregate + Output: max((new.f4 + x_1.x)) + -> Function Scan on pg_catalog.generate_series x_1 + Output: x_1.x + Function Call: generate_series(1, 10) +(16 rows) + +INSERT INTO foo VALUES (5, 'subquery test') + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + old_max | new_max +---------+--------- + | 109 +(1 row) + +EXPLAIN (verbose, costs off) +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING (SELECT old.f4 = new.f4), + (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + QUERY PLAN +--------------------------------------------------------------- + Update on pg_temp.foo + Output: (SubPlan 1), (SubPlan 2), (SubPlan 3) + Update on pg_temp.foo foo_1 + -> Result + Output: '100'::bigint, foo_1.tableoid, foo_1.ctid + -> Seq Scan on pg_temp.foo foo_1 + Output: foo_1.tableoid, foo_1.ctid + Filter: (foo_1.f1 = 5) + SubPlan 1 + -> Result + Output: (old.f4 = new.f4) + SubPlan 2 + -> Aggregate + Output: max((old.f4 + x.x)) + -> Function Scan on pg_catalog.generate_series x + Output: x.x + Function Call: generate_series(1, 10) + SubPlan 3 + -> Aggregate + Output: max((new.f4 + x_1.x)) + -> Function Scan on pg_catalog.generate_series x_1 + Output: x_1.x + Function Call: generate_series(1, 10) +(23 rows) + +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING (SELECT old.f4 = new.f4), + (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + ?column? | old_max | new_max +----------+---------+--------- + f | 109 | 110 +(1 row) + +EXPLAIN (verbose, costs off) +DELETE FROM foo WHERE f1 = 5 + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + QUERY PLAN +--------------------------------------------------------------- + Delete on pg_temp.foo + Output: (SubPlan 1), (SubPlan 2) + Delete on pg_temp.foo foo_1 + -> Seq Scan on pg_temp.foo foo_1 + Output: foo_1.tableoid, foo_1.ctid + Filter: (foo_1.f1 = 5) + SubPlan 1 + -> Aggregate + Output: max((old.f4 + x.x)) + -> Function Scan on pg_catalog.generate_series x + Output: x.x + Function Call: generate_series(1, 10) + SubPlan 2 + -> Aggregate + Output: max((new.f4 + x_1.x)) + -> Function Scan on pg_catalog.generate_series x_1 + Output: x_1.x + Function Call: generate_series(1, 10) +(18 rows) + +DELETE FROM foo WHERE f1 = 5 + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + old_max | new_max +---------+--------- + 110 | +(1 row) + +-- DELETE turned into UPDATE by a rule has OLD and NEW +CREATE RULE foo_del_rule AS ON DELETE TO foo DO INSTEAD + UPDATE foo SET f2 = f2||' (deleted)', f3 = -1, f4 = -1 WHERE f1 = OLD.f1 + RETURNING *; +EXPLAIN (verbose, costs off) +DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- + Update on pg_temp.foo + Output: old.f1, old.f2, old.f3, old.f4, new.f1, new.f2, new.f3, new.f4, foo_2.f1, foo_2.f2, foo_2.f3, foo_2.f4 + Update on pg_temp.foo foo_2 + -> Nested Loop + Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.tableoid, foo_2.ctid + -> Seq Scan on pg_temp.foo foo_2 + Output: foo_2.f2, foo_2.f1, foo_2.tableoid, foo_2.ctid + Filter: (foo_2.f1 = 4) + -> Seq Scan on pg_temp.foo foo_1 + Output: foo_1.ctid, foo_1.f1, foo_1.tableoid + Filter: (foo_1.f1 = 4) +(11 rows) + +DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *; + f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4 | f1 | f2 | f3 | f4 +----+------------+----+----+----+----------------------+----+----+----+----------------------+----+---- + 4 | conflicted | -1 | 99 | 4 | conflicted (deleted) | -1 | -1 | 4 | conflicted (deleted) | -1 | -1 +(1 row) + +-- UPDATE on view with rule +EXPLAIN (verbose, costs off) +UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Update on pg_temp.foo + Output: old.f1, old.f2, old.f3, old.f4, joinme.other, new.f1, new.f2, new.f3, new.f4, joinme.other, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4, joinme.other, (new.f3 - old.f3) + Update on pg_temp.foo foo_1 + -> Hash Join + Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.tableoid, foo_1.ctid, foo_2.tableoid + Hash Cond: (foo_1.f2 = joinme.f2j) + -> Hash Join + Output: foo_1.f2, foo_1.tableoid, foo_1.ctid, joinme_1.ctid, joinme_1.f2j + Hash Cond: (joinme_1.f2j = foo_1.f2) + -> Seq Scan on pg_temp.joinme joinme_1 + Output: joinme_1.ctid, joinme_1.f2j + -> Hash + Output: foo_1.f2, foo_1.tableoid, foo_1.ctid + -> Seq Scan on pg_temp.foo foo_1 + Output: foo_1.f2, foo_1.tableoid, foo_1.ctid + -> Hash + Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid + -> Hash Join + Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid + Hash Cond: (joinme.f2j = foo_2.f2) + -> Seq Scan on pg_temp.joinme + Output: joinme.ctid, joinme.other, joinme.f2j + -> Hash + Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid + -> Seq Scan on pg_temp.foo foo_2 + Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid + Filter: (foo_2.f3 = 57) +(27 rows) + +UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; + f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | delta_f3 +----+------+----+----+-------+----+------+----+----+-------+----+------+----+----+-------+---------- + 3 | zoo2 | 57 | 99 | 54321 | 3 | zoo2 | 58 | 99 | 54321 | 3 | zoo2 | 58 | 99 | 54321 | 1 +(1 row) + +-- UPDATE on view with INSTEAD OF trigger +CREATE FUNCTION joinview_upd_trig_fn() RETURNS trigger +LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE 'UPDATE: % -> %', old, new; + UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10 + FROM joinme WHERE f2 = f2j AND f2 = old.f2 + RETURNING new.f1, new.f4 INTO new.f1, new.f4; -- should fail + RETURN NEW; +END; +$$; +CREATE TRIGGER joinview_upd_trig INSTEAD OF UPDATE ON joinview + FOR EACH ROW EXECUTE FUNCTION joinview_upd_trig_fn(); +DROP RULE joinview_u ON joinview; +UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should fail +NOTICE: UPDATE: (3,zoo2,58,99,54321) -> (3,zoo2,59,7,54321) +ERROR: column reference "new.f1" is ambiguous +LINE 3: RETURNING new.f1, new.f4 + ^ +DETAIL: It could refer to either a PL/pgSQL variable or a table column. +QUERY: UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10 + FROM joinme WHERE f2 = f2j AND f2 = old.f2 + RETURNING new.f1, new.f4 +CONTEXT: PL/pgSQL function joinview_upd_trig_fn() line 4 at SQL statement +CREATE OR REPLACE FUNCTION joinview_upd_trig_fn() RETURNS trigger +LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE 'UPDATE: % -> %', old, new; + UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10 + FROM joinme WHERE f2 = f2j AND f2 = old.f2 + RETURNING WITH (new AS n) new.f1, n.f4 INTO new.f1, new.f4; -- now ok + RETURN NEW; +END; +$$; +EXPLAIN (verbose, costs off) +UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Update on pg_temp.joinview + Output: old.f1, old.f2, old.f3, old.f4, old.other, new.f1, new.f2, new.f3, new.f4, new.other, joinview.f1, joinview.f2, joinview.f3, joinview.f4, joinview.other, (new.f3 - old.f3) + -> Hash Join + Output: (foo.f3 + 1), '7'::bigint, ROW(foo.f1, foo.f2, foo.f3, foo.f4, joinme.other), foo.ctid, joinme.ctid, foo.tableoid + Hash Cond: (joinme.f2j = foo.f2) + -> Seq Scan on pg_temp.joinme + Output: joinme.other, joinme.ctid, joinme.f2j + -> Hash + Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid + -> Seq Scan on pg_temp.foo + Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid + Filter: (foo.f3 = 58) +(12 rows) + +UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should succeed +NOTICE: UPDATE: (3,zoo2,58,99,54321) -> (3,zoo2,59,7,54321) + f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | f1 | f2 | f3 | f4 | other | delta_f3 +----+------+----+----+-------+----+------+----+----+-------+----+------+----+----+-------+---------- + 3 | zoo2 | 58 | 99 | 54321 | 3 | zoo2 | 59 | 70 | 54321 | 3 | zoo2 | 59 | 70 | 54321 | 1 +(1 row) + +-- Test wholerow & dropped column handling +ALTER TABLE foo DROP COLUMN f3 CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to rule voo_i on view voo +drop cascades to view joinview +drop cascades to rule foo_del_rule on table foo +UPDATE foo SET f4 = f4 + 1 RETURNING old.f3; -- should fail +ERROR: column old.f3 does not exist +LINE 1: UPDATE foo SET f4 = f4 + 1 RETURNING old.f3; + ^ +UPDATE foo SET f4 = f4 + 1 RETURNING old, new; + old | new +-------------------------------+------------------------------ + (1,xxx,20) | (1,xxx,21) + (2,more,141) | (2,more,142) + (4,"conflicted (deleted)",-1) | (4,"conflicted (deleted)",0) + (3,zoo2,70) | (3,zoo2,71) +(4 rows) + +-- INSERT/DELETE on zero column table +CREATE TABLE zerocol(); +INSERT INTO zerocol SELECT RETURNING old.*, new.*, *; +ERROR: RETURNING must have at least one column +LINE 1: INSERT INTO zerocol SELECT RETURNING old.*, new.*, *; + ^ +INSERT INTO zerocol SELECT + RETURNING old.tableoid::regclass, old.ctid, + new.tableoid::regclass, new.ctid, ctid, *; + tableoid | ctid | tableoid | ctid | ctid +----------+------+----------+-------+------- + | | zerocol | (0,1) | (0,1) +(1 row) + +DELETE FROM zerocol + RETURNING old.tableoid::regclass, old.ctid, + new.tableoid::regclass, new.ctid, ctid, *; + tableoid | ctid | tableoid | ctid | ctid +----------+-------+----------+------+------- + zerocol | (0,1) | | | (0,1) +(1 row) + +DROP TABLE zerocol; +-- Test cross-partition updates and attribute mapping +CREATE TABLE foo_parted (a int, b float8, c text) PARTITION BY LIST (a); +CREATE TABLE foo_part_s1 PARTITION OF foo_parted FOR VALUES IN (1); +CREATE TABLE foo_part_s2 PARTITION OF foo_parted FOR VALUES IN (2); +CREATE TABLE foo_part_d1 (c text, a int, b float8); +ALTER TABLE foo_parted ATTACH PARTITION foo_part_d1 FOR VALUES IN (3); +CREATE TABLE foo_part_d2 (b float8, c text, a int); +ALTER TABLE foo_parted ATTACH PARTITION foo_part_d2 FOR VALUES IN (4); +INSERT INTO foo_parted + VALUES (1, 17.1, 'P1'), (2, 17.2, 'P2'), (3, 17.3, 'P3'), (4, 17.4, 'P4') + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c +----------+------+---+---+---+-------------+-------+---+------+----+---+------+---- + | | | | | foo_part_s1 | (0,1) | 1 | 17.1 | P1 | 1 | 17.1 | P1 + | | | | | foo_part_s2 | (0,1) | 2 | 17.2 | P2 | 2 | 17.2 | P2 + | | | | | foo_part_d1 | (0,1) | 3 | 17.3 | P3 | 3 | 17.3 | P3 + | | | | | foo_part_d2 | (0,1) | 4 | 17.4 | P4 | 4 | 17.4 | P4 +(4 rows) + +UPDATE foo_parted SET a = 2, b = b + 1, c = c || '->P2' WHERE a = 1 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c +-------------+-------+---+------+----+-------------+-------+---+------+--------+---+------+-------- + foo_part_s1 | (0,1) | 1 | 17.1 | P1 | foo_part_s2 | (0,2) | 2 | 18.1 | P1->P2 | 2 | 18.1 | P1->P2 +(1 row) + +UPDATE foo_parted SET a = 1, b = b + 1, c = c || '->P1' WHERE a = 3 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c +-------------+-------+---+------+----+-------------+-------+---+------+--------+---+------+-------- + foo_part_d1 | (0,1) | 3 | 17.3 | P3 | foo_part_s1 | (0,2) | 1 | 18.3 | P3->P1 | 1 | 18.3 | P3->P1 +(1 row) + +UPDATE foo_parted SET a = 3, b = b + 1, c = c || '->P3' WHERE a = 1 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c +-------------+-------+---+------+--------+-------------+-------+---+------+------------+---+------+------------ + foo_part_s1 | (0,2) | 1 | 18.3 | P3->P1 | foo_part_d1 | (0,2) | 3 | 19.3 | P3->P1->P3 | 3 | 19.3 | P3->P1->P3 +(1 row) + +UPDATE foo_parted SET a = 4, b = b + 1, c = c || '->P4' WHERE a = 3 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c +-------------+-------+---+------+------------+-------------+-------+---+------+----------------+---+------+---------------- + foo_part_d1 | (0,2) | 3 | 19.3 | P3->P1->P3 | foo_part_d2 | (0,2) | 4 | 20.3 | P3->P1->P3->P4 | 4 | 20.3 | P3->P1->P3->P4 +(1 row) + +-- cross-partition update that uses ReturningExpr nodes, without returning +-- old/new table values +CREATE VIEW foo_parted_v AS SELECT *, 'xxx' AS dummy FROM foo_parted; +UPDATE foo_parted_v SET a = 1, c = c || '->P1' WHERE a = 2 AND c = 'P2' + RETURNING 'P2:'||old.dummy, 'P1:'||new.dummy; + ?column? | ?column? +----------+---------- + P2:xxx | P1:xxx +(1 row) + +DELETE FROM foo_parted + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + tableoid | ctid | a | b | c | tableoid | ctid | a | b | c | a | b | c +-------------+-------+---+------+----------------+----------+------+---+---+---+---+------+---------------- + foo_part_s1 | (0,3) | 1 | 17.2 | P2->P1 | | | | | | 1 | 17.2 | P2->P1 + foo_part_s2 | (0,2) | 2 | 18.1 | P1->P2 | | | | | | 2 | 18.1 | P1->P2 + foo_part_d2 | (0,1) | 4 | 17.4 | P4 | | | | | | 4 | 17.4 | P4 + foo_part_d2 | (0,2) | 4 | 20.3 | P3->P1->P3->P4 | | | | | | 4 | 20.3 | P3->P1->P3->P4 +(4 rows) + +DROP TABLE foo_parted CASCADE; +NOTICE: drop cascades to view foo_parted_v +-- Test deparsing +CREATE FUNCTION foo_update() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + WITH u1 AS ( + UPDATE foo SET f1 = f1 + 1 RETURNING old.*, new.* + ), u2 AS ( + UPDATE foo SET f1 = f1 + 1 RETURNING WITH (OLD AS "old foo") "old foo".*, new.* + ), u3 AS ( + UPDATE foo SET f1 = f1 + 1 RETURNING WITH (NEW AS "new foo") old.*, "new foo".* + ) + UPDATE foo SET f1 = f1 + 1 + RETURNING WITH (OLD AS o, NEW AS n) + o.*, n.*, o, n, o.f1 = n.f1, o = n, + (SELECT o.f2 = n.f2), + (SELECT count(*) FROM foo WHERE foo.f1 = o.f4), + (SELECT count(*) FROM foo WHERE foo.f4 = n.f4), + (SELECT count(*) FROM foo WHERE foo = o), + (SELECT count(*) FROM foo WHERE foo = n); +END; +\sf foo_update +CREATE OR REPLACE FUNCTION public.foo_update() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + WITH u1 AS ( + UPDATE foo foo_1 SET f1 = (foo_1.f1 + 1) + RETURNING old.f1, + old.f2, + old.f4, + new.f1, + new.f2, + new.f4 + ), u2 AS ( + UPDATE foo foo_1 SET f1 = (foo_1.f1 + 1) + RETURNING WITH (OLD AS "old foo") "old foo".f1, + "old foo".f2, + "old foo".f4, + new.f1, + new.f2, + new.f4 + ), u3 AS ( + UPDATE foo foo_1 SET f1 = (foo_1.f1 + 1) + RETURNING WITH (NEW AS "new foo") old.f1, + old.f2, + old.f4, + "new foo".f1, + "new foo".f2, + "new foo".f4 + ) + UPDATE foo SET f1 = (foo.f1 + 1) + RETURNING WITH (OLD AS o, NEW AS n) o.f1, + o.f2, + o.f4, + n.f1, + n.f2, + n.f4, + o.*::foo AS o, + n.*::foo AS n, + (o.f1 = n.f1), + (o.* = n.*), + ( SELECT (o.f2 = n.f2)), + ( SELECT count(*) AS count + FROM foo foo_1 + WHERE (foo_1.f1 = o.f4)) AS count, + ( SELECT count(*) AS count + FROM foo foo_1 + WHERE (foo_1.f4 = n.f4)) AS count, + ( SELECT count(*) AS count + FROM foo foo_1 + WHERE (foo_1.* = o.*)) AS count, + ( SELECT count(*) AS count + FROM foo foo_1 + WHERE (foo_1.* = n.*)) AS count; +END +DROP FUNCTION foo_update; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 29580c90710..ff921bbda00 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3649,7 +3649,10 @@ MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s -- test deparsing CREATE TABLE sf_target(id int, data text, filling int[]); CREATE FUNCTION merge_sf_test() - RETURNS TABLE(action text, a int, b text, id int, data text, filling int[]) + RETURNS TABLE(action text, a int, b text, + id int, data text, filling int[], + old_id int, old_data text, old_filling int[], + new_id int, new_data text, new_filling int[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -3688,11 +3691,12 @@ WHEN NOT MATCHED THEN INSERT (filling[1], id) VALUES (s.a, s.a) RETURNING - merge_action() AS action, *; + WITH (OLD AS o, NEW AS n) + merge_action() AS action, *, o.*, n.*; END; \sf merge_sf_test CREATE OR REPLACE FUNCTION public.merge_sf_test() - RETURNS TABLE(action text, a integer, b text, id integer, data text, filling integer[]) + RETURNS TABLE(action text, a integer, b text, id integer, data text, filling integer[], old_id integer, old_data text, old_filling integer[], new_id integer, new_data text, new_filling integer[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -3730,12 +3734,18 @@ BEGIN ATOMIC WHEN NOT MATCHED THEN INSERT (filling[1], id) VALUES (s.a, s.a) - RETURNING MERGE_ACTION() AS action, + RETURNING WITH (OLD AS o, NEW AS n) MERGE_ACTION() AS action, s.a, s.b, t.id, t.data, - t.filling; + t.filling, + o.id, + o.data, + o.filling, + n.id, + n.data, + n.filling; END CREATE FUNCTION merge_sf_test2() RETURNS void diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 8786058ed0c..095df0a670c 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -437,7 +437,8 @@ NOTICE: drop cascades to view ro_view19 -- simple updatable view CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); -CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0; +CREATE VIEW rw_view1 AS + SELECT *, 'Const' AS c, (SELECT concat('b: ', b)) AS d FROM base_tbl WHERE a>0; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'rw_view1'; @@ -462,7 +463,9 @@ SELECT table_name, column_name, is_updatable ------------+-------------+-------------- rw_view1 | a | YES rw_view1 | b | YES -(2 rows) + rw_view1 | c | NO + rw_view1 | d | NO +(4 rows) INSERT INTO rw_view1 VALUES (3, 'Row 3'); INSERT INTO rw_view1 (a) VALUES (4); @@ -479,20 +482,22 @@ SELECT * FROM base_tbl; 5 | Unspecified (6 rows) +SET jit_above_cost = 0; MERGE INTO rw_view1 t USING (VALUES (0, 'ROW 0'), (1, 'ROW 1'), (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) - RETURNING merge_action(), v.*, t.*; - merge_action | a | b | a | b ---------------+---+-------+---+------------- - UPDATE | 1 | ROW 1 | 1 | ROW 1 - DELETE | 3 | ROW 3 | 3 | Row 3 - INSERT | 2 | ROW 2 | 2 | Unspecified + RETURNING merge_action(), v.*, old, new, old.*, new.*, t.*; + merge_action | a | b | old | new | a | b | c | d | a | b | c | d | a | b | c | d +--------------+---+-------+------------------------------+----------------------------------------+---+-------+-------+----------+---+-------------+-------+----------------+---+-------------+-------+---------------- + UPDATE | 1 | ROW 1 | (1,"Row 1",Const,"b: Row 1") | (1,"ROW 1",Const,"b: ROW 1") | 1 | Row 1 | Const | b: Row 1 | 1 | ROW 1 | Const | b: ROW 1 | 1 | ROW 1 | Const | b: ROW 1 + DELETE | 3 | ROW 3 | (3,"Row 3",Const,"b: Row 3") | | 3 | Row 3 | Const | b: Row 3 | | | | | 3 | Row 3 | Const | b: Row 3 + INSERT | 2 | ROW 2 | | (2,Unspecified,Const,"b: Unspecified") | | | | | 2 | Unspecified | Const | b: Unspecified | 2 | Unspecified | Const | b: Unspecified (3 rows) +SET jit_above_cost TO DEFAULT; SELECT * FROM base_tbl ORDER BY a; a | b ----+------------- @@ -511,13 +516,13 @@ MERGE INTO rw_view1 t WHEN MATCHED THEN DELETE WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) - RETURNING merge_action(), v.*, t.*; - merge_action | a | b | a | b ---------------+---+----+---+------------- - UPDATE | 1 | R1 | 1 | R1 - DELETE | | | 5 | Unspecified - DELETE | 2 | R2 | 2 | Unspecified - INSERT | 3 | R3 | 3 | Unspecified + RETURNING merge_action(), v.*, old, new, old.*, new.*, t.*; + merge_action | a | b | old | new | a | b | c | d | a | b | c | d | a | b | c | d +--------------+---+----+----------------------------------------+----------------------------------------+---+-------------+-------+----------------+---+-------------+-------+----------------+---+-------------+-------+---------------- + UPDATE | 1 | R1 | (1,"ROW 1",Const,"b: ROW 1") | (1,R1,Const,"b: R1") | 1 | ROW 1 | Const | b: ROW 1 | 1 | R1 | Const | b: R1 | 1 | R1 | Const | b: R1 + DELETE | | | (5,Unspecified,Const,"b: Unspecified") | | 5 | Unspecified | Const | b: Unspecified | | | | | 5 | Unspecified | Const | b: Unspecified + DELETE | 2 | R2 | (2,Unspecified,Const,"b: Unspecified") | | 2 | Unspecified | Const | b: Unspecified | | | | | 2 | Unspecified | Const | b: Unspecified + INSERT | 3 | R3 | | (3,Unspecified,Const,"b: Unspecified") | | | | | 3 | Unspecified | Const | b: Unspecified | 3 | Unspecified | Const | b: Unspecified (4 rows) SELECT * FROM base_tbl ORDER BY a; @@ -634,8 +639,10 @@ DROP TABLE base_tbl_hist; -- view on top of view CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); -CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl WHERE a>0; -CREATE VIEW rw_view2 AS SELECT aa AS aaa, bb AS bbb FROM rw_view1 WHERE aa<10; +CREATE VIEW rw_view1 AS + SELECT b AS bb, a AS aa, 'Const1' AS c FROM base_tbl WHERE a>0; +CREATE VIEW rw_view2 AS + SELECT aa AS aaa, bb AS bbb, c AS c1, 'Const2' AS c2 FROM rw_view1 WHERE aa<10; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'rw_view2'; @@ -660,27 +667,29 @@ SELECT table_name, column_name, is_updatable ------------+-------------+-------------- rw_view2 | aaa | YES rw_view2 | bbb | YES -(2 rows) + rw_view2 | c1 | NO + rw_view2 | c2 | NO +(4 rows) INSERT INTO rw_view2 VALUES (3, 'Row 3'); INSERT INTO rw_view2 (aaa) VALUES (4); SELECT * FROM rw_view2; - aaa | bbb ------+------------- - 1 | Row 1 - 2 | Row 2 - 3 | Row 3 - 4 | Unspecified + aaa | bbb | c1 | c2 +-----+-------------+--------+-------- + 1 | Row 1 | Const1 | Const2 + 2 | Row 2 | Const1 | Const2 + 3 | Row 3 | Const1 | Const2 + 4 | Unspecified | Const1 | Const2 (4 rows) UPDATE rw_view2 SET bbb='Row 4' WHERE aaa=4; DELETE FROM rw_view2 WHERE aaa=2; SELECT * FROM rw_view2; - aaa | bbb ------+------- - 1 | Row 1 - 3 | Row 3 - 4 | Row 4 + aaa | bbb | c1 | c2 +-----+-------+--------+-------- + 1 | Row 1 | Const1 | Const2 + 3 | Row 3 | Const1 | Const2 + 4 | Row 4 | Const1 | Const2 (3 rows) MERGE INTO rw_view2 t @@ -688,20 +697,20 @@ MERGE INTO rw_view2 t WHEN MATCHED AND aaa = 3 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) - RETURNING merge_action(), v.*, t.*; - merge_action | a | b | aaa | bbb ---------------+---+----+-----+------------- - DELETE | 3 | R3 | 3 | Row 3 - UPDATE | 4 | R4 | 4 | R4 - INSERT | 5 | R5 | 5 | Unspecified + RETURNING merge_action(), v.*, (SELECT old), (SELECT (SELECT new)), t.*; + merge_action | a | b | old | new | aaa | bbb | c1 | c2 +--------------+---+----+---------------------------+-------------------------------+-----+-------------+--------+-------- + DELETE | 3 | R3 | (3,"Row 3",Const1,Const2) | | 3 | Row 3 | Const1 | Const2 + UPDATE | 4 | R4 | (4,"Row 4",Const1,Const2) | (4,R4,Const1,Const2) | 4 | R4 | Const1 | Const2 + INSERT | 5 | R5 | | (5,Unspecified,Const1,Const2) | 5 | Unspecified | Const1 | Const2 (3 rows) SELECT * FROM rw_view2 ORDER BY aaa; - aaa | bbb ------+------------- - 1 | Row 1 - 4 | R4 - 5 | Unspecified + aaa | bbb | c1 | c2 +-----+-------------+--------+-------- + 1 | Row 1 | Const1 | Const2 + 4 | R4 | Const1 | Const2 + 5 | Unspecified | Const1 | Const2 (3 rows) MERGE INTO rw_view2 t @@ -710,21 +719,21 @@ MERGE INTO rw_view2 t WHEN MATCHED THEN UPDATE SET bbb = v.b WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source' - RETURNING merge_action(), v.*, t.*; - merge_action | a | b | aaa | bbb ---------------+---+----+-----+----------------------- - UPDATE | | | 1 | Not matched by source - DELETE | 4 | r4 | 4 | R4 - UPDATE | 5 | r5 | 5 | r5 - INSERT | 6 | r6 | 6 | Unspecified + RETURNING merge_action(), v.*, old, (SELECT new FROM (VALUES ((SELECT new)))), t.*; + merge_action | a | b | old | new | aaa | bbb | c1 | c2 +--------------+---+----+-------------------------------+-------------------------------------------+-----+-----------------------+--------+-------- + UPDATE | | | (1,"Row 1",Const1,Const2) | (1,"Not matched by source",Const1,Const2) | 1 | Not matched by source | Const1 | Const2 + DELETE | 4 | r4 | (4,R4,Const1,Const2) | | 4 | R4 | Const1 | Const2 + UPDATE | 5 | r5 | (5,Unspecified,Const1,Const2) | (5,r5,Const1,Const2) | 5 | r5 | Const1 | Const2 + INSERT | 6 | r6 | | (6,Unspecified,Const1,Const2) | 6 | Unspecified | Const1 | Const2 (4 rows) SELECT * FROM rw_view2 ORDER BY aaa; - aaa | bbb ------+----------------------- - 1 | Not matched by source - 5 | r5 - 6 | Unspecified + aaa | bbb | c1 | c2 +-----+-----------------------+--------+-------- + 1 | Not matched by source | Const1 | Const2 + 5 | r5 | Const1 | Const2 + 6 | Unspecified | Const1 | Const2 (3 rows) EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; @@ -886,16 +895,25 @@ SELECT table_name, column_name, is_updatable rw_view2 | b | YES (4 rows) -INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *; - a | b ----+------- - 3 | Row 3 +INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING old.*, new.*; + a | b | a | b +---+---+---+------- + | | 3 | Row 3 (1 row) -UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *; - a | b ----+----------- - 3 | Row three +UPDATE rw_view2 SET b='R3' WHERE a=3 RETURNING old.*, new.*; -- rule returns NEW + a | b | a | b +---+----+---+---- + 3 | R3 | 3 | R3 +(1 row) + +DROP RULE rw_view1_upd_rule ON rw_view1; +CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1 + DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING *; +UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING old.*, new.*; + a | b | a | b +---+----+---+----------- + 3 | R3 | 3 | Row three (1 row) SELECT * FROM rw_view2; @@ -906,10 +924,10 @@ SELECT * FROM rw_view2; 3 | Row three (3 rows) -DELETE FROM rw_view2 WHERE a=3 RETURNING *; - a | b ----+----------- - 3 | Row three +DELETE FROM rw_view2 WHERE a=3 RETURNING old.*, new.*; + a | b | a | b +---+-----------+---+--- + 3 | Row three | | (1 row) SELECT * FROM rw_view2; @@ -960,8 +978,10 @@ drop cascades to view rw_view2 -- view on top of view with triggers CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); -CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers -CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10; +CREATE VIEW rw_view1 AS + SELECT *, 'Const1' AS c1 FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers +CREATE VIEW rw_view2 AS + SELECT *, 'Const2' AS c2 FROM rw_view1 WHERE a<10; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' @@ -992,9 +1012,12 @@ SELECT table_name, column_name, is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO + rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO -(4 rows) + rw_view2 | c1 | NO + rw_view2 | c2 | NO +(7 rows) CREATE FUNCTION rw_view1_trig_fn() RETURNS trigger AS @@ -1002,9 +1025,11 @@ $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO base_tbl VALUES (NEW.a, NEW.b); + NEW.c1 = 'Trigger Const1'; RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a; + NEW.c1 = 'Trigger Const1'; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN DELETE FROM base_tbl WHERE a=OLD.a; @@ -1045,9 +1070,12 @@ SELECT table_name, column_name, is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO + rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO -(4 rows) + rw_view2 | c1 | NO + rw_view2 | c2 | NO +(7 rows) CREATE TRIGGER rw_view1_upd_trig INSTEAD OF UPDATE ON rw_view1 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); @@ -1081,9 +1109,12 @@ SELECT table_name, column_name, is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO + rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO -(4 rows) + rw_view2 | c1 | NO + rw_view2 | c2 | NO +(7 rows) CREATE TRIGGER rw_view1_del_trig INSTEAD OF DELETE ON rw_view1 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); @@ -1117,41 +1148,44 @@ SELECT table_name, column_name, is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO + rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO -(4 rows) + rw_view2 | c1 | NO + rw_view2 | c2 | NO +(7 rows) -INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *; - a | b ----+------- - 3 | Row 3 +INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING old.*, new.*; + a | b | c1 | c2 | a | b | c1 | c2 +---+---+----+----+---+-------+----------------+-------- + | | | | 3 | Row 3 | Trigger Const1 | Const2 (1 row) -UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *; - a | b ----+----------- - 3 | Row three +UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING old.*, new.*; + a | b | c1 | c2 | a | b | c1 | c2 +---+-------+--------+--------+---+-----------+----------------+-------- + 3 | Row 3 | Const1 | Const2 | 3 | Row three | Trigger Const1 | Const2 (1 row) SELECT * FROM rw_view2; - a | b ----+----------- - 1 | Row 1 - 2 | Row 2 - 3 | Row three + a | b | c1 | c2 +---+-----------+--------+-------- + 1 | Row 1 | Const1 | Const2 + 2 | Row 2 | Const1 | Const2 + 3 | Row three | Const1 | Const2 (3 rows) -DELETE FROM rw_view2 WHERE a=3 RETURNING *; - a | b ----+----------- - 3 | Row three +DELETE FROM rw_view2 WHERE a=3 RETURNING old.*, new.*; + a | b | c1 | c2 | a | b | c1 | c2 +---+-----------+--------+--------+---+---+----+---- + 3 | Row three | Const1 | Const2 | | | | (1 row) SELECT * FROM rw_view2; - a | b ----+------- - 1 | Row 1 - 2 | Row 2 + a | b | c1 | c2 +---+-------+--------+-------- + 1 | Row 1 | Const1 | Const2 + 2 | Row 2 | Const1 | Const2 (2 rows) MERGE INTO rw_view2 t @@ -1159,12 +1193,12 @@ MERGE INTO rw_view2 t WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) - RETURNING merge_action(), s.*, t.*; - merge_action | a | b | a | b ---------------+---+----+---+------- - DELETE | 1 | R1 | 1 | Row 1 - UPDATE | 2 | R2 | 2 | R2 - INSERT | 3 | R3 | 3 | R3 + RETURNING merge_action(), s.*, old, new, t.*; + merge_action | a | b | old | new | a | b | c1 | c2 +--------------+---+----+---------------------------+--------------------------------+---+-------+----------------+-------- + DELETE | 1 | R1 | (1,"Row 1",Const1,Const2) | | 1 | Row 1 | Const1 | Const2 + UPDATE | 2 | R2 | (2,"Row 2",Const1,Const2) | (2,R2,"Trigger Const1",Const2) | 2 | R2 | Trigger Const1 | Const2 + INSERT | 3 | R3 | | (3,R3,"Trigger Const1",Const2) | 3 | R3 | Trigger Const1 | Const2 (3 rows) SELECT * FROM base_tbl ORDER BY a; @@ -1182,12 +1216,12 @@ MERGE INTO rw_view2 t WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = 'Not matched by source' - RETURNING merge_action(), s.*, t.*; - merge_action | a | b | a | b ---------------+---+----+---+----------------------- - UPDATE | 2 | r2 | 2 | r2 - UPDATE | | | 3 | Not matched by source - INSERT | 1 | r1 | 1 | r1 + RETURNING merge_action(), s.*, old, new, t.*; + merge_action | a | b | old | new | a | b | c1 | c2 +--------------+---+----+----------------------+-----------------------------------------------------+---+-----------------------+----------------+-------- + UPDATE | 2 | r2 | (2,R2,Const1,Const2) | (2,r2,"Trigger Const1",Const2) | 2 | r2 | Trigger Const1 | Const2 + UPDATE | | | (3,R3,Const1,Const2) | (3,"Not matched by source","Trigger Const1",Const2) | 3 | Not matched by source | Trigger Const1 | Const2 + INSERT | 1 | r1 | | (1,r1,"Trigger Const1",Const2) | 1 | r1 | Trigger Const1 | Const2 (3 rows) SELECT * FROM base_tbl ORDER BY a; diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 54929a92fac..07b6295b3ba 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -235,7 +235,7 @@ WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (s.sid, s.delta) -RETURNING merge_action(), t.*; +RETURNING merge_action(), old, new, t.*; SELECT * FROM target ORDER BY tid; ROLLBACK; @@ -677,7 +677,7 @@ WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN UPDATE SET balance = 0 WHEN NOT MATCHED BY SOURCE THEN DELETE -RETURNING merge_action(), t.*; +RETURNING merge_action(), old, new, t.*; SELECT * FROM target ORDER BY tid; ROLLBACK; @@ -930,7 +930,9 @@ WHEN MATCHED AND tid < 2 THEN DELETE RETURNING (SELECT abbrev FROM merge_actions WHERE action = merge_action()) AS action, - t.*, + old.tid AS old_tid, old.balance AS old_balance, + new.tid AS new_tid, new.balance AS new_balance, + (SELECT new.balance - old.balance AS delta_balance), t.*, CASE merge_action() WHEN 'INSERT' THEN 'Inserted '||t WHEN 'UPDATE' THEN 'Added '||delta||' to balance' @@ -956,7 +958,7 @@ WITH m AS ( INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN DELETE - RETURNING merge_action() AS action, t.*, + RETURNING merge_action() AS action, old AS old_data, new AS new_data, t.*, CASE merge_action() WHEN 'INSERT' THEN 'Inserted '||t WHEN 'UPDATE' THEN 'Added '||delta||' to balance' @@ -970,7 +972,7 @@ WITH m AS ( UPDATE SET last_change = description WHEN NOT MATCHED THEN INSERT VALUES (m.tid, description) - RETURNING action, merge_action() AS log_action, l.* + RETURNING m.*, merge_action() AS log_action, old AS old_log, new AS new_log, l.* ) SELECT * FROM m2; SELECT * FROM sq_target_merge_log ORDER BY tid; @@ -988,7 +990,7 @@ COPY ( INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN DELETE - RETURNING merge_action(), t.* + RETURNING merge_action(), old.*, new.* ) TO stdout; ROLLBACK; @@ -1265,7 +1267,7 @@ MERGE INTO pa_target t 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 merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; SELECT * FROM pa_target ORDER BY tid; ROLLBACK; @@ -1456,7 +1458,7 @@ MERGE INTO pa_target t UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge') - RETURNING merge_action(), t.*; + RETURNING merge_action(), old, new, t.*; SELECT * FROM pa_target ORDER BY tid; ROLLBACK; diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql index a460f82fb7c..b7258a3d97e 100644 --- a/src/test/regress/sql/returning.sql +++ b/src/test/regress/sql/returning.sql @@ -160,3 +160,221 @@ INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok + +-- +-- Test RETURNING OLD/NEW. +-- +-- Start with new data, to ensure predictable TIDs. +-- +TRUNCATE foo; +INSERT INTO foo VALUES (1, 'xxx', 10, 20), (2, 'more', 42, 141), (3, 'zoo2', 57, 99); + +-- Error cases +INSERT INTO foo DEFAULT VALUES RETURNING WITH (nonsuch AS something) *; +INSERT INTO foo DEFAULT VALUES RETURNING WITH (new AS foo) *; +INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS o, new AS n, old AS o) *; +INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS o, new AS n, new AS n) *; +INSERT INTO foo DEFAULT VALUES RETURNING WITH (old AS x, new AS x) *; + +-- INSERT has NEW, but not OLD +EXPLAIN (verbose, costs off) +INSERT INTO foo VALUES (4) + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; +INSERT INTO foo VALUES (4) + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +-- INSERT ... ON CONFLICT ... UPDATE has OLD and NEW +CREATE UNIQUE INDEX foo_f1_idx ON foo (f1); +EXPLAIN (verbose, costs off) +INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok') + ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1 + RETURNING WITH (OLD AS o, NEW AS n) + o.tableoid::regclass, o.ctid, o.*, + n.tableoid::regclass, n.ctid, n.*, *; +INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok') + ON CONFLICT (f1) DO UPDATE SET f2 = excluded.f2||'ed', f3 = -1 + RETURNING WITH (OLD AS o, NEW AS n) + o.tableoid::regclass, o.ctid, o.*, + n.tableoid::regclass, n.ctid, n.*, *; + +-- UPDATE has OLD and NEW +EXPLAIN (verbose, costs off) +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, old, + new.tableoid::regclass, new.ctid, new.*, new, + old.f4::text||'->'||new.f4::text AS change; +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, old, + new.tableoid::regclass, new.ctid, new.*, new, + old.f4::text||'->'||new.f4::text AS change; + +-- DELETE has OLD, but not NEW +EXPLAIN (verbose, costs off) +DELETE FROM foo WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; +DELETE FROM foo WHERE f1 = 5 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +-- RETURNING OLD and NEW from subquery +EXPLAIN (verbose, costs off) +INSERT INTO foo VALUES (5, 'subquery test') + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; +INSERT INTO foo VALUES (5, 'subquery test') + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + +EXPLAIN (verbose, costs off) +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING (SELECT old.f4 = new.f4), + (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; +UPDATE foo SET f4 = 100 WHERE f1 = 5 + RETURNING (SELECT old.f4 = new.f4), + (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + +EXPLAIN (verbose, costs off) +DELETE FROM foo WHERE f1 = 5 + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; +DELETE FROM foo WHERE f1 = 5 + RETURNING (SELECT max(old.f4 + x) FROM generate_series(1, 10) x) old_max, + (SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max; + +-- DELETE turned into UPDATE by a rule has OLD and NEW +CREATE RULE foo_del_rule AS ON DELETE TO foo DO INSTEAD + UPDATE foo SET f2 = f2||' (deleted)', f3 = -1, f4 = -1 WHERE f1 = OLD.f1 + RETURNING *; +EXPLAIN (verbose, costs off) +DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *; +DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *; + +-- UPDATE on view with rule +EXPLAIN (verbose, costs off) +UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; +UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; + +-- UPDATE on view with INSTEAD OF trigger +CREATE FUNCTION joinview_upd_trig_fn() RETURNS trigger +LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE 'UPDATE: % -> %', old, new; + UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10 + FROM joinme WHERE f2 = f2j AND f2 = old.f2 + RETURNING new.f1, new.f4 INTO new.f1, new.f4; -- should fail + RETURN NEW; +END; +$$; +CREATE TRIGGER joinview_upd_trig INSTEAD OF UPDATE ON joinview + FOR EACH ROW EXECUTE FUNCTION joinview_upd_trig_fn(); +DROP RULE joinview_u ON joinview; +UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should fail + +CREATE OR REPLACE FUNCTION joinview_upd_trig_fn() RETURNS trigger +LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE 'UPDATE: % -> %', old, new; + UPDATE foo SET f1 = new.f1, f3 = new.f3, f4 = new.f4 * 10 + FROM joinme WHERE f2 = f2j AND f2 = old.f2 + RETURNING WITH (new AS n) new.f1, n.f4 INTO new.f1, new.f4; -- now ok + RETURN NEW; +END; +$$; +EXPLAIN (verbose, costs off) +UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; +UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58 + RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3; -- should succeed + +-- Test wholerow & dropped column handling +ALTER TABLE foo DROP COLUMN f3 CASCADE; +UPDATE foo SET f4 = f4 + 1 RETURNING old.f3; -- should fail +UPDATE foo SET f4 = f4 + 1 RETURNING old, new; + +-- INSERT/DELETE on zero column table +CREATE TABLE zerocol(); +INSERT INTO zerocol SELECT RETURNING old.*, new.*, *; +INSERT INTO zerocol SELECT + RETURNING old.tableoid::regclass, old.ctid, + new.tableoid::regclass, new.ctid, ctid, *; +DELETE FROM zerocol + RETURNING old.tableoid::regclass, old.ctid, + new.tableoid::regclass, new.ctid, ctid, *; +DROP TABLE zerocol; + +-- Test cross-partition updates and attribute mapping +CREATE TABLE foo_parted (a int, b float8, c text) PARTITION BY LIST (a); +CREATE TABLE foo_part_s1 PARTITION OF foo_parted FOR VALUES IN (1); +CREATE TABLE foo_part_s2 PARTITION OF foo_parted FOR VALUES IN (2); +CREATE TABLE foo_part_d1 (c text, a int, b float8); +ALTER TABLE foo_parted ATTACH PARTITION foo_part_d1 FOR VALUES IN (3); +CREATE TABLE foo_part_d2 (b float8, c text, a int); +ALTER TABLE foo_parted ATTACH PARTITION foo_part_d2 FOR VALUES IN (4); + +INSERT INTO foo_parted + VALUES (1, 17.1, 'P1'), (2, 17.2, 'P2'), (3, 17.3, 'P3'), (4, 17.4, 'P4') + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +UPDATE foo_parted SET a = 2, b = b + 1, c = c || '->P2' WHERE a = 1 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +UPDATE foo_parted SET a = 1, b = b + 1, c = c || '->P1' WHERE a = 3 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +UPDATE foo_parted SET a = 3, b = b + 1, c = c || '->P3' WHERE a = 1 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +UPDATE foo_parted SET a = 4, b = b + 1, c = c || '->P4' WHERE a = 3 + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +-- cross-partition update that uses ReturningExpr nodes, without returning +-- old/new table values +CREATE VIEW foo_parted_v AS SELECT *, 'xxx' AS dummy FROM foo_parted; +UPDATE foo_parted_v SET a = 1, c = c || '->P1' WHERE a = 2 AND c = 'P2' + RETURNING 'P2:'||old.dummy, 'P1:'||new.dummy; + +DELETE FROM foo_parted + RETURNING old.tableoid::regclass, old.ctid, old.*, + new.tableoid::regclass, new.ctid, new.*, *; + +DROP TABLE foo_parted CASCADE; + +-- Test deparsing +CREATE FUNCTION foo_update() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + WITH u1 AS ( + UPDATE foo SET f1 = f1 + 1 RETURNING old.*, new.* + ), u2 AS ( + UPDATE foo SET f1 = f1 + 1 RETURNING WITH (OLD AS "old foo") "old foo".*, new.* + ), u3 AS ( + UPDATE foo SET f1 = f1 + 1 RETURNING WITH (NEW AS "new foo") old.*, "new foo".* + ) + UPDATE foo SET f1 = f1 + 1 + RETURNING WITH (OLD AS o, NEW AS n) + o.*, n.*, o, n, o.f1 = n.f1, o = n, + (SELECT o.f2 = n.f2), + (SELECT count(*) FROM foo WHERE foo.f1 = o.f4), + (SELECT count(*) FROM foo WHERE foo.f4 = n.f4), + (SELECT count(*) FROM foo WHERE foo = o), + (SELECT count(*) FROM foo WHERE foo = n); +END; + +\sf foo_update +DROP FUNCTION foo_update; diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 4a5fa505855..fdd3ff1d161 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1294,7 +1294,10 @@ MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s CREATE TABLE sf_target(id int, data text, filling int[]); CREATE FUNCTION merge_sf_test() - RETURNS TABLE(action text, a int, b text, id int, data text, filling int[]) + RETURNS TABLE(action text, a int, b text, + id int, data text, filling int[], + old_id int, old_data text, old_filling int[], + new_id int, new_data text, new_filling int[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -1333,7 +1336,8 @@ WHEN NOT MATCHED THEN INSERT (filling[1], id) VALUES (s.a, s.a) RETURNING - merge_action() AS action, *; + WITH (OLD AS o, NEW AS n) + merge_action() AS action, *, o.*, n.*; END; \sf merge_sf_test diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 93b693ae837..c071fffc116 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -154,7 +154,8 @@ DROP SEQUENCE uv_seq CASCADE; CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); -CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0; +CREATE VIEW rw_view1 AS + SELECT *, 'Const' AS c, (SELECT concat('b: ', b)) AS d FROM base_tbl WHERE a>0; SELECT table_name, is_insertable_into FROM information_schema.tables @@ -175,13 +176,18 @@ UPDATE rw_view1 SET a=5 WHERE a=4; DELETE FROM rw_view1 WHERE b='Row 2'; SELECT * FROM base_tbl; +SET jit_above_cost = 0; + MERGE INTO rw_view1 t USING (VALUES (0, 'ROW 0'), (1, 'ROW 1'), (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) - RETURNING merge_action(), v.*, t.*; + RETURNING merge_action(), v.*, old, new, old.*, new.*, t.*; + +SET jit_above_cost TO DEFAULT; + SELECT * FROM base_tbl ORDER BY a; MERGE INTO rw_view1 t @@ -191,7 +197,7 @@ MERGE INTO rw_view1 t WHEN MATCHED THEN DELETE WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) - RETURNING merge_action(), v.*, t.*; + RETURNING merge_action(), v.*, old, new, old.*, new.*, t.*; SELECT * FROM base_tbl ORDER BY a; EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5; @@ -240,8 +246,10 @@ DROP TABLE base_tbl_hist; CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); -CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl WHERE a>0; -CREATE VIEW rw_view2 AS SELECT aa AS aaa, bb AS bbb FROM rw_view1 WHERE aa<10; +CREATE VIEW rw_view1 AS + SELECT b AS bb, a AS aa, 'Const1' AS c FROM base_tbl WHERE a>0; +CREATE VIEW rw_view2 AS + SELECT aa AS aaa, bb AS bbb, c AS c1, 'Const2' AS c2 FROM rw_view1 WHERE aa<10; SELECT table_name, is_insertable_into FROM information_schema.tables @@ -268,7 +276,7 @@ MERGE INTO rw_view2 t WHEN MATCHED AND aaa = 3 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) - RETURNING merge_action(), v.*, t.*; + RETURNING merge_action(), v.*, (SELECT old), (SELECT (SELECT new)), t.*; SELECT * FROM rw_view2 ORDER BY aaa; MERGE INTO rw_view2 t @@ -277,7 +285,7 @@ MERGE INTO rw_view2 t WHEN MATCHED THEN UPDATE SET bbb = v.b WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source' - RETURNING merge_action(), v.*, t.*; + RETURNING merge_action(), v.*, old, (SELECT new FROM (VALUES ((SELECT new)))), t.*; SELECT * FROM rw_view2 ORDER BY aaa; EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; @@ -362,10 +370,14 @@ SELECT table_name, column_name, is_updatable WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; -INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *; -UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *; +INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING old.*, new.*; +UPDATE rw_view2 SET b='R3' WHERE a=3 RETURNING old.*, new.*; -- rule returns NEW +DROP RULE rw_view1_upd_rule ON rw_view1; +CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1 + DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING *; +UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING old.*, new.*; SELECT * FROM rw_view2; -DELETE FROM rw_view2 WHERE a=3 RETURNING *; +DELETE FROM rw_view2 WHERE a=3 RETURNING old.*, new.*; SELECT * FROM rw_view2; MERGE INTO rw_view2 t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a @@ -381,8 +393,10 @@ DROP TABLE base_tbl CASCADE; CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); -CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers -CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10; +CREATE VIEW rw_view1 AS + SELECT *, 'Const1' AS c1 FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers +CREATE VIEW rw_view2 AS + SELECT *, 'Const2' AS c2 FROM rw_view1 WHERE a<10; SELECT table_name, is_insertable_into FROM information_schema.tables @@ -407,9 +421,11 @@ $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO base_tbl VALUES (NEW.a, NEW.b); + NEW.c1 = 'Trigger Const1'; RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a; + NEW.c1 = 'Trigger Const1'; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN DELETE FROM base_tbl WHERE a=OLD.a; @@ -479,10 +495,10 @@ SELECT table_name, column_name, is_updatable WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; -INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *; -UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *; +INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING old.*, new.*; +UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING old.*, new.*; SELECT * FROM rw_view2; -DELETE FROM rw_view2 WHERE a=3 RETURNING *; +DELETE FROM rw_view2 WHERE a=3 RETURNING old.*, new.*; SELECT * FROM rw_view2; MERGE INTO rw_view2 t @@ -490,7 +506,7 @@ MERGE INTO rw_view2 t WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) - RETURNING merge_action(), s.*, t.*; + RETURNING merge_action(), s.*, old, new, t.*; SELECT * FROM base_tbl ORDER BY a; MERGE INTO rw_view2 t @@ -498,7 +514,7 @@ MERGE INTO rw_view2 t WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = 'Not matched by source' - RETURNING merge_action(), s.*, t.*; + RETURNING merge_action(), s.*, old, new, t.*; SELECT * FROM base_tbl ORDER BY a; EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 94dc956ae8c..56ba63f3d92 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2479,6 +2479,10 @@ ResultRelInfo ResultState ReturnSetInfo ReturnStmt +ReturningClause +ReturningExpr +ReturningOption +ReturningOptionKind RevmapContents RevokeRoleGrantAction RewriteMappingDataEntry @@ -2625,6 +2629,7 @@ SetOperation SetOperationStmt SetQuantifier SetToDefault +SetVarReturningType_context SetupWorkerPtrType ShDependObjectInfo SharedAggInfo @@ -3086,6 +3091,7 @@ Var VarBit VarChar VarParamState +VarReturningType VarString VarStringSortSupport Variable |