PostgreSQL 8.4.22 Documentation | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 30. libpq - C Library | Fast Forward | Next |
The COPY command in PostgreSQL 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.)
Note: These additional data values are only available when using protocol 3.0. When using protocol 2.0, all these functions will return 0.
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 specified buffer, of
length nbytes, to the server.
The result is 1 if the data was sent, zero if it was not
sent because the attempt would block (this case is only
possible if the connection is in nonblocking mode), or -1
if an error occurred. (Use PQerrorMessage
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 the COPY 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 if errormsg is NULL. If errormsg is not NULL then the COPY is forced to fail, with the string pointed to by errormsg 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 the COPY for its own reasons. Also note that the option to force failure does not work when using pre-3.0-protocol connections.)
The result is 1 if the termination data was sent, zero
if it was not sent because the attempt would block (this
case is only possible if the connection is in nonblocking
mode), or -1 if an error occurred. (Use PQerrorMessage
to retrieve details if
the return value is -1. If the value is zero, wait for
write-ready and try again.)
After successfully calling PQputCopyEnd
, call PQgetResult
to obtain the final result
status of the COPY command. One
can wait for this result to be available in the usual
way. Then return to normal operation.
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. The buffer parameter
must be non-NULL. *buffer is set to point to the allocated
memory, or to NULL in cases where
no buffer is returned. A non-NULL
result buffer must be freed using PQfreemem
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 the COPY is still
in progress, but no row is yet available (this is only
possible when async is true).
A result of -1 indicates that the COPY is done. A result of -2 indicates
that an error occurred (consult PQerrorMessage
for the reason).
When async is true (not
zero), PQgetCopyData
will
not block waiting for input; it will return zero if the
COPY is still in progress but no
complete row is available. (In this case wait for
read-ready and then call PQconsumeInput
before calling
PQgetCopyData
again.) When
async is false (zero),
PQgetCopyData
will block
until data is available or the operation completes.
After PQgetCopyData
returns -1, call PQgetResult
to obtain the final result
status of the COPY command. One
can wait for this result to be available in the usual
way. Then return to normal operation.
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
returns
EOF 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 the COPY command. If the application might receive lines that are more than length-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 read COPY
data asynchronously, that is, without blocking. Having
issued the COPY command and
gotten a PGRES_COPY_OUT
response, the application should call PQconsumeInput
and PQgetlineAsync
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 call
PQendcopy
, 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 binary COPY, actual parsing of the COPY 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 a bufsize 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 to PQputline
has the same format as that
returned by PQgetlineAsync
,
except that applications are not obliged to send exactly
one data row per PQputline
call; it is okay to send a partial line or multiple lines
per call.
Note: Before PostgreSQL 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 sending COPY 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 call
PQendcopy
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 using PGgetline
. 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. (Use PQerrorMessage
to retrieve details if
the return value is nonzero.)
When using PQgetResult
,
the application should respond to a PGRES_COPY_OUT result by executing
PQgetline
repeatedly,
followed by PQendcopy
after
the terminator line is seen. It should then return to the
PQgetResult
loop until
PQgetResult
returns a null
pointer. Similarly a PGRES_COPY_IN result is processed by a
series of PQputline
calls
followed by PQendcopy
, then
return to the PQgetResult
loop. This arrangement will ensure that a COPY command embedded in a series of
SQL commands will be
executed correctly.
Older applications are likely to submit a COPY via PQexec
and assume that the transaction
is done after PQendcopy
.
This will work correctly only if the COPY is the only SQL command in the command
string.