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, 27 insertions, 6 deletions

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;