153
Chapter 31. libpq - C Library
sufficient space to store the return value. (There is no check!) The actual result length in bytes will
be returned in the integer pointed to by
result_len
.If a 2- or 4-byte integer result is expected, set
result_is_int
to 1, otherwise set it to 0. Setting
result_is_int
to 1 causes libpq to byte-swap
the value if necessary, so that it is delivered as a proper
int
value for the client machine; note that a
4-byte integer is delivered into
*
result_buf
for either allowed result size. When
result_is_int
is 0, the binary-format byte string sent by the server is returned unmodified. (In this case it’s better to
consider
result_buf
as being of type
void
*
.)
PQfn
always returns avalid
PGresult
pointer. The resultstatus shouldbecheckedbefore the resultis
used. The caller is responsible for freeing the
PGresult
with
PQclear
when it is no longer needed.
Note that it is not possible to handle null arguments, null results, nor set-valued results when using
this interface.
31.8. Asynchronous Notification
PostgreSQL offers asynchronous notificationviathe
LISTEN
and
NOTIFY
commands. Aclient session
registers its interest in a particular notification channel with the
LISTEN
command (and can stop
listeningwith the
UNLISTEN
command). All sessions listeningon aparticular channelwill benotified
asynchronously when a
NOTIFY
command with that channel name is executed by any session. A
“payload” string can be passed to communicate additional data to the listeners.
libpq applications submit
LISTEN
,
UNLISTEN
,and
NOTIFY
commands as ordinary SQL commands.
The arrival of
NOTIFY
messages can subsequently be detected by calling
PQnotifies
.
The function
PQnotifies
returns the nextnotification from a listof unhandled notification messages
received from the server. It returns a null pointer if there are no pending notifications. Once a noti-
fication is returned from
PQnotifies
,it is considered handled and will be removed from the list of
notifications.
PGnotify
*
PQnotifies(PGconn
*
conn);
typedef struct pgNotify
{
char
*
relname;
/
*
notification channel name
*
/
int
be_pid;
/
*
process ID of notifying server process
*
/
char
*
extra;
/
*
notification payload string
*
/
} PGnotify;
After processing a
PGnotify
object returned by
PQnotifies
,be sure to free it with
PQfreemem
.It
is sufficient to free the
PGnotify
pointer; the
relname
and
extra
fields do not represent separate
allocations. (The names of these fields are historical; in particular, channel names need not have
anything to do with relation names.)
Example 31-2 gives a sample program that illustrates the use of asynchronous notification.
PQnotifies
does notactually read data from the server;it just returns messages previouslyabsorbed
by another libpq function. In prior releases of libpq, the only way to ensure timely receipt of
NOTIFY
messages was to constantly submit commands, even empty ones, and then check
PQnotifies
after
each
PQexec
.While this still works, it is deprecated as a waste of processing power.
Abetter way to check for
NOTIFY
messages when you have no useful commands to execute is to
call
PQconsumeInput
,then check
PQnotifies
.You can use
select()
to wait for data to arrive
from the server, thereby using no CPU power unless there is something to do. (See
PQsocket
to
obtain the file descriptor number to use with
select()
.) Note that this will work OK whether you
submit commands with
PQsendQuery
/
PQgetResult
or simply use
PQexec
.You should, however,
692
95
Chapter 31. libpq - C Library
remember to check
PQnotifies
after each
PQgetResult
or
PQexec
,to see if any notifications
came in during the processing of the command.
31.9. Functions Associated with the
COPY
Command
The
COPY
command inPostgreSQL has options to read from or write to the network connection used
bylibpq. Thefunctions describedinthis section allow applications to take advantage of this capability
by supplying or consuming copieddata.
The overall process is that the application first issues the SQL
COPY
command via
PQexec
or one of
theequivalent functions. Theresponse to this (if there is no error inthe command) willbea
PGresult
object bearing a status code of
PGRES_COPY_OUT
or
PGRES_COPY_IN
(depending on the specified
copy direction). Theapplicationshouldthenuse thefunctionsof this sectiontoreceiveor transmitdata
rows. Whenthe 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 con-
nection with query results:
PQnfields
Returns the number of columns (fields) to be copied.
PQbinaryTuples
0indicates 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 oper-
ation. 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 im-
plementation 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 pro-
tocol 2.0, all these functions will return 0.
693
111
Chapter 31. libpq - C Library
31.9.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 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 alreadyfailed 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.
31.9.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
694
127
Chapter 31. libpq - C Library
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 rowis successfullyreturned, 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
probablyonly useful for textual
COPY
.A resultof zero indicates that the
COPY
is still in progress,
but no rowis yetavailable (this is only possible when
async
is true). Aresult 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 finalresultstatusof the
COPY
command. One can wait for this result to be available in the usual way. Then return to normal
operation.
31.9.3. Obsolete Functions for
COPY
These functions represent older methods of handling
COPY
.Although they still work, they are depre-
cated due to poor error handling, inconvenient methods of detecting end-of-data, and lack of support
for binaryor 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 intoa zerobyte.
PQgetline
returns
EOF
atthe end of input, 0if 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 applicationmust check to see if a new line consists of the two characters
\.
,which
indicates that theserver 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.
695
103
Chapter 31. libpq - C Library
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 rowarrives. 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 OKand
EOF
if unable to sendthe string.
int PQputline(PGconn
*
conn,
const char
*
string);
The
COPY
data stream sent bya series of callsto
PQputline
has thesameformatas 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 protocol3.0, it was necessary for the application toexplicitly send
thetwo characters
\.
as afinalline to indicate to theserver that it hadfinishedsending
COPY
data. While this stillworks, it is deprecatedandthe special meaningof
\.
canbeexpectedto
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-terminatedsince 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
laststring has been sent to the server using
PQputline
or when the laststring 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
696
95
Chapter 31. libpq - C Library
shouldthenreturnto the
PQgetResult
loopuntil
PQgetResult
returns a nullpointer. Similarly
a
PGRES_COPY_IN
result is processed bya series of
PQputline
calls followed by
PQendcopy
,
then return to the
PQgetResult
loop. This arrangement will ensure that a
COPY
command em-
bedded 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.
31.10. Control Functions
These functions control miscellaneous details of libpq’s behavior.
PQclientEncoding
Returns the client encoding.
int PQclientEncoding(const PGconn
*conn
);
Note that it returns the encoding ID, not a symbolic string such as
EUC_JP
.If unsuccessful, it
returns -1. To convert an encoding ID to an encoding name, you canuse:
char
*
pg_encoding_to_char(int
encoding_id
);
PQsetClientEncoding
Sets the client encoding.
int PQsetClientEncoding(PGconn
*conn
, const char
*encoding
);
conn
is aconnectiontotheserver, and
encoding
is theencoding youwant to use. If the function
successfullysets the encoding, itreturns 0, otherwise-1. The current encodingfor this connection
can be determined by using
PQclientEncoding
.
PQsetErrorVerbosity
Determines the verbosity
of messages returned
by
PQerrorMessage
and
PQresultErrorMessage
.
typedef enum
{
PQERRORS_TERSE,
PQERRORS_DEFAULT,
PQERRORS_VERBOSE
} PGVerbosity;
PGVerbosity PQsetErrorVerbosity(PGconn
*
conn, PGVerbosity verbosity);
PQsetErrorVerbosity
sets the verbosity mode, returning the connection’s previous setting.
In TERSE mode, returned messages include severity, primary text, and position only; this will
normally fit on a single line. The default mode produces messages that include the above plus
any detail, hint, or context fields (these mightspan multiple lines). The VERBOSE mode includes
all available fields. Changing the verbosity does not affect the messages available from already-
existing
PGresult
objects, only subsequently-created ones.
PQtrace
Enables tracing of the client/server communication to a debugging file stream.
void PQtrace(PGconn
*
conn, FILE
*
stream);
697
Documents you may be interested
Documents you may be interested