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