diff options
Diffstat (limited to 'doc/src/sgml/ref/create_table_as.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_table_as.sgml | 95 |
1 files changed, 86 insertions, 9 deletions
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 >= '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,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> |