Re-implement pl/pgsql's expression and assignment parsing.

Invent new RawParseModes that allow the core grammar to handle
pl/pgsql expressions and assignments directly, and thereby get rid
of a lot of hackery in pl/pgsql's parser.  This moves a good deal
of knowledge about pl/pgsql into the core code: notably, we have to
invent a CoercionContext that matches pl/pgsql's (rather dubious)
historical behavior for assignment coercions.  That's getting away
from the original idea of pl/pgsql as an arm's-length extension of
the core, but really we crossed that bridge a long time ago.

The main advantage of doing this is that we can now use the core
parser to generate FieldStore and/or SubscriptingRef nodes to handle
assignments to pl/pgsql variables that are records or arrays.  That
fixes a number of cases that had never been implemented in pl/pgsql
assignment, such as nested records and array slicing, and it allows
pl/pgsql assignment to support the datatype-specific subscripting
behaviors introduced in commit c7aba7c14.

There are cosmetic benefits too: when a syntax error occurs in a
pl/pgsql expression, the error report no longer includes the confusing
"SELECT" keyword that used to get prefixed to the expression text.
Also, there seem to be some small speed gains.

Discussion: https://postgr.es/m/4165684.1607707277@sss.pgh.pa.us

12 files changed, 484 insertions, 30 deletions

diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 55950e754d2..7a4e104623b 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -1628,6 +1628,7 @@ CreateCast(CreateCastStmt *stmt)
 		case COERCION_ASSIGNMENT:
 			castcontext = COERCION_CODE_ASSIGNMENT;
 			break;
+			/* COERCION_PLPGSQL is intentionally not covered here */
 		case COERCION_EXPLICIT:
 			castcontext = COERCION_CODE_EXPLICIT;
 			break;
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 6c0593686a9..e28d2429222 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -51,6 +51,12 @@ static _SPI_connection *_SPI_current = NULL;
 static int	_SPI_stack_depth = 0;	/* allocated size of _SPI_stack */
 static int	_SPI_connected = -1;	/* current stack index */
 
+typedef struct SPICallbackArg
+{
+	const char *query;
+	RawParseMode mode;
+} SPICallbackArg;
+
 static Portal SPI_cursor_open_internal(const char *name, SPIPlanPtr plan,
 									   ParamListInfo paramLI, bool read_only);
 
@@ -1479,6 +1485,7 @@ SPI_cursor_open_internal(const char *name, SPIPlanPtr plan,
 	Snapshot	snapshot;
 	MemoryContext oldcontext;
 	Portal		portal;
+	SPICallbackArg spicallbackarg;
 	ErrorContextCallback spierrcontext;
 
 	/*
@@ -1533,8 +1540,10 @@ SPI_cursor_open_internal(const char *name, SPIPlanPtr plan,
 	 * Setup error traceback support for ereport(), in case GetCachedPlan
 	 * throws an error.
 	 */
+	spicallbackarg.query = plansource->query_string;
+	spicallbackarg.mode = plan->parse_mode;
 	spierrcontext.callback = _SPI_error_callback;
-	spierrcontext.arg = unconstify(char *, plansource->query_string);
+	spierrcontext.arg = &spicallbackarg;
 	spierrcontext.previous = error_context_stack;
 	error_context_stack = &spierrcontext;
 
@@ -1952,6 +1961,7 @@ SPI_plan_get_cached_plan(SPIPlanPtr plan)
 {
 	CachedPlanSource *plansource;
 	CachedPlan *cplan;
+	SPICallbackArg spicallbackarg;
 	ErrorContextCallback spierrcontext;
 
 	Assert(plan->magic == _SPI_PLAN_MAGIC);
@@ -1966,8 +1976,10 @@ SPI_plan_get_cached_plan(SPIPlanPtr plan)
 	plansource = (CachedPlanSource *) linitial(plan->plancache_list);
 
 	/* Setup error traceback support for ereport() */
+	spicallbackarg.query = plansource->query_string;
+	spicallbackarg.mode = plan->parse_mode;
 	spierrcontext.callback = _SPI_error_callback;
-	spierrcontext.arg = unconstify(char *, plansource->query_string);
+	spierrcontext.arg = &spicallbackarg;
 	spierrcontext.previous = error_context_stack;
 	error_context_stack = &spierrcontext;
 
@@ -2094,13 +2106,16 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan)
 	List	   *raw_parsetree_list;
 	List	   *plancache_list;
 	ListCell   *list_item;
+	SPICallbackArg spicallbackarg;
 	ErrorContextCallback spierrcontext;
 
 	/*
 	 * Setup error traceback support for ereport()
 	 */
+	spicallbackarg.query = src;
+	spicallbackarg.mode = plan->parse_mode;
 	spierrcontext.callback = _SPI_error_callback;
-	spierrcontext.arg = unconstify(char *, src);
+	spierrcontext.arg = &spicallbackarg;
 	spierrcontext.previous = error_context_stack;
 	error_context_stack = &spierrcontext;
 
@@ -2199,13 +2214,16 @@ _SPI_prepare_oneshot_plan(const char *src, SPIPlanPtr plan)
 	List	   *raw_parsetree_list;
 	List	   *plancache_list;
 	ListCell   *list_item;
+	SPICallbackArg spicallbackarg;
 	ErrorContextCallback spierrcontext;
 
 	/*
 	 * Setup error traceback support for ereport()
 	 */
+	spicallbackarg.query = src;
+	spicallbackarg.mode = plan->parse_mode;
 	spierrcontext.callback = _SPI_error_callback;
-	spierrcontext.arg = unconstify(char *, src);
+	spierrcontext.arg = &spicallbackarg;
 	spierrcontext.previous = error_context_stack;
 	error_context_stack = &spierrcontext;
 
@@ -2263,6 +2281,7 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
 	SPITupleTable *my_tuptable = NULL;
 	int			res = 0;
 	bool		pushed_active_snap = false;
+	SPICallbackArg spicallbackarg;
 	ErrorContextCallback spierrcontext;
 	CachedPlan *cplan = NULL;
 	ListCell   *lc1;
@@ -2270,8 +2289,10 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
 	/*
 	 * Setup error traceback support for ereport()
 	 */
+	spicallbackarg.query = NULL;	/* we'll fill this below */
+	spicallbackarg.mode = plan->parse_mode;
 	spierrcontext.callback = _SPI_error_callback;
-	spierrcontext.arg = NULL;	/* we'll fill this below */
+	spierrcontext.arg = &spicallbackarg;
 	spierrcontext.previous = error_context_stack;
 	error_context_stack = &spierrcontext;
 
@@ -2318,7 +2339,7 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI,
 		List	   *stmt_list;
 		ListCell   *lc2;
 
-		spierrcontext.arg = unconstify(char *, plansource->query_string);
+		spicallbackarg.query = plansource->query_string;
 
 		/*
 		 * If this is a one-shot plan, we still need to do parse analysis.
@@ -2722,7 +2743,8 @@ _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, uint64 tcount)
 static void
 _SPI_error_callback(void *arg)
 {
-	const char *query = (const char *) arg;
+	SPICallbackArg *carg = (SPICallbackArg *) arg;
+	const char *query = carg->query;
 	int			syntaxerrposition;
 
 	if (query == NULL)			/* in case arg wasn't set yet */
@@ -2740,7 +2762,23 @@ _SPI_error_callback(void *arg)
 		internalerrquery(query);
 	}
 	else
-		errcontext("SQL statement \"%s\"", query);
+	{
+		/* Use the parse mode to decide how to describe the query */
+		switch (carg->mode)
+		{
+			case RAW_PARSE_PLPGSQL_EXPR:
+				errcontext("SQL expression \"%s\"", query);
+				break;
+			case RAW_PARSE_PLPGSQL_ASSIGN1:
+			case RAW_PARSE_PLPGSQL_ASSIGN2:
+			case RAW_PARSE_PLPGSQL_ASSIGN3:
+				errcontext("PL/pgSQL assignment \"%s\"", query);
+				break;
+			default:
+				errcontext("SQL statement \"%s\"", query);
+				break;
+		}
+	}
 }
 
 /*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 67d45418662..ba3ccc712c8 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3199,6 +3199,20 @@ _copySetOperationStmt(const SetOperationStmt *from)
 	return newnode;
 }
 
+static PLAssignStmt *
+_copyPLAssignStmt(const PLAssignStmt *from)
+{
+	PLAssignStmt *newnode = makeNode(PLAssignStmt);
+
+	COPY_STRING_FIELD(name);
+	COPY_NODE_FIELD(indirection);
+	COPY_SCALAR_FIELD(nnames);
+	COPY_NODE_FIELD(val);
+	COPY_LOCATION_FIELD(location);
+
+	return newnode;
+}
+
 static AlterTableStmt *
 _copyAlterTableStmt(const AlterTableStmt *from)
 {
@@ -5220,6 +5234,9 @@ copyObjectImpl(const void *from)
 		case T_SetOperationStmt:
 			retval = _copySetOperationStmt(from);
 			break;
+		case T_PLAssignStmt:
+			retval = _copyPLAssignStmt(from);
+			break;
 		case T_AlterTableStmt:
 			retval = _copyAlterTableStmt(from);
 			break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 4d4258b0cb2..a2ef853dc2a 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1086,6 +1086,18 @@ _equalSetOperationStmt(const SetOperationStmt *a, const SetOperationStmt *b)
 }
 
 static bool
+_equalPLAssignStmt(const PLAssignStmt *a, const PLAssignStmt *b)
+{
+	COMPARE_STRING_FIELD(name);
+	COMPARE_NODE_FIELD(indirection);
+	COMPARE_SCALAR_FIELD(nnames);
+	COMPARE_NODE_FIELD(val);
+	COMPARE_LOCATION_FIELD(location);
+
+	return true;
+}
+
+static bool
 _equalAlterTableStmt(const AlterTableStmt *a, const AlterTableStmt *b)
 {
 	COMPARE_NODE_FIELD(relation);
@@ -3275,6 +3287,9 @@ equal(const void *a, const void *b)
 		case T_SetOperationStmt:
 			retval = _equalSetOperationStmt(a, b);
 			break;
+		case T_PLAssignStmt:
+			retval = _equalPLAssignStmt(a, b);
+			break;
 		case T_AlterTableStmt:
 			retval = _equalAlterTableStmt(a, b);
 			break;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index a011bc7b98d..6be19916fce 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3669,6 +3669,16 @@ raw_expression_tree_walker(Node *node,
 					return true;
 			}
 			break;
+		case T_PLAssignStmt:
+			{
+				PLAssignStmt *stmt = (PLAssignStmt *) node;
+
+				if (walker(stmt->indirection, context))
+					return true;
+				if (walker(stmt->val, context))
+					return true;
+			}
+			break;
 		case T_A_Expr:
 			{
 				A_Expr	   *expr = (A_Expr *) node;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 6b8ec03fa77..8392be6d44a 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2776,6 +2776,18 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
 }
 
 static void
+_outPLAssignStmt(StringInfo str, const PLAssignStmt *node)
+{
+	WRITE_NODE_TYPE("PLASSIGN");
+
+	WRITE_STRING_FIELD(name);
+	WRITE_NODE_FIELD(indirection);
+	WRITE_INT_FIELD(nnames);
+	WRITE_NODE_FIELD(val);
+	WRITE_LOCATION_FIELD(location);
+}
+
+static void
 _outFuncCall(StringInfo str, const FuncCall *node)
 {
 	WRITE_NODE_TYPE("FUNCCALL");
@@ -4211,6 +4223,9 @@ outNode(StringInfo str, const void *obj)
 			case T_SelectStmt:
 				_outSelectStmt(str, obj);
 				break;
+			case T_PLAssignStmt:
+				_outPLAssignStmt(str, obj);
+				break;
 			case T_ColumnDef:
 				_outColumnDef(str, obj);
 				break;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 1066f9458f3..28e192f51c8 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -42,8 +42,10 @@
 #include "parser/parse_param.h"
 #include "parser/parse_relation.h"
 #include "parser/parse_target.h"
+#include "parser/parse_type.h"
 #include "parser/parsetree.h"
 #include "rewrite/rewriteManip.h"
+#include "utils/builtins.h"
 #include "utils/rel.h"
 
 
@@ -70,6 +72,8 @@ static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
 static List *transformReturningList(ParseState *pstate, List *returningList);
 static List *transformUpdateTargetList(ParseState *pstate,
 									   List *targetList);
+static Query *transformPLAssignStmt(ParseState *pstate,
+									PLAssignStmt *stmt);
 static Query *transformDeclareCursorStmt(ParseState *pstate,
 										 DeclareCursorStmt *stmt);
 static Query *transformExplainStmt(ParseState *pstate,
@@ -304,6 +308,11 @@ transformStmt(ParseState *pstate, Node *parseTree)
 			}
 			break;
 
+		case T_PLAssignStmt:
+			result = transformPLAssignStmt(pstate,
+										   (PLAssignStmt *) parseTree);
+			break;
+
 			/*
 			 * Special cases
 			 */
@@ -367,6 +376,7 @@ analyze_requires_snapshot(RawStmt *parseTree)
 		case T_DeleteStmt:
 		case T_UpdateStmt:
 		case T_SelectStmt:
+		case T_PLAssignStmt:
 			result = true;
 			break;
 
@@ -2394,6 +2404,236 @@ transformReturningList(ParseState *pstate, List *returningList)
 
 
 /*
+ * transformPLAssignStmt -
+ *	  transform a PL/pgSQL assignment statement
+ *
+ * If there is no opt_indirection, the transformed statement looks like
+ * "SELECT a_expr ...", except the expression has been cast to the type of
+ * the target.  With indirection, it's still a SELECT, but the expression will
+ * incorporate FieldStore and/or assignment SubscriptingRef nodes to compute a
+ * new value for a container-type variable represented by the target.  The
+ * expression references the target as the container source.
+ */
+static Query *
+transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt)
+{
+	Query	   *qry = makeNode(Query);
+	ColumnRef  *cref = makeNode(ColumnRef);
+	List	   *indirection = stmt->indirection;
+	int			nnames = stmt->nnames;
+	SelectStmt *sstmt = stmt->val;
+	Node	   *target;
+	Oid			targettype;
+	int32		targettypmod;
+	Oid			targetcollation;
+	List	   *tlist;
+	TargetEntry *tle;
+	Oid			type_id;
+	Node	   *qual;
+	ListCell   *l;
+
+	/*
+	 * First, construct a ColumnRef for the target variable.  If the target
+	 * has more than one dotted name, we have to pull the extra names out of
+	 * the indirection list.
+	 */
+	cref->fields = list_make1(makeString(stmt->name));
+	cref->location = stmt->location;
+	if (nnames > 1)
+	{
+		/* avoid munging the raw parsetree */
+		indirection = list_copy(indirection);
+		while (--nnames > 0 && indirection != NIL)
+		{
+			Node	   *ind = (Node *) linitial(indirection);
+
+			if (!IsA(ind, String))
+				elog(ERROR, "invalid name count in PLAssignStmt");
+			cref->fields = lappend(cref->fields, ind);
+			indirection = list_delete_first(indirection);
+		}
+	}
+
+	/*
+	 * Transform the target reference.  Typically we will get back a Param
+	 * node, but there's no reason to be too picky about its type.
+	 */
+	target = transformExpr(pstate, (Node *) cref,
+						   EXPR_KIND_UPDATE_TARGET);
+	targettype = exprType(target);
+	targettypmod = exprTypmod(target);
+	targetcollation = exprCollation(target);
+
+	/*
+	 * The rest mostly matches transformSelectStmt, except that we needn't
+	 * consider WITH or DISTINCT, and we build a targetlist our own way.
+	 */
+	qry->commandType = CMD_SELECT;
+	pstate->p_is_insert = false;
+
+	/* make FOR UPDATE/FOR SHARE info available to addRangeTableEntry */
+	pstate->p_locking_clause = sstmt->lockingClause;
+
+	/* make WINDOW info available for window functions, too */
+	pstate->p_windowdefs = sstmt->windowClause;
+
+	/* process the FROM clause */
+	transformFromClause(pstate, sstmt->fromClause);
+
+	/* initially transform the targetlist as if in SELECT */
+	tlist = transformTargetList(pstate, sstmt->targetList,
+								EXPR_KIND_SELECT_TARGET);
+
+	/* we should have exactly one targetlist item */
+	if (list_length(tlist) != 1)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg_plural("assignment source returned %d column",
+							   "assignment source returned %d columns",
+							   list_length(tlist),
+							   list_length(tlist))));
+
+	tle = linitial_node(TargetEntry, tlist);
+
+	/*
+	 * This next bit is similar to transformAssignedExpr; the key difference
+	 * is we use COERCION_PLPGSQL not COERCION_ASSIGNMENT.
+	 */
+	type_id = exprType((Node *) tle->expr);
+
+	pstate->p_expr_kind = EXPR_KIND_UPDATE_TARGET;
+
+	if (indirection)
+	{
+		tle->expr = (Expr *)
+			transformAssignmentIndirection(pstate,
+										   target,
+										   stmt->name,
+										   false,
+										   targettype,
+										   targettypmod,
+										   targetcollation,
+										   indirection,
+										   list_head(indirection),
+										   (Node *) tle->expr,
+										   COERCION_PLPGSQL,
+										   exprLocation(target));
+	}
+	else if (targettype != type_id &&
+			 (targettype == RECORDOID || ISCOMPLEX(targettype)) &&
+			 (type_id == RECORDOID || ISCOMPLEX(type_id)))
+	{
+		/*
+		 * Hack: do not let coerce_to_target_type() deal with inconsistent
+		 * composite types.  Just pass the expression result through as-is,
+		 * and let the PL/pgSQL executor do the conversion its way.  This is
+		 * rather bogus, but it's needed for backwards compatibility.
+		 */
+	}
+	else
+	{
+		/*
+		 * For normal non-qualified target column, do type checking and
+		 * coercion.
+		 */
+		Node	   *orig_expr = (Node *) tle->expr;
+
+		tle->expr = (Expr *)
+			coerce_to_target_type(pstate,
+								  orig_expr, type_id,
+								  targettype, targettypmod,
+								  COERCION_PLPGSQL,
+								  COERCE_IMPLICIT_CAST,
+								  -1);
+		/* With COERCION_PLPGSQL, this error is probably unreachable */
+		if (tle->expr == NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("variable \"%s\" is of type %s"
+							" but expression is of type %s",
+							stmt->name,
+							format_type_be(targettype),
+							format_type_be(type_id)),
+					 errhint("You will need to rewrite or cast the expression."),
+					 parser_errposition(pstate, exprLocation(orig_expr))));
+	}
+
+	pstate->p_expr_kind = EXPR_KIND_NONE;
+
+	qry->targetList = list_make1(tle);
+
+	/* transform WHERE */
+	qual = transformWhereClause(pstate, sstmt->whereClause,
+								EXPR_KIND_WHERE, "WHERE");
+
+	/* initial processing of HAVING clause is much like WHERE clause */
+	qry->havingQual = transformWhereClause(pstate, sstmt->havingClause,
+										   EXPR_KIND_HAVING, "HAVING");
+
+	/*
+	 * Transform sorting/grouping stuff.  Do ORDER BY first because both
+	 * transformGroupClause and transformDistinctClause need the results. Note
+	 * that these functions can also change the targetList, so it's passed to
+	 * them by reference.
+	 */
+	qry->sortClause = transformSortClause(pstate,
+										  sstmt->sortClause,
+										  &qry->targetList,
+										  EXPR_KIND_ORDER_BY,
+										  false /* allow SQL92 rules */ );
+
+	qry->groupClause = transformGroupClause(pstate,
+											sstmt->groupClause,
+											&qry->groupingSets,
+											&qry->targetList,
+											qry->sortClause,
+											EXPR_KIND_GROUP_BY,
+											false /* allow SQL92 rules */ );
+
+	/* No DISTINCT clause */
+	Assert(!sstmt->distinctClause);
+	qry->distinctClause = NIL;
+	qry->hasDistinctOn = false;
+
+	/* transform LIMIT */
+	qry->limitOffset = transformLimitClause(pstate, sstmt->limitOffset,
+											EXPR_KIND_OFFSET, "OFFSET",
+											sstmt->limitOption);
+	qry->limitCount = transformLimitClause(pstate, sstmt->limitCount,
+										   EXPR_KIND_LIMIT, "LIMIT",
+										   sstmt->limitOption);
+	qry->limitOption = sstmt->limitOption;
+
+	/* transform window clauses after we have seen all window functions */
+	qry->windowClause = transformWindowDefinitions(pstate,
+												   pstate->p_windowdefs,
+												   &qry->targetList);
+
+	qry->rtable = pstate->p_rtable;
+	qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
+
+	qry->hasSubLinks = pstate->p_hasSubLinks;
+	qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
+	qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
+	qry->hasAggs = pstate->p_hasAggs;
+
+	foreach(l, sstmt->lockingClause)
+	{
+		transformLockingClause(pstate, qry,
+							   (LockingClause *) lfirst(l), false);
+	}
+
+	assign_query_collations(pstate, qry);
+
+	/* this must be done after collations, for reliable comparison of exprs */
+	if (pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual)
+		parseCheckAggregates(pstate, qry);
+
+	return qry;
+}
+
+
+/*
  * transformDeclareCursorStmt -
  *	transform a DECLARE CURSOR Statement
  *
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index fb025f08a4e..31c95443a5b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -294,6 +294,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type <node>	select_no_parens select_with_parens select_clause
 				simple_select values_clause
+				PLpgSQL_Expr PLAssignStmt
 
 %type <node>	alter_column_default opclass_item opclass_drop alter_using
 %type <ival>	add_drop opt_asc_desc opt_nulls_order
@@ -535,7 +536,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <str>		ColId ColLabel BareColLabel
 %type <str>		NonReservedWord NonReservedWord_or_Sconst
 %type <str>		var_name type_function_name param_name
-%type <str>		createdb_opt_name
+%type <str>		createdb_opt_name plassign_target
 %type <node>	var_value zone_value
 %type <rolespec> auth_ident RoleSpec opt_granted_by
 
@@ -731,6 +732,10 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
  * something other than the usual list of SQL commands.
  */
 %token		MODE_TYPE_NAME
+%token		MODE_PLPGSQL_EXPR
+%token		MODE_PLPGSQL_ASSIGN1
+%token		MODE_PLPGSQL_ASSIGN2
+%token		MODE_PLPGSQL_ASSIGN3
 
 
 /* Precedence: lowest to highest */
@@ -810,6 +815,32 @@ parse_toplevel:
 			{
 				pg_yyget_extra(yyscanner)->parsetree = list_make1($2);
 			}
+			| MODE_PLPGSQL_EXPR PLpgSQL_Expr
+			{
+				pg_yyget_extra(yyscanner)->parsetree =
+					list_make1(makeRawStmt($2, 0));
+			}
+			| MODE_PLPGSQL_ASSIGN1 PLAssignStmt
+			{
+				PLAssignStmt *n = (PLAssignStmt *) $2;
+				n->nnames = 1;
+				pg_yyget_extra(yyscanner)->parsetree =
+					list_make1(makeRawStmt((Node *) n, 0));
+			}
+			| MODE_PLPGSQL_ASSIGN2 PLAssignStmt
+			{
+				PLAssignStmt *n = (PLAssignStmt *) $2;
+				n->nnames = 2;
+				pg_yyget_extra(yyscanner)->parsetree =
+					list_make1(makeRawStmt((Node *) n, 0));
+			}
+			| MODE_PLPGSQL_ASSIGN3 PLAssignStmt
+			{
+				PLAssignStmt *n = (PLAssignStmt *) $2;
+				n->nnames = 3;
+				pg_yyget_extra(yyscanner)->parsetree =
+					list_make1(makeRawStmt((Node *) n, 0));
+			}
 		;
 
 /*
@@ -15024,6 +15055,72 @@ role_list:	RoleSpec
 					{ $$ = lappend($1, $3); }
 		;
 
+
+/*****************************************************************************
+ *
+ * PL/pgSQL extensions
+ *
+ * You'd think a PL/pgSQL "expression" should be just an a_expr, but
+ * historically it can include just about anything that can follow SELECT.
+ * Therefore the returned struct is a SelectStmt.
+ *****************************************************************************/
+
+PLpgSQL_Expr: opt_target_list
+			from_clause where_clause
+			group_clause having_clause window_clause
+			opt_sort_clause opt_select_limit opt_for_locking_clause
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+
+					n->targetList = $1;
+					n->fromClause = $2;
+					n->whereClause = $3;
+					n->groupClause = $4;
+					n->havingClause = $5;
+					n->windowClause = $6;
+					n->sortClause = $7;
+					if ($8)
+					{
+						n->limitOffset = $8->limitOffset;
+						n->limitCount = $8->limitCount;
+						if (!n->sortClause &&
+							$8->limitOption == LIMIT_OPTION_WITH_TIES)
+							ereport(ERROR,
+									(errcode(ERRCODE_SYNTAX_ERROR),
+									 errmsg("WITH TIES cannot be specified without ORDER BY clause")));
+						n->limitOption = $8->limitOption;
+					}
+					n->lockingClause = $9;
+					$$ = (Node *) n;
+				}
+		;
+
+/*
+ * PL/pgSQL Assignment statement: name opt_indirection := PLpgSQL_Expr
+ */
+
+PLAssignStmt: plassign_target opt_indirection plassign_equals PLpgSQL_Expr
+				{
+					PLAssignStmt *n = makeNode(PLAssignStmt);
+
+					n->name = $1;
+					n->indirection = check_indirection($2, yyscanner);
+					/* nnames will be filled by calling production */
+					n->val = (SelectStmt *) $4;
+					n->location = @1;
+					$$ = (Node *) n;
+				}
+		;
+
+plassign_target: ColId							{ $$ = $1; }
+			| PARAM								{ $$ = psprintf("$%d", $1); }
+		;
+
+plassign_equals: COLON_EQUALS
+			| '='
+		;
+
+
 /*
  * Name classification hierarchy.
  *
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 74eb39c0e4d..d5310f27db1 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -3098,6 +3098,14 @@ find_coercion_pathway(Oid targetTypeId, Oid sourceTypeId,
 		}
 	}
 
+	/*
+	 * When parsing PL/pgSQL assignments, allow an I/O cast to be used
+	 * whenever no normal coercion is available.
+	 */
+	if (result == COERCION_PATH_NONE &&
+		ccontext == COERCION_PLPGSQL)
+		result = COERCION_PATH_COERCEVIAIO;
+
 	return result;
 }
 
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index fdf5500bf20..7eaa076771a 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -34,17 +34,6 @@
 
 static void markTargetListOrigin(ParseState *pstate, TargetEntry *tle,
 								 Var *var, int levelsup);
-static Node *transformAssignmentIndirection(ParseState *pstate,
-											Node *basenode,
-											const char *targetName,
-											bool targetIsSubscripting,
-											Oid targetTypeId,
-											int32 targetTypMod,
-											Oid targetCollation,
-											List *indirection,
-											ListCell *indirection_cell,
-											Node *rhs,
-											int location);
 static Node *transformAssignmentSubscripts(ParseState *pstate,
 										   Node *basenode,
 										   const char *targetName,
@@ -56,6 +45,7 @@ static Node *transformAssignmentSubscripts(ParseState *pstate,
 										   List *indirection,
 										   ListCell *next_indirection,
 										   Node *rhs,
+										   CoercionContext ccontext,
 										   int location);
 static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref,
 								 bool make_target_entry);
@@ -561,6 +551,7 @@ transformAssignedExpr(ParseState *pstate,
 										   indirection,
 										   list_head(indirection),
 										   (Node *) expr,
+										   COERCION_ASSIGNMENT,
 										   location);
 	}
 	else
@@ -642,15 +633,15 @@ updateTargetListEntry(ParseState *pstate,
 
 /*
  * Process indirection (field selection or subscripting) of the target
- * column in INSERT/UPDATE.  This routine recurses for multiple levels
- * of indirection --- but note that several adjacent A_Indices nodes in
- * the indirection list are treated as a single multidimensional subscript
+ * column in INSERT/UPDATE/assignment.  This routine recurses for multiple
+ * levels of indirection --- but note that several adjacent A_Indices nodes
+ * in the indirection list are treated as a single multidimensional subscript
  * operation.
  *
  * In the initial call, basenode is a Var for the target column in UPDATE,
- * or a null Const of the target's type in INSERT.  In recursive calls,
- * basenode is NULL, indicating that a substitute node should be consed up if
- * needed.
+ * or a null Const of the target's type in INSERT, or a Param for the target
+ * variable in PL/pgSQL assignment.  In recursive calls, basenode is NULL,
+ * indicating that a substitute node should be consed up if needed.
  *
  * targetName is the name of the field or subfield we're assigning to, and
  * targetIsSubscripting is true if we're subscripting it.  These are just for
@@ -667,12 +658,16 @@ updateTargetListEntry(ParseState *pstate,
  * rhs is the already-transformed value to be assigned; note it has not been
  * coerced to any particular type.
  *
+ * ccontext is the coercion level to use while coercing the rhs.  For
+ * normal statements it'll be COERCION_ASSIGNMENT, but PL/pgSQL uses
+ * a special value.
+ *
  * location is the cursor error position for any errors.  (Note: this points
  * to the head of the target clause, eg "foo" in "foo.bar[baz]".  Later we
  * might want to decorate indirection cells with their own location info,
  * in which case the location argument could probably be dropped.)
  */
-static Node *
+Node *
 transformAssignmentIndirection(ParseState *pstate,
 							   Node *basenode,
 							   const char *targetName,
@@ -683,6 +678,7 @@ transformAssignmentIndirection(ParseState *pstate,
 							   List *indirection,
 							   ListCell *indirection_cell,
 							   Node *rhs,
+							   CoercionContext ccontext,
 							   int location)
 {
 	Node	   *result;
@@ -757,6 +753,7 @@ transformAssignmentIndirection(ParseState *pstate,
 													 indirection,
 													 i,
 													 rhs,
+													 ccontext,
 													 location);
 			}
 
@@ -807,6 +804,7 @@ transformAssignmentIndirection(ParseState *pstate,
 												 indirection,
 												 lnext(indirection, i),
 												 rhs,
+												 ccontext,
 												 location);
 
 			/* and build a FieldStore node */
@@ -845,6 +843,7 @@ transformAssignmentIndirection(ParseState *pstate,
 											 indirection,
 											 NULL,
 											 rhs,
+											 ccontext,
 											 location);
 	}
 
@@ -853,7 +852,7 @@ transformAssignmentIndirection(ParseState *pstate,
 	result = coerce_to_target_type(pstate,
 								   rhs, exprType(rhs),
 								   targetTypeId, targetTypMod,
-								   COERCION_ASSIGNMENT,
+								   ccontext,
 								   COERCE_IMPLICIT_CAST,
 								   -1);
 	if (result == NULL)
@@ -898,6 +897,7 @@ transformAssignmentSubscripts(ParseState *pstate,
 							  List *indirection,
 							  ListCell *next_indirection,
 							  Node *rhs,
+							  CoercionContext ccontext,
 							  int location)
 {
 	Node	   *result;
@@ -949,6 +949,7 @@ transformAssignmentSubscripts(ParseState *pstate,
 										 indirection,
 										 next_indirection,
 										 rhs,
+										 ccontext,
 										 location);
 
 	/*
@@ -969,7 +970,7 @@ transformAssignmentSubscripts(ParseState *pstate,
 		result = coerce_to_target_type(pstate,
 									   result, resulttype,
 									   targetTypeId, targetTypMod,
-									   COERCION_ASSIGNMENT,
+									   ccontext,
 									   COERCE_IMPLICIT_CAST,
 									   -1);
 		/* can fail if we had int2vector/oidvector, but not for true domains */
diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c
index 8eb8feb372e..875de7ba28f 100644
--- a/src/backend/parser/parser.c
+++ b/src/backend/parser/parser.c
@@ -57,7 +57,11 @@ raw_parser(const char *str, RawParseMode mode)
 		/* this array is indexed by RawParseMode enum */
 		static const int mode_token[] = {
 			0,					/* RAW_PARSE_DEFAULT */
-			MODE_TYPE_NAME		/* RAW_PARSE_TYPE_NAME */
+			MODE_TYPE_NAME,		/* RAW_PARSE_TYPE_NAME */
+			MODE_PLPGSQL_EXPR,	/* RAW_PARSE_PLPGSQL_EXPR */
+			MODE_PLPGSQL_ASSIGN1,	/* RAW_PARSE_PLPGSQL_ASSIGN1 */
+			MODE_PLPGSQL_ASSIGN2,	/* RAW_PARSE_PLPGSQL_ASSIGN2 */
+			MODE_PLPGSQL_ASSIGN3	/* RAW_PARSE_PLPGSQL_ASSIGN3 */
 		};
 
 		yyextra.have_lookahead = true;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 5825d3d8142..53a511f1da8 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -2313,6 +2313,10 @@ CreateCommandTag(Node *parsetree)
 			tag = CMDTAG_SELECT;
 			break;
 
+		case T_PLAssignStmt:
+			tag = CMDTAG_SELECT;
+			break;
+
 			/* utility statements --- same whether raw or cooked */
 		case T_TransactionStmt:
 			{
@@ -3181,6 +3185,10 @@ GetCommandLogLevel(Node *parsetree)
 				lev = LOGSTMT_ALL;
 			break;
 
+		case T_PLAssignStmt:
+			lev = LOGSTMT_ALL;
+			break;
+
 			/* utility statements --- same whether raw or cooked */
 		case T_TransactionStmt:
 			lev = LOGSTMT_ALL;