This patch adds most of the options available for regular CREATE TABLE syntax to the CREATE TABLE x AS SELECT ... and AS EXECUTE ... Specifically this allows specification of on commit behavior for temp tables and tablespaces for regular tables to these two statements. Additionally with/without oids is now available for the EXECUTE variant. Currently you still cannot specify inheritance attributes with these commands, but this seems like a more complicated task.

Kris Jurka
? GNUmakefile
? config.log
? config.status
? log
? contrib/spi/.deps
? src/Makefile.global
? src/backend/postgres
? src/backend/access/common/.deps
? src/backend/access/gist/.deps
? src/backend/access/hash/.deps
? src/backend/access/heap/.deps
? src/backend/access/index/.deps
? src/backend/access/nbtree/.deps
? src/backend/access/transam/.deps
? src/backend/bootstrap/.deps
? src/backend/catalog/.deps
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/catalog/postgres.shdescription
? src/backend/commands/.deps
? src/backend/executor/.deps
? src/backend/lib/.deps
? src/backend/libpq/.deps
? src/backend/main/.deps
? src/backend/nodes/.deps
? src/backend/optimizer/geqo/.deps
? src/backend/optimizer/path/.deps
? src/backend/optimizer/plan/.deps
? src/backend/optimizer/prep/.deps
? src/backend/optimizer/util/.deps
? src/backend/parser/.deps
? src/backend/port/.deps
? src/backend/postmaster/.deps
? src/backend/regex/.deps
? src/backend/rewrite/.deps
? src/backend/storage/buffer/.deps
? src/backend/storage/file/.deps
? src/backend/storage/freespace/.deps
? src/backend/storage/ipc/.deps
? src/backend/storage/large_object/.deps
? src/backend/storage/lmgr/.deps
? src/backend/storage/page/.deps
? src/backend/storage/smgr/.deps
? src/backend/tcop/.deps
? src/backend/utils/.deps
? src/backend/utils/adt/.deps
? src/backend/utils/cache/.deps
? src/backend/utils/error/.deps
? src/backend/utils/fmgr/.deps
? src/backend/utils/hash/.deps
? src/backend/utils/init/.deps
? src/backend/utils/mb/.deps
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
? 
src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
? 
src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
? 
src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
? 
src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1252/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_win1252/libutf8_and_win1252.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1258/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_win1258/libutf8_and_win1258.so.0.0
? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
? 
src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0.0
? src/backend/utils/misc/.deps
? src/backend/utils/mmgr/.deps
? src/backend/utils/resowner/.deps
? src/backend/utils/sort/.deps
? src/backend/utils/time/.deps
? src/bin/initdb/.deps
? src/bin/initdb/initdb
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/.deps
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/.deps
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/.deps
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/.deps
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_resetxlog/.deps
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/psql/.deps
? src/bin/psql/psql
? src/bin/scripts/.deps
? src/bin/scripts/clusterdb
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/reindexdb
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/.deps
? src/interfaces/ecpg/compatlib/libecpg_compat.so.2.2
? src/interfaces/ecpg/ecpglib/.deps
? src/interfaces/ecpg/ecpglib/libecpg.so.5.2
? src/interfaces/ecpg/pgtypeslib/.deps
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.2.2
? src/interfaces/ecpg/preproc/.deps
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpq/.deps
? src/interfaces/libpq/libpq.so.4.2
? src/pl/plpgsql/src/.deps
? src/pl/plpgsql/src/libplpgsql.so.1.0
? src/port/.deps
? src/port/pg_config_paths.h
? src/test/regress/.deps
? src/test/regress/libregress.so.0.0
? src/test/regress/log
? src/test/regress/pg_regress
? src/test/regress/results
? src/test/regress/testtablespace
? src/test/regress/tmp_check
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/misc.out
? src/test/regress/expected/tablespace.out
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/misc.sql
? src/test/regress/sql/tablespace.sql
? src/timezone/.deps
? src/timezone/zic
Index: src/backend/commands/prepare.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/prepare.c,v
retrieving revision 1.47
diff -c -r1.47 prepare.c
*** src/backend/commands/prepare.c      18 Jan 2006 06:49:26 -0000      1.47
--- src/backend/commands/prepare.c      14 Feb 2006 19:18:41 -0000
***************
*** 196,201 ****
--- 196,207 ----
                                        (errcode(ERRCODE_WRONG_OBJECT_TYPE),
                                         errmsg("prepared statement is not a 
SELECT")));
                query->into = copyObject(stmt->into);
+               query->intoHasOids = stmt->intohasoids;
+               query->intoOnCommit = stmt->intooncommit;
+               if (stmt->intotablespacename)
+                       query->intoTableSpaceName = 
pstrdup(stmt->intotablespacename);
+               else
+                       query->intoTableSpaceName = NULL;
  
                MemoryContextSwitchTo(oldContext);
        }
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.265
diff -c -r1.265 execMain.c
*** src/backend/executor/execMain.c     12 Jan 2006 21:48:53 -0000      1.265
--- src/backend/executor/execMain.c     14 Feb 2006 19:18:41 -0000
***************
*** 37,42 ****
--- 37,43 ----
  #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,740 ****
--- 731,750 ----
        {
                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,759 ****
                                                   
get_namespace_name(namespaceId));
  
                /*
                 * have to copy tupType to get rid of constraints
                 */
                tupdesc = CreateTupleDescCopy(tupType);
  
                intoRelationId = heap_create_with_catalog(intoName,
                                                                                
                  namespaceId,
!                                                                               
                  InvalidOid,
                                                                                
                  InvalidOid,
                                                                                
                  GetUserId(),
                                                                                
                  tupdesc,
--- 757,798 ----
                                                   
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,
!                                                                               
                  tablespaceId,
                                                                                
                  InvalidOid,
                                                                                
                  GetUserId(),
                                                                                
                  tupdesc,
***************
*** 761,767 ****
                                                                                
                  false,
                                                                                
                  true,
                                                                                
                  0,
!                                                                               
                  ONCOMMIT_NOOP,
                                                                                
                  allowSystemTableMods);
  
                FreeTupleDesc(tupdesc);
--- 800,806 ----
                                                                                
                  false,
                                                                                
                  true,
                                                                                
                  0,
!                                                                               
                  parseTree->intoOnCommit,
                                                                                
                  allowSystemTableMods);
  
                FreeTupleDesc(tupdesc);
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.326
diff -c -r1.326 copyfuncs.c
*** src/backend/nodes/copyfuncs.c       4 Feb 2006 19:06:46 -0000       1.326
--- src/backend/nodes/copyfuncs.c       14 Feb 2006 19:18:41 -0000
***************
*** 1662,1667 ****
--- 1662,1669 ----
        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,1734 ****
--- 1731,1738 ----
        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,2636 ****
--- 2635,2644 ----
  
        COPY_STRING_FIELD(name);
        COPY_NODE_FIELD(into);
+       COPY_SCALAR_FIELD(intohasoids);
+       COPY_SCALAR_FIELD(intocontainsoids);
+       COPY_SCALAR_FIELD(intooncommit);
+       COPY_STRING_FIELD(intotablespacename);
        COPY_NODE_FIELD(params);
  
        return newnode;
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.262
diff -c -r1.262 equalfuncs.c
*** src/backend/nodes/equalfuncs.c      4 Feb 2006 19:06:46 -0000       1.262
--- src/backend/nodes/equalfuncs.c      14 Feb 2006 19:18:41 -0000
***************
*** 673,678 ****
--- 673,680 ----
        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,737 ****
--- 734,741 ----
        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,1498 ****
--- 1497,1506 ----
  {
        COMPARE_STRING_FIELD(name);
        COMPARE_NODE_FIELD(into);
+       COMPARE_SCALAR_FIELD(intocontainsoids);
+       COMPARE_SCALAR_FIELD(intohasoids);
+       COMPARE_SCALAR_FIELD(intooncommit);
+       COMPARE_STRING_FIELD(intotablespacename);
        COMPARE_NODE_FIELD(params);
  
        return true;
Index: src/backend/nodes/outfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/nodes/outfuncs.c,v
retrieving revision 1.267
diff -c -r1.267 outfuncs.c
*** src/backend/nodes/outfuncs.c        31 Jan 2006 21:39:23 -0000      1.267
--- src/backend/nodes/outfuncs.c        14 Feb 2006 19:18:41 -0000
***************
*** 1374,1379 ****
--- 1374,1381 ----
        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,1509 ****
--- 1506,1514 ----
  
        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);
Index: src/backend/nodes/readfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/nodes/readfuncs.c,v
retrieving revision 1.183
diff -c -r1.183 readfuncs.c
*** src/backend/nodes/readfuncs.c       28 Dec 2005 01:29:59 -0000      1.183
--- src/backend/nodes/readfuncs.c       14 Feb 2006 19:18:41 -0000
***************
*** 140,145 ****
--- 140,148 ----
        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);
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.328
diff -c -r1.328 analyze.c
*** src/backend/parser/analyze.c        15 Jan 2006 22:18:46 -0000      1.328
--- src/backend/parser/analyze.c        14 Feb 2006 19:18:41 -0000
***************
*** 1818,1823 ****
--- 1818,1828 ----
                applyColumnNames(qry->targetList, stmt->intoColNames);
  
        qry->intoHasOids = interpretOidsOption(stmt->intoHasOids);
+       qry->intoOnCommit = stmt->intoOnCommit;
+       if (stmt->intoTableSpaceName)
+               qry->intoTableSpaceName = pstrdup(stmt->intoTableSpaceName);
+       else
+               qry->intoTableSpaceName = NULL;
  
        /* mark column origins */
        markTargetListOrigins(pstate, qry->targetList);
***************
*** 2662,2667 ****
--- 2667,2674 ----
  
        paramtypes = FetchPreparedStatementParams(stmt->name);
  
+       stmt->intohasoids = interpretOidsOption(stmt->intocontainsoids);
+ 
        if (stmt->params || paramtypes)
        {
                int                     nparams = list_length(stmt->params);
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.529
diff -c -r2.529 gram.y
*** src/backend/parser/gram.y   12 Feb 2006 19:11:01 -0000      2.529
--- src/backend/parser/gram.y   14 Feb 2006 19:18:41 -0000
***************
*** 239,245 ****
  
  %type <boolean>  TriggerForType OptTemp
  %type <oncommit> OnCommitOption
! %type <withoids> OptWithOids WithOidsAs
  
  %type <node>  for_locking_clause opt_for_locking_clause
  %type <list>  locked_rels_list
--- 239,245 ----
  
  %type <boolean>  TriggerForType OptTemp
  %type <oncommit> OnCommitOption
! %type <withoids> OptWithOids
  
  %type <node>  for_locking_clause opt_for_locking_clause
  %type <list>  locked_rels_list
***************
*** 2171,2177 ****
   */
  
  CreateAsStmt:
!                       CREATE OptTemp TABLE qualified_name OptCreateAs 
WithOidsAs SelectStmt
                                {
                                        /*
                                         * When the SelectStmt is a 
set-operation tree, we must
--- 2171,2178 ----
   */
  
  CreateAsStmt:
!               CREATE OptTemp TABLE qualified_name OptCreateAs
!                       OptWithOids OnCommitOption OptTableSpace AS SelectStmt
                                {
                                        /*
                                         * When the SelectStmt is a 
set-operation tree, we must
***************
*** 2180,2186 ****
                                         * to find it.  Similarly, the output 
column names must
                                         * be attached to that Select's target 
list.
                                         */
!                                       SelectStmt *n = 
findLeftmostSelect((SelectStmt *) $7);
                                        if (n->into != NULL)
                                                ereport(ERROR,
                                                                
(errcode(ERRCODE_SYNTAX_ERROR),
--- 2181,2187 ----
                                         * to find it.  Similarly, the output 
column names must
                                         * be attached to that Select's target 
list.
                                         */
!                                       SelectStmt *n = 
findLeftmostSelect((SelectStmt *) $10);
                                        if (n->into != NULL)
                                                ereport(ERROR,
                                                                
(errcode(ERRCODE_SYNTAX_ERROR),
***************
*** 2189,2210 ****
                                        n->into = $4;
                                        n->intoColNames = $5;
                                        n->intoHasOids = $6;
!                                       $$ = $7;
                                }
                ;
  
- /*
-  * 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; }
--- 2190,2201 ----
                                        n->into = $4;
                                        n->intoColNames = $5;
                                        n->intoHasOids = $6;
!                                       n->intoOnCommit = $7;
!                                       n->intoTableSpaceName = $8;
!                                       $$ = $10;
                                }
                ;
  
  OptCreateAs:
                        '(' CreateAsList ')'                                    
{ $$ = $2; }
                        | /*EMPTY*/                                             
                { $$ = NIL; }
***************
*** 5066,5078 ****
                                        n->into = NULL;
                                        $$ = (Node *) n;
                                }
!                       | CREATE OptTemp TABLE qualified_name OptCreateAs AS 
EXECUTE name execute_param_clause
                                {
                                        ExecuteStmt *n = makeNode(ExecuteStmt);
!                                       n->name = $8;
!                                       n->params = $9;
                                        $4->istemp = $2;
                                        n->into = $4;
                                        if ($5)
                                                ereport(ERROR,
                                                                
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
--- 5057,5074 ----
                                        n->into = NULL;
                                        $$ = (Node *) n;
                                }
!                       | CREATE OptTemp TABLE qualified_name OptCreateAs
!                               OptWithOids OnCommitOption OptTableSpace AS
!                               EXECUTE name execute_param_clause
                                {
                                        ExecuteStmt *n = makeNode(ExecuteStmt);
!                                       n->name = $11;
!                                       n->params = $12;
                                        $4->istemp = $2;
                                        n->into = $4;
+                                       n->intocontainsoids = $6;
+                                       n->intooncommit = $7;
+                                       n->intotablespacename = $8;
                                        if ($5)
                                                ereport(ERROR,
                                                                
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.300
diff -c -r1.300 parsenodes.h
*** src/include/nodes/parsenodes.h      4 Feb 2006 19:06:46 -0000       1.300
--- src/include/nodes/parsenodes.h      14 Feb 2006 19:18:41 -0000
***************
*** 27,32 ****
--- 27,42 ----
        QSRC_NON_INSTEAD_RULE           /* added by non-INSTEAD rule */
  } QuerySource;
  
+ /* What to do at commit time for temporary relations */
+ typedef enum OnCommitAction
+ {
+       ONCOMMIT_NOOP,                          /* No ON COMMIT clause (do 
nothing) */
+       ONCOMMIT_PRESERVE_ROWS,         /* ON COMMIT PRESERVE ROWS (do nothing) 
*/
+       ONCOMMIT_DELETE_ROWS,           /* ON COMMIT DELETE ROWS */
+       ONCOMMIT_DROP                           /* ON COMMIT DROP */
+ } OnCommitAction;
+ 
+ 
  /*
   * Grantable rights are encoded so that we can OR them together in a bitmask.
   * The present representation of AclItem limits us to 16 distinct rights,
***************
*** 82,87 ****
--- 92,99 ----
  
        RangeVar   *into;                       /* target relation for SELECT 
INTO */
        bool            intoHasOids;    /* should target relation contain OIDs? 
*/
+       OnCommitAction  intoOnCommit;           /* what do we do at COMMIT? */
+       char               *intoTableSpaceName; /* table space to use, or NULL 
*/
  
        bool            hasAggs;                /* has aggregates in tlist or 
havingQual */
        bool            hasSubLinks;    /* has subquery SubLink */
***************
*** 682,687 ****
--- 694,701 ----
        RangeVar   *into;                       /* target table (for select 
into table) */
        List       *intoColNames;       /* column names for into table */
        ContainsOids intoHasOids;       /* should target table have OIDs? */
+       OnCommitAction  intoOnCommit;           /* what do we do at COMMIT? */
+       char       *intoTableSpaceName;         /* table space to use, or NULL 
*/
        List       *targetList;         /* the target list (of ResTarget) */
        List       *fromClause;         /* the FROM clause */
        Node       *whereClause;        /* WHERE qualification */
***************
*** 976,990 ****
   * ----------------------
   */
  
- /* What to do at commit time for temporary relations */
- typedef enum OnCommitAction
- {
-       ONCOMMIT_NOOP,                          /* No ON COMMIT clause (do 
nothing) */
-       ONCOMMIT_PRESERVE_ROWS,         /* ON COMMIT PRESERVE ROWS (do nothing) 
*/
-       ONCOMMIT_DELETE_ROWS,           /* ON COMMIT DELETE ROWS */
-       ONCOMMIT_DROP                           /* ON COMMIT DROP */
- } OnCommitAction;
- 
  typedef struct CreateStmt
  {
        NodeTag         type;
--- 990,995 ----
***************
*** 1862,1871 ****
  
  typedef struct ExecuteStmt
  {
!       NodeTag         type;
!       char       *name;                       /* The name of the plan to 
execute */
!       RangeVar   *into;                       /* Optional table to store 
results in */
!       List       *params;                     /* Values to assign to 
parameters */
  } ExecuteStmt;
  
  
--- 1867,1880 ----
  
  typedef struct ExecuteStmt
  {
!       NodeTag                 type;
!       char               *name;                               /* The name of 
the plan to execute */
!       RangeVar           *into;                               /* Optional 
table to store results in */
!       ContainsOids    intocontainsoids;       /* should it have OIDs? */
!       bool                    intohasoids;            /* merge GUC info with 
user input */
!       OnCommitAction  intooncommit;           /* what do we do at COMMIT? */
!       char               *intotablespacename; /* table space to use, or NULL 
*/
!       List               *params;                             /* Values to 
assign to parameters */
  } ExecuteStmt;
  
  
Index: src/test/regress/expected/temp.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/temp.out,v
retrieving revision 1.10
diff -c -r1.10 temp.out
*** src/test/regress/expected/temp.out  27 Jan 2005 03:19:08 -0000      1.10
--- src/test/regress/expected/temp.out  14 Feb 2006 19:18:42 -0000
***************
*** 64,69 ****
--- 64,84 ----
  (0 rows)
  
  DROP TABLE temptest;
+ BEGIN;
+ CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
+ SELECT * FROM temptest;
+  col 
+ -----
+    1
+ (1 row)
+ 
+ COMMIT;
+ SELECT * FROM temptest;
+  col 
+ -----
+ (0 rows)
+ 
+ DROP TABLE temptest;
  -- Test ON COMMIT DROP
  BEGIN;
  CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
***************
*** 79,87 ****
--- 94,115 ----
  COMMIT;
  SELECT * FROM temptest;
  ERROR:  relation "temptest" does not exist
+ BEGIN;
+ CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1;
+ SELECT * FROM temptest;
+  col 
+ -----
+    1
+ (1 row)
+ 
+ COMMIT;
+ SELECT * FROM temptest;
+ ERROR:  relation "temptest" does not exist
  -- ON COMMIT is only allowed for TEMP
  CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
  ERROR:  ON COMMIT can only be used on temporary tables
+ CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
+ ERROR:  ON COMMIT can only be used on temporary tables
  -- Test foreign keys
  BEGIN;
  CREATE TEMP TABLE temptest1(col int PRIMARY KEY);
Index: src/test/regress/expected/without_oid.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/without_oid.out,v
retrieving revision 1.5
diff -c -r1.5 without_oid.out
*** src/test/regress/expected/without_oid.out   4 Aug 2004 21:34:33 -0000       
1.5
--- src/test/regress/expected/without_oid.out   14 Feb 2006 19:18:42 -0000
***************
*** 76,81 ****
--- 76,96 ----
  -- should fail
  SELECT count(oid) FROM create_table_test3;
  ERROR:  column "oid" does not exist
+ PREPARE table_source(int) AS
+     SELECT a + b AS c1, a - b AS c2, $1 AS c3 FROM create_table_test;
+ CREATE TABLE execute_with WITH OIDS AS EXECUTE table_source(1);
+ CREATE TABLE execute_without WITHOUT OIDS AS EXECUTE table_source(2);
+ SELECT count(oid) FROM execute_with;
+  count 
+ -------
+      2
+ (1 row)
+ 
+ -- should fail
+ SELECT count(oid) FROM execute_without;
+ ERROR:  column "oid" does not exist
  DROP TABLE create_table_test;
  DROP TABLE create_table_test2;
  DROP TABLE create_table_test3;
+ DROP TABLE execute_with;
+ DROP TABLE execute_without;
Index: src/test/regress/input/tablespace.source
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/input/tablespace.source,v
retrieving revision 1.2
diff -c -r1.2 tablespace.source
*** src/test/regress/input/tablespace.source    5 Nov 2004 19:16:46 -0000       
1.2
--- src/test/regress/input/tablespace.source    14 Feb 2006 19:18:42 -0000
***************
*** 12,17 ****
--- 12,28 ----
  INSERT INTO testschema.foo VALUES(1);
  INSERT INTO testschema.foo VALUES(2);
  
+ -- tables from dynamic sources
+ CREATE TABLE testschema.asselect TABLESPACE testspace AS SELECT 1;
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+     where c.reltablespace = t.oid AND c.relname = 'asselect';
+ 
+ PREPARE selectsource(int) AS SELECT $1;
+ CREATE TABLE testschema.asexecute TABLESPACE testspace
+     AS EXECUTE selectsource(2);
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+     where c.reltablespace = t.oid AND c.relname = 'asexecute';
+ 
  -- index
  CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
  SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
Index: src/test/regress/output/tablespace.source
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/output/tablespace.source,v
retrieving revision 1.2
diff -c -r1.2 tablespace.source
*** src/test/regress/output/tablespace.source   5 Nov 2004 19:17:13 -0000       
1.2
--- src/test/regress/output/tablespace.source   14 Feb 2006 19:18:42 -0000
***************
*** 13,18 ****
--- 13,37 ----
  
  INSERT INTO testschema.foo VALUES(1);
  INSERT INTO testschema.foo VALUES(2);
+ -- tables from dynamic sources
+ CREATE TABLE testschema.asselect TABLESPACE testspace AS SELECT 1;
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+     where c.reltablespace = t.oid AND c.relname = 'asselect';
+  relname  |  spcname  
+ ----------+-----------
+  asselect | testspace
+ (1 row)
+ 
+ PREPARE selectsource(int) AS SELECT $1;
+ CREATE TABLE testschema.asexecute TABLESPACE testspace
+     AS EXECUTE selectsource(2);
+ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
+     where c.reltablespace = t.oid AND c.relname = 'asexecute';
+   relname  |  spcname  
+ -----------+-----------
+  asexecute | testspace
+ (1 row)
+ 
  -- index
  CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
  SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
***************
*** 32,37 ****
--- 51,58 ----
  DROP TABLESPACE testspace;
  ERROR:  tablespace "testspace" is not empty
  DROP SCHEMA testschema CASCADE;
+ NOTICE:  drop cascades to table testschema.asexecute
+ NOTICE:  drop cascades to table testschema.asselect
  NOTICE:  drop cascades to table testschema.foo
  -- Should succeed
  DROP TABLESPACE testspace;
Index: src/test/regress/sql/temp.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/temp.sql,v
retrieving revision 1.6
diff -c -r1.6 temp.sql
*** src/test/regress/sql/temp.sql       27 Jan 2005 03:19:37 -0000      1.6
--- src/test/regress/sql/temp.sql       14 Feb 2006 19:18:42 -0000
***************
*** 66,71 ****
--- 66,81 ----
  
  DROP TABLE temptest;
  
+ BEGIN;
+ CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
+ 
+ SELECT * FROM temptest;
+ COMMIT;
+ 
+ SELECT * FROM temptest;
+ 
+ DROP TABLE temptest;
+ 
  -- Test ON COMMIT DROP
  
  BEGIN;
***************
*** 80,88 ****
--- 90,107 ----
  
  SELECT * FROM temptest;
  
+ BEGIN;
+ CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1;
+ 
+ SELECT * FROM temptest;
+ COMMIT;
+ 
+ SELECT * FROM temptest;
+ 
  -- ON COMMIT is only allowed for TEMP
  
  CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
+ CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1;
  
  -- Test foreign keys
  BEGIN;
Index: src/test/regress/sql/without_oid.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/without_oid.sql,v
retrieving revision 1.5
diff -c -r1.5 without_oid.sql
*** src/test/regress/sql/without_oid.sql        4 Aug 2004 21:34:35 -0000       
1.5
--- src/test/regress/sql/without_oid.sql        14 Feb 2006 19:18:42 -0000
***************
*** 74,79 ****
--- 74,91 ----
  -- should fail
  SELECT count(oid) FROM create_table_test3;
  
+ PREPARE table_source(int) AS
+     SELECT a + b AS c1, a - b AS c2, $1 AS c3 FROM create_table_test;
+ 
+ CREATE TABLE execute_with WITH OIDS AS EXECUTE table_source(1);
+ CREATE TABLE execute_without WITHOUT OIDS AS EXECUTE table_source(2);
+ 
+ SELECT count(oid) FROM execute_with;
+ -- should fail
+ SELECT count(oid) FROM execute_without;
+ 
  DROP TABLE create_table_test;
  DROP TABLE create_table_test2;
  DROP TABLE create_table_test3;
+ DROP TABLE execute_with;
+ DROP TABLE execute_without;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to