On Tue, 14 Feb 2006, Kris Jurka wrote:


This patch adds most of the options available for regular CREATE TABLE syntax to the CREATE TABLE x AS SELECT ... and AS EXECUTE ...

Here's the doc changes for this.

Kris Jurka
Index: doc/src/sgml/ref/create_table_as.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v
retrieving revision 1.31
diff -c -r1.31 create_table_as.sgml
*** doc/src/sgml/ref/create_table_as.sgml       1 Nov 2005 21:09:50 -0000       
1.31
--- doc/src/sgml/ref/create_table_as.sgml       14 Feb 2006 21:07:27 -0000
***************
*** 21,27 ****
   <refsynopsisdiv>
  <synopsis>
  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE 
<replaceable>table_name</replaceable>
!     [ (<replaceable>column_name</replaceable> [, ...] ) ] [ [ WITH | WITHOUT 
] OIDS ]
      AS <replaceable>query</replaceable>
  </synopsis>
   </refsynopsisdiv>
--- 21,30 ----
   <refsynopsisdiv>
  <synopsis>
  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE 
<replaceable>table_name</replaceable>
!     [ (<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,119 ****
--- 117,180 ----
     </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"> 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,175 ****
--- 231,250 ----
    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,202 ****
  
      <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
--- 265,270 ----
***************
*** 219,224 ****
--- 287,300 ----
        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>
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to