Extend COPY to support COPY (SELECT ...) TO ...

Bernd Helmle

2 files changed, 27 insertions, 22 deletions

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c1d87e601c2..cb1eaa08048 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.74 2006/04/22 03:03:11 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.75 2006/08/30 23:34:20 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -33,7 +33,7 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
                 [ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ]
                 [ FORCE NOT NULL <replaceable class="parameter">column</replaceable> [, ...] ]
 
-COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
+COPY { <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
     TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
     [ [ WITH ] 
           [ BINARY ]
@@ -57,7 +57,8 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
    files. <command>COPY TO</command> copies the contents of a table
    <emphasis>to</> a file, while <command>COPY FROM</command> copies
    data <emphasis>from</> a file to a table (appending the data to
-   whatever is in the table already).
+   whatever is in the table already).  <command>COPY TO</command>
+   can also copy the results of a <command>SELECT</> query.
   </para>
 
   <para>
@@ -97,7 +98,17 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
      <listitem>
      <para>
       An optional list of columns to be copied.  If no column list is
-      specified, all columns will be used.
+      specified, all columns of the table will be copied.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">query</replaceable></term>
+    <listitem>
+     <para>
+      A <command>SELECT</> query whose results are to be copied.
+      Note that parentheses are required around the query.
      </para>
     </listitem>
    </varlistentry>
@@ -148,7 +159,8 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
      <para>
       Specifies copying the OID for each row.  (An error is raised if
       <literal>OIDS</literal> is specified for a table that does not
-      have OIDs.)
+      have OIDs, or in the case of copying a <replaceable
+      class="parameter">query</replaceable>.)
      </para>
     </listitem>
    </varlistentry>
@@ -265,7 +277,7 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
 COPY <replaceable class="parameter">count</replaceable>
 </screen>
    The <replaceable class="parameter">count</replaceable> is the number
-   of rows inserted into or copied from the table.
+   of rows copied.
   </para>
  </refsect1>
 
@@ -274,7 +286,8 @@ COPY <replaceable class="parameter">count</replaceable>
 
    <para>
     <command>COPY</command> can only be used with plain tables, not
-    with views.
+    with views.  However, you can write <literal>COPY (SELECT * FROM
+    <replaceable class="parameter">viewname</replaceable>) TO ...</literal>.
    </para>
 
    <para>
@@ -320,8 +333,8 @@ COPY <replaceable class="parameter">count</replaceable>
     server in the case of <command>COPY TO</command>, but for
     <command>COPY FROM</command> you do have the option of reading from
     a file specified by a relative path. The path will be interpreted
-    relative to the working directory of the server process (somewhere below
-    the data directory), not the client's working directory.
+    relative to the working directory of the server process (normally
+    the cluster's data directory), not the client's working directory.
    </para>
 
    <para>
@@ -737,14 +750,9 @@ COPY country FROM '/usr1/proj/bray/sql/country_data';
   </para>
 
   <para>
-   To copy into a file just the countries whose names start with 'A'
-   using a temporary table which is automatically deleted:
+   To copy into a file just the countries whose names start with 'A':
 <programlisting>
-BEGIN;
-CREATE TEMP TABLE a_list_countries AS
-    SELECT * FROM country WHERE country_name LIKE 'A%';
-COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy';
-ROLLBACK;
+COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';
 </programlisting>
   </para>
 
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d6528d0bc10..acac4d3daf3 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.167 2006/08/29 22:25:04 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/psql-ref.sgml,v 1.168 2006/08/30 23:34:21 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -739,8 +739,7 @@ testdb=&gt;
       </varlistentry>
 
       <varlistentry>
-        <term><literal>\copy <replaceable class="parameter">table</replaceable>
-        [ ( <replaceable class="parameter">column_list</replaceable> ) ]
+        <term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
         { <literal>from</literal> | <literal>to</literal> }
         { <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
         [ with ]
@@ -779,9 +778,7 @@ testdb=&gt;
         </para>
 
         <para>
-        <literal>\copy <replaceable
-        class="parameter">table</replaceable> from <replaceable
-        class="parameter">stdin | stdout</replaceable></literal>
+        <literal>\copy ... from stdin | to stdout</literal>
         reads/writes based on the command input and output respectively.
         All rows are read from the same source that issued the command,
         continuing until <literal>\.</literal> is read or the stream