34.10. Functions Associated with the COPY Command

34.10. Functions Associated with the `COPY` Command
Prev	Up	Chapter 34. libpq — C Library	Home	Next

34.10. Functions Associated with the `COPY` Command

34.10.1. Functions for Sending COPY Data
34.10.2. Functions for Receiving COPY Data
34.10.3. Obsolete Functions for COPY

+ 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.) +

34.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 specified + buffer, of length nbytes, 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. + (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.) +

+ 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 the PQputCopyEnd call + again.) If a hard error occurs, -1 is returned; you can use + PQerrorMessage to retrieve details. +

+ 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. +

34.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. 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 should 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. +

34.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 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 + PQgetline. 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. +

Prev	Up	Next
34.9. Asynchronous Notification	Home	34.11. Control Functions