Re: generic copy options - Mailing list pgsql-hackers
| From | Emmanuel Cecchet |
|---|---|
| Subject | Re: generic copy options |
| Date | |
| Msg-id | 4AB16A1B.7060102@asterdata.com Whole thread Raw |
| In response to | Re: generic copy options (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: generic copy options
|
| List | pgsql-hackers |
Robert, Here is a new version of the patch with an updated doc and psql. I changed the name of the CSV options to prefix them with csv_ to avoid confusion with any future options. I also had to change the grammar to allow '*' as a parameter (needed for cvs_force_quote). When we decide to drop the old syntax (in 8.6?), we will be able to clean a lot especially in psql. Emmanuel > On Mon, Sep 14, 2009 at 2:51 PM, Emmanuel Cecchet <manu@asterdata.com> wrote: > >> This looks good. Shoud I try to elaborate on that for the patch with error >> logging and autopartitioning in COPY? >> > > That make sense to me. You shouldn't need to do anything else in > gram.y; whatever you want to add should just involve changing copy.c. > If not, please post the details. > > We also need to fix the psql end of this, and the docs... any > interest in taking a crack at either of those? > > ...Robert > > -- 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 16 Sep 2009 22:37:31 -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 16 Sep 2009 22:37:31 -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 16 Sep 2009 22:37:31 -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 16 Sep 2009 22:37:31 -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 16 Sep 2009 22:37:31 -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 16 Sep 2009 22:37:31 -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,48 @@ "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'\\') +csv_force_quote +"Jackson, Sam",\h +"It is \"perfect\".", +"", +\COPY y TO stdout (CSV, CSV_FORCE_QUOTE *) +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 16 Sep 2009 22:37:31 -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 + - OIDS + - DELIMITER '<replaceable class="parameter">delimiter</replaceable>' + - NULL '<replaceable class="parameter">null string</replaceable>' + - CSV + - CSV_HEADER + - 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,7 +140,13 @@ </para> </listitem> </varlistentry> + </variablelist> + </refsect1> + <refsect1> + <title>Options</title> + + <variablelist> <varlistentry> <term><literal>BINARY</literal></term> <listitem> @@ -168,7 +171,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 +182,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 +214,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 +225,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 +235,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 +259,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 +298,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 +541,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 +560,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 +580,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 +762,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 +812,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 +826,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 16 Sep 2009 22:37:31 -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; } ; @@ -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 16 Sep 2009 22:37:31 -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,26 +61,24 @@ 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 *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; }; - -static void -free_copy_options(struct copy_options * ptr) +static void free_copy_options(struct copy_options * ptr) { if (!ptr) return; @@ -89,12 +94,10 @@ free(ptr); } - /* concatenate "more" onto "var", freeing the original value of *var */ -static void -xstrcat(char **var, const char *more) +static void xstrcat(char **var, const char *more) { - char *newvar; + char *newvar; newvar = pg_malloc(strlen(*var) + strlen(more) + 1); strcpy(newvar, *var); @@ -103,15 +106,54 @@ *var = newvar; } +/* Extract parameters of the CSV_FORCE_QUOTE or CSV_FORCE_NOT_NULL options. + * Return true if the list was successfully extracted or false if an error + * occurred. */ +static bool extract_csv_force_list(char *list, char *token) +{ + const char *whitespace = " \t\n\r"; + const char *separator = "(,)"; + + for (;;) + { + token = strtokx(NULL, whitespace, separator, "\"", 0, false, false, + pset.encoding); + + if (!token) + return false; + if (strchr(separator, token[0]) != NULL) + continue; /* fetch next token */ + if (!list) + { + list = pg_strdup(token); + if (token[0] == '*') + return true; + } + else + { + if (token[0] == '*') /* '*' can only be accepted as a single argument */ + return false; + xstrcat(&list, token); + } + token = strtokx(NULL, whitespace, separator, "\"", 0, false, false, + pset.encoding); + if (!token || strchr(")", token[0])) + break; + if (strchr(separator, token[0]) != NULL) + continue; // fetch next token + xstrcat(&list, token); + } + return true; +} static struct copy_options * parse_slash_copy(const char *args) { struct copy_options *result; - char *line; - char *token; - const char *whitespace = " \t\n\r"; - char nonstd_backslash = standard_strings() ? 0 : '\\'; + char *line; + char *token; + char *whitespace = " \t\n\r"; + char nonstd_backslash = standard_strings() ? 0 : '\\'; if (args) line = pg_strdup(args); @@ -123,16 +165,16 @@ result = pg_calloc(1, sizeof(struct copy_options)); - token = strtokx(line, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); + token = strtokx(line, whitespace, ".,()", "\"", 0, false, false, + pset.encoding); if (!token) goto error; if (pg_strcasecmp(token, "binary") == 0) { result->binary = true; - token = strtokx(NULL, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); + token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, + pset.encoding); if (!token) goto error; } @@ -142,12 +184,12 @@ /* Handle COPY (SELECT) case */ if (token[0] == '(') { - int parens = 1; + int parens = 1; while (parens > 0) { - token = strtokx(NULL, whitespace, ".,()", "\"'", - nonstd_backslash, true, false, pset.encoding); + token = strtokx(NULL, whitespace, ".,()", "\"'", nonstd_backslash, + true, false, pset.encoding); if (!token) goto error; if (token[0] == '(') @@ -159,8 +201,8 @@ } } - token = strtokx(NULL, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); + token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, + pset.encoding); if (!token) goto error; @@ -172,13 +214,13 @@ { /* handle schema . table */ xstrcat(&result->table, token); - token = strtokx(NULL, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); + 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); + token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, + pset.encoding); if (!token) goto error; } @@ -189,13 +231,13 @@ result->column_list = pg_strdup(token); for (;;) { - token = strtokx(NULL, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); + 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); + token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, + pset.encoding); if (!token) goto error; xstrcat(&result->column_list, token); @@ -204,8 +246,8 @@ if (token[0] != ',') goto error; } - token = strtokx(NULL, whitespace, ".,()", "\"", - 0, false, false, pset.encoding); + token = strtokx(NULL, whitespace, ".,()", "\"", 0, false, false, + pset.encoding); if (!token) goto error; } @@ -217,19 +259,18 @@ else goto error; - token = strtokx(NULL, whitespace, NULL, "'", - 0, false, true, pset.encoding); + token = strtokx(NULL, whitespace, NULL, "'", 0, false, true, pset.encoding); if (!token) goto error; - if (pg_strcasecmp(token, "stdin") == 0 || - pg_strcasecmp(token, "stdout") == 0) + if (pg_strcasecmp(token, "stdin") == 0 || pg_strcasecmp(token, "stdout") + == 0) { result->psql_inout = false; result->file = NULL; } - else if (pg_strcasecmp(token, "pstdin") == 0 || - pg_strcasecmp(token, "pstdout") == 0) + else if (pg_strcasecmp(token, "pstdin") == 0 || pg_strcasecmp(token, + "pstdout") == 0) { result->psql_inout = true; result->file = NULL; @@ -241,22 +282,33 @@ expand_tilde(&result->file); } - token = strtokx(NULL, whitespace, NULL, NULL, - 0, false, false, pset.encoding); + token = strtokx(NULL, whitespace, "(", NULL, 0, false, false, + pset.encoding); if (token) { + char *option_separator = NULL; + + /* New 8.5 syntax, option are between () */ + if (token[0] == '(') + { + option_separator = ",)"; + token = strtokx(NULL, whitespace, NULL, option_separator, 0, false, false, + pset.encoding); + } /* * 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); + else if (pg_strcasecmp(token, "with") == 0) + { + token = strtokx(NULL, whitespace, NULL, NULL, 0, false, false, + pset.encoding); + } while (token) { - bool fetch_next; + bool fetch_next; fetch_next = true; @@ -266,17 +318,18 @@ result->binary = true; else if (pg_strcasecmp(token, "csv") == 0) result->csv_mode = true; - else if (pg_strcasecmp(token, "header") == 0) + else if ((pg_strcasecmp(token, "header") == 0) || (pg_strcasecmp( + token, "csv_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); + 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); + nonstd_backslash, true, false, pset.encoding); if (token) result->delim = pg_strdup(token); else @@ -286,39 +339,41 @@ { if (result->null) goto error; - token = strtokx(NULL, whitespace, NULL, "'", - nonstd_backslash, true, false, pset.encoding); + 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); + nonstd_backslash, true, false, pset.encoding); if (token) result->null = pg_strdup(token); else goto error; } - else if (pg_strcasecmp(token, "quote") == 0) + else if ((pg_strcasecmp(token, "quote") == 0) || (pg_strcasecmp( + token, "csv_quote") == 0)) { if (result->quote) goto error; - token = strtokx(NULL, whitespace, NULL, "'", - nonstd_backslash, true, false, pset.encoding); + 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); + nonstd_backslash, true, false, pset.encoding); if (token) result->quote = pg_strdup(token); else goto error; } - else if (pg_strcasecmp(token, "escape") == 0) + else if ((pg_strcasecmp(token, "escape") == 0) || (pg_strcasecmp( + token, "csv_escape") == 0)) { if (result->escape) goto error; - token = strtokx(NULL, whitespace, NULL, "'", - nonstd_backslash, true, false, pset.encoding); + 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); + nonstd_backslash, true, false, pset.encoding); if (token) result->escape = pg_strdup(token); else @@ -326,67 +381,62 @@ } else if (pg_strcasecmp(token, "force") == 0) { - token = strtokx(NULL, whitespace, ",", "\"", - 0, false, false, pset.encoding); + 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); - } + if (!extract_csv_force_list(result->force_quote_list, token)) + goto error; } else if (pg_strcasecmp(token, "not") == 0) { if (result->force_notnull_list) goto error; - token = strtokx(NULL, whitespace, ",", "\"", - 0, false, false, pset.encoding); + 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); - } + if (!extract_csv_force_list(result->force_notnull_list, + token)) + goto error; } else goto error; } + else if (pg_strcasecmp(token, "csv_force_quote") == 0) + { + printf("csv_force_quote\n"); + if (result->force_quote_list) + goto error; + if (!extract_csv_force_list(result->force_quote_list, token)) + goto error; + } + else if (pg_strcasecmp(token, "csv_force_not_null") == 0) + { + if (result->force_notnull_list) + goto error; + if (!extract_csv_force_list(result->force_notnull_list, token)) + goto error; + } else goto error; if (fetch_next) - token = strtokx(NULL, whitespace, NULL, NULL, - 0, false, false, pset.encoding); + { + token = strtokx(NULL, whitespace, option_separator, NULL, 0, + false, false, pset.encoding); + if ((option_separator != NULL) && (strchr(option_separator, token[0]))) + { + token = strtokx(NULL, whitespace, option_separator, NULL, + 0, false, false, pset.encoding); + } + } } } @@ -394,8 +444,7 @@ return result; -error: - if (token) + error: if (token) psql_error("\\copy: parse error at \"%s\"\n", token); else psql_error("\\copy: parse error at end of line\n"); @@ -405,37 +454,34 @@ return NULL; } - /* * 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) +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] == '\'')) + 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. */ -bool -do_copy(const char *args) +bool do_copy(const char *args) { PQExpBufferData query; - FILE *copystream; + FILE *copystream; struct copy_options *options; - PGresult *result; - bool success; + PGresult *result; + bool success; struct stat st; /* parse options */ @@ -458,7 +504,6 @@ else appendPQExpBuffer(&query, "TO STDOUT"); - if (options->binary) appendPQExpBuffer(&query, " BINARY "); @@ -487,7 +532,8 @@ appendPQExpBuffer(&query, " FORCE QUOTE %s", options->force_quote_list); if (options->force_notnull_list) - appendPQExpBuffer(&query, " FORCE NOT NULL %s", options->force_notnull_list); + appendPQExpBuffer(&query, " FORCE NOT NULL %s", + options->force_notnull_list); if (options->file) canonicalize_path(options->file); @@ -504,8 +550,8 @@ else { if (options->file) - copystream = fopen(options->file, - options->binary ? PG_BINARY_W : "w"); + copystream = fopen(options->file, options->binary ? PG_BINARY_W + : "w"); else if (!options->psql_inout) copystream = pset.queryFout; else @@ -514,8 +560,7 @@ if (!copystream) { - psql_error("%s: %s\n", - options->file, strerror(errno)); + psql_error("%s: %s\n", options->file, strerror(errno)); free_copy_options(options); return false; } @@ -525,8 +570,7 @@ if (S_ISDIR(st.st_mode)) { fclose(copystream); - psql_error("%s: cannot copy from/to a directory\n", - options->file); + psql_error("%s: cannot copy from/to a directory\n", options->file); free_copy_options(options); return false; } @@ -536,28 +580,26 @@ switch (PQresultStatus(result)) { - case PGRES_COPY_OUT: - SetCancelConn(); - success = handleCopyOut(pset.db, copystream); - ResetCancelConn(); - break; - case PGRES_COPY_IN: - SetCancelConn(); - success = handleCopyIn(pset.db, copystream, - PQbinaryTuples(result)); - ResetCancelConn(); - break; - case PGRES_NONFATAL_ERROR: - case PGRES_FATAL_ERROR: - case PGRES_BAD_RESPONSE: - success = false; - psql_error("\\copy: %s", PQerrorMessage(pset.db)); - break; - default: - success = false; - psql_error("\\copy: unexpected response (%d)\n", - PQresultStatus(result)); - break; + case PGRES_COPY_OUT: + SetCancelConn(); + success = handleCopyOut(pset.db, copystream); + ResetCancelConn(); + break; + case PGRES_COPY_IN: + SetCancelConn(); + success = handleCopyIn(pset.db, copystream, PQbinaryTuples(result)); + ResetCancelConn(); + break; + case PGRES_NONFATAL_ERROR: + case PGRES_FATAL_ERROR: + case PGRES_BAD_RESPONSE: + success = false; + psql_error("\\copy: %s", PQerrorMessage(pset.db)); + break; + default: + success = false; + psql_error("\\copy: unexpected response (%d)\n", PQresultStatus(result)); + break; } PQclear(result); @@ -569,8 +611,7 @@ while ((result = PQgetResult(pset.db)) != NULL) { success = false; - psql_error("\\copy: unexpected response (%d)\n", - PQresultStatus(result)); + psql_error("\\copy: unexpected response (%d)\n", PQresultStatus(result)); /* if still in COPY IN state, try to get out of it */ if (PQresultStatus(result) == PGRES_COPY_IN) PQputCopyEnd(pset.db, _("trying to exit copy mode")); @@ -589,7 +630,6 @@ return success; } - /* * Functions for handling COPY IN/OUT data transfer. * @@ -607,28 +647,27 @@ * * result is true if successful, false if not. */ -bool -handleCopyOut(PGconn *conn, FILE *copystream) +bool handleCopyOut(PGconn *conn, FILE *copystream) { - bool OK = true; - char *buf; - int ret; - PGresult *res; + bool OK = true; + char *buf; + int ret; + PGresult *res; for (;;) { ret = PQgetCopyData(conn, &buf, 0); if (ret < 0) - break; /* done or error */ + break; /* done or error */ if (buf) { if (fwrite(buf, 1, ret, copystream) != ret) { - if (OK) /* complain only once, keep reading data */ - psql_error("could not write COPY data: %s\n", - strerror(errno)); + if (OK) /* complain only once, keep reading data */ + psql_error("could not write COPY data: %s\n", strerror( + errno)); OK = false; } PQfreemem(buf); @@ -637,8 +676,7 @@ if (OK && fflush(copystream)) { - psql_error("could not write COPY data: %s\n", - strerror(errno)); + psql_error("could not write COPY data: %s\n", strerror(errno)); OK = false; } @@ -675,13 +713,12 @@ /* read chunk size for COPY IN - size is not critical */ #define COPYBUFSIZ 8192 -bool -handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary) +bool handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary) { - bool OK; + bool OK; const char *prompt; - char buf[COPYBUFSIZ]; - PGresult *res; + char buf[COPYBUFSIZ]; + PGresult *res; /* * Establish longjmp destination for exiting from wait-for-input. (This is @@ -708,7 +745,7 @@ { if (!pset.quiet) puts(_("Enter data to be copied followed by a newline.\n" - "End with a backslash and a period on a line by itself.")); + "End with a backslash and a period on a line by itself.")); prompt = get_prompt(PROMPT_COPY); } else @@ -727,7 +764,7 @@ for (;;) { - int buflen; + int buflen; /* enable longjmp while waiting for input */ sigint_interrupt_enabled = true; @@ -748,12 +785,12 @@ } else { - bool copydone = false; + bool copydone = false; while (!copydone) - { /* for each input line ... */ - bool firstload; - bool linedone; + { /* for each input line ... */ + bool firstload; + bool linedone; if (prompt) { @@ -765,9 +802,9 @@ linedone = false; while (!linedone) - { /* for each bufferload in line ... */ - int linelen; - char *fgresult; + { /* for each bufferload in line ... */ + int linelen; + char *fgresult; /* enable longjmp while waiting for input */ sigint_interrupt_enabled = true; @@ -791,8 +828,8 @@ /* check for EOF marker, but not on a partial line */ if (firstload) { - if (strcmp(buf, "\\.\n") == 0 || - strcmp(buf, "\\.\r\n") == 0) + if (strcmp(buf, "\\.\n") == 0 || strcmp(buf, "\\.\r\n") + == 0) { copydone = true; break; @@ -818,8 +855,8 @@ OK = false; /* Terminate data transfer */ - if (PQputCopyEnd(conn, - OK ? NULL : _("aborted because of read failure")) <= 0) + if (PQputCopyEnd(conn, OK ? NULL : _("aborted because of read failure")) + <= 0) OK = false; /* Check command status and return to normal libpq state */ 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 16 Sep 2009 22:37:31 -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 '/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_escapeE'\\'); +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 +-------+------+-------- +(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 '/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_escapeE'\\') +\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 +-------+------+-------- +(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 16 Sep 2009 22:37:31 -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 16 Sep 2009 22:37:31 -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, "header") == 0) || (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, "quote") == 0) || (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, "escape") == 0) || (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, "force_quote") == 0) || (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, "force_not_null") == 0) || (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: