4
<title><application>libpq</application> - C Library</title>
6
<indexterm zone="libpq">
7
<primary>libpq</primary>
10
<indexterm zone="libpq">
15
<application>libpq</application> is the <acronym>C</acronym>
16
application programmer's interface to <productname>PostgreSQL</>.
17
<application>libpq</> is a set of library functions that allow
18
client programs to pass queries to the <productname>PostgreSQL</>
19
backend server and to receive the results of these queries.
23
<application>libpq</> is also the underlying engine for several
24
other <productname>PostgreSQL</> application interfaces, including
25
those written for C++, Perl, Python, Tcl and <application>ECPG</>.
26
So some aspects of <application>libpq</>'s behavior will be
27
important to you if you use one of those packages. In particular,
28
<xref linkend="libpq-envars">,
29
<xref linkend="libpq-pgpass"> and
30
<xref linkend="libpq-ssl">
31
describe behavior that is visible to the user of any application
32
that uses <application>libpq</>.
36
Some short programs are included at the end of this chapter (<xref linkend="libpq-example">) to show how
37
to write programs that use <application>libpq</application>. There are also several
38
complete examples of <application>libpq</application> applications in the
39
directory <filename>src/test/examples</filename> in the source code distribution.
43
Client programs that use <application>libpq</application> must
44
include the header file
45
<filename>libpq-fe.h</filename><indexterm><primary>libpq-fe.h</></>
46
and must link with the <application>libpq</application> library.
49
<sect1 id="libpq-connect">
50
<title>Database Connection Control Functions</title>
53
The following functions deal with making a connection to a
54
<productname>PostgreSQL</productname> backend server. An
55
application program can have several backend connections open at
56
one time. (One reason to do that is to access more than one
57
database.) Each connection is represented by a
58
<structname>PGconn</><indexterm><primary>PGconn</></> object, which
59
is obtained from the function <function>PQconnectdb</> or
60
<function>PQsetdbLogin</>. Note that these functions will always
61
return a non-null object pointer, unless perhaps there is too
62
little memory even to allocate the <structname>PGconn</> object.
63
The <function>PQstatus</> function should be called to check
64
whether a connection was successfully made before queries are sent
65
via the connection object.
69
On Windows, there is a way to improve performance if a single
70
database connection is repeatedly started and shutdown. Internally,
71
libpq calls WSAStartup() and WSACleanup() for connection startup
72
and shutdown, respectively. WSAStartup() increments an internal
73
Windows library reference count which is decremented by WSACleanup().
74
When the reference count is just one, calling WSACleanup() frees
75
all resources and all DLLs are unloaded. This is an expensive
76
operation. To avoid this, an application can manually call
77
WSAStartup() so resources will not be freed when the last database
84
<term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
87
Makes a new connection to the database server.
90
PGconn *PQconnectdb(const char *conninfo);
95
This function opens a new database connection using the parameters taken
96
from the string <literal>conninfo</literal>. Unlike <function>PQsetdbLogin</> below,
97
the parameter set can be extended without changing the function signature,
98
so use of this function (or its nonblocking analogues <function>PQconnectStart</>
99
and <function>PQconnectPoll</function>) is preferred for new application programming.
104
can be empty to use all default parameters, or it can contain one or more
105
parameter settings separated by whitespace.
106
Each parameter setting is in the form <literal>keyword = value</literal>.
107
Spaces around the equal sign are optional.
108
To write an empty value or a value containing
109
spaces, surround it with single quotes, e.g.,
110
<literal>keyword = 'a value'</literal>.
111
Single quotes and backslashes within the value must be escaped with a
112
backslash, i.e., <literal>\'</literal> and <literal>\\</literal>.
116
The currently recognized parameter key words are:
119
<varlistentry id="libpq-connect-host" xreflabel="host">
120
<term><literal>host</literal></term>
123
Name of host to connect to.<indexterm><primary>host name</></>
124
If this begins with a slash, it specifies Unix-domain
125
communication rather than TCP/IP communication; the value is the
126
name of the directory in which the socket file is stored. The
127
default behavior when <literal>host</literal> is not specified
128
is to connect to a Unix-domain
129
socket<indexterm><primary>Unix domain socket</></> in
130
<filename>/tmp</filename> (or whatever socket directory was specified
131
when <productname>PostgreSQL</> was built). On machines without
132
Unix-domain sockets, the default is to connect to <literal>localhost</>.
137
<varlistentry id="libpq-connect-hostaddr" xreflabel="hostaddr">
138
<term><literal>hostaddr</literal></term>
141
Numeric IP address of host to connect to. This should be in the
142
standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
143
your machine supports IPv6, you can also use those addresses.
144
TCP/IP communication is
145
always used when a nonempty string is specified for this parameter.
149
Using <literal>hostaddr</> instead of <literal>host</> allows the
150
application to avoid a host name look-up, which might be important in
151
applications with time constraints. However, Kerberos and GSSAPI authentication
152
requires the host name. The following therefore applies: If
153
<literal>host</> is specified without <literal>hostaddr</>, a host name
154
lookup occurs. If <literal>hostaddr</> is specified without
155
<literal>host</>, the value for <literal>hostaddr</> gives the remote
156
address. When Kerberos is used, a reverse name query occurs to obtain
157
the host name for Kerberos. If both
158
<literal>host</> and <literal>hostaddr</> are specified, the value for
159
<literal>hostaddr</> gives the remote address; the value for
160
<literal>host</> is ignored, unless Kerberos is used, in which case that
161
value is used for Kerberos authentication. (Note that authentication is
162
likely to fail if <application>libpq</application> is passed a host name
163
that is not the name of the machine at <literal>hostaddr</>.) Also,
164
<literal>host</> rather than <literal>hostaddr</> is used to identify
165
the connection in <filename>~/.pgpass</> (see
166
<xref linkend="libpq-pgpass">).
170
Without either a host name or host address,
171
<application>libpq</application> will connect using a
172
local Unix-domain socket; or on machines without Unix-domain
173
sockets, it will attempt to connect to <literal>localhost</>.
178
<varlistentry id="libpq-connect-port" xreflabel="port">
179
<term><literal>port</literal></term>
182
Port number to connect to at the server host, or socket file
183
name extension for Unix-domain
184
connections.<indexterm><primary>port</></>
189
<varlistentry id="libpq-connect-dbname" xreflabel="dbname">
190
<term><literal>dbname</literal></term>
193
The database name. Defaults to be the same as the user name.
198
<varlistentry id="libpq-connect-user" xreflabel="user">
199
<term><literal>user</literal></term>
202
<productname>PostgreSQL</productname> user name to connect as.
203
Defaults to be the same as the operating system name of the user
204
running the application.
209
<varlistentry id="libpq-connect-password" xreflabel="password">
210
<term><literal>password</literal></term>
213
Password to be used if the server demands password authentication.
218
<varlistentry id="libpq-connect-connect-timeout" xreflabel="connect_timeout">
219
<term><literal>connect_timeout</literal></term>
222
Maximum wait for connection, in seconds (write as a decimal integer
223
string). Zero or not specified means wait indefinitely. It is not
224
recommended to use a timeout of less than 2 seconds.
229
<varlistentry id="libpq-connect-options" xreflabel="options">
230
<term><literal>options</literal></term>
233
Adds command-line options to send to the server at run-time.
234
For example, setting this to <literal>-c geqo=off</> sets the
235
session's value of the <varname>geqo</> parameter to
236
<literal>off</>. For a detailed discussion of the available
237
options, consult <xref linkend="runtime-config">.
242
<varlistentry id="libpq-connect-tty" xreflabel="tty">
243
<term><literal>tty</literal></term>
246
Ignored (formerly, this specified where to send server debug output).
251
<varlistentry id="libpq-connect-sslmode" xreflabel="sslmode">
252
<term><literal>sslmode</literal></term>
255
This option determines whether or with what priority a
256
<acronym>SSL</> TCP/IP connection will be negotiated with the
257
server. There are four modes: <literal>disable</> will attempt
258
only an unencrypted <acronym>SSL</> connection;
259
<literal>allow</> will negotiate, trying first a
260
non-<acronym>SSL</> connection, then if that fails, trying an
261
<acronym>SSL</> connection; <literal>prefer</> (the default)
262
will negotiate, trying first an <acronym>SSL</> connection,
263
then if that fails, trying a regular non-<acronym>SSL</>
264
connection; <literal>require</> will try only an
265
<acronym>SSL</> connection. <literal>sslmode</> is ignored
266
for Unix domain socket communication.
270
If <productname>PostgreSQL</> is compiled without SSL support,
271
using option <literal>require</> will cause an error, while
272
options <literal>allow</> and <literal>prefer</> will be
273
accepted but <application>libpq</> will not in fact attempt
275
connection.<indexterm><primary>SSL</><secondary
276
sortas="libpq">with libpq</></indexterm>
281
<varlistentry id="libpq-connect-sslverify" xreflabel="sslverify">
282
<term><literal>sslverify</literal></term>
285
This option controls how libpq verifies the certificate on the
286
server when performing an <acronym>SSL</> connection. There are
287
three options: <literal>none</> disables verification completely
288
(not recommended); <literal>cert</> enables verification that
289
the server certificate chains to a known certificate
290
authority (CA); <literal>cn</> will both verify that the
291
certificate chains to a known CA and that the <literal>cn</>
292
attribute of the server certificate matches the server's
297
It is always recommended to use the <literal>cn</> value for
298
this parameter, since this is the only option that prevents
299
man-in-the-middle attacks. Note that this requires the server
300
name on the certificate to match exactly with the host name
301
used for the connection, and therefore does not support connections
302
to aliased names. It can be used with pure IP address connections
303
only if the certificate also has just the IP address in the
304
<literal>cn</> field.
308
If the <literal>cn</> attribute in the certificate sent by the
309
server starts with an asterisk (<literal>*</>), it will be treated
310
as a wildcard. This wildcard can only be present at the start of
311
the value, and will match all characters <emphasis>except</> a
312
dot (<literal>.</>). This means the certificate will not match
318
<varlistentry id="libpq-connect-requiressl" xreflabel="requiressl">
319
<term><literal>requiressl</literal></term>
322
This option is deprecated in favor of the <literal>sslmode</>
327
If set to 1, an <acronym>SSL</acronym> connection to the server
328
is required (this is equivalent to <literal>sslmode</>
329
<literal>require</>). <application>libpq</> will then refuse
330
to connect if the server does not accept an
331
<acronym>SSL</acronym> connection. If set to 0 (default),
332
<application>libpq</> will negotiate the connection type with
333
the server (equivalent to <literal>sslmode</>
334
<literal>prefer</>). This option is only available if
335
<productname>PostgreSQL</> is compiled with SSL support.
340
<varlistentry id="libpq-connect-sslcert" xreflabel="sslcert">
341
<term><literal>sslcert</literal></term>
344
This parameter specifies the file name of the client SSL
350
<varlistentry id="libpq-connect-sslkey" xreflabel="sslkey">
351
<term><literal>sslkey</literal></term>
354
This parameter specifies the location for the secret key
355
used for the client certificate. It can either specify a filename
356
that will be used instead of the default
357
<filename>~/.postgresql/postgresql.key</>, or can specify an external
358
engine (engines are <productname>OpenSSL</> loadable modules). The
359
external engine specification should consist of a colon-separated
360
engine name and an engine-specific key identifier.
365
<varlistentry id="libpq-connect-sslrootcert" xreflabel="sslrootcert">
366
<term><literal>sslrootcert</literal></term>
369
This parameter specifies the file name of the root SSL certificate.
374
<varlistentry id="libpq-connect-sslcrl" xreflabel="sslcrl">
375
<term><literal>sslcrl</literal></term>
378
This parameter specifies the file name of the SSL certificate
379
revocation list (CRL).
384
<varlistentry id="libpq-connect-krbsrvname" xreflabel="krbsrvname">
385
<term><literal>krbsrvname</literal></term>
388
Kerberos service name to use when authenticating with Kerberos 5
390
This must match the service name specified in the server
391
configuration for Kerberos authentication to succeed. (See also
392
<xref linkend="kerberos-auth"> and <xref linkend="gssapi-auth">.)
397
<varlistentry id="libpq-connect-gsslib" xreflabel="gsslib">
398
<term><literal>gsslib</literal></term>
401
GSS library to use for GSSAPI authentication. Only used on Windows.
402
Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
403
library for authentication instead of the default SSPI.
408
<varlistentry id="libpq-connect-service" xreflabel="service">
409
<term><literal>service</literal></term>
412
Service name to use for additional parameters. It specifies a service
413
name in <filename>pg_service.conf</filename> that holds additional connection parameters.
414
This allows applications to specify only a service name so connection parameters
415
can be centrally maintained. See <xref linkend="libpq-pgservice">.
421
If any parameter is unspecified, then the corresponding
422
environment variable (see <xref linkend="libpq-envars">)
423
is checked. If the environment variable is not set either,
424
then the indicated built-in defaults are used.
430
<term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
433
Makes a new connection to the database server.
435
PGconn *PQsetdbLogin(const char *pghost,
437
const char *pgoptions,
446
This is the predecessor of <function>PQconnectdb</function> with a fixed
447
set of parameters. It has the same functionality except that the
448
missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
449
empty string for any one of the fixed parameters that is to be defaulted.
453
If the <parameter>dbName</parameter> contains an <symbol>=</symbol> sign, it
454
is taken as a <parameter>conninfo</parameter> string in exactly the same way as
455
if it had been passed to <function>PQconnectdb</function>, and the remaining
456
parameters are then applied as above.
462
<term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
465
Makes a new connection to the database server.
467
PGconn *PQsetdb(char *pghost,
476
This is a macro that calls <function>PQsetdbLogin</function> with null pointers
477
for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
478
for backward compatibility with very old programs.
484
<term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
485
<term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
488
<indexterm><primary>nonblocking connection</primary></indexterm>
489
Make a connection to the database server in a nonblocking manner.
492
PGconn *PQconnectStart(const char *conninfo);
496
PostgresPollingStatusType PQconnectPoll(PGconn *conn);
501
These two functions are used to open a connection to a database server such
502
that your application's thread of execution is not blocked on remote I/O
504
The point of this approach is that the waits for I/O to complete can occur
505
in the application's main loop, rather than down inside
506
<function>PQconnectdb</>, and so the application can manage this
507
operation in parallel with other activities.
511
The database connection is made using the parameters taken from the string
512
<literal>conninfo</literal>, passed to <function>PQconnectStart</function>. This string is in
513
the same format as described above for <function>PQconnectdb</function>.
516
Neither <function>PQconnectStart</function> nor <function>PQconnectPoll</function> will block, so long as a number of
517
restrictions are met:
521
The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
522
name and reverse name queries are not made. See the documentation of
523
these parameters under <function>PQconnectdb</function> above for details.
529
If you call <function>PQtrace</function>, ensure that the stream object
530
into which you trace will not block.
536
You ensure that the socket is in the appropriate state
537
before calling <function>PQconnectPoll</function>, as described below.
544
To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
545
If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
546
structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
547
representing a valid connection to the database). On return from
548
<function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
549
<symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
553
If <function>PQconnectStart</> succeeds, the next stage is to poll
554
<application>libpq</> so that it can proceed with the connection sequence.
555
Use <function>PQsocket(conn)</function> to obtain the descriptor of the
556
socket underlying the database connection.
557
Loop thus: If <function>PQconnectPoll(conn)</function> last returned
558
<symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
559
read (as indicated by <function>select()</>, <function>poll()</>, or
560
similar system function).
561
Then call <function>PQconnectPoll(conn)</function> again.
562
Conversely, if <function>PQconnectPoll(conn)</function> last returned
563
<symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
564
to write, then call <function>PQconnectPoll(conn)</function> again.
565
If you have yet to call
566
<function>PQconnectPoll</function>, i.e., just after the call to
567
<function>PQconnectStart</function>, behave as if it last returned
568
<symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
569
<function>PQconnectPoll(conn)</function> returns
570
<symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
571
has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
572
has been successfully made.
576
At any time during connection, the status of the connection can be
577
checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
578
connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
579
connection is ready. Both of these states are equally detectable
580
from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
581
during (and only during) an asynchronous connection procedure. These
582
indicate the current stage of the connection procedure and might be useful
583
to provide feedback to the user for example. These statuses are:
587
<term><symbol>CONNECTION_STARTED</symbol></term>
590
Waiting for connection to be made.
596
<term><symbol>CONNECTION_MADE</symbol></term>
599
Connection OK; waiting to send.
605
<term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
608
Waiting for a response from the server.
614
<term><symbol>CONNECTION_AUTH_OK</symbol></term>
617
Received authentication; waiting for backend start-up to finish.
623
<term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
626
Negotiating SSL encryption.
632
<term><symbol>CONNECTION_SETENV</symbol></term>
635
Negotiating environment-driven parameter settings.
641
Note that, although these constants will remain (in order to maintain
642
compatibility), an application should never rely upon these occurring in a
643
particular order, or at all, or on the status always being one of these
644
documented values. An application might do something like this:
646
switch(PQstatus(conn))
648
case CONNECTION_STARTED:
649
feedback = "Connecting...";
652
case CONNECTION_MADE:
653
feedback = "Connected to server...";
659
feedback = "Connecting...";
665
The <literal>connect_timeout</literal> connection parameter is ignored
666
when using <function>PQconnectPoll</function>; it is the application's
667
responsibility to decide whether an excessive amount of time has elapsed.
668
Otherwise, <function>PQconnectStart</function> followed by a
669
<function>PQconnectPoll</function> loop is equivalent to
670
<function>PQconnectdb</function>.
674
Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
675
<function>PQfinish</function> when you are finished with it, in order to dispose of
676
the structure and any associated memory blocks. This must be done even if
677
the connection attempt fails or is abandoned.
683
<term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
686
Returns the default connection options.
688
PQconninfoOption *PQconndefaults(void);
692
char *keyword; /* The keyword of the option */
693
char *envvar; /* Fallback environment variable name */
694
char *compiled; /* Fallback compiled in default value */
695
char *val; /* Option's current value, or NULL */
696
char *label; /* Label for field in connect dialog */
697
char *dispchar; /* Indicates how to display this field
698
in a connect dialog. Values are:
699
"" Display entered value as is
700
"*" Password field - hide value
701
"D" Debug option - don't show by default */
702
int dispsize; /* Field size in characters for dialog */
708
Returns a connection options array. This can be used to determine
709
all possible <function>PQconnectdb</function> options and their
710
current default values. The return value points to an array of
711
<structname>PQconninfoOption</structname> structures, which ends
712
with an entry having a null <structfield>keyword</> pointer. The
713
null pointer is returned if memory could not be allocated. Note that
714
the current default values (<structfield>val</structfield> fields)
715
will depend on environment variables and other context. Callers
716
must treat the connection options data as read-only.
720
After processing the options array, free it by passing it to
721
<function>PQconninfoFree</function>. If this is not done, a small amount of memory
722
is leaked for each call to <function>PQconndefaults</function>.
729
<term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</></></term>
732
Returns parsed connection options from the provided connection string.
735
PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
740
Parses a connection string and returns the resulting options as an
741
array; or returns NULL if there is a problem with the connection
742
string. This can be used to determine
743
the <function>PQconnectdb</function> options in the provided
744
connection string. The return value points to an array of
745
<structname>PQconninfoOption</structname> structures, which ends
746
with an entry having a null <structfield>keyword</> pointer.
750
Note that only options explicitly specified in the string will have
751
values set in the result array; no defaults are inserted.
755
If <literal>errmsg</> is not NULL, then <literal>*errmsg</> is set
756
to NULL on success, else to a malloc'd error string explaining
757
the problem. (It is also possible for <literal>*errmsg</> to be
758
set to NULL even when NULL is returned; this indicates an out-of-memory
763
After processing the options array, free it by passing it to
764
<function>PQconninfoFree</function>. If this is not done, some memory
765
is leaked for each call to <function>PQconninfoParse</function>.
766
Conversely, if an error occurs and <literal>errmsg</> is not NULL,
767
be sure to free the error string using <function>PQfreemem</>.
774
<term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
777
Closes the connection to the server. Also frees
778
memory used by the <structname>PGconn</structname> object.
780
void PQfinish(PGconn *conn);
785
Note that even if the server connection attempt fails (as
786
indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
787
to free the memory used by the <structname>PGconn</structname> object.
788
The <structname>PGconn</> pointer must not be used again after
789
<function>PQfinish</function> has been called.
795
<term><function>PQreset</function><indexterm><primary>PQreset</></></term>
798
Resets the communication channel to the server.
800
void PQreset(PGconn *conn);
805
This function will close the connection
806
to the server and attempt to reestablish a new
807
connection to the same server, using all the same
808
parameters previously used. This might be useful for
809
error recovery if a working connection is lost.
815
<term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
816
<term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
819
Reset the communication channel to the server, in a nonblocking manner.
822
int PQresetStart(PGconn *conn);
825
PostgresPollingStatusType PQresetPoll(PGconn *conn);
830
These functions will close the connection to the server and attempt to
831
reestablish a new connection to the same server, using all the same
832
parameters previously used. This can be useful for error recovery if a
833
working connection is lost. They differ from <function>PQreset</function> (above) in that they
834
act in a nonblocking manner. These functions suffer from the same
835
restrictions as <function>PQconnectStart</> and <function>PQconnectPoll</>.
839
To initiate a connection reset, call
840
<function>PQresetStart</function>. If it returns 0, the reset has
841
failed. If it returns 1, poll the reset using
842
<function>PQresetPoll</function> in exactly the same way as you
843
would create the connection using <function>PQconnectPoll</function>.
852
<sect1 id="libpq-status">
853
<title>Connection Status Functions</title>
856
These functions can be used to interrogate the status
857
of an existing database connection object.
862
<indexterm><primary>libpq-fe.h</></>
863
<indexterm><primary>libpq-int.h</></>
864
<application>libpq</application> application programmers should be careful to
865
maintain the <structname>PGconn</structname> abstraction. Use the accessor
866
functions described below to get at the contents of <structname>PGconn</structname>.
867
Reference to internal <structname>PGconn</structname> fields using
868
<filename>libpq-int.h</> is not recommended because they are subject to change
874
The following functions return parameter values established at connection.
875
These values are fixed for the life of the <structname>PGconn</> object.
880
<function>PQdb</function>
882
<primary>PQdb</primary>
888
Returns the database name of the connection.
890
char *PQdb(const PGconn *conn);
898
<function>PQuser</function>
900
<primary>PQuser</primary>
906
Returns the user name of the connection.
908
char *PQuser(const PGconn *conn);
916
<function>PQpass</function>
918
<primary>PQpass</primary>
924
Returns the password of the connection.
926
char *PQpass(const PGconn *conn);
934
<function>PQhost</function>
936
<primary>PQhost</primary>
942
Returns the server host name of the connection.
944
char *PQhost(const PGconn *conn);
952
<function>PQport</function>
954
<primary>PQport</primary>
960
Returns the port of the connection.
963
char *PQport(const PGconn *conn);
971
<function>PQtty</function>
973
<primary>PQtty</primary>
979
Returns the debug <acronym>TTY</acronym> of the connection.
980
(This is obsolete, since the server no longer pays attention
981
to the <acronym>TTY</acronym> setting, but the function remains
982
for backwards compatibility.)
985
char *PQtty(const PGconn *conn);
993
<function>PQoptions</function>
995
<primary>PQoptions</primary>
1001
Returns the command-line options passed in the connection request.
1003
char *PQoptions(const PGconn *conn);
1012
The following functions return status data that can change as operations
1013
are executed on the <structname>PGconn</> object.
1018
<function>PQstatus</function>
1020
<primary>PQstatus</primary>
1026
Returns the status of the connection.
1028
ConnStatusType PQstatus(const PGconn *conn);
1033
The status can be one of a number of values. However, only two of
1034
these are seen outside of an asynchronous connection procedure:
1035
<literal>CONNECTION_OK</literal> and
1036
<literal>CONNECTION_BAD</literal>. A good connection to the database
1037
has the status <literal>CONNECTION_OK</literal>. A failed
1038
connection attempt is signaled by status
1039
<literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
1040
remain so until <function>PQfinish</function>, but a communications
1041
failure might result in the status changing to
1042
<literal>CONNECTION_BAD</literal> prematurely. In that case the
1043
application could try to recover by calling
1044
<function>PQreset</function>.
1048
See the entry for <function>PQconnectStart</> and <function>PQconnectPoll</> with regards
1049
to other status codes
1057
<function>PQtransactionStatus</function>
1059
<primary>PQtransactionStatus</primary>
1065
Returns the current in-transaction status of the server.
1068
PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
1071
The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
1072
<literal>PQTRANS_ACTIVE</literal> (a command is in progress),
1073
<literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
1074
or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
1075
<literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
1076
<literal>PQTRANS_ACTIVE</literal> is reported only when a query
1077
has been sent to the server and not yet completed.
1082
<function>PQtransactionStatus</> will give incorrect results when using
1083
a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
1084
set to off. The server-side autocommit feature has been
1085
deprecated and does not exist in later server versions.
1093
<function>PQparameterStatus</function>
1095
<primary>PQparameterStatus</primary>
1101
Looks up a current parameter setting of the server.
1104
const char *PQparameterStatus(const PGconn *conn, const char *paramName);
1107
Certain parameter values are reported by the server automatically at
1108
connection startup or whenever their values change.
1109
<function>PQparameterStatus</> can be used to interrogate these settings.
1110
It returns the current value of a parameter if known, or <symbol>NULL</symbol>
1111
if the parameter is not known.
1115
Parameters reported as of the current release include
1116
<literal>server_version</>,
1117
<literal>server_encoding</>,
1118
<literal>client_encoding</>,
1119
<literal>is_superuser</>,
1120
<literal>session_authorization</>,
1121
<literal>DateStyle</>,
1122
<literal>IntervalStyle</>,
1123
<literal>TimeZone</>,
1124
<literal>integer_datetimes</>, and
1125
<literal>standard_conforming_strings</>.
1126
(<literal>server_encoding</>, <literal>TimeZone</>, and
1127
<literal>integer_datetimes</> were not reported by releases before 8.0;
1128
<literal>standard_conforming_strings</> was not reported by releases
1129
before 8.1; <literal>IntervalStyle</> was not reported by releases
1132
<literal>server_version</>,
1133
<literal>server_encoding</> and
1134
<literal>integer_datetimes</>
1135
cannot change after startup.
1139
Pre-3.0-protocol servers do not report parameter settings, but
1140
<application>libpq</> includes logic to obtain values for
1141
<literal>server_version</> and <literal>client_encoding</> anyway.
1142
Applications are encouraged to use <function>PQparameterStatus</>
1143
rather than <foreignphrase>ad hoc</> code to determine these values.
1144
(Beware however that on a pre-3.0 connection, changing
1145
<literal>client_encoding</> via <command>SET</> after connection
1146
startup will not be reflected by <function>PQparameterStatus</>.)
1147
For <literal>server_version</>, see also
1148
<function>PQserverVersion</>, which returns the information in a
1149
numeric form that is much easier to compare against.
1153
If no value for <literal>standard_conforming_strings</> is reported,
1154
applications can assume it is <literal>off</>, that is, backslashes
1155
are treated as escapes in string literals. Also, the presence of
1156
this parameter can be taken as an indication that the escape string
1157
syntax (<literal>E'...'</>) is accepted.
1161
Although the returned pointer is declared <literal>const</>, it in fact
1162
points to mutable storage associated with the <literal>PGconn</> structure.
1163
It is unwise to assume the pointer will remain valid across queries.
1170
<function>PQprotocolVersion</function>
1172
<primary>PQprotocolVersion</primary>
1178
Interrogates the frontend/backend protocol being used.
1180
int PQprotocolVersion(const PGconn *conn);
1182
Applications might wish to use this to determine whether certain
1183
features are supported. Currently, the possible values are 2 (2.0
1184
protocol), 3 (3.0 protocol), or zero (connection bad). This will
1185
not change after connection startup is complete, but it could
1186
theoretically change during a connection reset. The 3.0 protocol
1187
will normally be used when communicating with
1188
<productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
1189
support only protocol 2.0. (Protocol 1.0 is obsolete and not
1190
supported by <application>libpq</application>.)
1197
<function>PQserverVersion</function>
1199
<primary>PQserverVersion</primary>
1205
Returns an integer representing the backend version.
1207
int PQserverVersion(const PGconn *conn);
1209
Applications might use this to determine the version of the database
1210
server they are connected to. The number is formed by converting
1211
the major, minor, and revision numbers into two-decimal-digit
1212
numbers and appending them together. For example, version 8.1.5
1213
will be returned as 80105, and version 8.2 will be returned as
1214
80200 (leading zeroes are not shown). Zero is returned if the
1222
<function>PQerrorMessage</function>
1224
<primary>PQerrorMessage</primary>
1230
<indexterm><primary>error message</></> Returns the error message
1231
most recently generated by an operation on the connection.
1234
char *PQerrorMessage(const PGconn *conn);
1240
Nearly all <application>libpq</> functions will set a message for
1241
<function>PQerrorMessage</function> if they fail. Note that by
1242
<application>libpq</application> convention, a nonempty
1243
<function>PQerrorMessage</function> result can be multiple lines,
1244
and will include a trailing newline. The caller should not free
1245
the result directly. It will be freed when the associated
1246
<structname>PGconn</> handle is passed to
1247
<function>PQfinish</function>. The result string should not be
1248
expected to remain the same across operations on the
1249
<literal>PGconn</> structure.
1255
<term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1258
Obtains the file descriptor number of the connection socket to
1259
the server. A valid descriptor will be greater than or equal
1260
to 0; a result of -1 indicates that no server connection is
1261
currently open. (This will not change during normal operation,
1262
but could change during connection setup or reset.)
1265
int PQsocket(const PGconn *conn);
1273
<term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1276
Returns the process <acronym>ID</acronym>
1277
(PID)<indexterm><primary>PID</><secondary>determining PID of
1278
server process</><tertiary>in libpq</></> of the backend server
1279
process handling this connection.
1282
int PQbackendPID(const PGconn *conn);
1287
The backend <acronym>PID</acronym> is useful for debugging
1288
purposes and for comparison to <command>NOTIFY</command>
1289
messages (which include the <acronym>PID</acronym> of the
1290
notifying backend process). Note that the
1291
<acronym>PID</acronym> belongs to a process executing on the
1292
database server host, not the local host!
1298
<term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
1301
Returns true (1) if the connection authentication method
1302
required a password, but none was available.
1303
Returns false (0) if not.
1306
int PQconnectionNeedsPassword(const PGconn *conn);
1311
This function can be applied after a failed connection attempt
1312
to decide whether to prompt the user for a password.
1318
<term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
1321
Returns true (1) if the connection authentication method
1322
used a password. Returns false (0) if not.
1325
int PQconnectionUsedPassword(const PGconn *conn);
1330
This function can be applied after either a failed or successful
1331
connection attempt to detect whether the server demanded a password.
1337
<term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1340
<indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1341
Returns the SSL structure used in the connection, or null
1342
if SSL is not in use.
1345
SSL *PQgetssl(const PGconn *conn);
1350
This structure can be used to verify encryption levels, check server
1351
certificates, and more. Refer to the <productname>OpenSSL</>
1352
documentation for information about this structure.
1356
You must define <symbol>USE_SSL</symbol> in order to get the
1357
correct prototype for this function. Doing this will also
1358
automatically include <filename>ssl.h</filename> from <productname>OpenSSL</productname>.
1368
<sect1 id="libpq-exec">
1369
<title>Command Execution Functions</title>
1372
Once a connection to a database server has been successfully
1373
established, the functions described here are used to perform
1374
SQL queries and commands.
1377
<sect2 id="libpq-exec-main">
1378
<title>Main Functions</title>
1384
<function>PQexec</function>
1386
<primary>PQexec</primary>
1392
Submits a command to the server and waits for the result.
1395
PGresult *PQexec(PGconn *conn, const char *command);
1400
Returns a <structname>PGresult</structname> pointer or possibly a null
1401
pointer. A non-null pointer will generally be returned except in
1402
out-of-memory conditions or serious errors such as inability to send
1403
the command to the server. If a null pointer is returned, it should
1404
be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
1405
<function>PQerrorMessage</function> to get more information about such
1412
It is allowed to include multiple SQL commands (separated by semicolons)
1413
in the command string. Multiple queries sent in a single
1414
<function>PQexec</> call are processed in a single transaction, unless
1415
there are explicit <command>BEGIN</command>/<command>COMMIT</command>
1416
commands included in the query string to divide it into multiple
1417
transactions. Note however that the returned
1418
<structname>PGresult</structname> structure describes only the result
1419
of the last command executed from the string. Should one of the
1420
commands fail, processing of the string stops with it and the returned
1421
<structname>PGresult</structname> describes the error condition.
1428
<function>PQexecParams</function>
1430
<primary>PQexecParams</primary>
1436
Submits a command to the server and waits for the result,
1437
with the ability to pass parameters separately from the SQL
1441
PGresult *PQexecParams(PGconn *conn,
1442
const char *command,
1444
const Oid *paramTypes,
1445
const char * const *paramValues,
1446
const int *paramLengths,
1447
const int *paramFormats,
1453
<function>PQexecParams</> is like <function>PQexec</>, but offers additional
1454
functionality: parameter values can be specified separately from the command
1455
string proper, and query results can be requested in either text or binary
1456
format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1457
connections; it will fail when using protocol 2.0.
1461
The function arguments are:
1465
<term><parameter>conn</parameter></term>
1469
The connection object to send the command through.
1475
<term><parameter>command</parameter></term>
1478
The SQL command string to be executed. If parameters are used,
1479
they are referred to in the command string as <literal>$1</>,
1480
<literal>$2</>, etc.
1486
<term><parameter>nParams</parameter></term>
1489
The number of parameters supplied; it is the length of the arrays
1490
<parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1491
<parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1492
array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
1499
<term><parameter>paramTypes[]</parameter></term>
1502
Specifies, by OID, the data types to be assigned to the
1503
parameter symbols. If <parameter>paramTypes</> is
1504
<symbol>NULL</symbol>, or any particular element in the array
1505
is zero, the server infers a data type for the parameter symbol
1506
in the same way it would do for an untyped literal string.
1512
<term><parameter>paramValues[]</parameter></term>
1515
Specifies the actual values of the parameters. A null pointer
1516
in this array means the corresponding parameter is null;
1517
otherwise the pointer points to a zero-terminated text string
1518
(for text format) or binary data in the format expected by the
1519
server (for binary format).
1525
<term><parameter>paramLengths[]</parameter></term>
1528
Specifies the actual data lengths of binary-format parameters.
1529
It is ignored for null parameters and text-format parameters.
1530
The array pointer can be null when there are no binary parameters.
1536
<term><parameter>paramFormats[]</parameter></term>
1539
Specifies whether parameters are text (put a zero in the
1540
array entry for the corresponding parameter) or binary (put
1541
a one in the array entry for the corresponding parameter).
1542
If the array pointer is null then all parameters are presumed
1546
Values passed in binary format require knowlege of
1547
the internal representation expected by the backend.
1548
For example, integers must be passed in network byte
1549
order. Passing <type>numeric</> values requires
1550
knowledge of the server storage format, as implemented
1552
<filename>src/backend/utils/adt/numeric.c::numeric_send()</> and
1553
<filename>src/backend/utils/adt/numeric.c::numeric_recv()</>.
1559
<term><parameter>resultFormat</parameter></term>
1562
Specify zero to obtain results in text format, or one to obtain
1563
results in binary format. (There is not currently a provision
1564
to obtain different result columns in different formats,
1565
although that is possible in the underlying protocol.)
1577
The primary advantage of <function>PQexecParams</> over
1578
<function>PQexec</> is that parameter values can be separated from the
1579
command string, thus avoiding the need for tedious and error-prone
1580
quoting and escaping.
1584
Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
1585
one SQL command in the given string. (There can be semicolons in it,
1586
but not more than one nonempty command.) This is a limitation of the
1587
underlying protocol, but has some usefulness as an extra defense against
1588
SQL-injection attacks.
1593
Specifying parameter types via OIDs is tedious, particularly if you prefer
1594
not to hard-wire particular OID values into your program. However, you can
1595
avoid doing so even in cases where the server by itself cannot determine the
1596
type of the parameter, or chooses a different type than you want. In the
1597
SQL command text, attach an explicit cast to the parameter symbol to show what
1598
data type you will send. For example:
1600
SELECT * FROM mytable WHERE x = $1::bigint;
1602
This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
1603
by default it would be assigned the same type as <literal>x</>. Forcing the
1604
parameter type decision, either this way or by specifying a numeric type OID,
1605
is strongly recommended when sending parameter values in binary format, because
1606
binary format has less redundancy than text format and so there is less chance
1607
that the server will detect a type mismatch mistake for you.
1614
<term><function>PQprepare</function>
1616
<primary>PQprepare</primary>
1622
Submits a request to create a prepared statement with the
1623
given parameters, and waits for completion.
1625
PGresult *PQprepare(PGconn *conn,
1626
const char *stmtName,
1629
const Oid *paramTypes);
1634
<function>PQprepare</> creates a prepared statement for later
1635
execution with <function>PQexecPrepared</>. This feature allows
1636
commands that will be used repeatedly to be parsed and planned just
1637
once, rather than each time they are executed.
1638
<function>PQprepare</> is supported only in protocol 3.0 and later
1639
connections; it will fail when using protocol 2.0.
1643
The function creates a prepared statement named
1644
<parameter>stmtName</> from the <parameter>query</> string, which
1645
must contain a single SQL command. <parameter>stmtName</> can be
1646
<literal>""</> to create an unnamed statement, in which case any
1647
pre-existing unnamed statement is automatically replaced; otherwise
1648
it is an error if the statement name is already defined in the
1649
current session. If any parameters are used, they are referred
1650
to in the query as <literal>$1</>, <literal>$2</>, etc.
1651
<parameter>nParams</> is the number of parameters for which types
1652
are pre-specified in the array <parameter>paramTypes[]</>. (The
1653
array pointer can be <symbol>NULL</symbol> when
1654
<parameter>nParams</> is zero.) <parameter>paramTypes[]</>
1655
specifies, by OID, the data types to be assigned to the parameter
1656
symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1657
or any particular element in the array is zero, the server assigns
1658
a data type to the parameter symbol in the same way it would do
1659
for an untyped literal string. Also, the query can use parameter
1660
symbols with numbers higher than <parameter>nParams</>; data types
1661
will be inferred for these symbols as well. (See
1662
<function>PQdescribePrepared</function> for a means to find out
1663
what data types were inferred.)
1667
As with <function>PQexec</>, the result is normally a
1668
<structname>PGresult</structname> object whose contents indicate
1669
server-side success or failure. A null result indicates
1670
out-of-memory or inability to send the command at all. Use
1671
<function>PQerrorMessage</function> to get more information about
1678
Prepared statements for use with <function>PQexecPrepared</> can also
1679
be created by executing SQL <xref linkend="sql-prepare"
1680
endterm="sql-prepare-title"> statements. (But <function>PQprepare</>
1681
is more flexible since it does not require parameter types to be
1682
pre-specified.) Also, although there is no <application>libpq</>
1683
function for deleting a prepared statement, the SQL <xref
1684
linkend="sql-deallocate" endterm="sql-deallocate-title"> statement
1685
can be used for that purpose.
1692
<function>PQexecPrepared</function>
1694
<primary>PQexecPrepared</primary>
1700
Sends a request to execute a prepared statement with given
1701
parameters, and waits for the result.
1703
PGresult *PQexecPrepared(PGconn *conn,
1704
const char *stmtName,
1706
const char * const *paramValues,
1707
const int *paramLengths,
1708
const int *paramFormats,
1714
<function>PQexecPrepared</> is like <function>PQexecParams</>,
1715
but the command to be executed is specified by naming a
1716
previously-prepared statement, instead of giving a query string.
1717
This feature allows commands that will be used repeatedly to be
1718
parsed and planned just once, rather than each time they are
1719
executed. The statement must have been prepared previously in
1720
the current session. <function>PQexecPrepared</> is supported
1721
only in protocol 3.0 and later connections; it will fail when
1726
The parameters are identical to <function>PQexecParams</>, except that the
1727
name of a prepared statement is given instead of a query string, and the
1728
<parameter>paramTypes[]</> parameter is not present (it is not needed since
1729
the prepared statement's parameter types were determined when it was created).
1736
<function>PQdescribePrepared</function>
1738
<primary>PQdescribePrepared</primary>
1744
Submits a request to obtain information about the specified
1745
prepared statement, and waits for completion.
1747
PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
1752
<function>PQdescribePrepared</> allows an application to obtain
1753
information about a previously prepared statement.
1754
<function>PQdescribePrepared</> is supported only in protocol 3.0
1755
and later connections; it will fail when using protocol 2.0.
1759
<parameter>stmtName</> can be <literal>""</> or NULL to reference
1760
the unnamed statement, otherwise it must be the name of an existing
1761
prepared statement. On success, a <structname>PGresult</> with
1762
status <literal>PGRES_COMMAND_OK</literal> is returned. The
1763
functions <function>PQnparams</function> and
1764
<function>PQparamtype</function> can be applied to this
1765
<structname>PGresult</> to obtain information about the parameters
1766
of the prepared statement, and the functions
1767
<function>PQnfields</function>, <function>PQfname</function>,
1768
<function>PQftype</function>, etc provide information about the
1769
result columns (if any) of the statement.
1776
<function>PQdescribePortal</function>
1778
<primary>PQdescribePortal</primary>
1784
Submits a request to obtain information about the specified
1785
portal, and waits for completion.
1787
PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
1792
<function>PQdescribePortal</> allows an application to obtain
1793
information about a previously created portal.
1794
(<application>libpq</> does not provide any direct access to
1795
portals, but you can use this function to inspect the properties
1796
of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
1797
<function>PQdescribePortal</> is supported only in protocol 3.0
1798
and later connections; it will fail when using protocol 2.0.
1802
<parameter>portalName</> can be <literal>""</> or NULL to reference
1803
the unnamed portal, otherwise it must be the name of an existing
1804
portal. On success, a <structname>PGresult</> with status
1805
<literal>PGRES_COMMAND_OK</literal> is returned. The functions
1806
<function>PQnfields</function>, <function>PQfname</function>,
1807
<function>PQftype</function>, etc can be applied to the
1808
<structname>PGresult</> to obtain information about the result
1809
columns (if any) of the portal.
1817
The <structname>PGresult</structname><indexterm><primary>PGresult</></>
1818
structure encapsulates the result returned by the server.
1819
<application>libpq</application> application programmers should be
1820
careful to maintain the <structname>PGresult</structname> abstraction.
1821
Use the accessor functions below to get at the contents of
1822
<structname>PGresult</structname>. Avoid directly referencing the
1823
fields of the <structname>PGresult</structname> structure because they
1824
are subject to change in the future.
1829
<function>PQresultStatus</function>
1831
<primary>PQresultStatus</primary>
1837
Returns the result status of the command.
1839
ExecStatusType PQresultStatus(const PGresult *res);
1844
<function>PQresultStatus</function> can return one of the following values:
1848
<term><literal>PGRES_EMPTY_QUERY</literal></term>
1851
The string sent to the server was empty.
1857
<term><literal>PGRES_COMMAND_OK</literal></term>
1860
Successful completion of a command returning no data.
1866
<term><literal>PGRES_TUPLES_OK</literal></term>
1869
Successful completion of a command returning data (such as
1870
a <command>SELECT</> or <command>SHOW</>).
1876
<term><literal>PGRES_COPY_OUT</literal></term>
1879
Copy Out (from server) data transfer started.
1885
<term><literal>PGRES_COPY_IN</literal></term>
1888
Copy In (to server) data transfer started.
1894
<term><literal>PGRES_BAD_RESPONSE</literal></term>
1897
The server's response was not understood.
1903
<term><literal>PGRES_NONFATAL_ERROR</literal></term>
1906
A nonfatal error (a notice or warning) occurred.
1912
<term><literal>PGRES_FATAL_ERROR</literal></term>
1915
A fatal error occurred.
1921
If the result status is <literal>PGRES_TUPLES_OK</literal>, then
1922
the functions described below can be used to retrieve the rows
1923
returned by the query. Note that a <command>SELECT</command>
1924
command that happens to retrieve zero rows still shows
1925
<literal>PGRES_TUPLES_OK</literal>.
1926
<literal>PGRES_COMMAND_OK</literal> is for commands that can never
1927
return rows (<command>INSERT</command>, <command>UPDATE</command>,
1928
etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
1929
indicate a bug in the client software.
1933
A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
1934
never be returned directly by <function>PQexec</function> or other
1935
query execution functions; results of this kind are instead passed
1936
to the notice processor (see <xref
1937
linkend="libpq-notice-processing">).
1944
<function>PQresStatus</function>
1946
<primary>PQresStatus</primary>
1952
Converts the enumerated type returned by
1953
<function>PQresultStatus</> into a string constant describing the
1954
status code. The caller should not free the result.
1957
char *PQresStatus(ExecStatusType status);
1965
<function>PQresultErrorMessage</function>
1967
<primary>PQresultErrorMessage</primary>
1973
Returns the error message associated with the command, or an empty string
1974
if there was no error.
1976
char *PQresultErrorMessage(const PGresult *res);
1978
If there was an error, the returned string will include a trailing
1979
newline. The caller should not free the result directly. It will
1980
be freed when the associated <structname>PGresult</> handle is
1981
passed to <function>PQclear</function>.
1985
Immediately following a <function>PQexec</function> or
1986
<function>PQgetResult</function> call,
1987
<function>PQerrorMessage</function> (on the connection) will return
1988
the same string as <function>PQresultErrorMessage</function> (on
1989
the result). However, a <structname>PGresult</structname> will
1990
retain its error message until destroyed, whereas the connection's
1991
error message will change when subsequent operations are done.
1992
Use <function>PQresultErrorMessage</function> when you want to
1993
know the status associated with a particular
1994
<structname>PGresult</structname>; use
1995
<function>PQerrorMessage</function> when you want to know the
1996
status from the latest operation on the connection.
2002
<term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
2005
Returns an individual field of an error report.
2007
char *PQresultErrorField(const PGresult *res, int fieldcode);
2009
<parameter>fieldcode</> is an error field identifier; see the symbols
2010
listed below. <symbol>NULL</symbol> is returned if the
2011
<structname>PGresult</structname> is not an error or warning result,
2012
or does not include the specified field. Field values will normally
2013
not include a trailing newline. The caller should not free the
2014
result directly. It will be freed when the
2015
associated <structname>PGresult</> handle is passed to
2016
<function>PQclear</function>.
2020
The following field codes are available:
2023
<term><symbol>PG_DIAG_SEVERITY</></term>
2026
The severity; the field contents are <literal>ERROR</>,
2027
<literal>FATAL</>, or <literal>PANIC</> (in an error message),
2028
or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
2029
<literal>INFO</>, or <literal>LOG</> (in a notice message), or
2030
a localized translation of one of these. Always present.
2037
<primary>error codes</primary>
2038
<secondary>libpq</secondary>
2040
<term><symbol>PG_DIAG_SQLSTATE</></term>
2043
The SQLSTATE code for the error. The SQLSTATE code identifies
2044
the type of error that has occurred; it can be used by
2045
front-end applications to perform specific operations (such
2046
as error handling) in response to a particular database error.
2047
For a list of the possible SQLSTATE codes, see <xref
2048
linkend="errcodes-appendix">. This field is not localizable,
2049
and is always present.
2055
<term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
2058
The primary human-readable error message (typically one line).
2065
<term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
2068
Detail: an optional secondary error message carrying more
2069
detail about the problem. Might run to multiple lines.
2075
<term><symbol>PG_DIAG_MESSAGE_HINT</></term>
2078
Hint: an optional suggestion what to do about the problem.
2079
This is intended to differ from detail in that it offers advice
2080
(potentially inappropriate) rather than hard facts. Might
2081
run to multiple lines.
2087
<term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
2090
A string containing a decimal integer indicating an error cursor
2091
position as an index into the original statement string. The
2092
first character has index 1, and positions are measured in
2093
characters not bytes.
2099
<term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
2102
This is defined the same as the
2103
<symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
2104
when the cursor position refers to an internally generated
2105
command rather than the one submitted by the client. The
2106
<symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
2113
<term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
2116
The text of a failed internally-generated command. This could
2117
be, for example, a SQL query issued by a PL/pgSQL function.
2123
<term><symbol>PG_DIAG_CONTEXT</></term>
2126
An indication of the context in which the error occurred.
2127
Presently this includes a call stack traceback of active
2128
procedural language functions and internally-generated queries.
2129
The trace is one entry per line, most recent first.
2135
<term><symbol>PG_DIAG_SOURCE_FILE</></term>
2138
The file name of the source-code location where the error was
2145
<term><symbol>PG_DIAG_SOURCE_LINE</></term>
2148
The line number of the source-code location where the error
2155
<term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
2158
The name of the source-code function reporting the error.
2166
The client is responsible for formatting displayed information to meet
2167
its needs; in particular it should break long lines as needed.
2168
Newline characters appearing in the error message fields should be
2169
treated as paragraph breaks, not line breaks.
2173
Errors generated internally by <application>libpq</application> will
2174
have severity and primary message, but typically no other fields.
2175
Errors returned by a pre-3.0-protocol server will include severity and
2176
primary message, and sometimes a detail message, but no other fields.
2180
Note that error fields are only available from
2181
<structname>PGresult</structname> objects, not
2182
<structname>PGconn</structname> objects; there is no
2183
<function>PQerrorField</function> function.
2189
<term><function>PQclear</function><indexterm><primary>PQclear</></></term>
2192
Frees the storage associated with a
2193
<structname>PGresult</structname>. Every command result should be
2194
freed via <function>PQclear</function> when it is no longer
2198
void PQclear(PGresult *res);
2203
You can keep a <structname>PGresult</structname> object around for
2204
as long as you need it; it does not go away when you issue a new
2205
command, nor even if you close the connection. To get rid of it,
2206
you must call <function>PQclear</function>. Failure to do this
2207
will result in memory leaks in your application.
2215
<sect2 id="libpq-exec-select-info">
2216
<title>Retrieving Query Result Information</title>
2219
These functions are used to extract information from a
2220
<structname>PGresult</structname> object that represents a successful
2221
query result (that is, one that has status
2222
<literal>PGRES_TUPLES_OK</literal>). They can also be used to extract
2223
information from a successful Describe operation: a Describe's result
2224
has all the same column information that actual execution of the query
2225
would provide, but it has zero rows. For objects with other status values,
2226
these functions will act as though the result has zero rows and zero columns.
2232
<function>PQntuples</function>
2234
<primary>PQntuples</primary>
2240
Returns the number of rows (tuples) in the query result. Because
2241
it returns an integer result, large result sets might overflow the
2242
return value on 32-bit operating systems.
2245
int PQntuples(const PGresult *res);
2254
<function>PQnfields</function>
2256
<primary>PQnfields</primary>
2262
Returns the number of columns (fields) in each row of the query
2266
int PQnfields(const PGresult *res);
2274
<function>PQfname</function>
2276
<primary>PQfname</primary>
2282
Returns the column name associated with the given column number.
2283
Column numbers start at 0. The caller should not free the result
2284
directly. It will be freed when the associated
2285
<structname>PGresult</> handle is passed to
2286
<function>PQclear</function>.
2288
char *PQfname(const PGresult *res,
2294
<symbol>NULL</symbol> is returned if the column number is out of range.
2301
<function>PQfnumber</function>
2303
<primary>PQfnumber</primary>
2309
Returns the column number associated with the given column name.
2311
int PQfnumber(const PGresult *res,
2312
const char *column_name);
2317
-1 is returned if the given name does not match any column.
2321
The given name is treated like an identifier in an SQL command,
2322
that is, it is downcased unless double-quoted. For example, given
2323
a query result generated from the SQL command:
2325
SELECT 1 AS FOO, 2 AS "BAR";
2327
we would have the results:
2329
PQfname(res, 0) <lineannotation>foo</lineannotation>
2330
PQfname(res, 1) <lineannotation>BAR</lineannotation>
2331
PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
2332
PQfnumber(res, "foo") <lineannotation>0</lineannotation>
2333
PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
2334
PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
2342
<function>PQftable</function>
2344
<primary>PQftable</primary>
2350
Returns the OID of the table from which the given column was
2351
fetched. Column numbers start at 0.
2353
Oid PQftable(const PGresult *res,
2359
<literal>InvalidOid</> is returned if the column number is out of range,
2360
or if the specified column is not a simple reference to a table column,
2361
or when using pre-3.0 protocol.
2362
You can query the system table <literal>pg_class</literal> to determine
2363
exactly which table is referenced.
2367
The type <type>Oid</type> and the constant
2368
<literal>InvalidOid</literal> will be defined when you include
2369
the <application>libpq</application> header file. They will both
2370
be some integer type.
2377
<function>PQftablecol</function>
2379
<primary>PQftablecol</primary>
2385
Returns the column number (within its table) of the column making
2386
up the specified query result column. Query-result column numbers
2387
start at 0, but table columns have nonzero numbers.
2389
int PQftablecol(const PGresult *res,
2395
Zero is returned if the column number is out of range, or if the
2396
specified column is not a simple reference to a table column, or
2397
when using pre-3.0 protocol.
2404
<function>PQfformat</function>
2406
<primary>PQfformat</primary>
2412
Returns the format code indicating the format of the given
2413
column. Column numbers start at 0.
2415
int PQfformat(const PGresult *res,
2421
Format code zero indicates textual data representation, while format
2422
code one indicates binary representation. (Other codes are reserved
2423
for future definition.)
2430
<function>PQftype</function>
2432
<primary>PQftype</primary>
2438
Returns the data type associated with the given column number.
2439
The integer returned is the internal OID number of the type.
2440
Column numbers start at 0.
2442
Oid PQftype(const PGresult *res,
2448
You can query the system table <literal>pg_type</literal> to
2449
obtain the names and properties of the various data types. The
2450
<acronym>OID</acronym>s of the built-in data types are defined
2451
in the file <filename>src/include/catalog/pg_type.h</filename>
2459
<function>PQfmod</function>
2461
<primary>PQfmod</primary>
2467
Returns the type modifier of the column associated with the
2468
given column number. Column numbers start at 0.
2470
int PQfmod(const PGresult *res,
2476
The interpretation of modifier values is type-specific; they
2477
typically indicate precision or size limits. The value -1 is
2478
used to indicate <quote>no information available</>. Most data
2479
types do not use modifiers, in which case the value is always
2487
<function>PQfsize</function>
2489
<primary>PQfsize</primary>
2495
Returns the size in bytes of the column associated with the
2496
given column number. Column numbers start at 0.
2498
int PQfsize(const PGresult *res,
2504
<function>PQfsize</> returns the space allocated for this column
2505
in a database row, in other words the size of the server's
2506
internal representation of the data type. (Accordingly, it is
2507
not really very useful to clients.) A negative value indicates
2508
the data type is variable-length.
2515
<function>PQbinaryTuples</function>
2517
<primary>PQbinaryTuples</primary>
2523
Returns 1 if the <structname>PGresult</> contains binary data
2524
and 0 if it contains text data.
2526
int PQbinaryTuples(const PGresult *res);
2531
This function is deprecated (except for its use in connection with
2532
<command>COPY</>), because it is possible for a single
2533
<structname>PGresult</> to contain text data in some columns and
2534
binary data in others. <function>PQfformat</> is preferred.
2535
<function>PQbinaryTuples</> returns 1 only if all columns of the
2536
result are binary (format 1).
2543
<function>PQgetvalue</function>
2545
<primary>PQgetvalue</primary>
2551
Returns a single field value of one row of a
2552
<structname>PGresult</structname>. Row and column numbers start
2553
at 0. The caller should not free the result directly. It will
2554
be freed when the associated <structname>PGresult</> handle is
2555
passed to <function>PQclear</function>.
2557
char *PQgetvalue(const PGresult *res,
2564
For data in text format, the value returned by
2565
<function>PQgetvalue</function> is a null-terminated character
2566
string representation of the field value. For data in binary
2567
format, the value is in the binary representation determined by
2568
the data type's <function>typsend</> and <function>typreceive</>
2569
functions. (The value is actually followed by a zero byte in
2570
this case too, but that is not ordinarily useful, since the
2571
value is likely to contain embedded nulls.)
2575
An empty string is returned if the field value is null. See
2576
<function>PQgetisnull</> to distinguish null values from
2577
empty-string values.
2581
The pointer returned by <function>PQgetvalue</function> points
2582
to storage that is part of the <structname>PGresult</structname>
2583
structure. One should not modify the data it points to, and one
2584
must explicitly copy the data into other storage if it is to be
2585
used past the lifetime of the <structname>PGresult</structname>
2593
<function>PQgetisnull</function>
2595
<primary>PQgetisnull</primary>
2598
<primary>null value</primary>
2599
<secondary sortas="libpq">in libpq</secondary>
2605
Tests a field for a null value. Row and column numbers start
2608
int PQgetisnull(const PGresult *res,
2615
This function returns 1 if the field is null and 0 if it
2616
contains a non-null value. (Note that
2617
<function>PQgetvalue</function> will return an empty string,
2618
not a null pointer, for a null field.)
2625
<function>PQgetlength</function>
2627
<primary>PQgetlength</primary>
2632
Returns the actual length of a field value in bytes. Row and
2633
column numbers start at 0.
2635
int PQgetlength(const PGresult *res,
2642
This is the actual data length for the particular data value,
2643
that is, the size of the object pointed to by
2644
<function>PQgetvalue</function>. For text data format this is
2645
the same as <function>strlen()</>. For binary format this is
2646
essential information. Note that one should <emphasis>not</>
2647
rely on <function>PQfsize</function> to obtain the actual data
2655
<function>PQnparams</function>
2657
<primary>PQnparams</primary>
2663
Returns the number of parameters of a prepared statement.
2665
int PQnparams(const PGresult *res);
2670
This function is only useful when inspecting the result of
2671
<function>PQdescribePrepared</>. For other types of queries it
2679
<function>PQparamtype</function>
2681
<primary>PQparamtype</primary>
2687
Returns the data type of the indicated statement parameter.
2688
Parameter numbers start at 0.
2690
Oid PQparamtype(const PGresult *res, int param_number);
2695
This function is only useful when inspecting the result of
2696
<function>PQdescribePrepared</>. For other types of queries it
2704
<function>PQprint</function>
2706
<primary>PQprint</primary>
2712
Prints out all the rows and, optionally, the column names to
2713
the specified output stream.
2715
void PQprint(FILE *fout, /* output stream */
2716
const PGresult *res,
2717
const PQprintOpt *po);
2719
pqbool header; /* print output field headings and row count */
2720
pqbool align; /* fill align the fields */
2721
pqbool standard; /* old brain dead format */
2722
pqbool html3; /* output HTML tables */
2723
pqbool expanded; /* expand tables */
2724
pqbool pager; /* use pager for output if needed */
2725
char *fieldSep; /* field separator */
2726
char *tableOpt; /* attributes for HTML table element */
2727
char *caption; /* HTML table caption */
2728
char **fieldName; /* null-terminated array of replacement field names */
2734
This function was formerly used by <application>psql</application>
2735
to print query results, but this is no longer the case. Note
2736
that it assumes all the data is in text format.
2743
<sect2 id="libpq-exec-nonselect">
2744
<title>Retrieving Result Information for Other Commands</title>
2747
These functions are used to extract information from
2748
<structname>PGresult</structname> objects that are not
2749
<command>SELECT</> results.
2755
<function>PQcmdStatus</function>
2757
<primary>PQcmdStatus</primary>
2763
Returns the command status tag from the SQL command that generated
2764
the <structname>PGresult</structname>.
2766
char *PQcmdStatus(PGresult *res);
2771
Commonly this is just the name of the command, but it might include
2772
additional data such as the number of rows processed. The caller
2773
should not free the result directly. It will be freed when the
2774
associated <structname>PGresult</> handle is passed to
2775
<function>PQclear</function>.
2782
<function>PQcmdTuples</function>
2784
<primary>PQcmdTuples</primary>
2790
Returns the number of rows affected by the SQL command.
2792
char *PQcmdTuples(PGresult *res);
2797
This function returns a string containing the number of rows
2798
affected by the <acronym>SQL</> statement that generated the
2799
<structname>PGresult</>. This function can only be used following
2800
the execution of an <command>INSERT</>, <command>UPDATE</>,
2801
<command>DELETE</>, <command>MOVE</>, <command>FETCH</>, or
2802
<command>COPY</> statement, or an <command>EXECUTE</> of a
2803
prepared query that contains an <command>INSERT</>,
2804
<command>UPDATE</>, or <command>DELETE</> statement. If the
2805
command that generated the <structname>PGresult</> was anything
2806
else, <function>PQcmdTuples</> returns an empty string. The caller
2807
should not free the return value directly. It will be freed when
2808
the associated <structname>PGresult</> handle is passed to
2809
<function>PQclear</function>.
2816
<function>PQoidValue</function>
2818
<primary>PQoidValue</primary>
2824
Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
2825
of the inserted row, if the <acronym>SQL</> command was an
2826
<command>INSERT</> that inserted exactly one row into a table that
2827
has OIDs, or a <command>EXECUTE</> of a prepared query containing
2828
a suitable <command>INSERT</> statement. Otherwise, this function
2829
returns <literal>InvalidOid</literal>. This function will also
2830
return <literal>InvalidOid</literal> if the table affected by the
2831
<command>INSERT</> statement does not contain OIDs.
2833
Oid PQoidValue(const PGresult *res);
2841
<function>PQoidStatus</function>
2843
<primary>PQoidStatus</primary>
2849
Returns a string with the OID of the inserted row, if the
2850
<acronym>SQL</acronym> command was an <command>INSERT</command>
2851
that inserted exactly one row, or a <command>EXECUTE</command> of
2852
a prepared statement consisting of a suitable
2853
<command>INSERT</command>. (The string will be <literal>0</> if
2854
the <command>INSERT</command> did not insert exactly one row, or
2855
if the target table does not have OIDs.) If the command was not
2856
an <command>INSERT</command>, returns an empty string.
2858
char *PQoidStatus(const PGresult *res);
2863
This function is deprecated in favor of
2864
<function>PQoidValue</function>. It is not thread-safe.
2872
<sect2 id="libpq-exec-escape-string">
2873
<title>Escaping Strings for Inclusion in SQL Commands</title>
2875
<indexterm zone="libpq-exec-escape-string">
2876
<primary>PQescapeStringConn</primary>
2878
<indexterm zone="libpq-exec-escape-string">
2879
<primary>PQescapeString</primary>
2881
<indexterm zone="libpq-exec-escape-string">
2882
<primary>escaping strings</primary>
2883
<secondary>in libpq</secondary>
2887
<function>PQescapeStringConn</function> escapes a string for use within an SQL
2888
command. This is useful when inserting data values as literal constants
2889
in SQL commands. Certain characters (such as quotes and backslashes) must
2890
be escaped to prevent them from being interpreted specially by the SQL parser.
2891
<function>PQescapeStringConn</> performs this operation.
2896
It is especially important to do proper escaping when handling strings that
2897
were received from an untrustworthy source. Otherwise there is a security
2898
risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
2899
SQL commands are fed to your database.
2904
Note that it is not necessary nor correct to do escaping when a data
2905
value is passed as a separate parameter in <function>PQexecParams</> or
2906
its sibling routines.
2909
size_t PQescapeStringConn (PGconn *conn,
2910
char *to, const char *from, size_t length,
2916
<function>PQescapeStringConn</> writes an escaped version of the
2917
<parameter>from</> string to the <parameter>to</> buffer, escaping
2918
special characters so that they cannot cause any harm, and adding a
2919
terminating zero byte. The single quotes that must surround
2920
<productname>PostgreSQL</> string literals are not included in the
2921
result string; they should be provided in the SQL command that the
2922
result is inserted into. The parameter <parameter>from</> points to
2923
the first character of the string that is to be escaped, and the
2924
<parameter>length</> parameter gives the number of bytes in this
2925
string. A terminating zero byte is not required, and should not be
2926
counted in <parameter>length</>. (If a terminating zero byte is found
2927
before <parameter>length</> bytes are processed,
2928
<function>PQescapeStringConn</> stops at the zero; the behavior is
2929
thus rather like <function>strncpy</>.) <parameter>to</> shall point
2930
to a buffer that is able to hold at least one more byte than twice
2931
the value of <parameter>length</>, otherwise the behavior is undefined.
2932
Behavior is likewise undefined if the <parameter>to</> and
2933
<parameter>from</> strings overlap.
2937
If the <parameter>error</> parameter is not NULL, then
2938
<literal>*error</> is set to zero on success, nonzero on error.
2939
Presently the only possible error conditions involve invalid multibyte
2940
encoding in the source string. The output string is still generated
2941
on error, but it can be expected that the server will reject it as
2942
malformed. On error, a suitable message is stored in the
2943
<parameter>conn</> object, whether or not <parameter>error</> is NULL.
2947
<function>PQescapeStringConn</> returns the number of bytes written
2948
to <parameter>to</>, not including the terminating zero byte.
2953
size_t PQescapeString (char *to, const char *from, size_t length);
2958
<function>PQescapeString</> is an older, deprecated version of
2959
<function>PQescapeStringConn</>; the difference is that it does
2960
not take <parameter>conn</> or <parameter>error</> parameters.
2961
Because of this, it cannot adjust its behavior depending on the
2962
connection properties (such as character encoding) and therefore
2963
<emphasis>it might give the wrong results</>. Also, it has no way
2964
to report error conditions.
2968
<function>PQescapeString</> can be used safely in single-threaded
2969
client programs that work with only one <productname>PostgreSQL</>
2970
connection at a time (in this case it can find out what it needs to
2971
know <quote>behind the scenes</>). In other contexts it is a security
2972
hazard and should be avoided in favor of
2973
<function>PQescapeStringConn</>.
2978
<sect2 id="libpq-exec-escape-bytea">
2979
<title>Escaping Binary Strings for Inclusion in SQL Commands</title>
2981
<indexterm zone="libpq-exec-escape-bytea">
2982
<primary>bytea</primary>
2983
<secondary sortas="libpq">in libpq</secondary>
2989
<function>PQescapeByteaConn</function>
2991
<primary>PQescapeByteaConn</primary>
2997
Escapes binary data for use within an SQL command with the type
2998
<type>bytea</type>. As with <function>PQescapeStringConn</function>,
2999
this is only used when inserting data directly into an SQL command string.
3001
unsigned char *PQescapeByteaConn(PGconn *conn,
3002
const unsigned char *from,
3009
Certain byte values <emphasis>must</emphasis> be escaped (but all
3010
byte values <emphasis>can</emphasis> be escaped) when used as part
3011
of a <type>bytea</type> literal in an <acronym>SQL</acronym>
3012
statement. In general, to escape a byte, it is converted into the
3013
three digit octal number equal to the octet value, and preceded by
3014
usually two backslashes. The single quote (<literal>'</>) and backslash
3015
(<literal>\</>) characters have special alternative escape
3016
sequences. See <xref linkend="datatype-binary"> for more
3017
information. <function>PQescapeByteaConn</function> performs this
3018
operation, escaping only the minimally required bytes.
3022
The <parameter>from</parameter> parameter points to the first
3023
byte of the string that is to be escaped, and the
3024
<parameter>from_length</parameter> parameter gives the number of
3025
bytes in this binary string. (A terminating zero byte is
3026
neither necessary nor counted.) The <parameter>to_length</parameter>
3027
parameter points to a variable that will hold the resultant
3028
escaped string length. This result string length includes the terminating
3029
zero byte of the result.
3033
<function>PQescapeByteaConn</> returns an escaped version of the
3034
<parameter>from</parameter> parameter binary string in memory
3035
allocated with <function>malloc()</>. This memory must be freed using
3036
<function>PQfreemem()</> when the result is no longer needed. The
3037
return string has all special characters replaced so that they can
3038
be properly processed by the <productname>PostgreSQL</productname>
3039
string literal parser, and the <type>bytea</type> input function. A
3040
terminating zero byte is also added. The single quotes that must
3041
surround <productname>PostgreSQL</productname> string literals are
3042
not part of the result string.
3046
On error, a NULL pointer is returned, and a suitable error message
3047
is stored in the <parameter>conn</> object. Currently, the only
3048
possible error is insufficient memory for the result string.
3055
<function>PQescapeBytea</function>
3057
<primary>PQescapeBytea</primary>
3063
<function>PQescapeBytea</> is an older, deprecated version of
3064
<function>PQescapeByteaConn</>.
3066
unsigned char *PQescapeBytea(const unsigned char *from,
3073
The only difference from <function>PQescapeByteaConn</> is that
3074
<function>PQescapeBytea</> does not take a <structname>PGconn</>
3075
parameter. Because of this, it cannot adjust its behavior
3076
depending on the connection properties (in particular, whether
3077
standard-conforming strings are enabled) and therefore
3078
<emphasis>it might give the wrong results</>. Also, it has no
3079
way to return an error message on failure.
3083
<function>PQescapeBytea</> can be used safely in single-threaded
3084
client programs that work with only one <productname>PostgreSQL</>
3085
connection at a time (in this case it can find out what it needs
3086
to know <quote>behind the scenes</>). In other contexts it is
3087
a security hazard and should be avoided in favor of
3088
<function>PQescapeByteaConn</>.
3095
<function>PQunescapeBytea</function>
3097
<primary>PQunescapeBytea</primary>
3103
Converts a string representation of binary data into binary data
3104
— the reverse of <function>PQescapeBytea</function>. This
3105
is needed when retrieving <type>bytea</type> data in text format,
3106
but not when retrieving it in binary format.
3109
unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
3114
The <parameter>from</parameter> parameter points to a string
3115
such as might be returned by <function>PQgetvalue</function> when applied
3116
to a <type>bytea</type> column. <function>PQunescapeBytea</function>
3117
converts this string representation into its binary representation.
3118
It returns a pointer to a buffer allocated with
3119
<function>malloc()</function>, or null on error, and puts the size of
3120
the buffer in <parameter>to_length</parameter>. The result must be
3121
freed using <function>PQfreemem</> when it is no longer needed.
3125
This conversion is not exactly the inverse of
3126
<function>PQescapeBytea</function>, because the string is not expected
3127
to be <quote>escaped</> when received from <function>PQgetvalue</function>.
3128
In particular this means there is no need for string quoting considerations,
3129
and so no need for a <structname>PGconn</> parameter.
3139
<sect1 id="libpq-async">
3140
<title>Asynchronous Command Processing</title>
3142
<indexterm zone="libpq-async">
3143
<primary>nonblocking connection</primary>
3147
The <function>PQexec</function> function is adequate for submitting
3148
commands in normal, synchronous applications. It has a couple of
3149
deficiencies, however, that can be of importance to some users:
3154
<function>PQexec</function> waits for the command to be completed.
3155
The application might have other work to do (such as maintaining a
3156
user interface), in which case it won't want to block waiting for
3163
Since the execution of the client application is suspended while it
3164
waits for the result, it is hard for the application to decide that
3165
it would like to try to cancel the ongoing command. (It can be done
3166
from a signal handler, but not otherwise.)
3172
<function>PQexec</function> can return only one
3173
<structname>PGresult</structname> structure. If the submitted command
3174
string contains multiple <acronym>SQL</acronym> commands, all but
3175
the last <structname>PGresult</structname> are discarded by
3176
<function>PQexec</function>.
3183
Applications that do not like these limitations can instead use the
3184
underlying functions that <function>PQexec</function> is built from:
3185
<function>PQsendQuery</function> and <function>PQgetResult</function>.
3187
<function>PQsendQueryParams</function>,
3188
<function>PQsendPrepare</function>,
3189
<function>PQsendQueryPrepared</function>,
3190
<function>PQsendDescribePrepared</function>, and
3191
<function>PQsendDescribePortal</function>,
3192
which can be used with <function>PQgetResult</function> to duplicate
3193
the functionality of
3194
<function>PQexecParams</function>,
3195
<function>PQprepare</function>,
3196
<function>PQexecPrepared</function>,
3197
<function>PQdescribePrepared</function>, and
3198
<function>PQdescribePortal</function>
3204
<function>PQsendQuery</function>
3206
<primary>PQsendQuery</primary>
3212
Submits a command to the server without waiting for the result(s).
3213
1 is returned if the command was successfully dispatched and 0 if
3214
not (in which case, use <function>PQerrorMessage</> to get more
3215
information about the failure).
3217
int PQsendQuery(PGconn *conn, const char *command);
3220
After successfully calling <function>PQsendQuery</function>, call
3221
<function>PQgetResult</function> one or more times to obtain the
3222
results. <function>PQsendQuery</function> cannot be called again
3223
(on the same connection) until <function>PQgetResult</function>
3224
has returned a null pointer, indicating that the command is done.
3231
<function>PQsendQueryParams</function>
3233
<primary>PQsendQueryParams</primary>
3239
Submits a command and separate parameters to the server without
3240
waiting for the result(s).
3242
int PQsendQueryParams(PGconn *conn,
3243
const char *command,
3245
const Oid *paramTypes,
3246
const char * const *paramValues,
3247
const int *paramLengths,
3248
const int *paramFormats,
3252
This is equivalent to <function>PQsendQuery</function> except that
3253
query parameters can be specified separately from the query string.
3254
The function's parameters are handled identically to
3255
<function>PQexecParams</function>. Like
3256
<function>PQexecParams</function>, it will not work on 2.0-protocol
3257
connections, and it allows only one command in the query string.
3264
<function>PQsendPrepare</>
3266
<primary>PQsendPrepare</primary>
3272
Sends a request to create a prepared statement with the given
3273
parameters, without waiting for completion.
3275
int PQsendPrepare(PGconn *conn,
3276
const char *stmtName,
3279
const Oid *paramTypes);
3282
This is an asynchronous version of <function>PQprepare</>: it
3283
returns 1 if it was able to dispatch the request, and 0 if not.
3284
After a successful call, call <function>PQgetResult</function> to
3285
determine whether the server successfully created the prepared
3286
statement. The function's parameters are handled identically to
3287
<function>PQprepare</function>. Like
3288
<function>PQprepare</function>, it will not work on 2.0-protocol
3296
<function>PQsendQueryPrepared</function>
3298
<primary>PQsendQueryPrepared</primary>
3304
Sends a request to execute a prepared statement with given
3305
parameters, without waiting for the result(s).
3307
int PQsendQueryPrepared(PGconn *conn,
3308
const char *stmtName,
3310
const char * const *paramValues,
3311
const int *paramLengths,
3312
const int *paramFormats,
3316
This is similar to <function>PQsendQueryParams</function>, but
3317
the command to be executed is specified by naming a
3318
previously-prepared statement, instead of giving a query string.
3319
The function's parameters are handled identically to
3320
<function>PQexecPrepared</function>. Like
3321
<function>PQexecPrepared</function>, it will not work on
3322
2.0-protocol connections.
3329
<function>PQsendDescribePrepared</>
3331
<primary>PQsendDescribePrepared</primary>
3337
Submits a request to obtain information about the specified
3338
prepared statement, without waiting for completion.
3340
int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
3343
This is an asynchronous version of <function>PQdescribePrepared</>:
3344
it returns 1 if it was able to dispatch the request, and 0 if not.
3345
After a successful call, call <function>PQgetResult</function> to
3346
obtain the results. The function's parameters are handled
3347
identically to <function>PQdescribePrepared</function>. Like
3348
<function>PQdescribePrepared</function>, it will not work on
3349
2.0-protocol connections.
3356
<function>PQsendDescribePortal</>
3358
<primary>PQsendDescribePortal</primary>
3364
Submits a request to obtain information about the specified
3365
portal, without waiting for completion.
3367
int PQsendDescribePortal(PGconn *conn, const char *portalName);
3370
This is an asynchronous version of <function>PQdescribePortal</>:
3371
it returns 1 if it was able to dispatch the request, and 0 if not.
3372
After a successful call, call <function>PQgetResult</function> to
3373
obtain the results. The function's parameters are handled
3374
identically to <function>PQdescribePortal</function>. Like
3375
<function>PQdescribePortal</function>, it will not work on
3376
2.0-protocol connections.
3383
<function>PQgetResult</function>
3385
<primary>PQgetResult</primary>
3391
Waits for the next result from a prior
3392
<function>PQsendQuery</function>,
3393
<function>PQsendQueryParams</function>,
3394
<function>PQsendPrepare</function>, or
3395
<function>PQsendQueryPrepared</function> call, and returns it.
3396
A null pointer is returned when the command is complete and there
3397
will be no more results.
3399
PGresult *PQgetResult(PGconn *conn);
3404
<function>PQgetResult</function> must be called repeatedly until
3405
it returns a null pointer, indicating that the command is done.
3406
(If called when no command is active,
3407
<function>PQgetResult</function> will just return a null pointer
3408
at once.) Each non-null result from
3409
<function>PQgetResult</function> should be processed using the
3410
same <structname>PGresult</> accessor functions previously
3411
described. Don't forget to free each result object with
3412
<function>PQclear</function> when done with it. Note that
3413
<function>PQgetResult</function> will block only if a command is
3414
active and the necessary response data has not yet been read by
3415
<function>PQconsumeInput</function>.
3423
Using <function>PQsendQuery</function> and
3424
<function>PQgetResult</function> solves one of
3425
<function>PQexec</function>'s problems: If a command string contains
3426
multiple <acronym>SQL</acronym> commands, the results of those commands
3427
can be obtained individually. (This allows a simple form of overlapped
3428
processing, by the way: the client can be handling the results of one
3429
command while the server is still working on later queries in the same
3430
command string.) However, calling <function>PQgetResult</function>
3431
will still cause the client to block until the server completes the
3432
next <acronym>SQL</acronym> command. This can be avoided by proper
3433
use of two more functions:
3438
<function>PQconsumeInput</function>
3440
<primary>PQconsumeInput</primary>
3446
If input is available from the server, consume it.
3448
int PQconsumeInput(PGconn *conn);
3453
<function>PQconsumeInput</function> normally returns 1 indicating
3454
<quote>no error</quote>, but returns 0 if there was some kind of
3455
trouble (in which case <function>PQerrorMessage</function> can be
3456
consulted). Note that the result does not say whether any input
3457
data was actually collected. After calling
3458
<function>PQconsumeInput</function>, the application can check
3459
<function>PQisBusy</function> and/or
3460
<function>PQnotifies</function> to see if their state has changed.
3464
<function>PQconsumeInput</function> can be called even if the
3465
application is not prepared to deal with a result or notification
3466
just yet. The function will read available data and save it in
3467
a buffer, thereby causing a <function>select()</function>
3468
read-ready indication to go away. The application can thus use
3469
<function>PQconsumeInput</function> to clear the
3470
<function>select()</function> condition immediately, and then
3471
examine the results at leisure.
3478
<function>PQisBusy</function>
3480
<primary>PQisBusy</primary>
3486
Returns 1 if a command is busy, that is,
3487
<function>PQgetResult</function> would block waiting for input.
3488
A 0 return indicates that <function>PQgetResult</function> can be
3489
called with assurance of not blocking.
3491
int PQisBusy(PGconn *conn);
3496
<function>PQisBusy</function> will not itself attempt to read data
3497
from the server; therefore <function>PQconsumeInput</function>
3498
must be invoked first, or the busy state will never end.
3506
A typical application using these functions will have a main loop that
3507
uses <function>select()</function> or <function>poll()</> to wait for
3508
all the conditions that it must respond to. One of the conditions
3509
will be input available from the server, which in terms of
3510
<function>select()</function> means readable data on the file
3511
descriptor identified by <function>PQsocket</function>. When the main
3512
loop detects input ready, it should call
3513
<function>PQconsumeInput</function> to read the input. It can then
3514
call <function>PQisBusy</function>, followed by
3515
<function>PQgetResult</function> if <function>PQisBusy</function>
3516
returns false (0). It can also call <function>PQnotifies</function>
3517
to detect <command>NOTIFY</> messages (see <xref
3518
linkend="libpq-notify">).
3523
<function>PQsendQuery</function>/<function>PQgetResult</function>
3524
can also attempt to cancel a command that is still being processed
3525
by the server; see <xref linkend="libpq-cancel">. But regardless of
3526
the return value of <function>PQcancel</function>, the application
3527
must continue with the normal result-reading sequence using
3528
<function>PQgetResult</function>. A successful cancellation will
3529
simply cause the command to terminate sooner than it would have
3534
By using the functions described above, it is possible to avoid
3535
blocking while waiting for input from the database server. However,
3536
it is still possible that the application will block waiting to send
3537
output to the server. This is relatively uncommon but can happen if
3538
very long SQL commands or data values are sent. (It is much more
3539
probable if the application sends data via <command>COPY IN</command>,
3540
however.) To prevent this possibility and achieve completely
3541
nonblocking database operation, the following additional functions
3547
<function>PQsetnonblocking</function>
3549
<primary>PQsetnonblocking</primary>
3555
Sets the nonblocking status of the connection.
3557
int PQsetnonblocking(PGconn *conn, int arg);
3562
Sets the state of the connection to nonblocking if
3563
<parameter>arg</parameter> is 1, or blocking if
3564
<parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
3568
In the nonblocking state, calls to
3569
<function>PQsendQuery</function>, <function>PQputline</function>,
3570
<function>PQputnbytes</function>, and
3571
<function>PQendcopy</function> will not block but instead return
3572
an error if they need to be called again.
3576
Note that <function>PQexec</function> does not honor nonblocking
3577
mode; if it is called, it will act in blocking fashion anyway.
3584
<function>PQisnonblocking</function>
3586
<primary>PQisnonblocking</primary>
3592
Returns the blocking status of the database connection.
3594
int PQisnonblocking(const PGconn *conn);
3599
Returns 1 if the connection is set to nonblocking mode and 0 if
3607
<function>PQflush</function>
3609
<primary>PQflush</primary>
3615
Attempts to flush any queued output data to the server. Returns
3616
0 if successful (or if the send queue is empty), -1 if it failed
3617
for some reason, or 1 if it was unable to send all the data in
3618
the send queue yet (this case can only occur if the connection
3621
int PQflush(PGconn *conn);
3630
After sending any command or data on a nonblocking connection, call
3631
<function>PQflush</function>. If it returns 1, wait for the socket
3632
to be write-ready and call it again; repeat until it returns 0. Once
3633
<function>PQflush</function> returns 0, wait for the socket to be
3634
read-ready and then read the response as described above.
3639
<sect1 id="libpq-cancel">
3640
<title>Cancelling Queries in Progress</title>
3642
<indexterm zone="libpq-cancel">
3643
<primary>canceling</primary>
3644
<secondary>SQL command</secondary>
3648
A client application can request cancellation of a command that is
3649
still being processed by the server, using the functions described in
3655
<function>PQgetCancel</function>
3657
<primary>PQgetCancel</primary>
3663
Creates a data structure containing the information needed to cancel
3664
a command issued through a particular database connection.
3666
PGcancel *PQgetCancel(PGconn *conn);
3671
<function>PQgetCancel</function> creates a
3672
<structname>PGcancel</><indexterm><primary>PGcancel</></> object
3673
given a <structname>PGconn</> connection object. It will return
3674
NULL if the given <parameter>conn</> is NULL or an invalid
3675
connection. The <structname>PGcancel</> object is an opaque
3676
structure that is not meant to be accessed directly by the
3677
application; it can only be passed to <function>PQcancel</function>
3678
or <function>PQfreeCancel</function>.
3685
<function>PQfreeCancel</function>
3687
<primary>PQfreeCancel</primary>
3693
Frees a data structure created by <function>PQgetCancel</function>.
3695
void PQfreeCancel(PGcancel *cancel);
3700
<function>PQfreeCancel</function> frees a data object previously created
3701
by <function>PQgetCancel</function>.
3708
<function>PQcancel</function>
3710
<primary>PQcancel</primary>
3716
Requests that the server abandon processing of the current command.
3718
int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
3723
The return value is 1 if the cancel request was successfully
3724
dispatched and 0 if not. If not, <parameter>errbuf</> is filled
3725
with an error message explaining why not. <parameter>errbuf</>
3726
must be a char array of size <parameter>errbufsize</> (the
3727
recommended size is 256 bytes).
3731
Successful dispatch is no guarantee that the request will have
3732
any effect, however. If the cancellation is effective, the current
3733
command will terminate early and return an error result. If the
3734
cancellation fails (say, because the server was already done
3735
processing the command), then there will be no visible result at
3740
<function>PQcancel</function> can safely be invoked from a signal
3741
handler, if the <parameter>errbuf</> is a local variable in the
3742
signal handler. The <structname>PGcancel</> object is read-only
3743
as far as <function>PQcancel</function> is concerned, so it can
3744
also be invoked from a thread that is separate from the one
3745
manipulating the <structname>PGconn</> object.
3754
<function>PQrequestCancel</function>
3756
<primary>PQrequestCancel</primary>
3762
Requests that the server abandon processing of the current
3765
int PQrequestCancel(PGconn *conn);
3770
<function>PQrequestCancel</function> is a deprecated variant of
3771
<function>PQcancel</function>. It operates directly on the
3772
<structname>PGconn</> object, and in case of failure stores the
3773
error message in the <structname>PGconn</> object (whence it can
3774
be retrieved by <function>PQerrorMessage</function>). Although
3775
the functionality is the same, this approach creates hazards for
3776
multiple-thread programs and signal handlers, since it is possible
3777
that overwriting the <structname>PGconn</>'s error message will
3778
mess up the operation currently in progress on the connection.
3787
<sect1 id="libpq-fastpath">
3788
<title>The Fast-Path Interface</title>
3790
<indexterm zone="libpq-fastpath">
3791
<primary>fast path</primary>
3795
<productname>PostgreSQL</productname> provides a fast-path interface
3796
to send simple function calls to the server.
3801
This interface is somewhat obsolete, as one can achieve similar
3802
performance and greater functionality by setting up a prepared
3803
statement to define the function call. Then, executing the statement
3804
with binary transmission of parameters and results substitutes for a
3805
fast-path function call.
3810
The function <function>PQfn</function><indexterm><primary>PQfn</></>
3811
requests execution of a server function via the fast-path interface:
3813
PGresult *PQfn(PGconn *conn,
3818
const PQArgBlock *args,
3833
The <parameter>fnid</> argument is the OID of the function to be
3834
executed. <parameter>args</> and <parameter>nargs</> define the
3835
parameters to be passed to the function; they must match the declared
3836
function argument list. When the <parameter>isint</> field of a
3837
parameter structure is true, the <parameter>u.integer</> value is sent
3838
to the server as an integer of the indicated length (this must be 1,
3839
2, or 4 bytes); proper byte-swapping occurs. When <parameter>isint</>
3840
is false, the indicated number of bytes at <parameter>*u.ptr</> are
3841
sent with no processing; the data must be in the format expected by
3842
the server for binary transmission of the function's argument data
3843
type. <parameter>result_buf</parameter> is the buffer in which to
3844
place the return value. The caller must have allocated sufficient
3845
space to store the return value. (There is no check!) The actual result
3846
length will be returned in the integer pointed to by
3847
<parameter>result_len</parameter>. If a 1, 2, or 4-byte integer result
3848
is expected, set <parameter>result_is_int</parameter> to 1, otherwise
3849
set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
3850
<application>libpq</> to byte-swap the value if necessary, so that it
3851
is delivered as a proper <type>int</type> value for the client machine.
3852
When <parameter>result_is_int</> is 0, the binary-format byte string
3853
sent by the server is returned unmodified.
3857
<function>PQfn</function> always returns a valid
3858
<structname>PGresult</structname> pointer. The result status should be
3859
checked before the result is used. The caller is responsible for
3860
freeing the <structname>PGresult</structname> with
3861
<function>PQclear</function> when it is no longer needed.
3865
Note that it is not possible to handle null arguments, null results,
3866
nor set-valued results when using this interface.
3871
<sect1 id="libpq-notify">
3872
<title>Asynchronous Notification</title>
3874
<indexterm zone="libpq-notify">
3875
<primary>NOTIFY</primary>
3876
<secondary>in libpq</secondary>
3880
<productname>PostgreSQL</productname> offers asynchronous notification
3881
via the <command>LISTEN</command> and <command>NOTIFY</command>
3882
commands. A client session registers its interest in a particular
3883
notification condition with the <command>LISTEN</command> command (and
3884
can stop listening with the <command>UNLISTEN</command> command). All
3885
sessions listening on a particular condition will be notified
3886
asynchronously when a <command>NOTIFY</command> command with that
3887
condition name is executed by any session. No additional information
3888
is passed from the notifier to the listener. Thus, typically, any
3889
actual data that needs to be communicated is transferred through a
3890
database table. Commonly, the condition name is the same as the
3891
associated table, but it is not necessary for there to be any associated
3896
<application>libpq</application> applications submit
3897
<command>LISTEN</command> and <command>UNLISTEN</command> commands as
3898
ordinary SQL commands. The arrival of <command>NOTIFY</command>
3899
messages can subsequently be detected by calling
3900
<function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
3904
The function <function>PQnotifies</function>
3905
returns the next notification from a list of unhandled
3906
notification messages received from the server. It returns a null pointer if
3907
there are no pending notifications. Once a notification is
3908
returned from <function>PQnotifies</>, it is considered handled and will be
3909
removed from the list of notifications.
3911
PGnotify *PQnotifies(PGconn *conn);
3913
typedef struct pgNotify {
3914
char *relname; /* notification condition name */
3915
int be_pid; /* process ID of notifying server process */
3916
char *extra; /* notification parameter */
3919
After processing a <structname>PGnotify</structname> object returned
3920
by <function>PQnotifies</function>, be sure to free it with
3921
<function>PQfreemem</function>. It is sufficient to free the
3922
<structname>PGnotify</structname> pointer; the
3923
<structfield>relname</structfield> and <structfield>extra</structfield>
3924
fields do not represent separate allocations. (At present, the
3925
<structfield>extra</structfield> field is unused and will always point
3926
to an empty string.)
3930
<xref linkend="libpq-example-2"> gives a sample program that illustrates
3931
the use of asynchronous notification.
3935
<function>PQnotifies</function> does not actually read data from the
3936
server; it just returns messages previously absorbed by another
3937
<application>libpq</application> function. In prior releases of
3938
<application>libpq</application>, the only way to ensure timely receipt
3939
of <command>NOTIFY</> messages was to constantly submit commands, even
3940
empty ones, and then check <function>PQnotifies</function> after each
3941
<function>PQexec</function>. While this still works, it is deprecated
3942
as a waste of processing power.
3946
A better way to check for <command>NOTIFY</> messages when you have no
3947
useful commands to execute is to call
3948
<function>PQconsumeInput</function>, then check
3949
<function>PQnotifies</function>. You can use
3950
<function>select()</function> to wait for data to arrive from the
3951
server, thereby using no <acronym>CPU</acronym> power unless there is
3952
something to do. (See <function>PQsocket</function> to obtain the file
3953
descriptor number to use with <function>select()</function>.) Note that
3954
this will work OK whether you submit commands with
3955
<function>PQsendQuery</function>/<function>PQgetResult</function> or
3956
simply use <function>PQexec</function>. You should, however, remember
3957
to check <function>PQnotifies</function> after each
3958
<function>PQgetResult</function> or <function>PQexec</function>, to
3959
see if any notifications came in during the processing of the command.
3964
<sect1 id="libpq-copy">
3965
<title>Functions Associated with the <command>COPY</command> Command</title>
3967
<indexterm zone="libpq-copy">
3968
<primary>COPY</primary>
3969
<secondary>with libpq</secondary>
3973
The <command>COPY</command> command in
3974
<productname>PostgreSQL</productname> has options to read from or write
3975
to the network connection used by <application>libpq</application>.
3976
The functions described in this section allow applications to take
3977
advantage of this capability by supplying or consuming copied data.
3981
The overall process is that the application first issues the SQL
3982
<command>COPY</command> command via <function>PQexec</function> or one
3983
of the equivalent functions. The response to this (if there is no
3984
error in the command) will be a <structname>PGresult</> object bearing
3985
a status code of <literal>PGRES_COPY_OUT</literal> or
3986
<literal>PGRES_COPY_IN</literal> (depending on the specified copy
3987
direction). The application should then use the functions of this
3988
section to receive or transmit data rows. When the data transfer is
3989
complete, another <structname>PGresult</> object is returned to indicate
3990
success or failure of the transfer. Its status will be
3991
<literal>PGRES_COMMAND_OK</literal> for success or
3992
<literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
3993
At this point further SQL commands can be issued via
3994
<function>PQexec</function>. (It is not possible to execute other SQL
3995
commands using the same connection while the <command>COPY</command>
3996
operation is in progress.)
4000
If a <command>COPY</command> command is issued via
4001
<function>PQexec</function> in a string that could contain additional
4002
commands, the application must continue fetching results via
4003
<function>PQgetResult</> after completing the <command>COPY</command>
4004
sequence. Only when <function>PQgetResult</> returns
4005
<symbol>NULL</symbol> is it certain that the <function>PQexec</function>
4006
command string is done and it is safe to issue more commands.
4010
The functions of this section should be executed only after obtaining
4011
a result status of <literal>PGRES_COPY_OUT</literal> or
4012
<literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
4013
<function>PQgetResult</function>.
4017
A <structname>PGresult</> object bearing one of these status values
4018
carries some additional data about the <command>COPY</command> operation
4019
that is starting. This additional data is available using functions
4020
that are also used in connection with query results:
4025
<function>PQnfields</function>
4027
<primary>PQnfields</primary>
4028
<secondary>with COPY</secondary>
4034
Returns the number of columns (fields) to be copied.
4041
<function>PQbinaryTuples</function>
4043
<primary>PQbinaryTuples</primary>
4044
<secondary>with COPY</secondary>
4050
0 indicates the overall copy format is textual (rows separated by
4051
newlines, columns separated by separator characters, etc). 1
4052
indicates the overall copy format is binary. See <xref
4053
linkend="sql-copy" endterm="sql-copy-title"> for more information.
4060
<function>PQfformat</function>
4062
<primary>PQfformat</primary>
4063
<secondary>with COPY</secondary>
4069
Returns the format code (0 for text, 1 for binary) associated with
4070
each column of the copy operation. The per-column format codes
4071
will always be zero when the overall copy format is textual, but
4072
the binary format can support both text and binary columns.
4073
(However, as of the current implementation of <command>COPY</>,
4074
only binary columns appear in a binary copy; so the per-column
4075
formats always match the overall format at present.)
4084
These additional data values are only available when using protocol
4085
3.0. When using protocol 2.0, all these functions will return 0.
4089
<sect2 id="libpq-copy-send">
4090
<title>Functions for Sending <command>COPY</command> Data</title>
4093
These functions are used to send data during <literal>COPY FROM
4094
STDIN</>. They will fail if called when the connection is not in
4095
<literal>COPY_IN</> state.
4101
<function>PQputCopyData</function>
4103
<primary>PQputCopyData</primary>
4109
Sends data to the server during <literal>COPY_IN</> state.
4111
int PQputCopyData(PGconn *conn,
4118
Transmits the <command>COPY</command> data in the specified
4119
<parameter>buffer</>, of length <parameter>nbytes</>, to the server.
4120
The result is 1 if the data was sent, zero if it was not sent
4121
because the attempt would block (this case is only possible if the
4122
connection is in nonblocking mode), or -1 if an error occurred.
4123
(Use <function>PQerrorMessage</function> to retrieve details if
4124
the return value is -1. If the value is zero, wait for write-ready
4129
The application can divide the <command>COPY</command> data stream
4130
into buffer loads of any convenient size. Buffer-load boundaries
4131
have no semantic significance when sending. The contents of the
4132
data stream must match the data format expected by the
4133
<command>COPY</> command; see <xref linkend="sql-copy"
4134
endterm="sql-copy-title"> for details.
4141
<function>PQputCopyEnd</function>
4143
<primary>PQputCopyEnd</primary>
4149
Sends end-of-data indication to the server during <literal>COPY_IN</> state.
4151
int PQputCopyEnd(PGconn *conn,
4152
const char *errormsg);
4157
Ends the <literal>COPY_IN</> operation successfully if
4158
<parameter>errormsg</> is <symbol>NULL</symbol>. If
4159
<parameter>errormsg</> is not <symbol>NULL</symbol> then the
4160
<command>COPY</> is forced to fail, with the string pointed to by
4161
<parameter>errormsg</> used as the error message. (One should not
4162
assume that this exact error message will come back from the server,
4163
however, as the server might have already failed the
4164
<command>COPY</> for its own reasons. Also note that the option
4165
to force failure does not work when using pre-3.0-protocol
4170
The result is 1 if the termination data was sent, zero if it was
4171
not sent because the attempt would block (this case is only possible
4172
if the connection is in nonblocking mode), or -1 if an error
4173
occurred. (Use <function>PQerrorMessage</function> to retrieve
4174
details if the return value is -1. If the value is zero, wait for
4175
write-ready and try again.)
4179
After successfully calling <function>PQputCopyEnd</>, call
4180
<function>PQgetResult</> to obtain the final result status of the
4181
<command>COPY</> command. One can wait for this result to be
4182
available in the usual way. Then return to normal operation.
4190
<sect2 id="libpq-copy-receive">
4191
<title>Functions for Receiving <command>COPY</command> Data</title>
4194
These functions are used to receive data during <literal>COPY TO
4195
STDOUT</>. They will fail if called when the connection is not in
4196
<literal>COPY_OUT</> state.
4202
<function>PQgetCopyData</function>
4204
<primary>PQgetCopyData</primary>
4210
Receives data from the server during <literal>COPY_OUT</> state.
4212
int PQgetCopyData(PGconn *conn,
4219
Attempts to obtain another row of data from the server during a
4220
<command>COPY</command>. Data is always returned one data row at
4221
a time; if only a partial row is available, it is not returned.
4222
Successful return of a data row involves allocating a chunk of
4223
memory to hold the data. The <parameter>buffer</> parameter must
4224
be non-<symbol>NULL</symbol>. <parameter>*buffer</> is set to
4225
point to the allocated memory, or to <symbol>NULL</symbol> in cases
4226
where no buffer is returned. A non-<symbol>NULL</symbol> result
4227
buffer must be freed using <function>PQfreemem</> when no longer
4232
When a row is successfully returned, the return value is the number
4233
of data bytes in the row (this will always be greater than zero).
4234
The returned string is always null-terminated, though this is
4235
probably only useful for textual <command>COPY</command>. A result
4236
of zero indicates that the <command>COPY</command> is still in
4237
progress, but no row is yet available (this is only possible when
4238
<parameter>async</> is true). A result of -1 indicates that the
4239
<command>COPY</command> is done. A result of -2 indicates that an
4240
error occurred (consult <function>PQerrorMessage</> for the reason).
4244
When <parameter>async</> is true (not zero),
4245
<function>PQgetCopyData</> will not block waiting for input; it
4246
will return zero if the <command>COPY</command> is still in progress
4247
but no complete row is available. (In this case wait for read-ready
4248
and then call <function>PQconsumeInput</> before calling
4249
<function>PQgetCopyData</> again.) When <parameter>async</> is
4250
false (zero), <function>PQgetCopyData</> will block until data is
4251
available or the operation completes.
4255
After <function>PQgetCopyData</> returns -1, call
4256
<function>PQgetResult</> to obtain the final result status of the
4257
<command>COPY</> command. One can wait for this result to be
4258
available in the usual way. Then return to normal operation.
4266
<sect2 id="libpq-copy-deprecated">
4267
<title>Obsolete Functions for <command>COPY</command></title>
4270
These functions represent older methods of handling <command>COPY</>.
4271
Although they still work, they are deprecated due to poor error handling,
4272
inconvenient methods of detecting end-of-data, and lack of support for binary
4273
or nonblocking transfers.
4279
<function>PQgetline</function>
4281
<primary>PQgetline</primary>
4287
Reads a newline-terminated line of characters (transmitted
4288
by the server) into a buffer string of size <parameter>length</>.
4290
int PQgetline(PGconn *conn,
4297
This function copies up to <parameter>length</>-1 characters into
4298
the buffer and converts the terminating newline into a zero byte.
4299
<function>PQgetline</function> returns <symbol>EOF</symbol> at the
4300
end of input, 0 if the entire line has been read, and 1 if the
4301
buffer is full but the terminating newline has not yet been read.
4304
Note that the application must check to see if a new line consists
4305
of the two characters <literal>\.</literal>, which indicates
4306
that the server has finished sending the results of the
4307
<command>COPY</command> command. If the application might receive
4308
lines that are more than <parameter>length</>-1 characters long,
4309
care is needed to be sure it recognizes the <literal>\.</literal>
4310
line correctly (and does not, for example, mistake the end of a
4311
long data line for a terminator line).
4318
<function>PQgetlineAsync</function>
4320
<primary>PQgetlineAsync</primary>
4326
Reads a row of <command>COPY</command> data (transmitted by the
4327
server) into a buffer without blocking.
4329
int PQgetlineAsync(PGconn *conn,
4336
This function is similar to <function>PQgetline</function>, but it can be used
4338
that must read <command>COPY</command> data asynchronously, that is, without blocking.
4339
Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
4341
application should call <function>PQconsumeInput</function> and
4342
<function>PQgetlineAsync</function> until the
4343
end-of-data signal is detected.
4346
Unlike <function>PQgetline</function>, this function takes
4347
responsibility for detecting end-of-data.
4351
On each call, <function>PQgetlineAsync</function> will return data if a
4352
complete data row is available in <application>libpq</>'s input buffer.
4353
Otherwise, no data is returned until the rest of the row arrives.
4354
The function returns -1 if the end-of-copy-data marker has been recognized,
4355
or 0 if no data is available, or a positive number giving the number of
4356
bytes of data returned. If -1 is returned, the caller must next call
4357
<function>PQendcopy</function>, and then return to normal processing.
4361
The data returned will not extend beyond a data-row boundary. If possible
4362
a whole row will be returned at one time. But if the buffer offered by
4363
the caller is too small to hold a row sent by the server, then a partial
4364
data row will be returned. With textual data this can be detected by testing
4365
whether the last returned byte is <literal>\n</literal> or not. (In a binary
4366
<command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
4367
equivalent determination.)
4368
The returned string is not null-terminated. (If you want to add a
4369
terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
4370
than the room actually available.)
4377
<function>PQputline</function>
4379
<primary>PQputline</primary>
4385
Sends a null-terminated string to the server. Returns 0 if
4386
OK and <symbol>EOF</symbol> if unable to send the string.
4388
int PQputline(PGconn *conn,
4389
const char *string);
4394
The <command>COPY</command> data stream sent by a series of calls
4395
to <function>PQputline</function> has the same format as that
4396
returned by <function>PQgetlineAsync</function>, except that
4397
applications are not obliged to send exactly one data row per
4398
<function>PQputline</function> call; it is okay to send a partial
4399
line or multiple lines per call.
4404
Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
4405
for the application to explicitly send the two characters
4406
<literal>\.</literal> as a final line to indicate to the server that it had
4407
finished sending <command>COPY</> data. While this still works, it is deprecated and the
4408
special meaning of <literal>\.</literal> can be expected to be removed in a
4409
future release. It is sufficient to call <function>PQendcopy</function> after
4410
having sent the actual data.
4418
<function>PQputnbytes</function>
4420
<primary>PQputnbytes</primary>
4426
Sends a non-null-terminated string to the server. Returns
4427
0 if OK and <symbol>EOF</symbol> if unable to send the string.
4429
int PQputnbytes(PGconn *conn,
4436
This is exactly like <function>PQputline</function>, except that the data
4437
buffer need not be null-terminated since the number of bytes to send is
4438
specified directly. Use this procedure when sending binary data.
4445
<function>PQendcopy</function>
4447
<primary>PQendcopy</primary>
4453
Synchronizes with the server.
4455
int PQendcopy(PGconn *conn);
4457
This function waits until the server has finished the copying.
4458
It should either be issued when the last string has been sent
4459
to the server using <function>PQputline</function> or when the
4460
last string has been received from the server using
4461
<function>PGgetline</function>. It must be issued or the server
4462
will get <quote>out of sync</quote> with the client. Upon return
4463
from this function, the server is ready to receive the next SQL
4464
command. The return value is 0 on successful completion,
4465
nonzero otherwise. (Use <function>PQerrorMessage</function> to
4466
retrieve details if the return value is nonzero.)
4470
When using <function>PQgetResult</function>, the application should
4471
respond to a <literal>PGRES_COPY_OUT</literal> result by executing
4472
<function>PQgetline</function> repeatedly, followed by
4473
<function>PQendcopy</function> after the terminator line is seen.
4474
It should then return to the <function>PQgetResult</function> loop
4475
until <function>PQgetResult</function> returns a null pointer.
4476
Similarly a <literal>PGRES_COPY_IN</literal> result is processed
4477
by a series of <function>PQputline</function> calls followed by
4478
<function>PQendcopy</function>, then return to the
4479
<function>PQgetResult</function> loop. This arrangement will
4480
ensure that a <command>COPY</command> command embedded in a series
4481
of <acronym>SQL</acronym> commands will be executed correctly.
4485
Older applications are likely to submit a <command>COPY</command>
4486
via <function>PQexec</function> and assume that the transaction
4487
is done after <function>PQendcopy</function>. This will work
4488
correctly only if the <command>COPY</command> is the only
4489
<acronym>SQL</acronym> command in the command string.
4499
<sect1 id="libpq-control">
4500
<title>Control Functions</title>
4503
These functions control miscellaneous details of <application>libpq</>'s
4510
<function>PQclientEncoding</function>
4512
<primary>PQclientEncoding</primary>
4518
Returns the client encoding.
4520
int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
4523
Note that it returns the encoding ID, not a symbolic string
4524
such as <literal>EUC_JP</literal>. To convert an encoding ID to an encoding name, you
4528
char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
4536
<function>PQsetClientEncoding</function>
4538
<primary>PQsetClientEncoding</primary>
4544
Sets the client encoding.
4546
int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
4549
<replaceable>conn</replaceable> is a connection to the server,
4550
and <replaceable>encoding</replaceable> is the encoding you want to
4551
use. If the function successfully sets the encoding, it returns 0,
4552
otherwise -1. The current encoding for this connection can be
4553
determined by using <function>PQclientEncoding</>.
4560
<function>PQsetErrorVerbosity</function>
4562
<primary>PQsetErrorVerbosity</primary>
4568
Determines the verbosity of messages returned by
4569
<function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
4577
PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
4580
<function>PQsetErrorVerbosity</> sets the verbosity mode, returning
4581
the connection's previous setting. In <firstterm>TERSE</> mode,
4582
returned messages include severity, primary text, and position only;
4583
this will normally fit on a single line. The default mode produces
4584
messages that include the above plus any detail, hint, or context
4585
fields (these might span multiple lines). The <firstterm>VERBOSE</>
4586
mode includes all available fields. Changing the verbosity does not
4587
affect the messages available from already-existing
4588
<structname>PGresult</> objects, only subsequently-created ones.
4595
<function>PQtrace</function>
4597
<primary>PQtrace</primary>
4603
Enables tracing of the client/server communication to a debugging file stream.
4605
void PQtrace(PGconn *conn, FILE *stream);
4611
On Windows, if the <application>libpq</> library and an application are
4612
compiled with different flags, this function call will crash the
4613
application because the internal representation of the <literal>FILE</>
4614
pointers differ. Specifically, multithreaded/single-threaded,
4615
release/debug, and static/dynamic flags should be the same for the
4616
library and all applications using that library.
4625
<function>PQuntrace</function>
4627
<primary>PQuntrace</primary>
4633
Disables tracing started by <function>PQtrace</function>.
4635
void PQuntrace(PGconn *conn);
4644
<sect1 id="libpq-misc">
4645
<title>Miscellaneous Functions</title>
4648
As always, there are some functions that just don't fit anywhere.
4654
<function>PQfreemem</function>
4656
<primary>PQfreemem</primary>
4662
Frees memory allocated by <application>libpq</>.
4664
void PQfreemem(void *ptr);
4669
Frees memory allocated by <application>libpq</>, particularly
4670
<function>PQescapeByteaConn</function>,
4671
<function>PQescapeBytea</function>,
4672
<function>PQunescapeBytea</function>,
4673
and <function>PQnotifies</function>.
4674
It is particularly important that this function, rather than
4675
<function>free()</>, be used on Microsoft Windows. This is because
4676
allocating memory in a DLL and releasing it in the application works
4677
only if multithreaded/single-threaded, release/debug, and static/dynamic
4678
flags are the same for the DLL and the application. On non-Microsoft
4679
Windows platforms, this function is the same as the standard library
4680
function <function>free()</>.
4687
<function>PQconninfoFree</function>
4689
<primary>PQconninfoFree</primary>
4695
Frees the data structures allocated by
4696
<function>PQconndefaults</> or <function>PQconninfoParse</>.
4698
void PQconninfoFree(PQconninfoOption *connOptions);
4703
A simple <function>PQfreemem</function> will not do for this, since
4704
the array contains references to subsidiary strings.
4711
<function>PQencryptPassword</function>
4713
<primary>PQencryptPassword</primary>
4719
Prepares the encrypted form of a <productname>PostgreSQL</> password.
4721
char * PQencryptPassword(const char *passwd, const char *user);
4723
This function is intended to be used by client applications that
4724
wish to send commands like <literal>ALTER USER joe PASSWORD
4725
'pwd'</>. It is good practice not to send the original cleartext
4726
password in such a command, because it might be exposed in command
4727
logs, activity displays, and so on. Instead, use this function to
4728
convert the password to encrypted form before it is sent. The
4729
arguments are the cleartext password, and the SQL name of the user
4730
it is for. The return value is a string allocated by
4731
<function>malloc</function>, or <symbol>NULL</symbol> if out of
4732
memory. The caller can assume the string doesn't contain any
4733
special characters that would require escaping. Use
4734
<function>PQfreemem</> to free the result when done with it.
4741
<function>PQmakeEmptyPGresult</function>
4743
<primary>PQmakeEmptyPGresult</primary>
4749
Constructs an empty <structname>PGresult</structname> object with the given status.
4751
PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
4756
This is <application>libpq</>'s internal function to allocate and
4757
initialize an empty <structname>PGresult</structname> object. This
4758
function returns NULL if memory could not be allocated. It is
4759
exported because some applications find it useful to generate result
4760
objects (particularly objects with error status) themselves. If
4761
<parameter>conn</parameter> is not null and <parameter>status</>
4762
indicates an error, the current error message of the specified
4763
connection is copied into the <structname>PGresult</structname>.
4764
Also, if <parameter>conn</parameter> is not null, any event procedures
4765
registered in the connection are copied into the
4766
<structname>PGresult</structname>. (They do not get
4767
<literal>PGEVT_RESULTCREATE</> calls, but see
4768
<function>PQfireResultCreateEvents</function>.)
4769
Note that <function>PQclear</function> should eventually be called
4770
on the object, just as with a <structname>PGresult</structname>
4771
returned by <application>libpq</application> itself.
4778
<function>PQfireResultCreateEvents</function>
4780
<primary>PQfireResultCreateEvents</primary>
4785
Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
4786
linkend="libpq-events">) for each event procedure registered in the
4787
<structname>PGresult</structname> object. Returns non-zero for success,
4788
zero if any event procedure fails.
4791
int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
4796
The <literal>conn</> argument is passed through to event procedures
4797
but not used directly. It can be <literal>NULL</> if the event
4798
procedures won't use it.
4802
Event procedures that have already received a
4803
<literal>PGEVT_RESULTCREATE</> or <literal>PGEVT_RESULTCOPY</> event
4804
for this object are not fired again.
4808
The main reason that this function is separate from
4809
<function>PQmakeEmptyPGResult</function> is that it is often appropriate
4810
to create a <structname>PGresult</structname> and fill it with data
4811
before invoking the event procedures.
4818
<function>PQcopyResult</function>
4820
<primary>PQcopyResult</primary>
4826
Makes a copy of a <structname>PGresult</structname> object. The copy is
4827
not linked to the source result in any way and
4828
<function>PQclear</function> must be called when the copy is no longer
4829
needed. If the function fails, NULL is returned.
4832
PGresult *PQcopyResult(const PGresult *src, int flags);
4837
This is not intended to make an exact copy. The returned result is
4838
always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
4839
copy any error message in the source. (It does copy the command status
4840
string, however.) The <parameter>flags</parameter> argument determines
4841
what else is copied. It is a bitwise OR of several flags.
4842
<literal>PG_COPYRES_ATTRS</literal> specifies copying the source
4843
result's attributes (column definitions).
4844
<literal>PG_COPYRES_TUPLES</literal> specifies copying the source
4845
result's tuples. (This implies copying the attributes, too.)
4846
<literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
4847
copying the source result's notify hooks.
4848
<literal>PG_COPYRES_EVENTS</literal> specifies copying the source
4849
result's events. (But any instance data associated with the source
4857
<function>PQsetResultAttrs</function>
4859
<primary>PQsetResultAttrs</primary>
4865
Sets the attributes of a <structname>PGresult</structname> object.
4867
int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
4872
The provided <parameter>attDescs</parameter> are copied into the result.
4873
If the <parameter>attDescs</parameter> pointer is NULL or
4874
<parameter>numAttributes</parameter> is less than one, the request is
4875
ignored and the function succeeds. If <parameter>res</parameter>
4876
already contains attributes, the function will fail. If the function
4877
fails, the return value is zero. If the function succeeds, the return
4885
<function>PQsetvalue</function>
4887
<primary>PQsetvalue</primary>
4893
Sets a tuple field value of a <structname>PGresult</structname> object.
4895
int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
4900
The function will automatically grow the result's internal tuples array
4901
as needed. However, the <parameter>tup_num</parameter> argument must be
4902
less than or equal to <function>PQntuples</function>, meaning this
4903
function can only grow the tuples array one tuple at a time. But any
4904
field of any existing tuple can be modified in any order. If a value at
4905
<parameter>field_num</parameter> already exists, it will be overwritten.
4906
If <parameter>len</parameter> is <literal>-1</literal> or
4907
<parameter>value</parameter> is <literal>NULL</literal>, the field value
4908
will be set to an SQL <literal>NULL</literal>. The
4909
<parameter>value</parameter> is copied into the result's private storage,
4910
thus is no longer needed after the function
4911
returns. If the function fails, the return value is zero. If the
4912
function succeeds, the return value is non-zero.
4919
<function>PQresultAlloc</function>
4921
<primary>PQresultAlloc</primary>
4927
Allocate subsidiary storage for a <structname>PGresult</structname> object.
4929
void *PQresultAlloc(PGresult *res, size_t nBytes);
4934
Any memory allocated with this function will be freed when
4935
<parameter>res</parameter> is cleared. If the function fails,
4936
the return value is <literal>NULL</literal>. The result is
4937
guaranteed to be adequately aligned for any type of data,
4938
just as for <function>malloc</>.
4947
<sect1 id="libpq-notice-processing">
4948
<title>Notice Processing</title>
4950
<indexterm zone="libpq-notice-processing">
4951
<primary>notice processing</primary>
4952
<secondary>in libpq</secondary>
4956
Notice and warning messages generated by the server are not returned
4957
by the query execution functions, since they do not imply failure of
4958
the query. Instead they are passed to a notice handling function, and
4959
execution continues normally after the handler returns. The default
4960
notice handling function prints the message on
4961
<filename>stderr</filename>, but the application can override this
4962
behavior by supplying its own handling function.
4966
For historical reasons, there are two levels of notice handling, called
4967
the notice receiver and notice processor. The default behavior is for
4968
the notice receiver to format the notice and pass a string to the notice
4969
processor for printing. However, an application that chooses to provide
4970
its own notice receiver will typically ignore the notice processor
4971
layer and just do all the work in the notice receiver.
4975
The function <function>PQsetNoticeReceiver</function>
4976
<indexterm><primary>notice
4977
receiver</></><indexterm><primary>PQsetNoticeReceiver</></> sets or
4978
examines the current notice receiver for a connection object.
4979
Similarly, <function>PQsetNoticeProcessor</function>
4980
<indexterm><primary>notice
4981
processor</></><indexterm><primary>PQsetNoticeProcessor</></> sets or
4982
examines the current notice processor.
4985
typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
4988
PQsetNoticeReceiver(PGconn *conn,
4989
PQnoticeReceiver proc,
4992
typedef void (*PQnoticeProcessor) (void *arg, const char *message);
4995
PQsetNoticeProcessor(PGconn *conn,
4996
PQnoticeProcessor proc,
5000
Each of these functions returns the previous notice receiver or
5001
processor function pointer, and sets the new value. If you supply a
5002
null function pointer, no action is taken, but the current pointer is
5007
When a notice or warning message is received from the server, or
5008
generated internally by <application>libpq</application>, the notice
5009
receiver function is called. It is passed the message in the form of
5010
a <symbol>PGRES_NONFATAL_ERROR</symbol>
5011
<structname>PGresult</structname>. (This allows the receiver to extract
5012
individual fields using <function>PQresultErrorField</>, or the complete
5013
preformatted message using <function>PQresultErrorMessage</>.) The same
5014
void pointer passed to <function>PQsetNoticeReceiver</function> is also
5015
passed. (This pointer can be used to access application-specific state
5020
The default notice receiver simply extracts the message (using
5021
<function>PQresultErrorMessage</>) and passes it to the notice
5026
The notice processor is responsible for handling a notice or warning
5027
message given in text form. It is passed the string text of the message
5028
(including a trailing newline), plus a void pointer that is the same
5029
one passed to <function>PQsetNoticeProcessor</function>. (This pointer
5030
can be used to access application-specific state if needed.)
5034
The default notice processor is simply:
5037
defaultNoticeProcessor(void *arg, const char *message)
5039
fprintf(stderr, "%s", message);
5045
Once you have set a notice receiver or processor, you should expect
5046
that that function could be called as long as either the
5047
<structname>PGconn</> object or <structname>PGresult</> objects made
5048
from it exist. At creation of a <structname>PGresult</>, the
5049
<structname>PGconn</>'s current notice handling pointers are copied
5050
into the <structname>PGresult</> for possible use by functions like
5051
<function>PQgetvalue</function>.
5056
<sect1 id="libpq-events">
5057
<title>Event System</title>
5060
<application>libpq</application>'s event system is designed to notify
5061
registered event handlers about interesting
5062
<application>libpq</application> events, such as the creation or
5063
destruction of <structname>PGconn</structname> and
5064
<structname>PGresult</structname> objects. A principal use case is that
5065
this allows applications to associate their own data with a
5066
<structname>PGconn</structname> or <structname>PGresult</structname>
5067
and ensure that that data is freed at an appropriate time.
5071
Each registered event handler is associated with two pieces of data,
5072
known to <application>libpq</application> only as opaque <literal>void *</>
5073
pointers. There is a <firstterm>passthrough</> pointer that is provided
5074
by the application when the event handler is registered with a
5075
<structname>PGconn</>. The passthrough pointer never changes for the
5076
life of the <structname>PGconn</> and all <structname>PGresult</>s
5077
generated from it; so if used, it must point to long-lived data.
5078
In addition there is an <firstterm>instance data</> pointer, which starts
5079
out NULL in every <structname>PGconn</> and <structname>PGresult</>.
5080
This pointer can be manipulated using the
5081
<function>PQinstanceData</function>,
5082
<function>PQsetInstanceData</function>,
5083
<function>PQresultInstanceData</function> and
5084
<function>PQsetResultInstanceData</function> functions. Note that
5085
unlike the passthrough pointer, instance data of a <structname>PGconn</>
5086
is not automatically inherited by <structname>PGresult</>s created from
5087
it. <application>libpq</application> does not know what passthrough
5088
and instance data pointers point to (if anything) and will never attempt
5089
to free them — that is the responsibility of the event handler.
5092
<sect2 id="libpq-events-types">
5093
<title>Event Types</title>
5096
The enum <literal>PGEventId</> names the types of events handled by
5097
the event system. All its values have names beginning with
5098
<literal>PGEVT</literal>. For each event type, there is a corresponding
5099
event info structure that carries the parameters passed to the event
5100
handlers. The event types are:
5105
<term><literal>PGEVT_REGISTER</literal></term>
5108
The register event occurs when <function>PQregisterEventProc</function>
5109
is called. It is the ideal time to initialize any
5110
<literal>instanceData</literal> an event procedure may need. Only one
5111
register event will be fired per event handler per connection. If the
5112
event procedure fails, the registration is aborted.
5121
When a <literal>PGEVT_REGISTER</literal> event is received, the
5122
<parameter>evtInfo</parameter> pointer should be cast to a
5123
<structname>PGEventRegister *</structname>. This structure contains a
5124
<structname>PGconn</structname> that should be in the
5125
<literal>CONNECTION_OK</literal> status; guaranteed if one calls
5126
<function>PQregisterEventProc</function> right after obtaining a good
5127
<structname>PGconn</structname>. When returning a failure code, all
5128
cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
5135
<term><literal>PGEVT_CONNRESET</literal></term>
5138
The connection reset event is fired on completion of
5139
<function>PQreset</function> or <function>PQresetPoll</function>. In
5140
both cases, the event is only fired if the reset was successful. If
5141
the event procedure fails, the entire connection reset will fail; the
5142
<structname>PGconn</structname> is put into
5143
<literal>CONNECTION_BAD</literal> status and
5144
<function>PQresetPoll</function> will return
5145
<literal>PGRES_POLLING_FAILED</literal>.
5154
When a <literal>PGEVT_CONNRESET</literal> event is received, the
5155
<parameter>evtInfo</parameter> pointer should be cast to a
5156
<structname>PGEventConnReset *</structname>. Although the contained
5157
<structname>PGconn</structname> was just reset, all event data remains
5158
unchanged. This event should be used to reset/reload/requery any
5159
associated <literal>instanceData</literal>. Note that even if the
5160
event procedure fails to process <literal>PGEVT_CONNRESET</>, it will
5161
still receive a <literal>PGEVT_CONNDESTROY</> event when the connection
5168
<term><literal>PGEVT_CONNDESTROY</literal></term>
5171
The connection destroy event is fired in response to
5172
<function>PQfinish</function>. It is the event procedure's
5173
responsibility to properly clean up its event data as libpq has no
5174
ability to manage this memory. Failure to clean up will lead
5181
} PGEventConnDestroy;
5184
When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
5185
<parameter>evtInfo</parameter> pointer should be cast to a
5186
<structname>PGEventConnDestroy *</structname>. This event is fired
5187
prior to <function>PQfinish</function> performing any other cleanup.
5188
The return value of the event procedure is ignored since there is no
5189
way of indicating a failure from <function>PQfinish</function>. Also,
5190
an event procedure failure should not abort the process of cleaning up
5197
<term><literal>PGEVT_RESULTCREATE</literal></term>
5200
The result creation event is fired in response to any query execution
5201
function that generates a result, including
5202
<function>PQgetResult</function>. This event will only be fired after
5203
the result has been created successfully.
5210
} PGEventResultCreate;
5213
When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
5214
<parameter>evtInfo</parameter> pointer should be cast to a
5215
<structname>PGEventResultCreate *</structname>. The
5216
<parameter>conn</parameter> is the connection used to generate the
5217
result. This is the ideal place to initialize any
5218
<literal>instanceData</literal> that needs to be associated with the
5219
result. If the event procedure fails, the result will be cleared and
5220
the failure will be propagated. The event procedure must not try to
5221
<function>PQclear</> the result object for itself. When returning a
5222
failure code, all cleanup must be performed as no
5223
<literal>PGEVT_RESULTDESTROY</literal> event will be sent.
5229
<term><literal>PGEVT_RESULTCOPY</literal></term>
5232
The result copy event is fired in response to
5233
<function>PQcopyResult</function>. This event will only be fired after
5234
the copy is complete. Only event procedures that have
5235
successfully handled the <literal>PGEVT_RESULTCREATE</literal>
5236
or <literal>PGEVT_RESULTCOPY</literal> event for the source result
5237
will receive <literal>PGEVT_RESULTCOPY</literal> events.
5242
const PGresult *src;
5244
} PGEventResultCopy;
5247
When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
5248
<parameter>evtInfo</parameter> pointer should be cast to a
5249
<structname>PGEventResultCopy *</structname>. The
5250
<parameter>src</parameter> result is what was copied while the
5251
<parameter>dest</parameter> result is the copy destination. This event
5252
can be used to provide a deep copy of <literal>instanceData</literal>,
5253
since <literal>PQcopyResult</literal> cannot do that. If the event
5254
procedure fails, the entire copy operation will fail and the
5255
<parameter>dest</parameter> result will be cleared. When returning a
5256
failure code, all cleanup must be performed as no
5257
<literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
5264
<term><literal>PGEVT_RESULTDESTROY</literal></term>
5267
The result destroy event is fired in response to a
5268
<function>PQclear</function>. It is the event procedure's
5269
responsibility to properly clean up its event data as libpq has no
5270
ability to manage this memory. Failure to clean up will lead
5277
} PGEventResultDestroy;
5280
When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
5281
<parameter>evtInfo</parameter> pointer should be cast to a
5282
<structname>PGEventResultDestroy *</structname>. This event is fired
5283
prior to <function>PQclear</function> performing any other cleanup.
5284
The return value of the event procedure is ignored since there is no
5285
way of indicating a failure from <function>PQclear</function>. Also,
5286
an event procedure failure should not abort the process of cleaning up
5294
<sect2 id="libpq-events-proc">
5295
<title>Event Callback Procedure</title>
5300
<literal>PGEventProc</literal>
5302
<primary>PGEventProc</primary>
5308
<literal>PGEventProc</literal> is a typedef for a pointer to an
5309
event procedure, that is, the user callback function that receives
5310
events from libpq. The signature of an event procedure must be
5313
int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
5316
The <parameter>evtId</parameter> parameter indicates which
5317
<literal>PGEVT</literal> event occurred. The
5318
<parameter>evtInfo</parameter> pointer must be cast to the appropriate
5319
structure type to obtain further information about the event.
5320
The <parameter>passThrough</parameter> parameter is the pointer
5321
provided to <function>PQregisterEventProc</function> when the event
5322
procedure was registered. The function should return a non-zero value
5323
if it succeeds and zero if it fails.
5327
A particular event procedure can be registered only once in any
5328
<structname>PGconn</>. This is because the address of the procedure
5329
is used as a lookup key to identify the associated instance data.
5334
On Windows, functions can have two different addresses: one visible
5335
from outside a DLL and another visible from inside the DLL. One
5336
should be careful that only one of these addresses is used with
5337
<application>libpq</>'s event-procedure functions, else confusion will
5338
result. The simplest rule for writing code that will work is to
5339
ensure that event procedures are declared <literal>static</>. If the
5340
procedure's address must be available outside its own source file,
5341
expose a separate function to return the address.
5349
<sect2 id="libpq-events-funcs">
5350
<title>Event Support Functions</title>
5355
<function>PQregisterEventProc</function>
5357
<primary>PQregisterEventProc</primary>
5363
Registers an event callback procedure with libpq.
5366
int PQregisterEventProc(PGconn *conn, PGEventProc proc,
5367
const char *name, void *passThrough);
5372
An event procedure must be registered once on each
5373
<structname>PGconn</> you want to receive events about. There is no
5374
limit, other than memory, on the number of event procedures that
5375
can be registered with a connection. The function returns a non-zero
5376
value if it succeeds and zero if it fails.
5380
The <parameter>proc</parameter> argument will be called when a libpq
5381
event is fired. Its memory address is also used to lookup
5382
<literal>instanceData</literal>. The <parameter>name</parameter>
5383
argument is used to refer to the event procedure in error messages.
5384
This value cannot be NULL or a zero-length string. The name string is
5385
copied into the <structname>PGconn</>, so what is passed need not be
5386
long-lived. The <parameter>passThrough</parameter> pointer is passed
5387
to the <parameter>proc</parameter> whenever an event occurs. This
5388
argument can be NULL.
5395
<function>PQsetInstanceData</function>
5397
<primary>PQsetInstanceData</primary>
5402
Sets the conn's instanceData for proc to data. This returns non-zero
5403
for success and zero for failure. (Failure is only possible if
5404
the proc has not been properly registered in the conn.)
5407
int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
5415
<function>PQinstanceData</function>
5417
<primary>PQinstanceData</primary>
5422
Returns the conn's instanceData associated with proc, or NULL
5426
void *PQinstanceData(const PGconn *conn, PGEventProc proc);
5434
<function>PQresultSetInstanceData</function>
5436
<primary>PQresultSetInstanceData</primary>
5441
Sets the result's instanceData for proc to data. This returns non-zero
5442
for success and zero for failure. (Failure is only possible if the
5443
proc has not been properly registered in the result.)
5446
int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
5454
<function>PQresultInstanceData</function>
5456
<primary>PQresultInstanceData</primary>
5461
Returns the result's instanceData associated with proc, or NULL
5465
void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
5473
<sect2 id="libpq-events-example">
5474
<title>Event Example</title>
5477
Here is a skeleton example of managing private data associated with
5478
libpq connections and results.
5483
/* required header for libpq events (note: includes libpq-fe.h) */
5484
#include <libpq-events.h>
5486
/* The instanceData */
5494
static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);
5501
PGconn *conn = PQconnectdb("dbname = postgres");
5503
if (PQstatus(conn) != CONNECTION_OK)
5505
fprintf(stderr, "Connection to database failed: %s",
5506
PQerrorMessage(conn));
5511
/* called once on any connection that should receive events.
5512
* Sends a PGEVT_REGISTER to myEventProc.
5514
if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
5516
fprintf(stderr, "Cannot register PGEventProc\n");
5521
/* conn instanceData is available */
5522
data = PQinstanceData(conn, myEventProc);
5524
/* Sends a PGEVT_RESULTCREATE to myEventProc */
5525
res = PQexec(conn, "SELECT 1 + 1");
5527
/* result instanceData is available */
5528
data = PQresultInstanceData(res, myEventProc);
5530
/* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
5531
res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);
5533
/* result instanceData is available if PG_COPYRES_EVENTS was
5534
* used during the PQcopyResult call.
5536
data = PQresultInstanceData(res_copy, myEventProc);
5538
/* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
5542
/* Sends a PGEVT_CONNDESTROY to myEventProc */
5549
myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
5553
case PGEVT_REGISTER:
5555
PGEventRegister *e = (PGEventRegister *)evtInfo;
5556
mydata *data = get_mydata(e->conn);
5558
/* associate app specific data with connection */
5559
PQsetInstanceData(e->conn, myEventProc, data);
5563
case PGEVT_CONNRESET:
5565
PGEventConnReset *e = (PGEventConnReset *)evtInfo;
5566
mydata *data = PQinstanceData(e->conn, myEventProc);
5569
memset(data, 0, sizeof(mydata));
5573
case PGEVT_CONNDESTROY:
5575
PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
5576
mydata *data = PQinstanceData(e->conn, myEventProc);
5578
/* free instance data because the conn is being destroyed */
5584
case PGEVT_RESULTCREATE:
5586
PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
5587
mydata *conn_data = PQinstanceData(e->conn, myEventProc);
5588
mydata *res_data = dup_mydata(conn_data);
5590
/* associate app specific data with result (copy it from conn) */
5591
PQsetResultInstanceData(e->result, myEventProc, res_data);
5595
case PGEVT_RESULTCOPY:
5597
PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
5598
mydata *src_data = PQresultInstanceData(e->src, myEventProc);
5599
mydata *dest_data = dup_mydata(src_data);
5601
/* associate app specific data with result (copy it from a result) */
5602
PQsetResultInstanceData(e->dest, myEventProc, dest_data);
5606
case PGEVT_RESULTDESTROY:
5608
PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
5609
mydata *data = PQresultInstanceData(e->result, myEventProc);
5611
/* free instance data because the result is being destroyed */
5617
/* unknown event id, just return TRUE. */
5622
return TRUE; /* event processing succeeded */
5629
<sect1 id="libpq-envars">
5630
<title>Environment Variables</title>
5632
<indexterm zone="libpq-envars">
5633
<primary>environment variable</primary>
5637
The following environment variables can be used to select default
5638
connection parameter values, which will be used by
5639
<function>PQconnectdb</>, <function>PQsetdbLogin</> and
5640
<function>PQsetdb</> if no value is directly specified by the calling
5641
code. These are useful to avoid hard-coding database connection
5642
information into simple client applications, for example.
5648
<primary><envar>PGHOST</envar></primary>
5650
<envar>PGHOST</envar> behaves the same as <xref
5651
linkend="libpq-connect-host"> connection parameter.
5658
<primary><envar>PGHOSTADDR</envar></primary>
5660
<envar>PGHOSTADDR</envar> behaves the same as <xref
5661
linkend="libpq-connect-hostaddr"> connection parameter.
5662
This can be set instead of or in addition to <envar>PGHOST</envar>
5663
to avoid DNS lookup overhead.
5670
<primary><envar>PGPORT</envar></primary>
5672
<envar>PGPORT</envar> behaves the same as <xref
5673
linkend="libpq-connect-port"> connection parameter.
5680
<primary><envar>PGDATABASE</envar></primary>
5682
<envar>PGDATABASE</envar> behaves the same as <xref
5683
linkend="libpq-connect-dbname"> connection parameter.
5690
<primary><envar>PGUSER</envar></primary>
5692
<envar>PGUSER</envar> behaves the same as <xref
5693
linkend="libpq-connect-user"> connection parameter.
5701
<primary><envar>PGPASSWORD</envar></primary>
5703
<envar>PGPASSWORD</envar> behaves the same as <xref
5704
linkend="libpq-connect-password"> connection parameter.
5705
Use of this environment variable
5706
is not recommended for security reasons (some operating systems
5707
allow non-root users to see process environment variables via
5708
<application>ps</>); instead consider using the
5709
<filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
5716
<primary><envar>PGPASSFILE</envar></primary>
5718
<envar>PGPASSFILE</envar> specifies the name of the password file to
5719
use for lookups. If not set, it defaults to <filename>~/.pgpass</>
5720
(see <xref linkend="libpq-pgpass">).
5727
<primary><envar>PGSERVICE</envar></primary>
5729
<envar>PGSERVICE</envar> behaves the same as <xref
5730
linkend="libpq-connect-service"> connection parameter.
5737
<primary><envar>PGREALM</envar></primary>
5739
<envar>PGREALM</envar> sets the Kerberos realm to use with
5740
<productname>PostgreSQL</productname>, if it is different from the
5741
local realm. If <envar>PGREALM</envar> is set,
5742
<application>libpq</application> applications will attempt
5743
authentication with servers for this realm and use separate ticket
5744
files to avoid conflicts with local ticket files. This
5745
environment variable is only used if Kerberos authentication is
5746
selected by the server.
5753
<primary><envar>PGOPTIONS</envar></primary>
5755
<envar>PGOPTIONS</envar> behaves the same as <xref
5756
linkend="libpq-connect-options"> connection parameter.
5763
<primary><envar>PGSSLMODE</envar></primary>
5765
<envar>PGSSLMODE</envar> behaves the same as <xref
5766
linkend="libpq-connect-sslmode"> connection parameter.
5773
<primary><envar>PGSSLVERIFY</envar></primary>
5775
<envar>PGSSLVERIFY</envar> behaves the same as <xref
5776
linkend="libpq-connect-sslverify"> connection parameter.
5783
<primary><envar>PGREQUIRESSL</envar></primary>
5785
<envar>PGREQUIRESSL</envar> behaves the same as <xref
5786
linkend="libpq-connect-requiressl"> connection parameter.
5793
<primary><envar>PGSSLCERT</envar></primary>
5795
<envar>PGSSLCERT</envar> behaves the same as <xref
5796
linkend="libpq-connect-sslcert"> connection parameter.
5803
<primary><envar>PGSSLKEY</envar></primary>
5805
<envar>PGSSLKEY</envar> behaves the same as <xref
5806
linkend="libpq-connect-sslkey"> connection parameter.
5813
<primary><envar>PGSSLROOTCERT</envar></primary>
5815
<envar>PGSSLROOTCERT</envar> behaves the same as <xref
5816
linkend="libpq-connect-sslrootcert"> connection parameter.
5823
<primary><envar>PGSSLCRL</envar></primary>
5825
<envar>PGSSLCRL</envar> behaves the same as <xref
5826
linkend="libpq-connect-sslcrl"> connection parameter.
5833
<primary><envar>PGKRBSRVNAME</envar></primary>
5835
<envar>PGKRBSRVNAME</envar> behaves the same as <xref
5836
linkend="libpq-connect-krbsrvname"> connection parameter.
5843
<primary><envar>PGGSSLIB</envar></primary>
5845
<envar>PGGSSLIB</envar> behaves the same as <xref
5846
linkend="libpq-connect-gsslib"> connection parameter.
5853
<primary><envar>PGCONNECT_TIMEOUT</envar></primary>
5855
<envar>PGCONNECT_TIMEOUT</envar> behaves the same as <xref
5856
linkend="libpq-connect-connect-timeout"> connection parameter.
5863
The following environment variables can be used to specify default
5864
behavior for each <productname>PostgreSQL</productname> session. (See
5865
also the <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
5866
and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">
5867
commands for ways to set default behavior on a per-user or per-database
5874
<primary><envar>PGDATESTYLE</envar></primary>
5876
<envar>PGDATESTYLE</envar> sets the default style of date/time
5877
representation. (Equivalent to <literal>SET datestyle TO
5885
<primary><envar>PGTZ</envar></primary>
5887
<envar>PGTZ</envar> sets the default time zone. (Equivalent to
5888
<literal>SET timezone TO ...</literal>.)
5895
<primary><envar>PGCLIENTENCODING</envar></primary>
5897
<envar>PGCLIENTENCODING</envar> sets the default client character
5898
set encoding. (Equivalent to <literal>SET client_encoding TO
5906
<primary><envar>PGGEQO</envar></primary>
5908
<envar>PGGEQO</envar> sets the default mode for the genetic query
5909
optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
5914
Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set"
5915
endterm="sql-set-title"> for information on correct values for these
5916
environment variables.
5920
The following environment variables determine internal behavior of
5921
<application>libpq</application>; they override compiled-in defaults.
5927
<primary><envar>PGSYSCONFDIR</envar></primary>
5929
<envar>PGSYSCONFDIR</envar> sets the directory containing the
5930
<filename>pg_service.conf</> file.
5937
<primary><envar>PGLOCALEDIR</envar></primary>
5939
<envar>PGLOCALEDIR</envar> sets the directory containing the
5940
<literal>locale</> files for message internationalization.
5949
<sect1 id="libpq-pgpass">
5950
<title>The Password File</title>
5952
<indexterm zone="libpq-pgpass">
5953
<primary>password file</primary>
5955
<indexterm zone="libpq-pgpass">
5956
<primary>.pgpass</primary>
5960
The file <filename>.pgpass</filename> in a user's home directory or the
5961
file referenced by <envar>PGPASSFILE</envar> can contain passwords to
5962
be used if the connection requires a password (and no password has been
5963
specified otherwise). On Microsoft Windows the file is named
5964
<filename>%APPDATA%\postgresql\pgpass.conf</> (where
5965
<filename>%APPDATA%</> refers to the Application Data subdirectory in
5966
the user's profile).
5970
This file should contain lines of the following format:
5972
<replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
5974
Each of the first four fields can be a literal value, or
5975
<literal>*</literal>, which matches anything. The password field from
5976
the first line that matches the current connection parameters will be
5977
used. (Therefore, put more-specific entries first when you are using
5978
wildcards.) If an entry needs to contain <literal>:</literal> or
5979
<literal>\</literal>, escape this character with <literal>\</literal>.
5980
A host name of <literal>localhost</> matches both TCP (host name
5981
<literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
5982
or the default socket directory) connections coming from the local
5987
On Unix systems, the permissions on <filename>.pgpass</filename> must
5988
disallow any access to world or group; achieve this by the command
5989
<command>chmod 0600 ~/.pgpass</command>. If the permissions are less
5990
strict than this, the file will be ignored. On Microsoft Windows, it
5991
is assumed that the file is stored in a directory that is secure, so
5992
no special permissions check is made.
5997
<sect1 id="libpq-pgservice">
5998
<title>The Connection Service File</title>
6000
<indexterm zone="libpq-pgservice">
6001
<primary>connection service file</primary>
6003
<indexterm zone="libpq-pgservice">
6004
<primary>pg_service.conf</primary>
6008
The connection service file allows libpq connection parameters to be
6009
associated with a single service name. That service name can then be
6010
specified by a libpq connection, and the associated settings will be
6011
used. This allows connection parameters to be modified without requiring
6012
a recompile of the libpq application. The service name can also be
6013
specified using the <envar>PGSERVICE</envar> environment variable.
6017
To use this feature, copy
6018
<filename>share/pg_service.conf.sample</filename> to
6019
<filename>etc/pg_service.conf</filename> and edit the file to add
6020
service names and parameters. This file can be used for client-only
6021
installs too. The file's location can also be specified by the
6022
<envar>PGSYSCONFDIR</envar> environment variable.
6027
<sect1 id="libpq-ldap">
6028
<title>LDAP Lookup of Connection Parameters</title>
6030
<indexterm zone="libpq-ldap">
6031
<primary>LDAP connection parameter lookup</primary>
6035
If <application>libpq</application> has been compiled with LDAP support (option
6036
<literal><option>--with-ldap</option></literal> for <command>configure</command>)
6037
it is possible to retrieve connection options like <literal>host</literal>
6038
or <literal>dbname</literal> via LDAP from a central server.
6039
The advantage is that if the connection parameters for a database change,
6040
the connection information doesn't have to be updated on all client machines.
6044
LDAP connection parameter lookup uses the connection service file
6045
<filename>pg_service.conf</filename> (see <xref
6046
linkend="libpq-pgservice">). A line in a
6047
<filename>pg_service.conf</filename> stanza that starts with
6048
<literal>ldap://</literal> will be recognized as an LDAP URL and an
6049
LDAP query will be performed. The result must be a list of
6050
<literal>keyword = value</literal> pairs which will be used to set
6051
connection options. The URL must conform to RFC 1959 and be of the
6054
ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
6056
where <replaceable>hostname</replaceable> defaults to
6057
<literal>localhost</literal> and <replaceable>port</replaceable>
6062
Processing of <filename>pg_service.conf</filename> is terminated after
6063
a successful LDAP lookup, but is continued if the LDAP server cannot
6064
be contacted. This is to provide a fallback with further LDAP URL
6065
lines that point to different LDAP servers, classical <literal>keyword
6066
= value</literal> pairs, or default connection options. If you would
6067
rather get an error message in this case, add a syntactically incorrect
6068
line after the LDAP URL.
6072
A sample LDAP entry that has been created with the LDIF file
6075
dn:cn=mydatabase,dc=mycompany,dc=com
6078
objectclass:groupOfUniqueNames
6080
uniqueMember:host=dbserver.mycompany.com
6081
uniqueMember:port=5439
6082
uniqueMember:dbname=mydb
6083
uniqueMember:user=mydb_user
6084
uniqueMember:sslmode=require
6086
might be queried with the following LDAP URL:
6088
ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)
6093
You can also mix regular service file entries with LDAP lookups.
6094
A complete example for a stanza in <filename>pg_service.conf</filename>
6097
# only host and port are stored in LDAP, specify dbname and user explicitly
6101
ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
6108
<sect1 id="libpq-ssl">
6109
<title>SSL Support</title>
6111
<indexterm zone="libpq-ssl">
6112
<primary>SSL</primary>
6116
<productname>PostgreSQL</> has native support for using <acronym>SSL</>
6117
connections to encrypt client/server communications for increased
6118
security. See <xref linkend="ssl-tcp"> for details about the server-side
6119
<acronym>SSL</> functionality.
6123
<application>libpq</application> reads the system-wide
6124
<productname>OpenSSL</productname> configuration file. By default, this
6125
file is named <filename>openssl.cnf</filename> and is located in the
6126
directory reported by <literal>openssl version -d</>. This default
6127
can be overridden by setting environment variable
6128
<envar>OPENSSL_CONF</envar> to the name of the desired configuration
6133
When the <literal>sslverify</> parameter is set to <literal>cn</> or
6134
<literal>cert</>, libpq will verify that the server certificate is
6135
trustworthy by checking the certificate chain up to a <acronym>CA</>.
6136
For this to work, place the certificate of a trusted <acronym>CA</>
6137
in the file <filename>~/.postgresql/root.crt</> in the user's home directory.
6138
(On Microsoft Windows the file is named
6139
<filename>%APPDATA%\postgresql\root.crt</filename>.)
6140
<application>libpq</application> will then verify that the server's
6141
certificate is signed by one of the trusted certificate authorities.
6142
The SSL connection will fail if the server does not present a trusted
6143
certificate. Certificate Revocation List (CRL) entries are also checked
6144
if the file <filename>~/.postgresql/root.crl</filename> exists
6145
(<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
6147
The location of the root certificate store and the CRL can be overridden
6148
by the connection parameters <literal>sslrootcert</> and <literal>sslcrl</>
6149
or the environment variables <envar>PGSSLROOTCERT</> and <envar>PGSSLCRL</>.
6153
If the server requests a trusted client certificate,
6154
<application>libpq</application> will send the certificate stored in
6155
file <filename>~/.postgresql/postgresql.crt</> in the user's home
6156
directory. The certificate must be signed by one of the certificate
6157
authorities (<acronym>CA</acronym>) trusted by the server. A matching
6158
private key file <filename>~/.postgresql/postgresql.key</> must also
6159
be present. The private
6160
key file must not allow any access to world or group; achieve this by the
6161
command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
6162
On Microsoft Windows these files are named
6163
<filename>%APPDATA%\postgresql\postgresql.crt</filename> and
6164
<filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
6165
is no special permissions check since the directory is presumed secure.
6166
The location of the certificate and key files can be overridden by the
6167
connection parameters <literal>sslcert</> and <literal>sslkey</> or the
6168
environment variables <envar>PGSSLCERT</> and <envar>PGSSLKEY</>.
6172
If your application initializes <literal>libssl</> or
6173
<literal>libcrypto</> libraries and <application>libpq</application>
6174
is built with <acronym>SSL</> support, you should call
6175
<function>PQinitSSL(0)</> to tell <application>libpq</application>
6176
that the <literal>libssl</> and <literal>libcrypto</> libraries
6177
have been initialized by your application so
6178
<application>libpq</application> will not initialize those libraries.
6179
<!-- If this URL changes replace it with a URL to www.archive.org. -->
6181
url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
6182
for details on the SSL API.
6185
<table id="libpq-ssl-file-usage">
6186
<title>Libpq/Client SSL File Usage</title>
6191
<entry>Contents</entry>
6192
<entry>Effect</entry>
6199
<entry><filename>~/.postgresql/postgresql.crt</></entry>
6200
<entry>client certificate</entry>
6201
<entry>requested by server</entry>
6205
<entry><filename>~/.postgresql/postgresql.key</></entry>
6206
<entry>client private key</entry>
6207
<entry>proves client certificate sent by owner; does not indicate
6208
certificate owner is trustworthy</entry>
6212
<entry><filename>~/.postgresql/root.crt</></entry>
6213
<entry>trusted certificate authorities</entry>
6214
<entry>checks server certificate is signed by a trusted certificate
6219
<entry><filename>~/.postgresql/root.crl</></entry>
6220
<entry>certificates revoked by certificate authorities</entry>
6221
<entry>server certificate must not be on this list</entry>
6231
<sect1 id="libpq-threading">
6232
<title>Behavior in Threaded Programs</title>
6234
<indexterm zone="libpq-threading">
6235
<primary>threads</primary>
6236
<secondary>with libpq</secondary>
6240
<application>libpq</application> is reentrant and thread-safe if the
6241
<filename>configure</filename> command-line option
6242
<literal>--enable-thread-safety</> was used when the
6243
<productname>PostgreSQL</productname> distribution was built. In
6244
addition, you might need to use additional compiler command-line
6245
options when you compile your application code. Refer to your
6246
system's documentation for information about how to build
6247
thread-enabled applications, or look in
6248
<filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
6249
and <literal>PTHREAD_LIBS</>. This function allows the querying of
6250
<application>libpq</application>'s thread-safe status:
6256
<function>PQisthreadsafe</function>
6258
<primary>PQisthreadsafe</primary>
6264
Returns the thread safety status of the
6265
<application>libpq</application> library.
6267
int PQisthreadsafe();
6272
Returns 1 if the <application>libpq</application> is thread-safe
6280
One thread restriction is that no two threads attempt to manipulate
6281
the same <structname>PGconn</> object at the same time. In particular,
6282
you cannot issue concurrent commands from different threads through
6283
the same connection object. (If you need to run concurrent commands,
6284
use multiple connections.)
6288
<structname>PGresult</> objects are read-only after creation, and so
6289
can be passed around freely between threads.
6293
The deprecated functions <function>PQrequestCancel</function> and
6294
<function>PQoidStatus</function> are not thread-safe and should not be
6295
used in multithread programs. <function>PQrequestCancel</function>
6296
can be replaced by <function>PQcancel</function>.
6297
<function>PQoidStatus</function> can be replaced by
6298
<function>PQoidValue</function>.
6302
If you are using Kerberos inside your application (in addition to inside
6303
<application>libpq</application>), you will need to do locking around
6304
Kerberos calls because Kerberos functions are not thread-safe. See
6305
function <function>PQregisterThreadLock</> in the
6306
<application>libpq</application> source code for a way to do cooperative
6307
locking between <application>libpq</application> and your application.
6311
If you experience problems with threaded applications, run the program
6312
in <filename>src/tools/thread</> to see if your platform has
6313
thread-unsafe functions. This program is run by
6314
<filename>configure</filename>, but for binary distributions your
6315
library might not match the library used to build the binaries.
6320
<sect1 id="libpq-build">
6321
<title>Building <application>libpq</application> Programs</title>
6323
<indexterm zone="libpq-build">
6324
<primary>compiling</primary>
6325
<secondary>libpq applications</secondary>
6329
To build (i.e., compile and link) a program using
6330
<application>libpq</application> you need to do all of the following
6336
Include the <filename>libpq-fe.h</filename> header file:
6338
#include <libpq-fe.h>
6340
If you failed to do that then you will normally get error messages
6341
from your compiler similar to
6343
foo.c: In function `main':
6344
foo.c:34: `PGconn' undeclared (first use in this function)
6345
foo.c:35: `PGresult' undeclared (first use in this function)
6346
foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
6347
foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
6348
foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
6355
Point your compiler to the directory where the <productname>PostgreSQL</> header
6356
files were installed, by supplying the
6357
<literal>-I<replaceable>directory</replaceable></literal> option
6358
to your compiler. (In some cases the compiler will look into
6359
the directory in question by default, so you can omit this
6360
option.) For instance, your compile command line could look
6363
cc -c -I/usr/local/pgsql/include testprog.c
6365
If you are using makefiles then add the option to the
6366
<varname>CPPFLAGS</varname> variable:
6368
CPPFLAGS += -I/usr/local/pgsql/include
6373
If there is any chance that your program might be compiled by
6374
other users then you should not hardcode the directory location
6375
like that. Instead, you can run the utility
6376
<command>pg_config</command><indexterm><primary>pg_config</><secondary
6377
sortas="libpq">with libpq</></> to find out where the header
6378
files are on the local system:
6380
<prompt>$</prompt> pg_config --includedir
6381
<computeroutput>/usr/local/include</computeroutput>
6386
Failure to specify the correct option to the compiler will
6387
result in an error message such as
6389
testlibpq.c:8:22: libpq-fe.h: No such file or directory
6396
When linking the final program, specify the option
6397
<literal>-lpq</literal> so that the <application>libpq</application>
6398
library gets pulled in, as well as the option
6399
<literal>-L<replaceable>directory</replaceable></literal> to point
6400
the compiler to the directory where the
6401
<application>libpq</application> library resides. (Again, the
6402
compiler will search some directories by default.) For maximum
6403
portability, put the <option>-L</option> option before the
6404
<option>-lpq</option> option. For example:
6406
cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
6411
You can find out the library directory using
6412
<command>pg_config</command> as well:
6414
<prompt>$</prompt> pg_config --libdir
6415
<computeroutput>/usr/local/pgsql/lib</computeroutput>
6420
Error messages that point to problems in this area could look like
6423
testlibpq.o: In function `main':
6424
testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
6425
testlibpq.o(.text+0x71): undefined reference to `PQstatus'
6426
testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
6428
This means you forgot <option>-lpq</option>.
6430
/usr/bin/ld: cannot find -lpq
6432
This means you forgot the <option>-L</option> option or did not
6433
specify the right directory.
6442
<sect1 id="libpq-example">
6443
<title>Example Programs</title>
6446
These examples and others can be found in the
6447
directory <filename>src/test/examples</filename> in the source code
6451
<example id="libpq-example-1">
6452
<title><application>libpq</application> Example Program 1</title>
6459
* Test the C version of libpq, the PostgreSQL frontend library.
6463
#include "libpq-fe.h"
6466
exit_nicely(PGconn *conn)
6473
main(int argc, char **argv)
6475
const char *conninfo;
6483
* If the user supplies a parameter on the command line, use it as the
6484
* conninfo string; otherwise default to setting dbname=postgres and using
6485
* environment variables or defaults for all other connection parameters.
6490
conninfo = "dbname = postgres";
6492
/* Make a connection to the database */
6493
conn = PQconnectdb(conninfo);
6495
/* Check to see that the backend connection was successfully made */
6496
if (PQstatus(conn) != CONNECTION_OK)
6498
fprintf(stderr, "Connection to database failed: %s",
6499
PQerrorMessage(conn));
6504
* Our test case here involves using a cursor, for which we must be inside
6505
* a transaction block. We could do the whole thing with a single
6506
* PQexec() of "select * from pg_database", but that's too trivial to make
6510
/* Start a transaction block */
6511
res = PQexec(conn, "BEGIN");
6512
if (PQresultStatus(res) != PGRES_COMMAND_OK)
6514
fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
6520
* Should PQclear PGresult whenever it is no longer needed to avoid memory
6526
* Fetch rows from pg_database, the system catalog of databases
6528
res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
6529
if (PQresultStatus(res) != PGRES_COMMAND_OK)
6531
fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
6537
res = PQexec(conn, "FETCH ALL in myportal");
6538
if (PQresultStatus(res) != PGRES_TUPLES_OK)
6540
fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
6545
/* first, print out the attribute names */
6546
nFields = PQnfields(res);
6547
for (i = 0; i < nFields; i++)
6548
printf("%-15s", PQfname(res, i));
6551
/* next, print out the rows */
6552
for (i = 0; i < PQntuples(res); i++)
6554
for (j = 0; j < nFields; j++)
6555
printf("%-15s", PQgetvalue(res, i, j));
6561
/* close the portal ... we don't bother to check for errors ... */
6562
res = PQexec(conn, "CLOSE myportal");
6565
/* end the transaction */
6566
res = PQexec(conn, "END");
6569
/* close the connection to the database and cleanup */
6578
<example id="libpq-example-2">
6579
<title><application>libpq</application> Example Program 2</title>
6585
* Test of the asynchronous notification interface
6587
* Start this program, then from psql in another window do
6589
* Repeat four times to get this program to exit.
6591
* Or, if you want to get fancy, try this:
6592
* populate a database with the following commands
6593
* (provided in src/test/examples/testlibpq2.sql):
6595
* CREATE TABLE TBL1 (i int4);
6597
* CREATE TABLE TBL2 (i int4);
6599
* CREATE RULE r1 AS ON INSERT TO TBL1 DO
6600
* (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
6602
* and do this four times:
6604
* INSERT INTO TBL1 VALUES (10);
6610
#include <sys/time.h>
6611
#include "libpq-fe.h"
6614
exit_nicely(PGconn *conn)
6621
main(int argc, char **argv)
6623
const char *conninfo;
6630
* If the user supplies a parameter on the command line, use it as the
6631
* conninfo string; otherwise default to setting dbname=postgres and using
6632
* environment variables or defaults for all other connection parameters.
6637
conninfo = "dbname = postgres";
6639
/* Make a connection to the database */
6640
conn = PQconnectdb(conninfo);
6642
/* Check to see that the backend connection was successfully made */
6643
if (PQstatus(conn) != CONNECTION_OK)
6645
fprintf(stderr, "Connection to database failed: %s",
6646
PQerrorMessage(conn));
6651
* Issue LISTEN command to enable notifications from the rule's NOTIFY.
6653
res = PQexec(conn, "LISTEN TBL2");
6654
if (PQresultStatus(res) != PGRES_COMMAND_OK)
6656
fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
6662
* should PQclear PGresult whenever it is no longer needed to avoid memory
6667
/* Quit after four notifies are received. */
6669
while (nnotifies < 4)
6672
* Sleep until something happens on the connection. We use select(2)
6673
* to wait for input, but you could also use poll() or similar
6679
sock = PQsocket(conn);
6682
break; /* shouldn't happen */
6684
FD_ZERO(&input_mask);
6685
FD_SET(sock, &input_mask);
6687
if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
6689
fprintf(stderr, "select() failed: %s\n", strerror(errno));
6693
/* Now check for input */
6694
PQconsumeInput(conn);
6695
while ((notify = PQnotifies(conn)) != NULL)
6698
"ASYNC NOTIFY of '%s' received from backend pid %d\n",
6699
notify->relname, notify->be_pid);
6705
fprintf(stderr, "Done.\n");
6707
/* close the connection to the database and cleanup */
6716
<example id="libpq-example-3">
6717
<title><application>libpq</application> Example Program 3</>
6723
* Test out-of-line parameters and binary I/O.
6725
* Before running this, populate a database with the following commands
6726
* (provided in src/test/examples/testlibpq3.sql):
6728
* CREATE TABLE test1 (i int4, t text, b bytea);
6730
* INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
6731
* INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
6733
* The expected output is:
6737
* t = (11 bytes) 'joe's place'
6738
* b = (5 bytes) \000\001\002\003\004
6742
* t = (8 bytes) 'ho there'
6743
* b = (5 bytes) \004\003\002\001\000
6748
#include <sys/types.h>
6749
#include "libpq-fe.h"
6751
/* for ntohl/htonl */
6752
#include <netinet/in.h>
6753
#include <arpa/inet.h>
6757
exit_nicely(PGconn *conn)
6764
* This function prints a query result that is a binary-format fetch from
6765
* a table defined as in the comment above. We split it out because the
6766
* main() function uses it twice.
6769
show_binary_results(PGresult *res)
6777
/* Use PQfnumber to avoid assumptions about field order in result */
6778
i_fnum = PQfnumber(res, "i");
6779
t_fnum = PQfnumber(res, "t");
6780
b_fnum = PQfnumber(res, "b");
6782
for (i = 0; i < PQntuples(res); i++)
6790
/* Get the field values (we ignore possibility they are null!) */
6791
iptr = PQgetvalue(res, i, i_fnum);
6792
tptr = PQgetvalue(res, i, t_fnum);
6793
bptr = PQgetvalue(res, i, b_fnum);
6796
* The binary representation of INT4 is in network byte order, which
6797
* we'd better coerce to the local byte order.
6799
ival = ntohl(*((uint32_t *) iptr));
6802
* The binary representation of TEXT is, well, text, and since libpq
6803
* was nice enough to append a zero byte to it, it'll work just fine
6806
* The binary representation of BYTEA is a bunch of bytes, which could
6807
* include embedded nulls so we have to pay attention to field length.
6809
blen = PQgetlength(res, i, b_fnum);
6811
printf("tuple %d: got\n", i);
6812
printf(" i = (%d bytes) %d\n",
6813
PQgetlength(res, i, i_fnum), ival);
6814
printf(" t = (%d bytes) '%s'\n",
6815
PQgetlength(res, i, t_fnum), tptr);
6816
printf(" b = (%d bytes) ", blen);
6817
for (j = 0; j < blen; j++)
6818
printf("\\%03o", bptr[j]);
6824
main(int argc, char **argv)
6826
const char *conninfo;
6829
const char *paramValues[1];
6830
int paramLengths[1];
6831
int paramFormats[1];
6832
uint32_t binaryIntVal;
6835
* If the user supplies a parameter on the command line, use it as the
6836
* conninfo string; otherwise default to setting dbname=postgres and using
6837
* environment variables or defaults for all other connection parameters.
6842
conninfo = "dbname = postgres";
6844
/* Make a connection to the database */
6845
conn = PQconnectdb(conninfo);
6847
/* Check to see that the backend connection was successfully made */
6848
if (PQstatus(conn) != CONNECTION_OK)
6850
fprintf(stderr, "Connection to database failed: %s",
6851
PQerrorMessage(conn));
6856
* The point of this program is to illustrate use of PQexecParams() with
6857
* out-of-line parameters, as well as binary transmission of data.
6859
* This first example transmits the parameters as text, but receives the
6860
* results in binary format. By using out-of-line parameters we can
6861
* avoid a lot of tedious mucking about with quoting and escaping, even
6862
* though the data is text. Notice how we don't have to do anything
6863
* special with the quote mark in the parameter value.
6866
/* Here is our out-of-line parameter value */
6867
paramValues[0] = "joe's place";
6869
res = PQexecParams(conn,
6870
"SELECT * FROM test1 WHERE t = $1",
6872
NULL, /* let the backend deduce param type */
6874
NULL, /* don't need param lengths since text */
6875
NULL, /* default to all text params */
6876
1); /* ask for binary results */
6878
if (PQresultStatus(res) != PGRES_TUPLES_OK)
6880
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
6885
show_binary_results(res);
6890
* In this second example we transmit an integer parameter in binary
6891
* form, and again retrieve the results in binary form.
6893
* Although we tell PQexecParams we are letting the backend deduce
6894
* parameter type, we really force the decision by casting the parameter
6895
* symbol in the query text. This is a good safety measure when sending
6896
* binary parameters.
6899
/* Convert integer value "2" to network byte order */
6900
binaryIntVal = htonl((uint32_t) 2);
6902
/* Set up parameter arrays for PQexecParams */
6903
paramValues[0] = (char *) &binaryIntVal;
6904
paramLengths[0] = sizeof(binaryIntVal);
6905
paramFormats[0] = 1; /* binary */
6907
res = PQexecParams(conn,
6908
"SELECT * FROM test1 WHERE i = $1::int4",
6910
NULL, /* let the backend deduce param type */
6914
1); /* ask for binary results */
6916
if (PQresultStatus(res) != PGRES_TUPLES_OK)
6918
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
6923
show_binary_results(res);
6927
/* close the connection to the database and cleanup */