Add TABLESPACE and ON COMMIT clauses to CREATE TABLE AS. ON COMMIT is

required by the SQL standard, and TABLESPACE is useful functionality.
Patch from Kris Jurka, minor editorialization by Neil Conway.

8 files changed, 98 insertions, 29 deletions

diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index f0afdbba367..4fd43d7f496 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -10,7 +10,7 @@
  * Copyright (c) 2002-2005, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.47 2006/01/18 06:49:26 neilc Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.48 2006/02/19 00:04:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -196,6 +196,10 @@ ExecuteQuery(ExecuteStmt *stmt, ParamListInfo params,
 					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
 					 errmsg("prepared statement is not a SELECT")));
 		query->into = copyObject(stmt->into);
+		query->intoHasOids = stmt->into_has_oids;
+		query->intoOnCommit = stmt->into_on_commit;
+		if (stmt->into_tbl_space)
+			query->intoTableSpaceName = pstrdup(stmt->into_tbl_space);
 
 		MemoryContextSwitchTo(oldContext);
 	}
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index a0f9cfedd3f..99c3bca0a9a 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -26,7 +26,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.265 2006/01/12 21:48:53 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.266 2006/02/19 00:04:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -37,6 +37,7 @@
 #include "catalog/heap.h"
 #include "catalog/namespace.h"
 #include "commands/tablecmds.h"
+#include "commands/tablespace.h"
 #include "commands/trigger.h"
 #include "executor/execdebug.h"
 #include "executor/execdefs.h"
@@ -730,11 +731,20 @@ InitPlan(QueryDesc *queryDesc, bool explainOnly)
 	{
 		char	   *intoName;
 		Oid			namespaceId;
+		Oid			tablespaceId;
 		AclResult	aclresult;
 		Oid			intoRelationId;
 		TupleDesc	tupdesc;
 
 		/*
+		 * Check consistency of arguments
+		 */
+		if (parseTree->intoOnCommit != ONCOMMIT_NOOP && !parseTree->into->istemp)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+					 errmsg("ON COMMIT can only be used on temporary tables")));
+
+		/*
 		 * find namespace to create in, check permissions
 		 */
 		intoName = parseTree->into->relname;
@@ -747,13 +757,44 @@ InitPlan(QueryDesc *queryDesc, bool explainOnly)
 						   get_namespace_name(namespaceId));
 
 		/*
+		 * Select tablespace to use.  If not specified, use default_tablespace
+		 * (which may in turn default to database's default).
+		 */
+		if (parseTree->intoTableSpaceName)
+		{
+			tablespaceId = get_tablespace_oid(parseTree->intoTableSpaceName);
+			if (!OidIsValid(tablespaceId))
+				ereport(ERROR,
+						(errcode(ERRCODE_UNDEFINED_OBJECT),
+						 errmsg("tablespace \"%s\" does not exist",
+								parseTree->intoTableSpaceName)));
+		} else
+		{
+			tablespaceId = GetDefaultTablespace();
+			/* note InvalidOid is OK in this case */
+		}
+
+		/* Check permissions except when using the database's default */
+		if (OidIsValid(tablespaceId))
+		{
+			AclResult	aclresult;
+
+			aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
+											   ACL_CREATE);
+
+			if (aclresult != ACLCHECK_OK)
+				aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
+							   get_tablespace_name(tablespaceId));
+		}
+
+		/*
 		 * have to copy tupType to get rid of constraints
 		 */
 		tupdesc = CreateTupleDescCopy(tupType);
 
 		intoRelationId = heap_create_with_catalog(intoName,
 												  namespaceId,
-												  InvalidOid,
+												  tablespaceId,
 												  InvalidOid,
 												  GetUserId(),
 												  tupdesc,
@@ -761,7 +802,7 @@ InitPlan(QueryDesc *queryDesc, bool explainOnly)
 												  false,
 												  true,
 												  0,
-												  ONCOMMIT_NOOP,
+												  parseTree->intoOnCommit,
 												  allowSystemTableMods);
 
 		FreeTupleDesc(tupdesc);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 6578bf37afd..91f06df039a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.326 2006/02/04 19:06:46 adunstan Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.327 2006/02/19 00:04:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1662,6 +1662,8 @@ _copyQuery(Query *from)
 	COPY_SCALAR_FIELD(resultRelation);
 	COPY_NODE_FIELD(into);
 	COPY_SCALAR_FIELD(intoHasOids);
+	COPY_SCALAR_FIELD(intoOnCommit);
+	COPY_STRING_FIELD(intoTableSpaceName);
 	COPY_SCALAR_FIELD(hasAggs);
 	COPY_SCALAR_FIELD(hasSubLinks);
 	COPY_NODE_FIELD(rtable);
@@ -1729,6 +1731,8 @@ _copySelectStmt(SelectStmt *from)
 	COPY_NODE_FIELD(into);
 	COPY_NODE_FIELD(intoColNames);
 	COPY_SCALAR_FIELD(intoHasOids);
+	COPY_SCALAR_FIELD(intoOnCommit);
+	COPY_STRING_FIELD(intoTableSpaceName);
 	COPY_NODE_FIELD(targetList);
 	COPY_NODE_FIELD(fromClause);
 	COPY_NODE_FIELD(whereClause);
@@ -2631,6 +2635,10 @@ _copyExecuteStmt(ExecuteStmt *from)
 
 	COPY_STRING_FIELD(name);
 	COPY_NODE_FIELD(into);
+	COPY_SCALAR_FIELD(into_contains_oids);
+	COPY_SCALAR_FIELD(into_has_oids);
+	COPY_SCALAR_FIELD(into_on_commit);
+	COPY_STRING_FIELD(into_tbl_space);
 	COPY_NODE_FIELD(params);
 
 	return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index a9fdc95f6bb..9a2a5fd0d59 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -18,7 +18,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.262 2006/02/04 19:06:46 adunstan Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.263 2006/02/19 00:04:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -673,6 +673,8 @@ _equalQuery(Query *a, Query *b)
 	COMPARE_SCALAR_FIELD(resultRelation);
 	COMPARE_NODE_FIELD(into);
 	COMPARE_SCALAR_FIELD(intoHasOids);
+	COMPARE_SCALAR_FIELD(intoOnCommit);
+	COMPARE_STRING_FIELD(intoTableSpaceName);
 	COMPARE_SCALAR_FIELD(hasAggs);
 	COMPARE_SCALAR_FIELD(hasSubLinks);
 	COMPARE_NODE_FIELD(rtable);
@@ -732,6 +734,8 @@ _equalSelectStmt(SelectStmt *a, SelectStmt *b)
 	COMPARE_NODE_FIELD(into);
 	COMPARE_NODE_FIELD(intoColNames);
 	COMPARE_SCALAR_FIELD(intoHasOids);
+	COMPARE_SCALAR_FIELD(intoOnCommit);
+	COMPARE_STRING_FIELD(intoTableSpaceName);
 	COMPARE_NODE_FIELD(targetList);
 	COMPARE_NODE_FIELD(fromClause);
 	COMPARE_NODE_FIELD(whereClause);
@@ -1493,6 +1497,10 @@ _equalExecuteStmt(ExecuteStmt *a, ExecuteStmt *b)
 {
 	COMPARE_STRING_FIELD(name);
 	COMPARE_NODE_FIELD(into);
+	COMPARE_SCALAR_FIELD(into_contains_oids);
+	COMPARE_SCALAR_FIELD(into_has_oids);
+	COMPARE_SCALAR_FIELD(into_on_commit);
+	COMPARE_STRING_FIELD(into_tbl_space);
 	COMPARE_NODE_FIELD(params);
 
 	return true;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index d6d63ee096f..9884b0e4db4 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.267 2006/01/31 21:39:23 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.268 2006/02/19 00:04:26 neilc Exp $
  *
  * NOTES
  *	  Every node type that can appear in stored rules' parsetrees *must*
@@ -1374,6 +1374,8 @@ _outSelectStmt(StringInfo str, SelectStmt *node)
 	WRITE_NODE_FIELD(into);
 	WRITE_NODE_FIELD(intoColNames);
 	WRITE_ENUM_FIELD(intoHasOids, ContainsOids);
+	WRITE_ENUM_FIELD(intoOnCommit, OnCommitAction);
+	WRITE_STRING_FIELD(intoTableSpaceName);
 	WRITE_NODE_FIELD(targetList);
 	WRITE_NODE_FIELD(fromClause);
 	WRITE_NODE_FIELD(whereClause);
@@ -1504,6 +1506,9 @@ _outQuery(StringInfo str, Query *node)
 
 	WRITE_INT_FIELD(resultRelation);
 	WRITE_NODE_FIELD(into);
+	WRITE_BOOL_FIELD(intoHasOids);
+	WRITE_ENUM_FIELD(intoOnCommit, OnCommitAction);
+	WRITE_STRING_FIELD(intoTableSpaceName);
 	WRITE_BOOL_FIELD(hasAggs);
 	WRITE_BOOL_FIELD(hasSubLinks);
 	WRITE_NODE_FIELD(rtable);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index eb2886d8437..19cb6e9544f 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.183 2005/12/28 01:29:59 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.184 2006/02/19 00:04:26 neilc Exp $
  *
  * NOTES
  *	  Path and Plan nodes do not have any readfuncs support, because we
@@ -140,6 +140,9 @@ _readQuery(void)
 	READ_NODE_FIELD(utilityStmt);
 	READ_INT_FIELD(resultRelation);
 	READ_NODE_FIELD(into);
+	READ_BOOL_FIELD(intoHasOids);
+	READ_ENUM_FIELD(intoOnCommit, OnCommitAction);
+	READ_STRING_FIELD(intoTableSpaceName);
 	READ_BOOL_FIELD(hasAggs);
 	READ_BOOL_FIELD(hasSubLinks);
 	READ_NODE_FIELD(rtable);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index abfb0fbf303..057ff415185 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.328 2006/01/15 22:18:46 neilc Exp $
+ *	$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.329 2006/02/19 00:04:26 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1818,6 +1818,8 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 		applyColumnNames(qry->targetList, stmt->intoColNames);
 
 	qry->intoHasOids = interpretOidsOption(stmt->intoHasOids);
+	qry->intoOnCommit = stmt->intoOnCommit;
+	qry->intoTableSpaceName = stmt->intoTableSpaceName;
 
 	/* mark column origins */
 	markTargetListOrigins(pstate, qry->targetList);
@@ -2662,6 +2664,8 @@ transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt)
 
 	paramtypes = FetchPreparedStatementParams(stmt->name);
 
+	stmt->into_has_oids = interpretOidsOption(stmt->into_contains_oids);
+
 	if (stmt->params || paramtypes)
 	{
 		int			nparams = list_length(stmt->params);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 88d32d837d4..6cb6f96fa4f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.529 2006/02/12 19:11:01 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.530 2006/02/19 00:04:27 neilc Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -239,7 +239,7 @@ static void doNegateFloat(Value *v);
 
 %type <boolean>  TriggerForType OptTemp
 %type <oncommit> OnCommitOption
-%type <withoids> OptWithOids WithOidsAs
+%type <withoids> OptWithOids
 
 %type <node>	for_locking_clause opt_for_locking_clause
 %type <list>	locked_rels_list
@@ -2171,7 +2171,8 @@ OptConsTableSpace:   USING INDEX TABLESPACE name	{ $$ = $4; }
  */
 
 CreateAsStmt:
-			CREATE OptTemp TABLE qualified_name OptCreateAs WithOidsAs SelectStmt
+		CREATE OptTemp TABLE qualified_name OptCreateAs
+			OptWithOids OnCommitOption OptTableSpace AS SelectStmt
 				{
 					/*
 					 * When the SelectStmt is a set-operation tree, we must
@@ -2180,7 +2181,7 @@ CreateAsStmt:
 					 * to find it.	Similarly, the output column names must
 					 * be attached to that Select's target list.
 					 */
-					SelectStmt *n = findLeftmostSelect((SelectStmt *) $7);
+					SelectStmt *n = findLeftmostSelect((SelectStmt *) $10);
 					if (n->into != NULL)
 						ereport(ERROR,
 								(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2189,22 +2190,12 @@ CreateAsStmt:
 					n->into = $4;
 					n->intoColNames = $5;
 					n->intoHasOids = $6;
-					$$ = $7;
+					n->intoOnCommit = $7;
+					n->intoTableSpaceName = $8;
+					$$ = $10;
 				}
 		;
 
-/*
- * To avoid a shift/reduce conflict in CreateAsStmt, we need to
- * include the 'AS' terminal in the parsing of WITH/WITHOUT
- * OIDS. Unfortunately that means this production is effectively a
- * duplicate of OptWithOids.
- */
-WithOidsAs:
-			WITH OIDS AS 							{ $$ = MUST_HAVE_OIDS; }
-			| WITHOUT OIDS AS 						{ $$ = MUST_NOT_HAVE_OIDS; }
-			| AS 									{ $$ = DEFAULT_OIDS; }
-			;
-
 OptCreateAs:
 			'(' CreateAsList ')'					{ $$ = $2; }
 			| /*EMPTY*/								{ $$ = NIL; }
@@ -5066,13 +5057,18 @@ ExecuteStmt: EXECUTE name execute_param_clause
 					n->into = NULL;
 					$$ = (Node *) n;
 				}
-			| CREATE OptTemp TABLE qualified_name OptCreateAs AS EXECUTE name execute_param_clause
+			| CREATE OptTemp TABLE qualified_name OptCreateAs
+				OptWithOids OnCommitOption OptTableSpace AS
+				EXECUTE name execute_param_clause
 				{
 					ExecuteStmt *n = makeNode(ExecuteStmt);
-					n->name = $8;
-					n->params = $9;
+					n->name = $11;
+					n->params = $12;
 					$4->istemp = $2;
 					n->into = $4;
+					n->into_contains_oids = $6;
+					n->into_on_commit = $7;
+					n->into_tbl_space = $8;
 					if ($5)
 						ereport(ERROR,
 								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),