Hi
here is a version with both direction support.
postgres=# copy foo from '/tmp/1.jpg' (format raw);
COPY 1
Time: 93.021 ms
postgres=# \dt+ foo
List of relations
┌────────┬──────┬───────┬───────┬────────┬─────────────┐
│ Schema │ Name │ Type │ Owner │ Size │ Description │
╞════════╪══════╪═══════╪═══════╪════════╪═════════════╡
│ public │ foo │ table │ pavel │ 256 kB │ │
└────────┴──────┴───────┴───────┴────────┴─────────────┘
(1 row)
postgres=# \copy foo to '~/3.jpg' (format raw)
COPY 1
Time: 2.401 ms
Regards
Pavel
2015-07-02 17:02 GMT+02:00 Tom Lane <[email protected]>:
> Andrew Dunstan <[email protected]> writes:
> > Does the COPY line protocol even support binary data?
>
> The protocol, per se, just transmits a byte stream. There is a field
> in the CopyInResponse/CopyOutResponse messages that indicates whether
> a text or binary copy is being done. One thing we'd have to consider
> is whether "raw" mode is sufficiently different from binary to justify
> an additional value for this field, and if so whether that constitutes
> a protocol break.
>
> IIRC, psql wouldn't really care; it just transfers the byte stream to or
> from the target file, regardless of text or binary mode. But there might
> be other client libraries that are smarter and expect "binary" mode to
> mean the binary file format specified in the COPY reference page. So
> there may be value in being explicit about "raw" mode in these messages.
>
> A key point in all this is that people who need "raw" transfer probably
> need it in both directions, a point that your SELECT proposal cannot
> satisfy, but hacking COPY could. So I lean towards the latter really.
>
> regards, tom lane
>
commit 5599347d6b0b29a2674d465b3ff03164fce59810
Author: Pavel Stehule <[email protected]>
Date: Mon Jul 6 23:18:18 2015 +0200
COPY FROM/TO (FORMAT RAW)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 2850b47..4b7b64d 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -190,7 +190,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
Selects the data format to be read or written:
<literal>text</>,
<literal>csv</> (Comma Separated Values),
- or <literal>binary</>.
+ <literal>binary</> or <literal>raw</literal>.
The default is <literal>text</>.
</para>
</listitem>
@@ -881,6 +881,23 @@ OIDs to be shown as null if that ever proves desirable.
</para>
</refsect3>
</refsect2>
+
+ <refsect2>
+ <title>Raw Format</title>
+
+ <para>
+ The <literal>raw</literal> format option causes all data to be
+ stored/read as binary format rather than as text. It shares format
+ for data with <literal>binary</literal> format. This format doesn't
+ use any metadata - only row data in network byte order are exported
+ or imported.
+ </para>
+
+ <para>
+ Because this format doesn't support any delimiter, only one value
+ can be exported or imported. NULL values are not allowed.
+ </para>
+ </refsect2>
</refsect1>
<refsect1>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 8904676..2ad7eb1 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -92,6 +92,11 @@ typedef enum EolType
* it's faster to make useless comparisons to trailing bytes than it is to
* invoke pg_encoding_mblen() to skip over them. encoding_embeds_ascii is TRUE
* when we have to do it the hard way.
+ *
+ * COPY supports three modes: text, binary and raw. The text format is plain
+ * text multiline format with specified delimiter. The binary format holds
+ * metadata (numbers, sizes) and data. The raw format holds data only and
+ * only one non NULL value can be processed.
*/
typedef struct CopyStateData
{
@@ -113,6 +118,7 @@ typedef struct CopyStateData
char *filename; /* filename, or NULL for STDIN/STDOUT */
bool is_program; /* is 'filename' a program to popen? */
bool binary; /* binary format? */
+ bool raw; /* required raw binary? */
bool oids; /* include OIDs? */
bool freeze; /* freeze rows on loading? */
bool csv_mode; /* Comma Separated Value format? */
@@ -202,6 +208,9 @@ typedef struct CopyStateData
char *raw_buf;
int raw_buf_index; /* next byte to process */
int raw_buf_len; /* total # of bytes stored */
+
+ /* field for RAW mode */
+ bool row_processed; /* true, when first row was processed */
} CopyStateData;
/* DestReceiver for COPY (SELECT) TO */
@@ -345,9 +354,16 @@ SendCopyBegin(CopyState cstate)
/* new way */
StringInfoData buf;
int natts = list_length(cstate->attnumlist);
- int16 format = (cstate->binary ? 1 : 0);
+ int16 format;
int i;
+ if (cstate->raw)
+ format = 2;
+ else if (cstate->binary)
+ format = 1;
+ else
+ format = 0;
+
pq_beginmessage(&buf, 'H');
pq_sendbyte(&buf, format); /* overall format */
pq_sendint(&buf, natts, 2);
@@ -359,7 +375,7 @@ SendCopyBegin(CopyState cstate)
else if (PG_PROTOCOL_MAJOR(FrontendProtocol) >= 2)
{
/* old way */
- if (cstate->binary)
+ if (cstate->binary && cstate->raw)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY BINARY is not supported to stdout or from stdin")));
@@ -371,7 +387,7 @@ SendCopyBegin(CopyState cstate)
else
{
/* very old way */
- if (cstate->binary)
+ if (cstate->binary && cstate->raw)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY BINARY is not supported to stdout or from stdin")));
@@ -390,9 +406,16 @@ ReceiveCopyBegin(CopyState cstate)
/* new way */
StringInfoData buf;
int natts = list_length(cstate->attnumlist);
- int16 format = (cstate->binary ? 1 : 0);
+ int16 format;
int i;
+ if (cstate->raw)
+ format = 2;
+ else if (cstate->binary)
+ format = 1;
+ else
+ format = 0;
+
pq_beginmessage(&buf, 'G');
pq_sendbyte(&buf, format); /* overall format */
pq_sendint(&buf, natts, 2);
@@ -405,7 +428,7 @@ ReceiveCopyBegin(CopyState cstate)
else if (PG_PROTOCOL_MAJOR(FrontendProtocol) >= 2)
{
/* old way */
- if (cstate->binary)
+ if (cstate->binary || cstate->raw)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY BINARY is not supported to stdout or from stdin")));
@@ -417,7 +440,7 @@ ReceiveCopyBegin(CopyState cstate)
else
{
/* very old way */
- if (cstate->binary)
+ if (cstate->binary || cstate->raw)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY BINARY is not supported to stdout or from stdin")));
@@ -485,7 +508,7 @@ CopySendEndOfRow(CopyState cstate)
switch (cstate->copy_dest)
{
case COPY_FILE:
- if (!cstate->binary)
+ if (!cstate->binary && !cstate->raw)
{
/* Default line termination depends on platform */
#ifndef WIN32
@@ -530,7 +553,7 @@ CopySendEndOfRow(CopyState cstate)
break;
case COPY_OLD_FE:
/* The FE/BE protocol uses \n as newline for all platforms */
- if (!cstate->binary)
+ if (!cstate->binary && !cstate->raw)
CopySendChar(cstate, '\n');
if (pq_putbytes(fe_msgbuf->data, fe_msgbuf->len))
@@ -543,7 +566,7 @@ CopySendEndOfRow(CopyState cstate)
break;
case COPY_NEW_FE:
/* The FE/BE protocol uses \n as newline for all platforms */
- if (!cstate->binary)
+ if (!cstate->binary && !cstate->raw)
CopySendChar(cstate, '\n');
/* Dump the accumulated row as one CopyData message */
@@ -600,6 +623,7 @@ CopyGetData(CopyState cstate, void *databuf, int minread, int maxread)
bytesread = minread;
break;
case COPY_NEW_FE:
+
while (maxread > 0 && bytesread < minread && !cstate->fe_eof)
{
int avail;
@@ -769,6 +793,36 @@ CopyLoadRawBuf(CopyState cstate)
return (inbytes > 0);
}
+/*
+ * CopyLoadAllRawBuf load all file into raw_buf.
+ *
+ * It is used for reading content in raw mode. If original RAW_BUF_SIZE is not
+ * enough, the buffer is enlarged.
+ */
+static void
+CopyLoadAllRawBuf(CopyState cstate)
+{
+ int nbytes = 0;
+ int inbytes;
+ Size raw_buf_size = RAW_BUF_SIZE;
+
+ inbytes = CopyGetData(cstate, cstate->raw_buf + nbytes, 1, RAW_BUF_SIZE);
+ while (inbytes == RAW_BUF_SIZE)
+ {
+ nbytes += inbytes;
+
+ /* Have to enlarge raw_buf */
+ raw_buf_size += RAW_BUF_SIZE + 1;
+ cstate->raw_buf = repalloc(cstate->raw_buf, raw_buf_size);
+
+ inbytes = CopyGetData(cstate, cstate->raw_buf + nbytes, 1, RAW_BUF_SIZE);
+ }
+
+ nbytes += inbytes;
+ cstate->raw_buf[nbytes] = '\0';
+ cstate->raw_buf_index = 0;
+ cstate->raw_buf_len = nbytes;
+}
/*
* DoCopy executes the SQL COPY statement
@@ -1006,6 +1060,8 @@ ProcessCopyOptions(CopyState cstate,
cstate->csv_mode = true;
else if (strcmp(fmt, "binary") == 0)
cstate->binary = true;
+ else if (strcmp(fmt, "raw") == 0)
+ cstate->raw = true;
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1155,15 +1211,20 @@ ProcessCopyOptions(CopyState cstate,
* Check for incompatible options (must do these two before inserting
* defaults)
*/
- if (cstate->binary && cstate->delim)
+ if ((cstate->binary || cstate->raw) && cstate->delim)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot specify DELIMITER in BINARY or RAW mode")));
+
+ if ((cstate->binary || cstate->raw) && cstate->null_print)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot specify DELIMITER in BINARY mode")));
+ errmsg("cannot specify NULL in BINARY or RAW mode")));
- if (cstate->binary && cstate->null_print)
+ if (cstate->raw && cstate->oids)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot specify NULL in BINARY mode")));
+ errmsg("cannot specify OIDS in RAW mode")));
/* Set defaults for omitted options */
if (!cstate->delim)
@@ -1559,6 +1620,20 @@ BeginCopy(bool is_from,
}
}
+ /*
+ * Initializaze the field "row_processed" for one row output in RAW mode,
+ * and ensure only one output column.
+ */
+ if (cstate->raw)
+ {
+ cstate->row_processed = false;
+
+ if (num_phys_attrs > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only single column result is allowed in RAW mode")));
+ }
+
/* Use client encoding when ENCODING option is not specified. */
if (cstate->file_encoding < 0)
cstate->file_encoding = pg_get_client_encoding();
@@ -1821,7 +1896,7 @@ CopyTo(CopyState cstate)
Oid out_func_oid;
bool isvarlena;
- if (cstate->binary)
+ if (cstate->binary || cstate->raw)
getTypeBinaryOutputInfo(attr[attnum - 1]->atttypid,
&out_func_oid,
&isvarlena);
@@ -1860,7 +1935,7 @@ CopyTo(CopyState cstate)
tmp = 0;
CopySendInt32(cstate, tmp);
}
- else
+ else if (!cstate->raw)
{
/*
* For non-binary copy, we need to convert null_print to file
@@ -1928,7 +2003,7 @@ CopyTo(CopyState cstate)
else
{
/* run the plan --- the dest receiver will send tuples */
- ExecutorRun(cstate->queryDesc, ForwardScanDirection, 0L);
+ ExecutorRun(cstate->queryDesc, ForwardScanDirection, cstate->raw ? 2L : 0L);
processed = ((DR_copy *) cstate->queryDesc->dest)->processed;
}
@@ -1972,6 +2047,14 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls)
CopySendInt32(cstate, tupleOid);
}
}
+ else if (cstate->raw)
+ {
+ if (cstate->row_processed)
+ ereport(ERROR,
+ (errcode(ERRCODE_TOO_MANY_ROWS),
+ errmsg("only single row result is allowed in RAW mode")));
+ cstate->row_processed = true;
+ }
else
{
/* Text format has no per-tuple header, but send OID if wanted */
@@ -1991,7 +2074,7 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls)
Datum value = values[attnum - 1];
bool isnull = nulls[attnum - 1];
- if (!cstate->binary)
+ if (!cstate->binary && !cstate->raw)
{
if (need_delim)
CopySendChar(cstate, cstate->delim[0]);
@@ -2000,14 +2083,32 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls)
if (isnull)
{
- if (!cstate->binary)
+ if (cstate->raw)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("cannot to copy NULL value in RAW mode.")));
+ else if (!cstate->binary)
CopySendString(cstate, cstate->null_print_client);
else
CopySendInt32(cstate, -1);
}
else
{
- if (!cstate->binary)
+ if (cstate->binary || cstate->raw)
+ {
+ bytea *outputbytes;
+
+ outputbytes = SendFunctionCall(&out_functions[attnum - 1],
+ value);
+
+ /* send the size only in binary mode */
+ if (cstate->binary)
+ CopySendInt32(cstate, VARSIZE(outputbytes) - VARHDRSZ);
+
+ CopySendData(cstate, VARDATA(outputbytes),
+ VARSIZE(outputbytes) - VARHDRSZ);
+ }
+ else
{
string = OutputFunctionCall(&out_functions[attnum - 1],
value);
@@ -2018,16 +2119,6 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls)
else
CopyAttributeOutText(cstate, string);
}
- else
- {
- bytea *outputbytes;
-
- outputbytes = SendFunctionCall(&out_functions[attnum - 1],
- value);
- CopySendInt32(cstate, VARSIZE(outputbytes) - VARHDRSZ);
- CopySendData(cstate, VARDATA(outputbytes),
- VARSIZE(outputbytes) - VARHDRSZ);
- }
}
}
@@ -2657,7 +2748,7 @@ BeginCopyFrom(Relation rel,
continue;
/* Fetch the input function and typioparam info */
- if (cstate->binary)
+ if (cstate->binary || cstate->raw)
getTypeBinaryInputInfo(attr[attnum - 1]->atttypid,
&in_func_oid, &typioparams[attnum - 1]);
else
@@ -2752,7 +2843,7 @@ BeginCopyFrom(Relation rel,
}
}
- if (!cstate->binary)
+ if (!cstate->binary || cstate->raw)
{
/* must rely on user to tell us... */
cstate->file_has_oids = cstate->oids;
@@ -2804,7 +2895,7 @@ BeginCopyFrom(Relation rel,
}
/* create workspace for CopyReadAttributes results */
- if (!cstate->binary)
+ if (!cstate->binary && !cstate->raw)
{
AttrNumber attr_count = list_length(cstate->attnumlist);
int nfields = cstate->file_has_oids ? (attr_count + 1) : attr_count;
@@ -2909,8 +3000,120 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
MemSet(values, 0, num_phys_attrs * sizeof(Datum));
MemSet(nulls, true, num_phys_attrs * sizeof(bool));
- if (!cstate->binary)
+ if (cstate->binary)
+ {
+ int16 fld_count;
+ ListCell *cur;
+
+ cstate->cur_lineno++;
+
+ if (!CopyGetInt16(cstate, &fld_count))
+ {
+ /* EOF detected (end of file, or protocol-level EOF) */
+ return false;
+ }
+
+ if (fld_count == -1)
+ {
+ /*
+ * Received EOF marker. In a V3-protocol copy, wait for the
+ * protocol-level EOF, and complain if it doesn't come
+ * immediately. This ensures that we correctly handle CopyFail,
+ * if client chooses to send that now.
+ *
+ * Note that we MUST NOT try to read more data in an old-protocol
+ * copy, since there is no protocol-level EOF marker then. We
+ * could go either way for copy from file, but choose to throw
+ * error if there's data after the EOF marker, for consistency
+ * with the new-protocol case.
+ */
+ char dummy;
+
+ if (cstate->copy_dest != COPY_OLD_FE &&
+ CopyGetData(cstate, &dummy, 1, 1) > 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+ errmsg("received copy data after EOF marker")));
+ return false;
+ }
+
+ if (fld_count != attr_count)
+ ereport(ERROR,
+ (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+ errmsg("row field count is %d, expected %d",
+ (int) fld_count, attr_count)));
+
+ if (file_has_oids)
+ {
+ Oid loaded_oid;
+
+ cstate->cur_attname = "oid";
+ loaded_oid =
+ DatumGetObjectId(CopyReadBinaryAttribute(cstate,
+ 0,
+ &cstate->oid_in_function,
+ cstate->oid_typioparam,
+ -1,
+ &isnull));
+ if (isnull || loaded_oid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+ errmsg("invalid OID in COPY data")));
+ cstate->cur_attname = NULL;
+ if (cstate->oids && tupleOid != NULL)
+ *tupleOid = loaded_oid;
+ }
+
+ i = 0;
+ foreach(cur, cstate->attnumlist)
+ {
+ int attnum = lfirst_int(cur);
+ int m = attnum - 1;
+
+ cstate->cur_attname = NameStr(attr[m]->attname);
+ i++;
+ values[m] = CopyReadBinaryAttribute(cstate,
+ i,
+ &in_functions[m],
+ typioparams[m],
+ attr[m]->atttypmod,
+ &nulls[m]);
+ cstate->cur_attname = NULL;
+ }
+ }
+ else if (cstate->raw)
{
+ if (cstate->row_processed)
+ return false;
+
+ CopyLoadAllRawBuf(cstate);
+ cstate->cur_attname = NameStr(attr[0]->attname);
+
+ if (cstate->attribute_buf.data != NULL)
+ pfree(cstate->attribute_buf.data);
+
+ cstate->attribute_buf.data = cstate->raw_buf;
+ cstate->attribute_buf.len = cstate->raw_buf_len;
+ cstate->attribute_buf.cursor = 0;
+
+ cstate->raw_buf = NULL;
+
+ /* Call the column type's binary input converter */
+ values[0] = ReceiveFunctionCall(&in_functions[0], &cstate->attribute_buf,
+ typioparams[0], attr[0]->atttypmod);
+ nulls[0] = false;
+
+ /* Trouble if it didn't eat the whole buffer */
+ if (cstate->attribute_buf.cursor != cstate->attribute_buf.len)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
+ errmsg("incorrect binary data format")));
+
+ cstate->row_processed = true;
+ }
+ else
+ {
+ /* text */
char **field_strings;
ListCell *cur;
int fldct;
@@ -3015,88 +3218,6 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
Assert(fieldno == nfields);
}
- else
- {
- /* binary */
- int16 fld_count;
- ListCell *cur;
-
- cstate->cur_lineno++;
-
- if (!CopyGetInt16(cstate, &fld_count))
- {
- /* EOF detected (end of file, or protocol-level EOF) */
- return false;
- }
-
- if (fld_count == -1)
- {
- /*
- * Received EOF marker. In a V3-protocol copy, wait for the
- * protocol-level EOF, and complain if it doesn't come
- * immediately. This ensures that we correctly handle CopyFail,
- * if client chooses to send that now.
- *
- * Note that we MUST NOT try to read more data in an old-protocol
- * copy, since there is no protocol-level EOF marker then. We
- * could go either way for copy from file, but choose to throw
- * error if there's data after the EOF marker, for consistency
- * with the new-protocol case.
- */
- char dummy;
-
- if (cstate->copy_dest != COPY_OLD_FE &&
- CopyGetData(cstate, &dummy, 1, 1) > 0)
- ereport(ERROR,
- (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
- errmsg("received copy data after EOF marker")));
- return false;
- }
-
- if (fld_count != attr_count)
- ereport(ERROR,
- (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
- errmsg("row field count is %d, expected %d",
- (int) fld_count, attr_count)));
-
- if (file_has_oids)
- {
- Oid loaded_oid;
-
- cstate->cur_attname = "oid";
- loaded_oid =
- DatumGetObjectId(CopyReadBinaryAttribute(cstate,
- 0,
- &cstate->oid_in_function,
- cstate->oid_typioparam,
- -1,
- &isnull));
- if (isnull || loaded_oid == InvalidOid)
- ereport(ERROR,
- (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
- errmsg("invalid OID in COPY data")));
- cstate->cur_attname = NULL;
- if (cstate->oids && tupleOid != NULL)
- *tupleOid = loaded_oid;
- }
-
- i = 0;
- foreach(cur, cstate->attnumlist)
- {
- int attnum = lfirst_int(cur);
- int m = attnum - 1;
-
- cstate->cur_attname = NameStr(attr[m]->attname);
- i++;
- values[m] = CopyReadBinaryAttribute(cstate,
- i,
- &in_functions[m],
- typioparams[m],
- attr[m]->atttypmod,
- &nulls[m]);
- cstate->cur_attname = NULL;
- }
- }
/*
* Now compute and insert any defaults available for the columns not
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 5e31737..30e77ca 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -469,3 +469,16 @@ DROP FUNCTION truncate_in_subxact();
DROP TABLE x, y;
DROP FUNCTION fn_x_before();
DROP FUNCTION fn_x_after();
+CREATE TABLE x(a bytea);
+INSERT INTO x VALUES('\x41484f4a0a');
+INSERT INTO x VALUES('\x41484f4a0a');
+-- should to fail
+COPY (SELECT a,a FROM x LIMIT 1) TO STDOUT (FORMAT raw);
+ERROR: only single column result is allowed in RAW mode
+COPY (SELECT a FROM x) TO STDOUT (FORMAT raw);
+AHOJ
+ERROR: only single row result is allowed in RAW mode
+-- should be ok
+COPY (SELECT a FROM x LIMIT 1) TO STDOUT (FORMAT raw);
+AHOJ
+DROP TABLE x;
diff --git a/src/test/regress/input/copy.source b/src/test/regress/input/copy.source
index cb13606..d8970b1 100644
--- a/src/test/regress/input/copy.source
+++ b/src/test/regress/input/copy.source
@@ -133,3 +133,36 @@ this is just a line full of junk that would error out if parsed
\.
copy copytest3 to stdout csv header;
+
+-- copy raw
+CREATE TABLE x(a bytea);
+INSERT INTO x VALUES('\x41484f4a0a');
+SELECT length(a) FROM x;
+
+INSERT INTO x VALUES('\x41484f4a0a');
+
+-- should to fail
+COPY (SELECT a,a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw);
+COPY (SELECT a FROM x) TO '@abs_builddir@/results/raw.data' (FORMAT raw);
+
+-- should be ok
+COPY (SELECT a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw);
+TRUNCATE x;
+COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw);
+SELECT length(a) FROM x;
+COPY x TO stdout (FORMAT raw);
+
+TRUNCATE x;
+
+\COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw)
+SELECT length(a) FROM x;
+COPY x TO stdout (FORMAT raw);
+
+\COPY x TO '@abs_builddir@/results/raw2.data' (FORMAT raw)
+TRUNCATE x;
+
+\COPY x FROM '@abs_builddir@/results/raw2.data' (FORMAT raw)
+SELECT length(a) FROM x;
+COPY x TO stdout (FORMAT raw);
+
+DROP TABLE x;
diff --git a/src/test/regress/output/copy.source b/src/test/regress/output/copy.source
index b7e372d..878797a 100644
--- a/src/test/regress/output/copy.source
+++ b/src/test/regress/output/copy.source
@@ -95,3 +95,52 @@ copy copytest3 to stdout csv header;
c1,"col with , comma","col with "" quote"
1,a,1
2,b,2
+-- copy raw
+CREATE TABLE x(a bytea);
+INSERT INTO x VALUES('\x41484f4a0a');
+SELECT length(a) FROM x;
+ length
+--------
+ 5
+(1 row)
+
+INSERT INTO x VALUES('\x41484f4a0a');
+-- should to fail
+COPY (SELECT a,a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw);
+ERROR: only single column result is allowed in RAW mode
+COPY (SELECT a FROM x) TO '@abs_builddir@/results/raw.data' (FORMAT raw);
+ERROR: only single row result is allowed in RAW mode
+-- should be ok
+COPY (SELECT a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw);
+TRUNCATE x;
+COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw);
+SELECT length(a) FROM x;
+ length
+--------
+ 5
+(1 row)
+
+COPY x TO stdout (FORMAT raw);
+AHOJ
+TRUNCATE x;
+\COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw)
+SELECT length(a) FROM x;
+ length
+--------
+ 5
+(1 row)
+
+COPY x TO stdout (FORMAT raw);
+AHOJ
+\COPY x TO '@abs_builddir@/results/raw2.data' (FORMAT raw)
+TRUNCATE x;
+\COPY x FROM '@abs_builddir@/results/raw2.data' (FORMAT raw)
+SELECT length(a) FROM x;
+ length
+--------
+ 5
+(1 row)
+
+COPY x TO stdout (FORMAT raw);
+AHOJ
+DROP TABLE x;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 39a9deb..e5703a5 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -333,3 +333,16 @@ DROP FUNCTION truncate_in_subxact();
DROP TABLE x, y;
DROP FUNCTION fn_x_before();
DROP FUNCTION fn_x_after();
+
+CREATE TABLE x(a bytea);
+INSERT INTO x VALUES('\x41484f4a0a');
+INSERT INTO x VALUES('\x41484f4a0a');
+
+-- should to fail
+COPY (SELECT a,a FROM x LIMIT 1) TO STDOUT (FORMAT raw);
+COPY (SELECT a FROM x) TO STDOUT (FORMAT raw);
+
+-- should be ok
+COPY (SELECT a FROM x LIMIT 1) TO STDOUT (FORMAT raw);
+
+DROP TABLE x;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
