35.10. Functions Associated with the COPY
Command
The COPY
command in Postgres Pro has options to read from or write to the network connection used by libpq. The functions described in this section allow applications to take advantage of this capability by supplying or consuming copied data.
The overall process is that the application first issues the SQL COPY
command via PQexec
or one of the equivalent functions. The response to this (if there is no error in the command) will be a PGresult
object bearing a status code of PGRES_COPY_OUT
or PGRES_COPY_IN
(depending on the specified copy direction). The application should then use the functions of this section to receive or transmit data rows. When the data transfer is complete, another PGresult
object is returned to indicate success or failure of the transfer. Its status will be PGRES_COMMAND_OK
for success or PGRES_FATAL_ERROR
if some problem was encountered. At this point further SQL commands can be issued via PQexec
. (It is not possible to execute other SQL commands using the same connection while the COPY
operation is in progress.)
If a COPY
command is issued via PQexec
in a string that could contain additional commands, the application must continue fetching results via PQgetResult
after completing the COPY
sequence. Only when PQgetResult
returns NULL
is it certain that the PQexec
command string is done and it is safe to issue more commands.
The functions of this section should be executed only after obtaining a result status of PGRES_COPY_OUT
or PGRES_COPY_IN
from PQexec
or PQgetResult
.
A PGresult
object bearing one of these status values carries some additional data about the COPY
operation that is starting. This additional data is available using functions that are also used in connection with query results:
PQnfields
Returns the number of columns (fields) to be copied.
PQbinaryTuples
0 indicates the overall copy format is textual (rows separated by newlines, columns separated by separator characters, etc). 1 indicates the overall copy format is binary. See COPY for more information.
PQfformat
Returns the format code (0 for text, 1 for binary) associated with each column of the copy operation. The per-column format codes will always be zero when the overall copy format is textual, but the binary format can support both text and binary columns. (However, as of the current implementation of
COPY
, only binary columns appear in a binary copy; so the per-column formats always match the overall format at present.)
35.10.1. Functions for Sending COPY
Data
These functions are used to send data during COPY FROM STDIN
. They will fail if called when the connection is not in COPY_IN
state.
PQputCopyData
Sends data to the server during
COPY_IN
state.int PQputCopyData(PGconn *conn, const char *buffer, int nbytes);
Transmits the
COPY
data in the specifiedbuffer
, of lengthnbytes
, to the server. The result is 1 if the data was queued, zero if it was not queued because of full buffers (this will only happen in nonblocking mode), or -1 if an error occurred. (UsePQerrorMessage
to retrieve details if the return value is -1. If the value is zero, wait for write-ready and try again.)The application can divide the
COPY
data stream into buffer loads of any convenient size. Buffer-load boundaries have no semantic significance when sending. The contents of the data stream must match the data format expected by theCOPY
command; see COPY for details.PQputCopyEnd
Sends end-of-data indication to the server during
COPY_IN
state.int PQputCopyEnd(PGconn *conn, const char *errormsg);
Ends the
COPY_IN
operation successfully iferrormsg
isNULL
. Iferrormsg
is notNULL
then theCOPY
is forced to fail, with the string pointed to byerrormsg
used as the error message. (One should not assume that this exact error message will come back from the server, however, as the server might have already failed theCOPY
for its own reasons.)The result is 1 if the termination message was sent; or in nonblocking mode, this may only indicate that the termination message was successfully queued. (In nonblocking mode, to be certain that the data has been sent, you should next wait for write-ready and call
PQflush
, repeating until it returns zero.) Zero indicates that the function could not queue the termination message because of full buffers; this will only happen in nonblocking mode. (In this case, wait for write-ready and try thePQputCopyEnd
call again.) If a hard error occurs, -1 is returned; you can usePQerrorMessage
to retrieve details.After successfully calling
PQputCopyEnd
, callPQgetResult
to obtain the final result status of theCOPY
command. One can wait for this result to be available in the usual way. Then return to normal operation.
35.10.2. Functions for Receiving COPY
Data
These functions are used to receive data during COPY TO STDOUT
. They will fail if called when the connection is not in COPY_OUT
state.
PQgetCopyData
Receives data from the server during
COPY_OUT
state.int PQgetCopyData(PGconn *conn, char **buffer, int async);
Attempts to obtain another row of data from the server during a
COPY
. Data is always returned one data row at a time; if only a partial row is available, it is not returned. Successful return of a data row involves allocating a chunk of memory to hold the data. Thebuffer
parameter must be non-NULL
.*buffer
is set to point to the allocated memory, or toNULL
in cases where no buffer is returned. A non-NULL
result buffer should be freed usingPQfreemem
when no longer needed.When a row is successfully returned, the return value is the number of data bytes in the row (this will always be greater than zero). The returned string is always null-terminated, though this is probably only useful for textual
COPY
. A result of zero indicates that theCOPY
is still in progress, but no row is yet available (this is only possible whenasync
is true). A result of -1 indicates that theCOPY
is done. A result of -2 indicates that an error occurred (consultPQerrorMessage
for the reason).When
async
is true (not zero),PQgetCopyData
will not block waiting for input; it will return zero if theCOPY
is still in progress but no complete row is available. (In this case wait for read-ready and then callPQconsumeInput
before callingPQgetCopyData
again.) Whenasync
is false (zero),PQgetCopyData
will block until data is available or the operation completes.After
PQgetCopyData
returns -1, callPQgetResult
to obtain the final result status of theCOPY
command. One can wait for this result to be available in the usual way. Then return to normal operation.
35.10.3. Obsolete Functions for COPY
These functions represent older methods of handling COPY
. Although they still work, they are deprecated due to poor error handling, inconvenient methods of detecting end-of-data, and lack of support for binary or nonblocking transfers.
PQgetline
Reads a newline-terminated line of characters (transmitted by the server) into a buffer string of size
length
.int PQgetline(PGconn *conn, char *buffer, int length);
This function copies up to
length
-1 characters into the buffer and converts the terminating newline into a zero byte.PQgetline
returnsEOF
at the end of input, 0 if the entire line has been read, and 1 if the buffer is full but the terminating newline has not yet been read.Note that the application must check to see if a new line consists of the two characters
\.
, which indicates that the server has finished sending the results of theCOPY
command. If the application might receive lines that are more thanlength
-1 characters long, care is needed to be sure it recognizes the\.
line correctly (and does not, for example, mistake the end of a long data line for a terminator line).PQgetlineAsync
Reads a row of
COPY
data (transmitted by the server) into a buffer without blocking.int PQgetlineAsync(PGconn *conn, char *buffer, int bufsize);
This function is similar to
PQgetline
, but it can be used by applications that must readCOPY
data asynchronously, that is, without blocking. Having issued theCOPY
command and gotten aPGRES_COPY_OUT
response, the application should callPQconsumeInput
andPQgetlineAsync
until the end-of-data signal is detected.Unlike
PQgetline
, this function takes responsibility for detecting end-of-data.On each call,
PQgetlineAsync
will return data if a complete data row is available in libpq's input buffer. Otherwise, no data is returned until the rest of the row arrives. The function returns -1 if the end-of-copy-data marker has been recognized, or 0 if no data is available, or a positive number giving the number of bytes of data returned. If -1 is returned, the caller must next callPQendcopy
, and then return to normal processing.The data returned will not extend beyond a data-row boundary. If possible a whole row will be returned at one time. But if the buffer offered by the caller is too small to hold a row sent by the server, then a partial data row will be returned. With textual data this can be detected by testing whether the last returned byte is
\n
or not. (In a binaryCOPY
, actual parsing of theCOPY
data format will be needed to make the equivalent determination.) The returned string is not null-terminated. (If you want to add a terminating null, be sure to pass abufsize
one smaller than the room actually available.)PQputline
Sends a null-terminated string to the server. Returns 0 if OK and
EOF
if unable to send the string.int PQputline(PGconn *conn, const char *string);
The
COPY
data stream sent by a series of calls toPQputline
has the same format as that returned byPQgetlineAsync
, except that applications are not obliged to send exactly one data row perPQputline
call; it is okay to send a partial line or multiple lines per call.Note
Before Postgres Pro protocol 3.0, it was necessary for the application to explicitly send the two characters
\.
as a final line to indicate to the server that it had finished sendingCOPY
data. While this still works, it is deprecated and the special meaning of\.
can be expected to be removed in a future release. It is sufficient to callPQendcopy
after having sent the actual data.PQputnbytes
Sends a non-null-terminated string to the server. Returns 0 if OK and
EOF
if unable to send the string.int PQputnbytes(PGconn *conn, const char *buffer, int nbytes);
This is exactly like
PQputline
, except that the data buffer need not be null-terminated since the number of bytes to send is specified directly. Use this procedure when sending binary data.PQendcopy
Synchronizes with the server.
int PQendcopy(PGconn *conn);
This function waits until the server has finished the copying. It should either be issued when the last string has been sent to the server using
PQputline
or when the last string has been received from the server usingPQgetline
. It must be issued or the server will get “out of sync” with the client. Upon return from this function, the server is ready to receive the next SQL command. The return value is 0 on successful completion, nonzero otherwise. (UsePQerrorMessage
to retrieve details if the return value is nonzero.)When using
PQgetResult
, the application should respond to aPGRES_COPY_OUT
result by executingPQgetline
repeatedly, followed byPQendcopy
after the terminator line is seen. It should then return to thePQgetResult
loop untilPQgetResult
returns a null pointer. Similarly aPGRES_COPY_IN
result is processed by a series ofPQputline
calls followed byPQendcopy
, then return to thePQgetResult
loop. This arrangement will ensure that aCOPY
command embedded in a series of SQL commands will be executed correctly.Older applications are likely to submit a
COPY
viaPQexec
and assume that the transaction is done afterPQendcopy
. This will work correctly only if theCOPY
is the only SQL command in the command string.