Restructure SELECT INTO's parsetree representation into CreateTableAsStmt.

Making this operation look like a utility statement seems generally a good
idea, and particularly so in light of the desire to provide command
triggers for utility statements.  The original choice of representing it as
SELECT with an IntoClause appendage had metastasized into rather a lot of
places, unfortunately, so that this patch is a great deal more complicated
than one might at first expect.

In particular, keeping EXPLAIN working for SELECT INTO and CREATE TABLE AS
subcommands required restructuring some EXPLAIN-related APIs.  Add-on code
that calls ExplainOnePlan or ExplainOneUtility, or uses
ExplainOneQuery_hook, will need adjustment.

Also, the cases PREPARE ... SELECT INTO and CREATE RULE ... SELECT INTO,
which formerly were accepted though undocumented, are no longer accepted.
The PREPARE case can be replaced with use of CREATE TABLE AS EXECUTE.
The CREATE RULE case doesn't seem to have much real-world use (since the
rule would work only once before failing with "table already exists"),
so we'll not bother with that one.

Both SELECT INTO and CREATE TABLE AS still return a command tag of
"SELECT nnnn".  There was some discussion of returning "CREATE TABLE nnnn",
but for the moment backwards compatibility wins the day.

Andres Freund and Tom Lane

5 files changed, 125 insertions, 86 deletions

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index b187b03666a..3329e9c9645 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -10,8 +10,8 @@
  * utility commands, no locks are obtained here (and if they were, we could
  * not be sure we'd still have them at execution).  Hence the general rule
  * for utility commands is to just dump them into a Query node untransformed.
- * DECLARE CURSOR and EXPLAIN are exceptions because they contain
- * optimizable statements.
+ * DECLARE CURSOR, EXPLAIN, and CREATE TABLE AS are exceptions because they
+ * contain optimizable statements, which we should transform.
  *
  *
  * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
@@ -62,6 +62,8 @@ static Query *transformDeclareCursorStmt(ParseState *pstate,
 						   DeclareCursorStmt *stmt);
 static Query *transformExplainStmt(ParseState *pstate,
 					 ExplainStmt *stmt);
+static Query *transformCreateTableAsStmt(ParseState *pstate,
+						   CreateTableAsStmt *stmt);
 static void transformLockingClause(ParseState *pstate, Query *qry,
 					   LockingClause *lc, bool pushedDown);
 
@@ -91,7 +93,7 @@ parse_analyze(Node *parseTree, const char *sourceText,
 	if (numParams > 0)
 		parse_fixed_parameters(pstate, paramTypes, numParams);
 
-	query = transformStmt(pstate, parseTree);
+	query = transformTopLevelStmt(pstate, parseTree);
 
 	free_parsestate(pstate);
 
@@ -118,7 +120,7 @@ parse_analyze_varparams(Node *parseTree, const char *sourceText,
 
 	parse_variable_parameters(pstate, paramTypes, numParams);
 
-	query = transformStmt(pstate, parseTree);
+	query = transformTopLevelStmt(pstate, parseTree);
 
 	/* make sure all is well with parameter types */
 	check_variable_parameters(pstate, query);
@@ -151,8 +153,52 @@ parse_sub_analyze(Node *parseTree, ParseState *parentParseState,
 }
 
 /*
- * transformStmt -
+ * transformTopLevelStmt -
  *	  transform a Parse tree into a Query tree.
+ *
+ * The only thing we do here that we don't do in transformStmt() is to
+ * convert SELECT ... INTO into CREATE TABLE AS.  Since utility statements
+ * aren't allowed within larger statements, this is only allowed at the top
+ * of the parse tree, and so we only try it before entering the recursive
+ * transformStmt() processing.
+ */
+Query *
+transformTopLevelStmt(ParseState *pstate, Node *parseTree)
+{
+	if (IsA(parseTree, SelectStmt))
+	{
+		SelectStmt *stmt = (SelectStmt *) parseTree;
+
+		/* If it's a set-operation tree, drill down to leftmost SelectStmt */
+		while (stmt && stmt->op != SETOP_NONE)
+			stmt = stmt->larg;
+		Assert(stmt && IsA(stmt, SelectStmt) && stmt->larg == NULL);
+
+		if (stmt->intoClause)
+		{
+			CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
+
+			ctas->query = parseTree;
+			ctas->into = stmt->intoClause;
+			ctas->is_select_into = true;
+
+			/*
+			 * Remove the intoClause from the SelectStmt.  This makes it safe
+			 * for transformSelectStmt to complain if it finds intoClause set
+			 * (implying that the INTO appeared in a disallowed place).
+			 */
+			stmt->intoClause = NULL;
+
+			parseTree = (Node *) ctas;
+		}
+	}
+
+	return transformStmt(pstate, parseTree);
+}
+
+/*
+ * transformStmt -
+ *	  recursively transform a Parse tree into a Query tree.
  */
 Query *
 transformStmt(ParseState *pstate, Node *parseTree)
@@ -202,6 +248,11 @@ transformStmt(ParseState *pstate, Node *parseTree)
 										  (ExplainStmt *) parseTree);
 			break;
 
+		case T_CreateTableAsStmt:
+			result = transformCreateTableAsStmt(pstate,
+											(CreateTableAsStmt *) parseTree);
+			break;
+
 		default:
 
 			/*
@@ -258,6 +309,7 @@ analyze_requires_snapshot(Node *parseTree)
 			break;
 
 		case T_ExplainStmt:
+		case T_CreateTableAsStmt:
 			/* yes, because we must analyze the contained statement */
 			result = true;
 			break;
@@ -459,17 +511,11 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 
 		free_parsestate(sub_pstate);
 
-		/* The grammar should have produced a SELECT, but it might have INTO */
+		/* The grammar should have produced a SELECT */
 		if (!IsA(selectQuery, Query) ||
 			selectQuery->commandType != CMD_SELECT ||
 			selectQuery->utilityStmt != NULL)
 			elog(ERROR, "unexpected non-SELECT command in INSERT ... SELECT");
-		if (selectQuery->intoClause)
-			ereport(ERROR,
-					(errcode(ERRCODE_SYNTAX_ERROR),
-					 errmsg("INSERT ... SELECT cannot specify INTO"),
-					 parser_errposition(pstate,
-						   exprLocation((Node *) selectQuery->intoClause))));
 
 		/*
 		 * Make the source be a subquery in the INSERT's rangetable, and add
@@ -539,6 +585,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 		int			sublist_length = -1;
 		int			i;
 
+		Assert(selectStmt->intoClause == NULL);
+
 		foreach(lc, selectStmt->valuesLists)
 		{
 			List	   *sublist = (List *) lfirst(lc);
@@ -653,6 +701,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
 		List	   *valuesLists = selectStmt->valuesLists;
 
 		Assert(list_length(valuesLists) == 1);
+		Assert(selectStmt->intoClause == NULL);
 
 		/* Do basic expression transformation (same as a ROW() expr) */
 		exprList = transformExpressionList(pstate,
@@ -886,6 +935,14 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 		qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
 	}
 
+	/* Complain if we get called from someplace where INTO is not allowed */
+	if (stmt->intoClause)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("SELECT ... INTO is not allowed here"),
+				 parser_errposition(pstate,
+									exprLocation((Node *) stmt->intoClause))));
+
 	/* make FOR UPDATE/FOR SHARE info available to addRangeTableEntry */
 	pstate->p_locking_clause = stmt->lockingClause;
 
@@ -963,9 +1020,6 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 												   pstate->p_windowdefs,
 												   &qry->targetList);
 
-	/* SELECT INTO/CREATE TABLE AS spec is just passed through */
-	qry->intoClause = stmt->intoClause;
-
 	qry->rtable = pstate->p_rtable;
 	qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
 
@@ -1013,6 +1067,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
 
 	/* Most SELECT stuff doesn't apply in a VALUES clause */
 	Assert(stmt->distinctClause == NIL);
+	Assert(stmt->intoClause == NULL);
 	Assert(stmt->targetList == NIL);
 	Assert(stmt->fromClause == NIL);
 	Assert(stmt->whereClause == NULL);
@@ -1185,9 +1240,6 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 			 errmsg("SELECT FOR UPDATE/SHARE cannot be applied to VALUES")));
 
-	/* CREATE TABLE AS spec is just passed through */
-	qry->intoClause = stmt->intoClause;
-
 	/*
 	 * There mustn't have been any table references in the expressions, else
 	 * strange things would happen, like Cartesian products of those tables
@@ -1286,21 +1338,27 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 	}
 
 	/*
-	 * Find leftmost leaf SelectStmt; extract the one-time-only items from it
-	 * and from the top-level node.
+	 * Find leftmost leaf SelectStmt.  We currently only need to do this in
+	 * order to deliver a suitable error message if there's an INTO clause
+	 * there, implying the set-op tree is in a context that doesn't allow
+	 * INTO.  (transformSetOperationTree would throw error anyway, but it
+	 * seems worth the trouble to throw a different error for non-leftmost
+	 * INTO, so we produce that error in transformSetOperationTree.)
 	 */
 	leftmostSelect = stmt->larg;
 	while (leftmostSelect && leftmostSelect->op != SETOP_NONE)
 		leftmostSelect = leftmostSelect->larg;
 	Assert(leftmostSelect && IsA(leftmostSelect, SelectStmt) &&
 		   leftmostSelect->larg == NULL);
-	qry->intoClause = leftmostSelect->intoClause;
-
-	/* clear this to prevent complaints in transformSetOperationTree() */
-	leftmostSelect->intoClause = NULL;
+	if (leftmostSelect->intoClause)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("SELECT ... INTO is not allowed here"),
+				 parser_errposition(pstate,
+									exprLocation((Node *) leftmostSelect->intoClause))));
 
 	/*
-	 * These are not one-time, exactly, but we want to process them here and
+	 * We need to extract ORDER BY and other top-level clauses here and
 	 * not let transformSetOperationTree() see them --- else it'll just
 	 * recurse right back here!
 	 */
@@ -2107,14 +2165,6 @@ transformDeclareCursorStmt(ParseState *pstate, DeclareCursorStmt *stmt)
 		result->utilityStmt != NULL)
 		elog(ERROR, "unexpected non-SELECT command in DECLARE CURSOR");
 
-	/* But we must explicitly disallow DECLARE CURSOR ... SELECT INTO */
-	if (result->intoClause)
-		ereport(ERROR,
-				(errcode(ERRCODE_INVALID_CURSOR_DEFINITION),
-				 errmsg("DECLARE CURSOR cannot specify INTO"),
-				 parser_errposition(pstate,
-								exprLocation((Node *) result->intoClause))));
-
 	/*
 	 * We also disallow data-modifying WITH in a cursor.  (This could be
 	 * allowed, but the semantics of when the updates occur might be
@@ -2170,6 +2220,29 @@ transformExplainStmt(ParseState *pstate, ExplainStmt *stmt)
 {
 	Query	   *result;
 
+	/* transform contained query, allowing SELECT INTO */
+	stmt->query = (Node *) transformTopLevelStmt(pstate, stmt->query);
+
+	/* represent the command as a utility Query */
+	result = makeNode(Query);
+	result->commandType = CMD_UTILITY;
+	result->utilityStmt = (Node *) stmt;
+
+	return result;
+}
+
+
+/*
+ * transformCreateTableAsStmt -
+ *	transform a CREATE TABLE AS (or SELECT ... INTO) Statement
+ *
+ * As with EXPLAIN, transform the contained statement now.
+ */
+static Query *
+transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt)
+{
+	Query	   *result;
+
 	/* transform contained query */
 	stmt->query = (Node *) transformStmt(pstate, stmt->query);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index feb28a41720..3827e2e1add 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -124,7 +124,6 @@ static void check_qualified_name(List *names, core_yyscan_t yyscanner);
 static List *check_func_name(List *names, core_yyscan_t yyscanner);
 static List *check_indirection(List *indirection, core_yyscan_t yyscanner);
 static List *extractArgTypes(List *parameters);
-static SelectStmt *findLeftmostSelect(SelectStmt *node);
 static void insertSelectOptions(SelectStmt *stmt,
 								List *sortClause, List *lockingClause,
 								Node *limitOffset, Node *limitCount,
@@ -3044,31 +3043,27 @@ ExistingIndex:   USING INDEX index_name				{ $$ = $3; }
 		;
 
 
-/*
- * Note: CREATE TABLE ... AS SELECT ... is just another spelling for
- * SELECT ... INTO.
- */
+/*****************************************************************************
+ *
+ *		QUERY :
+ *				CREATE TABLE relname AS SelectStmt [ WITH [NO] DATA ]
+ *
+ *
+ * Note: SELECT ... INTO is a now-deprecated alternative for this.
+ *
+ *****************************************************************************/
 
 CreateAsStmt:
 		CREATE OptTemp TABLE create_as_target AS SelectStmt opt_with_data
 				{
-					/*
-					 * When the SelectStmt is a set-operation tree, we must
-					 * stuff the INTO information into the leftmost component
-					 * Select, because that's where analyze.c will expect
-					 * to find it.
-					 */
-					SelectStmt *n = findLeftmostSelect((SelectStmt *) $6);
-					if (n->intoClause != NULL)
-						ereport(ERROR,
-								(errcode(ERRCODE_SYNTAX_ERROR),
-								 errmsg("CREATE TABLE AS cannot specify INTO"),
-								 parser_errposition(exprLocation((Node *) n->intoClause))));
-					n->intoClause = $4;
+					CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
+					ctas->query = $6;
+					ctas->into = $4;
+					ctas->is_select_into = false;
 					/* cram additional flags into the IntoClause */
 					$4->rel->relpersistence = $2;
 					$4->skipData = !($7);
-					$$ = $6;
+					$$ = (Node *) ctas;
 				}
 		;
 
@@ -8285,20 +8280,22 @@ ExecuteStmt: EXECUTE name execute_param_clause
 					ExecuteStmt *n = makeNode(ExecuteStmt);
 					n->name = $2;
 					n->params = $3;
-					n->into = NULL;
 					$$ = (Node *) n;
 				}
 			| CREATE OptTemp TABLE create_as_target AS
 				EXECUTE name execute_param_clause opt_with_data
 				{
+					CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);
 					ExecuteStmt *n = makeNode(ExecuteStmt);
 					n->name = $7;
 					n->params = $8;
-					n->into = $4;
+					ctas->query = (Node *) n;
+					ctas->into = $4;
+					ctas->is_select_into = false;
 					/* cram additional flags into the IntoClause */
 					$4->rel->relpersistence = $2;
 					$4->skipData = !($9);
-					$$ = (Node *) n;
+					$$ = (Node *) ctas;
 				}
 		;
 
@@ -12870,18 +12867,6 @@ extractArgTypes(List *parameters)
 	return result;
 }
 
-/* findLeftmostSelect()
- * Find the leftmost component SelectStmt in a set-operation parsetree.
- */
-static SelectStmt *
-findLeftmostSelect(SelectStmt *node)
-{
-	while (node && node->op != SETOP_NONE)
-		node = node->larg;
-	Assert(node && IsA(node, SelectStmt) && node->larg == NULL);
-	return node;
-}
-
 /* insertSelectOptions()
  * Insert ORDER BY, etc into an already-constructed SelectStmt.
  *
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 7f4da921b2f..3a23cddd337 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -495,12 +495,6 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
 		query->commandType != CMD_SELECT ||
 		query->utilityStmt != NULL)
 		elog(ERROR, "unexpected non-SELECT command in subquery in FROM");
-	if (query->intoClause)
-		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("subquery in FROM cannot have SELECT INTO"),
-				 parser_errposition(pstate,
-								 exprLocation((Node *) query->intoClause))));
 
 	/*
 	 * The subquery cannot make use of any variables from FROM items created
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index c0c62401037..2a7d4cd0724 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -253,13 +253,6 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
 	if (query->utilityStmt != NULL)
 		elog(ERROR, "unexpected utility statement in WITH");
 
-	if (query->intoClause)
-		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("subquery in WITH cannot have SELECT INTO"),
-				 parser_errposition(pstate,
-								 exprLocation((Node *) query->intoClause))));
-
 	/*
 	 * We disallow data-modifying WITH except at the top level of a query,
 	 * because it's not clear when such a modification should be executed.
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d22d8a12bac..973265bcb0b 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1408,12 +1408,6 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
 		qtree->commandType != CMD_SELECT ||
 		qtree->utilityStmt != NULL)
 		elog(ERROR, "unexpected non-SELECT command in SubLink");
-	if (qtree->intoClause)
-		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("subquery cannot have SELECT INTO"),
-				 parser_errposition(pstate,
-								 exprLocation((Node *) qtree->intoClause))));
 
 	sublink->subselect = (Node *) qtree;