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, 126 insertions, 24 deletions

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;
 }