1 <!-- doc/src/sgml/libpq.sgml -->
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</>,
60 <function>PQconnectdbParams</>, or
61 <function>PQsetdbLogin</>. Note that these functions will always
62 return a non-null object pointer, unless perhaps there is too
63 little memory even to allocate the <structname>PGconn</> object.
64 The <function>PQstatus</> function should be called to check
65 whether a connection was successfully made before queries are sent
66 via the connection object.
70 On Unix, forking a process with open libpq connections can lead to
71 unpredictable results because the parent and child processes share
72 the same sockets and operating system resources. For this reason,
73 such usage is not recommended, though doing an <function>exec</> from
74 the child process to load a new executable is safe.
80 On Windows, there is a way to improve performance if a single
81 database connection is repeatedly started and shutdown. Internally,
82 libpq calls <function>WSAStartup()</> and <function>WSACleanup()</> for connection startup
83 and shutdown, respectively. <function>WSAStartup()</> increments an internal
84 Windows library reference count which is decremented by <function>WSACleanup()</>.
85 When the reference count is just one, calling <function>WSACleanup()</> frees
86 all resources and all DLLs are unloaded. This is an expensive
87 operation. To avoid this, an application can manually call
88 <function>WSAStartup()</> so resources will not be freed when the last database
94 <varlistentry id="libpq-pqconnectdbparams">
95 <term><function>PQconnectdbParams</function><indexterm><primary>PQconnectdbParams</></></term>
98 Makes a new connection to the database server.
101 PGconn *PQconnectdbParams(const char * const *keywords,
102 const char * const *values,
108 This function opens a new database connection using the parameters taken
109 from two <symbol>NULL</symbol>-terminated arrays. The first,
110 <literal>keywords</literal>, is defined as an array of strings, each one
111 being a key word. The second, <literal>values</literal>, gives the value
112 for each key word. Unlike <function>PQsetdbLogin</> below, the parameter
113 set can be extended without changing the function signature, so use of
114 this function (or its nonblocking analogs <function>PQconnectStartParams</>
115 and <function>PQconnectPoll</function>) is preferred for new application
120 When <literal>expand_dbname</literal> is non-zero, the
121 <parameter>dbname</parameter> key word value is allowed to be recognized
122 as a <parameter>conninfo</parameter> string. See below for details.
126 The passed arrays can be empty to use all default parameters, or can
127 contain one or more parameter settings. They should be matched in length.
128 Processing will stop with the last non-<symbol>NULL</symbol> element
129 of the <literal>keywords</literal> array.
133 The currently recognized parameter key words are:
136 <varlistentry id="libpq-connect-host" xreflabel="host">
137 <term><literal>host</literal></term>
140 Name of host to connect to.<indexterm><primary>host name</></>
141 If this begins with a slash, it specifies Unix-domain
142 communication rather than TCP/IP communication; the value is the
143 name of the directory in which the socket file is stored. The
144 default behavior when <literal>host</literal> is not specified
145 is to connect to a Unix-domain
146 socket<indexterm><primary>Unix domain socket</></> in
147 <filename>/tmp</filename> (or whatever socket directory was specified
148 when <productname>PostgreSQL</> was built). On machines without
149 Unix-domain sockets, the default is to connect to <literal>localhost</>.
154 <varlistentry id="libpq-connect-hostaddr" xreflabel="hostaddr">
155 <term><literal>hostaddr</literal></term>
158 Numeric IP address of host to connect to. This should be in the
159 standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
160 your machine supports IPv6, you can also use those addresses.
161 TCP/IP communication is
162 always used when a nonempty string is specified for this parameter.
166 Using <literal>hostaddr</> instead of <literal>host</> allows the
167 application to avoid a host name look-up, which might be important
168 in applications with time constraints. However, a host name is
169 required for Kerberos, GSSAPI, or SSPI authentication
170 methods, as well as for <literal>verify-full</> SSL
171 certificate verification. The following rules are used:
175 If <literal>host</> is specified without <literal>hostaddr</>,
176 a host name lookup occurs.
181 If <literal>hostaddr</> is specified without <literal>host</>,
182 the value for <literal>hostaddr</> gives the server network address.
183 The connection attempt will fail if the authentication
184 method requires a host name.
189 If both <literal>host</> and <literal>hostaddr</> are specified,
190 the value for <literal>hostaddr</> gives the server network address.
191 The value for <literal>host</> is ignored unless the
192 authentication method requires it, in which case it will be
193 used as the host name.
197 Note that authentication is likely to fail if <literal>host</>
198 is not the name of the server at network address <literal>hostaddr</>.
199 Also, note that <literal>host</> rather than <literal>hostaddr</>
200 is used to identify the connection in <filename>~/.pgpass</> (see
201 <xref linkend="libpq-pgpass">).
205 Without either a host name or host address,
206 <application>libpq</application> will connect using a
207 local Unix-domain socket; or on machines without Unix-domain
208 sockets, it will attempt to connect to <literal>localhost</>.
213 <varlistentry id="libpq-connect-port" xreflabel="port">
214 <term><literal>port</literal></term>
217 Port number to connect to at the server host, or socket file
218 name extension for Unix-domain
219 connections.<indexterm><primary>port</></>
224 <varlistentry id="libpq-connect-dbname" xreflabel="dbname">
225 <term><literal>dbname</literal></term>
228 The database name. Defaults to be the same as the user name.
233 <varlistentry id="libpq-connect-user" xreflabel="user">
234 <term><literal>user</literal></term>
237 <productname>PostgreSQL</productname> user name to connect as.
238 Defaults to be the same as the operating system name of the user
239 running the application.
244 <varlistentry id="libpq-connect-password" xreflabel="password">
245 <term><literal>password</literal></term>
248 Password to be used if the server demands password authentication.
253 <varlistentry id="libpq-connect-connect-timeout" xreflabel="connect_timeout">
254 <term><literal>connect_timeout</literal></term>
257 Maximum wait for connection, in seconds (write as a decimal integer
258 string). Zero or not specified means wait indefinitely. It is not
259 recommended to use a timeout of less than 2 seconds.
264 <varlistentry id="libpq-connect-client-encoding" xreflabel="client_encoding">
265 <term><literal>client_encoding</literal></term>
268 This sets the <varname>client_encoding</varname>
269 configuration parameter for this connection. In addition to
270 the values accepted by the corresponding server option, you
271 can use <literal>auto</literal> to determine the right
272 encoding from the current locale in the client
273 (<envar>LC_CTYPE</envar> environment variable on Unix
279 <varlistentry id="libpq-connect-options" xreflabel="options">
280 <term><literal>options</literal></term>
283 Adds command-line options to send to the server at run-time.
284 For example, setting this to <literal>-c geqo=off</> sets the
285 session's value of the <varname>geqo</> parameter to
286 <literal>off</>. For a detailed discussion of the available
287 options, consult <xref linkend="runtime-config">.
292 <varlistentry id="libpq-connect-application-name" xreflabel="application_name">
293 <term><literal>application_name</literal></term>
296 Specifies a value for the <xref linkend="guc-application-name">
297 configuration parameter.
302 <varlistentry id="libpq-connect-fallback-application-name" xreflabel="fallback_application_name">
303 <term><literal>fallback_application_name</literal></term>
306 Specifies a fallback value for the <xref
307 linkend="guc-application-name"> configuration parameter.
308 This value will be used if no value has been given for
309 <literal>application_name</> via a connection parameter or the
310 <envar>PGAPPNAME</envar> environment variable. Specifying
311 a fallback name is useful in generic utility programs that
312 wish to set a default application name but allow it to be
313 overridden by the user.
318 <varlistentry id="libpq-keepalives" xreflabel="keepalives">
319 <term><literal>keepalives</literal></term>
322 Controls whether client-side TCP keepalives are used. The default
323 value is 1, meaning on, but you can change this to 0, meaning off,
324 if keepalives are not wanted. This parameter is ignored for
325 connections made via a Unix-domain socket.
330 <varlistentry id="libpq-keepalives-idle" xreflabel="keepalives_idle">
331 <term><literal>keepalives_idle</literal></term>
334 Controls the number of seconds of inactivity after which TCP should
335 send a keepalive message to the server. A value of zero uses the
336 system default. This parameter is ignored for connections made via a
337 Unix-domain socket, or if keepalives are disabled. It is only supported
338 on systems where the <symbol>TCP_KEEPIDLE</> or <symbol>TCP_KEEPALIVE</>
339 socket option is available, and on Windows; on other systems, it has no
345 <varlistentry id="libpq-keepalives-interval" xreflabel="keepalives_interval">
346 <term><literal>keepalives_interval</literal></term>
349 Controls the number of seconds after which a TCP keepalive message
350 that is not acknowledged by the server should be retransmitted. A
351 value of zero uses the system default. This parameter is ignored for
352 connections made via a Unix-domain socket, or if keepalives are disabled.
353 It is only supported on systems where the <symbol>TCP_KEEPINTVL</>
354 socket option is available, and on Windows; on other systems, it has no
360 <varlistentry id="libpq-keepalives-count" xreflabel="keepalives_count">
361 <term><literal>keepalives_count</literal></term>
364 Controls the number of TCP keepalives that can be lost before the
365 client's connection to the server is considered dead. A value of
366 zero uses the system default. This parameter is ignored for
367 connections made via a Unix-domain socket, or if keepalives are disabled.
368 It is only supported on systems where the <symbol>TCP_KEEPCNT</>
369 socket option is available; on other systems, it has no effect.
374 <varlistentry id="libpq-connect-tty" xreflabel="tty">
375 <term><literal>tty</literal></term>
378 Ignored (formerly, this specified where to send server debug output).
383 <varlistentry id="libpq-connect-sslmode" xreflabel="sslmode">
384 <term><literal>sslmode</literal></term>
387 This option determines whether or with what priority a secure
388 <acronym>SSL</> TCP/IP connection will be negotiated with the
389 server. There are six modes:
393 <term><literal>disable</literal></term>
396 only try a non-<acronym>SSL</> connection
402 <term><literal>allow</literal></term>
405 first try a non-<acronym>SSL</> connection; if that
406 fails, try an <acronym>SSL</> connection
412 <term><literal>prefer</literal> (default)</term>
415 first try an <acronym>SSL</> connection; if that fails,
416 try a non-<acronym>SSL</> connection
422 <term><literal>require</literal></term>
425 only try an <acronym>SSL</> connection. If a root CA
426 file is present, verify the certificate in the same way as
427 if <literal>verify-ca</literal> was specified
433 <term><literal>verify-ca</literal></term>
436 only try an <acronym>SSL</> connection, and verify that
437 the server certificate is issued by a trusted
438 certificate authority (<acronym>CA</>)
444 <term><literal>verify-full</literal></term>
447 only try an <acronym>SSL</> connection, verify that the
448 server certificate is issued by a
449 trusted <acronym>CA</> and that the server host name
450 matches that in the certificate
456 See <xref linkend="libpq-ssl"> for a detailed description of how
461 <literal>sslmode</> is ignored for Unix domain socket
463 If <productname>PostgreSQL</> is compiled without SSL support,
464 using options <literal>require</>, <literal>verify-ca</>, or
465 <literal>verify-full</> will cause an error, while
466 options <literal>allow</> and <literal>prefer</> will be
467 accepted but <application>libpq</> will not actually attempt
469 connection.<indexterm><primary>SSL</><secondary
470 sortas="libpq">with libpq</></indexterm>
475 <varlistentry id="libpq-connect-requiressl" xreflabel="requiressl">
476 <term><literal>requiressl</literal></term>
479 This option is deprecated in favor of the <literal>sslmode</>
484 If set to 1, an <acronym>SSL</acronym> connection to the server
485 is required (this is equivalent to <literal>sslmode</>
486 <literal>require</>). <application>libpq</> will then refuse
487 to connect if the server does not accept an
488 <acronym>SSL</acronym> connection. If set to 0 (default),
489 <application>libpq</> will negotiate the connection type with
490 the server (equivalent to <literal>sslmode</>
491 <literal>prefer</>). This option is only available if
492 <productname>PostgreSQL</> is compiled with SSL support.
497 <varlistentry id="libpq-connect-sslcert" xreflabel="sslcert">
498 <term><literal>sslcert</literal></term>
501 This parameter specifies the file name of the client SSL
502 certificate, replacing the default
503 <filename>~/.postgresql/postgresql.crt</>.
504 This parameter is ignored if an SSL connection is not made.
509 <varlistentry id="libpq-connect-sslkey" xreflabel="sslkey">
510 <term><literal>sslkey</literal></term>
513 This parameter specifies the location for the secret key used for
514 the client certificate. It can either specify a file name that will
515 be used instead of the default
516 <filename>~/.postgresql/postgresql.key</>, or it can specify a key
517 obtained from an external <quote>engine</> (engines are
518 <productname>OpenSSL</> loadable modules). An external engine
519 specification should consist of a colon-separated engine name and
520 an engine-specific key identifier. This parameter is ignored if an
521 SSL connection is not made.
526 <varlistentry id="libpq-connect-sslrootcert" xreflabel="sslrootcert">
527 <term><literal>sslrootcert</literal></term>
530 This parameter specifies the name of a file containing SSL
531 certificate authority (<acronym>CA</>) certificate(s).
532 If the file exists, the server's certificate will be verified
533 to be signed by one of these authorities. The default is
534 <filename>~/.postgresql/root.crt</>.
539 <varlistentry id="libpq-connect-sslcrl" xreflabel="sslcrl">
540 <term><literal>sslcrl</literal></term>
543 This parameter specifies the file name of the SSL certificate
544 revocation list (CRL). Certificates listed in this file, if it
545 exists, will be rejected while attempting to authenticate the
546 server's certificate. The default is
547 <filename>~/.postgresql/root.crl</>.
552 <varlistentry id="libpq-connect-requirepeer" xreflabel="requirepeer">
553 <term><literal>requirepeer</literal></term>
556 This parameter specifies the operating-system user name of the
557 server, for example <literal>requirepeer=postgres</literal>.
558 When making a Unix-domain socket connection, if this
559 parameter is set, the client checks at the beginning of the
560 connection that the server process is running under the specified
561 user name; if it is not, the connection is aborted with an error.
562 This parameter can be used to provide server authentication similar
563 to that available with SSL certificates on TCP/IP connections.
564 (Note that if the Unix-domain socket is in
565 <filename>/tmp</filename> or another publicly writable location,
566 any user could start a server listening there. Use this parameter
567 to ensure that you are connected to a server run by a trusted user.)
568 This option is only supported on platforms for which the
569 <literal>peer</> authentication method is implemented; see
570 <xref linkend="auth-peer">.
575 <varlistentry id="libpq-connect-krbsrvname" xreflabel="krbsrvname">
576 <term><literal>krbsrvname</literal></term>
579 Kerberos service name to use when authenticating with Kerberos 5
581 This must match the service name specified in the server
582 configuration for Kerberos authentication to succeed. (See also
583 <xref linkend="kerberos-auth"> and <xref linkend="gssapi-auth">.)
588 <varlistentry id="libpq-connect-gsslib" xreflabel="gsslib">
589 <term><literal>gsslib</literal></term>
592 GSS library to use for GSSAPI authentication. Only used on Windows.
593 Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
594 library for authentication instead of the default SSPI.
599 <varlistentry id="libpq-connect-service" xreflabel="service">
600 <term><literal>service</literal></term>
603 Service name to use for additional parameters. It specifies a service
604 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
605 This allows applications to specify only a service name so connection parameters
606 can be centrally maintained. See <xref linkend="libpq-pgservice">.
612 If any parameter is unspecified, then the corresponding
613 environment variable (see <xref linkend="libpq-envars">)
614 is checked. If the environment variable is not set either,
615 then the indicated built-in defaults are used.
619 If <literal>expand_dbname</literal> is non-zero and
620 <parameter>dbname</parameter> contains an <symbol>=</symbol> sign, it
621 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
622 if it had been passed to <function>PQconnectdb</function>(see below). Previously
623 processed key words will be overridden by key words in the
624 <parameter>conninfo</parameter> string.
628 In general key words are processed from the beginning of these arrays in index
629 order. The effect of this is that when key words are repeated, the last processed
630 value is retained. Therefore, through careful placement of the
631 <parameter>dbname</parameter> key word, it is possible to determine what may
632 be overridden by a <parameter>conninfo</parameter> string, and what may not.
638 <varlistentry id="libpq-pqconnectdb">
639 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
642 Makes a new connection to the database server.
645 PGconn *PQconnectdb(const char *conninfo);
650 This function opens a new database connection using the parameters taken
651 from the string <literal>conninfo</literal>.
655 The passed string can be empty to use all default parameters, or it can
656 contain one or more parameter settings separated by whitespace.
657 Each parameter setting is in the form <literal>keyword = value</literal>.
658 Spaces around the equal sign are optional. To write an empty value,
659 or a value containing spaces, surround it with single quotes, e.g.,
660 <literal>keyword = 'a value'</literal>. Single quotes and backslashes
661 within the value must be escaped with a backslash, i.e.,
662 <literal>\'</literal> and <literal>\\</literal>.
666 The currently recognized parameter key words are the same as above.
671 <varlistentry id="libpq-pqsetdblogin">
672 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
675 Makes a new connection to the database server.
677 PGconn *PQsetdbLogin(const char *pghost,
679 const char *pgoptions,
688 This is the predecessor of <function>PQconnectdb</function> with a fixed
689 set of parameters. It has the same functionality except that the
690 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
691 empty string for any one of the fixed parameters that is to be defaulted.
695 If the <parameter>dbName</parameter> contains an <symbol>=</symbol> sign, it
696 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
697 if it had been passed to <function>PQconnectdb</function>, and the remaining
698 parameters are then applied as above.
703 <varlistentry id="libpq-pqsetdb">
704 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
707 Makes a new connection to the database server.
709 PGconn *PQsetdb(char *pghost,
718 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
719 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
720 for backward compatibility with very old programs.
725 <varlistentry id="libpq-pqconnectstartparams">
726 <term><function>PQconnectStartParams</function><indexterm><primary>PQconnectStartParams</></></term>
727 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
728 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
731 <indexterm><primary>nonblocking connection</primary></indexterm>
732 Make a connection to the database server in a nonblocking manner.
735 PGconn *PQconnectStartParams(const char * const *keywords,
736 const char * const *values,
739 PGconn *PQconnectStart(const char *conninfo);
741 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
746 These three functions are used to open a connection to a database server such
747 that your application's thread of execution is not blocked on remote I/O
748 whilst doing so. The point of this approach is that the waits for I/O to
749 complete can occur in the application's main loop, rather than down inside
750 <function>PQconnectdbParams</> or <function>PQconnectdb</>, and so the
751 application can manage this operation in parallel with other activities.
755 With <function>PQconnectStartParams</function>, the database connection is made
756 using the parameters taken from the <literal>keywords</literal> and
757 <literal>values</literal> arrays, and controlled by <literal>expand_dbname</literal>,
758 as described above for <function>PQconnectdbParams</function>.
762 With <function>PQconnectStart</function>, the database connection is made
763 using the parameters taken from the string <literal>conninfo</literal> as
764 described above for <function>PQconnectdb</function>.
768 Neither <function>PQconnectStartParams</function> nor <function>PQconnectStart</function>
769 nor <function>PQconnectPoll</function> will block, so long as a number of
770 restrictions are met:
774 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
775 name and reverse name queries are not made. See the documentation of
776 these parameters under <function>PQconnectdbParams</function> above for details.
782 If you call <function>PQtrace</function>, ensure that the stream object
783 into which you trace will not block.
789 You ensure that the socket is in the appropriate state
790 before calling <function>PQconnectPoll</function>, as described below.
797 Note: use of <function>PQconnectStartParams</> is analogous to
798 <function>PQconnectStart</> shown below.
802 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
803 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
804 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
805 representing a valid connection to the database). On return from
806 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
807 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
811 If <function>PQconnectStart</> succeeds, the next stage is to poll
812 <application>libpq</> so that it can proceed with the connection sequence.
813 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
814 socket underlying the database connection.
815 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
816 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
817 read (as indicated by <function>select()</>, <function>poll()</>, or
818 similar system function).
819 Then call <function>PQconnectPoll(conn)</function> again.
820 Conversely, if <function>PQconnectPoll(conn)</function> last returned
821 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
822 to write, then call <function>PQconnectPoll(conn)</function> again.
823 If you have yet to call
824 <function>PQconnectPoll</function>, i.e., just after the call to
825 <function>PQconnectStart</function>, behave as if it last returned
826 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
827 <function>PQconnectPoll(conn)</function> returns
828 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
829 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
830 has been successfully made.
834 At any time during connection, the status of the connection can be
835 checked by calling <function>PQstatus</>. If this call returns <symbol>CONNECTION_BAD</>, then the
836 connection procedure has failed; if the call returns <function>CONNECTION_OK</>, then the
837 connection is ready. Both of these states are equally detectable
838 from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
839 during (and only during) an asynchronous connection procedure. These
840 indicate the current stage of the connection procedure and might be useful
841 to provide feedback to the user for example. These statuses are:
844 <varlistentry id="libpq-connection-started">
845 <term><symbol>CONNECTION_STARTED</symbol></term>
848 Waiting for connection to be made.
853 <varlistentry id="libpq-connection-made">
854 <term><symbol>CONNECTION_MADE</symbol></term>
857 Connection OK; waiting to send.
862 <varlistentry id="libpq-connection-awaiting-response">
863 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
866 Waiting for a response from the server.
871 <varlistentry id="libpq-connection-auth-ok">
872 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
875 Received authentication; waiting for backend start-up to finish.
880 <varlistentry id="libpq-connection-ssl-startup">
881 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
884 Negotiating SSL encryption.
889 <varlistentry id="libpq-connection-setenv">
890 <term><symbol>CONNECTION_SETENV</symbol></term>
893 Negotiating environment-driven parameter settings.
899 Note that, although these constants will remain (in order to maintain
900 compatibility), an application should never rely upon these occurring in a
901 particular order, or at all, or on the status always being one of these
902 documented values. An application might do something like this:
904 switch(PQstatus(conn))
906 case CONNECTION_STARTED:
907 feedback = "Connecting...";
910 case CONNECTION_MADE:
911 feedback = "Connected to server...";
917 feedback = "Connecting...";
923 The <literal>connect_timeout</literal> connection parameter is ignored
924 when using <function>PQconnectPoll</function>; it is the application's
925 responsibility to decide whether an excessive amount of time has elapsed.
926 Otherwise, <function>PQconnectStart</function> followed by a
927 <function>PQconnectPoll</function> loop is equivalent to
928 <function>PQconnectdb</function>.
932 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
933 <function>PQfinish</function> when you are finished with it, in order to dispose of
934 the structure and any associated memory blocks. This must be done even if
935 the connection attempt fails or is abandoned.
940 <varlistentry id="libpq-pqconndefaults">
941 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
944 Returns the default connection options.
946 PQconninfoOption *PQconndefaults(void);
950 char *keyword; /* The keyword of the option */
951 char *envvar; /* Fallback environment variable name */
952 char *compiled; /* Fallback compiled in default value */
953 char *val; /* Option's current value, or NULL */
954 char *label; /* Label for field in connect dialog */
955 char *dispchar; /* Indicates how to display this field
956 in a connect dialog. Values are:
957 "" Display entered value as is
958 "*" Password field - hide value
959 "D" Debug option - don't show by default */
960 int dispsize; /* Field size in characters for dialog */
966 Returns a connection options array. This can be used to determine
967 all possible <function>PQconnectdb</function> options and their
968 current default values. The return value points to an array of
969 <structname>PQconninfoOption</structname> structures, which ends
970 with an entry having a null <structfield>keyword</> pointer. The
971 null pointer is returned if memory could not be allocated. Note that
972 the current default values (<structfield>val</structfield> fields)
973 will depend on environment variables and other context. Callers
974 must treat the connection options data as read-only.
978 After processing the options array, free it by passing it to
979 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
980 is leaked for each call to <function>PQconndefaults</function>.
986 <varlistentry id="libpq-pqconninfoparse">
987 <term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</></></term>
990 Returns parsed connection options from the provided connection string.
993 PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
998 Parses a connection string and returns the resulting options as an
999 array; or returns <symbol>NULL</> if there is a problem with the connection
1000 string. This function can be used to extract
1001 the <function>PQconnectdb</function> options in the provided
1002 connection string. The return value points to an array of
1003 <structname>PQconninfoOption</structname> structures, which ends
1004 with an entry having a null <structfield>keyword</> pointer.
1008 Note that only options explicitly specified in the string will have
1009 values set in the result array; no defaults are inserted.
1013 If <literal>errmsg</> is not <symbol>NULL</>, then <literal>*errmsg</> is set
1014 to <symbol>NULL</> on success, else to a <function>malloc</>'d error string explaining
1015 the problem. (It is also possible for <literal>*errmsg</> to be
1016 set to <symbol>NULL</> and the function to return <symbol>NULL</>;
1017 this indicates an out-of-memory condition.)
1021 After processing the options array, free it by passing it to
1022 <function>PQconninfoFree</function>. If this is not done, some memory
1023 is leaked for each call to <function>PQconninfoParse</function>.
1024 Conversely, if an error occurs and <literal>errmsg</> is not <symbol>NULL</>,
1025 be sure to free the error string using <function>PQfreemem</>.
1031 <varlistentry id="libpq-pqfinish">
1032 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
1035 Closes the connection to the server. Also frees
1036 memory used by the <structname>PGconn</structname> object.
1038 void PQfinish(PGconn *conn);
1043 Note that even if the server connection attempt fails (as
1044 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
1045 to free the memory used by the <structname>PGconn</structname> object.
1046 The <structname>PGconn</> pointer must not be used again after
1047 <function>PQfinish</function> has been called.
1052 <varlistentry id="libpq-pqreset">
1053 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
1056 Resets the communication channel to the server.
1058 void PQreset(PGconn *conn);
1063 This function will close the connection
1064 to the server and attempt to reestablish a new
1065 connection to the same server, using all the same
1066 parameters previously used. This might be useful for
1067 error recovery if a working connection is lost.
1072 <varlistentry id="libpq-pqresetstart">
1073 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
1074 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
1077 Reset the communication channel to the server, in a nonblocking manner.
1080 int PQresetStart(PGconn *conn);
1082 PostgresPollingStatusType PQresetPoll(PGconn *conn);
1087 These functions will close the connection to the server and attempt to
1088 reestablish a new connection to the same server, using all the same
1089 parameters previously used. This can be useful for error recovery if a
1090 working connection is lost. They differ from <function>PQreset</function> (above) in that they
1091 act in a nonblocking manner. These functions suffer from the same
1092 restrictions as <function>PQconnectStartParams</>, <function>PQconnectStart</>
1093 and <function>PQconnectPoll</>.
1097 To initiate a connection reset, call
1098 <function>PQresetStart</function>. If it returns 0, the reset has
1099 failed. If it returns 1, poll the reset using
1100 <function>PQresetPoll</function> in exactly the same way as you
1101 would create the connection using <function>PQconnectPoll</function>.
1106 <varlistentry id="libpq-pqpingparams">
1107 <term><function>PQpingParams</function><indexterm><primary>PQpingParams</></></term>
1110 <function>PQpingParams</function> reports the status of the
1111 server. It accepts connection parameters identical to those of
1112 <function>PQconnectdbParams</>, described above. It is not, however,
1113 necessary to supply correct user name, password, or database name
1114 values to obtain the server status.
1117 PGPing PQpingParams(const char * const *keywords,
1118 const char * const *values,
1122 The function returns one of the following values:
1125 <varlistentry id="libpq-pqpingparams-pqping-ok">
1126 <term><literal>PQPING_OK</literal></term>
1129 The server is running and appears to be accepting connections.
1134 <varlistentry id="libpq-pqpingparams-pqping-reject">
1135 <term><literal>PQPING_REJECT</literal></term>
1138 The server is running but is in a state that disallows connections
1139 (startup, shutdown, or crash recovery).
1144 <varlistentry id="libpq-pqpingparams-pqping-no-response">
1145 <term><literal>PQPING_NO_RESPONSE</literal></term>
1148 The server could not be contacted. This might indicate that the
1149 server is not running, or that there is something wrong with the
1150 given connection parameters (for example, wrong port number), or
1151 that there is a network connectivity problem (for example, a
1152 firewall blocking the connection request).
1157 <varlistentry id="libpq-pqpingparams-pqping-no-attempt">
1158 <term><literal>PQPING_NO_ATTEMPT</literal></term>
1161 No attempt was made to contact the server, because the supplied
1162 parameters were obviously incorrect or there was some client-side
1163 problem (for example, out of memory).
1174 <varlistentry id="libpq-pqping">
1175 <term><function>PQping</function><indexterm><primary>PQping</></></term>
1178 <function>PQping</function> reports the status of the
1179 server. It accepts connection parameters identical to those of
1180 <function>PQconnectdb</>, described above. It is not, however,
1181 necessary to supply correct user name, password, or database name
1182 values to obtain the server status.
1185 PGPing PQping(const char *conninfo);
1190 The return values are the same as for <function>PQpingParams</>.
1200 <sect1 id="libpq-status">
1201 <title>Connection Status Functions</title>
1204 These functions can be used to interrogate the status
1205 of an existing database connection object.
1210 <indexterm><primary>libpq-fe.h</></>
1211 <indexterm><primary>libpq-int.h</></>
1212 <application>libpq</application> application programmers should be careful to
1213 maintain the <structname>PGconn</structname> abstraction. Use the accessor
1214 functions described below to get at the contents of <structname>PGconn</structname>.
1215 Reference to internal <structname>PGconn</structname> fields using
1216 <filename>libpq-int.h</> is not recommended because they are subject to change
1222 The following functions return parameter values established at connection.
1223 These values are fixed for the life of the <structname>PGconn</> object.
1226 <varlistentry id="libpq-pqdb">
1228 <function>PQdb</function>
1230 <primary>PQdb</primary>
1236 Returns the database name of the connection.
1238 char *PQdb(const PGconn *conn);
1244 <varlistentry id="libpq-pquser">
1246 <function>PQuser</function>
1248 <primary>PQuser</primary>
1254 Returns the user name of the connection.
1256 char *PQuser(const PGconn *conn);
1262 <varlistentry id="libpq-pqpass">
1264 <function>PQpass</function>
1266 <primary>PQpass</primary>
1272 Returns the password of the connection.
1274 char *PQpass(const PGconn *conn);
1280 <varlistentry id="libpq-pqhost">
1282 <function>PQhost</function>
1284 <primary>PQhost</primary>
1290 Returns the server host name of the connection.
1292 char *PQhost(const PGconn *conn);
1298 <varlistentry id="libpq-pqport">
1300 <function>PQport</function>
1302 <primary>PQport</primary>
1308 Returns the port of the connection.
1311 char *PQport(const PGconn *conn);
1317 <varlistentry id="libpq-pqtty">
1319 <function>PQtty</function>
1321 <primary>PQtty</primary>
1327 Returns the debug <acronym>TTY</acronym> of the connection.
1328 (This is obsolete, since the server no longer pays attention
1329 to the <acronym>TTY</acronym> setting, but the function remains
1330 for backward compatibility.)
1333 char *PQtty(const PGconn *conn);
1339 <varlistentry id="libpq-pqoptions">
1341 <function>PQoptions</function>
1343 <primary>PQoptions</primary>
1349 Returns the command-line options passed in the connection request.
1351 char *PQoptions(const PGconn *conn);
1360 The following functions return status data that can change as operations
1361 are executed on the <structname>PGconn</> object.
1364 <varlistentry id="libpq-pqstatus">
1366 <function>PQstatus</function>
1368 <primary>PQstatus</primary>
1374 Returns the status of the connection.
1376 ConnStatusType PQstatus(const PGconn *conn);
1381 The status can be one of a number of values. However, only two of
1382 these are seen outside of an asynchronous connection procedure:
1383 <literal>CONNECTION_OK</literal> and
1384 <literal>CONNECTION_BAD</literal>. A good connection to the database
1385 has the status <literal>CONNECTION_OK</literal>. A failed
1386 connection attempt is signaled by status
1387 <literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
1388 remain so until <function>PQfinish</function>, but a communications
1389 failure might result in the status changing to
1390 <literal>CONNECTION_BAD</literal> prematurely. In that case the
1391 application could try to recover by calling
1392 <function>PQreset</function>.
1396 See the entry for <function>PQconnectStartParams</>, <function>PQconnectStart</>
1397 and <function>PQconnectPoll</> with regards to other status codes that
1403 <varlistentry id="libpq-pqtransactionstatus">
1405 <function>PQtransactionStatus</function>
1407 <primary>PQtransactionStatus</primary>
1413 Returns the current in-transaction status of the server.
1416 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
1419 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
1420 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
1421 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
1422 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
1423 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
1424 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
1425 has been sent to the server and not yet completed.
1430 <function>PQtransactionStatus</> will give incorrect results when using
1431 a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
1432 set to off. The server-side autocommit feature has been
1433 deprecated and does not exist in later server versions.
1439 <varlistentry id="libpq-pqparameterstatus">
1441 <function>PQparameterStatus</function>
1443 <primary>PQparameterStatus</primary>
1449 Looks up a current parameter setting of the server.
1452 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
1455 Certain parameter values are reported by the server automatically at
1456 connection startup or whenever their values change.
1457 <function>PQparameterStatus</> can be used to interrogate these settings.
1458 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
1459 if the parameter is not known.
1463 Parameters reported as of the current release include
1464 <varname>server_version</>,
1465 <varname>server_encoding</>,
1466 <varname>client_encoding</>,
1467 <varname>application_name</>,
1468 <varname>is_superuser</>,
1469 <varname>session_authorization</>,
1470 <varname>DateStyle</>,
1471 <varname>IntervalStyle</>,
1472 <varname>TimeZone</>,
1473 <varname>integer_datetimes</>, and
1474 <varname>standard_conforming_strings</>.
1475 (<varname>server_encoding</>, <varname>TimeZone</>, and
1476 <varname>integer_datetimes</> were not reported by releases before 8.0;
1477 <varname>standard_conforming_strings</> was not reported by releases
1479 <varname>IntervalStyle</> was not reported by releases before 8.4;
1480 <varname>application_name</> was not reported by releases before 9.0.)
1482 <varname>server_version</>,
1483 <varname>server_encoding</> and
1484 <varname>integer_datetimes</>
1485 cannot change after startup.
1489 Pre-3.0-protocol servers do not report parameter settings, but
1490 <application>libpq</> includes logic to obtain values for
1491 <varname>server_version</> and <varname>client_encoding</> anyway.
1492 Applications are encouraged to use <function>PQparameterStatus</>
1493 rather than <foreignphrase>ad hoc</> code to determine these values.
1494 (Beware however that on a pre-3.0 connection, changing
1495 <varname>client_encoding</> via <command>SET</> after connection
1496 startup will not be reflected by <function>PQparameterStatus</>.)
1497 For <varname>server_version</>, see also
1498 <function>PQserverVersion</>, which returns the information in a
1499 numeric form that is much easier to compare against.
1503 If no value for <varname>standard_conforming_strings</> is reported,
1504 applications can assume it is <literal>off</>, that is, backslashes
1505 are treated as escapes in string literals. Also, the presence of
1506 this parameter can be taken as an indication that the escape string
1507 syntax (<literal>E'...'</>) is accepted.
1511 Although the returned pointer is declared <literal>const</>, it in fact
1512 points to mutable storage associated with the <literal>PGconn</> structure.
1513 It is unwise to assume the pointer will remain valid across queries.
1518 <varlistentry id="libpq-pqprotocolversion">
1520 <function>PQprotocolVersion</function>
1522 <primary>PQprotocolVersion</primary>
1528 Interrogates the frontend/backend protocol being used.
1530 int PQprotocolVersion(const PGconn *conn);
1532 Applications might wish to use this function to determine whether certain
1533 features are supported. Currently, the possible values are 2 (2.0
1534 protocol), 3 (3.0 protocol), or zero (connection bad). The
1535 protocol version will
1536 not change after connection startup is complete, but it could
1537 theoretically change during a connection reset. The 3.0 protocol
1538 will normally be used when communicating with
1539 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
1540 support only protocol 2.0. (Protocol 1.0 is obsolete and not
1541 supported by <application>libpq</application>.)
1546 <varlistentry id="libpq-pqserverversion">
1548 <function>PQserverVersion</function>
1550 <primary>PQserverVersion</primary>
1556 Returns an integer representing the backend version.
1558 int PQserverVersion(const PGconn *conn);
1560 Applications might use this function to determine the version of the database
1561 server they are connected to. The number is formed by converting
1562 the major, minor, and revision numbers into two-decimal-digit
1563 numbers and appending them together. For example, version 8.1.5
1564 will be returned as 80105, and version 8.2 will be returned as
1565 80200 (leading zeroes are not shown). Zero is returned if the
1571 <varlistentry id="libpq-pqerrormessage">
1573 <function>PQerrorMessage</function>
1575 <primary>PQerrorMessage</primary>
1581 <indexterm><primary>error message</></> Returns the error message
1582 most recently generated by an operation on the connection.
1585 char *PQerrorMessage(const PGconn *conn);
1591 Nearly all <application>libpq</> functions will set a message for
1592 <function>PQerrorMessage</function> if they fail. Note that by
1593 <application>libpq</application> convention, a nonempty
1594 <function>PQerrorMessage</function> result can consist of multiple lines,
1595 and will include a trailing newline. The caller should not free
1596 the result directly. It will be freed when the associated
1597 <structname>PGconn</> handle is passed to
1598 <function>PQfinish</function>. The result string should not be
1599 expected to remain the same across operations on the
1600 <literal>PGconn</> structure.
1605 <varlistentry id="libpq-pqsocket">
1606 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1609 Obtains the file descriptor number of the connection socket to
1610 the server. A valid descriptor will be greater than or equal
1611 to 0; a result of -1 indicates that no server connection is
1612 currently open. (This will not change during normal operation,
1613 but could change during connection setup or reset.)
1616 int PQsocket(const PGconn *conn);
1623 <varlistentry id="libpq-pqbackendpid">
1624 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1627 Returns the process <acronym>ID</acronym> (PID)<indexterm>
1629 <secondary>determining PID of server process</>
1630 <tertiary>in libpq</>
1632 of the backend process handling this connection.
1635 int PQbackendPID(const PGconn *conn);
1640 The backend <acronym>PID</acronym> is useful for debugging
1641 purposes and for comparison to <command>NOTIFY</command>
1642 messages (which include the <acronym>PID</acronym> of the
1643 notifying backend process). Note that the
1644 <acronym>PID</acronym> belongs to a process executing on the
1645 database server host, not the local host!
1650 <varlistentry id="libpq-pqconnectionneedspassword">
1651 <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
1654 Returns true (1) if the connection authentication method
1655 required a password, but none was available.
1656 Returns false (0) if not.
1659 int PQconnectionNeedsPassword(const PGconn *conn);
1664 This function can be applied after a failed connection attempt
1665 to decide whether to prompt the user for a password.
1670 <varlistentry id="libpq-pqconnectionusedpassword">
1671 <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
1674 Returns true (1) if the connection authentication method
1675 used a password. Returns false (0) if not.
1678 int PQconnectionUsedPassword(const PGconn *conn);
1683 This function can be applied after either a failed or successful
1684 connection attempt to detect whether the server demanded a password.
1689 <varlistentry id="libpq-pqgetssl">
1690 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1693 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1694 Returns the SSL structure used in the connection, or null
1695 if SSL is not in use.
1698 SSL *PQgetssl(const PGconn *conn);
1703 This structure can be used to verify encryption levels, check server
1704 certificates, and more. Refer to the <productname>OpenSSL</>
1705 documentation for information about this structure.
1709 You must define <symbol>USE_SSL</symbol> in order to get the
1710 correct prototype for this function. Doing so will also
1711 automatically include <filename>ssl.h</filename> from
1712 <productname>OpenSSL</productname>.
1722 <sect1 id="libpq-exec">
1723 <title>Command Execution Functions</title>
1726 Once a connection to a database server has been successfully
1727 established, the functions described here are used to perform
1728 SQL queries and commands.
1731 <sect2 id="libpq-exec-main">
1732 <title>Main Functions</title>
1736 <varlistentry id="libpq-pqexec">
1738 <function>PQexec</function>
1740 <primary>PQexec</primary>
1746 Submits a command to the server and waits for the result.
1749 PGresult *PQexec(PGconn *conn, const char *command);
1754 Returns a <structname>PGresult</structname> pointer or possibly a null
1755 pointer. A non-null pointer will generally be returned except in
1756 out-of-memory conditions or serious errors such as inability to send
1757 the command to the server. If a null pointer is returned, it should
1758 be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
1759 <function>PQerrorMessage</function> to get more information about such
1766 The command string can include multiple SQL commands
1767 (separated by semicolons). Multiple queries sent in a single
1768 <function>PQexec</> call are processed in a single transaction, unless
1769 there are explicit <command>BEGIN</command>/<command>COMMIT</command>
1770 commands included in the query string to divide it into multiple
1771 transactions. Note however that the returned
1772 <structname>PGresult</structname> structure describes only the result
1773 of the last command executed from the string. Should one of the
1774 commands fail, processing of the string stops with it and the returned
1775 <structname>PGresult</structname> describes the error condition.
1780 <varlistentry id="libpq-pqexecparams">
1782 <function>PQexecParams</function>
1784 <primary>PQexecParams</primary>
1790 Submits a command to the server and waits for the result,
1791 with the ability to pass parameters separately from the SQL
1795 PGresult *PQexecParams(PGconn *conn,
1796 const char *command,
1798 const Oid *paramTypes,
1799 const char * const *paramValues,
1800 const int *paramLengths,
1801 const int *paramFormats,
1807 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1808 functionality: parameter values can be specified separately from the command
1809 string proper, and query results can be requested in either text or binary
1810 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1811 connections; it will fail when using protocol 2.0.
1815 The function arguments are:
1819 <term><parameter>conn</parameter></term>
1823 The connection object to send the command through.
1829 <term><parameter>command</parameter></term>
1832 The SQL command string to be executed. If parameters are used,
1833 they are referred to in the command string as <literal>$1</>,
1834 <literal>$2</>, etc.
1840 <term><parameter>nParams</parameter></term>
1843 The number of parameters supplied; it is the length of the arrays
1844 <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1845 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1846 array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
1853 <term><parameter>paramTypes[]</parameter></term>
1856 Specifies, by OID, the data types to be assigned to the
1857 parameter symbols. If <parameter>paramTypes</> is
1858 <symbol>NULL</symbol>, or any particular element in the array
1859 is zero, the server infers a data type for the parameter symbol
1860 in the same way it would do for an untyped literal string.
1866 <term><parameter>paramValues[]</parameter></term>
1869 Specifies the actual values of the parameters. A null pointer
1870 in this array means the corresponding parameter is null;
1871 otherwise the pointer points to a zero-terminated text string
1872 (for text format) or binary data in the format expected by the
1873 server (for binary format).
1879 <term><parameter>paramLengths[]</parameter></term>
1882 Specifies the actual data lengths of binary-format parameters.
1883 It is ignored for null parameters and text-format parameters.
1884 The array pointer can be null when there are no binary parameters.
1890 <term><parameter>paramFormats[]</parameter></term>
1893 Specifies whether parameters are text (put a zero in the
1894 array entry for the corresponding parameter) or binary (put
1895 a one in the array entry for the corresponding parameter).
1896 If the array pointer is null then all parameters are presumed
1900 Values passed in binary format require knowledge of
1901 the internal representation expected by the backend.
1902 For example, integers must be passed in network byte
1903 order. Passing <type>numeric</> values requires
1904 knowledge of the server storage format, as implemented
1906 <filename>src/backend/utils/adt/numeric.c::numeric_send()</> and
1907 <filename>src/backend/utils/adt/numeric.c::numeric_recv()</>.
1913 <term><parameter>resultFormat</parameter></term>
1916 Specify zero to obtain results in text format, or one to obtain
1917 results in binary format. (There is not currently a provision
1918 to obtain different result columns in different formats,
1919 although that is possible in the underlying protocol.)
1931 The primary advantage of <function>PQexecParams</> over
1932 <function>PQexec</> is that parameter values can be separated from the
1933 command string, thus avoiding the need for tedious and error-prone
1934 quoting and escaping.
1938 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
1939 one SQL command in the given string. (There can be semicolons in it,
1940 but not more than one nonempty command.) This is a limitation of the
1941 underlying protocol, but has some usefulness as an extra defense against
1942 SQL-injection attacks.
1947 Specifying parameter types via OIDs is tedious, particularly if you prefer
1948 not to hard-wire particular OID values into your program. However, you can
1949 avoid doing so even in cases where the server by itself cannot determine the
1950 type of the parameter, or chooses a different type than you want. In the
1951 SQL command text, attach an explicit cast to the parameter symbol to show what
1952 data type you will send. For example:
1954 SELECT * FROM mytable WHERE x = $1::bigint;
1956 This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
1957 by default it would be assigned the same type as <literal>x</>. Forcing the
1958 parameter type decision, either this way or by specifying a numeric type OID,
1959 is strongly recommended when sending parameter values in binary format, because
1960 binary format has less redundancy than text format and so there is less chance
1961 that the server will detect a type mismatch mistake for you.
1967 <varlistentry id="libpq-pqprepare">
1968 <term><function>PQprepare</function>
1970 <primary>PQprepare</primary>
1976 Submits a request to create a prepared statement with the
1977 given parameters, and waits for completion.
1979 PGresult *PQprepare(PGconn *conn,
1980 const char *stmtName,
1983 const Oid *paramTypes);
1988 <function>PQprepare</> creates a prepared statement for later
1989 execution with <function>PQexecPrepared</>. This feature allows
1990 commands that will be used repeatedly to be parsed and planned just
1991 once, rather than each time they are executed.
1992 <function>PQprepare</> is supported only in protocol 3.0 and later
1993 connections; it will fail when using protocol 2.0.
1997 The function creates a prepared statement named
1998 <parameter>stmtName</> from the <parameter>query</> string, which
1999 must contain a single SQL command. <parameter>stmtName</> can be
2000 <literal>""</> to create an unnamed statement, in which case any
2001 pre-existing unnamed statement is automatically replaced; otherwise
2002 it is an error if the statement name is already defined in the
2003 current session. If any parameters are used, they are referred
2004 to in the query as <literal>$1</>, <literal>$2</>, etc.
2005 <parameter>nParams</> is the number of parameters for which types
2006 are pre-specified in the array <parameter>paramTypes[]</>. (The
2007 array pointer can be <symbol>NULL</symbol> when
2008 <parameter>nParams</> is zero.) <parameter>paramTypes[]</>
2009 specifies, by OID, the data types to be assigned to the parameter
2010 symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
2011 or any particular element in the array is zero, the server assigns
2012 a data type to the parameter symbol in the same way it would do
2013 for an untyped literal string. Also, the query can use parameter
2014 symbols with numbers higher than <parameter>nParams</>; data types
2015 will be inferred for these symbols as well. (See
2016 <function>PQdescribePrepared</function> for a means to find out
2017 what data types were inferred.)
2021 As with <function>PQexec</>, the result is normally a
2022 <structname>PGresult</structname> object whose contents indicate
2023 server-side success or failure. A null result indicates
2024 out-of-memory or inability to send the command at all. Use
2025 <function>PQerrorMessage</function> to get more information about
2032 Prepared statements for use with <function>PQexecPrepared</> can also
2033 be created by executing SQL <xref linkend="sql-prepare">
2034 statements. Also, although there is no <application>libpq</>
2035 function for deleting a prepared statement, the SQL <xref
2036 linkend="sql-deallocate"> statement
2037 can be used for that purpose.
2042 <varlistentry id="libpq-pqexecprepared">
2044 <function>PQexecPrepared</function>
2046 <primary>PQexecPrepared</primary>
2052 Sends a request to execute a prepared statement with given
2053 parameters, and waits for the result.
2055 PGresult *PQexecPrepared(PGconn *conn,
2056 const char *stmtName,
2058 const char * const *paramValues,
2059 const int *paramLengths,
2060 const int *paramFormats,
2066 <function>PQexecPrepared</> is like <function>PQexecParams</>,
2067 but the command to be executed is specified by naming a
2068 previously-prepared statement, instead of giving a query string.
2069 This feature allows commands that will be used repeatedly to be
2070 parsed and planned just once, rather than each time they are
2071 executed. The statement must have been prepared previously in
2072 the current session. <function>PQexecPrepared</> is supported
2073 only in protocol 3.0 and later connections; it will fail when
2078 The parameters are identical to <function>PQexecParams</>, except that the
2079 name of a prepared statement is given instead of a query string, and the
2080 <parameter>paramTypes[]</> parameter is not present (it is not needed since
2081 the prepared statement's parameter types were determined when it was created).
2086 <varlistentry id="libpq-pqdescribeprepared">
2088 <function>PQdescribePrepared</function>
2090 <primary>PQdescribePrepared</primary>
2096 Submits a request to obtain information about the specified
2097 prepared statement, and waits for completion.
2099 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
2104 <function>PQdescribePrepared</> allows an application to obtain
2105 information about a previously prepared statement.
2106 <function>PQdescribePrepared</> is supported only in protocol 3.0
2107 and later connections; it will fail when using protocol 2.0.
2111 <parameter>stmtName</> can be <literal>""</> or <symbol>NULL</> to reference
2112 the unnamed statement, otherwise it must be the name of an existing
2113 prepared statement. On success, a <structname>PGresult</> with
2114 status <literal>PGRES_COMMAND_OK</literal> is returned. The
2115 functions <function>PQnparams</function> and
2116 <function>PQparamtype</function> can be applied to this
2117 <structname>PGresult</> to obtain information about the parameters
2118 of the prepared statement, and the functions
2119 <function>PQnfields</function>, <function>PQfname</function>,
2120 <function>PQftype</function>, etc provide information about the
2121 result columns (if any) of the statement.
2126 <varlistentry id="libpq-pqdescribeportal">
2128 <function>PQdescribePortal</function>
2130 <primary>PQdescribePortal</primary>
2136 Submits a request to obtain information about the specified
2137 portal, and waits for completion.
2139 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
2144 <function>PQdescribePortal</> allows an application to obtain
2145 information about a previously created portal.
2146 (<application>libpq</> does not provide any direct access to
2147 portals, but you can use this function to inspect the properties
2148 of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
2149 <function>PQdescribePortal</> is supported only in protocol 3.0
2150 and later connections; it will fail when using protocol 2.0.
2154 <parameter>portalName</> can be <literal>""</> or <symbol>NULL</> to reference
2155 the unnamed portal, otherwise it must be the name of an existing
2156 portal. On success, a <structname>PGresult</> with status
2157 <literal>PGRES_COMMAND_OK</literal> is returned. The functions
2158 <function>PQnfields</function>, <function>PQfname</function>,
2159 <function>PQftype</function>, etc can be applied to the
2160 <structname>PGresult</> to obtain information about the result
2161 columns (if any) of the portal.
2169 The <structname>PGresult</structname><indexterm><primary>PGresult</></>
2170 structure encapsulates the result returned by the server.
2171 <application>libpq</application> application programmers should be
2172 careful to maintain the <structname>PGresult</structname> abstraction.
2173 Use the accessor functions below to get at the contents of
2174 <structname>PGresult</structname>. Avoid directly referencing the
2175 fields of the <structname>PGresult</structname> structure because they
2176 are subject to change in the future.
2179 <varlistentry id="libpq-pqresultstatus">
2181 <function>PQresultStatus</function>
2183 <primary>PQresultStatus</primary>
2189 Returns the result status of the command.
2191 ExecStatusType PQresultStatus(const PGresult *res);
2196 <function>PQresultStatus</function> can return one of the following values:
2199 <varlistentry id="libpq-pgres-empty-query">
2200 <term><literal>PGRES_EMPTY_QUERY</literal></term>
2203 The string sent to the server was empty.
2208 <varlistentry id="libpq-pgres-command-ok">
2209 <term><literal>PGRES_COMMAND_OK</literal></term>
2212 Successful completion of a command returning no data.
2217 <varlistentry id="libpq-pgres-tuples-ok">
2218 <term><literal>PGRES_TUPLES_OK</literal></term>
2221 Successful completion of a command returning data (such as
2222 a <command>SELECT</> or <command>SHOW</>).
2227 <varlistentry id="libpq-pgres-copy-out">
2228 <term><literal>PGRES_COPY_OUT</literal></term>
2231 Copy Out (from server) data transfer started.
2236 <varlistentry id="libpq-pgres-copy-in">
2237 <term><literal>PGRES_COPY_IN</literal></term>
2240 Copy In (to server) data transfer started.
2245 <varlistentry id="libpq-pgres-bad-response">
2246 <term><literal>PGRES_BAD_RESPONSE</literal></term>
2249 The server's response was not understood.
2254 <varlistentry id="libpq-pgres-nonfatal-error">
2255 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
2258 A nonfatal error (a notice or warning) occurred.
2263 <varlistentry id="libpq-pgres-fatal-error">
2264 <term><literal>PGRES_FATAL_ERROR</literal></term>
2267 A fatal error occurred.
2272 <varlistentry id="libpq-pgres-copy-both">
2273 <term><literal>PGRES_COPY_BOTH</literal></term>
2276 Copy In/Out (to and from server) data transfer started. This is
2277 currently used only for streaming replication.
2283 If the result status is <literal>PGRES_TUPLES_OK</literal>, then
2284 the functions described below can be used to retrieve the rows
2285 returned by the query. Note that a <command>SELECT</command>
2286 command that happens to retrieve zero rows still shows
2287 <literal>PGRES_TUPLES_OK</literal>.
2288 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
2289 return rows (<command>INSERT</command>, <command>UPDATE</command>,
2290 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
2291 indicate a bug in the client software.
2295 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
2296 never be returned directly by <function>PQexec</function> or other
2297 query execution functions; results of this kind are instead passed
2298 to the notice processor (see <xref
2299 linkend="libpq-notice-processing">).
2304 <varlistentry id="libpq-pqresstatus">
2306 <function>PQresStatus</function>
2308 <primary>PQresStatus</primary>
2314 Converts the enumerated type returned by
2315 <function>PQresultStatus</> into a string constant describing the
2316 status code. The caller should not free the result.
2319 char *PQresStatus(ExecStatusType status);
2325 <varlistentry id="libpq-pqresulterrormessage">
2327 <function>PQresultErrorMessage</function>
2329 <primary>PQresultErrorMessage</primary>
2335 Returns the error message associated with the command, or an empty string
2336 if there was no error.
2338 char *PQresultErrorMessage(const PGresult *res);
2340 If there was an error, the returned string will include a trailing
2341 newline. The caller should not free the result directly. It will
2342 be freed when the associated <structname>PGresult</> handle is
2343 passed to <function>PQclear</function>.
2347 Immediately following a <function>PQexec</function> or
2348 <function>PQgetResult</function> call,
2349 <function>PQerrorMessage</function> (on the connection) will return
2350 the same string as <function>PQresultErrorMessage</function> (on
2351 the result). However, a <structname>PGresult</structname> will
2352 retain its error message until destroyed, whereas the connection's
2353 error message will change when subsequent operations are done.
2354 Use <function>PQresultErrorMessage</function> when you want to
2355 know the status associated with a particular
2356 <structname>PGresult</structname>; use
2357 <function>PQerrorMessage</function> when you want to know the
2358 status from the latest operation on the connection.
2363 <varlistentry id="libpq-pqresulterrorfield">
2364 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
2367 Returns an individual field of an error report.
2369 char *PQresultErrorField(const PGresult *res, int fieldcode);
2371 <parameter>fieldcode</> is an error field identifier; see the symbols
2372 listed below. <symbol>NULL</symbol> is returned if the
2373 <structname>PGresult</structname> is not an error or warning result,
2374 or does not include the specified field. Field values will normally
2375 not include a trailing newline. The caller should not free the
2376 result directly. It will be freed when the
2377 associated <structname>PGresult</> handle is passed to
2378 <function>PQclear</function>.
2382 The following field codes are available:
2384 <varlistentry id="libpq-pg-diag-severity">
2385 <term><symbol>PG_DIAG_SEVERITY</></term>
2388 The severity; the field contents are <literal>ERROR</>,
2389 <literal>FATAL</>, or <literal>PANIC</> (in an error message),
2390 or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
2391 <literal>INFO</>, or <literal>LOG</> (in a notice message), or
2392 a localized translation of one of these. Always present.
2397 <varlistentry id="libpq-pg-diag-sqlstate">
2399 <primary>error codes</primary>
2400 <secondary>libpq</secondary>
2402 <term><symbol>PG_DIAG_SQLSTATE</></term>
2405 The SQLSTATE code for the error. The SQLSTATE code identifies
2406 the type of error that has occurred; it can be used by
2407 front-end applications to perform specific operations (such
2408 as error handling) in response to a particular database error.
2409 For a list of the possible SQLSTATE codes, see <xref
2410 linkend="errcodes-appendix">. This field is not localizable,
2411 and is always present.
2416 <varlistentry id="libpq-pg-diag-message-primary">
2417 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
2420 The primary human-readable error message (typically one line).
2426 <varlistentry id="libpq-pg-diag-message-detail">
2427 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
2430 Detail: an optional secondary error message carrying more
2431 detail about the problem. Might run to multiple lines.
2436 <varlistentry id="libpq-pg-diag-message-hint">
2437 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
2440 Hint: an optional suggestion what to do about the problem.
2441 This is intended to differ from detail in that it offers advice
2442 (potentially inappropriate) rather than hard facts. Might
2443 run to multiple lines.
2448 <varlistentry id="libpq-pg-diag-statement-position">
2449 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
2452 A string containing a decimal integer indicating an error cursor
2453 position as an index into the original statement string. The
2454 first character has index 1, and positions are measured in
2455 characters not bytes.
2460 <varlistentry id="libpq-pg-diag-internal-position">
2461 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
2464 This is defined the same as the
2465 <symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
2466 when the cursor position refers to an internally generated
2467 command rather than the one submitted by the client. The
2468 <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
2474 <varlistentry id="libpq-pg-diag-internal-query">
2475 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
2478 The text of a failed internally-generated command. This could
2479 be, for example, a SQL query issued by a PL/pgSQL function.
2484 <varlistentry id="libpq-pg-diag-context">
2485 <term><symbol>PG_DIAG_CONTEXT</></term>
2488 An indication of the context in which the error occurred.
2489 Presently this includes a call stack traceback of active
2490 procedural language functions and internally-generated queries.
2491 The trace is one entry per line, most recent first.
2496 <varlistentry id="libpq-pg-diag-source-file">
2497 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
2500 The file name of the source-code location where the error was
2506 <varlistentry id="libpq-pg-diag-source-line">
2507 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
2510 The line number of the source-code location where the error
2516 <varlistentry id="libpq-pg-diag-source-function">
2517 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
2520 The name of the source-code function reporting the error.
2528 The client is responsible for formatting displayed information to meet
2529 its needs; in particular it should break long lines as needed.
2530 Newline characters appearing in the error message fields should be
2531 treated as paragraph breaks, not line breaks.
2535 Errors generated internally by <application>libpq</application> will
2536 have severity and primary message, but typically no other fields.
2537 Errors returned by a pre-3.0-protocol server will include severity and
2538 primary message, and sometimes a detail message, but no other fields.
2542 Note that error fields are only available from
2543 <structname>PGresult</structname> objects, not
2544 <structname>PGconn</structname> objects; there is no
2545 <function>PQerrorField</function> function.
2550 <varlistentry id="libpq-pqclear">
2551 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
2554 Frees the storage associated with a
2555 <structname>PGresult</structname>. Every command result should be
2556 freed via <function>PQclear</function> when it is no longer
2560 void PQclear(PGresult *res);
2565 You can keep a <structname>PGresult</structname> object around for
2566 as long as you need it; it does not go away when you issue a new
2567 command, nor even if you close the connection. To get rid of it,
2568 you must call <function>PQclear</function>. Failure to do this
2569 will result in memory leaks in your application.
2577 <sect2 id="libpq-exec-select-info">
2578 <title>Retrieving Query Result Information</title>
2581 These functions are used to extract information from a
2582 <structname>PGresult</structname> object that represents a successful
2583 query result (that is, one that has status
2584 <literal>PGRES_TUPLES_OK</literal>). They can also be used to extract
2585 information from a successful Describe operation: a Describe's result
2586 has all the same column information that actual execution of the query
2587 would provide, but it has zero rows. For objects with other status values,
2588 these functions will act as though the result has zero rows and zero columns.
2592 <varlistentry id="libpq-pqntuples">
2594 <function>PQntuples</function>
2596 <primary>PQntuples</primary>
2602 Returns the number of rows (tuples) in the query result. Because
2603 it returns an integer result, large result sets might overflow the
2604 return value on 32-bit operating systems.
2607 int PQntuples(const PGresult *res);
2614 <varlistentry id="libpq-pqnfields">
2616 <function>PQnfields</function>
2618 <primary>PQnfields</primary>
2624 Returns the number of columns (fields) in each row of the query
2628 int PQnfields(const PGresult *res);
2634 <varlistentry id="libpq-pqfname">
2636 <function>PQfname</function>
2638 <primary>PQfname</primary>
2644 Returns the column name associated with the given column number.
2645 Column numbers start at 0. The caller should not free the result
2646 directly. It will be freed when the associated
2647 <structname>PGresult</> handle is passed to
2648 <function>PQclear</function>.
2650 char *PQfname(const PGresult *res,
2656 <symbol>NULL</symbol> is returned if the column number is out of range.
2661 <varlistentry id="libpq-pqfnumber">
2663 <function>PQfnumber</function>
2665 <primary>PQfnumber</primary>
2671 Returns the column number associated with the given column name.
2673 int PQfnumber(const PGresult *res,
2674 const char *column_name);
2679 -1 is returned if the given name does not match any column.
2683 The given name is treated like an identifier in an SQL command,
2684 that is, it is downcased unless double-quoted. For example, given
2685 a query result generated from the SQL command:
2687 SELECT 1 AS FOO, 2 AS "BAR";
2689 we would have the results:
2691 PQfname(res, 0) <lineannotation>foo</lineannotation>
2692 PQfname(res, 1) <lineannotation>BAR</lineannotation>
2693 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
2694 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
2695 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
2696 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
2702 <varlistentry id="libpq-pqftable">
2704 <function>PQftable</function>
2706 <primary>PQftable</primary>
2712 Returns the OID of the table from which the given column was
2713 fetched. Column numbers start at 0.
2715 Oid PQftable(const PGresult *res,
2721 <literal>InvalidOid</> is returned if the column number is out of range,
2722 or if the specified column is not a simple reference to a table column,
2723 or when using pre-3.0 protocol.
2724 You can query the system table <literal>pg_class</literal> to determine
2725 exactly which table is referenced.
2729 The type <type>Oid</type> and the constant
2730 <literal>InvalidOid</literal> will be defined when you include
2731 the <application>libpq</application> header file. They will both
2732 be some integer type.
2737 <varlistentry id="libpq-pqftablecol">
2739 <function>PQftablecol</function>
2741 <primary>PQftablecol</primary>
2747 Returns the column number (within its table) of the column making
2748 up the specified query result column. Query-result column numbers
2749 start at 0, but table columns have nonzero numbers.
2751 int PQftablecol(const PGresult *res,
2757 Zero is returned if the column number is out of range, or if the
2758 specified column is not a simple reference to a table column, or
2759 when using pre-3.0 protocol.
2764 <varlistentry id="libpq-pqfformat">
2766 <function>PQfformat</function>
2768 <primary>PQfformat</primary>
2774 Returns the format code indicating the format of the given
2775 column. Column numbers start at 0.
2777 int PQfformat(const PGresult *res,
2783 Format code zero indicates textual data representation, while format
2784 code one indicates binary representation. (Other codes are reserved
2785 for future definition.)
2790 <varlistentry id="libpq-pqftype">
2792 <function>PQftype</function>
2794 <primary>PQftype</primary>
2800 Returns the data type associated with the given column number.
2801 The integer returned is the internal OID number of the type.
2802 Column numbers start at 0.
2804 Oid PQftype(const PGresult *res,
2810 You can query the system table <literal>pg_type</literal> to
2811 obtain the names and properties of the various data types. The
2812 <acronym>OID</acronym>s of the built-in data types are defined
2813 in the file <filename>src/include/catalog/pg_type.h</filename>
2819 <varlistentry id="libpq-pqfmod">
2821 <function>PQfmod</function>
2823 <primary>PQfmod</primary>
2829 Returns the type modifier of the column associated with the
2830 given column number. Column numbers start at 0.
2832 int PQfmod(const PGresult *res,
2838 The interpretation of modifier values is type-specific; they
2839 typically indicate precision or size limits. The value -1 is
2840 used to indicate <quote>no information available</>. Most data
2841 types do not use modifiers, in which case the value is always
2847 <varlistentry id="libpq-pqfsize">
2849 <function>PQfsize</function>
2851 <primary>PQfsize</primary>
2857 Returns the size in bytes of the column associated with the
2858 given column number. Column numbers start at 0.
2860 int PQfsize(const PGresult *res,
2866 <function>PQfsize</> returns the space allocated for this column
2867 in a database row, in other words the size of the server's
2868 internal representation of the data type. (Accordingly, it is
2869 not really very useful to clients.) A negative value indicates
2870 the data type is variable-length.
2875 <varlistentry id="libpq-pqbinarytuples">
2877 <function>PQbinaryTuples</function>
2879 <primary>PQbinaryTuples</primary>
2885 Returns 1 if the <structname>PGresult</> contains binary data
2886 and 0 if it contains text data.
2888 int PQbinaryTuples(const PGresult *res);
2893 This function is deprecated (except for its use in connection with
2894 <command>COPY</>), because it is possible for a single
2895 <structname>PGresult</> to contain text data in some columns and
2896 binary data in others. <function>PQfformat</> is preferred.
2897 <function>PQbinaryTuples</> returns 1 only if all columns of the
2898 result are binary (format 1).
2903 <varlistentry id="libpq-pqgetvalue">
2905 <function>PQgetvalue</function>
2907 <primary>PQgetvalue</primary>
2913 Returns a single field value of one row of a
2914 <structname>PGresult</structname>. Row and column numbers start
2915 at 0. The caller should not free the result directly. It will
2916 be freed when the associated <structname>PGresult</> handle is
2917 passed to <function>PQclear</function>.
2919 char *PQgetvalue(const PGresult *res,
2926 For data in text format, the value returned by
2927 <function>PQgetvalue</function> is a null-terminated character
2928 string representation of the field value. For data in binary
2929 format, the value is in the binary representation determined by
2930 the data type's <function>typsend</> and <function>typreceive</>
2931 functions. (The value is actually followed by a zero byte in
2932 this case too, but that is not ordinarily useful, since the
2933 value is likely to contain embedded nulls.)
2937 An empty string is returned if the field value is null. See
2938 <function>PQgetisnull</> to distinguish null values from
2939 empty-string values.
2943 The pointer returned by <function>PQgetvalue</function> points
2944 to storage that is part of the <structname>PGresult</structname>
2945 structure. One should not modify the data it points to, and one
2946 must explicitly copy the data into other storage if it is to be
2947 used past the lifetime of the <structname>PGresult</structname>
2953 <varlistentry id="libpq-pqgetisnull">
2955 <function>PQgetisnull</function>
2957 <primary>PQgetisnull</primary>
2960 <primary>null value</primary>
2961 <secondary sortas="libpq">in libpq</secondary>
2967 Tests a field for a null value. Row and column numbers start
2970 int PQgetisnull(const PGresult *res,
2977 This function returns 1 if the field is null and 0 if it
2978 contains a non-null value. (Note that
2979 <function>PQgetvalue</function> will return an empty string,
2980 not a null pointer, for a null field.)
2985 <varlistentry id="libpq-pqgetlength">
2987 <function>PQgetlength</function>
2989 <primary>PQgetlength</primary>
2994 Returns the actual length of a field value in bytes. Row and
2995 column numbers start at 0.
2997 int PQgetlength(const PGresult *res,
3004 This is the actual data length for the particular data value,
3005 that is, the size of the object pointed to by
3006 <function>PQgetvalue</function>. For text data format this is
3007 the same as <function>strlen()</>. For binary format this is
3008 essential information. Note that one should <emphasis>not</>
3009 rely on <function>PQfsize</function> to obtain the actual data
3015 <varlistentry id="libpq-pqnparams">
3017 <function>PQnparams</function>
3019 <primary>PQnparams</primary>
3025 Returns the number of parameters of a prepared statement.
3027 int PQnparams(const PGresult *res);
3032 This function is only useful when inspecting the result of
3033 <function>PQdescribePrepared</>. For other types of queries it
3039 <varlistentry id="libpq-pqparamtype">
3041 <function>PQparamtype</function>
3043 <primary>PQparamtype</primary>
3049 Returns the data type of the indicated statement parameter.
3050 Parameter numbers start at 0.
3052 Oid PQparamtype(const PGresult *res, int param_number);
3057 This function is only useful when inspecting the result of
3058 <function>PQdescribePrepared</>. For other types of queries it
3064 <varlistentry id="libpq-pqprint">
3066 <function>PQprint</function>
3068 <primary>PQprint</primary>
3074 Prints out all the rows and, optionally, the column names to
3075 the specified output stream.
3077 void PQprint(FILE *fout, /* output stream */
3078 const PGresult *res,
3079 const PQprintOpt *po);
3082 pqbool header; /* print output field headings and row count */
3083 pqbool align; /* fill align the fields */
3084 pqbool standard; /* old brain dead format */
3085 pqbool html3; /* output HTML tables */
3086 pqbool expanded; /* expand tables */
3087 pqbool pager; /* use pager for output if needed */
3088 char *fieldSep; /* field separator */
3089 char *tableOpt; /* attributes for HTML table element */
3090 char *caption; /* HTML table caption */
3091 char **fieldName; /* null-terminated array of replacement field names */
3097 This function was formerly used by <application>psql</application>
3098 to print query results, but this is no longer the case. Note
3099 that it assumes all the data is in text format.
3106 <sect2 id="libpq-exec-nonselect">
3107 <title>Retrieving Other Result Information</title>
3110 These functions are used to extract other information from
3111 <structname>PGresult</structname> objects.
3115 <varlistentry id="libpq-pqcmdstatus">
3117 <function>PQcmdStatus</function>
3119 <primary>PQcmdStatus</primary>
3125 Returns the command status tag from the SQL command that generated
3126 the <structname>PGresult</structname>.
3128 char *PQcmdStatus(PGresult *res);
3133 Commonly this is just the name of the command, but it might include
3134 additional data such as the number of rows processed. The caller
3135 should not free the result directly. It will be freed when the
3136 associated <structname>PGresult</> handle is passed to
3137 <function>PQclear</function>.
3142 <varlistentry id="libpq-pqcmdtuples">
3144 <function>PQcmdTuples</function>
3146 <primary>PQcmdTuples</primary>
3152 Returns the number of rows affected by the SQL command.
3154 char *PQcmdTuples(PGresult *res);
3159 This function returns a string containing the number of rows
3160 affected by the <acronym>SQL</> statement that generated the
3161 <structname>PGresult</>. This function can only be used following
3162 the execution of a <command>SELECT</>, <command>CREATE TABLE AS</>,
3163 <command>INSERT</>, <command>UPDATE</>, <command>DELETE</>,
3164 <command>MOVE</>, <command>FETCH</>, or <command>COPY</> statement,
3165 or an <command>EXECUTE</> of a prepared query that contains an
3166 <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</> statement.
3167 If the command that generated the <structname>PGresult</> was anything
3168 else, <function>PQcmdTuples</> returns an empty string. The caller
3169 should not free the return value directly. It will be freed when
3170 the associated <structname>PGresult</> handle is passed to
3171 <function>PQclear</function>.
3176 <varlistentry id="libpq-pqoidvalue">
3178 <function>PQoidValue</function>
3180 <primary>PQoidValue</primary>
3186 Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
3187 of the inserted row, if the <acronym>SQL</> command was an
3188 <command>INSERT</> that inserted exactly one row into a table that
3189 has OIDs, or a <command>EXECUTE</> of a prepared query containing
3190 a suitable <command>INSERT</> statement. Otherwise, this function
3191 returns <literal>InvalidOid</literal>. This function will also
3192 return <literal>InvalidOid</literal> if the table affected by the
3193 <command>INSERT</> statement does not contain OIDs.
3195 Oid PQoidValue(const PGresult *res);
3201 <varlistentry id="libpq-pqoidstatus">
3203 <function>PQoidStatus</function>
3205 <primary>PQoidStatus</primary>
3211 This function is deprecated in favor of
3212 <function>PQoidValue</function> and is not thread-safe.
3213 It returns a string with the OID of the inserted row, while
3214 <function>PQoidValue</function> returns the OID value.
3216 char *PQoidStatus(const PGresult *res);
3226 <sect2 id="libpq-exec-escape-string">
3227 <title>Escaping Strings for Inclusion in SQL Commands</title>
3229 <indexterm zone="libpq-exec-escape-string">
3230 <primary>escaping strings</primary>
3231 <secondary>in libpq</secondary>
3235 <varlistentry id="libpq-pqescapeliteral">
3237 <function>PQescapeLiteral</function>
3239 <primary>PQescapeLiteral</primary>
3246 char *PQescapeLiteral(PGconn *conn, const char *str, size_t length);
3251 <function>PQescapeLiteral</function> escapes a string for
3252 use within an SQL command. This is useful when inserting data
3253 values as literal constants in SQL commands. Certain characters
3254 (such as quotes and backslashes) must be escaped to prevent them
3255 from being interpreted specially by the SQL parser.
3256 <function>PQescapeLiteral</> performs this operation.
3260 <function>PQescapeLiteral</> returns an escaped version of the
3261 <parameter>str</parameter> parameter in memory allocated with
3262 <function>malloc()</>. This memory should be freed using
3263 <function>PQfreemem()</> when the result is no longer needed.
3264 A terminating zero byte is not required, and should not be
3265 counted in <parameter>length</>. (If a terminating zero byte is found
3266 before <parameter>length</> bytes are processed,
3267 <function>PQescapeLiteral</> stops at the zero; the behavior is
3268 thus rather like <function>strncpy</>.) The
3269 return string has all special characters replaced so that they can
3270 be properly processed by the <productname>PostgreSQL</productname>
3271 string literal parser. A terminating zero byte is also added. The
3272 single quotes that must surround <productname>PostgreSQL</productname>
3273 string literals are included in the result string.
3277 On error, <function>PQescapeLiteral</> returns <symbol>NULL</> and a suitable
3278 message is stored in the <parameter>conn</> object.
3283 It is especially important to do proper escaping when handling
3284 strings that were received from an untrustworthy source.
3285 Otherwise there is a security risk: you are vulnerable to
3286 <quote>SQL injection</> attacks wherein unwanted SQL commands are
3287 fed to your database.
3292 Note that it is not necessary nor correct to do escaping when a data
3293 value is passed as a separate parameter in <function>PQexecParams</> or
3294 its sibling routines.
3299 <varlistentry id="libpq-pqescapeidentifier">
3301 <function>PQescapeIdentifier</function>
3303 <primary>PQescapeIdentifier</primary>
3310 char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length);
3315 <function>PQescapeIdentifier</function> escapes a string for
3316 use as an SQL identifier, such as a table, column, or function name.
3317 This is useful when a user-supplied identifier might contain
3318 special characters that would otherwise not be interpreted as part
3319 of the identifier by the SQL parser, or when the identifier might
3320 contain upper case characters whose case should be preserved.
3324 <function>PQescapeIdentifier</> returns a version of the
3325 <parameter>str</parameter> parameter escaped as an SQL identifier
3326 in memory allocated with <function>malloc()</>. This memory must be
3327 freed using <function>PQfreemem()</> when the result is no longer
3328 needed. A terminating zero byte is not required, and should not be
3329 counted in <parameter>length</>. (If a terminating zero byte is found
3330 before <parameter>length</> bytes are processed,
3331 <function>PQescapeIdentifier</> stops at the zero; the behavior is
3332 thus rather like <function>strncpy</>.) The
3333 return string has all special characters replaced so that it
3334 will be properly processed as an SQL identifier. A terminating zero byte
3335 is also added. The return string will also be surrounded by double
3340 On error, <function>PQescapeIdentifier</> returns <symbol>NULL</> and a suitable
3341 message is stored in the <parameter>conn</> object.
3346 As with string literals, to prevent SQL injection attacks,
3347 SQL identifiers must be escaped when they are received from an
3348 untrustworthy source.
3354 <varlistentry id="libpq-pqescapestringconn">
3356 <function>PQescapeStringConn</function>
3358 <primary>PQescapeStringConn</primary>
3365 size_t PQescapeStringConn(PGconn *conn,
3366 char *to, const char *from, size_t length,
3372 <function>PQescapeStringConn</> escapes string literals, much like
3373 <function>PQescapeLiteral</>. Unlike <function>PQescapeLiteral</>,
3374 the caller is responsible for providing an appropriately sized buffer.
3375 Furthermore, <function>PQescapeStringConn</> does not generate the
3376 single quotes that must surround <productname>PostgreSQL</> string
3377 literals; they should be provided in the SQL command that the
3378 result is inserted into. The parameter <parameter>from</> points to
3379 the first character of the string that is to be escaped, and the
3380 <parameter>length</> parameter gives the number of bytes in this
3381 string. A terminating zero byte is not required, and should not be
3382 counted in <parameter>length</>. (If a terminating zero byte is found
3383 before <parameter>length</> bytes are processed,
3384 <function>PQescapeStringConn</> stops at the zero; the behavior is
3385 thus rather like <function>strncpy</>.) <parameter>to</> shall point
3386 to a buffer that is able to hold at least one more byte than twice
3387 the value of <parameter>length</>, otherwise the behavior is undefined.
3388 Behavior is likewise undefined if the <parameter>to</> and
3389 <parameter>from</> strings overlap.
3393 If the <parameter>error</> parameter is not <symbol>NULL</>, then
3394 <literal>*error</> is set to zero on success, nonzero on error.
3395 Presently the only possible error conditions involve invalid multibyte
3396 encoding in the source string. The output string is still generated
3397 on error, but it can be expected that the server will reject it as
3398 malformed. On error, a suitable message is stored in the
3399 <parameter>conn</> object, whether or not <parameter>error</> is <symbol>NULL</>.
3403 <function>PQescapeStringConn</> returns the number of bytes written
3404 to <parameter>to</>, not including the terminating zero byte.
3409 <varlistentry id="libpq-pqescapestring">
3411 <function>PQescapeString</function>
3413 <primary>PQescapeString</primary>
3419 <function>PQescapeString</> is an older, deprecated version of
3420 <function>PQescapeStringConn</>.
3422 size_t PQescapeString (char *to, const char *from, size_t length);
3427 The only difference from <function>PQescapeStringConn</> is that
3428 <function>PQescapeString</> does not take <structname>PGconn</>
3429 or <parameter>error</> parameters.
3430 Because of this, it cannot adjust its behavior depending on the
3431 connection properties (such as character encoding) and therefore
3432 <emphasis>it might give the wrong results</>. Also, it has no way
3433 to report error conditions.
3437 <function>PQescapeString</> can be used safely in
3438 client programs that work with only one <productname>PostgreSQL</>
3439 connection at a time (in this case it can find out what it needs to
3440 know <quote>behind the scenes</>). In other contexts it is a security
3441 hazard and should be avoided in favor of
3442 <function>PQescapeStringConn</>.
3447 <varlistentry id="libpq-pqescapebyteaconn">
3449 <function>PQescapeByteaConn</function>
3451 <primary>PQescapeByteaConn</primary>
3457 Escapes binary data for use within an SQL command with the type
3458 <type>bytea</type>. As with <function>PQescapeStringConn</function>,
3459 this is only used when inserting data directly into an SQL command string.
3461 unsigned char *PQescapeByteaConn(PGconn *conn,
3462 const unsigned char *from,
3469 Certain byte values must be escaped when used as part of a
3470 <type>bytea</type> literal in an <acronym>SQL</acronym> statement.
3471 <function>PQescapeByteaConn</function> escapes bytes using
3472 either hex encoding or backslash escaping. See <xref
3473 linkend="datatype-binary"> for more information.
3477 The <parameter>from</parameter> parameter points to the first
3478 byte of the string that is to be escaped, and the
3479 <parameter>from_length</parameter> parameter gives the number of
3480 bytes in this binary string. (A terminating zero byte is
3481 neither necessary nor counted.) The <parameter>to_length</parameter>
3482 parameter points to a variable that will hold the resultant
3483 escaped string length. This result string length includes the terminating
3484 zero byte of the result.
3488 <function>PQescapeByteaConn</> returns an escaped version of the
3489 <parameter>from</parameter> parameter binary string in memory
3490 allocated with <function>malloc()</>. This memory should be freed using
3491 <function>PQfreemem()</> when the result is no longer needed. The
3492 return string has all special characters replaced so that they can
3493 be properly processed by the <productname>PostgreSQL</productname>
3494 string literal parser, and the <type>bytea</type> input function. A
3495 terminating zero byte is also added. The single quotes that must
3496 surround <productname>PostgreSQL</productname> string literals are
3497 not part of the result string.
3501 On error, a null pointer is returned, and a suitable error message
3502 is stored in the <parameter>conn</> object. Currently, the only
3503 possible error is insufficient memory for the result string.
3508 <varlistentry id="libpq-pqescapebytea">
3510 <function>PQescapeBytea</function>
3512 <primary>PQescapeBytea</primary>
3518 <function>PQescapeBytea</> is an older, deprecated version of
3519 <function>PQescapeByteaConn</>.
3521 unsigned char *PQescapeBytea(const unsigned char *from,
3528 The only difference from <function>PQescapeByteaConn</> is that
3529 <function>PQescapeBytea</> does not take a <structname>PGconn</>
3530 parameter. Because of this, <function>PQescapeBytea</> can
3531 only be used safely in client programs that use a single
3532 <productname>PostgreSQL</> connection at a time (in this case
3533 it can find out what it needs to know <quote>behind the
3534 scenes</>). It <emphasis>might give the wrong results</> if
3535 used in programs that use multiple database connections (use
3536 <function>PQescapeByteaConn</> in such cases).
3541 <varlistentry id="libpq-pqunescapebytea">
3543 <function>PQunescapeBytea</function>
3545 <primary>PQunescapeBytea</primary>
3551 Converts a string representation of binary data into binary data
3552 — the reverse of <function>PQescapeBytea</function>. This
3553 is needed when retrieving <type>bytea</type> data in text format,
3554 but not when retrieving it in binary format.
3557 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
3562 The <parameter>from</parameter> parameter points to a string
3563 such as might be returned by <function>PQgetvalue</function> when applied
3564 to a <type>bytea</type> column. <function>PQunescapeBytea</function>
3565 converts this string representation into its binary representation.
3566 It returns a pointer to a buffer allocated with
3567 <function>malloc()</function>, or <symbol>NULL</> on error, and puts the size of
3568 the buffer in <parameter>to_length</parameter>. The result must be
3569 freed using <function>PQfreemem</> when it is no longer needed.
3573 This conversion is not exactly the inverse of
3574 <function>PQescapeBytea</function>, because the string is not expected
3575 to be <quote>escaped</> when received from <function>PQgetvalue</function>.
3576 In particular this means there is no need for string quoting considerations,
3577 and so no need for a <structname>PGconn</> parameter.
3587 <sect1 id="libpq-async">
3588 <title>Asynchronous Command Processing</title>
3590 <indexterm zone="libpq-async">
3591 <primary>nonblocking connection</primary>
3595 The <function>PQexec</function> function is adequate for submitting
3596 commands in normal, synchronous applications. It has a couple of
3597 deficiencies, however, that can be of importance to some users:
3602 <function>PQexec</function> waits for the command to be completed.
3603 The application might have other work to do (such as maintaining a
3604 user interface), in which case it won't want to block waiting for
3611 Since the execution of the client application is suspended while it
3612 waits for the result, it is hard for the application to decide that
3613 it would like to try to cancel the ongoing command. (It can be done
3614 from a signal handler, but not otherwise.)
3620 <function>PQexec</function> can return only one
3621 <structname>PGresult</structname> structure. If the submitted command
3622 string contains multiple <acronym>SQL</acronym> commands, all but
3623 the last <structname>PGresult</structname> are discarded by
3624 <function>PQexec</function>.
3631 Applications that do not like these limitations can instead use the
3632 underlying functions that <function>PQexec</function> is built from:
3633 <function>PQsendQuery</function> and <function>PQgetResult</function>.
3635 <function>PQsendQueryParams</function>,
3636 <function>PQsendPrepare</function>,
3637 <function>PQsendQueryPrepared</function>,
3638 <function>PQsendDescribePrepared</function>, and
3639 <function>PQsendDescribePortal</function>,
3640 which can be used with <function>PQgetResult</function> to duplicate
3641 the functionality of
3642 <function>PQexecParams</function>,
3643 <function>PQprepare</function>,
3644 <function>PQexecPrepared</function>,
3645 <function>PQdescribePrepared</function>, and
3646 <function>PQdescribePortal</function>
3650 <varlistentry id="libpq-pqsendquery">
3652 <function>PQsendQuery</function>
3654 <primary>PQsendQuery</primary>
3660 Submits a command to the server without waiting for the result(s).
3661 1 is returned if the command was successfully dispatched and 0 if
3662 not (in which case, use <function>PQerrorMessage</> to get more
3663 information about the failure).
3665 int PQsendQuery(PGconn *conn, const char *command);
3668 After successfully calling <function>PQsendQuery</function>, call
3669 <function>PQgetResult</function> one or more times to obtain the
3670 results. <function>PQsendQuery</function> cannot be called again
3671 (on the same connection) until <function>PQgetResult</function>
3672 has returned a null pointer, indicating that the command is done.
3677 <varlistentry id="libpq-pqsendqueryparams">
3679 <function>PQsendQueryParams</function>
3681 <primary>PQsendQueryParams</primary>
3687 Submits a command and separate parameters to the server without
3688 waiting for the result(s).
3690 int PQsendQueryParams(PGconn *conn,
3691 const char *command,
3693 const Oid *paramTypes,
3694 const char * const *paramValues,
3695 const int *paramLengths,
3696 const int *paramFormats,
3700 This is equivalent to <function>PQsendQuery</function> except that
3701 query parameters can be specified separately from the query string.
3702 The function's parameters are handled identically to
3703 <function>PQexecParams</function>. Like
3704 <function>PQexecParams</function>, it will not work on 2.0-protocol
3705 connections, and it allows only one command in the query string.
3710 <varlistentry id="libpq-pqsendprepare">
3712 <function>PQsendPrepare</>
3714 <primary>PQsendPrepare</primary>
3720 Sends a request to create a prepared statement with the given
3721 parameters, without waiting for completion.
3723 int PQsendPrepare(PGconn *conn,
3724 const char *stmtName,
3727 const Oid *paramTypes);
3730 This is an asynchronous version of <function>PQprepare</>: it
3731 returns 1 if it was able to dispatch the request, and 0 if not.
3732 After a successful call, call <function>PQgetResult</function> to
3733 determine whether the server successfully created the prepared
3734 statement. The function's parameters are handled identically to
3735 <function>PQprepare</function>. Like
3736 <function>PQprepare</function>, it will not work on 2.0-protocol
3742 <varlistentry id="libpq-pqsendqueryprepared">
3744 <function>PQsendQueryPrepared</function>
3746 <primary>PQsendQueryPrepared</primary>
3752 Sends a request to execute a prepared statement with given
3753 parameters, without waiting for the result(s).
3755 int PQsendQueryPrepared(PGconn *conn,
3756 const char *stmtName,
3758 const char * const *paramValues,
3759 const int *paramLengths,
3760 const int *paramFormats,
3764 This is similar to <function>PQsendQueryParams</function>, but
3765 the command to be executed is specified by naming a
3766 previously-prepared statement, instead of giving a query string.
3767 The function's parameters are handled identically to
3768 <function>PQexecPrepared</function>. Like
3769 <function>PQexecPrepared</function>, it will not work on
3770 2.0-protocol connections.
3775 <varlistentry id="libpq-pqsenddescribeprepared">
3777 <function>PQsendDescribePrepared</>
3779 <primary>PQsendDescribePrepared</primary>
3785 Submits a request to obtain information about the specified
3786 prepared statement, without waiting for completion.
3788 int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
3791 This is an asynchronous version of <function>PQdescribePrepared</>:
3792 it returns 1 if it was able to dispatch the request, and 0 if not.
3793 After a successful call, call <function>PQgetResult</function> to
3794 obtain the results. The function's parameters are handled
3795 identically to <function>PQdescribePrepared</function>. Like
3796 <function>PQdescribePrepared</function>, it will not work on
3797 2.0-protocol connections.
3802 <varlistentry id="libpq-pqsenddescribeportal">
3804 <function>PQsendDescribePortal</>
3806 <primary>PQsendDescribePortal</primary>
3812 Submits a request to obtain information about the specified
3813 portal, without waiting for completion.
3815 int PQsendDescribePortal(PGconn *conn, const char *portalName);
3818 This is an asynchronous version of <function>PQdescribePortal</>:
3819 it returns 1 if it was able to dispatch the request, and 0 if not.
3820 After a successful call, call <function>PQgetResult</function> to
3821 obtain the results. The function's parameters are handled
3822 identically to <function>PQdescribePortal</function>. Like
3823 <function>PQdescribePortal</function>, it will not work on
3824 2.0-protocol connections.
3829 <varlistentry id="libpq-pqgetresult">
3831 <function>PQgetResult</function>
3833 <primary>PQgetResult</primary>
3839 Waits for the next result from a prior
3840 <function>PQsendQuery</function>,
3841 <function>PQsendQueryParams</function>,
3842 <function>PQsendPrepare</function>, or
3843 <function>PQsendQueryPrepared</function> call, and returns it.
3844 A null pointer is returned when the command is complete and there
3845 will be no more results.
3847 PGresult *PQgetResult(PGconn *conn);
3852 <function>PQgetResult</function> must be called repeatedly until
3853 it returns a null pointer, indicating that the command is done.
3854 (If called when no command is active,
3855 <function>PQgetResult</function> will just return a null pointer
3856 at once.) Each non-null result from
3857 <function>PQgetResult</function> should be processed using the
3858 same <structname>PGresult</> accessor functions previously
3859 described. Don't forget to free each result object with
3860 <function>PQclear</function> when done with it. Note that
3861 <function>PQgetResult</function> will block only if a command is
3862 active and the necessary response data has not yet been read by
3863 <function>PQconsumeInput</function>.
3868 Even when <function>PQresultStatus</function> indicates a fatal
3869 error, <function>PQgetResult</function> should be called until it
3870 returns a null pointer to allow <application>libpq</> to
3871 process the error information completely.
3880 Using <function>PQsendQuery</function> and
3881 <function>PQgetResult</function> solves one of
3882 <function>PQexec</function>'s problems: If a command string contains
3883 multiple <acronym>SQL</acronym> commands, the results of those commands
3884 can be obtained individually. (This allows a simple form of overlapped
3885 processing, by the way: the client can be handling the results of one
3886 command while the server is still working on later queries in the same
3887 command string.) However, calling <function>PQgetResult</function>
3888 will still cause the client to block until the server completes the
3889 next <acronym>SQL</acronym> command. This can be avoided by proper
3890 use of two more functions:
3893 <varlistentry id="libpq-pqconsumeinput">
3895 <function>PQconsumeInput</function>
3897 <primary>PQconsumeInput</primary>
3903 If input is available from the server, consume it.
3905 int PQconsumeInput(PGconn *conn);
3910 <function>PQconsumeInput</function> normally returns 1 indicating
3911 <quote>no error</quote>, but returns 0 if there was some kind of
3912 trouble (in which case <function>PQerrorMessage</function> can be
3913 consulted). Note that the result does not say whether any input
3914 data was actually collected. After calling
3915 <function>PQconsumeInput</function>, the application can check
3916 <function>PQisBusy</function> and/or
3917 <function>PQnotifies</function> to see if their state has changed.
3921 <function>PQconsumeInput</function> can be called even if the
3922 application is not prepared to deal with a result or notification
3923 just yet. The function will read available data and save it in
3924 a buffer, thereby causing a <function>select()</function>
3925 read-ready indication to go away. The application can thus use
3926 <function>PQconsumeInput</function> to clear the
3927 <function>select()</function> condition immediately, and then
3928 examine the results at leisure.
3933 <varlistentry id="libpq-pqisbusy">
3935 <function>PQisBusy</function>
3937 <primary>PQisBusy</primary>
3943 Returns 1 if a command is busy, that is,
3944 <function>PQgetResult</function> would block waiting for input.
3945 A 0 return indicates that <function>PQgetResult</function> can be
3946 called with assurance of not blocking.
3948 int PQisBusy(PGconn *conn);
3953 <function>PQisBusy</function> will not itself attempt to read data
3954 from the server; therefore <function>PQconsumeInput</function>
3955 must be invoked first, or the busy state will never end.
3963 A typical application using these functions will have a main loop that
3964 uses <function>select()</function> or <function>poll()</> to wait for
3965 all the conditions that it must respond to. One of the conditions
3966 will be input available from the server, which in terms of
3967 <function>select()</function> means readable data on the file
3968 descriptor identified by <function>PQsocket</function>. When the main
3969 loop detects input ready, it should call
3970 <function>PQconsumeInput</function> to read the input. It can then
3971 call <function>PQisBusy</function>, followed by
3972 <function>PQgetResult</function> if <function>PQisBusy</function>
3973 returns false (0). It can also call <function>PQnotifies</function>
3974 to detect <command>NOTIFY</> messages (see <xref
3975 linkend="libpq-notify">).
3980 <function>PQsendQuery</function>/<function>PQgetResult</function>
3981 can also attempt to cancel a command that is still being processed
3982 by the server; see <xref linkend="libpq-cancel">. But regardless of
3983 the return value of <function>PQcancel</function>, the application
3984 must continue with the normal result-reading sequence using
3985 <function>PQgetResult</function>. A successful cancellation will
3986 simply cause the command to terminate sooner than it would have
3991 By using the functions described above, it is possible to avoid
3992 blocking while waiting for input from the database server. However,
3993 it is still possible that the application will block waiting to send
3994 output to the server. This is relatively uncommon but can happen if
3995 very long SQL commands or data values are sent. (It is much more
3996 probable if the application sends data via <command>COPY IN</command>,
3997 however.) To prevent this possibility and achieve completely
3998 nonblocking database operation, the following additional functions
4002 <varlistentry id="libpq-pqsetnonblocking">
4004 <function>PQsetnonblocking</function>
4006 <primary>PQsetnonblocking</primary>
4012 Sets the nonblocking status of the connection.
4014 int PQsetnonblocking(PGconn *conn, int arg);
4019 Sets the state of the connection to nonblocking if
4020 <parameter>arg</parameter> is 1, or blocking if
4021 <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
4025 In the nonblocking state, calls to
4026 <function>PQsendQuery</function>, <function>PQputline</function>,
4027 <function>PQputnbytes</function>, and
4028 <function>PQendcopy</function> will not block but instead return
4029 an error if they need to be called again.
4033 Note that <function>PQexec</function> does not honor nonblocking
4034 mode; if it is called, it will act in blocking fashion anyway.
4039 <varlistentry id="libpq-pqisnonblocking">
4041 <function>PQisnonblocking</function>
4043 <primary>PQisnonblocking</primary>
4049 Returns the blocking status of the database connection.
4051 int PQisnonblocking(const PGconn *conn);
4056 Returns 1 if the connection is set to nonblocking mode and 0 if
4062 <varlistentry id="libpq-pqflush">
4064 <function>PQflush</function>
4066 <primary>PQflush</primary>
4072 Attempts to flush any queued output data to the server. Returns
4073 0 if successful (or if the send queue is empty), -1 if it failed
4074 for some reason, or 1 if it was unable to send all the data in
4075 the send queue yet (this case can only occur if the connection
4078 int PQflush(PGconn *conn);
4087 After sending any command or data on a nonblocking connection, call
4088 <function>PQflush</function>. If it returns 1, wait for the socket
4089 to be write-ready and call it again; repeat until it returns 0. Once
4090 <function>PQflush</function> returns 0, wait for the socket to be
4091 read-ready and then read the response as described above.
4096 <sect1 id="libpq-cancel">
4097 <title>Canceling Queries in Progress</title>
4099 <indexterm zone="libpq-cancel">
4100 <primary>canceling</primary>
4101 <secondary>SQL command</secondary>
4105 A client application can request cancellation of a command that is
4106 still being processed by the server, using the functions described in
4110 <varlistentry id="libpq-pqgetcancel">
4112 <function>PQgetCancel</function>
4114 <primary>PQgetCancel</primary>
4120 Creates a data structure containing the information needed to cancel
4121 a command issued through a particular database connection.
4123 PGcancel *PQgetCancel(PGconn *conn);
4128 <function>PQgetCancel</function> creates a
4129 <structname>PGcancel</><indexterm><primary>PGcancel</></> object
4130 given a <structname>PGconn</> connection object. It will return
4131 <symbol>NULL</> if the given <parameter>conn</> is <symbol>NULL</> or an invalid
4132 connection. The <structname>PGcancel</> object is an opaque
4133 structure that is not meant to be accessed directly by the
4134 application; it can only be passed to <function>PQcancel</function>
4135 or <function>PQfreeCancel</function>.
4140 <varlistentry id="libpq-pqfreecancel">
4142 <function>PQfreeCancel</function>
4144 <primary>PQfreeCancel</primary>
4150 Frees a data structure created by <function>PQgetCancel</function>.
4152 void PQfreeCancel(PGcancel *cancel);
4157 <function>PQfreeCancel</function> frees a data object previously created
4158 by <function>PQgetCancel</function>.
4163 <varlistentry id="libpq-pqcancel">
4165 <function>PQcancel</function>
4167 <primary>PQcancel</primary>
4173 Requests that the server abandon processing of the current command.
4175 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
4180 The return value is 1 if the cancel request was successfully
4181 dispatched and 0 if not. If not, <parameter>errbuf</> is filled
4182 with an explanatory error message. <parameter>errbuf</>
4183 must be a char array of size <parameter>errbufsize</> (the
4184 recommended size is 256 bytes).
4188 Successful dispatch is no guarantee that the request will have
4189 any effect, however. If the cancellation is effective, the current
4190 command will terminate early and return an error result. If the
4191 cancellation fails (say, because the server was already done
4192 processing the command), then there will be no visible result at
4197 <function>PQcancel</function> can safely be invoked from a signal
4198 handler, if the <parameter>errbuf</> is a local variable in the
4199 signal handler. The <structname>PGcancel</> object is read-only
4200 as far as <function>PQcancel</function> is concerned, so it can
4201 also be invoked from a thread that is separate from the one
4202 manipulating the <structname>PGconn</> object.
4209 <varlistentry id="libpq-pqrequestcancel">
4211 <function>PQrequestCancel</function>
4213 <primary>PQrequestCancel</primary>
4219 <function>PQrequestCancel</function> is a deprecated variant of
4220 <function>PQcancel</function>.
4222 int PQrequestCancel(PGconn *conn);
4227 Requests that the server abandon processing of the current
4228 command. It operates directly on the
4229 <structname>PGconn</> object, and in case of failure stores the
4230 error message in the <structname>PGconn</> object (whence it can
4231 be retrieved by <function>PQerrorMessage</function>). Although
4232 the functionality is the same, this approach creates hazards for
4233 multiple-thread programs and signal handlers, since it is possible
4234 that overwriting the <structname>PGconn</>'s error message will
4235 mess up the operation currently in progress on the connection.
4244 <sect1 id="libpq-fastpath">
4245 <title>The Fast-Path Interface</title>
4247 <indexterm zone="libpq-fastpath">
4248 <primary>fast path</primary>
4252 <productname>PostgreSQL</productname> provides a fast-path interface
4253 to send simple function calls to the server.
4258 This interface is somewhat obsolete, as one can achieve similar
4259 performance and greater functionality by setting up a prepared
4260 statement to define the function call. Then, executing the statement
4261 with binary transmission of parameters and results substitutes for a
4262 fast-path function call.
4267 The function <function>PQfn</function><indexterm><primary>PQfn</></>
4268 requests execution of a server function via the fast-path interface:
4270 PGresult *PQfn(PGconn *conn,
4275 const PQArgBlock *args,
4292 The <parameter>fnid</> argument is the OID of the function to be
4293 executed. <parameter>args</> and <parameter>nargs</> define the
4294 parameters to be passed to the function; they must match the declared
4295 function argument list. When the <parameter>isint</> field of a
4296 parameter structure is true, the <parameter>u.integer</> value is sent
4297 to the server as an integer of the indicated length (this must be 1,
4298 2, or 4 bytes); proper byte-swapping occurs. When <parameter>isint</>
4299 is false, the indicated number of bytes at <parameter>*u.ptr</> are
4300 sent with no processing; the data must be in the format expected by
4301 the server for binary transmission of the function's argument data
4302 type. <parameter>result_buf</parameter> is the buffer in which to
4303 place the return value. The caller must have allocated sufficient
4304 space to store the return value. (There is no check!) The actual result
4305 length will be returned in the integer pointed to by
4306 <parameter>result_len</parameter>. If a 1, 2, or 4-byte integer result
4307 is expected, set <parameter>result_is_int</parameter> to 1, otherwise
4308 set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
4309 <application>libpq</> to byte-swap the value if necessary, so that it
4310 is delivered as a proper <type>int</type> value for the client machine.
4311 When <parameter>result_is_int</> is 0, the binary-format byte string
4312 sent by the server is returned unmodified.
4316 <function>PQfn</function> always returns a valid
4317 <structname>PGresult</structname> pointer. The result status should be
4318 checked before the result is used. The caller is responsible for
4319 freeing the <structname>PGresult</structname> with
4320 <function>PQclear</function> when it is no longer needed.
4324 Note that it is not possible to handle null arguments, null results,
4325 nor set-valued results when using this interface.
4330 <sect1 id="libpq-notify">
4331 <title>Asynchronous Notification</title>
4333 <indexterm zone="libpq-notify">
4334 <primary>NOTIFY</primary>
4335 <secondary>in libpq</secondary>
4339 <productname>PostgreSQL</productname> offers asynchronous notification
4340 via the <command>LISTEN</command> and <command>NOTIFY</command>
4341 commands. A client session registers its interest in a particular
4342 notification channel with the <command>LISTEN</command> command (and
4343 can stop listening with the <command>UNLISTEN</command> command). All
4344 sessions listening on a particular channel will be notified
4345 asynchronously when a <command>NOTIFY</command> command with that
4346 channel name is executed by any session. A <quote>payload</> string can
4347 be passed to communicate additional data to the listeners.
4351 <application>libpq</application> applications submit
4352 <command>LISTEN</command>, <command>UNLISTEN</command>,
4353 and <command>NOTIFY</command> commands as
4354 ordinary SQL commands. The arrival of <command>NOTIFY</command>
4355 messages can subsequently be detected by calling
4356 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
4360 The function <function>PQnotifies</function> returns the next notification
4361 from a list of unhandled notification messages received from the server.
4362 It returns a null pointer if there are no pending notifications. Once a
4363 notification is returned from <function>PQnotifies</>, it is considered
4364 handled and will be removed from the list of notifications.
4367 PGnotify *PQnotifies(PGconn *conn);
4369 typedef struct pgNotify
4371 char *relname; /* notification channel name */
4372 int be_pid; /* process ID of notifying server process */
4373 char *extra; /* notification payload string */
4377 After processing a <structname>PGnotify</structname> object returned
4378 by <function>PQnotifies</function>, be sure to free it with
4379 <function>PQfreemem</function>. It is sufficient to free the
4380 <structname>PGnotify</structname> pointer; the
4381 <structfield>relname</structfield> and <structfield>extra</structfield>
4382 fields do not represent separate allocations. (The names of these fields
4383 are historical; in particular, channel names need not have anything to
4384 do with relation names.)
4388 <xref linkend="libpq-example-2"> gives a sample program that illustrates
4389 the use of asynchronous notification.
4393 <function>PQnotifies</function> does not actually read data from the
4394 server; it just returns messages previously absorbed by another
4395 <application>libpq</application> function. In prior releases of
4396 <application>libpq</application>, the only way to ensure timely receipt
4397 of <command>NOTIFY</> messages was to constantly submit commands, even
4398 empty ones, and then check <function>PQnotifies</function> after each
4399 <function>PQexec</function>. While this still works, it is deprecated
4400 as a waste of processing power.
4404 A better way to check for <command>NOTIFY</> messages when you have no
4405 useful commands to execute is to call
4406 <function>PQconsumeInput</function>, then check
4407 <function>PQnotifies</function>. You can use
4408 <function>select()</function> to wait for data to arrive from the
4409 server, thereby using no <acronym>CPU</acronym> power unless there is
4410 something to do. (See <function>PQsocket</function> to obtain the file
4411 descriptor number to use with <function>select()</function>.) Note that
4412 this will work OK whether you submit commands with
4413 <function>PQsendQuery</function>/<function>PQgetResult</function> or
4414 simply use <function>PQexec</function>. You should, however, remember
4415 to check <function>PQnotifies</function> after each
4416 <function>PQgetResult</function> or <function>PQexec</function>, to
4417 see if any notifications came in during the processing of the command.
4422 <sect1 id="libpq-copy">
4423 <title>Functions Associated with the <command>COPY</command> Command</title>
4425 <indexterm zone="libpq-copy">
4426 <primary>COPY</primary>
4427 <secondary>with libpq</secondary>
4431 The <command>COPY</command> command in
4432 <productname>PostgreSQL</productname> has options to read from or write
4433 to the network connection used by <application>libpq</application>.
4434 The functions described in this section allow applications to take
4435 advantage of this capability by supplying or consuming copied data.
4439 The overall process is that the application first issues the SQL
4440 <command>COPY</command> command via <function>PQexec</function> or one
4441 of the equivalent functions. The response to this (if there is no
4442 error in the command) will be a <structname>PGresult</> object bearing
4443 a status code of <literal>PGRES_COPY_OUT</literal> or
4444 <literal>PGRES_COPY_IN</literal> (depending on the specified copy
4445 direction). The application should then use the functions of this
4446 section to receive or transmit data rows. When the data transfer is
4447 complete, another <structname>PGresult</> object is returned to indicate
4448 success or failure of the transfer. Its status will be
4449 <literal>PGRES_COMMAND_OK</literal> for success or
4450 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
4451 At this point further SQL commands can be issued via
4452 <function>PQexec</function>. (It is not possible to execute other SQL
4453 commands using the same connection while the <command>COPY</command>
4454 operation is in progress.)
4458 If a <command>COPY</command> command is issued via
4459 <function>PQexec</function> in a string that could contain additional
4460 commands, the application must continue fetching results via
4461 <function>PQgetResult</> after completing the <command>COPY</command>
4462 sequence. Only when <function>PQgetResult</> returns
4463 <symbol>NULL</symbol> is it certain that the <function>PQexec</function>
4464 command string is done and it is safe to issue more commands.
4468 The functions of this section should be executed only after obtaining
4469 a result status of <literal>PGRES_COPY_OUT</literal> or
4470 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
4471 <function>PQgetResult</function>.
4475 A <structname>PGresult</> object bearing one of these status values
4476 carries some additional data about the <command>COPY</command> operation
4477 that is starting. This additional data is available using functions
4478 that are also used in connection with query results:
4481 <varlistentry id="libpq-pqnfields-1">
4483 <function>PQnfields</function>
4485 <primary>PQnfields</primary>
4486 <secondary>with COPY</secondary>
4492 Returns the number of columns (fields) to be copied.
4497 <varlistentry id="libpq-pqbinarytuples-1">
4499 <function>PQbinaryTuples</function>
4501 <primary>PQbinaryTuples</primary>
4502 <secondary>with COPY</secondary>
4508 0 indicates the overall copy format is textual (rows separated by
4509 newlines, columns separated by separator characters, etc). 1
4510 indicates the overall copy format is binary. See <xref
4511 linkend="sql-copy"> for more information.
4516 <varlistentry id="libpq-pqfformat-1">
4518 <function>PQfformat</function>
4520 <primary>PQfformat</primary>
4521 <secondary>with COPY</secondary>
4527 Returns the format code (0 for text, 1 for binary) associated with
4528 each column of the copy operation. The per-column format codes
4529 will always be zero when the overall copy format is textual, but
4530 the binary format can support both text and binary columns.
4531 (However, as of the current implementation of <command>COPY</>,
4532 only binary columns appear in a binary copy; so the per-column
4533 formats always match the overall format at present.)
4542 These additional data values are only available when using protocol
4543 3.0. When using protocol 2.0, all these functions will return 0.
4547 <sect2 id="libpq-copy-send">
4548 <title>Functions for Sending <command>COPY</command> Data</title>
4551 These functions are used to send data during <literal>COPY FROM
4552 STDIN</>. They will fail if called when the connection is not in
4553 <literal>COPY_IN</> state.
4557 <varlistentry id="libpq-pqputcopydata">
4559 <function>PQputCopyData</function>
4561 <primary>PQputCopyData</primary>
4567 Sends data to the server during <literal>COPY_IN</> state.
4569 int PQputCopyData(PGconn *conn,
4576 Transmits the <command>COPY</command> data in the specified
4577 <parameter>buffer</>, of length <parameter>nbytes</>, to the server.
4578 The result is 1 if the data was sent, zero if it was not sent
4579 because the attempt would block (this case is only possible if the
4580 connection is in nonblocking mode), or -1 if an error occurred.
4581 (Use <function>PQerrorMessage</function> to retrieve details if
4582 the return value is -1. If the value is zero, wait for write-ready
4587 The application can divide the <command>COPY</command> data stream
4588 into buffer loads of any convenient size. Buffer-load boundaries
4589 have no semantic significance when sending. The contents of the
4590 data stream must match the data format expected by the
4591 <command>COPY</> command; see <xref linkend="sql-copy"> for details.
4596 <varlistentry id="libpq-pqputcopyend">
4598 <function>PQputCopyEnd</function>
4600 <primary>PQputCopyEnd</primary>
4606 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
4608 int PQputCopyEnd(PGconn *conn,
4609 const char *errormsg);
4614 Ends the <literal>COPY_IN</> operation successfully if
4615 <parameter>errormsg</> is <symbol>NULL</symbol>. If
4616 <parameter>errormsg</> is not <symbol>NULL</symbol> then the
4617 <command>COPY</> is forced to fail, with the string pointed to by
4618 <parameter>errormsg</> used as the error message. (One should not
4619 assume that this exact error message will come back from the server,
4620 however, as the server might have already failed the
4621 <command>COPY</> for its own reasons. Also note that the option
4622 to force failure does not work when using pre-3.0-protocol
4627 The result is 1 if the termination data was sent, zero if it was
4628 not sent because the attempt would block (this case is only possible
4629 if the connection is in nonblocking mode), or -1 if an error
4630 occurred. (Use <function>PQerrorMessage</function> to retrieve
4631 details if the return value is -1. If the value is zero, wait for
4632 write-ready and try again.)
4636 After successfully calling <function>PQputCopyEnd</>, call
4637 <function>PQgetResult</> to obtain the final result status of the
4638 <command>COPY</> command. One can wait for this result to be
4639 available in the usual way. Then return to normal operation.
4647 <sect2 id="libpq-copy-receive">
4648 <title>Functions for Receiving <command>COPY</command> Data</title>
4651 These functions are used to receive data during <literal>COPY TO
4652 STDOUT</>. They will fail if called when the connection is not in
4653 <literal>COPY_OUT</> state.
4657 <varlistentry id="libpq-pqgetcopydata">
4659 <function>PQgetCopyData</function>
4661 <primary>PQgetCopyData</primary>
4667 Receives data from the server during <literal>COPY_OUT</> state.
4669 int PQgetCopyData(PGconn *conn,
4676 Attempts to obtain another row of data from the server during a
4677 <command>COPY</command>. Data is always returned one data row at
4678 a time; if only a partial row is available, it is not returned.
4679 Successful return of a data row involves allocating a chunk of
4680 memory to hold the data. The <parameter>buffer</> parameter must
4681 be non-<symbol>NULL</symbol>. <parameter>*buffer</> is set to
4682 point to the allocated memory, or to <symbol>NULL</symbol> in cases
4683 where no buffer is returned. A non-<symbol>NULL</symbol> result
4684 buffer should be freed using <function>PQfreemem</> when no longer
4689 When a row is successfully returned, the return value is the number
4690 of data bytes in the row (this will always be greater than zero).
4691 The returned string is always null-terminated, though this is
4692 probably only useful for textual <command>COPY</command>. A result
4693 of zero indicates that the <command>COPY</command> is still in
4694 progress, but no row is yet available (this is only possible when
4695 <parameter>async</> is true). A result of -1 indicates that the
4696 <command>COPY</command> is done. A result of -2 indicates that an
4697 error occurred (consult <function>PQerrorMessage</> for the reason).
4701 When <parameter>async</> is true (not zero),
4702 <function>PQgetCopyData</> will not block waiting for input; it
4703 will return zero if the <command>COPY</command> is still in progress
4704 but no complete row is available. (In this case wait for read-ready
4705 and then call <function>PQconsumeInput</> before calling
4706 <function>PQgetCopyData</> again.) When <parameter>async</> is
4707 false (zero), <function>PQgetCopyData</> will block until data is
4708 available or the operation completes.
4712 After <function>PQgetCopyData</> returns -1, call
4713 <function>PQgetResult</> to obtain the final result status of the
4714 <command>COPY</> command. One can wait for this result to be
4715 available in the usual way. Then return to normal operation.
4723 <sect2 id="libpq-copy-deprecated">
4724 <title>Obsolete Functions for <command>COPY</command></title>
4727 These functions represent older methods of handling <command>COPY</>.
4728 Although they still work, they are deprecated due to poor error handling,
4729 inconvenient methods of detecting end-of-data, and lack of support for binary
4730 or nonblocking transfers.
4734 <varlistentry id="libpq-pqgetline">
4736 <function>PQgetline</function>
4738 <primary>PQgetline</primary>
4744 Reads a newline-terminated line of characters (transmitted
4745 by the server) into a buffer string of size <parameter>length</>.
4747 int PQgetline(PGconn *conn,
4754 This function copies up to <parameter>length</>-1 characters into
4755 the buffer and converts the terminating newline into a zero byte.
4756 <function>PQgetline</function> returns <symbol>EOF</symbol> at the
4757 end of input, 0 if the entire line has been read, and 1 if the
4758 buffer is full but the terminating newline has not yet been read.
4761 Note that the application must check to see if a new line consists
4762 of the two characters <literal>\.</literal>, which indicates
4763 that the server has finished sending the results of the
4764 <command>COPY</command> command. If the application might receive
4765 lines that are more than <parameter>length</>-1 characters long,
4766 care is needed to be sure it recognizes the <literal>\.</literal>
4767 line correctly (and does not, for example, mistake the end of a
4768 long data line for a terminator line).
4773 <varlistentry id="libpq-pqgetlineasync">
4775 <function>PQgetlineAsync</function>
4777 <primary>PQgetlineAsync</primary>
4783 Reads a row of <command>COPY</command> data (transmitted by the
4784 server) into a buffer without blocking.
4786 int PQgetlineAsync(PGconn *conn,
4793 This function is similar to <function>PQgetline</function>, but it can be used
4795 that must read <command>COPY</command> data asynchronously, that is, without blocking.
4796 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
4798 application should call <function>PQconsumeInput</function> and
4799 <function>PQgetlineAsync</function> until the
4800 end-of-data signal is detected.
4803 Unlike <function>PQgetline</function>, this function takes
4804 responsibility for detecting end-of-data.
4808 On each call, <function>PQgetlineAsync</function> will return data if a
4809 complete data row is available in <application>libpq</>'s input buffer.
4810 Otherwise, no data is returned until the rest of the row arrives.
4811 The function returns -1 if the end-of-copy-data marker has been recognized,
4812 or 0 if no data is available, or a positive number giving the number of
4813 bytes of data returned. If -1 is returned, the caller must next call
4814 <function>PQendcopy</function>, and then return to normal processing.
4818 The data returned will not extend beyond a data-row boundary. If possible
4819 a whole row will be returned at one time. But if the buffer offered by
4820 the caller is too small to hold a row sent by the server, then a partial
4821 data row will be returned. With textual data this can be detected by testing
4822 whether the last returned byte is <literal>\n</literal> or not. (In a binary
4823 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
4824 equivalent determination.)
4825 The returned string is not null-terminated. (If you want to add a
4826 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
4827 than the room actually available.)
4832 <varlistentry id="libpq-pqputline">
4834 <function>PQputline</function>
4836 <primary>PQputline</primary>
4842 Sends a null-terminated string to the server. Returns 0 if
4843 OK and <symbol>EOF</symbol> if unable to send the string.
4845 int PQputline(PGconn *conn,
4846 const char *string);
4851 The <command>COPY</command> data stream sent by a series of calls
4852 to <function>PQputline</function> has the same format as that
4853 returned by <function>PQgetlineAsync</function>, except that
4854 applications are not obliged to send exactly one data row per
4855 <function>PQputline</function> call; it is okay to send a partial
4856 line or multiple lines per call.
4861 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
4862 for the application to explicitly send the two characters
4863 <literal>\.</literal> as a final line to indicate to the server that it had
4864 finished sending <command>COPY</> data. While this still works, it is deprecated and the
4865 special meaning of <literal>\.</literal> can be expected to be removed in a
4866 future release. It is sufficient to call <function>PQendcopy</function> after
4867 having sent the actual data.
4873 <varlistentry id="libpq-pqputnbytes">
4875 <function>PQputnbytes</function>
4877 <primary>PQputnbytes</primary>
4883 Sends a non-null-terminated string to the server. Returns
4884 0 if OK and <symbol>EOF</symbol> if unable to send the string.
4886 int PQputnbytes(PGconn *conn,
4893 This is exactly like <function>PQputline</function>, except that the data
4894 buffer need not be null-terminated since the number of bytes to send is
4895 specified directly. Use this procedure when sending binary data.
4900 <varlistentry id="libpq-pqendcopy">
4902 <function>PQendcopy</function>
4904 <primary>PQendcopy</primary>
4910 Synchronizes with the server.
4912 int PQendcopy(PGconn *conn);
4914 This function waits until the server has finished the copying.
4915 It should either be issued when the last string has been sent
4916 to the server using <function>PQputline</function> or when the
4917 last string has been received from the server using
4918 <function>PGgetline</function>. It must be issued or the server
4919 will get <quote>out of sync</quote> with the client. Upon return
4920 from this function, the server is ready to receive the next SQL
4921 command. The return value is 0 on successful completion,
4922 nonzero otherwise. (Use <function>PQerrorMessage</function> to
4923 retrieve details if the return value is nonzero.)
4927 When using <function>PQgetResult</function>, the application should
4928 respond to a <literal>PGRES_COPY_OUT</literal> result by executing
4929 <function>PQgetline</function> repeatedly, followed by
4930 <function>PQendcopy</function> after the terminator line is seen.
4931 It should then return to the <function>PQgetResult</function> loop
4932 until <function>PQgetResult</function> returns a null pointer.
4933 Similarly a <literal>PGRES_COPY_IN</literal> result is processed
4934 by a series of <function>PQputline</function> calls followed by
4935 <function>PQendcopy</function>, then return to the
4936 <function>PQgetResult</function> loop. This arrangement will
4937 ensure that a <command>COPY</command> command embedded in a series
4938 of <acronym>SQL</acronym> commands will be executed correctly.
4942 Older applications are likely to submit a <command>COPY</command>
4943 via <function>PQexec</function> and assume that the transaction
4944 is done after <function>PQendcopy</function>. This will work
4945 correctly only if the <command>COPY</command> is the only
4946 <acronym>SQL</acronym> command in the command string.
4956 <sect1 id="libpq-control">
4957 <title>Control Functions</title>
4960 These functions control miscellaneous details of <application>libpq</>'s
4965 <varlistentry id="libpq-pqclientencoding">
4967 <function>PQclientEncoding</function>
4969 <primary>PQclientEncoding</primary>
4975 Returns the client encoding.
4977 int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
4980 Note that it returns the encoding ID, not a symbolic string
4981 such as <literal>EUC_JP</literal>. To convert an encoding ID to an encoding name, you
4985 char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
4991 <varlistentry id="libpq-pqsetclientencoding">
4993 <function>PQsetClientEncoding</function>
4995 <primary>PQsetClientEncoding</primary>
5001 Sets the client encoding.
5003 int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
5006 <replaceable>conn</replaceable> is a connection to the server,
5007 and <replaceable>encoding</replaceable> is the encoding you want to
5008 use. If the function successfully sets the encoding, it returns 0,
5009 otherwise -1. The current encoding for this connection can be
5010 determined by using <function>PQclientEncoding</>.
5015 <varlistentry id="libpq-pqseterrorverbosity">
5017 <function>PQsetErrorVerbosity</function>
5019 <primary>PQsetErrorVerbosity</primary>
5025 Determines the verbosity of messages returned by
5026 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
5035 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
5038 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
5039 the connection's previous setting. In <firstterm>TERSE</> mode,
5040 returned messages include severity, primary text, and position only;
5041 this will normally fit on a single line. The default mode produces
5042 messages that include the above plus any detail, hint, or context
5043 fields (these might span multiple lines). The <firstterm>VERBOSE</>
5044 mode includes all available fields. Changing the verbosity does not
5045 affect the messages available from already-existing
5046 <structname>PGresult</> objects, only subsequently-created ones.
5051 <varlistentry id="libpq-pqtrace">
5053 <function>PQtrace</function>
5055 <primary>PQtrace</primary>
5061 Enables tracing of the client/server communication to a debugging file stream.
5063 void PQtrace(PGconn *conn, FILE *stream);
5069 On Windows, if the <application>libpq</> library and an application are
5070 compiled with different flags, this function call will crash the
5071 application because the internal representation of the <literal>FILE</>
5072 pointers differ. Specifically, multithreaded/single-threaded,
5073 release/debug, and static/dynamic flags should be the same for the
5074 library and all applications using that library.
5081 <varlistentry id="libpq-pquntrace">
5083 <function>PQuntrace</function>
5085 <primary>PQuntrace</primary>
5091 Disables tracing started by <function>PQtrace</function>.
5093 void PQuntrace(PGconn *conn);
5102 <sect1 id="libpq-misc">
5103 <title>Miscellaneous Functions</title>
5106 As always, there are some functions that just don't fit anywhere.
5110 <varlistentry id="libpq-pqfreemem">
5112 <function>PQfreemem</function>
5114 <primary>PQfreemem</primary>
5120 Frees memory allocated by <application>libpq</>.
5122 void PQfreemem(void *ptr);
5127 Frees memory allocated by <application>libpq</>, particularly
5128 <function>PQescapeByteaConn</function>,
5129 <function>PQescapeBytea</function>,
5130 <function>PQunescapeBytea</function>,
5131 and <function>PQnotifies</function>.
5132 It is particularly important that this function, rather than
5133 <function>free()</>, be used on Microsoft Windows. This is because
5134 allocating memory in a DLL and releasing it in the application works
5135 only if multithreaded/single-threaded, release/debug, and static/dynamic
5136 flags are the same for the DLL and the application. On non-Microsoft
5137 Windows platforms, this function is the same as the standard library
5138 function <function>free()</>.
5143 <varlistentry id="libpq-pqconninfofree">
5145 <function>PQconninfoFree</function>
5147 <primary>PQconninfoFree</primary>
5153 Frees the data structures allocated by
5154 <function>PQconndefaults</> or <function>PQconninfoParse</>.
5156 void PQconninfoFree(PQconninfoOption *connOptions);
5161 A simple <function>PQfreemem</function> will not do for this, since
5162 the array contains references to subsidiary strings.
5167 <varlistentry id="libpq-pqencryptpassword">
5169 <function>PQencryptPassword</function>
5171 <primary>PQencryptPassword</primary>
5177 Prepares the encrypted form of a <productname>PostgreSQL</> password.
5179 char * PQencryptPassword(const char *passwd, const char *user);
5181 This function is intended to be used by client applications that
5182 wish to send commands like <literal>ALTER USER joe PASSWORD
5183 'pwd'</>. It is good practice not to send the original cleartext
5184 password in such a command, because it might be exposed in command
5185 logs, activity displays, and so on. Instead, use this function to
5186 convert the password to encrypted form before it is sent. The
5187 arguments are the cleartext password, and the SQL name of the user
5188 it is for. The return value is a string allocated by
5189 <function>malloc</function>, or <symbol>NULL</symbol> if out of
5190 memory. The caller can assume the string doesn't contain any
5191 special characters that would require escaping. Use
5192 <function>PQfreemem</> to free the result when done with it.
5197 <varlistentry id="libpq-pqmakeemptypgresult">
5199 <function>PQmakeEmptyPGresult</function>
5201 <primary>PQmakeEmptyPGresult</primary>
5207 Constructs an empty <structname>PGresult</structname> object with the given status.
5209 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
5214 This is <application>libpq</>'s internal function to allocate and
5215 initialize an empty <structname>PGresult</structname> object. This
5216 function returns <symbol>NULL</> if memory could not be allocated. It is
5217 exported because some applications find it useful to generate result
5218 objects (particularly objects with error status) themselves. If
5219 <parameter>conn</parameter> is not null and <parameter>status</>
5220 indicates an error, the current error message of the specified
5221 connection is copied into the <structname>PGresult</structname>.
5222 Also, if <parameter>conn</parameter> is not null, any event procedures
5223 registered in the connection are copied into the
5224 <structname>PGresult</structname>. (They do not get
5225 <literal>PGEVT_RESULTCREATE</> calls, but see
5226 <function>PQfireResultCreateEvents</function>.)
5227 Note that <function>PQclear</function> should eventually be called
5228 on the object, just as with a <structname>PGresult</structname>
5229 returned by <application>libpq</application> itself.
5234 <varlistentry id="libpq-pqfireresultcreateevents">
5236 <function>PQfireResultCreateEvents</function>
5238 <primary>PQfireResultCreateEvents</primary>
5243 Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
5244 linkend="libpq-events">) for each event procedure registered in the
5245 <structname>PGresult</structname> object. Returns non-zero for success,
5246 zero if any event procedure fails.
5249 int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
5254 The <literal>conn</> argument is passed through to event procedures
5255 but not used directly. It can be <symbol>NULL</> if the event
5256 procedures won't use it.
5260 Event procedures that have already received a
5261 <literal>PGEVT_RESULTCREATE</> or <literal>PGEVT_RESULTCOPY</> event
5262 for this object are not fired again.
5266 The main reason that this function is separate from
5267 <function>PQmakeEmptyPGResult</function> is that it is often appropriate
5268 to create a <structname>PGresult</structname> and fill it with data
5269 before invoking the event procedures.
5274 <varlistentry id="libpq-pqcopyresult">
5276 <function>PQcopyResult</function>
5278 <primary>PQcopyResult</primary>
5284 Makes a copy of a <structname>PGresult</structname> object. The copy is
5285 not linked to the source result in any way and
5286 <function>PQclear</function> must be called when the copy is no longer
5287 needed. If the function fails, <symbol>NULL</> is returned.
5290 PGresult *PQcopyResult(const PGresult *src, int flags);
5295 This is not intended to make an exact copy. The returned result is
5296 always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
5297 copy any error message in the source. (It does copy the command status
5298 string, however.) The <parameter>flags</parameter> argument determines
5299 what else is copied. It is a bitwise OR of several flags.
5300 <literal>PG_COPYRES_ATTRS</literal> specifies copying the source
5301 result's attributes (column definitions).
5302 <literal>PG_COPYRES_TUPLES</literal> specifies copying the source
5303 result's tuples. (This implies copying the attributes, too.)
5304 <literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
5305 copying the source result's notify hooks.
5306 <literal>PG_COPYRES_EVENTS</literal> specifies copying the source
5307 result's events. (But any instance data associated with the source
5313 <varlistentry id="libpq-pqsetresultattrs">
5315 <function>PQsetResultAttrs</function>
5317 <primary>PQsetResultAttrs</primary>
5323 Sets the attributes of a <structname>PGresult</structname> object.
5325 int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
5330 The provided <parameter>attDescs</parameter> are copied into the result.
5331 If the <parameter>attDescs</parameter> pointer is <symbol>NULL</> or
5332 <parameter>numAttributes</parameter> is less than one, the request is
5333 ignored and the function succeeds. If <parameter>res</parameter>
5334 already contains attributes, the function will fail. If the function
5335 fails, the return value is zero. If the function succeeds, the return
5341 <varlistentry id="libpq-pqsetvalue">
5343 <function>PQsetvalue</function>
5345 <primary>PQsetvalue</primary>
5351 Sets a tuple field value of a <structname>PGresult</structname> object.
5353 int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
5358 The function will automatically grow the result's internal tuples array
5359 as needed. However, the <parameter>tup_num</parameter> argument must be
5360 less than or equal to <function>PQntuples</function>, meaning this
5361 function can only grow the tuples array one tuple at a time. But any
5362 field of any existing tuple can be modified in any order. If a value at
5363 <parameter>field_num</parameter> already exists, it will be overwritten.
5364 If <parameter>len</parameter> is -1 or
5365 <parameter>value</parameter> is <symbol>NULL</>, the field value
5366 will be set to an SQL null value. The
5367 <parameter>value</parameter> is copied into the result's private storage,
5368 thus is no longer needed after the function
5369 returns. If the function fails, the return value is zero. If the
5370 function succeeds, the return value is non-zero.
5375 <varlistentry id="libpq-pqresultalloc">
5377 <function>PQresultAlloc</function>
5379 <primary>PQresultAlloc</primary>
5385 Allocate subsidiary storage for a <structname>PGresult</structname> object.
5387 void *PQresultAlloc(PGresult *res, size_t nBytes);
5392 Any memory allocated with this function will be freed when
5393 <parameter>res</parameter> is cleared. If the function fails,
5394 the return value is <symbol>NULL</>. The result is
5395 guaranteed to be adequately aligned for any type of data,
5396 just as for <function>malloc</>.
5401 <varlistentry id="libpq-pqlibversion">
5403 <function>PQlibVersion</function>
5405 <primary>PQlibVersion</primary>
5406 <seealso>PQserverVersion</seealso>
5412 Return the version of <productname>libpq</> that is being used.
5414 int PQlibVersion(void);
5419 The result of this function can be used to determine, at
5420 run time, if specific functionality is available in the currently
5421 loaded version of libpq. The function can be used, for example,
5422 to determine which connection options are available for
5423 <function>PQconnectdb</> or if the <literal>hex</> <type>bytea</>
5424 output added in PostgreSQL 9.0 is supported.
5428 The number is formed by converting the major, minor, and revision
5429 numbers into two-decimal-digit numbers and appending them together.
5430 For example, version 9.1 will be returned as 90100, and version
5431 9.1.2 will be returned as 90102 (leading zeroes are not shown).
5436 This function appeared in <productname>PostgreSQL</> version 9.1, so
5437 it cannot be used to detect required functionality in earlier
5438 versions, since linking to it will create a link dependency
5449 <sect1 id="libpq-notice-processing">
5450 <title>Notice Processing</title>
5452 <indexterm zone="libpq-notice-processing">
5453 <primary>notice processing</primary>
5454 <secondary>in libpq</secondary>
5458 Notice and warning messages generated by the server are not returned
5459 by the query execution functions, since they do not imply failure of
5460 the query. Instead they are passed to a notice handling function, and
5461 execution continues normally after the handler returns. The default
5462 notice handling function prints the message on
5463 <filename>stderr</filename>, but the application can override this
5464 behavior by supplying its own handling function.
5468 For historical reasons, there are two levels of notice handling, called
5469 the notice receiver and notice processor. The default behavior is for
5470 the notice receiver to format the notice and pass a string to the notice
5471 processor for printing. However, an application that chooses to provide
5472 its own notice receiver will typically ignore the notice processor
5473 layer and just do all the work in the notice receiver.
5477 The function <function>PQsetNoticeReceiver</function>
5478 <indexterm><primary>notice receiver</></>
5479 <indexterm><primary>PQsetNoticeReceiver</></> sets or
5480 examines the current notice receiver for a connection object.
5481 Similarly, <function>PQsetNoticeProcessor</function>
5482 <indexterm><primary>notice processor</></>
5483 <indexterm><primary>PQsetNoticeProcessor</></> sets or
5484 examines the current notice processor.
5487 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
5490 PQsetNoticeReceiver(PGconn *conn,
5491 PQnoticeReceiver proc,
5494 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
5497 PQsetNoticeProcessor(PGconn *conn,
5498 PQnoticeProcessor proc,
5502 Each of these functions returns the previous notice receiver or
5503 processor function pointer, and sets the new value. If you supply a
5504 null function pointer, no action is taken, but the current pointer is
5509 When a notice or warning message is received from the server, or
5510 generated internally by <application>libpq</application>, the notice
5511 receiver function is called. It is passed the message in the form of
5512 a <symbol>PGRES_NONFATAL_ERROR</symbol>
5513 <structname>PGresult</structname>. (This allows the receiver to extract
5514 individual fields using <function>PQresultErrorField</>, or the complete
5515 preformatted message using <function>PQresultErrorMessage</>.) The same
5516 void pointer passed to <function>PQsetNoticeReceiver</function> is also
5517 passed. (This pointer can be used to access application-specific state
5522 The default notice receiver simply extracts the message (using
5523 <function>PQresultErrorMessage</>) and passes it to the notice
5528 The notice processor is responsible for handling a notice or warning
5529 message given in text form. It is passed the string text of the message
5530 (including a trailing newline), plus a void pointer that is the same
5531 one passed to <function>PQsetNoticeProcessor</function>. (This pointer
5532 can be used to access application-specific state if needed.)
5536 The default notice processor is simply:
5539 defaultNoticeProcessor(void *arg, const char *message)
5541 fprintf(stderr, "%s", message);
5547 Once you have set a notice receiver or processor, you should expect
5548 that that function could be called as long as either the
5549 <structname>PGconn</> object or <structname>PGresult</> objects made
5550 from it exist. At creation of a <structname>PGresult</>, the
5551 <structname>PGconn</>'s current notice handling pointers are copied
5552 into the <structname>PGresult</> for possible use by functions like
5553 <function>PQgetvalue</function>.
5558 <sect1 id="libpq-events">
5559 <title>Event System</title>
5562 <application>libpq</application>'s event system is designed to notify
5563 registered event handlers about interesting
5564 <application>libpq</application> events, such as the creation or
5565 destruction of <structname>PGconn</structname> and
5566 <structname>PGresult</structname> objects. A principal use case is that
5567 this allows applications to associate their own data with a
5568 <structname>PGconn</structname> or <structname>PGresult</structname>
5569 and ensure that that data is freed at an appropriate time.
5573 Each registered event handler is associated with two pieces of data,
5574 known to <application>libpq</application> only as opaque <literal>void *</>
5575 pointers. There is a <firstterm>passthrough</> pointer that is provided
5576 by the application when the event handler is registered with a
5577 <structname>PGconn</>. The passthrough pointer never changes for the
5578 life of the <structname>PGconn</> and all <structname>PGresult</>s
5579 generated from it; so if used, it must point to long-lived data.
5580 In addition there is an <firstterm>instance data</> pointer, which starts
5581 out <symbol>NULL</> in every <structname>PGconn</> and <structname>PGresult</>.
5582 This pointer can be manipulated using the
5583 <function>PQinstanceData</function>,
5584 <function>PQsetInstanceData</function>,
5585 <function>PQresultInstanceData</function> and
5586 <function>PQsetResultInstanceData</function> functions. Note that
5587 unlike the passthrough pointer, instance data of a <structname>PGconn</>
5588 is not automatically inherited by <structname>PGresult</>s created from
5589 it. <application>libpq</application> does not know what passthrough
5590 and instance data pointers point to (if anything) and will never attempt
5591 to free them — that is the responsibility of the event handler.
5594 <sect2 id="libpq-events-types">
5595 <title>Event Types</title>
5598 The enum <literal>PGEventId</> names the types of events handled by
5599 the event system. All its values have names beginning with
5600 <literal>PGEVT</literal>. For each event type, there is a corresponding
5601 event info structure that carries the parameters passed to the event
5602 handlers. The event types are:
5606 <varlistentry id="libpq-pgevt-register">
5607 <term><literal>PGEVT_REGISTER</literal></term>
5610 The register event occurs when <function>PQregisterEventProc</function>
5611 is called. It is the ideal time to initialize any
5612 <literal>instanceData</literal> an event procedure may need. Only one
5613 register event will be fired per event handler per connection. If the
5614 event procedure fails, the registration is aborted.
5623 When a <literal>PGEVT_REGISTER</literal> event is received, the
5624 <parameter>evtInfo</parameter> pointer should be cast to a
5625 <structname>PGEventRegister *</structname>. This structure contains a
5626 <structname>PGconn</structname> that should be in the
5627 <literal>CONNECTION_OK</literal> status; guaranteed if one calls
5628 <function>PQregisterEventProc</function> right after obtaining a good
5629 <structname>PGconn</structname>. When returning a failure code, all
5630 cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
5636 <varlistentry id="libpq-pgevt-connreset">
5637 <term><literal>PGEVT_CONNRESET</literal></term>
5640 The connection reset event is fired on completion of
5641 <function>PQreset</function> or <function>PQresetPoll</function>. In
5642 both cases, the event is only fired if the reset was successful. If
5643 the event procedure fails, the entire connection reset will fail; the
5644 <structname>PGconn</structname> is put into
5645 <literal>CONNECTION_BAD</literal> status and
5646 <function>PQresetPoll</function> will return
5647 <literal>PGRES_POLLING_FAILED</literal>.
5656 When a <literal>PGEVT_CONNRESET</literal> event is received, the
5657 <parameter>evtInfo</parameter> pointer should be cast to a
5658 <structname>PGEventConnReset *</structname>. Although the contained
5659 <structname>PGconn</structname> was just reset, all event data remains
5660 unchanged. This event should be used to reset/reload/requery any
5661 associated <literal>instanceData</literal>. Note that even if the
5662 event procedure fails to process <literal>PGEVT_CONNRESET</>, it will
5663 still receive a <literal>PGEVT_CONNDESTROY</> event when the connection
5669 <varlistentry id="libpq-pgevt-conndestroy">
5670 <term><literal>PGEVT_CONNDESTROY</literal></term>
5673 The connection destroy event is fired in response to
5674 <function>PQfinish</function>. It is the event procedure's
5675 responsibility to properly clean up its event data as libpq has no
5676 ability to manage this memory. Failure to clean up will lead
5683 } PGEventConnDestroy;
5686 When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
5687 <parameter>evtInfo</parameter> pointer should be cast to a
5688 <structname>PGEventConnDestroy *</structname>. This event is fired
5689 prior to <function>PQfinish</function> performing any other cleanup.
5690 The return value of the event procedure is ignored since there is no
5691 way of indicating a failure from <function>PQfinish</function>. Also,
5692 an event procedure failure should not abort the process of cleaning up
5698 <varlistentry id="libpq-pgevt-resultcreate">
5699 <term><literal>PGEVT_RESULTCREATE</literal></term>
5702 The result creation event is fired in response to any query execution
5703 function that generates a result, including
5704 <function>PQgetResult</function>. This event will only be fired after
5705 the result has been created successfully.
5712 } PGEventResultCreate;
5715 When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
5716 <parameter>evtInfo</parameter> pointer should be cast to a
5717 <structname>PGEventResultCreate *</structname>. The
5718 <parameter>conn</parameter> is the connection used to generate the
5719 result. This is the ideal place to initialize any
5720 <literal>instanceData</literal> that needs to be associated with the
5721 result. If the event procedure fails, the result will be cleared and
5722 the failure will be propagated. The event procedure must not try to
5723 <function>PQclear</> the result object for itself. When returning a
5724 failure code, all cleanup must be performed as no
5725 <literal>PGEVT_RESULTDESTROY</literal> event will be sent.
5730 <varlistentry id="libpq-pgevt-resultcopy">
5731 <term><literal>PGEVT_RESULTCOPY</literal></term>
5734 The result copy event is fired in response to
5735 <function>PQcopyResult</function>. This event will only be fired after
5736 the copy is complete. Only event procedures that have
5737 successfully handled the <literal>PGEVT_RESULTCREATE</literal>
5738 or <literal>PGEVT_RESULTCOPY</literal> event for the source result
5739 will receive <literal>PGEVT_RESULTCOPY</literal> events.
5744 const PGresult *src;
5746 } PGEventResultCopy;
5749 When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
5750 <parameter>evtInfo</parameter> pointer should be cast to a
5751 <structname>PGEventResultCopy *</structname>. The
5752 <parameter>src</parameter> result is what was copied while the
5753 <parameter>dest</parameter> result is the copy destination. This event
5754 can be used to provide a deep copy of <literal>instanceData</literal>,
5755 since <literal>PQcopyResult</literal> cannot do that. If the event
5756 procedure fails, the entire copy operation will fail and the
5757 <parameter>dest</parameter> result will be cleared. When returning a
5758 failure code, all cleanup must be performed as no
5759 <literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
5765 <varlistentry id="libpq-pgevt-resultdestroy">
5766 <term><literal>PGEVT_RESULTDESTROY</literal></term>
5769 The result destroy event is fired in response to a
5770 <function>PQclear</function>. It is the event procedure's
5771 responsibility to properly clean up its event data as libpq has no
5772 ability to manage this memory. Failure to clean up will lead
5779 } PGEventResultDestroy;
5782 When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
5783 <parameter>evtInfo</parameter> pointer should be cast to a
5784 <structname>PGEventResultDestroy *</structname>. This event is fired
5785 prior to <function>PQclear</function> performing any other cleanup.
5786 The return value of the event procedure is ignored since there is no
5787 way of indicating a failure from <function>PQclear</function>. Also,
5788 an event procedure failure should not abort the process of cleaning up
5796 <sect2 id="libpq-events-proc">
5797 <title>Event Callback Procedure</title>
5800 <varlistentry id="libpq-pgeventproc">
5802 <literal>PGEventProc</literal>
5804 <primary>PGEventProc</primary>
5810 <literal>PGEventProc</literal> is a typedef for a pointer to an
5811 event procedure, that is, the user callback function that receives
5812 events from libpq. The signature of an event procedure must be
5815 int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
5818 The <parameter>evtId</parameter> parameter indicates which
5819 <literal>PGEVT</literal> event occurred. The
5820 <parameter>evtInfo</parameter> pointer must be cast to the appropriate
5821 structure type to obtain further information about the event.
5822 The <parameter>passThrough</parameter> parameter is the pointer
5823 provided to <function>PQregisterEventProc</function> when the event
5824 procedure was registered. The function should return a non-zero value
5825 if it succeeds and zero if it fails.
5829 A particular event procedure can be registered only once in any
5830 <structname>PGconn</>. This is because the address of the procedure
5831 is used as a lookup key to identify the associated instance data.
5836 On Windows, functions can have two different addresses: one visible
5837 from outside a DLL and another visible from inside the DLL. One
5838 should be careful that only one of these addresses is used with
5839 <application>libpq</>'s event-procedure functions, else confusion will
5840 result. The simplest rule for writing code that will work is to
5841 ensure that event procedures are declared <literal>static</>. If the
5842 procedure's address must be available outside its own source file,
5843 expose a separate function to return the address.
5851 <sect2 id="libpq-events-funcs">
5852 <title>Event Support Functions</title>
5855 <varlistentry id="libpq-pqregistereventproc">
5857 <function>PQregisterEventProc</function>
5859 <primary>PQregisterEventProc</primary>
5865 Registers an event callback procedure with libpq.
5868 int PQregisterEventProc(PGconn *conn, PGEventProc proc,
5869 const char *name, void *passThrough);
5874 An event procedure must be registered once on each
5875 <structname>PGconn</> you want to receive events about. There is no
5876 limit, other than memory, on the number of event procedures that
5877 can be registered with a connection. The function returns a non-zero
5878 value if it succeeds and zero if it fails.
5882 The <parameter>proc</parameter> argument will be called when a libpq
5883 event is fired. Its memory address is also used to lookup
5884 <literal>instanceData</literal>. The <parameter>name</parameter>
5885 argument is used to refer to the event procedure in error messages.
5886 This value cannot be <symbol>NULL</> or a zero-length string. The name string is
5887 copied into the <structname>PGconn</>, so what is passed need not be
5888 long-lived. The <parameter>passThrough</parameter> pointer is passed
5889 to the <parameter>proc</parameter> whenever an event occurs. This
5890 argument can be <symbol>NULL</>.
5895 <varlistentry id="libpq-pqsetinstancedata">
5897 <function>PQsetInstanceData</function>
5899 <primary>PQsetInstanceData</primary>
5904 Sets the connection <parameter>conn</>'s <literal>instanceData</>
5905 for procedure <parameter>proc</> to <parameter>data</>. This
5906 returns non-zero for success and zero for failure. (Failure is
5907 only possible if <parameter>proc</> has not been properly
5908 registered in <parameter>conn</>.)
5911 int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
5917 <varlistentry id="libpq-pqinstancedata">
5919 <function>PQinstanceData</function>
5921 <primary>PQinstanceData</primary>
5927 connection <parameter>conn</>'s <literal>instanceData</literal>
5928 associated with procedure <parameter>proc</>,
5929 or <symbol>NULL</symbol> if there is none.
5932 void *PQinstanceData(const PGconn *conn, PGEventProc proc);
5938 <varlistentry id="libpq-pqresultsetinstancedata">
5940 <function>PQresultSetInstanceData</function>
5942 <primary>PQresultSetInstanceData</primary>
5947 Sets the result's <literal>instanceData</>
5948 for <parameter>proc</> to <parameter>data</>. This returns
5949 non-zero for success and zero for failure. (Failure is only
5950 possible if <parameter>proc</> has not been properly registered
5954 int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
5960 <varlistentry id="libpq-pqresultinstancedata">
5962 <function>PQresultInstanceData</function>
5964 <primary>PQresultInstanceData</primary>
5969 Returns the result's <literal>instanceData</> associated with <parameter>proc</>, or <symbol>NULL</>
5973 void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
5981 <sect2 id="libpq-events-example">
5982 <title>Event Example</title>
5985 Here is a skeleton example of managing private data associated with
5986 libpq connections and results.
5991 /* required header for libpq events (note: includes libpq-fe.h) */
5992 #include <libpq-events.h>
5994 /* The instanceData */
6002 static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);
6009 PGconn *conn = PQconnectdb("dbname = postgres");
6011 if (PQstatus(conn) != CONNECTION_OK)
6013 fprintf(stderr, "Connection to database failed: %s",
6014 PQerrorMessage(conn));
6019 /* called once on any connection that should receive events.
6020 * Sends a PGEVT_REGISTER to myEventProc.
6022 if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
6024 fprintf(stderr, "Cannot register PGEventProc\n");
6029 /* conn instanceData is available */
6030 data = PQinstanceData(conn, myEventProc);
6032 /* Sends a PGEVT_RESULTCREATE to myEventProc */
6033 res = PQexec(conn, "SELECT 1 + 1");
6035 /* result instanceData is available */
6036 data = PQresultInstanceData(res, myEventProc);
6038 /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
6039 res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);
6041 /* result instanceData is available if PG_COPYRES_EVENTS was
6042 * used during the PQcopyResult call.
6044 data = PQresultInstanceData(res_copy, myEventProc);
6046 /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
6050 /* Sends a PGEVT_CONNDESTROY to myEventProc */
6057 myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
6061 case PGEVT_REGISTER:
6063 PGEventRegister *e = (PGEventRegister *)evtInfo;
6064 mydata *data = get_mydata(e->conn);
6066 /* associate app specific data with connection */
6067 PQsetInstanceData(e->conn, myEventProc, data);
6071 case PGEVT_CONNRESET:
6073 PGEventConnReset *e = (PGEventConnReset *)evtInfo;
6074 mydata *data = PQinstanceData(e->conn, myEventProc);
6077 memset(data, 0, sizeof(mydata));
6081 case PGEVT_CONNDESTROY:
6083 PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
6084 mydata *data = PQinstanceData(e->conn, myEventProc);
6086 /* free instance data because the conn is being destroyed */
6092 case PGEVT_RESULTCREATE:
6094 PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
6095 mydata *conn_data = PQinstanceData(e->conn, myEventProc);
6096 mydata *res_data = dup_mydata(conn_data);
6098 /* associate app specific data with result (copy it from conn) */
6099 PQsetResultInstanceData(e->result, myEventProc, res_data);
6103 case PGEVT_RESULTCOPY:
6105 PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
6106 mydata *src_data = PQresultInstanceData(e->src, myEventProc);
6107 mydata *dest_data = dup_mydata(src_data);
6109 /* associate app specific data with result (copy it from a result) */
6110 PQsetResultInstanceData(e->dest, myEventProc, dest_data);
6114 case PGEVT_RESULTDESTROY:
6116 PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
6117 mydata *data = PQresultInstanceData(e->result, myEventProc);
6119 /* free instance data because the result is being destroyed */
6125 /* unknown event ID, just return TRUE. */
6130 return TRUE; /* event processing succeeded */
6137 <sect1 id="libpq-envars">
6138 <title>Environment Variables</title>
6140 <indexterm zone="libpq-envars">
6141 <primary>environment variable</primary>
6145 The following environment variables can be used to select default
6146 connection parameter values, which will be used by
6147 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
6148 <function>PQsetdb</> if no value is directly specified by the calling
6149 code. These are useful to avoid hard-coding database connection
6150 information into simple client applications, for example.
6156 <primary><envar>PGHOST</envar></primary>
6158 <envar>PGHOST</envar> behaves the same as the <xref
6159 linkend="libpq-connect-host"> connection parameter.
6166 <primary><envar>PGHOSTADDR</envar></primary>
6168 <envar>PGHOSTADDR</envar> behaves the same as the <xref
6169 linkend="libpq-connect-hostaddr"> connection parameter.
6170 This can be set instead of or in addition to <envar>PGHOST</envar>
6171 to avoid DNS lookup overhead.
6178 <primary><envar>PGPORT</envar></primary>
6180 <envar>PGPORT</envar> behaves the same as the <xref
6181 linkend="libpq-connect-port"> connection parameter.
6188 <primary><envar>PGDATABASE</envar></primary>
6190 <envar>PGDATABASE</envar> behaves the same as the <xref
6191 linkend="libpq-connect-dbname"> connection parameter.
6198 <primary><envar>PGUSER</envar></primary>
6200 <envar>PGUSER</envar> behaves the same as the <xref
6201 linkend="libpq-connect-user"> connection parameter.
6208 <primary><envar>PGPASSWORD</envar></primary>
6210 <envar>PGPASSWORD</envar> behaves the same as the <xref
6211 linkend="libpq-connect-password"> connection parameter.
6212 Use of this environment variable
6213 is not recommended for security reasons, as some operating systems
6214 allow non-root users to see process environment variables via
6215 <application>ps</>; instead consider using the
6216 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
6223 <primary><envar>PGPASSFILE</envar></primary>
6225 <envar>PGPASSFILE</envar> specifies the name of the password file to
6226 use for lookups. If not set, it defaults to <filename>~/.pgpass</>
6227 (see <xref linkend="libpq-pgpass">).
6234 <primary><envar>PGSERVICE</envar></primary>
6236 <envar>PGSERVICE</envar> behaves the same as the <xref
6237 linkend="libpq-connect-service"> connection parameter.
6244 <primary><envar>PGSERVICEFILE</envar></primary>
6246 <envar>PGSERVICEFILE</envar> specifies the name of the per-user
6247 connection service file. If not set, it defaults
6248 to <filename>~/.pg_service.conf</>
6249 (see <xref linkend="libpq-pgservice">).
6256 <primary><envar>PGREALM</envar></primary>
6258 <envar>PGREALM</envar> sets the Kerberos realm to use with
6259 <productname>PostgreSQL</productname>, if it is different from the
6260 local realm. If <envar>PGREALM</envar> is set,
6261 <application>libpq</application> applications will attempt
6262 authentication with servers for this realm and use separate ticket
6263 files to avoid conflicts with local ticket files. This
6264 environment variable is only used if Kerberos authentication is
6265 selected by the server.
6272 <primary><envar>PGOPTIONS</envar></primary>
6274 <envar>PGOPTIONS</envar> behaves the same as the <xref
6275 linkend="libpq-connect-options"> connection parameter.
6282 <primary><envar>PGAPPNAME</envar></primary>
6284 <envar>PGAPPNAME</envar> behaves the same as the <xref
6285 linkend="libpq-connect-application-name"> connection parameter.
6292 <primary><envar>PGSSLMODE</envar></primary>
6294 <envar>PGSSLMODE</envar> behaves the same as the <xref
6295 linkend="libpq-connect-sslmode"> connection parameter.
6302 <primary><envar>PGREQUIRESSL</envar></primary>
6304 <envar>PGREQUIRESSL</envar> behaves the same as the <xref
6305 linkend="libpq-connect-requiressl"> connection parameter.
6312 <primary><envar>PGSSLCERT</envar></primary>
6314 <envar>PGSSLCERT</envar> behaves the same as the <xref
6315 linkend="libpq-connect-sslcert"> connection parameter.
6322 <primary><envar>PGSSLKEY</envar></primary>
6324 <envar>PGSSLKEY</envar> behaves the same as the <xref
6325 linkend="libpq-connect-sslkey"> connection parameter.
6332 <primary><envar>PGSSLROOTCERT</envar></primary>
6334 <envar>PGSSLROOTCERT</envar> behaves the same as the <xref
6335 linkend="libpq-connect-sslrootcert"> connection parameter.
6342 <primary><envar>PGSSLCRL</envar></primary>
6344 <envar>PGSSLCRL</envar> behaves the same as the <xref
6345 linkend="libpq-connect-sslcrl"> connection parameter.
6352 <primary><envar>PGREQUIREPEER</envar></primary>
6354 <envar>PGREQUIREPEER</envar> behaves the same as the <xref
6355 linkend="libpq-connect-requirepeer"> connection parameter.
6362 <primary><envar>PGKRBSRVNAME</envar></primary>
6364 <envar>PGKRBSRVNAME</envar> behaves the same as the <xref
6365 linkend="libpq-connect-krbsrvname"> connection parameter.
6372 <primary><envar>PGGSSLIB</envar></primary>
6374 <envar>PGGSSLIB</envar> behaves the same as the <xref
6375 linkend="libpq-connect-gsslib"> connection parameter.
6382 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
6384 <envar>PGCONNECT_TIMEOUT</envar> behaves the same as the <xref
6385 linkend="libpq-connect-connect-timeout"> connection parameter.
6392 <primary><envar>PGCLIENTENCODING</envar></primary>
6394 <envar>PGCLIENTENCODING</envar> behaves the same as the <xref
6395 linkend="libpq-connect-client-encoding"> connection parameter.
6402 The following environment variables can be used to specify default
6403 behavior for each <productname>PostgreSQL</productname> session. (See
6404 also the <xref linkend="sql-alterrole">
6405 and <xref linkend="sql-alterdatabase">
6406 commands for ways to set default behavior on a per-user or per-database
6413 <primary><envar>PGDATESTYLE</envar></primary>
6415 <envar>PGDATESTYLE</envar> sets the default style of date/time
6416 representation. (Equivalent to <literal>SET datestyle TO
6424 <primary><envar>PGTZ</envar></primary>
6426 <envar>PGTZ</envar> sets the default time zone. (Equivalent to
6427 <literal>SET timezone TO ...</literal>.)
6434 <primary><envar>PGGEQO</envar></primary>
6436 <envar>PGGEQO</envar> sets the default mode for the genetic query
6437 optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
6442 Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set">
6443 for information on correct values for these
6444 environment variables.
6448 The following environment variables determine internal behavior of
6449 <application>libpq</application>; they override compiled-in defaults.
6455 <primary><envar>PGSYSCONFDIR</envar></primary>
6457 <envar>PGSYSCONFDIR</envar> sets the directory containing the
6458 <filename>pg_service.conf</> file and in a future version
6459 possibly other system-wide configuration files.
6466 <primary><envar>PGLOCALEDIR</envar></primary>
6468 <envar>PGLOCALEDIR</envar> sets the directory containing the
6469 <literal>locale</> files for message internationalization.
6478 <sect1 id="libpq-pgpass">
6479 <title>The Password File</title>
6481 <indexterm zone="libpq-pgpass">
6482 <primary>password file</primary>
6484 <indexterm zone="libpq-pgpass">
6485 <primary>.pgpass</primary>
6489 The file <filename>.pgpass</filename> in a user's home directory or the
6490 file referenced by <envar>PGPASSFILE</envar> can contain passwords to
6491 be used if the connection requires a password (and no password has been
6492 specified otherwise). On Microsoft Windows the file is named
6493 <filename>%APPDATA%\postgresql\pgpass.conf</> (where
6494 <filename>%APPDATA%</> refers to the Application Data subdirectory in
6495 the user's profile).
6499 This file should contain lines of the following format:
6501 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
6503 (You can add a reminder comment to the file by copying the line above and
6504 preceding it with <literal>#</>.)
6505 Each of the first four fields can be a literal value, or
6506 <literal>*</literal>, which matches anything. The password field from
6507 the first line that matches the current connection parameters will be
6508 used. (Therefore, put more-specific entries first when you are using
6509 wildcards.) If an entry needs to contain <literal>:</literal> or
6510 <literal>\</literal>, escape this character with <literal>\</literal>.
6511 A host name of <literal>localhost</> matches both TCP (host name
6512 <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
6513 or the default socket directory) connections coming from the local
6514 machine. In a standby server, a database name of <literal>replication</>
6515 matches streaming replication connections made to the master server.
6516 The <literal>database</> field is of limited usefulness because
6517 users have the same password for all databases in the same cluster.
6521 On Unix systems, the permissions on <filename>.pgpass</filename> must
6522 disallow any access to world or group; achieve this by the command
6523 <command>chmod 0600 ~/.pgpass</command>. If the permissions are less
6524 strict than this, the file will be ignored. On Microsoft Windows, it
6525 is assumed that the file is stored in a directory that is secure, so
6526 no special permissions check is made.
6531 <sect1 id="libpq-pgservice">
6532 <title>The Connection Service File</title>
6534 <indexterm zone="libpq-pgservice">
6535 <primary>connection service file</primary>
6537 <indexterm zone="libpq-pgservice">
6538 <primary>pg_service.conf</primary>
6540 <indexterm zone="libpq-pgservice">
6541 <primary>.pg_service.conf</primary>
6545 The connection service file allows libpq connection parameters to be
6546 associated with a single service name. That service name can then be
6547 specified by a libpq connection, and the associated settings will be
6548 used. This allows connection parameters to be modified without requiring
6549 a recompile of the libpq application. The service name can also be
6550 specified using the <envar>PGSERVICE</envar> environment variable.
6554 The connection service file can be a per-user service file
6555 at <filename>~/.pg_service.conf</filename> or the location
6556 specified by the environment variable <envar>PGSERVICEFILE</envar>,
6557 or it can be a system-wide file
6558 at <filename>etc/pg_service.conf</filename> or in the directory
6559 specified by the environment variable
6560 <envar>PGSYSCONFDIR</envar>. If service definitions with the same
6561 name exist in the user and the system file, the user file takes
6566 The file uses an <quote>INI file</quote> format where the section
6567 name is the service name and the parameters are connection
6568 parameters; see <xref linkend="libpq-connect"> for a list. For
6577 An example file is provided at
6578 <filename>share/pg_service.conf.sample</filename>.
6583 <sect1 id="libpq-ldap">
6584 <title>LDAP Lookup of Connection Parameters</title>
6586 <indexterm zone="libpq-ldap">
6587 <primary>LDAP connection parameter lookup</primary>
6591 If <application>libpq</application> has been compiled with LDAP support (option
6592 <literal><option>--with-ldap</option></literal> for <command>configure</command>)
6593 it is possible to retrieve connection options like <literal>host</literal>
6594 or <literal>dbname</literal> via LDAP from a central server.
6595 The advantage is that if the connection parameters for a database change,
6596 the connection information doesn't have to be updated on all client machines.
6600 LDAP connection parameter lookup uses the connection service file
6601 <filename>pg_service.conf</filename> (see <xref
6602 linkend="libpq-pgservice">). A line in a
6603 <filename>pg_service.conf</filename> stanza that starts with
6604 <literal>ldap://</literal> will be recognized as an LDAP URL and an
6605 LDAP query will be performed. The result must be a list of
6606 <literal>keyword = value</literal> pairs which will be used to set
6607 connection options. The URL must conform to RFC 1959 and be of the
6610 ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
6612 where <replaceable>hostname</replaceable> defaults to
6613 <literal>localhost</literal> and <replaceable>port</replaceable>
6618 Processing of <filename>pg_service.conf</filename> is terminated after
6619 a successful LDAP lookup, but is continued if the LDAP server cannot
6620 be contacted. This is to provide a fallback with further LDAP URL
6621 lines that point to different LDAP servers, classical <literal>keyword
6622 = value</literal> pairs, or default connection options. If you would
6623 rather get an error message in this case, add a syntactically incorrect
6624 line after the LDAP URL.
6628 A sample LDAP entry that has been created with the LDIF file
6631 dn:cn=mydatabase,dc=mycompany,dc=com
6634 objectclass:groupOfUniqueNames
6636 uniqueMember:host=dbserver.mycompany.com
6637 uniqueMember:port=5439
6638 uniqueMember:dbname=mydb
6639 uniqueMember:user=mydb_user
6640 uniqueMember:sslmode=require
6642 might be queried with the following LDAP URL:
6644 ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)
6649 You can also mix regular service file entries with LDAP lookups.
6650 A complete example for a stanza in <filename>pg_service.conf</filename>
6653 # only host and port are stored in LDAP, specify dbname and user explicitly
6657 ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
6664 <sect1 id="libpq-ssl">
6665 <title>SSL Support</title>
6667 <indexterm zone="libpq-ssl">
6668 <primary>SSL</primary>
6672 <productname>PostgreSQL</> has native support for using <acronym>SSL</>
6673 connections to encrypt client/server communications for increased
6674 security. See <xref linkend="ssl-tcp"> for details about the server-side
6675 <acronym>SSL</> functionality.
6679 <application>libpq</application> reads the system-wide
6680 <productname>OpenSSL</productname> configuration file. By default, this
6681 file is named <filename>openssl.cnf</filename> and is located in the
6682 directory reported by <literal>openssl version -d</>. This default
6683 can be overridden by setting environment variable
6684 <envar>OPENSSL_CONF</envar> to the name of the desired configuration
6688 <sect2 id="libq-ssl-certificates">
6689 <title>Client Verification of Server Certificates</title>
6692 By default, <productname>PostgreSQL</> will not perform any verification of
6693 the server certificate. This means that it is possible to spoof the server
6694 identity (for example by modifying a DNS record or by taking over the server
6695 IP address) without the client knowing. In order to prevent spoofing,
6696 <acronym>SSL</> certificate verification must be used.
6700 If the parameter <literal>sslmode</> is set to <literal>verify-ca</>,
6701 libpq will verify that the server is trustworthy by checking the
6702 certificate chain up to a trusted certificate authority
6703 (<acronym>CA</>). If <literal>sslmode</> is set to <literal>verify-full</>,
6704 libpq will <emphasis>also</> verify that the server host name matches its
6705 certificate. The SSL connection will fail if the server certificate cannot
6706 be verified. <literal>verify-full</> is recommended in most
6707 security-sensitive environments.
6711 In <literal>verify-full</> mode, the <literal>cn</> (Common Name) attribute
6712 of the certificate is matched against the host name. If the <literal>cn</>
6713 attribute starts with an asterisk (<literal>*</>), it will be treated as
6714 a wildcard, and will match all characters <emphasis>except</> a dot
6715 (<literal>.</>). This means the certificate will not match subdomains.
6716 If the connection is made using an IP address instead of a host name, the
6717 IP address will be matched (without doing any DNS lookups).
6721 To allow server certificate verification, the certificate(s) of one or more
6722 trusted <acronym>CA</>s must be
6723 placed in the file <filename>~/.postgresql/root.crt</> in the user's home
6724 directory. (On Microsoft Windows the file is named
6725 <filename>%APPDATA%\postgresql\root.crt</filename>.)
6729 Certificate Revocation List (CRL) entries are also checked
6730 if the file <filename>~/.postgresql/root.crl</filename> exists
6731 (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
6736 The location of the root certificate file and the CRL can be changed by
6738 the connection parameters <literal>sslrootcert</> and <literal>sslcrl</>
6739 or the environment variables <envar>PGSSLROOTCERT</> and <envar>PGSSLCRL</>.
6744 For backwards compatibility with earlier versions of PostgreSQL, if a
6745 root CA file exists, the behavior of
6746 <literal>sslmode</literal>=<literal>require</literal> will be the same
6747 as that of <literal>verify-ca</literal>, meaning the sever certificate
6748 is validated against the CA. Relying on this behavior is discouraged,
6749 and applications that need certificate validation should always use
6750 <literal>validate-ca</literal> or <literal>validate-full</literal>.
6755 <sect2 id="libpq-ssl-clientcert">
6756 <title>Client Certificates</title>
6759 If the server requests a trusted client certificate,
6760 <application>libpq</application> will send the certificate stored in
6761 file <filename>~/.postgresql/postgresql.crt</> in the user's home
6762 directory. The certificate must be signed by one of the certificate
6763 authorities (<acronym>CA</acronym>) trusted by the server. A matching
6764 private key file <filename>~/.postgresql/postgresql.key</> must also
6765 be present. The private
6766 key file must not allow any access to world or group; achieve this by the
6767 command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
6768 On Microsoft Windows these files are named
6769 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
6770 <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
6771 is no special permissions check since the directory is presumed secure.
6772 The location of the certificate and key files can be overridden by the
6773 connection parameters <literal>sslcert</> and <literal>sslkey</> or the
6774 environment variables <envar>PGSSLCERT</> and <envar>PGSSLKEY</>.
6778 In some cases, the client certificate might be signed by an
6779 <quote>intermediate</> certificate authority, rather than one that is
6780 directly trusted by the server. To use such a certificate, append the
6781 certificate of the signing authority to the <filename>postgresql.crt</>
6782 file, then its parent authority's certificate, and so on up to a
6783 <quote>root</> authority that is trusted by the server. The root
6784 certificate should be included in every case where
6785 <filename>postgresql.crt</> contains more than one certificate.
6789 Note that <filename>root.crt</filename> lists the top-level CAs that are
6790 considered trusted for signing server certificates. In principle it need
6791 not list the CA that signed the client's certificate, though in most cases
6792 that CA would also be trusted for server certificates.
6797 <sect2 id="libpq-ssl-protection">
6798 <title>Protection Provided in Different Modes</title>
6801 The different values for the <literal>sslmode</> parameter provide different
6802 levels of protection. SSL can provide
6803 protection against three types of attacks:
6807 <term>Eavesdropping</term>
6809 <para>If a third party can examine the network traffic between the
6810 client and the server, it can read both connection information (including
6811 the user name and password) and the data that is passed. <acronym>SSL</>
6812 uses encryption to prevent this.
6818 <term>Man in the middle (<acronym>MITM</>)</term>
6820 <para>If a third party can modify the data while passing between the
6821 client and server, it can pretend to be the server and therefore see and
6822 modify data <emphasis>even if it is encrypted</>. The third party can then
6823 forward the connection information and data to the original server,
6824 making it impossible to detect this attack. Common vectors to do this
6825 include DNS poisoning and address hijacking, whereby the client is directed
6826 to a different server than intended. There are also several other
6827 attack methods that can accomplish this. <acronym>SSL</> uses certificate
6828 verification to prevent this, by authenticating the server to the client.
6834 <term>Impersonation</term>
6836 <para>If a third party can pretend to be an authorized client, it can
6837 simply access data it should not have access to. Typically this can
6838 happen through insecure password management. <acronym>SSL</> uses
6839 client certificates to prevent this, by making sure that only holders
6840 of valid certificates can access the server.
6848 For a connection to be known secure, SSL usage must be configured
6849 on <emphasis>both the client and the server</> before the connection
6850 is made. If it is only configured on the server, the client may end up
6851 sending sensitive information (e.g. passwords) before
6852 it knows that the server requires high security. In libpq, secure
6853 connections can be ensured
6854 by setting the <literal>sslmode</> parameter to <literal>verify-full</> or
6855 <literal>verify-ca</>, and providing the system with a root certificate to
6856 verify against. This is analogous to using an <literal>https</>
6857 <acronym>URL</> for encrypted web browsing.
6861 Once the server has been authenticated, the client can pass sensitive data.
6862 This means that up until this point, the client does not need to know if
6863 certificates will be used for authentication, making it safe to specify that
6864 only in the server configuration.
6868 All <acronym>SSL</> options carry overhead in the form of encryption and
6869 key-exchange, so there is a tradeoff that has to be made between performance
6870 and security. <xref linkend="libpq-ssl-sslmode-statements">
6871 illustrates the risks the different <literal>sslmode</> values
6872 protect against, and what statement they make about security and overhead.
6875 <table id="libpq-ssl-sslmode-statements">
6876 <title>SSL Mode Descriptions</title>
6880 <entry><literal>sslmode</></entry>
6881 <entry>Eavesdropping protection</entry>
6882 <entry><acronym>MITM</> protection</entry>
6883 <entry>Statement</entry>
6889 <entry><literal>disable</></entry>
6892 <entry>I don't care about security, and I don't want to pay the overhead
6898 <entry><literal>allow</></entry>
6899 <entry>Maybe</entry>
6901 <entry>I don't care about security, but I will pay the overhead of
6902 encryption if the server insists on it.
6907 <entry><literal>prefer</></entry>
6908 <entry>Maybe</entry>
6910 <entry>I don't care about encryption, but I wish to pay the overhead of
6911 encryption if the server supports it.
6916 <entry><literal>require</></entry>
6919 <entry>I want my data to be encrypted, and I accept the overhead. I trust
6920 that the network will make sure I always connect to the server I want.
6925 <entry><literal>verify-ca</></entry>
6927 <entry><literal>Depends on CA</>-policy</entry>
6928 <entry>I want my data encrypted, and I accept the overhead. I want to be
6929 sure that I connect to a server that I trust.
6934 <entry><literal>verify-full</></entry>
6937 <entry>I want my data encrypted, and I accept the overhead. I want to be
6938 sure that I connect to a server I trust, and that it's the one I
6948 The difference between <literal>verify-ca</> and <literal>verify-full</>
6949 depends on the policy of the root <acronym>CA</>. If a public
6950 <acronym>CA</> is used, <literal>verify-ca</> allows connections to a server
6951 that <emphasis>somebody else</> may have registered with the <acronym>CA</>.
6952 In this case, <literal>verify-full</> should always be used. If
6953 a local <acronym>CA</> is used, or even a self-signed certificate, using
6954 <literal>verify-ca</> often provides enough protection.
6958 The default value for <literal>sslmode</> is <literal>prefer</>. As is shown
6959 in the table, this makes no sense from a security point of view, and it only
6960 promises performance overhead if possible. It is only provided as the default
6961 for backward compatibility, and is not recommended in secure deployments.
6966 <sect2 id="libpq-ssl-fileusage">
6967 <title>SSL Client File Usage</title>
6970 <xref linkend="libpq-ssl-file-usage"> summarizes the files that are
6971 relevant to the SSL setup on the client.
6974 <table id="libpq-ssl-file-usage">
6975 <title>Libpq/Client SSL File Usage</title>
6980 <entry>Contents</entry>
6981 <entry>Effect</entry>
6988 <entry><filename>~/.postgresql/postgresql.crt</></entry>
6989 <entry>client certificate</entry>
6990 <entry>requested by server</entry>
6994 <entry><filename>~/.postgresql/postgresql.key</></entry>
6995 <entry>client private key</entry>
6996 <entry>proves client certificate sent by owner; does not indicate
6997 certificate owner is trustworthy</entry>
7001 <entry><filename>~/.postgresql/root.crt</></entry>
7002 <entry>trusted certificate authorities</entry>
7003 <entry>checks that server certificate is signed by a trusted certificate
7008 <entry><filename>~/.postgresql/root.crl</></entry>
7009 <entry>certificates revoked by certificate authorities</entry>
7010 <entry>server certificate must not be on this list</entry>
7018 <sect2 id="libpq-ssl-initialize">
7019 <title>SSL Library Initialization</title>
7022 If your application initializes <literal>libssl</> and/or
7023 <literal>libcrypto</> libraries and <application>libpq</application>
7024 is built with <acronym>SSL</> support, you should call
7025 <function>PQinitOpenSSL</> to tell <application>libpq</application>
7026 that the <literal>libssl</> and/or <literal>libcrypto</> libraries
7027 have been initialized by your application, so that
7028 <application>libpq</application> will not also initialize those libraries.
7029 <!-- If this URL changes replace it with a URL to www.archive.org. -->
7031 url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
7032 for details on the SSL API.
7037 <varlistentry id="libpq-pqinitopenssl">
7039 <function>PQinitOpenSSL</function>
7041 <primary>PQinitOpenSSL</primary>
7047 Allows applications to select which security libraries to initialize.
7049 void PQinitOpenSSL(int do_ssl, int do_crypto);
7054 When <parameter>do_ssl</> is non-zero, <application>libpq</application>
7055 will initialize the <application>OpenSSL</> library before first
7056 opening a database connection. When <parameter>do_crypto</> is
7057 non-zero, the <literal>libcrypto</> library will be initialized. By
7058 default (if <function>PQinitOpenSSL</> is not called), both libraries
7059 are initialized. When SSL support is not compiled in, this function is
7060 present but does nothing.
7064 If your application uses and initializes either <application>OpenSSL</>
7065 or its underlying <literal>libcrypto</> library, you <emphasis>must</>
7066 call this function with zeroes for the appropriate parameter(s)
7067 before first opening a database connection. Also be sure that you
7068 have done that initialization before opening a database connection.
7073 <varlistentry id="libpq-pqinitssl">
7075 <function>PQinitSSL</function>
7077 <primary>PQinitSSL</primary>
7083 Allows applications to select which security libraries to initialize.
7085 void PQinitSSL(int do_ssl);
7090 This function is equivalent to
7091 <literal>PQinitOpenSSL(do_ssl, do_ssl)</>.
7092 It is sufficient for applications that initialize both or neither
7093 of <application>OpenSSL</> and <literal>libcrypto</>.
7097 <function>PQinitSSL</> has been present since
7098 <productname>PostgreSQL</> 8.0, while <function>PQinitOpenSSL</>
7099 was added in <productname>PostgreSQL</> 8.4, so <function>PQinitSSL</>
7100 might be preferable for applications that need to work with older
7101 versions of <application>libpq</application>.
7112 <sect1 id="libpq-threading">
7113 <title>Behavior in Threaded Programs</title>
7115 <indexterm zone="libpq-threading">
7116 <primary>threads</primary>
7117 <secondary>with libpq</secondary>
7121 <application>libpq</application> is reentrant and thread-safe by default.
7122 You might need to use special compiler command-line
7123 options when you compile your application code. Refer to your
7124 system's documentation for information about how to build
7125 thread-enabled applications, or look in
7126 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
7127 and <literal>PTHREAD_LIBS</>. This function allows the querying of
7128 <application>libpq</application>'s thread-safe status:
7132 <varlistentry id="libpq-pqisthreadsafe">
7134 <function>PQisthreadsafe</function>
7136 <primary>PQisthreadsafe</primary>
7142 Returns the thread safety status of the
7143 <application>libpq</application> library.
7145 int PQisthreadsafe();
7150 Returns 1 if the <application>libpq</application> is thread-safe
7158 One thread restriction is that no two threads attempt to manipulate
7159 the same <structname>PGconn</> object at the same time. In particular,
7160 you cannot issue concurrent commands from different threads through
7161 the same connection object. (If you need to run concurrent commands,
7162 use multiple connections.)
7166 <structname>PGresult</> objects are read-only after creation, and so
7167 can be passed around freely between threads.
7171 The deprecated functions <function>PQrequestCancel</function> and
7172 <function>PQoidStatus</function> are not thread-safe and should not be
7173 used in multithread programs. <function>PQrequestCancel</function>
7174 can be replaced by <function>PQcancel</function>.
7175 <function>PQoidStatus</function> can be replaced by
7176 <function>PQoidValue</function>.
7180 If you are using Kerberos inside your application (in addition to inside
7181 <application>libpq</application>), you will need to do locking around
7182 Kerberos calls because Kerberos functions are not thread-safe. See
7183 function <function>PQregisterThreadLock</> in the
7184 <application>libpq</application> source code for a way to do cooperative
7185 locking between <application>libpq</application> and your application.
7189 If you experience problems with threaded applications, run the program
7190 in <filename>src/tools/thread</> to see if your platform has
7191 thread-unsafe functions. This program is run by
7192 <filename>configure</filename>, but for binary distributions your
7193 library might not match the library used to build the binaries.
7198 <sect1 id="libpq-build">
7199 <title>Building <application>libpq</application> Programs</title>
7201 <indexterm zone="libpq-build">
7202 <primary>compiling</primary>
7203 <secondary>libpq applications</secondary>
7207 To build (i.e., compile and link) a program using
7208 <application>libpq</application> you need to do all of the following
7214 Include the <filename>libpq-fe.h</filename> header file:
7216 #include <libpq-fe.h>
7218 If you failed to do that then you will normally get error messages
7219 from your compiler similar to:
7221 foo.c: In function `main':
7222 foo.c:34: `PGconn' undeclared (first use in this function)
7223 foo.c:35: `PGresult' undeclared (first use in this function)
7224 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
7225 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
7226 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
7233 Point your compiler to the directory where the <productname>PostgreSQL</> header
7234 files were installed, by supplying the
7235 <literal>-I<replaceable>directory</replaceable></literal> option
7236 to your compiler. (In some cases the compiler will look into
7237 the directory in question by default, so you can omit this
7238 option.) For instance, your compile command line could look
7241 cc -c -I/usr/local/pgsql/include testprog.c
7243 If you are using makefiles then add the option to the
7244 <varname>CPPFLAGS</varname> variable:
7246 CPPFLAGS += -I/usr/local/pgsql/include
7251 If there is any chance that your program might be compiled by
7252 other users then you should not hardcode the directory location
7253 like that. Instead, you can run the utility
7254 <command>pg_config</command><indexterm><primary>pg_config</><secondary
7255 sortas="libpq">with libpq</></> to find out where the header
7256 files are on the local system:
7258 <prompt>$</prompt> pg_config --includedir
7259 <computeroutput>/usr/local/include</computeroutput>
7264 Failure to specify the correct option to the compiler will
7265 result in an error message such as:
7267 testlibpq.c:8:22: libpq-fe.h: No such file or directory
7274 When linking the final program, specify the option
7275 <literal>-lpq</literal> so that the <application>libpq</application>
7276 library gets pulled in, as well as the option
7277 <literal>-L<replaceable>directory</replaceable></literal> to point
7278 the compiler to the directory where the
7279 <application>libpq</application> library resides. (Again, the
7280 compiler will search some directories by default.) For maximum
7281 portability, put the <option>-L</option> option before the
7282 <option>-lpq</option> option. For example:
7284 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
7289 You can find out the library directory using
7290 <command>pg_config</command> as well:
7292 <prompt>$</prompt> pg_config --libdir
7293 <computeroutput>/usr/local/pgsql/lib</computeroutput>
7298 Error messages that point to problems in this area could look like
7301 testlibpq.o: In function `main':
7302 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
7303 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
7304 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
7306 This means you forgot <option>-lpq</option>.
7308 /usr/bin/ld: cannot find -lpq
7310 This means you forgot the <option>-L</option> option or did not
7311 specify the right directory.
7320 <sect1 id="libpq-example">
7321 <title>Example Programs</title>
7324 These examples and others can be found in the
7325 directory <filename>src/test/examples</filename> in the source code
7329 <example id="libpq-example-1">
7330 <title><application>libpq</application> Example Program 1</title>
7337 * Test the C version of libpq, the PostgreSQL frontend library.
7341 #include <libpq-fe.h>
7344 exit_nicely(PGconn *conn)
7351 main(int argc, char **argv)
7353 const char *conninfo;
7361 * If the user supplies a parameter on the command line, use it as the
7362 * conninfo string; otherwise default to setting dbname=postgres and using
7363 * environment variables or defaults for all other connection parameters.
7368 conninfo = "dbname = postgres";
7370 /* Make a connection to the database */
7371 conn = PQconnectdb(conninfo);
7373 /* Check to see that the backend connection was successfully made */
7374 if (PQstatus(conn) != CONNECTION_OK)
7376 fprintf(stderr, "Connection to database failed: %s",
7377 PQerrorMessage(conn));
7382 * Our test case here involves using a cursor, for which we must be inside
7383 * a transaction block. We could do the whole thing with a single
7384 * PQexec() of "select * from pg_database", but that's too trivial to make
7388 /* Start a transaction block */
7389 res = PQexec(conn, "BEGIN");
7390 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7392 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
7398 * Should PQclear PGresult whenever it is no longer needed to avoid memory
7404 * Fetch rows from pg_database, the system catalog of databases
7406 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
7407 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7409 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
7415 res = PQexec(conn, "FETCH ALL in myportal");
7416 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7418 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
7423 /* first, print out the attribute names */
7424 nFields = PQnfields(res);
7425 for (i = 0; i < nFields; i++)
7426 printf("%-15s", PQfname(res, i));
7429 /* next, print out the rows */
7430 for (i = 0; i < PQntuples(res); i++)
7432 for (j = 0; j < nFields; j++)
7433 printf("%-15s", PQgetvalue(res, i, j));
7439 /* close the portal ... we don't bother to check for errors ... */
7440 res = PQexec(conn, "CLOSE myportal");
7443 /* end the transaction */
7444 res = PQexec(conn, "END");
7447 /* close the connection to the database and cleanup */
7456 <example id="libpq-example-2">
7457 <title><application>libpq</application> Example Program 2</title>
7463 * Test of the asynchronous notification interface
7465 * Start this program, then from psql in another window do
7467 * Repeat four times to get this program to exit.
7469 * Or, if you want to get fancy, try this:
7470 * populate a database with the following commands
7471 * (provided in src/test/examples/testlibpq2.sql):
7473 * CREATE TABLE TBL1 (i int4);
7475 * CREATE TABLE TBL2 (i int4);
7477 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
7478 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
7480 * and do this four times:
7482 * INSERT INTO TBL1 VALUES (10);
7488 #include <sys/time.h>
7489 #include <libpq-fe.h>
7492 exit_nicely(PGconn *conn)
7499 main(int argc, char **argv)
7501 const char *conninfo;
7508 * If the user supplies a parameter on the command line, use it as the
7509 * conninfo string; otherwise default to setting dbname=postgres and using
7510 * environment variables or defaults for all other connection parameters.
7515 conninfo = "dbname = postgres";
7517 /* Make a connection to the database */
7518 conn = PQconnectdb(conninfo);
7520 /* Check to see that the backend connection was successfully made */
7521 if (PQstatus(conn) != CONNECTION_OK)
7523 fprintf(stderr, "Connection to database failed: %s",
7524 PQerrorMessage(conn));
7529 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
7531 res = PQexec(conn, "LISTEN TBL2");
7532 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7534 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
7540 * should PQclear PGresult whenever it is no longer needed to avoid memory
7545 /* Quit after four notifies are received. */
7547 while (nnotifies < 4)
7550 * Sleep until something happens on the connection. We use select(2)
7551 * to wait for input, but you could also use poll() or similar
7557 sock = PQsocket(conn);
7560 break; /* shouldn't happen */
7562 FD_ZERO(&input_mask);
7563 FD_SET(sock, &input_mask);
7565 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
7567 fprintf(stderr, "select() failed: %s\n", strerror(errno));
7571 /* Now check for input */
7572 PQconsumeInput(conn);
7573 while ((notify = PQnotifies(conn)) != NULL)
7576 "ASYNC NOTIFY of '%s' received from backend PID %d\n",
7577 notify->relname, notify->be_pid);
7583 fprintf(stderr, "Done.\n");
7585 /* close the connection to the database and cleanup */
7594 <example id="libpq-example-3">
7595 <title><application>libpq</application> Example Program 3</title>
7601 * Test out-of-line parameters and binary I/O.
7603 * Before running this, populate a database with the following commands
7604 * (provided in src/test/examples/testlibpq3.sql):
7606 * CREATE TABLE test1 (i int4, t text, b bytea);
7608 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
7609 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
7611 * The expected output is:
7615 * t = (11 bytes) 'joe's place'
7616 * b = (5 bytes) \000\001\002\003\004
7620 * t = (8 bytes) 'ho there'
7621 * b = (5 bytes) \004\003\002\001\000
7626 #include <sys/types.h>
7627 #include <libpq-fe.h>
7629 /* for ntohl/htonl */
7630 #include <netinet/in.h>
7631 #include <arpa/inet.h>
7635 exit_nicely(PGconn *conn)
7642 * This function prints a query result that is a binary-format fetch from
7643 * a table defined as in the comment above. We split it out because the
7644 * main() function uses it twice.
7647 show_binary_results(PGresult *res)
7655 /* Use PQfnumber to avoid assumptions about field order in result */
7656 i_fnum = PQfnumber(res, "i");
7657 t_fnum = PQfnumber(res, "t");
7658 b_fnum = PQfnumber(res, "b");
7660 for (i = 0; i < PQntuples(res); i++)
7668 /* Get the field values (we ignore possibility they are null!) */
7669 iptr = PQgetvalue(res, i, i_fnum);
7670 tptr = PQgetvalue(res, i, t_fnum);
7671 bptr = PQgetvalue(res, i, b_fnum);
7674 * The binary representation of INT4 is in network byte order, which
7675 * we'd better coerce to the local byte order.
7677 ival = ntohl(*((uint32_t *) iptr));
7680 * The binary representation of TEXT is, well, text, and since libpq
7681 * was nice enough to append a zero byte to it, it'll work just fine
7684 * The binary representation of BYTEA is a bunch of bytes, which could
7685 * include embedded nulls so we have to pay attention to field length.
7687 blen = PQgetlength(res, i, b_fnum);
7689 printf("tuple %d: got\n", i);
7690 printf(" i = (%d bytes) %d\n",
7691 PQgetlength(res, i, i_fnum), ival);
7692 printf(" t = (%d bytes) '%s'\n",
7693 PQgetlength(res, i, t_fnum), tptr);
7694 printf(" b = (%d bytes) ", blen);
7695 for (j = 0; j < blen; j++)
7696 printf("\\%03o", bptr[j]);
7702 main(int argc, char **argv)
7704 const char *conninfo;
7707 const char *paramValues[1];
7708 int paramLengths[1];
7709 int paramFormats[1];
7710 uint32_t binaryIntVal;
7713 * If the user supplies a parameter on the command line, use it as the
7714 * conninfo string; otherwise default to setting dbname=postgres and using
7715 * environment variables or defaults for all other connection parameters.
7720 conninfo = "dbname = postgres";
7722 /* Make a connection to the database */
7723 conn = PQconnectdb(conninfo);
7725 /* Check to see that the backend connection was successfully made */
7726 if (PQstatus(conn) != CONNECTION_OK)
7728 fprintf(stderr, "Connection to database failed: %s",
7729 PQerrorMessage(conn));
7734 * The point of this program is to illustrate use of PQexecParams() with
7735 * out-of-line parameters, as well as binary transmission of data.
7737 * This first example transmits the parameters as text, but receives the
7738 * results in binary format. By using out-of-line parameters we can
7739 * avoid a lot of tedious mucking about with quoting and escaping, even
7740 * though the data is text. Notice how we don't have to do anything
7741 * special with the quote mark in the parameter value.
7744 /* Here is our out-of-line parameter value */
7745 paramValues[0] = "joe's place";
7747 res = PQexecParams(conn,
7748 "SELECT * FROM test1 WHERE t = $1",
7750 NULL, /* let the backend deduce param type */
7752 NULL, /* don't need param lengths since text */
7753 NULL, /* default to all text params */
7754 1); /* ask for binary results */
7756 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7758 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
7763 show_binary_results(res);
7768 * In this second example we transmit an integer parameter in binary
7769 * form, and again retrieve the results in binary form.
7771 * Although we tell PQexecParams we are letting the backend deduce
7772 * parameter type, we really force the decision by casting the parameter
7773 * symbol in the query text. This is a good safety measure when sending
7774 * binary parameters.
7777 /* Convert integer value "2" to network byte order */
7778 binaryIntVal = htonl((uint32_t) 2);
7780 /* Set up parameter arrays for PQexecParams */
7781 paramValues[0] = (char *) &binaryIntVal;
7782 paramLengths[0] = sizeof(binaryIntVal);
7783 paramFormats[0] = 1; /* binary */
7785 res = PQexecParams(conn,
7786 "SELECT * FROM test1 WHERE i = $1::int4",
7788 NULL, /* let the backend deduce param type */
7792 1); /* ask for binary results */
7794 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7796 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
7801 show_binary_results(res);
7805 /* close the connection to the database and cleanup */
projects / postgresql / blob