The COPY command in Red Hat Database has options to read from or write to the network connection used by libpq. Functions are provided to access this network connection directly so that applications may take advantage of this capability. These functions should be executed only after obtaining a PGRES_COPY_OUT or PGRES_COPY_IN result from PQexec or PQgetResult.
PQgetline reads a newline-terminated line of characters (transmitted by the backend server) into a buffer string of size length.
int PQgetline(PGconn *conn, char *string, int length) |
Notice that the application must check to see if a line contains only the two characters "\.", which indicate that the backend 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 one recognizes the "\." line correctly (and does not, for example, mistake the end of a long data line for a terminator line). The code in src/bin/psql/copy.c contains example routines that correctly handle the copy protocol.
PQgetlineAsync reads a newline-terminated line of characters (transmitted by the backend server) into a buffer without blocking.
int PQgetlineAsync(PGconn *conn, char *buffer, int bufsize) |
The routine 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 newline character. If possible, a whole line will be returned at one time. However, if the caller's buffer is too small to hold a line sent by the backend, then a partial data line will be returned. This can be detected by testing whether the last returned byte is "\n". 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 actual buffer available.)
PQputline sends a NULL-terminated string to the backend server. Returns 0 if OK, EOF if unable to send the string.
int PQputline(PGconn *conn, const char *string); |
PQputnbytes sends a non-NULL-terminated string to the backend server. Returns 0 if OK, EOF if unable to send the string.
int PQputnbytes(PGconn *conn, const char *buffer, int nbytes); |
PQendcopy syncs with the backend. This function waits until the backend has finished the copy. It should either be issued when the last string has been sent to the backend using PQputline or when the last string has been received from the backend using PGgetline. It must be issued or the backend may get out of sync with the frontend. Upon return from this function, the backend is ready to receive the next query. The return value is 0 on successful completion, nonzero otherwise.
int PQendcopy(PGconn *conn); |
For example:
PQexec(conn, "create table foo (a int4, b char(16), d double precision)"); PQexec(conn, "copy foo from stdin"); PQputline(conn, "3\thello world\t4.5\n"); PQputline(conn,"4\tgoodbye world\t7.11\n"); ... PQputline(conn,"\\.\n"); PQendcopy(conn); |
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 NULL. 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 in/out command embedded in a series of SQL commands will be executed correctly.
Older applications used to submit a copy in or copy out via PQexec and assume that the transaction was done after PQendcopy. This will work correctly only if the copy in/out is the only SQL command in the query string.