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.

2 files changed, 91 insertions, 13 deletions

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 5dfeca1953c..a4cdc250187 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.99 2006/01/16 20:48:49 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.100 2006/02/19 00:04:26 neilc Exp $
 PostgreSQL documentation
 -->
 
@@ -580,9 +580,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
         <term><literal>DELETE ROWS</literal></term>
         <listitem>
          <para>
-          All rows in the temporary table will be deleted at the
-          end of each transaction block.  Essentially, an automatic
-          <xref linkend="sql-truncate"> is done at each commit.
+          All rows in the temporary table will be deleted at the end
+          of each transaction block.  Essentially, an automatic <xref
+          linkend="sql-truncate" endterm="sql-truncate-title"> is done
+          at each commit.
          </para>
         </listitem>
        </varlistentry>
diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml
index 201188d99cc..7e7fa7b6731 100644
--- a/doc/src/sgml/ref/create_table_as.sgml
+++ b/doc/src/sgml/ref/create_table_as.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.31 2005/11/01 21:09:50 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.32 2006/02/19 00:04:26 neilc Exp $
 PostgreSQL documentation
 -->
 
@@ -21,7 +21,10 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable>
-    [ (<replaceable>column_name</replaceable> [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
+    [ (<replaceable>column_name</replaceable> [, ...] ) ]
+    [ WITH OIDS | WITHOUT OIDS ]
+    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
+    [ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
     AS <replaceable>query</replaceable>
 </synopsis>
  </refsynopsisdiv>
@@ -114,6 +117,65 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
    </varlistentry>
 
    <varlistentry>
+    <term><literal>ON COMMIT</literal></term>
+    <listitem>
+     <para>
+      The behavior of temporary tables at the end of a transaction
+      block can be controlled using <literal>ON COMMIT</literal>.
+      The three options are:
+
+      <variablelist>
+       <varlistentry>
+        <term><literal>PRESERVE ROWS</literal></term>
+        <listitem>
+         <para>
+          No special action is taken at the ends of transactions.
+          This is the default behavior.
+         </para>
+        </listitem>
+       </varlistentry>
+
+       <varlistentry>
+        <term><literal>DELETE ROWS</literal></term>
+        <listitem>
+         <para>
+          All rows in the temporary table will be deleted at the end
+          of each transaction block.  Essentially, an automatic <xref
+          linkend="sql-truncate" endterm="sql-truncate-title"> is done
+          at each commit.
+         </para>
+        </listitem>
+       </varlistentry>
+
+       <varlistentry>
+        <term><literal>DROP</literal></term>
+        <listitem>
+         <para>
+          The temporary table will be dropped at the end of the current
+          transaction block.
+         </para>
+        </listitem>
+       </varlistentry>
+      </variablelist>
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term>
+    <listitem>
+     <para>
+      The <replaceable class="PARAMETER">tablespace</replaceable> is the name
+      of the tablespace in which the new table is to be created.
+      If not specified,
+      <xref linkend="guc-default-tablespace"> is used, or the database's
+      default tablespace if <varname>default_tablespace</> is an empty
+      string.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable>query</replaceable></term>
     <listitem>
      <para>
@@ -170,6 +232,20 @@ CREATE TABLE films_recent AS
   SELECT * FROM films WHERE date_prod &gt;= '2002-01-01';
 </programlisting>
   </para>
+
+  <para>
+   Create a new temporary table that will be dropped at commit
+   <literal>films_recent</literal> with oids consisting of only
+   recent entries from the table <literal>films</literal> using a
+   prepared statement:
+
+<programlisting>
+PREPARE recentfilms(date) AS
+  SELECT * FROM films WHERE date_prod &gt; $1;
+CREATE TEMP TABLE films_recent WITH OIDS ON COMMIT DROP AS
+  EXECUTE recentfilms('2002-01-01');
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -190,13 +266,6 @@ CREATE TABLE films_recent AS
 
     <listitem>
      <para>
-      The standard defines an <literal>ON COMMIT</literal> clause;
-      this is not currently implemented by <productname>PostgreSQL</>.
-     </para>
-    </listitem>
-
-    <listitem>
-     <para>
       The standard defines a <literal>WITH [ NO ] DATA</literal> clause;
       this is not currently implemented by <productname>PostgreSQL</>.
       The behavior provided by <productname>PostgreSQL</> is equivalent
@@ -219,6 +288,14 @@ CREATE TABLE films_recent AS
       for details.
      </para>
     </listitem>
+
+    <listitem>
+     <para>
+      The <productname>PostgreSQL</productname> concept of tablespaces is not
+      part of the standard.  Hence, the clause <literal>TABLESPACE</literal>
+      is an extension.
+     </para>
+    </listitem>
    </itemizedlist>
   </para>
  </refsect1>