Re: generic copy options - Mailing list pgsql-hackers
| From | Emmanuel Cecchet |
|---|---|
| Subject | Re: generic copy options |
| Date | |
| Msg-id | 4AB2A367.5010100@asterdata.com Whole thread Raw |
| In response to | Re: generic copy options (Dan Colish <dan@unencrypted.org>) |
| Responses |
Re: generic copy options
Re: generic copy options Re: generic copy options Re: generic copy options |
| List | pgsql-hackers |
Here you go. Emmanuel Dan Colish wrote: > On Thu, Sep 17, 2009 at 02:56:07PM -0400, Emmanuel Cecchet wrote: > >> Dan, >> >> My bad, I copy/pasted the hard link in output/copy.source instead of >> @abs_builddir@. >> Here is a complete version of the patch with the fix on output/copy.source. >> >> Emmanuel >> > > Emmanuel, > > Thanks for getting that back so quickly. As I said before, it > applies cleanly and passes regression tests. I'm reading through the > changes now. When you get a moment could you send me the patch as a > context diff? > > -- > --Dan > -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com Index: src/test/regress/sql/copy2.sql =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/sql/copy2.sql,v retrieving revision 1.18 diff -c -r1.18 copy2.sql *** src/test/regress/sql/copy2.sql 25 Jul 2009 00:07:14 -0000 1.18 --- src/test/regress/sql/copy2.sql 17 Sep 2009 20:59:50 -0000 *************** *** 73,89 **** \. -- various COPY options: delimiters, oids, NULL string ! COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; 500000,x,45,80,90 500001,x,\x,\\x,\\\x 500002,x,\,,\\\,,\\ \. ! COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; 3000;;c;; \. ! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X'; 4000:\X:C:\X:\X 4001:1:empty:: 4002:2:null:\X:\X --- 73,89 ---- \. -- various COPY options: delimiters, oids, NULL string ! COPY x (b, c, d, e) from stdin (oids, delimiter ',', null 'x'); 500000,x,45,80,90 500001,x,\x,\\x,\\\x 500002,x,\,,\\\,,\\ \. ! COPY x from stdin (DELIMITER ';', NULL ''); 3000;;c;; \. ! COPY x from stdin (DELIMITER ':', NULL E'\\X'); 4000:\X:C:\X:\X 4001:1:empty:: 4002:2:null:\X:\X *************** *** 108,120 **** INSERT INTO no_oids (a, b) VALUES (20, 30); -- should fail ! COPY no_oids FROM stdin WITH OIDS; ! COPY no_oids TO stdout WITH OIDS; -- check copy out COPY x TO stdout; COPY x (c, e) TO stdout; ! COPY x (b, e) TO stdout WITH NULL 'I''m null'; CREATE TEMP TABLE y ( col1 text, --- 108,120 ---- INSERT INTO no_oids (a, b) VALUES (20, 30); -- should fail ! COPY no_oids FROM stdin (OIDS); ! COPY no_oids TO stdout (OIDS); -- check copy out COPY x TO stdout; COPY x (c, e) TO stdout; ! COPY x (b, e) TO stdout (NULL 'I''m null'); CREATE TEMP TABLE y ( col1 text, *************** *** 130,140 **** COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\'; COPY y TO stdout WITH CSV FORCE QUOTE *; --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); ! COPY testnl FROM stdin CSV; 1,"a field with two LFs inside",2 --- 130,152 ---- COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE E'\\'; COPY y TO stdout WITH CSV FORCE QUOTE *; + -- Test new 8.5 syntax + + COPY y TO stdout (CSV); + COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|'); + COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\'); + COPY y TO stdout (CSV, CSV_FORCE_QUOTE *); + + \COPY y TO stdout (CSV) + \COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|') + \COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\') + \COPY y TO stdout (CSV, CSV_FORCE_QUOTE *) + --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); ! COPY testnl FROM stdin (CSV); 1,"a field with two LFs inside",2 *************** *** 143,156 **** -- test end of copy marker CREATE TEMP TABLE testeoc (a text); ! COPY testeoc FROM stdin CSV; a\. \.b c\.d "\." \. ! COPY testeoc TO stdout CSV; DROP TABLE x, y; DROP FUNCTION fn_x_before(); --- 155,168 ---- -- test end of copy marker CREATE TEMP TABLE testeoc (a text); ! COPY testeoc FROM stdin (CSV); a\. \.b c\.d "\." \. ! COPY testeoc TO stdout (CSV); DROP TABLE x, y; DROP FUNCTION fn_x_before(); Index: src/test/regress/sql/aggregates.sql =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/sql/aggregates.sql,v retrieving revision 1.15 diff -c -r1.15 aggregates.sql *** src/test/regress/sql/aggregates.sql 25 Apr 2009 16:44:56 -0000 1.15 --- src/test/regress/sql/aggregates.sql 17 Sep 2009 20:59:50 -0000 *************** *** 104,110 **** BIT_OR(i4) AS "?" FROM bitwise_test; ! COPY bitwise_test FROM STDIN NULL 'null'; 1 1 1 1 1 B0101 3 3 3 null 2 B0100 7 7 7 3 4 B1100 --- 104,110 ---- BIT_OR(i4) AS "?" FROM bitwise_test; ! COPY bitwise_test FROM STDIN (NULL 'null'); 1 1 1 1 1 B0101 3 3 3 null 2 B0100 7 7 7 3 4 B1100 *************** *** 171,177 **** BOOL_OR(b3) AS "n" FROM bool_test; ! COPY bool_test FROM STDIN NULL 'null'; TRUE null FALSE null FALSE TRUE null null null TRUE FALSE null --- 171,177 ---- BOOL_OR(b3) AS "n" FROM bool_test; ! COPY bool_test FROM STDIN (NULL 'null'); TRUE null FALSE null FALSE TRUE null null null TRUE FALSE null Index: src/test/regress/sql/copyselect.sql =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/sql/copyselect.sql,v retrieving revision 1.2 diff -c -r1.2 copyselect.sql *** src/test/regress/sql/copyselect.sql 7 Aug 2008 01:11:52 -0000 1.2 --- src/test/regress/sql/copyselect.sql 17 Sep 2009 20:59:50 -0000 *************** *** 61,67 **** -- -- Test headers, CSV and quotes -- ! copy (select t from test1 where id = 1) to stdout csv header force quote t; -- -- Test psql builtins, plain table -- --- 61,67 ---- -- -- Test headers, CSV and quotes -- ! copy (select t from test1 where id = 1) to stdout (csv, csv_header, csv_force_quote (t)); -- -- Test psql builtins, plain table -- Index: src/test/regress/expected/aggregates.out =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/expected/aggregates.out,v retrieving revision 1.19 diff -c -r1.19 aggregates.out *** src/test/regress/expected/aggregates.out 25 Apr 2009 16:44:56 -0000 1.19 --- src/test/regress/expected/aggregates.out 17 Sep 2009 20:59:50 -0000 *************** *** 326,332 **** | (1 row) ! COPY bitwise_test FROM STDIN NULL 'null'; SELECT BIT_AND(i2) AS "1", BIT_AND(i4) AS "1", --- 326,332 ---- | (1 row) ! COPY bitwise_test FROM STDIN (NULL 'null'); SELECT BIT_AND(i2) AS "1", BIT_AND(i4) AS "1", *************** *** 401,407 **** | (1 row) ! COPY bool_test FROM STDIN NULL 'null'; SELECT BOOL_AND(b1) AS "f", BOOL_AND(b2) AS "t", --- 401,407 ---- | (1 row) ! COPY bool_test FROM STDIN (NULL 'null'); SELECT BOOL_AND(b1) AS "f", BOOL_AND(b2) AS "t", Index: src/test/regress/expected/copyselect.out =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/expected/copyselect.out,v retrieving revision 1.2 diff -c -r1.2 copyselect.out *** src/test/regress/expected/copyselect.out 7 Aug 2008 01:11:52 -0000 1.2 --- src/test/regress/expected/copyselect.out 17 Sep 2009 20:59:50 -0000 *************** *** 93,99 **** -- -- Test headers, CSV and quotes -- ! copy (select t from test1 where id = 1) to stdout csv header force quote t; t "a" -- --- 93,99 ---- -- -- Test headers, CSV and quotes -- ! copy (select t from test1 where id = 1) to stdout (csv, csv_header, csv_force_quote (t)); t "a" -- Index: src/test/regress/expected/copy2.out =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/expected/copy2.out,v retrieving revision 1.27 diff -c -r1.27 copy2.out *** src/test/regress/expected/copy2.out 25 Jul 2009 00:07:14 -0000 1.27 --- src/test/regress/expected/copy2.out 17 Sep 2009 20:59:50 -0000 *************** *** 47,55 **** ERROR: extra data after last expected column CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80" -- various COPY options: delimiters, oids, NULL string ! COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; ! COPY x from stdin WITH DELIMITER AS ';' NULL AS ''; ! COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X'; -- check results of copy in SELECT * FROM x; a | b | c | d | e --- 47,55 ---- ERROR: extra data after last expected column CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80" -- various COPY options: delimiters, oids, NULL string ! COPY x (b, c, d, e) from stdin (oids, delimiter ',', null 'x'); ! COPY x from stdin (DELIMITER ';', NULL ''); ! COPY x from stdin (DELIMITER ':', NULL E'\\X'); -- check results of copy in SELECT * FROM x; a | b | c | d | e *************** *** 89,97 **** INSERT INTO no_oids (a, b) VALUES (5, 10); INSERT INTO no_oids (a, b) VALUES (20, 30); -- should fail ! COPY no_oids FROM stdin WITH OIDS; ERROR: table "no_oids" does not have OIDs ! COPY no_oids TO stdout WITH OIDS; ERROR: table "no_oids" does not have OIDs -- check copy out COPY x TO stdout; --- 89,97 ---- INSERT INTO no_oids (a, b) VALUES (5, 10); INSERT INTO no_oids (a, b) VALUES (20, 30); -- should fail ! COPY no_oids FROM stdin (OIDS); ERROR: table "no_oids" does not have OIDs ! COPY no_oids TO stdout (OIDS); ERROR: table "no_oids" does not have OIDs -- check copy out COPY x TO stdout; *************** *** 146,152 **** stuff after trigger fired stuff after trigger fired stuff after trigger fired ! COPY x (b, e) TO stdout WITH NULL 'I''m null'; I'm null before trigger fired 21 before trigger fired 22 before trigger fired --- 146,152 ---- stuff after trigger fired stuff after trigger fired stuff after trigger fired ! COPY x (b, e) TO stdout (NULL 'I''m null'); I'm null before trigger fired 21 before trigger fired 22 before trigger fired *************** *** 195,207 **** "Jackson, Sam","\h" "It is ""perfect""."," " "", --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); ! COPY testnl FROM stdin CSV; -- test end of copy marker CREATE TEMP TABLE testeoc (a text); ! COPY testeoc FROM stdin CSV; ! COPY testeoc TO stdout CSV; a\. \.b c\.d --- 195,240 ---- "Jackson, Sam","\h" "It is ""perfect""."," " "", + -- Test new 8.5 syntax + COPY y TO stdout (CSV); + "Jackson, Sam",\h + "It is ""perfect"".", + "", + COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|'); + Jackson, Sam|\h + It is "perfect".| + ''| + COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\'); + "Jackson, Sam","\\h" + "It is \"perfect\"."," " + "", + COPY y TO stdout (CSV, CSV_FORCE_QUOTE *); + "Jackson, Sam","\h" + "It is ""perfect""."," " + "", + \COPY y TO stdout (CSV) + "Jackson, Sam",\h + "It is ""perfect"".", + "", + \COPY y TO stdout (CSV, CSV_QUOTE '''', DELIMITER '|') + Jackson, Sam|\h + It is "perfect".| + ''| + \COPY y TO stdout (CSV, CSV_FORCE_QUOTE (col2), CSV_ESCAPE E'\\') + "Jackson, Sam","\\h" + "It is \"perfect\"."," " + "", + \COPY y TO stdout (CSV, CSV_FORCE_QUOTE *) + "Jackson, Sam","\h" + "It is ""perfect""."," " + "", --test that we read consecutive LFs properly CREATE TEMP TABLE testnl (a int, b text, c int); ! COPY testnl FROM stdin (CSV); -- test end of copy marker CREATE TEMP TABLE testeoc (a text); ! COPY testeoc FROM stdin (CSV); ! COPY testeoc TO stdout (CSV); a\. \.b c\.d Index: doc/src/sgml/ref/copy.sgml =================================================================== RCS file: /home/manu/cvsrepo/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.87 diff -c -r1.87 copy.sgml *** doc/src/sgml/ref/copy.sgml 5 Sep 2009 23:58:01 -0000 1.87 --- doc/src/sgml/ref/copy.sgml 17 Sep 2009 20:59:50 -0000 *************** *** 24,50 **** <synopsis> COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [,...] ) ] FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN } ! [ [ WITH ] ! [ BINARY ] ! [ OIDS ] ! [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ] ! [ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ] ! [ CSV [ HEADER ] ! [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ] ! [ 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> [,...] ) ] | ( <replaceable class="parameter">query</replaceable> ) } TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT } ! [ [ WITH ] ! [ BINARY ] ! [ OIDS ] ! [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ] ! [ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ] ! [ CSV [ HEADER ] ! [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ] ! [ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ] ! [ FORCE QUOTE { <replaceable class="parameter">column</replaceable> [, ...] | * } ] </synopsis> </refsynopsisdiv> --- 24,47 ---- <synopsis> COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [,...] ) ] FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN } ! [ ( options [,...] ) ] COPY { <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [,...] ) ] | ( <replaceable class="parameter">query</replaceable> ) } TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT } ! [ ( options [,...] ) ] ! ! Currently available options are: ! - BINARY [ <replaceable class="parameter">boolean</replaceable> ] ! - OIDS [ <replaceable class="parameter">boolean</replaceable> ] ! - DELIMITER '<replaceable class="parameter">delimiter</replaceable>' ! - NULL '<replaceable class="parameter">null string</replaceable>' ! - CSV [ <replaceable class="parameter">boolean</replaceable> ] ! - CSV_HEADER [ <replaceable class="parameter">boolean</replaceable> ] ! - CSV_QUOTE '<replaceable class="parameter">quote</replaceable>' ! - CSV_ESCAPE '<replaceable class="parameter">escape</replaceable>' ! - CSV_FORCE_QUOTE { ( <replaceable class="parameter">column</replaceable> [, ...] ) | * } ! - CSV_FORCE_NOT_NULL ( <replaceable class="parameter">column</replaceable> [, ...] ) ! </synopsis> </refsynopsisdiv> *************** *** 143,148 **** --- 140,166 ---- </para> </listitem> </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Options</title> + + <variablelist> + + <varlistentry> + <term><replaceable class="parameter">boolean</replaceable></term> + <listitem> + <para> + Specifies whether the selected option should be turned on or off. + You can write <literal>TRUE</literal>, <literal>ON</>, or + <literal>1</literal> to enable the option, and <literal>FALSE</literal>, + <literal>OFF</>, or <literal>0</literal> to disable it. The + <replaceable class="parameter">boolean</replaceable> value can also + be omitted, in which case <literal>TRUE</literal> is assumed. + </para> + </listitem> + </varlistentry> <varlistentry> <term><literal>BINARY</literal></term> *************** *** 168,174 **** </varlistentry> <varlistentry> ! <term><replaceable class="parameter">delimiter</replaceable></term> <listitem> <para> The single ASCII character that separates columns within each row --- 186,192 ---- </varlistentry> <varlistentry> ! <term><literal>DELIMITER</literal></term> <listitem> <para> The single ASCII character that separates columns within each row *************** *** 179,185 **** </varlistentry> <varlistentry> ! <term><replaceable class="parameter">null string</replaceable></term> <listitem> <para> The string that represents a null value. The default is --- 197,203 ---- </varlistentry> <varlistentry> ! <term><literal>NULL</literal></term> <listitem> <para> The string that represents a null value. The default is *************** *** 211,217 **** </varlistentry> <varlistentry> ! <term><literal>HEADER</literal></term> <listitem> <para> Specifies that the file contains a header line with the names of each --- 229,235 ---- </varlistentry> <varlistentry> ! <term><literal>CSV_HEADER</literal></term> <listitem> <para> Specifies that the file contains a header line with the names of each *************** *** 222,228 **** </varlistentry> <varlistentry> ! <term><replaceable class="parameter">quote</replaceable></term> <listitem> <para> Specifies the ASCII quotation character in <literal>CSV</> mode. --- 240,246 ---- </varlistentry> <varlistentry> ! <term><literal>CSV_QUOTE</literal></term> <listitem> <para> Specifies the ASCII quotation character in <literal>CSV</> mode. *************** *** 232,249 **** </varlistentry> <varlistentry> ! <term><replaceable class="parameter">escape</replaceable></term> <listitem> <para> Specifies the ASCII character that should appear before a ! <literal>QUOTE</> data character value in <literal>CSV</> mode. ! The default is the <literal>QUOTE</> value (usually double-quote). </para> </listitem> </varlistentry> <varlistentry> ! <term><literal>FORCE QUOTE</></term> <listitem> <para> In <literal>CSV</> <command>COPY TO</> mode, forces quoting to be --- 250,267 ---- </varlistentry> <varlistentry> ! <term><literal>CSV_ESCAPE</literal></term> <listitem> <para> Specifies the ASCII character that should appear before a ! <literal>CSV_QUOTE</> data character value in <literal>CSV</> mode. ! The default is the <literal>CSV_QUOTE</> value (usually double-quote). </para> </listitem> </varlistentry> <varlistentry> ! <term><literal>CSV_FORCE_QUOTE</></term> <listitem> <para> In <literal>CSV</> <command>COPY TO</> mode, forces quoting to be *************** *** 256,262 **** </varlistentry> <varlistentry> ! <term><literal>FORCE NOT NULL</></term> <listitem> <para> In <literal>CSV</> <command>COPY FROM</> mode, process each --- 274,280 ---- </varlistentry> <varlistentry> ! <term><literal>CSV_FORCE_NOT_NULL</></term> <listitem> <para> In <literal>CSV</> <command>COPY FROM</> mode, process each *************** *** 295,301 **** </para> <para> ! The <literal>BINARY</literal> key word causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the normal text mode, but a binary-format file is less portable across machine architectures and --- 313,319 ---- </para> <para> ! The <literal>BINARY</literal> option causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the normal text mode, but a binary-format file is less portable across machine architectures and *************** *** 538,549 **** <para> The values in each record are separated by the <literal>DELIMITER</> character. If the value contains the delimiter character, the ! <literal>QUOTE</> character, the <literal>NULL</> string, a carriage return, or line feed character, then the whole value is prefixed and ! suffixed by the <literal>QUOTE</> character, and any occurrence ! within the value of a <literal>QUOTE</> character or the ! <literal>ESCAPE</> character is preceded by the escape character. ! You can also use <literal>FORCE QUOTE</> to force quotes when outputting non-<literal>NULL</> values in specific columns. </para> --- 556,567 ---- <para> The values in each record are separated by the <literal>DELIMITER</> character. If the value contains the delimiter character, the ! <literal>CSV_QUOTE</> character, the <literal>NULL</> string, a carriage return, or line feed character, then the whole value is prefixed and ! suffixed by the <literal>CSV_QUOTE</> character, and any occurrence ! within the value of a <literal>CSV_QUOTE</> character or the ! <literal>CSV_ESCAPE</> character is preceded by the escape character. ! You can also use <literal>CSV_FORCE_QUOTE</> to force quotes when outputting non-<literal>NULL</> values in specific columns. </para> *************** *** 557,563 **** settings, a <literal>NULL</> is written as an unquoted empty string, while an empty string is written with double quotes (<literal>""</>). Reading values follows similar rules. You can ! use <literal>FORCE NOT NULL</> to prevent <literal>NULL</> input comparisons for specific columns. </para> --- 575,581 ---- settings, a <literal>NULL</> is written as an unquoted empty string, while an empty string is written with double quotes (<literal>""</>). Reading values follows similar rules. You can ! use <literal>CSV_FORCE_NOT_NULL</> to prevent <literal>NULL</> input comparisons for specific columns. </para> *************** *** 577,583 **** <para> In <literal>CSV</> mode, all characters are significant. A quoted value surrounded by white space, or any characters other than ! <literal>DELIMITER</>, will include those characters. This can cause errors if you import data from a system that pads <literal>CSV</> lines with white space out to some fixed width. If such a situation arises you might need to preprocess the <literal>CSV</> file to remove --- 595,601 ---- <para> In <literal>CSV</> mode, all characters are significant. A quoted value surrounded by white space, or any characters other than ! <literal>CSV_DELIMITER</>, will include those characters. This can cause errors if you import data from a system that pads <literal>CSV</> lines with white space out to some fixed width. If such a situation arises you might need to preprocess the <literal>CSV</> file to remove *************** *** 759,765 **** The following example copies a table to the client using the vertical bar (<literal>|</literal>) as the field delimiter: <programlisting> ! COPY country TO STDOUT WITH DELIMITER '|'; </programlisting> </para> --- 777,783 ---- The following example copies a table to the client using the vertical bar (<literal>|</literal>) as the field delimiter: <programlisting> ! COPY country TO STDOUT (DELIMITER '|'); </programlisting> </para> *************** *** 809,814 **** --- 827,838 ---- 0000200 M B A B W E 377 377 377 377 377 377 </programlisting> </para> + <para> + Multiple options are separated by a comma like: + <programlisting> + COPY (SELECT t FROM foo WHERE id = 1) TO STDOUT (CSV, CSV_HEADER, CSV_FORCE_QUOTE (t)); + </programlisting> + </para> </refsect1> <refsect1> *************** *** 817,823 **** --- 841,875 ---- <para> There is no <command>COPY</command> statement in the SQL standard. </para> + <para> + The following syntax was used before <productname>PostgreSQL</> + version 8.5 and is still supported: + <synopsis> + COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [,...] ) ] + FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN } + [ [ WITH ] + [ BINARY ] + [ OIDS ] + [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ] + [ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ] + [ CSV [ HEADER ] + [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ] + [ 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> [,...] ) ] | ( <replaceable class="parameter">query</replaceable> ) } + TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT } + [ [ WITH ] + [ BINARY ] + [ OIDS ] + [ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ] + [ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ] + [ CSV [ HEADER ] + [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ] + [ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ] + [ FORCE QUOTE { <replaceable class="parameter">column</replaceable> [, ...] | * } ] + </synopsis> + </para> <para> The following syntax was used before <productname>PostgreSQL</> version 7.3 and is still supported: Index: src/backend/parser/gram.y =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/backend/parser/gram.y,v retrieving revision 2.677 diff -c -r2.677 gram.y *** src/backend/parser/gram.y 18 Aug 2009 23:40:20 -0000 2.677 --- src/backend/parser/gram.y 17 Sep 2009 20:59:50 -0000 *************** *** 373,378 **** --- 373,382 ---- %type <node> explain_option_arg %type <defelt> explain_option_elem %type <list> explain_option_list + %type <str> copy_generic_option_name + %type <node> copy_generic_option_arg copy_generic_option_arg_item + %type <defelt> copy_generic_option_elem + %type <list> copy_generic_option_list copy_generic_option_arg_list %type <typnam> Typename SimpleTypename ConstTypename GenericType Numeric opt_float *************** *** 1934,1947 **** /***************************************************************************** * * QUERY : ! * COPY relname ['(' columnList ')'] FROM/TO file [WITH options] ! * ! * BINARY, OIDS, and DELIMITERS kept in old locations ! * for backward compatibility. 2002-06-18 * * COPY ( SELECT ... ) TO file [WITH options] ! * This form doesn't have the backwards-compatible option ! * syntax. * *****************************************************************************/ --- 1938,1956 ---- /***************************************************************************** * * QUERY : ! * New, more generic syntax, supported beginning with PostgreSQL ! * 8.5. Options are comma-separated. ! * COPY relname ['(' columnList ')'] FROM/TO file '(' options ')' * + * Older syntax, used from 7.3 to 8.4 and still supported for + * backwards compatibility + * COPY relname ['(' columnList ')'] FROM/TO file [WITH options] * COPY ( SELECT ... ) TO file [WITH options] ! * ! * Really old syntax, from versions 7.2 and prior: ! * COPY [ BINARY ] table [ WITH OIDS ] FROM/TO file ! * [ [ USING ] DELIMITERS 'delimiter' ] ] ! * [ WITH NULL AS 'null string' ] * *****************************************************************************/ *************** *** 2001,2006 **** --- 2010,2016 ---- copy_opt_list: copy_opt_list copy_opt_item { $$ = lappend($1, $2); } + | '(' copy_generic_option_list ')' { $$ = $2 ; } | /* EMPTY */ { $$ = NIL; } ; *************** *** 2028,2054 **** } | HEADER_P { ! $$ = makeDefElem("header", (Node *)makeInteger(TRUE)); } | QUOTE opt_as Sconst { ! $$ = makeDefElem("quote", (Node *)makeString($3)); } | ESCAPE opt_as Sconst { ! $$ = makeDefElem("escape", (Node *)makeString($3)); } | FORCE QUOTE columnList { ! $$ = makeDefElem("force_quote", (Node *)$3); } | FORCE QUOTE '*' { ! $$ = makeDefElem("force_quote", (Node *)makeNode(A_Star)); } | FORCE NOT NULL_P columnList { ! $$ = makeDefElem("force_notnull", (Node *)$4); } ; --- 2038,2064 ---- } | HEADER_P { ! $$ = makeDefElem("csv_header", (Node *)makeInteger(TRUE)); } | QUOTE opt_as Sconst { ! $$ = makeDefElem("csv_quote", (Node *)makeString($3)); } | ESCAPE opt_as Sconst { ! $$ = makeDefElem("csv_escape", (Node *)makeString($3)); } | FORCE QUOTE columnList { ! $$ = makeDefElem("csv_force_quote", (Node *)$3); } | FORCE QUOTE '*' { ! $$ = makeDefElem("csv_force_quote", (Node *)makeNode(A_Star)); } | FORCE NOT NULL_P columnList { ! $$ = makeDefElem("csv_force_not_null", (Node *)$4); } ; *************** *** 2084,2089 **** --- 2094,2146 ---- | /*EMPTY*/ {} ; + copy_generic_option_list: + copy_generic_option_elem + { + $$ = list_make1($1); + } + | copy_generic_option_list ',' copy_generic_option_elem + { + $$ = lappend($1, $3); + } + ; + + copy_generic_option_elem: + copy_generic_option_name copy_generic_option_arg + { + $$ = makeDefElem($1, $2); + } + ; + + copy_generic_option_name: + ColLabel { $$ = $1; } + ; + + copy_generic_option_arg: + copy_generic_option_arg_item { $$ = $1; } + | '(' copy_generic_option_arg_list ')' { $$ = (Node *) $2; } + | '*' { $$ = (Node *)makeNode(A_Star); } + | '(' ')' { $$ = NULL; } + | /* EMPTY */ { $$ = NULL; } + ; + + copy_generic_option_arg_list: + copy_generic_option_arg_item + { + $$ = list_make1($1); + } + | copy_generic_option_arg_list ',' copy_generic_option_arg_item + { + $$ = lappend($1, $3); + } + ; + + copy_generic_option_arg_item: + opt_boolean { $$ = (Node *) makeString($1); } + | ColId_or_Sconst { $$ = (Node *) makeString($1); } + | NumericOnly { $$ = (Node *) $1; } + ; + /***************************************************************************** * Index: src/bin/psql/copy.c =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/bin/psql/copy.c,v retrieving revision 1.82 diff -c -r1.82 copy.c *** src/bin/psql/copy.c 7 Aug 2009 20:16:11 -0000 1.82 --- src/bin/psql/copy.c 17 Sep 2009 20:59:50 -0000 *************** *** 26,45 **** #include "prompt.h" #include "stringutils.h" - /* * parse_slash_copy * -- parses \copy command line * * The documented syntax is: * \copy tablename [(columnlist)] from|to filename * [ with ] [ binary ] [ oids ] [ delimiter [as] char ] [ null [as] string ] ! * [ csv [ header ] [ quote [ AS ] string ] escape [as] string * [ force not null column [, ...] | force quote column [, ...] | * ] ] * * \copy ( select stmt ) to filename * [ with ] [ binary ] [ delimiter [as] char ] [ null [as] string ] ! * [ csv [ header ] [ quote [ AS ] string ] escape [as] string * [ force quote column [, ...] | * ] ] * * Force quote only applies for copy to; force not null only applies for --- 26,52 ---- #include "prompt.h" #include "stringutils.h" /* * parse_slash_copy * -- parses \copy command line * * The documented syntax is: + * Since 8.5: + * \copy tablename [(columnlist)] from|to filename [( options )] + * + * options is a comma separated list of options. Currently supported options: + * binary, oids, delimiter, null, csv, csv_header, csv_espace, csv_quote, + * csv_force_not_null, csv_force_quote + * + * Prior 8.5: * \copy tablename [(columnlist)] from|to filename * [ with ] [ binary ] [ oids ] [ delimiter [as] char ] [ null [as] string ] ! * [ csv [ header ] [ quote [ AS ] string ] [ escape [as] string ] * [ force not null column [, ...] | force quote column [, ...] | * ] ] * * \copy ( select stmt ) to filename * [ with ] [ binary ] [ delimiter [as] char ] [ null [as] string ] ! * [ csv [ header ] [ quote [ AS ] string ] [ escape [as] string ] * [ force quote column [, ...] | * ] ] * * Force quote only applies for copy to; force not null only applies for *************** *** 54,74 **** struct copy_options { ! char *table; ! char *column_list; ! char *file; /* NULL = stdin/stdout */ ! bool psql_inout; /* true = use psql stdin/stdout */ ! bool from; ! bool binary; ! bool oids; ! bool csv_mode; ! bool header; ! char *delim; ! char *null; ! char *quote; ! char *escape; ! char *force_quote_list; ! char *force_notnull_list; }; --- 61,71 ---- struct copy_options { ! char *before_tofrom; /* COPY string before TO/FROM */ ! char *after_tofrom; /* COPY string after TO/FROM (options) */ ! char *file; /* NULL = stdin/stdout */ ! bool psql_inout; /* true if pstdin/pstdout */ ! bool from; /* true = from, false = to */ }; *************** *** 77,91 **** { if (!ptr) return; - free(ptr->table); - free(ptr->column_list); free(ptr->file); ! free(ptr->delim); ! free(ptr->null); ! free(ptr->quote); ! free(ptr->escape); ! free(ptr->force_quote_list); ! free(ptr->force_notnull_list); free(ptr); } --- 74,82 ---- { if (!ptr) return; free(ptr->file); ! free(ptr->before_tofrom); ! free(ptr->after_tofrom); free(ptr); } *************** *** 128,143 **** if (!token) goto error; if (pg_strcasecmp(token, "binary") == 0) { - result->binary = true; token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); if (!token) goto error; - } ! result->table = pg_strdup(token); /* Handle COPY (SELECT) case */ if (token[0] == '(') --- 119,137 ---- if (!token) goto error; + result->before_tofrom = pg_strdup(token); + + /* The following can be removed when we drop 7.3 syntax support */ if (pg_strcasecmp(token, "binary") == 0) { token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); if (!token) goto error; ! xstrcat(&result->before_tofrom, " "); ! xstrcat(&result->before_tofrom, token); ! } /* Handle COPY (SELECT) case */ if (token[0] == '(') *************** *** 154,208 **** parens++; else if (token[0] == ')') parens--; ! xstrcat(&result->table, " "); ! xstrcat(&result->table, token); } } ! token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); if (!token) goto error; - /* - * strtokx() will not have returned a multi-character token starting with - * '.', so we don't need strcmp() here. Likewise for '(', etc, below. - */ - if (token[0] == '.') - { - /* handle schema . table */ - xstrcat(&result->table, token); - token = strtokx(NULL, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); - if (!token) - goto error; - xstrcat(&result->table, token); - token = strtokx(NULL, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); - if (!token) - goto error; - } - if (token[0] == '(') { /* handle parenthesized column list */ - result->column_list = pg_strdup(token); for (;;) { ! token = strtokx(NULL, whitespace, ".,()", "\"", ! 0, false, false, pset.encoding); ! if (!token || strchr(".,()", token[0])) ! goto error; ! xstrcat(&result->column_list, token); ! token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); if (!token) goto error; ! xstrcat(&result->column_list, token); if (token[0] == ')') break; - if (token[0] != ',') - goto error; } token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); --- 148,182 ---- parens++; else if (token[0] == ')') parens--; ! xstrcat(&result->before_tofrom, " "); ! xstrcat(&result->before_tofrom, token); } } ! token = strtokx(NULL, whitespace, ",()", "\"", 0, false, false, pset.encoding); if (!token) goto error; if (token[0] == '(') { + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); + /* handle parenthesized column list */ for (;;) { ! token = strtokx(NULL, whitespace, ")", "\"", 0, false, false, pset.encoding); + if (!token) goto error; ! ! xstrcat(&result->before_tofrom, " "); ! xstrcat(&result->before_tofrom, token); ! if (token[0] == ')') break; } token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); *************** *** 241,394 **** expand_tilde(&result->file); } ! token = strtokx(NULL, whitespace, NULL, NULL, ! 0, false, false, pset.encoding); if (token) ! { ! /* ! * WITH is optional. Also, the backend will allow WITH followed by ! * nothing, so we do too. ! */ ! if (pg_strcasecmp(token, "with") == 0) ! token = strtokx(NULL, whitespace, NULL, NULL, ! 0, false, false, pset.encoding); ! ! while (token) ! { ! bool fetch_next; ! ! fetch_next = true; ! ! if (pg_strcasecmp(token, "oids") == 0) ! result->oids = true; ! else if (pg_strcasecmp(token, "binary") == 0) ! result->binary = true; ! else if (pg_strcasecmp(token, "csv") == 0) ! result->csv_mode = true; ! else if (pg_strcasecmp(token, "header") == 0) ! result->header = true; ! else if (pg_strcasecmp(token, "delimiter") == 0) ! { ! if (result->delim) ! goto error; ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token && pg_strcasecmp(token, "as") == 0) ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token) ! result->delim = pg_strdup(token); ! else ! goto error; ! } ! else if (pg_strcasecmp(token, "null") == 0) ! { ! if (result->null) ! goto error; ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token && pg_strcasecmp(token, "as") == 0) ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token) ! result->null = pg_strdup(token); ! else ! goto error; ! } ! else if (pg_strcasecmp(token, "quote") == 0) ! { ! if (result->quote) ! goto error; ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token && pg_strcasecmp(token, "as") == 0) ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token) ! result->quote = pg_strdup(token); ! else ! goto error; ! } ! else if (pg_strcasecmp(token, "escape") == 0) ! { ! if (result->escape) ! goto error; ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token && pg_strcasecmp(token, "as") == 0) ! token = strtokx(NULL, whitespace, NULL, "'", ! nonstd_backslash, true, false, pset.encoding); ! if (token) ! result->escape = pg_strdup(token); ! else ! goto error; ! } ! else if (pg_strcasecmp(token, "force") == 0) ! { ! token = strtokx(NULL, whitespace, ",", "\"", ! 0, false, false, pset.encoding); ! if (pg_strcasecmp(token, "quote") == 0) ! { ! if (result->force_quote_list) ! goto error; ! /* handle column list */ ! fetch_next = false; ! for (;;) ! { ! token = strtokx(NULL, whitespace, ",", "\"", ! 0, false, false, pset.encoding); ! if (!token || strchr(",", token[0])) ! goto error; ! if (!result->force_quote_list) ! result->force_quote_list = pg_strdup(token); ! else ! xstrcat(&result->force_quote_list, token); ! token = strtokx(NULL, whitespace, ",", "\"", ! 0, false, false, pset.encoding); ! if (!token || token[0] != ',') ! break; ! xstrcat(&result->force_quote_list, token); ! } ! } ! else if (pg_strcasecmp(token, "not") == 0) ! { ! if (result->force_notnull_list) ! goto error; ! token = strtokx(NULL, whitespace, ",", "\"", ! 0, false, false, pset.encoding); ! if (pg_strcasecmp(token, "null") != 0) ! goto error; ! /* handle column list */ ! fetch_next = false; ! for (;;) ! { ! token = strtokx(NULL, whitespace, ",", "\"", ! 0, false, false, pset.encoding); ! if (!token || strchr(",", token[0])) ! goto error; ! if (!result->force_notnull_list) ! result->force_notnull_list = pg_strdup(token); ! else ! xstrcat(&result->force_notnull_list, token); ! token = strtokx(NULL, whitespace, ",", "\"", ! 0, false, false, pset.encoding); ! if (!token || token[0] != ',') ! break; ! xstrcat(&result->force_notnull_list, token); ! } ! } ! else ! goto error; ! } ! else ! goto error; ! ! if (fetch_next) ! token = strtokx(NULL, whitespace, NULL, NULL, ! 0, false, false, pset.encoding); ! } ! } free(line); --- 215,226 ---- expand_tilde(&result->file); } ! /* Catch the rest of the COPY options */ ! token = strtokx(NULL, "", NULL, NULL, 0, false, ! false, pset.encoding); if (token) ! result->after_tofrom = pg_strdup(token); free(line); *************** *** 407,429 **** /* - * Handle one of the "string" options of COPY. If the user gave a quoted - * string, pass it to the backend as-is; if it wasn't quoted then quote - * and escape it. - */ - static void - emit_copy_option(PQExpBuffer query, const char *keyword, const char *option) - { - appendPQExpBufferStr(query, keyword); - if (option[0] == '\'' || - ((option[0] == 'E' || option[0] == 'e') && option[1] == '\'')) - appendPQExpBufferStr(query, option); - else - appendStringLiteralConn(query, option, pset.db); - } - - - /* * Execute a \copy command (frontend copy). We have to open a file, then * submit a COPY query to the backend and either feed it data from the * file or route its response into the file. --- 239,244 ---- *************** *** 445,498 **** return false; initPQExpBuffer(&query); - printfPQExpBuffer(&query, "COPY "); ! appendPQExpBuffer(&query, "%s ", options->table); ! ! if (options->column_list) ! appendPQExpBuffer(&query, "%s ", options->column_list); ! ! if (options->from) ! appendPQExpBuffer(&query, "FROM STDIN"); else ! appendPQExpBuffer(&query, "TO STDOUT"); ! ! ! if (options->binary) ! appendPQExpBuffer(&query, " BINARY "); ! ! if (options->oids) ! appendPQExpBuffer(&query, " OIDS "); ! if (options->delim) ! emit_copy_option(&query, " DELIMITER ", options->delim); ! ! if (options->null) ! emit_copy_option(&query, " NULL AS ", options->null); ! ! if (options->csv_mode) ! appendPQExpBuffer(&query, " CSV"); ! ! if (options->header) ! appendPQExpBuffer(&query, " HEADER"); ! ! if (options->quote) ! emit_copy_option(&query, " QUOTE AS ", options->quote); ! ! if (options->escape) ! emit_copy_option(&query, " ESCAPE AS ", options->escape); ! ! if (options->force_quote_list) ! appendPQExpBuffer(&query, " FORCE QUOTE %s", options->force_quote_list); ! if (options->force_notnull_list) ! appendPQExpBuffer(&query, " FORCE NOT NULL %s", options->force_notnull_list); ! if (options->file) ! canonicalize_path(options->file); ! if (options->from) { if (options->file) copystream = fopen(options->file, PG_BINARY_R); --- 260,288 ---- return false; initPQExpBuffer(&query); printfPQExpBuffer(&query, "COPY "); ! if ((options->file == NULL) && (options->psql_inout == false)) ! { /* Send the query as is, it's a simple COPY operation */ ! appendPQExpBuffer(&query, "%s", pg_strdup(args)); ! } else ! { /* Replace the filename with STDIN/STDOUT */ ! appendPQExpBuffer(&query, "%s ", options->before_tofrom); ! if (options->from) ! appendPQExpBuffer(&query, " FROM STDIN "); ! else ! appendPQExpBuffer(&query, " TO STDOUT "); ! if (options->after_tofrom) ! appendPQExpBuffer(&query, "%s", options->after_tofrom); ! if (options->file) ! canonicalize_path(options->file); ! } ! if (options->from) { if (options->file) copystream = fopen(options->file, PG_BINARY_R); *************** *** 504,511 **** else { if (options->file) ! copystream = fopen(options->file, ! options->binary ? PG_BINARY_W : "w"); else if (!options->psql_inout) copystream = pset.queryFout; else --- 294,300 ---- else { if (options->file) ! copystream = fopen(options->file, PG_BINARY_W); else if (!options->psql_inout) copystream = pset.queryFout; else Index: src/test/regress/output/copy.source =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/output/copy.source,v retrieving revision 1.13 diff -c -r1.13 copy.source *** src/test/regress/output/copy.source 21 Aug 2007 01:11:31 -0000 1.13 --- src/test/regress/output/copy.source 17 Sep 2009 20:59:50 -0000 *************** *** 71,73 **** --- 71,119 ---- c1,"col with , comma","col with "" quote" 1,a,1 2,b,2 + -- Repeat the above tests with the new 8.5 option syntax + copy copytest to '@abs_builddir@/results/copytest.csv' (csv); + truncate copytest2; + copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv); + select * from copytest except select * from copytest2; + style | test | filler + -------+------+-------- + (0 rows) + + truncate copytest2; + copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\'); + copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\'); + select * from copytest except select * from copytest2; + style | test | filler + -------+------+-------- + (0 rows) + + truncate copytest3; + copy copytest3 from stdin (csv, csv_header); + copy copytest3 to stdout (csv, csv_header); + c1,"col with , comma","col with "" quote" + 1,a,1 + 2,b,2 + -- Repeat the above tests with the new 8.5 option syntax from psql + \copy copytest to '@abs_builddir@/results/copytest.csv' (csv) + truncate copytest2; + \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv) + select * from copytest except select * from copytest2; + style | test | filler + -------+------+-------- + (0 rows) + + truncate copytest2; + \copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\') + \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\') + select * from copytest except select * from copytest2; + style | test | filler + -------+------+-------- + (0 rows) + + truncate copytest3; + \copy copytest3 from stdin (csv, csv_header) + \copy copytest3 to stdout (csv, csv_header) + c1,"col with , comma","col with "" quote" + 1,a,1 + 2,b,2 Index: src/test/regress/input/copy.source =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/test/regress/input/copy.source,v retrieving revision 1.15 diff -c -r1.15 copy.source *** src/test/regress/input/copy.source 21 Aug 2007 01:11:31 -0000 1.15 --- src/test/regress/input/copy.source 17 Sep 2009 20:59:50 -0000 *************** *** 107,109 **** --- 107,164 ---- copy copytest3 to stdout csv header; + -- Repeat the above tests with the new 8.5 option syntax + + copy copytest to '@abs_builddir@/results/copytest.csv' (csv); + + truncate copytest2; + + copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv); + + select * from copytest except select * from copytest2; + + truncate copytest2; + + copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\'); + + copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\'); + + select * from copytest except select * from copytest2; + + truncate copytest3; + + copy copytest3 from stdin (csv, csv_header); + this is just a line full of junk that would error out if parsed + 1,a,1 + 2,b,2 + \. + + copy copytest3 to stdout (csv, csv_header); + + -- Repeat the above tests with the new 8.5 option syntax from psql + + \copy copytest to '@abs_builddir@/results/copytest.csv' (csv) + + truncate copytest2; + + \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv) + + select * from copytest except select * from copytest2; + + truncate copytest2; + + \copy copytest to '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\') + + \copy copytest2 from '@abs_builddir@/results/copytest.csv' (csv, csv_quote '''', csv_escape E'\\') + + select * from copytest except select * from copytest2; + + truncate copytest3; + + \copy copytest3 from stdin (csv, csv_header) + this is just a line full of junk that would error out if parsed + 1,a,1 + 2,b,2 + \. + + \copy copytest3 to stdout (csv, csv_header) Index: src/backend/commands/copy.c =================================================================== RCS file: /home/manu/cvsrepo/pgsql/src/backend/commands/copy.c,v retrieving revision 1.316 diff -c -r1.316 copy.c *** src/backend/commands/copy.c 29 Jul 2009 20:56:18 -0000 1.316 --- src/backend/commands/copy.c 17 Sep 2009 20:59:50 -0000 *************** *** 25,30 **** --- 25,31 ---- #include "catalog/namespace.h" #include "catalog/pg_type.h" #include "commands/copy.h" + #include "commands/defrem.h" #include "commands/trigger.h" #include "executor/executor.h" #include "libpq/libpq.h" *************** *** 745,751 **** ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->binary = intVal(defel->arg); } else if (strcmp(defel->defname, "oids") == 0) { --- 746,752 ---- ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->binary = defGetBoolean(defel); } else if (strcmp(defel->defname, "oids") == 0) { *************** *** 753,759 **** ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->oids = intVal(defel->arg); } else if (strcmp(defel->defname, "delimiter") == 0) { --- 754,760 ---- ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->oids = defGetBoolean(defel); } else if (strcmp(defel->defname, "delimiter") == 0) { *************** *** 761,767 **** ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->delim = strVal(defel->arg); } else if (strcmp(defel->defname, "null") == 0) { --- 762,768 ---- ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->delim = defGetString(defel); } else if (strcmp(defel->defname, "null") == 0) { *************** *** 769,775 **** ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->null_print = strVal(defel->arg); } else if (strcmp(defel->defname, "csv") == 0) { --- 770,776 ---- ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->null_print = defGetString(defel); } else if (strcmp(defel->defname, "csv") == 0) { *************** *** 777,809 **** ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->csv_mode = intVal(defel->arg); } ! else if (strcmp(defel->defname, "header") == 0) { if (cstate->header_line) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->header_line = intVal(defel->arg); } ! else if (strcmp(defel->defname, "quote") == 0) { if (cstate->quote) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->quote = strVal(defel->arg); } ! else if (strcmp(defel->defname, "escape") == 0) { if (cstate->escape) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->escape = strVal(defel->arg); } ! else if (strcmp(defel->defname, "force_quote") == 0) { if (force_quote || force_quote_all) ereport(ERROR, --- 778,810 ---- ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->csv_mode = defGetBoolean(defel); } ! else if (strcmp(defel->defname, "csv_header") == 0) { if (cstate->header_line) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->header_line = defGetBoolean(defel); } ! else if (strcmp(defel->defname, "csv_quote") == 0) { if (cstate->quote) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->quote = defGetString(defel); } ! else if (strcmp(defel->defname, "csv_escape") == 0) { if (cstate->escape) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! cstate->escape = defGetString(defel); } ! else if (strcmp(defel->defname, "csv_force_quote") == 0) { if (force_quote || force_quote_all) ereport(ERROR, *************** *** 811,830 **** errmsg("conflicting or redundant options"))); if (defel->arg && IsA(defel->arg, A_Star)) force_quote_all = true; ! else force_quote = (List *) defel->arg; } ! else if (strcmp(defel->defname, "force_notnull") == 0) { if (force_notnull) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! force_notnull = (List *) defel->arg; } else ! elog(ERROR, "option \"%s\" not recognized", ! defel->defname); } /* Check for incompatible options */ --- 812,856 ---- errmsg("conflicting or redundant options"))); if (defel->arg && IsA(defel->arg, A_Star)) force_quote_all = true; ! else if (defel->arg && IsA(defel->arg, List)) ! { ! ListCell *lc; ! force_quote = (List *) defel->arg; + foreach (lc, force_quote) + { + if (!IsA(lfirst(lc), String)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("argument to option \"%s\" must be a list of column names", + defel->defname))); + } + } + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("argument to option \"%s\" must be a list of column names", + defel->defname))); } ! else if (strcmp(defel->defname, "csv_force_not_null") == 0) { if (force_notnull) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); ! if (defel->arg && IsA(defel->arg, List)) ! force_notnull = (List *) defel->arg; ! else ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("argument to option \"%s\" must be a list", ! defel->defname))); } else ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("option \"%s\" not recognized", ! defel->defname))); } /* Check for incompatible options */
pgsql-hackers by date: