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 >= '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 > $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
