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 <m...@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_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
+-------+------+--------
+(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_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
+-------+------+--------
+(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 */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
