Add OLD/NEW support to RETURNING in DML queries.

This allows the RETURNING list of INSERT/UPDATE/DELETE/MERGE queries
to explicitly return old and new values by using the special aliases
"old" and "new", which are automatically added to the query (if not
already defined) while parsing its RETURNING list, allowing things
like:

  RETURNING old.colname, new.colname, ...

  RETURNING old.*, new.*

Additionally, a new syntax is supported, allowing the names "old" and
"new" to be changed to user-supplied alias names, e.g.:

  RETURNING WITH (OLD AS o, NEW AS n) o.colname, n.colname, ...

This is useful when the names "old" and "new" are already defined,
such as inside trigger functions, allowing backwards compatibility to
be maintained -- the interpretation of any existing queries that
happen to already refer to relations called "old" or "new", or use
those as aliases for other relations, is not changed.

For an INSERT, old values will generally be NULL, and for a DELETE,
new values will generally be NULL, but that may change for an INSERT
with an ON CONFLICT ... DO UPDATE clause, or if a query rewrite rule
changes the command type. Therefore, we put no restrictions on the use
of old and new in any DML queries.

Dean Rasheed, reviewed by Jian He and Jeff Davis.

Discussion: https://postgr.es/m/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com

1 files changed, 186 insertions, 3 deletions

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,