Re: generic copy options - Mailing list pgsql-hackers
| From | Emmanuel Cecchet |
|---|---|
| Subject | Re: generic copy options |
| Date | |
| Msg-id | 4AB28647.7020107@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 |
| List | pgsql-hackers |
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 > On Thu, Sep 17, 2009 at 11:07:33AM -0400, Emmanuel Cecchet wrote: > >> Tom Lane wrote: >> >>> I wonder though if we couldn't simplify matters. Offhand it seems to me >>> that psql doesn't need to validate the command's syntax fully. All it >>> really needs to do is find the target filename and replace it with >>> STDIN/STDOUT. Could we have it just treat the remainder of the line >>> literally, and not worry about the details of what the options might be? >>> Let the backend worry about throwing an error if they're bad. >>> >>> >> New version with the simplified psql. Still supports the 7.3 syntax. >> I am going to look into porting the other COPY enhancements (error >> logging and autopartitioning) on this implementation. We might come up >> with new ideas for the documentation side of things with more options. >> >> manu >> >> -- >> Emmanuel Cecchet >> Aster Data Systems >> Web: http://www.asterdata.com >> >> > > Hi, > > I've been working on a review of this patch and currently its > failing regression tests. Here's the regression.diff. I'm going to > spend some time today trying to figure out if the tests need to > change of there is an actual issue in the patch. Just wanted to give > a heads up. > > -- > --Dan > > *** /home/dc0lish/workspace/postgresql/src/test/regress/expected/copy.out 2009-09-17 11:45:04.041818319 -0700 > --- /home/dc0lish/workspace/postgresql/src/test/regress/results/copy.out 2009-09-17 11:45:14.215152558 -0700 > *************** > *** 72,88 **** > 1,a,1 > 2,b,2 > -- Repeat the above tests with the new 8.5 option syntax > ! copy copytest to '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv); > truncate copytest2; > ! copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv); > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > (0 rows) > > truncate copytest2; > ! copy copytest to '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv, csv_quote '''',csv_escape E'\\'); > ! copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv, csv_quote '''',csv_escape E'\\'); > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > --- 72,88 ---- > 1,a,1 > 2,b,2 > -- Repeat the above tests with the new 8.5 option syntax > ! copy copytest to '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv); > truncate copytest2; > ! copy copytest2 from '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv); > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > (0 rows) > > truncate copytest2; > ! copy copytest to '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv, csv_quote '''', csv_escapeE'\\'); > ! copy copytest2 from '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv, csv_quote '''',csv_escape E'\\'); > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > *************** > *** 95,111 **** > 1,a,1 > 2,b,2 > -- Repeat the above tests with the new 8.5 option syntax from psql > ! \copy copytest to '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv) > truncate copytest2; > ! \copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv) > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > (0 rows) > > truncate copytest2; > ! \copy copytest to '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv, csv_quote '''',csv_escape E'\\') > ! \copy copytest2 from '/home/manu/workspace/Postgres8.5-COPY/src/test/regress/results/copytest.csv' (csv, csv_quote '''',csv_escape E'\\') > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > --- 95,111 ---- > 1,a,1 > 2,b,2 > -- Repeat the above tests with the new 8.5 option syntax from psql > ! \copy copytest to '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv) > truncate copytest2; > ! \copy copytest2 from '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv) > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > (0 rows) > > truncate copytest2; > ! \copy copytest to '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv, csv_quote '''', csv_escapeE'\\') > ! \copy copytest2 from '/home/dc0lish/workspace/postgresql/src/test/regress/results/copytest.csv' (csv, csv_quote '''',csv_escape E'\\') > select * from copytest except select * from copytest2; > style | test | filler > -------+------+-------- > > ====================================================================== > > -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com ### Eclipse Workspace Patch 1.0 #P Postgres8.5-COPY 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 -u -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 18:54:20 -0000 @@ -73,17 +73,17 @@ \. -- various COPY options: delimiters, oids, NULL string -COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x'; +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 WITH DELIMITER AS ';' NULL AS ''; +COPY x from stdin (DELIMITER ';', NULL ''); 3000;;c;; \. -COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X'; +COPY x from stdin (DELIMITER ':', NULL E'\\X'); 4000:\X:C:\X:\X 4001:1:empty:: 4002:2:null:\X:\X @@ -108,13 +108,13 @@ 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; +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 WITH NULL 'I''m null'; +COPY x (b, e) TO stdout (NULL 'I''m null'); CREATE TEMP TABLE y ( col1 text, @@ -130,11 +130,23 @@ 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; +COPY testnl FROM stdin (CSV); 1,"a field with two LFs inside",2 @@ -143,14 +155,14 @@ -- test end of copy marker CREATE TEMP TABLE testeoc (a text); -COPY testeoc FROM stdin CSV; +COPY testeoc FROM stdin (CSV); a\. \.b c\.d "\." \. -COPY testeoc TO stdout CSV; +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 -u -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 18:54:20 -0000 @@ -104,7 +104,7 @@ BIT_OR(i4) AS "?" FROM bitwise_test; -COPY bitwise_test FROM STDIN NULL 'null'; +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,7 +171,7 @@ BOOL_OR(b3) AS "n" FROM bool_test; -COPY bool_test FROM STDIN NULL 'null'; +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 -u -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 18:54:20 -0000 @@ -61,7 +61,7 @@ -- -- Test headers, CSV and quotes -- -copy (select t from test1 where id = 1) to stdout csv header force quote t; +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 -u -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 18:54:20 -0000 @@ -326,7 +326,7 @@ | (1 row) -COPY bitwise_test FROM STDIN NULL 'null'; +COPY bitwise_test FROM STDIN (NULL 'null'); SELECT BIT_AND(i2) AS "1", BIT_AND(i4) AS "1", @@ -401,7 +401,7 @@ | (1 row) -COPY bool_test FROM STDIN NULL 'null'; +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 -u -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 18:54:20 -0000 @@ -93,7 +93,7 @@ -- -- Test headers, CSV and quotes -- -copy (select t from test1 where id = 1) to stdout csv header force quote t; +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 -u -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 18:54:20 -0000 @@ -47,9 +47,9 @@ 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'; +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,9 +89,9 @@ 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; +COPY no_oids FROM stdin (OIDS); ERROR: table "no_oids" does not have OIDs -COPY no_oids TO stdout WITH OIDS; +COPY no_oids TO stdout (OIDS); ERROR: table "no_oids" does not have OIDs -- check copy out COPY x TO stdout; @@ -146,7 +146,7 @@ stuff after trigger fired stuff after trigger fired stuff after trigger fired -COPY x (b, e) TO stdout WITH NULL 'I''m null'; +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,13 +195,46 @@ "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; +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; +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 -u -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 18:54:20 -0000 @@ -24,27 +24,24 @@ <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> [, ...] ] + [ ( options [,...] ) ] 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> [, ...] | * } ] + [ ( 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,6 +140,27 @@ </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,7 +186,7 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">delimiter</replaceable></term> + <term><literal>DELIMITER</literal></term> <listitem> <para> The single ASCII character that separates columns within each row @@ -179,7 +197,7 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">null string</replaceable></term> + <term><literal>NULL</literal></term> <listitem> <para> The string that represents a null value. The default is @@ -211,7 +229,7 @@ </varlistentry> <varlistentry> - <term><literal>HEADER</literal></term> + <term><literal>CSV_HEADER</literal></term> <listitem> <para> Specifies that the file contains a header line with the names of each @@ -222,7 +240,7 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">quote</replaceable></term> + <term><literal>CSV_QUOTE</literal></term> <listitem> <para> Specifies the ASCII quotation character in <literal>CSV</> mode. @@ -232,18 +250,18 @@ </varlistentry> <varlistentry> - <term><replaceable class="parameter">escape</replaceable></term> + <term><literal>CSV_ESCAPE</literal></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). + <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>FORCE QUOTE</></term> + <term><literal>CSV_FORCE_QUOTE</></term> <listitem> <para> In <literal>CSV</> <command>COPY TO</> mode, forces quoting to be @@ -256,7 +274,7 @@ </varlistentry> <varlistentry> - <term><literal>FORCE NOT NULL</></term> + <term><literal>CSV_FORCE_NOT_NULL</></term> <listitem> <para> In <literal>CSV</> <command>COPY FROM</> mode, process each @@ -295,7 +313,7 @@ </para> <para> - The <literal>BINARY</literal> key word causes all data to be + 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,12 +556,12 @@ <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 + <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>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 + 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,7 +575,7 @@ 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 + use <literal>CSV_FORCE_NOT_NULL</> to prevent <literal>NULL</> input comparisons for specific columns. </para> @@ -577,7 +595,7 @@ <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 + <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,7 +777,7 @@ 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 '|'; +COPY country TO STDOUT (DELIMITER '|'); </programlisting> </para> @@ -809,6 +827,12 @@ 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,7 +841,35 @@ <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 -u -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 18:54:20 -0000 @@ -373,6 +373,10 @@ %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,14 +1938,19 @@ /***************************************************************************** * * QUERY : - * COPY relname ['(' columnList ')'] FROM/TO file [WITH options] - * - * BINARY, OIDS, and DELIMITERS kept in old locations - * for backward compatibility. 2002-06-18 + * 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] - * This form doesn't have the backwards-compatible option - * syntax. + * + * 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,6 +2010,7 @@ copy_opt_list: copy_opt_list copy_opt_item { $$ = lappend($1, $2); } + | '(' copy_generic_option_list ')' { $$ = $2 ; } | /* EMPTY */ { $$ = NIL; } ; @@ -2028,27 +2038,27 @@ } | HEADER_P { - $$ = makeDefElem("header", (Node *)makeInteger(TRUE)); + $$ = makeDefElem("csv_header", (Node *)makeInteger(TRUE)); } | QUOTE opt_as Sconst { - $$ = makeDefElem("quote", (Node *)makeString($3)); + $$ = makeDefElem("csv_quote", (Node *)makeString($3)); } | ESCAPE opt_as Sconst { - $$ = makeDefElem("escape", (Node *)makeString($3)); + $$ = makeDefElem("csv_escape", (Node *)makeString($3)); } | FORCE QUOTE columnList { - $$ = makeDefElem("force_quote", (Node *)$3); + $$ = makeDefElem("csv_force_quote", (Node *)$3); } | FORCE QUOTE '*' { - $$ = makeDefElem("force_quote", (Node *)makeNode(A_Star)); + $$ = makeDefElem("csv_force_quote", (Node *)makeNode(A_Star)); } | FORCE NOT NULL_P columnList { - $$ = makeDefElem("force_notnull", (Node *)$4); + $$ = makeDefElem("csv_force_not_null", (Node *)$4); } ; @@ -2084,6 +2094,53 @@ | /*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 -u -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 18:54:20 -0000 @@ -26,20 +26,27 @@ #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 + * [ 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 + * [ 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,21 +61,11 @@ 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; + 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,15 +74,9 @@ { 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->before_tofrom); + free(ptr->after_tofrom); free(ptr); } @@ -128,16 +119,19 @@ 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) { - result->binary = true; token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); if (!token) goto error; - } - result->table = pg_strdup(token); + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); + } /* Handle COPY (SELECT) case */ if (token[0] == '(') @@ -154,55 +148,35 @@ parens++; else if (token[0] == ')') parens--; - xstrcat(&result->table, " "); - xstrcat(&result->table, token); + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); } } - token = strtokx(NULL, whitespace, ".,()", "\"", + 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] == '(') { + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); + /* 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, ".,()", "\"", + token = strtokx(NULL, whitespace, ")", "\"", 0, false, false, pset.encoding); + if (!token) goto error; - xstrcat(&result->column_list, token); + + xstrcat(&result->before_tofrom, " "); + xstrcat(&result->before_tofrom, token); + if (token[0] == ')') break; - if (token[0] != ',') - goto error; } token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, pset.encoding); @@ -241,154 +215,12 @@ expand_tilde(&result->file); } - token = strtokx(NULL, whitespace, NULL, NULL, - 0, false, false, pset.encoding); + /* Catch the rest of the COPY options */ + token = strtokx(NULL, "", 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); - } - } + result->after_tofrom = pg_strdup(token); free(line); @@ -407,23 +239,6 @@ /* - * 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. @@ -445,54 +260,29 @@ 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"); + 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 - appendPQExpBuffer(&query, "TO STDOUT"); - - - if (options->binary) - appendPQExpBuffer(&query, " BINARY "); - - if (options->oids) - appendPQExpBuffer(&query, " OIDS "); + { /* Replace the filename with STDIN/STDOUT */ + appendPQExpBuffer(&query, "%s ", options->before_tofrom); - 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->from) + appendPQExpBuffer(&query, " FROM STDIN "); + else + appendPQExpBuffer(&query, " TO STDOUT "); - if (options->force_notnull_list) - appendPQExpBuffer(&query, " FORCE NOT NULL %s", options->force_notnull_list); + if (options->after_tofrom) + appendPQExpBuffer(&query, "%s", options->after_tofrom); - if (options->file) - canonicalize_path(options->file); + if (options->file) + canonicalize_path(options->file); + } - if (options->from) + if (options->from) { if (options->file) copystream = fopen(options->file, PG_BINARY_R); @@ -504,8 +294,7 @@ else { if (options->file) - copystream = fopen(options->file, - options->binary ? PG_BINARY_W : "w"); + 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 -u -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 18:54:20 -0000 @@ -71,3 +71,49 @@ 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 -u -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 18:54:20 -0000 @@ -107,3 +107,58 @@ 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 -u -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 18:54:20 -0000 @@ -25,6 +25,7 @@ #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,7 +746,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->binary = intVal(defel->arg); + cstate->binary = defGetBoolean(defel); } else if (strcmp(defel->defname, "oids") == 0) { @@ -753,7 +754,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->oids = intVal(defel->arg); + cstate->oids = defGetBoolean(defel); } else if (strcmp(defel->defname, "delimiter") == 0) { @@ -761,7 +762,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->delim = strVal(defel->arg); + cstate->delim = defGetString(defel); } else if (strcmp(defel->defname, "null") == 0) { @@ -769,7 +770,7 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->null_print = strVal(defel->arg); + cstate->null_print = defGetString(defel); } else if (strcmp(defel->defname, "csv") == 0) { @@ -777,33 +778,33 @@ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->csv_mode = intVal(defel->arg); + cstate->csv_mode = defGetBoolean(defel); } - else if (strcmp(defel->defname, "header") == 0) + 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 = intVal(defel->arg); + cstate->header_line = defGetBoolean(defel); } - else if (strcmp(defel->defname, "quote") == 0) + else if (strcmp(defel->defname, "csv_quote") == 0) { if (cstate->quote) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->quote = strVal(defel->arg); + cstate->quote = defGetString(defel); } - else if (strcmp(defel->defname, "escape") == 0) + else if (strcmp(defel->defname, "csv_escape") == 0) { if (cstate->escape) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - cstate->escape = strVal(defel->arg); + cstate->escape = defGetString(defel); } - else if (strcmp(defel->defname, "force_quote") == 0) + else if (strcmp(defel->defname, "csv_force_quote") == 0) { if (force_quote || force_quote_all) ereport(ERROR, @@ -811,20 +812,45 @@ errmsg("conflicting or redundant options"))); if (defel->arg && IsA(defel->arg, A_Star)) force_quote_all = true; - else + 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, "force_notnull") == 0) + else if (strcmp(defel->defname, "csv_force_not_null") == 0) { if (force_notnull) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"))); - force_notnull = (List *) defel->arg; + 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 - elog(ERROR, "option \"%s\" not recognized", - defel->defname); + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("option \"%s\" not recognized", + defel->defname))); } /* Check for incompatible options */
pgsql-hackers by date: