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 **keywords, const char **values, int expand_dbname);
106 This function opens a new database connection using the parameters taken
107 from two <symbol>NULL</symbol>-terminated arrays. The first,
108 <literal>keywords</literal>, is defined as an array of strings, each one
109 being a key word. The second, <literal>values</literal>, gives the value
110 for each key word. Unlike <function>PQsetdbLogin</> below, the parameter
111 set can be extended without changing the function signature, so use of
112 this function (or its nonblocking analogs <function>PQconnectStartParams</>
113 and <function>PQconnectPoll</function>) is preferred for new application
118 When <literal>expand_dbname</literal> is non-zero, the
119 <parameter>dbname</parameter> key word value is allowed to be recognized
120 as a <parameter>conninfo</parameter> string. See below for details.
124 The passed arrays can be empty to use all default parameters, or can
125 contain one or more parameter settings. They should be matched in length.
126 Processing will stop with the last non-<symbol>NULL</symbol> element
127 of the <literal>keywords</literal> array.
131 The currently recognized parameter key words are:
134 <varlistentry id="libpq-connect-host" xreflabel="host">
135 <term><literal>host</literal></term>
138 Name of host to connect to.<indexterm><primary>host name</></>
139 If this begins with a slash, it specifies Unix-domain
140 communication rather than TCP/IP communication; the value is the
141 name of the directory in which the socket file is stored. The
142 default behavior when <literal>host</literal> is not specified
143 is to connect to a Unix-domain
144 socket<indexterm><primary>Unix domain socket</></> in
145 <filename>/tmp</filename> (or whatever socket directory was specified
146 when <productname>PostgreSQL</> was built). On machines without
147 Unix-domain sockets, the default is to connect to <literal>localhost</>.
152 <varlistentry id="libpq-connect-hostaddr" xreflabel="hostaddr">
153 <term><literal>hostaddr</literal></term>
156 Numeric IP address of host to connect to. This should be in the
157 standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
158 your machine supports IPv6, you can also use those addresses.
159 TCP/IP communication is
160 always used when a nonempty string is specified for this parameter.
164 Using <literal>hostaddr</> instead of <literal>host</> allows the
165 application to avoid a host name look-up, which might be important
166 in applications with time constraints. However, a host name is
167 required for Kerberos, GSSAPI, or SSPI authentication
168 methods, as well as for <literal>verify-full</> SSL
169 certificate verification. The following rules are used:
173 If <literal>host</> is specified without <literal>hostaddr</>,
174 a host name lookup occurs.
179 If <literal>hostaddr</> is specified without <literal>host</>,
180 the value for <literal>hostaddr</> gives the server network address.
181 The connection attempt will fail if the authentication
182 method requires a host name.
187 If both <literal>host</> and <literal>hostaddr</> are specified,
188 the value for <literal>hostaddr</> gives the server network address.
189 The value for <literal>host</> is ignored unless the
190 authentication method requires it, in which case it will be
191 used as the host name.
195 Note that authentication is likely to fail if <literal>host</>
196 is not the name of the server at network address <literal>hostaddr</>.
197 Also, note that <literal>host</> rather than <literal>hostaddr</>
198 is used to identify the connection in <filename>~/.pgpass</> (see
199 <xref linkend="libpq-pgpass">).
203 Without either a host name or host address,
204 <application>libpq</application> will connect using a
205 local Unix-domain socket; or on machines without Unix-domain
206 sockets, it will attempt to connect to <literal>localhost</>.
211 <varlistentry id="libpq-connect-port" xreflabel="port">
212 <term><literal>port</literal></term>
215 Port number to connect to at the server host, or socket file
216 name extension for Unix-domain
217 connections.<indexterm><primary>port</></>
222 <varlistentry id="libpq-connect-dbname" xreflabel="dbname">
223 <term><literal>dbname</literal></term>
226 The database name. Defaults to be the same as the user name.
231 <varlistentry id="libpq-connect-user" xreflabel="user">
232 <term><literal>user</literal></term>
235 <productname>PostgreSQL</productname> user name to connect as.
236 Defaults to be the same as the operating system name of the user
237 running the application.
242 <varlistentry id="libpq-connect-password" xreflabel="password">
243 <term><literal>password</literal></term>
246 Password to be used if the server demands password authentication.
251 <varlistentry id="libpq-connect-connect-timeout" xreflabel="connect_timeout">
252 <term><literal>connect_timeout</literal></term>
255 Maximum wait for connection, in seconds (write as a decimal integer
256 string). Zero or not specified means wait indefinitely. It is not
257 recommended to use a timeout of less than 2 seconds.
262 <varlistentry id="libpq-connect-client-encoding" xreflabel="client_encoding">
263 <term><literal>client_encoding</literal></term>
266 This sets the <varname>client_encoding</varname>
267 configuration parameter for this connection. In addition to
268 the values accepted by the corresponding server option, you
269 can use <literal>auto</literal> to determine the right
270 encoding from the current locale in the client
271 (<envar>LC_CTYPE</envar> environment variable on Unix
277 <varlistentry id="libpq-connect-options" xreflabel="options">
278 <term><literal>options</literal></term>
281 Adds command-line options to send to the server at run-time.
282 For example, setting this to <literal>-c geqo=off</> sets the
283 session's value of the <varname>geqo</> parameter to
284 <literal>off</>. For a detailed discussion of the available
285 options, consult <xref linkend="runtime-config">.
290 <varlistentry id="libpq-connect-application-name" xreflabel="application_name">
291 <term><literal>application_name</literal></term>
294 Specifies a value for the <xref linkend="guc-application-name">
295 configuration parameter.
300 <varlistentry id="libpq-connect-fallback-application-name" xreflabel="fallback_application_name">
301 <term><literal>fallback_application_name</literal></term>
304 Specifies a fallback value for the <xref
305 linkend="guc-application-name"> configuration parameter.
306 This value will be used if no value has been given for
307 <literal>application_name</> via a connection parameter or the
308 <envar>PGAPPNAME</envar> environment variable. Specifying
309 a fallback name is useful in generic utility programs that
310 wish to set a default application name but allow it to be
311 overridden by the user.
316 <varlistentry id="libpq-keepalives" xreflabel="keepalives">
317 <term><literal>keepalives</literal></term>
320 Controls whether client-side TCP keepalives are used. The default
321 value is 1, meaning on, but you can change this to 0, meaning off,
322 if keepalives are not wanted. This parameter is ignored for
323 connections made via a Unix-domain socket.
328 <varlistentry id="libpq-keepalives-idle" xreflabel="keepalives_idle">
329 <term><literal>keepalives_idle</literal></term>
332 Controls the number of seconds of inactivity after which TCP should
333 send a keepalive message to the server. A value of zero uses the
334 system default. This parameter is ignored for connections made via a
335 Unix-domain socket, or if keepalives are disabled. It is only supported
336 on systems where the <symbol>TCP_KEEPIDLE</> or <symbol>TCP_KEEPALIVE</>
337 socket option is available, and on Windows; on other systems, it has no
343 <varlistentry id="libpq-keepalives-interval" xreflabel="keepalives_interval">
344 <term><literal>keepalives_interval</literal></term>
347 Controls the number of seconds after which a TCP keepalive message
348 that is not acknowledged by the server should be retransmitted. A
349 value of zero uses the system default. This parameter is ignored for
350 connections made via a Unix-domain socket, or if keepalives are disabled.
351 It is only supported on systems where the <symbol>TCP_KEEPINTVL</>
352 socket option is available, and on Windows; on other systems, it has no
358 <varlistentry id="libpq-keepalives-count" xreflabel="keepalives_count">
359 <term><literal>keepalives_count</literal></term>
362 Controls the number of TCP keepalives that can be lost before the
363 client's connection to the server is considered dead. A value of
364 zero uses the system default. This parameter is ignored for
365 connections made via a Unix-domain socket, or if keepalives are disabled.
366 It is only supported on systems where the <symbol>TCP_KEEPINTVL</>
367 socket option is available; on other systems, it has no effect.
372 <varlistentry id="libpq-connect-tty" xreflabel="tty">
373 <term><literal>tty</literal></term>
376 Ignored (formerly, this specified where to send server debug output).
381 <varlistentry id="libpq-connect-sslmode" xreflabel="sslmode">
382 <term><literal>sslmode</literal></term>
385 This option determines whether or with what priority a secure
386 <acronym>SSL</> TCP/IP connection will be negotiated with the
387 server. There are six modes:
390 <table id="libpq-connect-sslmode-options">
391 <title><literal>sslmode</literal> Options</title>
395 <entry>Option</entry>
396 <entry>Description</entry>
403 <entry><literal>disable</></entry>
404 <entry>only try a non-<acronym>SSL</> connection</entry>
408 <entry><literal>allow</></entry>
409 <entry>first try a non-<acronym>SSL</>
410 connection; if that fails, try an <acronym>SSL</>
415 <entry><literal>prefer</> (default)</entry>
416 <entry>first try an <acronym>SSL</> connection; if
417 that fails, try a non-<acronym>SSL</>
422 <entry><literal>require</></entry>
423 <entry>only try an <acronym>SSL</> connection</entry>
427 <entry><literal>verify-ca</></entry>
428 <entry>only try an <acronym>SSL</> connection, and verify that
429 the server certificate is issued by a trusted certificate
430 authority (<acronym>CA</>)</entry>
434 <entry><literal>verify-full</></entry>
435 <entry>only try an <acronym>SSL</> connection, verify that
436 the server certificate is issued by a trusted <acronym>CA</> and
437 that the server host name matches that in the certificate</entry>
445 See <xref linkend="libpq-ssl"> for a detailed description of how
450 <literal>sslmode</> is ignored for Unix domain socket
452 If <productname>PostgreSQL</> is compiled without SSL support,
453 using options <literal>require</>, <literal>verify-ca</>, or
454 <literal>verify-full</> will cause an error, while
455 options <literal>allow</> and <literal>prefer</> will be
456 accepted but <application>libpq</> will not actually attempt
458 connection.<indexterm><primary>SSL</><secondary
459 sortas="libpq">with libpq</></indexterm>
464 <varlistentry id="libpq-connect-requiressl" xreflabel="requiressl">
465 <term><literal>requiressl</literal></term>
468 This option is deprecated in favor of the <literal>sslmode</>
473 If set to 1, an <acronym>SSL</acronym> connection to the server
474 is required (this is equivalent to <literal>sslmode</>
475 <literal>require</>). <application>libpq</> will then refuse
476 to connect if the server does not accept an
477 <acronym>SSL</acronym> connection. If set to 0 (default),
478 <application>libpq</> will negotiate the connection type with
479 the server (equivalent to <literal>sslmode</>
480 <literal>prefer</>). This option is only available if
481 <productname>PostgreSQL</> is compiled with SSL support.
486 <varlistentry id="libpq-connect-sslcert" xreflabel="sslcert">
487 <term><literal>sslcert</literal></term>
490 This parameter specifies the file name of the client SSL
491 certificate, replacing the default
492 <filename>~/.postgresql/postgresql.crt</>.
493 This parameter is ignored if an SSL connection is not made.
498 <varlistentry id="libpq-connect-sslkey" xreflabel="sslkey">
499 <term><literal>sslkey</literal></term>
502 This parameter specifies the location for the secret key used for
503 the client certificate. It can either specify a file name that will
504 be used instead of the default
505 <filename>~/.postgresql/postgresql.key</>, or it can specify a key
506 obtained from an external <quote>engine</> (engines are
507 <productname>OpenSSL</> loadable modules). An external engine
508 specification should consist of a colon-separated engine name and
509 an engine-specific key identifier. This parameter is ignored if an
510 SSL connection is not made.
515 <varlistentry id="libpq-connect-sslrootcert" xreflabel="sslrootcert">
516 <term><literal>sslrootcert</literal></term>
519 This parameter specifies the name of a file containing SSL
520 certificate authority (<acronym>CA</>) certificate(s).
521 If the file exists, the server's certificate will be verified
522 to be signed by one of these authorities. The default is
523 <filename>~/.postgresql/root.crt</>.
528 <varlistentry id="libpq-connect-sslcrl" xreflabel="sslcrl">
529 <term><literal>sslcrl</literal></term>
532 This parameter specifies the file name of the SSL certificate
533 revocation list (CRL). Certificates listed in this file, if it
534 exists, will be rejected while attempting to authenticate the
535 server's certificate. The default is
536 <filename>~/.postgresql/root.crl</>.
541 <varlistentry id="libpq-connect-requirepeer" xreflabel="requirepeer">
542 <term><literal>requirepeer</literal></term>
545 For Unix-domain socket connections, if this parameter is
546 set, the client checks at the beginning of the connection
547 that the server process runs under the specified user name,
548 otherwise the connection is aborted with an error. This
549 parameter can be used to achieve the kind of server
550 authentication that SSL certificates achieve on TCP/IP
551 connections. (Note that if the Unix-domain socket is
552 in <filename>/tmp</filename> or another publically writable
553 location, any user could start a server there. Use this
554 parameter to ensure that you are connected to a server run
556 e.g., <literal>requirepeer=postgres</literal>.) This
557 option is only supported on some platforms, currently
558 Linux, FreeBSD, NetBSD, OpenBSD, and Solaris.
563 <varlistentry id="libpq-connect-krbsrvname" xreflabel="krbsrvname">
564 <term><literal>krbsrvname</literal></term>
567 Kerberos service name to use when authenticating with Kerberos 5
569 This must match the service name specified in the server
570 configuration for Kerberos authentication to succeed. (See also
571 <xref linkend="kerberos-auth"> and <xref linkend="gssapi-auth">.)
576 <varlistentry id="libpq-connect-gsslib" xreflabel="gsslib">
577 <term><literal>gsslib</literal></term>
580 GSS library to use for GSSAPI authentication. Only used on Windows.
581 Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
582 library for authentication instead of the default SSPI.
587 <varlistentry id="libpq-connect-service" xreflabel="service">
588 <term><literal>service</literal></term>
591 Service name to use for additional parameters. It specifies a service
592 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
593 This allows applications to specify only a service name so connection parameters
594 can be centrally maintained. See <xref linkend="libpq-pgservice">.
600 If any parameter is unspecified, then the corresponding
601 environment variable (see <xref linkend="libpq-envars">)
602 is checked. If the environment variable is not set either,
603 then the indicated built-in defaults are used.
607 If <literal>expand_dbname</literal> is non-zero and
608 <parameter>dbname</parameter> contains an <symbol>=</symbol> sign, it
609 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
610 if it had been passed to <function>PQconnectdb</function>(see below). Previously
611 processed key words will be overridden by key words in the
612 <parameter>conninfo</parameter> string.
616 In general key words are processed from the beginning of these arrays in index
617 order. The effect of this is that when key words are repeated, the last processed
618 value is retained. Therefore, through careful placement of the
619 <parameter>dbname</parameter> key word, it is possible to determine what may
620 be overridden by a <parameter>conninfo</parameter> string, and what may not.
626 <varlistentry id="libpq-pqconnectdb">
627 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
630 Makes a new connection to the database server.
633 PGconn *PQconnectdb(const char *conninfo);
638 This function opens a new database connection using the parameters taken
639 from the string <literal>conninfo</literal>.
643 The passed string can be empty to use all default parameters, or it can
644 contain one or more parameter settings separated by whitespace.
645 Each parameter setting is in the form <literal>keyword = value</literal>.
646 Spaces around the equal sign are optional. To write an empty value,
647 or a value containing spaces, surround it with single quotes, e.g.,
648 <literal>keyword = 'a value'</literal>. Single quotes and backslashes
649 within the value must be escaped with a backslash, i.e.,
650 <literal>\'</literal> and <literal>\\</literal>.
654 The currently recognized parameter key words are the same as above.
659 <varlistentry id="libpq-pqsetdblogin">
660 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
663 Makes a new connection to the database server.
665 PGconn *PQsetdbLogin(const char *pghost,
667 const char *pgoptions,
676 This is the predecessor of <function>PQconnectdb</function> with a fixed
677 set of parameters. It has the same functionality except that the
678 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
679 empty string for any one of the fixed parameters that is to be defaulted.
683 If the <parameter>dbName</parameter> contains an <symbol>=</symbol> sign, it
684 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
685 if it had been passed to <function>PQconnectdb</function>, and the remaining
686 parameters are then applied as above.
691 <varlistentry id="libpq-pqsetdb">
692 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
695 Makes a new connection to the database server.
697 PGconn *PQsetdb(char *pghost,
706 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
707 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
708 for backward compatibility with very old programs.
713 <varlistentry id="libpq-pqconnectstartparams">
714 <term><function>PQconnectStartParams</function><indexterm><primary>PQconnectStartParams</></></term>
715 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
716 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
719 <indexterm><primary>nonblocking connection</primary></indexterm>
720 Make a connection to the database server in a nonblocking manner.
723 PGconn *PQconnectStartParams(const char **keywords,
727 PGconn *PQconnectStart(const char *conninfo);
729 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
734 These three functions are used to open a connection to a database server such
735 that your application's thread of execution is not blocked on remote I/O
736 whilst doing so. The point of this approach is that the waits for I/O to
737 complete can occur in the application's main loop, rather than down inside
738 <function>PQconnectdbParams</> or <function>PQconnectdb</>, and so the
739 application can manage this operation in parallel with other activities.
743 With <function>PQconnectStartParams</function>, the database connection is made
744 using the parameters taken from the <literal>keywords</literal> and
745 <literal>values</literal> arrays, and controlled by <literal>expand_dbname</literal>,
746 as described above for <function>PQconnectdbParams</function>.
750 With <function>PQconnectStart</function>, the database connection is made
751 using the parameters taken from the string <literal>conninfo</literal> as
752 described above for <function>PQconnectdb</function>.
756 Neither <function>PQconnectStartParams</function> nor <function>PQconnectStart</function>
757 nor <function>PQconnectPoll</function> will block, so long as a number of
758 restrictions are met:
762 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
763 name and reverse name queries are not made. See the documentation of
764 these parameters under <function>PQconnectdbParams</function> above for details.
770 If you call <function>PQtrace</function>, ensure that the stream object
771 into which you trace will not block.
777 You ensure that the socket is in the appropriate state
778 before calling <function>PQconnectPoll</function>, as described below.
785 Note: use of <function>PQconnectStartParams</> is analogous to
786 <function>PQconnectStart</> shown below.
790 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
791 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
792 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
793 representing a valid connection to the database). On return from
794 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
795 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
799 If <function>PQconnectStart</> succeeds, the next stage is to poll
800 <application>libpq</> so that it can proceed with the connection sequence.
801 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
802 socket underlying the database connection.
803 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
804 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
805 read (as indicated by <function>select()</>, <function>poll()</>, or
806 similar system function).
807 Then call <function>PQconnectPoll(conn)</function> again.
808 Conversely, if <function>PQconnectPoll(conn)</function> last returned
809 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
810 to write, then call <function>PQconnectPoll(conn)</function> again.
811 If you have yet to call
812 <function>PQconnectPoll</function>, i.e., just after the call to
813 <function>PQconnectStart</function>, behave as if it last returned
814 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
815 <function>PQconnectPoll(conn)</function> returns
816 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
817 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
818 has been successfully made.
822 At any time during connection, the status of the connection can be
823 checked by calling <function>PQstatus</>. If this call returns <symbol>CONNECTION_BAD</>, then the
824 connection procedure has failed; if the call returns <function>CONNECTION_OK</>, then the
825 connection is ready. Both of these states are equally detectable
826 from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
827 during (and only during) an asynchronous connection procedure. These
828 indicate the current stage of the connection procedure and might be useful
829 to provide feedback to the user for example. These statuses are:
832 <varlistentry id="libpq-connection-started">
833 <term><symbol>CONNECTION_STARTED</symbol></term>
836 Waiting for connection to be made.
841 <varlistentry id="libpq-connection-made">
842 <term><symbol>CONNECTION_MADE</symbol></term>
845 Connection OK; waiting to send.
850 <varlistentry id="libpq-connection-awaiting-response">
851 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
854 Waiting for a response from the server.
859 <varlistentry id="libpq-connection-auth-ok">
860 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
863 Received authentication; waiting for backend start-up to finish.
868 <varlistentry id="libpq-connection-ssl-startup">
869 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
872 Negotiating SSL encryption.
877 <varlistentry id="libpq-connection-setenv">
878 <term><symbol>CONNECTION_SETENV</symbol></term>
881 Negotiating environment-driven parameter settings.
887 Note that, although these constants will remain (in order to maintain
888 compatibility), an application should never rely upon these occurring in a
889 particular order, or at all, or on the status always being one of these
890 documented values. An application might do something like this:
892 switch(PQstatus(conn))
894 case CONNECTION_STARTED:
895 feedback = "Connecting...";
898 case CONNECTION_MADE:
899 feedback = "Connected to server...";
905 feedback = "Connecting...";
911 The <literal>connect_timeout</literal> connection parameter is ignored
912 when using <function>PQconnectPoll</function>; it is the application's
913 responsibility to decide whether an excessive amount of time has elapsed.
914 Otherwise, <function>PQconnectStart</function> followed by a
915 <function>PQconnectPoll</function> loop is equivalent to
916 <function>PQconnectdb</function>.
920 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
921 <function>PQfinish</function> when you are finished with it, in order to dispose of
922 the structure and any associated memory blocks. This must be done even if
923 the connection attempt fails or is abandoned.
928 <varlistentry id="libpq-pqconndefaults">
929 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
932 Returns the default connection options.
934 PQconninfoOption *PQconndefaults(void);
938 char *keyword; /* The keyword of the option */
939 char *envvar; /* Fallback environment variable name */
940 char *compiled; /* Fallback compiled in default value */
941 char *val; /* Option's current value, or NULL */
942 char *label; /* Label for field in connect dialog */
943 char *dispchar; /* Indicates how to display this field
944 in a connect dialog. Values are:
945 "" Display entered value as is
946 "*" Password field - hide value
947 "D" Debug option - don't show by default */
948 int dispsize; /* Field size in characters for dialog */
954 Returns a connection options array. This can be used to determine
955 all possible <function>PQconnectdb</function> options and their
956 current default values. The return value points to an array of
957 <structname>PQconninfoOption</structname> structures, which ends
958 with an entry having a null <structfield>keyword</> pointer. The
959 null pointer is returned if memory could not be allocated. Note that
960 the current default values (<structfield>val</structfield> fields)
961 will depend on environment variables and other context. Callers
962 must treat the connection options data as read-only.
966 After processing the options array, free it by passing it to
967 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
968 is leaked for each call to <function>PQconndefaults</function>.
974 <varlistentry id="libpq-pqconninfoparse">
975 <term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</></></term>
978 Returns parsed connection options from the provided connection string.
981 PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
986 Parses a connection string and returns the resulting options as an
987 array; or returns <symbol>NULL</> if there is a problem with the connection
988 string. This function can be used to extract
989 the <function>PQconnectdb</function> options in the provided
990 connection string. The return value points to an array of
991 <structname>PQconninfoOption</structname> structures, which ends
992 with an entry having a null <structfield>keyword</> pointer.
996 Note that only options explicitly specified in the string will have
997 values set in the result array; no defaults are inserted.
1001 If <literal>errmsg</> is not <symbol>NULL</>, then <literal>*errmsg</> is set
1002 to <symbol>NULL</> on success, else to a <function>malloc</>'d error string explaining
1003 the problem. (It is also possible for <literal>*errmsg</> to be
1004 set to <symbol>NULL</> and the function to return <symbol>NULL</>;
1005 this indicates an out-of-memory condition.)
1009 After processing the options array, free it by passing it to
1010 <function>PQconninfoFree</function>. If this is not done, some memory
1011 is leaked for each call to <function>PQconninfoParse</function>.
1012 Conversely, if an error occurs and <literal>errmsg</> is not <symbol>NULL</>,
1013 be sure to free the error string using <function>PQfreemem</>.
1019 <varlistentry id="libpq-pqfinish">
1020 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
1023 Closes the connection to the server. Also frees
1024 memory used by the <structname>PGconn</structname> object.
1026 void PQfinish(PGconn *conn);
1031 Note that even if the server connection attempt fails (as
1032 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
1033 to free the memory used by the <structname>PGconn</structname> object.
1034 The <structname>PGconn</> pointer must not be used again after
1035 <function>PQfinish</function> has been called.
1040 <varlistentry id="libpq-pqreset">
1041 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
1044 Resets the communication channel to the server.
1046 void PQreset(PGconn *conn);
1051 This function will close the connection
1052 to the server and attempt to reestablish a new
1053 connection to the same server, using all the same
1054 parameters previously used. This might be useful for
1055 error recovery if a working connection is lost.
1060 <varlistentry id="libpq-pqresetstart">
1061 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
1062 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
1065 Reset the communication channel to the server, in a nonblocking manner.
1068 int PQresetStart(PGconn *conn);
1070 PostgresPollingStatusType PQresetPoll(PGconn *conn);
1075 These functions will close the connection to the server and attempt to
1076 reestablish a new connection to the same server, using all the same
1077 parameters previously used. This can be useful for error recovery if a
1078 working connection is lost. They differ from <function>PQreset</function> (above) in that they
1079 act in a nonblocking manner. These functions suffer from the same
1080 restrictions as <function>PQconnectStartParams</>, <function>PQconnectStart</>
1081 and <function>PQconnectPoll</>.
1085 To initiate a connection reset, call
1086 <function>PQresetStart</function>. If it returns 0, the reset has
1087 failed. If it returns 1, poll the reset using
1088 <function>PQresetPoll</function> in exactly the same way as you
1089 would create the connection using <function>PQconnectPoll</function>.
1094 <varlistentry id="libpq-pqpingparams">
1095 <term><function>PQpingParams</function><indexterm><primary>PQpingParams</></></term>
1098 <function>PQpingParams</function> reports the status of the
1099 server. It accepts connection parameters identical to those of
1100 <function>PQconnectdbParams</>, described above. It is not, however,
1101 necessary to supply correct user name, password, or database name
1102 values to obtain the server status.
1105 PGPing PQpingParams(const char **keywords, const char **values, int expand_dbname);
1108 The function returns one of the following values:
1111 <varlistentry id="libpq-pqpingparams-pqping-ok">
1112 <term><literal>PQPING_OK</literal></term>
1115 The server is running and appears to be accepting connections.
1120 <varlistentry id="libpq-pqpingparams-pqping-reject">
1121 <term><literal>PQPING_REJECT</literal></term>
1124 The server is running but is in a state that disallows connections
1125 (startup, shutdown, or crash recovery).
1130 <varlistentry id="libpq-pqpingparams-pqping-no-response">
1131 <term><literal>PQPING_NO_RESPONSE</literal></term>
1134 The server could not be contacted. This might indicate that the
1135 server is not running, or that there is something wrong with the
1136 given connection parameters (for example, wrong port number), or
1137 that there is a network connectivity problem (for example, a
1138 firewall blocking the connection request).
1143 <varlistentry id="libpq-pqpingparams-pqping-no-attempt">
1144 <term><literal>PQPING_NO_ATTEMPT</literal></term>
1147 No attempt was made to contact the server, because the supplied
1148 parameters were obviously incorrect or there was some client-side
1149 problem (for example, out of memory).
1160 <varlistentry id="libpq-pqping">
1161 <term><function>PQping</function><indexterm><primary>PQping</></></term>
1164 <function>PQping</function> reports the status of the
1165 server. It accepts connection parameters identical to those of
1166 <function>PQconnectdb</>, described above. It is not, however,
1167 necessary to supply correct user name, password, or database name
1168 values to obtain the server status.
1171 PGPing PQping(const char *conninfo);
1176 The return values are the same as for <function>PQpingParams</>.
1186 <sect1 id="libpq-status">
1187 <title>Connection Status Functions</title>
1190 These functions can be used to interrogate the status
1191 of an existing database connection object.
1196 <indexterm><primary>libpq-fe.h</></>
1197 <indexterm><primary>libpq-int.h</></>
1198 <application>libpq</application> application programmers should be careful to
1199 maintain the <structname>PGconn</structname> abstraction. Use the accessor
1200 functions described below to get at the contents of <structname>PGconn</structname>.
1201 Reference to internal <structname>PGconn</structname> fields using
1202 <filename>libpq-int.h</> is not recommended because they are subject to change
1208 The following functions return parameter values established at connection.
1209 These values are fixed for the life of the <structname>PGconn</> object.
1212 <varlistentry id="libpq-pqdb">
1214 <function>PQdb</function>
1216 <primary>PQdb</primary>
1222 Returns the database name of the connection.
1224 char *PQdb(const PGconn *conn);
1230 <varlistentry id="libpq-pquser">
1232 <function>PQuser</function>
1234 <primary>PQuser</primary>
1240 Returns the user name of the connection.
1242 char *PQuser(const PGconn *conn);
1248 <varlistentry id="libpq-pqpass">
1250 <function>PQpass</function>
1252 <primary>PQpass</primary>
1258 Returns the password of the connection.
1260 char *PQpass(const PGconn *conn);
1266 <varlistentry id="libpq-pqhost">
1268 <function>PQhost</function>
1270 <primary>PQhost</primary>
1276 Returns the server host name of the connection.
1278 char *PQhost(const PGconn *conn);
1284 <varlistentry id="libpq-pqport">
1286 <function>PQport</function>
1288 <primary>PQport</primary>
1294 Returns the port of the connection.
1297 char *PQport(const PGconn *conn);
1303 <varlistentry id="libpq-pqtty">
1305 <function>PQtty</function>
1307 <primary>PQtty</primary>
1313 Returns the debug <acronym>TTY</acronym> of the connection.
1314 (This is obsolete, since the server no longer pays attention
1315 to the <acronym>TTY</acronym> setting, but the function remains
1316 for backward compatibility.)
1319 char *PQtty(const PGconn *conn);
1325 <varlistentry id="libpq-pqoptions">
1327 <function>PQoptions</function>
1329 <primary>PQoptions</primary>
1335 Returns the command-line options passed in the connection request.
1337 char *PQoptions(const PGconn *conn);
1346 The following functions return status data that can change as operations
1347 are executed on the <structname>PGconn</> object.
1350 <varlistentry id="libpq-pqstatus">
1352 <function>PQstatus</function>
1354 <primary>PQstatus</primary>
1360 Returns the status of the connection.
1362 ConnStatusType PQstatus(const PGconn *conn);
1367 The status can be one of a number of values. However, only two of
1368 these are seen outside of an asynchronous connection procedure:
1369 <literal>CONNECTION_OK</literal> and
1370 <literal>CONNECTION_BAD</literal>. A good connection to the database
1371 has the status <literal>CONNECTION_OK</literal>. A failed
1372 connection attempt is signaled by status
1373 <literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
1374 remain so until <function>PQfinish</function>, but a communications
1375 failure might result in the status changing to
1376 <literal>CONNECTION_BAD</literal> prematurely. In that case the
1377 application could try to recover by calling
1378 <function>PQreset</function>.
1382 See the entry for <function>PQconnectStartParams</>, <function>PQconnectStart</>
1383 and <function>PQconnectPoll</> with regards to other status codes that
1389 <varlistentry id="libpq-pqtransactionstatus">
1391 <function>PQtransactionStatus</function>
1393 <primary>PQtransactionStatus</primary>
1399 Returns the current in-transaction status of the server.
1402 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
1405 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
1406 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
1407 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
1408 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
1409 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
1410 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
1411 has been sent to the server and not yet completed.
1416 <function>PQtransactionStatus</> will give incorrect results when using
1417 a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
1418 set to off. The server-side autocommit feature has been
1419 deprecated and does not exist in later server versions.
1425 <varlistentry id="libpq-pqparameterstatus">
1427 <function>PQparameterStatus</function>
1429 <primary>PQparameterStatus</primary>
1435 Looks up a current parameter setting of the server.
1438 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
1441 Certain parameter values are reported by the server automatically at
1442 connection startup or whenever their values change.
1443 <function>PQparameterStatus</> can be used to interrogate these settings.
1444 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
1445 if the parameter is not known.
1449 Parameters reported as of the current release include
1450 <varname>server_version</>,
1451 <varname>server_encoding</>,
1452 <varname>client_encoding</>,
1453 <varname>application_name</>,
1454 <varname>is_superuser</>,
1455 <varname>session_authorization</>,
1456 <varname>DateStyle</>,
1457 <varname>IntervalStyle</>,
1458 <varname>TimeZone</>,
1459 <varname>integer_datetimes</>, and
1460 <varname>standard_conforming_strings</>.
1461 (<varname>server_encoding</>, <varname>TimeZone</>, and
1462 <varname>integer_datetimes</> were not reported by releases before 8.0;
1463 <varname>standard_conforming_strings</> was not reported by releases
1465 <varname>IntervalStyle</> was not reported by releases before 8.4;
1466 <varname>application_name</> was not reported by releases before 9.0.)
1468 <varname>server_version</>,
1469 <varname>server_encoding</> and
1470 <varname>integer_datetimes</>
1471 cannot change after startup.
1475 Pre-3.0-protocol servers do not report parameter settings, but
1476 <application>libpq</> includes logic to obtain values for
1477 <varname>server_version</> and <varname>client_encoding</> anyway.
1478 Applications are encouraged to use <function>PQparameterStatus</>
1479 rather than <foreignphrase>ad hoc</> code to determine these values.
1480 (Beware however that on a pre-3.0 connection, changing
1481 <varname>client_encoding</> via <command>SET</> after connection
1482 startup will not be reflected by <function>PQparameterStatus</>.)
1483 For <varname>server_version</>, see also
1484 <function>PQserverVersion</>, which returns the information in a
1485 numeric form that is much easier to compare against.
1489 If no value for <varname>standard_conforming_strings</> is reported,
1490 applications can assume it is <literal>off</>, that is, backslashes
1491 are treated as escapes in string literals. Also, the presence of
1492 this parameter can be taken as an indication that the escape string
1493 syntax (<literal>E'...'</>) is accepted.
1497 Although the returned pointer is declared <literal>const</>, it in fact
1498 points to mutable storage associated with the <literal>PGconn</> structure.
1499 It is unwise to assume the pointer will remain valid across queries.
1504 <varlistentry id="libpq-pqprotocolversion">
1506 <function>PQprotocolVersion</function>
1508 <primary>PQprotocolVersion</primary>
1514 Interrogates the frontend/backend protocol being used.
1516 int PQprotocolVersion(const PGconn *conn);
1518 Applications might wish to use this function to determine whether certain
1519 features are supported. Currently, the possible values are 2 (2.0
1520 protocol), 3 (3.0 protocol), or zero (connection bad). The
1521 protocol version will
1522 not change after connection startup is complete, but it could
1523 theoretically change during a connection reset. The 3.0 protocol
1524 will normally be used when communicating with
1525 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
1526 support only protocol 2.0. (Protocol 1.0 is obsolete and not
1527 supported by <application>libpq</application>.)
1532 <varlistentry id="libpq-pqserverversion">
1534 <function>PQserverVersion</function>
1536 <primary>PQserverVersion</primary>
1542 Returns an integer representing the backend version.
1544 int PQserverVersion(const PGconn *conn);
1546 Applications might use this function to determine the version of the database
1547 server they are connected to. The number is formed by converting
1548 the major, minor, and revision numbers into two-decimal-digit
1549 numbers and appending them together. For example, version 8.1.5
1550 will be returned as 80105, and version 8.2 will be returned as
1551 80200 (leading zeroes are not shown). Zero is returned if the
1557 <varlistentry id="libpq-pqerrormessage">
1559 <function>PQerrorMessage</function>
1561 <primary>PQerrorMessage</primary>
1567 <indexterm><primary>error message</></> Returns the error message
1568 most recently generated by an operation on the connection.
1571 char *PQerrorMessage(const PGconn *conn);
1577 Nearly all <application>libpq</> functions will set a message for
1578 <function>PQerrorMessage</function> if they fail. Note that by
1579 <application>libpq</application> convention, a nonempty
1580 <function>PQerrorMessage</function> result can consist of multiple lines,
1581 and will include a trailing newline. The caller should not free
1582 the result directly. It will be freed when the associated
1583 <structname>PGconn</> handle is passed to
1584 <function>PQfinish</function>. The result string should not be
1585 expected to remain the same across operations on the
1586 <literal>PGconn</> structure.
1591 <varlistentry id="libpq-pqsocket">
1592 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1595 Obtains the file descriptor number of the connection socket to
1596 the server. A valid descriptor will be greater than or equal
1597 to 0; a result of -1 indicates that no server connection is
1598 currently open. (This will not change during normal operation,
1599 but could change during connection setup or reset.)
1602 int PQsocket(const PGconn *conn);
1609 <varlistentry id="libpq-pqbackendpid">
1610 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1613 Returns the process <acronym>ID</acronym>
1614 (PID)<indexterm><primary>PID</><secondary>determining PID of
1615 server process</><tertiary>in libpq</></> of the backend
1616 process handling this connection.
1619 int PQbackendPID(const PGconn *conn);
1624 The backend <acronym>PID</acronym> is useful for debugging
1625 purposes and for comparison to <command>NOTIFY</command>
1626 messages (which include the <acronym>PID</acronym> of the
1627 notifying backend process). Note that the
1628 <acronym>PID</acronym> belongs to a process executing on the
1629 database server host, not the local host!
1634 <varlistentry id="libpq-pqconnectionneedspassword">
1635 <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
1638 Returns true (1) if the connection authentication method
1639 required a password, but none was available.
1640 Returns false (0) if not.
1643 int PQconnectionNeedsPassword(const PGconn *conn);
1648 This function can be applied after a failed connection attempt
1649 to decide whether to prompt the user for a password.
1654 <varlistentry id="libpq-pqconnectionusedpassword">
1655 <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
1658 Returns true (1) if the connection authentication method
1659 used a password. Returns false (0) if not.
1662 int PQconnectionUsedPassword(const PGconn *conn);
1667 This function can be applied after either a failed or successful
1668 connection attempt to detect whether the server demanded a password.
1673 <varlistentry id="libpq-pqgetssl">
1674 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1677 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1678 Returns the SSL structure used in the connection, or null
1679 if SSL is not in use.
1682 SSL *PQgetssl(const PGconn *conn);
1687 This structure can be used to verify encryption levels, check server
1688 certificates, and more. Refer to the <productname>OpenSSL</>
1689 documentation for information about this structure.
1693 You must define <symbol>USE_SSL</symbol> in order to get the
1694 correct prototype for this function. Doing so will also
1695 automatically include <filename>ssl.h</filename> from
1696 <productname>OpenSSL</productname>.
1706 <sect1 id="libpq-exec">
1707 <title>Command Execution Functions</title>
1710 Once a connection to a database server has been successfully
1711 established, the functions described here are used to perform
1712 SQL queries and commands.
1715 <sect2 id="libpq-exec-main">
1716 <title>Main Functions</title>
1720 <varlistentry id="libpq-pqexec">
1722 <function>PQexec</function>
1724 <primary>PQexec</primary>
1730 Submits a command to the server and waits for the result.
1733 PGresult *PQexec(PGconn *conn, const char *command);
1738 Returns a <structname>PGresult</structname> pointer or possibly a null
1739 pointer. A non-null pointer will generally be returned except in
1740 out-of-memory conditions or serious errors such as inability to send
1741 the command to the server. If a null pointer is returned, it should
1742 be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
1743 <function>PQerrorMessage</function> to get more information about such
1750 The command string can include multiple SQL commands
1751 (separated by semicolons). Multiple queries sent in a single
1752 <function>PQexec</> call are processed in a single transaction, unless
1753 there are explicit <command>BEGIN</command>/<command>COMMIT</command>
1754 commands included in the query string to divide it into multiple
1755 transactions. Note however that the returned
1756 <structname>PGresult</structname> structure describes only the result
1757 of the last command executed from the string. Should one of the
1758 commands fail, processing of the string stops with it and the returned
1759 <structname>PGresult</structname> describes the error condition.
1764 <varlistentry id="libpq-pqexecparams">
1766 <function>PQexecParams</function>
1768 <primary>PQexecParams</primary>
1774 Submits a command to the server and waits for the result,
1775 with the ability to pass parameters separately from the SQL
1779 PGresult *PQexecParams(PGconn *conn,
1780 const char *command,
1782 const Oid *paramTypes,
1783 const char * const *paramValues,
1784 const int *paramLengths,
1785 const int *paramFormats,
1791 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1792 functionality: parameter values can be specified separately from the command
1793 string proper, and query results can be requested in either text or binary
1794 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1795 connections; it will fail when using protocol 2.0.
1799 The function arguments are:
1803 <term><parameter>conn</parameter></term>
1807 The connection object to send the command through.
1813 <term><parameter>command</parameter></term>
1816 The SQL command string to be executed. If parameters are used,
1817 they are referred to in the command string as <literal>$1</>,
1818 <literal>$2</>, etc.
1824 <term><parameter>nParams</parameter></term>
1827 The number of parameters supplied; it is the length of the arrays
1828 <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1829 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1830 array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
1837 <term><parameter>paramTypes[]</parameter></term>
1840 Specifies, by OID, the data types to be assigned to the
1841 parameter symbols. If <parameter>paramTypes</> is
1842 <symbol>NULL</symbol>, or any particular element in the array
1843 is zero, the server infers a data type for the parameter symbol
1844 in the same way it would do for an untyped literal string.
1850 <term><parameter>paramValues[]</parameter></term>
1853 Specifies the actual values of the parameters. A null pointer
1854 in this array means the corresponding parameter is null;
1855 otherwise the pointer points to a zero-terminated text string
1856 (for text format) or binary data in the format expected by the
1857 server (for binary format).
1863 <term><parameter>paramLengths[]</parameter></term>
1866 Specifies the actual data lengths of binary-format parameters.
1867 It is ignored for null parameters and text-format parameters.
1868 The array pointer can be null when there are no binary parameters.
1874 <term><parameter>paramFormats[]</parameter></term>
1877 Specifies whether parameters are text (put a zero in the
1878 array entry for the corresponding parameter) or binary (put
1879 a one in the array entry for the corresponding parameter).
1880 If the array pointer is null then all parameters are presumed
1884 Values passed in binary format require knowledge of
1885 the internal representation expected by the backend.
1886 For example, integers must be passed in network byte
1887 order. Passing <type>numeric</> values requires
1888 knowledge of the server storage format, as implemented
1890 <filename>src/backend/utils/adt/numeric.c::numeric_send()</> and
1891 <filename>src/backend/utils/adt/numeric.c::numeric_recv()</>.
1897 <term><parameter>resultFormat</parameter></term>
1900 Specify zero to obtain results in text format, or one to obtain
1901 results in binary format. (There is not currently a provision
1902 to obtain different result columns in different formats,
1903 although that is possible in the underlying protocol.)
1915 The primary advantage of <function>PQexecParams</> over
1916 <function>PQexec</> is that parameter values can be separated from the
1917 command string, thus avoiding the need for tedious and error-prone
1918 quoting and escaping.
1922 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
1923 one SQL command in the given string. (There can be semicolons in it,
1924 but not more than one nonempty command.) This is a limitation of the
1925 underlying protocol, but has some usefulness as an extra defense against
1926 SQL-injection attacks.
1931 Specifying parameter types via OIDs is tedious, particularly if you prefer
1932 not to hard-wire particular OID values into your program. However, you can
1933 avoid doing so even in cases where the server by itself cannot determine the
1934 type of the parameter, or chooses a different type than you want. In the
1935 SQL command text, attach an explicit cast to the parameter symbol to show what
1936 data type you will send. For example:
1938 SELECT * FROM mytable WHERE x = $1::bigint;
1940 This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
1941 by default it would be assigned the same type as <literal>x</>. Forcing the
1942 parameter type decision, either this way or by specifying a numeric type OID,
1943 is strongly recommended when sending parameter values in binary format, because
1944 binary format has less redundancy than text format and so there is less chance
1945 that the server will detect a type mismatch mistake for you.
1951 <varlistentry id="libpq-pqprepare">
1952 <term><function>PQprepare</function>
1954 <primary>PQprepare</primary>
1960 Submits a request to create a prepared statement with the
1961 given parameters, and waits for completion.
1963 PGresult *PQprepare(PGconn *conn,
1964 const char *stmtName,
1967 const Oid *paramTypes);
1972 <function>PQprepare</> creates a prepared statement for later
1973 execution with <function>PQexecPrepared</>. This feature allows
1974 commands that will be used repeatedly to be parsed and planned just
1975 once, rather than each time they are executed.
1976 <function>PQprepare</> is supported only in protocol 3.0 and later
1977 connections; it will fail when using protocol 2.0.
1981 The function creates a prepared statement named
1982 <parameter>stmtName</> from the <parameter>query</> string, which
1983 must contain a single SQL command. <parameter>stmtName</> can be
1984 <literal>""</> to create an unnamed statement, in which case any
1985 pre-existing unnamed statement is automatically replaced; otherwise
1986 it is an error if the statement name is already defined in the
1987 current session. If any parameters are used, they are referred
1988 to in the query as <literal>$1</>, <literal>$2</>, etc.
1989 <parameter>nParams</> is the number of parameters for which types
1990 are pre-specified in the array <parameter>paramTypes[]</>. (The
1991 array pointer can be <symbol>NULL</symbol> when
1992 <parameter>nParams</> is zero.) <parameter>paramTypes[]</>
1993 specifies, by OID, the data types to be assigned to the parameter
1994 symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1995 or any particular element in the array is zero, the server assigns
1996 a data type to the parameter symbol in the same way it would do
1997 for an untyped literal string. Also, the query can use parameter
1998 symbols with numbers higher than <parameter>nParams</>; data types
1999 will be inferred for these symbols as well. (See
2000 <function>PQdescribePrepared</function> for a means to find out
2001 what data types were inferred.)
2005 As with <function>PQexec</>, the result is normally a
2006 <structname>PGresult</structname> object whose contents indicate
2007 server-side success or failure. A null result indicates
2008 out-of-memory or inability to send the command at all. Use
2009 <function>PQerrorMessage</function> to get more information about
2016 Prepared statements for use with <function>PQexecPrepared</> can also
2017 be created by executing SQL <xref linkend="sql-prepare">
2018 statements. Also, although there is no <application>libpq</>
2019 function for deleting a prepared statement, the SQL <xref
2020 linkend="sql-deallocate"> statement
2021 can be used for that purpose.
2026 <varlistentry id="libpq-pqexecprepared">
2028 <function>PQexecPrepared</function>
2030 <primary>PQexecPrepared</primary>
2036 Sends a request to execute a prepared statement with given
2037 parameters, and waits for the result.
2039 PGresult *PQexecPrepared(PGconn *conn,
2040 const char *stmtName,
2042 const char * const *paramValues,
2043 const int *paramLengths,
2044 const int *paramFormats,
2050 <function>PQexecPrepared</> is like <function>PQexecParams</>,
2051 but the command to be executed is specified by naming a
2052 previously-prepared statement, instead of giving a query string.
2053 This feature allows commands that will be used repeatedly to be
2054 parsed and planned just once, rather than each time they are
2055 executed. The statement must have been prepared previously in
2056 the current session. <function>PQexecPrepared</> is supported
2057 only in protocol 3.0 and later connections; it will fail when
2062 The parameters are identical to <function>PQexecParams</>, except that the
2063 name of a prepared statement is given instead of a query string, and the
2064 <parameter>paramTypes[]</> parameter is not present (it is not needed since
2065 the prepared statement's parameter types were determined when it was created).
2070 <varlistentry id="libpq-pqdescribeprepared">
2072 <function>PQdescribePrepared</function>
2074 <primary>PQdescribePrepared</primary>
2080 Submits a request to obtain information about the specified
2081 prepared statement, and waits for completion.
2083 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
2088 <function>PQdescribePrepared</> allows an application to obtain
2089 information about a previously prepared statement.
2090 <function>PQdescribePrepared</> is supported only in protocol 3.0
2091 and later connections; it will fail when using protocol 2.0.
2095 <parameter>stmtName</> can be <literal>""</> or <symbol>NULL</> to reference
2096 the unnamed statement, otherwise it must be the name of an existing
2097 prepared statement. On success, a <structname>PGresult</> with
2098 status <literal>PGRES_COMMAND_OK</literal> is returned. The
2099 functions <function>PQnparams</function> and
2100 <function>PQparamtype</function> can be applied to this
2101 <structname>PGresult</> to obtain information about the parameters
2102 of the prepared statement, and the functions
2103 <function>PQnfields</function>, <function>PQfname</function>,
2104 <function>PQftype</function>, etc provide information about the
2105 result columns (if any) of the statement.
2110 <varlistentry id="libpq-pqdescribeportal">
2112 <function>PQdescribePortal</function>
2114 <primary>PQdescribePortal</primary>
2120 Submits a request to obtain information about the specified
2121 portal, and waits for completion.
2123 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
2128 <function>PQdescribePortal</> allows an application to obtain
2129 information about a previously created portal.
2130 (<application>libpq</> does not provide any direct access to
2131 portals, but you can use this function to inspect the properties
2132 of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
2133 <function>PQdescribePortal</> is supported only in protocol 3.0
2134 and later connections; it will fail when using protocol 2.0.
2138 <parameter>portalName</> can be <literal>""</> or <symbol>NULL</> to reference
2139 the unnamed portal, otherwise it must be the name of an existing
2140 portal. On success, a <structname>PGresult</> with status
2141 <literal>PGRES_COMMAND_OK</literal> is returned. The functions
2142 <function>PQnfields</function>, <function>PQfname</function>,
2143 <function>PQftype</function>, etc can be applied to the
2144 <structname>PGresult</> to obtain information about the result
2145 columns (if any) of the portal.
2153 The <structname>PGresult</structname><indexterm><primary>PGresult</></>
2154 structure encapsulates the result returned by the server.
2155 <application>libpq</application> application programmers should be
2156 careful to maintain the <structname>PGresult</structname> abstraction.
2157 Use the accessor functions below to get at the contents of
2158 <structname>PGresult</structname>. Avoid directly referencing the
2159 fields of the <structname>PGresult</structname> structure because they
2160 are subject to change in the future.
2163 <varlistentry id="libpq-pqresultstatus">
2165 <function>PQresultStatus</function>
2167 <primary>PQresultStatus</primary>
2173 Returns the result status of the command.
2175 ExecStatusType PQresultStatus(const PGresult *res);
2180 <function>PQresultStatus</function> can return one of the following values:
2183 <varlistentry id="libpq-pgres-empty-query">
2184 <term><literal>PGRES_EMPTY_QUERY</literal></term>
2187 The string sent to the server was empty.
2192 <varlistentry id="libpq-pgres-command-ok">
2193 <term><literal>PGRES_COMMAND_OK</literal></term>
2196 Successful completion of a command returning no data.
2201 <varlistentry id="libpq-pgres-tuples-ok">
2202 <term><literal>PGRES_TUPLES_OK</literal></term>
2205 Successful completion of a command returning data (such as
2206 a <command>SELECT</> or <command>SHOW</>).
2211 <varlistentry id="libpq-pgres-copy-out">
2212 <term><literal>PGRES_COPY_OUT</literal></term>
2215 Copy Out (from server) data transfer started.
2220 <varlistentry id="libpq-pgres-copy-in">
2221 <term><literal>PGRES_COPY_IN</literal></term>
2224 Copy In (to server) data transfer started.
2229 <varlistentry id="libpq-pgres-bad-response">
2230 <term><literal>PGRES_BAD_RESPONSE</literal></term>
2233 The server's response was not understood.
2238 <varlistentry id="libpq-pgres-nonfatal-error">
2239 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
2242 A nonfatal error (a notice or warning) occurred.
2247 <varlistentry id="libpq-pgres-fatal-error">
2248 <term><literal>PGRES_FATAL_ERROR</literal></term>
2251 A fatal error occurred.
2256 <varlistentry id="libpq-pgres-copy-both">
2257 <term><literal>PGRES_COPY_BOTH</literal></term>
2260 Copy In/Out (to and from server) data transfer started. This is
2261 currently used only for streaming replication.
2267 If the result status is <literal>PGRES_TUPLES_OK</literal>, then
2268 the functions described below can be used to retrieve the rows
2269 returned by the query. Note that a <command>SELECT</command>
2270 command that happens to retrieve zero rows still shows
2271 <literal>PGRES_TUPLES_OK</literal>.
2272 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
2273 return rows (<command>INSERT</command>, <command>UPDATE</command>,
2274 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
2275 indicate a bug in the client software.
2279 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
2280 never be returned directly by <function>PQexec</function> or other
2281 query execution functions; results of this kind are instead passed
2282 to the notice processor (see <xref
2283 linkend="libpq-notice-processing">).
2288 <varlistentry id="libpq-pqresstatus">
2290 <function>PQresStatus</function>
2292 <primary>PQresStatus</primary>
2298 Converts the enumerated type returned by
2299 <function>PQresultStatus</> into a string constant describing the
2300 status code. The caller should not free the result.
2303 char *PQresStatus(ExecStatusType status);
2309 <varlistentry id="libpq-pqresulterrormessage">
2311 <function>PQresultErrorMessage</function>
2313 <primary>PQresultErrorMessage</primary>
2319 Returns the error message associated with the command, or an empty string
2320 if there was no error.
2322 char *PQresultErrorMessage(const PGresult *res);
2324 If there was an error, the returned string will include a trailing
2325 newline. The caller should not free the result directly. It will
2326 be freed when the associated <structname>PGresult</> handle is
2327 passed to <function>PQclear</function>.
2331 Immediately following a <function>PQexec</function> or
2332 <function>PQgetResult</function> call,
2333 <function>PQerrorMessage</function> (on the connection) will return
2334 the same string as <function>PQresultErrorMessage</function> (on
2335 the result). However, a <structname>PGresult</structname> will
2336 retain its error message until destroyed, whereas the connection's
2337 error message will change when subsequent operations are done.
2338 Use <function>PQresultErrorMessage</function> when you want to
2339 know the status associated with a particular
2340 <structname>PGresult</structname>; use
2341 <function>PQerrorMessage</function> when you want to know the
2342 status from the latest operation on the connection.
2347 <varlistentry id="libpq-pqresulterrorfield">
2348 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
2351 Returns an individual field of an error report.
2353 char *PQresultErrorField(const PGresult *res, int fieldcode);
2355 <parameter>fieldcode</> is an error field identifier; see the symbols
2356 listed below. <symbol>NULL</symbol> is returned if the
2357 <structname>PGresult</structname> is not an error or warning result,
2358 or does not include the specified field. Field values will normally
2359 not include a trailing newline. The caller should not free the
2360 result directly. It will be freed when the
2361 associated <structname>PGresult</> handle is passed to
2362 <function>PQclear</function>.
2366 The following field codes are available:
2368 <varlistentry id="libpq-pg-diag-severity">
2369 <term><symbol>PG_DIAG_SEVERITY</></term>
2372 The severity; the field contents are <literal>ERROR</>,
2373 <literal>FATAL</>, or <literal>PANIC</> (in an error message),
2374 or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
2375 <literal>INFO</>, or <literal>LOG</> (in a notice message), or
2376 a localized translation of one of these. Always present.
2381 <varlistentry id="libpq-pg-diag-sqlstate">
2383 <primary>error codes</primary>
2384 <secondary>libpq</secondary>
2386 <term><symbol>PG_DIAG_SQLSTATE</></term>
2389 The SQLSTATE code for the error. The SQLSTATE code identifies
2390 the type of error that has occurred; it can be used by
2391 front-end applications to perform specific operations (such
2392 as error handling) in response to a particular database error.
2393 For a list of the possible SQLSTATE codes, see <xref
2394 linkend="errcodes-appendix">. This field is not localizable,
2395 and is always present.
2400 <varlistentry id="libpq-pg-diag-message-primary">
2401 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
2404 The primary human-readable error message (typically one line).
2410 <varlistentry id="libpq-pg-diag-message-detail">
2411 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
2414 Detail: an optional secondary error message carrying more
2415 detail about the problem. Might run to multiple lines.
2420 <varlistentry id="libpq-pg-diag-message-hint">
2421 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
2424 Hint: an optional suggestion what to do about the problem.
2425 This is intended to differ from detail in that it offers advice
2426 (potentially inappropriate) rather than hard facts. Might
2427 run to multiple lines.
2432 <varlistentry id="libpq-pg-diag-statement-position">
2433 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
2436 A string containing a decimal integer indicating an error cursor
2437 position as an index into the original statement string. The
2438 first character has index 1, and positions are measured in
2439 characters not bytes.
2444 <varlistentry id="libpq-pg-diag-internal-position">
2445 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
2448 This is defined the same as the
2449 <symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
2450 when the cursor position refers to an internally generated
2451 command rather than the one submitted by the client. The
2452 <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
2458 <varlistentry id="libpq-pg-diag-internal-query">
2459 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
2462 The text of a failed internally-generated command. This could
2463 be, for example, a SQL query issued by a PL/pgSQL function.
2468 <varlistentry id="libpq-pg-diag-context">
2469 <term><symbol>PG_DIAG_CONTEXT</></term>
2472 An indication of the context in which the error occurred.
2473 Presently this includes a call stack traceback of active
2474 procedural language functions and internally-generated queries.
2475 The trace is one entry per line, most recent first.
2480 <varlistentry id="libpq-pg-diag-source-file">
2481 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
2484 The file name of the source-code location where the error was
2490 <varlistentry id="libpq-pg-diag-source-line">
2491 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
2494 The line number of the source-code location where the error
2500 <varlistentry id="libpq-pg-diag-source-function">
2501 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
2504 The name of the source-code function reporting the error.
2512 The client is responsible for formatting displayed information to meet
2513 its needs; in particular it should break long lines as needed.
2514 Newline characters appearing in the error message fields should be
2515 treated as paragraph breaks, not line breaks.
2519 Errors generated internally by <application>libpq</application> will
2520 have severity and primary message, but typically no other fields.
2521 Errors returned by a pre-3.0-protocol server will include severity and
2522 primary message, and sometimes a detail message, but no other fields.
2526 Note that error fields are only available from
2527 <structname>PGresult</structname> objects, not
2528 <structname>PGconn</structname> objects; there is no
2529 <function>PQerrorField</function> function.
2534 <varlistentry id="libpq-pqclear">
2535 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
2538 Frees the storage associated with a
2539 <structname>PGresult</structname>. Every command result should be
2540 freed via <function>PQclear</function> when it is no longer
2544 void PQclear(PGresult *res);
2549 You can keep a <structname>PGresult</structname> object around for
2550 as long as you need it; it does not go away when you issue a new
2551 command, nor even if you close the connection. To get rid of it,
2552 you must call <function>PQclear</function>. Failure to do this
2553 will result in memory leaks in your application.
2561 <sect2 id="libpq-exec-select-info">
2562 <title>Retrieving Query Result Information</title>
2565 These functions are used to extract information from a
2566 <structname>PGresult</structname> object that represents a successful
2567 query result (that is, one that has status
2568 <literal>PGRES_TUPLES_OK</literal>). They can also be used to extract
2569 information from a successful Describe operation: a Describe's result
2570 has all the same column information that actual execution of the query
2571 would provide, but it has zero rows. For objects with other status values,
2572 these functions will act as though the result has zero rows and zero columns.
2576 <varlistentry id="libpq-pqntuples">
2578 <function>PQntuples</function>
2580 <primary>PQntuples</primary>
2586 Returns the number of rows (tuples) in the query result. Because
2587 it returns an integer result, large result sets might overflow the
2588 return value on 32-bit operating systems.
2591 int PQntuples(const PGresult *res);
2598 <varlistentry id="libpq-pqnfields">
2600 <function>PQnfields</function>
2602 <primary>PQnfields</primary>
2608 Returns the number of columns (fields) in each row of the query
2612 int PQnfields(const PGresult *res);
2618 <varlistentry id="libpq-pqfname">
2620 <function>PQfname</function>
2622 <primary>PQfname</primary>
2628 Returns the column name associated with the given column number.
2629 Column numbers start at 0. The caller should not free the result
2630 directly. It will be freed when the associated
2631 <structname>PGresult</> handle is passed to
2632 <function>PQclear</function>.
2634 char *PQfname(const PGresult *res,
2640 <symbol>NULL</symbol> is returned if the column number is out of range.
2645 <varlistentry id="libpq-pqfnumber">
2647 <function>PQfnumber</function>
2649 <primary>PQfnumber</primary>
2655 Returns the column number associated with the given column name.
2657 int PQfnumber(const PGresult *res,
2658 const char *column_name);
2663 -1 is returned if the given name does not match any column.
2667 The given name is treated like an identifier in an SQL command,
2668 that is, it is downcased unless double-quoted. For example, given
2669 a query result generated from the SQL command:
2671 SELECT 1 AS FOO, 2 AS "BAR";
2673 we would have the results:
2675 PQfname(res, 0) <lineannotation>foo</lineannotation>
2676 PQfname(res, 1) <lineannotation>BAR</lineannotation>
2677 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
2678 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
2679 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
2680 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
2686 <varlistentry id="libpq-pqftable">
2688 <function>PQftable</function>
2690 <primary>PQftable</primary>
2696 Returns the OID of the table from which the given column was
2697 fetched. Column numbers start at 0.
2699 Oid PQftable(const PGresult *res,
2705 <literal>InvalidOid</> is returned if the column number is out of range,
2706 or if the specified column is not a simple reference to a table column,
2707 or when using pre-3.0 protocol.
2708 You can query the system table <literal>pg_class</literal> to determine
2709 exactly which table is referenced.
2713 The type <type>Oid</type> and the constant
2714 <literal>InvalidOid</literal> will be defined when you include
2715 the <application>libpq</application> header file. They will both
2716 be some integer type.
2721 <varlistentry id="libpq-pqftablecol">
2723 <function>PQftablecol</function>
2725 <primary>PQftablecol</primary>
2731 Returns the column number (within its table) of the column making
2732 up the specified query result column. Query-result column numbers
2733 start at 0, but table columns have nonzero numbers.
2735 int PQftablecol(const PGresult *res,
2741 Zero is returned if the column number is out of range, or if the
2742 specified column is not a simple reference to a table column, or
2743 when using pre-3.0 protocol.
2748 <varlistentry id="libpq-pqfformat">
2750 <function>PQfformat</function>
2752 <primary>PQfformat</primary>
2758 Returns the format code indicating the format of the given
2759 column. Column numbers start at 0.
2761 int PQfformat(const PGresult *res,
2767 Format code zero indicates textual data representation, while format
2768 code one indicates binary representation. (Other codes are reserved
2769 for future definition.)
2774 <varlistentry id="libpq-pqftype">
2776 <function>PQftype</function>
2778 <primary>PQftype</primary>
2784 Returns the data type associated with the given column number.
2785 The integer returned is the internal OID number of the type.
2786 Column numbers start at 0.
2788 Oid PQftype(const PGresult *res,
2794 You can query the system table <literal>pg_type</literal> to
2795 obtain the names and properties of the various data types. The
2796 <acronym>OID</acronym>s of the built-in data types are defined
2797 in the file <filename>src/include/catalog/pg_type.h</filename>
2803 <varlistentry id="libpq-pqfmod">
2805 <function>PQfmod</function>
2807 <primary>PQfmod</primary>
2813 Returns the type modifier of the column associated with the
2814 given column number. Column numbers start at 0.
2816 int PQfmod(const PGresult *res,
2822 The interpretation of modifier values is type-specific; they
2823 typically indicate precision or size limits. The value -1 is
2824 used to indicate <quote>no information available</>. Most data
2825 types do not use modifiers, in which case the value is always
2831 <varlistentry id="libpq-pqfsize">
2833 <function>PQfsize</function>
2835 <primary>PQfsize</primary>
2841 Returns the size in bytes of the column associated with the
2842 given column number. Column numbers start at 0.
2844 int PQfsize(const PGresult *res,
2850 <function>PQfsize</> returns the space allocated for this column
2851 in a database row, in other words the size of the server's
2852 internal representation of the data type. (Accordingly, it is
2853 not really very useful to clients.) A negative value indicates
2854 the data type is variable-length.
2859 <varlistentry id="libpq-pqbinarytuples">
2861 <function>PQbinaryTuples</function>
2863 <primary>PQbinaryTuples</primary>
2869 Returns 1 if the <structname>PGresult</> contains binary data
2870 and 0 if it contains text data.
2872 int PQbinaryTuples(const PGresult *res);
2877 This function is deprecated (except for its use in connection with
2878 <command>COPY</>), because it is possible for a single
2879 <structname>PGresult</> to contain text data in some columns and
2880 binary data in others. <function>PQfformat</> is preferred.
2881 <function>PQbinaryTuples</> returns 1 only if all columns of the
2882 result are binary (format 1).
2887 <varlistentry id="libpq-pqgetvalue">
2889 <function>PQgetvalue</function>
2891 <primary>PQgetvalue</primary>
2897 Returns a single field value of one row of a
2898 <structname>PGresult</structname>. Row and column numbers start
2899 at 0. The caller should not free the result directly. It will
2900 be freed when the associated <structname>PGresult</> handle is
2901 passed to <function>PQclear</function>.
2903 char *PQgetvalue(const PGresult *res,
2910 For data in text format, the value returned by
2911 <function>PQgetvalue</function> is a null-terminated character
2912 string representation of the field value. For data in binary
2913 format, the value is in the binary representation determined by
2914 the data type's <function>typsend</> and <function>typreceive</>
2915 functions. (The value is actually followed by a zero byte in
2916 this case too, but that is not ordinarily useful, since the
2917 value is likely to contain embedded nulls.)
2921 An empty string is returned if the field value is null. See
2922 <function>PQgetisnull</> to distinguish null values from
2923 empty-string values.
2927 The pointer returned by <function>PQgetvalue</function> points
2928 to storage that is part of the <structname>PGresult</structname>
2929 structure. One should not modify the data it points to, and one
2930 must explicitly copy the data into other storage if it is to be
2931 used past the lifetime of the <structname>PGresult</structname>
2937 <varlistentry id="libpq-pqgetisnull">
2939 <function>PQgetisnull</function>
2941 <primary>PQgetisnull</primary>
2944 <primary>null value</primary>
2945 <secondary sortas="libpq">in libpq</secondary>
2951 Tests a field for a null value. Row and column numbers start
2954 int PQgetisnull(const PGresult *res,
2961 This function returns 1 if the field is null and 0 if it
2962 contains a non-null value. (Note that
2963 <function>PQgetvalue</function> will return an empty string,
2964 not a null pointer, for a null field.)
2969 <varlistentry id="libpq-pqgetlength">
2971 <function>PQgetlength</function>
2973 <primary>PQgetlength</primary>
2978 Returns the actual length of a field value in bytes. Row and
2979 column numbers start at 0.
2981 int PQgetlength(const PGresult *res,
2988 This is the actual data length for the particular data value,
2989 that is, the size of the object pointed to by
2990 <function>PQgetvalue</function>. For text data format this is
2991 the same as <function>strlen()</>. For binary format this is
2992 essential information. Note that one should <emphasis>not</>
2993 rely on <function>PQfsize</function> to obtain the actual data
2999 <varlistentry id="libpq-pqnparams">
3001 <function>PQnparams</function>
3003 <primary>PQnparams</primary>
3009 Returns the number of parameters of a prepared statement.
3011 int PQnparams(const PGresult *res);
3016 This function is only useful when inspecting the result of
3017 <function>PQdescribePrepared</>. For other types of queries it
3023 <varlistentry id="libpq-pqparamtype">
3025 <function>PQparamtype</function>
3027 <primary>PQparamtype</primary>
3033 Returns the data type of the indicated statement parameter.
3034 Parameter numbers start at 0.
3036 Oid PQparamtype(const PGresult *res, int param_number);
3041 This function is only useful when inspecting the result of
3042 <function>PQdescribePrepared</>. For other types of queries it
3048 <varlistentry id="libpq-pqprint">
3050 <function>PQprint</function>
3052 <primary>PQprint</primary>
3058 Prints out all the rows and, optionally, the column names to
3059 the specified output stream.
3061 void PQprint(FILE *fout, /* output stream */
3062 const PGresult *res,
3063 const PQprintOpt *po);
3066 pqbool header; /* print output field headings and row count */
3067 pqbool align; /* fill align the fields */
3068 pqbool standard; /* old brain dead format */
3069 pqbool html3; /* output HTML tables */
3070 pqbool expanded; /* expand tables */
3071 pqbool pager; /* use pager for output if needed */
3072 char *fieldSep; /* field separator */
3073 char *tableOpt; /* attributes for HTML table element */
3074 char *caption; /* HTML table caption */
3075 char **fieldName; /* null-terminated array of replacement field names */
3081 This function was formerly used by <application>psql</application>
3082 to print query results, but this is no longer the case. Note
3083 that it assumes all the data is in text format.
3090 <sect2 id="libpq-exec-nonselect">
3091 <title>Retrieving Other Result Information</title>
3094 These functions are used to extract other information from
3095 <structname>PGresult</structname> objects.
3099 <varlistentry id="libpq-pqcmdstatus">
3101 <function>PQcmdStatus</function>
3103 <primary>PQcmdStatus</primary>
3109 Returns the command status tag from the SQL command that generated
3110 the <structname>PGresult</structname>.
3112 char *PQcmdStatus(PGresult *res);
3117 Commonly this is just the name of the command, but it might include
3118 additional data such as the number of rows processed. The caller
3119 should not free the result directly. It will be freed when the
3120 associated <structname>PGresult</> handle is passed to
3121 <function>PQclear</function>.
3126 <varlistentry id="libpq-pqcmdtuples">
3128 <function>PQcmdTuples</function>
3130 <primary>PQcmdTuples</primary>
3136 Returns the number of rows affected by the SQL command.
3138 char *PQcmdTuples(PGresult *res);
3143 This function returns a string containing the number of rows
3144 affected by the <acronym>SQL</> statement that generated the
3145 <structname>PGresult</>. This function can only be used following
3146 the execution of a <command>SELECT</>, <command>CREATE TABLE AS</>,
3147 <command>INSERT</>, <command>UPDATE</>, <command>DELETE</>,
3148 <command>MOVE</>, <command>FETCH</>, or <command>COPY</> statement,
3149 or an <command>EXECUTE</> of a prepared query that contains an
3150 <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</> statement.
3151 If the command that generated the <structname>PGresult</> was anything
3152 else, <function>PQcmdTuples</> returns an empty string. The caller
3153 should not free the return value directly. It will be freed when
3154 the associated <structname>PGresult</> handle is passed to
3155 <function>PQclear</function>.
3160 <varlistentry id="libpq-pqoidvalue">
3162 <function>PQoidValue</function>
3164 <primary>PQoidValue</primary>
3170 Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
3171 of the inserted row, if the <acronym>SQL</> command was an
3172 <command>INSERT</> that inserted exactly one row into a table that
3173 has OIDs, or a <command>EXECUTE</> of a prepared query containing
3174 a suitable <command>INSERT</> statement. Otherwise, this function
3175 returns <literal>InvalidOid</literal>. This function will also
3176 return <literal>InvalidOid</literal> if the table affected by the
3177 <command>INSERT</> statement does not contain OIDs.
3179 Oid PQoidValue(const PGresult *res);
3185 <varlistentry id="libpq-pqoidstatus">
3187 <function>PQoidStatus</function>
3189 <primary>PQoidStatus</primary>
3195 This function is deprecated in favor of
3196 <function>PQoidValue</function> and is not thread-safe.
3197 It returns a string with the OID of the inserted row, while
3198 <function>PQoidValue</function> returns the OID value.
3200 char *PQoidStatus(const PGresult *res);
3210 <sect2 id="libpq-exec-escape-string">
3211 <title>Escaping Strings for Inclusion in SQL Commands</title>
3213 <indexterm zone="libpq-exec-escape-string">
3214 <primary>escaping strings</primary>
3215 <secondary>in libpq</secondary>
3219 <varlistentry id="libpq-pqescapeliteral">
3221 <function>PQescapeLiteral</function>
3223 <primary>PQescapeLiteral</primary>
3230 char *PQescapeLiteral(PGconn *conn, const char *str, size_t length);
3235 <function>PQescapeLiteral</function> escapes a string for
3236 use within an SQL command. This is useful when inserting data
3237 values as literal constants in SQL commands. Certain characters
3238 (such as quotes and backslashes) must be escaped to prevent them
3239 from being interpreted specially by the SQL parser.
3240 <function>PQescapeLiteral</> performs this operation.
3244 <function>PQescapeLiteral</> returns an escaped version of the
3245 <parameter>str</parameter> parameter in memory allocated with
3246 <function>malloc()</>. This memory should be freed using
3247 <function>PQfreemem()</> when the result is no longer needed.
3248 A terminating zero byte is not required, and should not be
3249 counted in <parameter>length</>. (If a terminating zero byte is found
3250 before <parameter>length</> bytes are processed,
3251 <function>PQescapeLiteral</> stops at the zero; the behavior is
3252 thus rather like <function>strncpy</>.) The
3253 return string has all special characters replaced so that they can
3254 be properly processed by the <productname>PostgreSQL</productname>
3255 string literal parser. A terminating zero byte is also added. The
3256 single quotes that must surround <productname>PostgreSQL</productname>
3257 string literals are included in the result string.
3261 On error, <function>PQescapeLiteral</> returns <symbol>NULL</> and a suitable
3262 message is stored in the <parameter>conn</> object.
3267 It is especially important to do proper escaping when handling
3268 strings that were received from an untrustworthy source.
3269 Otherwise there is a security risk: you are vulnerable to
3270 <quote>SQL injection</> attacks wherein unwanted SQL commands are
3271 fed to your database.
3276 Note that it is not necessary nor correct to do escaping when a data
3277 value is passed as a separate parameter in <function>PQexecParams</> or
3278 its sibling routines.
3283 <varlistentry id="libpq-pqescapeidentifier">
3285 <function>PQescapeIdentifier</function>
3287 <primary>PQescapeIdentifier</primary>
3294 char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length);
3299 <function>PQescapeIdentifier</function> escapes a string for
3300 use as an SQL identifier, such as a table, column, or function name.
3301 This is useful when a user-supplied identifier might contain
3302 special characters that would otherwise not be interpreted as part
3303 of the identifier by the SQL parser, or when the identifier might
3304 contain upper case characters whose case should be preserved.
3308 <function>PQescapeIdentifier</> returns a version of the
3309 <parameter>str</parameter> parameter escaped as an SQL identifier
3310 in memory allocated with <function>malloc()</>. This memory must be
3311 freed using <function>PQfreemem()</> when the result is no longer
3312 needed. A terminating zero byte is not required, and should not be
3313 counted in <parameter>length</>. (If a terminating zero byte is found
3314 before <parameter>length</> bytes are processed,
3315 <function>PQescapeIdentifier</> stops at the zero; the behavior is
3316 thus rather like <function>strncpy</>.) The
3317 return string has all special characters replaced so that it
3318 will be properly processed as an SQL identifier. A terminating zero byte
3319 is also added. The return string will also be surrounded by double
3324 On error, <function>PQescapeIdentifier</> returns <symbol>NULL</> and a suitable
3325 message is stored in the <parameter>conn</> object.
3330 As with string literals, to prevent SQL injection attacks,
3331 SQL identifiers must be escaped when they are received from an
3332 untrustworthy source.
3338 <varlistentry id="libpq-pqescapestringconn">
3340 <function>PQescapeStringConn</function>
3342 <primary>PQescapeStringConn</primary>
3349 size_t PQescapeStringConn(PGconn *conn,
3350 char *to, const char *from, size_t length,
3356 <function>PQescapeStringConn</> escapes string literals, much like
3357 <function>PQescapeLiteral</>. Unlike <function>PQescapeLiteral</>,
3358 the caller is responsible for providing an appropriately sized buffer.
3359 Furthermore, <function>PQescapeStringConn</> does not generate the
3360 single quotes that must surround <productname>PostgreSQL</> string
3361 literals; they should be provided in the SQL command that the
3362 result is inserted into. The parameter <parameter>from</> points to
3363 the first character of the string that is to be escaped, and the
3364 <parameter>length</> parameter gives the number of bytes in this
3365 string. A terminating zero byte is not required, and should not be
3366 counted in <parameter>length</>. (If a terminating zero byte is found
3367 before <parameter>length</> bytes are processed,
3368 <function>PQescapeStringConn</> stops at the zero; the behavior is
3369 thus rather like <function>strncpy</>.) <parameter>to</> shall point
3370 to a buffer that is able to hold at least one more byte than twice
3371 the value of <parameter>length</>, otherwise the behavior is undefined.
3372 Behavior is likewise undefined if the <parameter>to</> and
3373 <parameter>from</> strings overlap.
3377 If the <parameter>error</> parameter is not <symbol>NULL</>, then
3378 <literal>*error</> is set to zero on success, nonzero on error.
3379 Presently the only possible error conditions involve invalid multibyte
3380 encoding in the source string. The output string is still generated
3381 on error, but it can be expected that the server will reject it as
3382 malformed. On error, a suitable message is stored in the
3383 <parameter>conn</> object, whether or not <parameter>error</> is <symbol>NULL</>.
3387 <function>PQescapeStringConn</> returns the number of bytes written
3388 to <parameter>to</>, not including the terminating zero byte.
3393 <varlistentry id="libpq-pqescapestring">
3395 <function>PQescapeString</function>
3397 <primary>PQescapeString</primary>
3403 <function>PQescapeString</> is an older, deprecated version of
3404 <function>PQescapeStringConn</>.
3406 size_t PQescapeString (char *to, const char *from, size_t length);
3411 The only difference from <function>PQescapeStringConn</> is that
3412 <function>PQescapeString</> does not take <structname>PGconn</>
3413 or <parameter>error</> parameters.
3414 Because of this, it cannot adjust its behavior depending on the
3415 connection properties (such as character encoding) and therefore
3416 <emphasis>it might give the wrong results</>. Also, it has no way
3417 to report error conditions.
3421 <function>PQescapeString</> can be used safely in
3422 client programs that work with only one <productname>PostgreSQL</>
3423 connection at a time (in this case it can find out what it needs to
3424 know <quote>behind the scenes</>). In other contexts it is a security
3425 hazard and should be avoided in favor of
3426 <function>PQescapeStringConn</>.
3431 <varlistentry id="libpq-pqescapebyteaconn">
3433 <function>PQescapeByteaConn</function>
3435 <primary>PQescapeByteaConn</primary>
3441 Escapes binary data for use within an SQL command with the type
3442 <type>bytea</type>. As with <function>PQescapeStringConn</function>,
3443 this is only used when inserting data directly into an SQL command string.
3445 unsigned char *PQescapeByteaConn(PGconn *conn,
3446 const unsigned char *from,
3453 Certain byte values must be escaped when used as part of a
3454 <type>bytea</type> literal in an <acronym>SQL</acronym> statement.
3455 <function>PQescapeByteaConn</function> escapes bytes using
3456 either hex encoding or backslash escaping. See <xref
3457 linkend="datatype-binary"> for more information.
3461 The <parameter>from</parameter> parameter points to the first
3462 byte of the string that is to be escaped, and the
3463 <parameter>from_length</parameter> parameter gives the number of
3464 bytes in this binary string. (A terminating zero byte is
3465 neither necessary nor counted.) The <parameter>to_length</parameter>
3466 parameter points to a variable that will hold the resultant
3467 escaped string length. This result string length includes the terminating
3468 zero byte of the result.
3472 <function>PQescapeByteaConn</> returns an escaped version of the
3473 <parameter>from</parameter> parameter binary string in memory
3474 allocated with <function>malloc()</>. This memory should be freed using
3475 <function>PQfreemem()</> when the result is no longer needed. The
3476 return string has all special characters replaced so that they can
3477 be properly processed by the <productname>PostgreSQL</productname>
3478 string literal parser, and the <type>bytea</type> input function. A
3479 terminating zero byte is also added. The single quotes that must
3480 surround <productname>PostgreSQL</productname> string literals are
3481 not part of the result string.
3485 On error, a null pointer is returned, and a suitable error message
3486 is stored in the <parameter>conn</> object. Currently, the only
3487 possible error is insufficient memory for the result string.
3492 <varlistentry id="libpq-pqescapebytea">
3494 <function>PQescapeBytea</function>
3496 <primary>PQescapeBytea</primary>
3502 <function>PQescapeBytea</> is an older, deprecated version of
3503 <function>PQescapeByteaConn</>.
3505 unsigned char *PQescapeBytea(const unsigned char *from,
3512 The only difference from <function>PQescapeByteaConn</> is that
3513 <function>PQescapeBytea</> does not take a <structname>PGconn</>
3514 parameter. Because of this, <function>PQescapeBytea</> can
3515 only be used safely in client programs that use a single
3516 <productname>PostgreSQL</> connection at a time (in this case
3517 it can find out what it needs to know <quote>behind the
3518 scenes</>). It <emphasis>might give the wrong results</> if
3519 used in programs that use multiple database connections (use
3520 <function>PQescapeByteaConn</> in such cases).
3525 <varlistentry id="libpq-pqunescapebytea">
3527 <function>PQunescapeBytea</function>
3529 <primary>PQunescapeBytea</primary>
3535 Converts a string representation of binary data into binary data
3536 — the reverse of <function>PQescapeBytea</function>. This
3537 is needed when retrieving <type>bytea</type> data in text format,
3538 but not when retrieving it in binary format.
3541 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
3546 The <parameter>from</parameter> parameter points to a string
3547 such as might be returned by <function>PQgetvalue</function> when applied
3548 to a <type>bytea</type> column. <function>PQunescapeBytea</function>
3549 converts this string representation into its binary representation.
3550 It returns a pointer to a buffer allocated with
3551 <function>malloc()</function>, or <symbol>NULL</> on error, and puts the size of
3552 the buffer in <parameter>to_length</parameter>. The result must be
3553 freed using <function>PQfreemem</> when it is no longer needed.
3557 This conversion is not exactly the inverse of
3558 <function>PQescapeBytea</function>, because the string is not expected
3559 to be <quote>escaped</> when received from <function>PQgetvalue</function>.
3560 In particular this means there is no need for string quoting considerations,
3561 and so no need for a <structname>PGconn</> parameter.
3571 <sect1 id="libpq-async">
3572 <title>Asynchronous Command Processing</title>
3574 <indexterm zone="libpq-async">
3575 <primary>nonblocking connection</primary>
3579 The <function>PQexec</function> function is adequate for submitting
3580 commands in normal, synchronous applications. It has a couple of
3581 deficiencies, however, that can be of importance to some users:
3586 <function>PQexec</function> waits for the command to be completed.
3587 The application might have other work to do (such as maintaining a
3588 user interface), in which case it won't want to block waiting for
3595 Since the execution of the client application is suspended while it
3596 waits for the result, it is hard for the application to decide that
3597 it would like to try to cancel the ongoing command. (It can be done
3598 from a signal handler, but not otherwise.)
3604 <function>PQexec</function> can return only one
3605 <structname>PGresult</structname> structure. If the submitted command
3606 string contains multiple <acronym>SQL</acronym> commands, all but
3607 the last <structname>PGresult</structname> are discarded by
3608 <function>PQexec</function>.
3615 Applications that do not like these limitations can instead use the
3616 underlying functions that <function>PQexec</function> is built from:
3617 <function>PQsendQuery</function> and <function>PQgetResult</function>.
3619 <function>PQsendQueryParams</function>,
3620 <function>PQsendPrepare</function>,
3621 <function>PQsendQueryPrepared</function>,
3622 <function>PQsendDescribePrepared</function>, and
3623 <function>PQsendDescribePortal</function>,
3624 which can be used with <function>PQgetResult</function> to duplicate
3625 the functionality of
3626 <function>PQexecParams</function>,
3627 <function>PQprepare</function>,
3628 <function>PQexecPrepared</function>,
3629 <function>PQdescribePrepared</function>, and
3630 <function>PQdescribePortal</function>
3634 <varlistentry id="libpq-pqsendquery">
3636 <function>PQsendQuery</function>
3638 <primary>PQsendQuery</primary>
3644 Submits a command to the server without waiting for the result(s).
3645 1 is returned if the command was successfully dispatched and 0 if
3646 not (in which case, use <function>PQerrorMessage</> to get more
3647 information about the failure).
3649 int PQsendQuery(PGconn *conn, const char *command);
3652 After successfully calling <function>PQsendQuery</function>, call
3653 <function>PQgetResult</function> one or more times to obtain the
3654 results. <function>PQsendQuery</function> cannot be called again
3655 (on the same connection) until <function>PQgetResult</function>
3656 has returned a null pointer, indicating that the command is done.
3661 <varlistentry id="libpq-pqsendqueryparams">
3663 <function>PQsendQueryParams</function>
3665 <primary>PQsendQueryParams</primary>
3671 Submits a command and separate parameters to the server without
3672 waiting for the result(s).
3674 int PQsendQueryParams(PGconn *conn,
3675 const char *command,
3677 const Oid *paramTypes,
3678 const char * const *paramValues,
3679 const int *paramLengths,
3680 const int *paramFormats,
3684 This is equivalent to <function>PQsendQuery</function> except that
3685 query parameters can be specified separately from the query string.
3686 The function's parameters are handled identically to
3687 <function>PQexecParams</function>. Like
3688 <function>PQexecParams</function>, it will not work on 2.0-protocol
3689 connections, and it allows only one command in the query string.
3694 <varlistentry id="libpq-pqsendprepare">
3696 <function>PQsendPrepare</>
3698 <primary>PQsendPrepare</primary>
3704 Sends a request to create a prepared statement with the given
3705 parameters, without waiting for completion.
3707 int PQsendPrepare(PGconn *conn,
3708 const char *stmtName,
3711 const Oid *paramTypes);
3714 This is an asynchronous version of <function>PQprepare</>: it
3715 returns 1 if it was able to dispatch the request, and 0 if not.
3716 After a successful call, call <function>PQgetResult</function> to
3717 determine whether the server successfully created the prepared
3718 statement. The function's parameters are handled identically to
3719 <function>PQprepare</function>. Like
3720 <function>PQprepare</function>, it will not work on 2.0-protocol
3726 <varlistentry id="libpq-pqsendqueryprepared">
3728 <function>PQsendQueryPrepared</function>
3730 <primary>PQsendQueryPrepared</primary>
3736 Sends a request to execute a prepared statement with given
3737 parameters, without waiting for the result(s).
3739 int PQsendQueryPrepared(PGconn *conn,
3740 const char *stmtName,
3742 const char * const *paramValues,
3743 const int *paramLengths,
3744 const int *paramFormats,
3748 This is similar to <function>PQsendQueryParams</function>, but
3749 the command to be executed is specified by naming a
3750 previously-prepared statement, instead of giving a query string.
3751 The function's parameters are handled identically to
3752 <function>PQexecPrepared</function>. Like
3753 <function>PQexecPrepared</function>, it will not work on
3754 2.0-protocol connections.
3759 <varlistentry id="libpq-pqsenddescribeprepared">
3761 <function>PQsendDescribePrepared</>
3763 <primary>PQsendDescribePrepared</primary>
3769 Submits a request to obtain information about the specified
3770 prepared statement, without waiting for completion.
3772 int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
3775 This is an asynchronous version of <function>PQdescribePrepared</>:
3776 it returns 1 if it was able to dispatch the request, and 0 if not.
3777 After a successful call, call <function>PQgetResult</function> to
3778 obtain the results. The function's parameters are handled
3779 identically to <function>PQdescribePrepared</function>. Like
3780 <function>PQdescribePrepared</function>, it will not work on
3781 2.0-protocol connections.
3786 <varlistentry id="libpq-pqsenddescribeportal">
3788 <function>PQsendDescribePortal</>
3790 <primary>PQsendDescribePortal</primary>
3796 Submits a request to obtain information about the specified
3797 portal, without waiting for completion.
3799 int PQsendDescribePortal(PGconn *conn, const char *portalName);
3802 This is an asynchronous version of <function>PQdescribePortal</>:
3803 it returns 1 if it was able to dispatch the request, and 0 if not.
3804 After a successful call, call <function>PQgetResult</function> to
3805 obtain the results. The function's parameters are handled
3806 identically to <function>PQdescribePortal</function>. Like
3807 <function>PQdescribePortal</function>, it will not work on
3808 2.0-protocol connections.
3813 <varlistentry id="libpq-pqgetresult">
3815 <function>PQgetResult</function>
3817 <primary>PQgetResult</primary>
3823 Waits for the next result from a prior
3824 <function>PQsendQuery</function>,
3825 <function>PQsendQueryParams</function>,
3826 <function>PQsendPrepare</function>, or
3827 <function>PQsendQueryPrepared</function> call, and returns it.
3828 A null pointer is returned when the command is complete and there
3829 will be no more results.
3831 PGresult *PQgetResult(PGconn *conn);
3836 <function>PQgetResult</function> must be called repeatedly until
3837 it returns a null pointer, indicating that the command is done.
3838 (If called when no command is active,
3839 <function>PQgetResult</function> will just return a null pointer
3840 at once.) Each non-null result from
3841 <function>PQgetResult</function> should be processed using the
3842 same <structname>PGresult</> accessor functions previously
3843 described. Don't forget to free each result object with
3844 <function>PQclear</function> when done with it. Note that
3845 <function>PQgetResult</function> will block only if a command is
3846 active and the necessary response data has not yet been read by
3847 <function>PQconsumeInput</function>.
3852 Even when <function>PQresultStatus</function> indicates a fatal
3853 error, <function>PQgetResult</function> should be called until it
3854 returns a null pointer to allow <application>libpq</> to
3855 process the error information completely.
3864 Using <function>PQsendQuery</function> and
3865 <function>PQgetResult</function> solves one of
3866 <function>PQexec</function>'s problems: If a command string contains
3867 multiple <acronym>SQL</acronym> commands, the results of those commands
3868 can be obtained individually. (This allows a simple form of overlapped
3869 processing, by the way: the client can be handling the results of one
3870 command while the server is still working on later queries in the same
3871 command string.) However, calling <function>PQgetResult</function>
3872 will still cause the client to block until the server completes the
3873 next <acronym>SQL</acronym> command. This can be avoided by proper
3874 use of two more functions:
3877 <varlistentry id="libpq-pqconsumeinput">
3879 <function>PQconsumeInput</function>
3881 <primary>PQconsumeInput</primary>
3887 If input is available from the server, consume it.
3889 int PQconsumeInput(PGconn *conn);
3894 <function>PQconsumeInput</function> normally returns 1 indicating
3895 <quote>no error</quote>, but returns 0 if there was some kind of
3896 trouble (in which case <function>PQerrorMessage</function> can be
3897 consulted). Note that the result does not say whether any input
3898 data was actually collected. After calling
3899 <function>PQconsumeInput</function>, the application can check
3900 <function>PQisBusy</function> and/or
3901 <function>PQnotifies</function> to see if their state has changed.
3905 <function>PQconsumeInput</function> can be called even if the
3906 application is not prepared to deal with a result or notification
3907 just yet. The function will read available data and save it in
3908 a buffer, thereby causing a <function>select()</function>
3909 read-ready indication to go away. The application can thus use
3910 <function>PQconsumeInput</function> to clear the
3911 <function>select()</function> condition immediately, and then
3912 examine the results at leisure.
3917 <varlistentry id="libpq-pqisbusy">
3919 <function>PQisBusy</function>
3921 <primary>PQisBusy</primary>
3927 Returns 1 if a command is busy, that is,
3928 <function>PQgetResult</function> would block waiting for input.
3929 A 0 return indicates that <function>PQgetResult</function> can be
3930 called with assurance of not blocking.
3932 int PQisBusy(PGconn *conn);
3937 <function>PQisBusy</function> will not itself attempt to read data
3938 from the server; therefore <function>PQconsumeInput</function>
3939 must be invoked first, or the busy state will never end.
3947 A typical application using these functions will have a main loop that
3948 uses <function>select()</function> or <function>poll()</> to wait for
3949 all the conditions that it must respond to. One of the conditions
3950 will be input available from the server, which in terms of
3951 <function>select()</function> means readable data on the file
3952 descriptor identified by <function>PQsocket</function>. When the main
3953 loop detects input ready, it should call
3954 <function>PQconsumeInput</function> to read the input. It can then
3955 call <function>PQisBusy</function>, followed by
3956 <function>PQgetResult</function> if <function>PQisBusy</function>
3957 returns false (0). It can also call <function>PQnotifies</function>
3958 to detect <command>NOTIFY</> messages (see <xref
3959 linkend="libpq-notify">).
3964 <function>PQsendQuery</function>/<function>PQgetResult</function>
3965 can also attempt to cancel a command that is still being processed
3966 by the server; see <xref linkend="libpq-cancel">. But regardless of
3967 the return value of <function>PQcancel</function>, the application
3968 must continue with the normal result-reading sequence using
3969 <function>PQgetResult</function>. A successful cancellation will
3970 simply cause the command to terminate sooner than it would have
3975 By using the functions described above, it is possible to avoid
3976 blocking while waiting for input from the database server. However,
3977 it is still possible that the application will block waiting to send
3978 output to the server. This is relatively uncommon but can happen if
3979 very long SQL commands or data values are sent. (It is much more
3980 probable if the application sends data via <command>COPY IN</command>,
3981 however.) To prevent this possibility and achieve completely
3982 nonblocking database operation, the following additional functions
3986 <varlistentry id="libpq-pqsetnonblocking">
3988 <function>PQsetnonblocking</function>
3990 <primary>PQsetnonblocking</primary>
3996 Sets the nonblocking status of the connection.
3998 int PQsetnonblocking(PGconn *conn, int arg);
4003 Sets the state of the connection to nonblocking if
4004 <parameter>arg</parameter> is 1, or blocking if
4005 <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
4009 In the nonblocking state, calls to
4010 <function>PQsendQuery</function>, <function>PQputline</function>,
4011 <function>PQputnbytes</function>, and
4012 <function>PQendcopy</function> will not block but instead return
4013 an error if they need to be called again.
4017 Note that <function>PQexec</function> does not honor nonblocking
4018 mode; if it is called, it will act in blocking fashion anyway.
4023 <varlistentry id="libpq-pqisnonblocking">
4025 <function>PQisnonblocking</function>
4027 <primary>PQisnonblocking</primary>
4033 Returns the blocking status of the database connection.
4035 int PQisnonblocking(const PGconn *conn);
4040 Returns 1 if the connection is set to nonblocking mode and 0 if
4046 <varlistentry id="libpq-pqflush">
4048 <function>PQflush</function>
4050 <primary>PQflush</primary>
4056 Attempts to flush any queued output data to the server. Returns
4057 0 if successful (or if the send queue is empty), -1 if it failed
4058 for some reason, or 1 if it was unable to send all the data in
4059 the send queue yet (this case can only occur if the connection
4062 int PQflush(PGconn *conn);
4071 After sending any command or data on a nonblocking connection, call
4072 <function>PQflush</function>. If it returns 1, wait for the socket
4073 to be write-ready and call it again; repeat until it returns 0. Once
4074 <function>PQflush</function> returns 0, wait for the socket to be
4075 read-ready and then read the response as described above.
4080 <sect1 id="libpq-cancel">
4081 <title>Cancelling Queries in Progress</title>
4083 <indexterm zone="libpq-cancel">
4084 <primary>canceling</primary>
4085 <secondary>SQL command</secondary>
4089 A client application can request cancellation of a command that is
4090 still being processed by the server, using the functions described in
4094 <varlistentry id="libpq-pqgetcancel">
4096 <function>PQgetCancel</function>
4098 <primary>PQgetCancel</primary>
4104 Creates a data structure containing the information needed to cancel
4105 a command issued through a particular database connection.
4107 PGcancel *PQgetCancel(PGconn *conn);
4112 <function>PQgetCancel</function> creates a
4113 <structname>PGcancel</><indexterm><primary>PGcancel</></> object
4114 given a <structname>PGconn</> connection object. It will return
4115 <symbol>NULL</> if the given <parameter>conn</> is <symbol>NULL</> or an invalid
4116 connection. The <structname>PGcancel</> object is an opaque
4117 structure that is not meant to be accessed directly by the
4118 application; it can only be passed to <function>PQcancel</function>
4119 or <function>PQfreeCancel</function>.
4124 <varlistentry id="libpq-pqfreecancel">
4126 <function>PQfreeCancel</function>
4128 <primary>PQfreeCancel</primary>
4134 Frees a data structure created by <function>PQgetCancel</function>.
4136 void PQfreeCancel(PGcancel *cancel);
4141 <function>PQfreeCancel</function> frees a data object previously created
4142 by <function>PQgetCancel</function>.
4147 <varlistentry id="libpq-pqcancel">
4149 <function>PQcancel</function>
4151 <primary>PQcancel</primary>
4157 Requests that the server abandon processing of the current command.
4159 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
4164 The return value is 1 if the cancel request was successfully
4165 dispatched and 0 if not. If not, <parameter>errbuf</> is filled
4166 with an explanatory error message. <parameter>errbuf</>
4167 must be a char array of size <parameter>errbufsize</> (the
4168 recommended size is 256 bytes).
4172 Successful dispatch is no guarantee that the request will have
4173 any effect, however. If the cancellation is effective, the current
4174 command will terminate early and return an error result. If the
4175 cancellation fails (say, because the server was already done
4176 processing the command), then there will be no visible result at
4181 <function>PQcancel</function> can safely be invoked from a signal
4182 handler, if the <parameter>errbuf</> is a local variable in the
4183 signal handler. The <structname>PGcancel</> object is read-only
4184 as far as <function>PQcancel</function> is concerned, so it can
4185 also be invoked from a thread that is separate from the one
4186 manipulating the <structname>PGconn</> object.
4193 <varlistentry id="libpq-pqrequestcancel">
4195 <function>PQrequestCancel</function>
4197 <primary>PQrequestCancel</primary>
4203 <function>PQrequestCancel</function> is a deprecated variant of
4204 <function>PQcancel</function>.
4206 int PQrequestCancel(PGconn *conn);
4211 Requests that the server abandon processing of the current
4212 command. It operates directly on the
4213 <structname>PGconn</> object, and in case of failure stores the
4214 error message in the <structname>PGconn</> object (whence it can
4215 be retrieved by <function>PQerrorMessage</function>). Although
4216 the functionality is the same, this approach creates hazards for
4217 multiple-thread programs and signal handlers, since it is possible
4218 that overwriting the <structname>PGconn</>'s error message will
4219 mess up the operation currently in progress on the connection.
4228 <sect1 id="libpq-fastpath">
4229 <title>The Fast-Path Interface</title>
4231 <indexterm zone="libpq-fastpath">
4232 <primary>fast path</primary>
4236 <productname>PostgreSQL</productname> provides a fast-path interface
4237 to send simple function calls to the server.
4242 This interface is somewhat obsolete, as one can achieve similar
4243 performance and greater functionality by setting up a prepared
4244 statement to define the function call. Then, executing the statement
4245 with binary transmission of parameters and results substitutes for a
4246 fast-path function call.
4251 The function <function>PQfn</function><indexterm><primary>PQfn</></>
4252 requests execution of a server function via the fast-path interface:
4254 PGresult *PQfn(PGconn *conn,
4259 const PQArgBlock *args,
4276 The <parameter>fnid</> argument is the OID of the function to be
4277 executed. <parameter>args</> and <parameter>nargs</> define the
4278 parameters to be passed to the function; they must match the declared
4279 function argument list. When the <parameter>isint</> field of a
4280 parameter structure is true, the <parameter>u.integer</> value is sent
4281 to the server as an integer of the indicated length (this must be 1,
4282 2, or 4 bytes); proper byte-swapping occurs. When <parameter>isint</>
4283 is false, the indicated number of bytes at <parameter>*u.ptr</> are
4284 sent with no processing; the data must be in the format expected by
4285 the server for binary transmission of the function's argument data
4286 type. <parameter>result_buf</parameter> is the buffer in which to
4287 place the return value. The caller must have allocated sufficient
4288 space to store the return value. (There is no check!) The actual result
4289 length will be returned in the integer pointed to by
4290 <parameter>result_len</parameter>. If a 1, 2, or 4-byte integer result
4291 is expected, set <parameter>result_is_int</parameter> to 1, otherwise
4292 set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
4293 <application>libpq</> to byte-swap the value if necessary, so that it
4294 is delivered as a proper <type>int</type> value for the client machine.
4295 When <parameter>result_is_int</> is 0, the binary-format byte string
4296 sent by the server is returned unmodified.
4300 <function>PQfn</function> always returns a valid
4301 <structname>PGresult</structname> pointer. The result status should be
4302 checked before the result is used. The caller is responsible for
4303 freeing the <structname>PGresult</structname> with
4304 <function>PQclear</function> when it is no longer needed.
4308 Note that it is not possible to handle null arguments, null results,
4309 nor set-valued results when using this interface.
4314 <sect1 id="libpq-notify">
4315 <title>Asynchronous Notification</title>
4317 <indexterm zone="libpq-notify">
4318 <primary>NOTIFY</primary>
4319 <secondary>in libpq</secondary>
4323 <productname>PostgreSQL</productname> offers asynchronous notification
4324 via the <command>LISTEN</command> and <command>NOTIFY</command>
4325 commands. A client session registers its interest in a particular
4326 notification channel with the <command>LISTEN</command> command (and
4327 can stop listening with the <command>UNLISTEN</command> command). All
4328 sessions listening on a particular channel will be notified
4329 asynchronously when a <command>NOTIFY</command> command with that
4330 channel name is executed by any session. A <quote>payload</> string can
4331 be passed to communicate additional data to the listeners.
4335 <application>libpq</application> applications submit
4336 <command>LISTEN</command>, <command>UNLISTEN</command>,
4337 and <command>NOTIFY</command> commands as
4338 ordinary SQL commands. The arrival of <command>NOTIFY</command>
4339 messages can subsequently be detected by calling
4340 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
4344 The function <function>PQnotifies</function> returns the next notification
4345 from a list of unhandled notification messages received from the server.
4346 It returns a null pointer if there are no pending notifications. Once a
4347 notification is returned from <function>PQnotifies</>, it is considered
4348 handled and will be removed from the list of notifications.
4351 PGnotify *PQnotifies(PGconn *conn);
4353 typedef struct pgNotify
4355 char *relname; /* notification channel name */
4356 int be_pid; /* process ID of notifying server process */
4357 char *extra; /* notification payload string */
4361 After processing a <structname>PGnotify</structname> object returned
4362 by <function>PQnotifies</function>, be sure to free it with
4363 <function>PQfreemem</function>. It is sufficient to free the
4364 <structname>PGnotify</structname> pointer; the
4365 <structfield>relname</structfield> and <structfield>extra</structfield>
4366 fields do not represent separate allocations. (The names of these fields
4367 are historical; in particular, channel names need not have anything to
4368 do with relation names.)
4372 <xref linkend="libpq-example-2"> gives a sample program that illustrates
4373 the use of asynchronous notification.
4377 <function>PQnotifies</function> does not actually read data from the
4378 server; it just returns messages previously absorbed by another
4379 <application>libpq</application> function. In prior releases of
4380 <application>libpq</application>, the only way to ensure timely receipt
4381 of <command>NOTIFY</> messages was to constantly submit commands, even
4382 empty ones, and then check <function>PQnotifies</function> after each
4383 <function>PQexec</function>. While this still works, it is deprecated
4384 as a waste of processing power.
4388 A better way to check for <command>NOTIFY</> messages when you have no
4389 useful commands to execute is to call
4390 <function>PQconsumeInput</function>, then check
4391 <function>PQnotifies</function>. You can use
4392 <function>select()</function> to wait for data to arrive from the
4393 server, thereby using no <acronym>CPU</acronym> power unless there is
4394 something to do. (See <function>PQsocket</function> to obtain the file
4395 descriptor number to use with <function>select()</function>.) Note that
4396 this will work OK whether you submit commands with
4397 <function>PQsendQuery</function>/<function>PQgetResult</function> or
4398 simply use <function>PQexec</function>. You should, however, remember
4399 to check <function>PQnotifies</function> after each
4400 <function>PQgetResult</function> or <function>PQexec</function>, to
4401 see if any notifications came in during the processing of the command.
4406 <sect1 id="libpq-copy">
4407 <title>Functions Associated with the <command>COPY</command> Command</title>
4409 <indexterm zone="libpq-copy">
4410 <primary>COPY</primary>
4411 <secondary>with libpq</secondary>
4415 The <command>COPY</command> command in
4416 <productname>PostgreSQL</productname> has options to read from or write
4417 to the network connection used by <application>libpq</application>.
4418 The functions described in this section allow applications to take
4419 advantage of this capability by supplying or consuming copied data.
4423 The overall process is that the application first issues the SQL
4424 <command>COPY</command> command via <function>PQexec</function> or one
4425 of the equivalent functions. The response to this (if there is no
4426 error in the command) will be a <structname>PGresult</> object bearing
4427 a status code of <literal>PGRES_COPY_OUT</literal> or
4428 <literal>PGRES_COPY_IN</literal> (depending on the specified copy
4429 direction). The application should then use the functions of this
4430 section to receive or transmit data rows. When the data transfer is
4431 complete, another <structname>PGresult</> object is returned to indicate
4432 success or failure of the transfer. Its status will be
4433 <literal>PGRES_COMMAND_OK</literal> for success or
4434 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
4435 At this point further SQL commands can be issued via
4436 <function>PQexec</function>. (It is not possible to execute other SQL
4437 commands using the same connection while the <command>COPY</command>
4438 operation is in progress.)
4442 If a <command>COPY</command> command is issued via
4443 <function>PQexec</function> in a string that could contain additional
4444 commands, the application must continue fetching results via
4445 <function>PQgetResult</> after completing the <command>COPY</command>
4446 sequence. Only when <function>PQgetResult</> returns
4447 <symbol>NULL</symbol> is it certain that the <function>PQexec</function>
4448 command string is done and it is safe to issue more commands.
4452 The functions of this section should be executed only after obtaining
4453 a result status of <literal>PGRES_COPY_OUT</literal> or
4454 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
4455 <function>PQgetResult</function>.
4459 A <structname>PGresult</> object bearing one of these status values
4460 carries some additional data about the <command>COPY</command> operation
4461 that is starting. This additional data is available using functions
4462 that are also used in connection with query results:
4465 <varlistentry id="libpq-pqnfields-1">
4467 <function>PQnfields</function>
4469 <primary>PQnfields</primary>
4470 <secondary>with COPY</secondary>
4476 Returns the number of columns (fields) to be copied.
4481 <varlistentry id="libpq-pqbinarytuples-1">
4483 <function>PQbinaryTuples</function>
4485 <primary>PQbinaryTuples</primary>
4486 <secondary>with COPY</secondary>
4492 0 indicates the overall copy format is textual (rows separated by
4493 newlines, columns separated by separator characters, etc). 1
4494 indicates the overall copy format is binary. See <xref
4495 linkend="sql-copy"> for more information.
4500 <varlistentry id="libpq-pqfformat-1">
4502 <function>PQfformat</function>
4504 <primary>PQfformat</primary>
4505 <secondary>with COPY</secondary>
4511 Returns the format code (0 for text, 1 for binary) associated with
4512 each column of the copy operation. The per-column format codes
4513 will always be zero when the overall copy format is textual, but
4514 the binary format can support both text and binary columns.
4515 (However, as of the current implementation of <command>COPY</>,
4516 only binary columns appear in a binary copy; so the per-column
4517 formats always match the overall format at present.)
4526 These additional data values are only available when using protocol
4527 3.0. When using protocol 2.0, all these functions will return 0.
4531 <sect2 id="libpq-copy-send">
4532 <title>Functions for Sending <command>COPY</command> Data</title>
4535 These functions are used to send data during <literal>COPY FROM
4536 STDIN</>. They will fail if called when the connection is not in
4537 <literal>COPY_IN</> state.
4541 <varlistentry id="libpq-pqputcopydata">
4543 <function>PQputCopyData</function>
4545 <primary>PQputCopyData</primary>
4551 Sends data to the server during <literal>COPY_IN</> state.
4553 int PQputCopyData(PGconn *conn,
4560 Transmits the <command>COPY</command> data in the specified
4561 <parameter>buffer</>, of length <parameter>nbytes</>, to the server.
4562 The result is 1 if the data was sent, zero if it was not sent
4563 because the attempt would block (this case is only possible if the
4564 connection is in nonblocking mode), or -1 if an error occurred.
4565 (Use <function>PQerrorMessage</function> to retrieve details if
4566 the return value is -1. If the value is zero, wait for write-ready
4571 The application can divide the <command>COPY</command> data stream
4572 into buffer loads of any convenient size. Buffer-load boundaries
4573 have no semantic significance when sending. The contents of the
4574 data stream must match the data format expected by the
4575 <command>COPY</> command; see <xref linkend="sql-copy"> for details.
4580 <varlistentry id="libpq-pqputcopyend">
4582 <function>PQputCopyEnd</function>
4584 <primary>PQputCopyEnd</primary>
4590 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
4592 int PQputCopyEnd(PGconn *conn,
4593 const char *errormsg);
4598 Ends the <literal>COPY_IN</> operation successfully if
4599 <parameter>errormsg</> is <symbol>NULL</symbol>. If
4600 <parameter>errormsg</> is not <symbol>NULL</symbol> then the
4601 <command>COPY</> is forced to fail, with the string pointed to by
4602 <parameter>errormsg</> used as the error message. (One should not
4603 assume that this exact error message will come back from the server,
4604 however, as the server might have already failed the
4605 <command>COPY</> for its own reasons. Also note that the option
4606 to force failure does not work when using pre-3.0-protocol
4611 The result is 1 if the termination data was sent, zero if it was
4612 not sent because the attempt would block (this case is only possible
4613 if the connection is in nonblocking mode), or -1 if an error
4614 occurred. (Use <function>PQerrorMessage</function> to retrieve
4615 details if the return value is -1. If the value is zero, wait for
4616 write-ready and try again.)
4620 After successfully calling <function>PQputCopyEnd</>, call
4621 <function>PQgetResult</> to obtain the final result status of the
4622 <command>COPY</> command. One can wait for this result to be
4623 available in the usual way. Then return to normal operation.
4631 <sect2 id="libpq-copy-receive">
4632 <title>Functions for Receiving <command>COPY</command> Data</title>
4635 These functions are used to receive data during <literal>COPY TO
4636 STDOUT</>. They will fail if called when the connection is not in
4637 <literal>COPY_OUT</> state.
4641 <varlistentry id="libpq-pqgetcopydata">
4643 <function>PQgetCopyData</function>
4645 <primary>PQgetCopyData</primary>
4651 Receives data from the server during <literal>COPY_OUT</> state.
4653 int PQgetCopyData(PGconn *conn,
4660 Attempts to obtain another row of data from the server during a
4661 <command>COPY</command>. Data is always returned one data row at
4662 a time; if only a partial row is available, it is not returned.
4663 Successful return of a data row involves allocating a chunk of
4664 memory to hold the data. The <parameter>buffer</> parameter must
4665 be non-<symbol>NULL</symbol>. <parameter>*buffer</> is set to
4666 point to the allocated memory, or to <symbol>NULL</symbol> in cases
4667 where no buffer is returned. A non-<symbol>NULL</symbol> result
4668 buffer should be freed using <function>PQfreemem</> when no longer
4673 When a row is successfully returned, the return value is the number
4674 of data bytes in the row (this will always be greater than zero).
4675 The returned string is always null-terminated, though this is
4676 probably only useful for textual <command>COPY</command>. A result
4677 of zero indicates that the <command>COPY</command> is still in
4678 progress, but no row is yet available (this is only possible when
4679 <parameter>async</> is true). A result of -1 indicates that the
4680 <command>COPY</command> is done. A result of -2 indicates that an
4681 error occurred (consult <function>PQerrorMessage</> for the reason).
4685 When <parameter>async</> is true (not zero),
4686 <function>PQgetCopyData</> will not block waiting for input; it
4687 will return zero if the <command>COPY</command> is still in progress
4688 but no complete row is available. (In this case wait for read-ready
4689 and then call <function>PQconsumeInput</> before calling
4690 <function>PQgetCopyData</> again.) When <parameter>async</> is
4691 false (zero), <function>PQgetCopyData</> will block until data is
4692 available or the operation completes.
4696 After <function>PQgetCopyData</> returns -1, call
4697 <function>PQgetResult</> to obtain the final result status of the
4698 <command>COPY</> command. One can wait for this result to be
4699 available in the usual way. Then return to normal operation.
4707 <sect2 id="libpq-copy-deprecated">
4708 <title>Obsolete Functions for <command>COPY</command></title>
4711 These functions represent older methods of handling <command>COPY</>.
4712 Although they still work, they are deprecated due to poor error handling,
4713 inconvenient methods of detecting end-of-data, and lack of support for binary
4714 or nonblocking transfers.
4718 <varlistentry id="libpq-pqgetline">
4720 <function>PQgetline</function>
4722 <primary>PQgetline</primary>
4728 Reads a newline-terminated line of characters (transmitted
4729 by the server) into a buffer string of size <parameter>length</>.
4731 int PQgetline(PGconn *conn,
4738 This function copies up to <parameter>length</>-1 characters into
4739 the buffer and converts the terminating newline into a zero byte.
4740 <function>PQgetline</function> returns <symbol>EOF</symbol> at the
4741 end of input, 0 if the entire line has been read, and 1 if the
4742 buffer is full but the terminating newline has not yet been read.
4745 Note that the application must check to see if a new line consists
4746 of the two characters <literal>\.</literal>, which indicates
4747 that the server has finished sending the results of the
4748 <command>COPY</command> command. If the application might receive
4749 lines that are more than <parameter>length</>-1 characters long,
4750 care is needed to be sure it recognizes the <literal>\.</literal>
4751 line correctly (and does not, for example, mistake the end of a
4752 long data line for a terminator line).
4757 <varlistentry id="libpq-pqgetlineasync">
4759 <function>PQgetlineAsync</function>
4761 <primary>PQgetlineAsync</primary>
4767 Reads a row of <command>COPY</command> data (transmitted by the
4768 server) into a buffer without blocking.
4770 int PQgetlineAsync(PGconn *conn,
4777 This function is similar to <function>PQgetline</function>, but it can be used
4779 that must read <command>COPY</command> data asynchronously, that is, without blocking.
4780 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
4782 application should call <function>PQconsumeInput</function> and
4783 <function>PQgetlineAsync</function> until the
4784 end-of-data signal is detected.
4787 Unlike <function>PQgetline</function>, this function takes
4788 responsibility for detecting end-of-data.
4792 On each call, <function>PQgetlineAsync</function> will return data if a
4793 complete data row is available in <application>libpq</>'s input buffer.
4794 Otherwise, no data is returned until the rest of the row arrives.
4795 The function returns -1 if the end-of-copy-data marker has been recognized,
4796 or 0 if no data is available, or a positive number giving the number of
4797 bytes of data returned. If -1 is returned, the caller must next call
4798 <function>PQendcopy</function>, and then return to normal processing.
4802 The data returned will not extend beyond a data-row boundary. If possible
4803 a whole row will be returned at one time. But if the buffer offered by
4804 the caller is too small to hold a row sent by the server, then a partial
4805 data row will be returned. With textual data this can be detected by testing
4806 whether the last returned byte is <literal>\n</literal> or not. (In a binary
4807 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
4808 equivalent determination.)
4809 The returned string is not null-terminated. (If you want to add a
4810 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
4811 than the room actually available.)
4816 <varlistentry id="libpq-pqputline">
4818 <function>PQputline</function>
4820 <primary>PQputline</primary>
4826 Sends a null-terminated string to the server. Returns 0 if
4827 OK and <symbol>EOF</symbol> if unable to send the string.
4829 int PQputline(PGconn *conn,
4830 const char *string);
4835 The <command>COPY</command> data stream sent by a series of calls
4836 to <function>PQputline</function> has the same format as that
4837 returned by <function>PQgetlineAsync</function>, except that
4838 applications are not obliged to send exactly one data row per
4839 <function>PQputline</function> call; it is okay to send a partial
4840 line or multiple lines per call.
4845 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
4846 for the application to explicitly send the two characters
4847 <literal>\.</literal> as a final line to indicate to the server that it had
4848 finished sending <command>COPY</> data. While this still works, it is deprecated and the
4849 special meaning of <literal>\.</literal> can be expected to be removed in a
4850 future release. It is sufficient to call <function>PQendcopy</function> after
4851 having sent the actual data.
4857 <varlistentry id="libpq-pqputnbytes">
4859 <function>PQputnbytes</function>
4861 <primary>PQputnbytes</primary>
4867 Sends a non-null-terminated string to the server. Returns
4868 0 if OK and <symbol>EOF</symbol> if unable to send the string.
4870 int PQputnbytes(PGconn *conn,
4877 This is exactly like <function>PQputline</function>, except that the data
4878 buffer need not be null-terminated since the number of bytes to send is
4879 specified directly. Use this procedure when sending binary data.
4884 <varlistentry id="libpq-pqendcopy">
4886 <function>PQendcopy</function>
4888 <primary>PQendcopy</primary>
4894 Synchronizes with the server.
4896 int PQendcopy(PGconn *conn);
4898 This function waits until the server has finished the copying.
4899 It should either be issued when the last string has been sent
4900 to the server using <function>PQputline</function> or when the
4901 last string has been received from the server using
4902 <function>PGgetline</function>. It must be issued or the server
4903 will get <quote>out of sync</quote> with the client. Upon return
4904 from this function, the server is ready to receive the next SQL
4905 command. The return value is 0 on successful completion,
4906 nonzero otherwise. (Use <function>PQerrorMessage</function> to
4907 retrieve details if the return value is nonzero.)
4911 When using <function>PQgetResult</function>, the application should
4912 respond to a <literal>PGRES_COPY_OUT</literal> result by executing
4913 <function>PQgetline</function> repeatedly, followed by
4914 <function>PQendcopy</function> after the terminator line is seen.
4915 It should then return to the <function>PQgetResult</function> loop
4916 until <function>PQgetResult</function> returns a null pointer.
4917 Similarly a <literal>PGRES_COPY_IN</literal> result is processed
4918 by a series of <function>PQputline</function> calls followed by
4919 <function>PQendcopy</function>, then return to the
4920 <function>PQgetResult</function> loop. This arrangement will
4921 ensure that a <command>COPY</command> command embedded in a series
4922 of <acronym>SQL</acronym> commands will be executed correctly.
4926 Older applications are likely to submit a <command>COPY</command>
4927 via <function>PQexec</function> and assume that the transaction
4928 is done after <function>PQendcopy</function>. This will work
4929 correctly only if the <command>COPY</command> is the only
4930 <acronym>SQL</acronym> command in the command string.
4940 <sect1 id="libpq-control">
4941 <title>Control Functions</title>
4944 These functions control miscellaneous details of <application>libpq</>'s
4949 <varlistentry id="libpq-pqclientencoding">
4951 <function>PQclientEncoding</function>
4953 <primary>PQclientEncoding</primary>
4959 Returns the client encoding.
4961 int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
4964 Note that it returns the encoding ID, not a symbolic string
4965 such as <literal>EUC_JP</literal>. To convert an encoding ID to an encoding name, you
4969 char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
4975 <varlistentry id="libpq-pqsetclientencoding">
4977 <function>PQsetClientEncoding</function>
4979 <primary>PQsetClientEncoding</primary>
4985 Sets the client encoding.
4987 int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
4990 <replaceable>conn</replaceable> is a connection to the server,
4991 and <replaceable>encoding</replaceable> is the encoding you want to
4992 use. If the function successfully sets the encoding, it returns 0,
4993 otherwise -1. The current encoding for this connection can be
4994 determined by using <function>PQclientEncoding</>.
4999 <varlistentry id="libpq-pqseterrorverbosity">
5001 <function>PQsetErrorVerbosity</function>
5003 <primary>PQsetErrorVerbosity</primary>
5009 Determines the verbosity of messages returned by
5010 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
5019 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
5022 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
5023 the connection's previous setting. In <firstterm>TERSE</> mode,
5024 returned messages include severity, primary text, and position only;
5025 this will normally fit on a single line. The default mode produces
5026 messages that include the above plus any detail, hint, or context
5027 fields (these might span multiple lines). The <firstterm>VERBOSE</>
5028 mode includes all available fields. Changing the verbosity does not
5029 affect the messages available from already-existing
5030 <structname>PGresult</> objects, only subsequently-created ones.
5035 <varlistentry id="libpq-pqtrace">
5037 <function>PQtrace</function>
5039 <primary>PQtrace</primary>
5045 Enables tracing of the client/server communication to a debugging file stream.
5047 void PQtrace(PGconn *conn, FILE *stream);
5053 On Windows, if the <application>libpq</> library and an application are
5054 compiled with different flags, this function call will crash the
5055 application because the internal representation of the <literal>FILE</>
5056 pointers differ. Specifically, multithreaded/single-threaded,
5057 release/debug, and static/dynamic flags should be the same for the
5058 library and all applications using that library.
5065 <varlistentry id="libpq-pquntrace">
5067 <function>PQuntrace</function>
5069 <primary>PQuntrace</primary>
5075 Disables tracing started by <function>PQtrace</function>.
5077 void PQuntrace(PGconn *conn);
5086 <sect1 id="libpq-misc">
5087 <title>Miscellaneous Functions</title>
5090 As always, there are some functions that just don't fit anywhere.
5094 <varlistentry id="libpq-pqfreemem">
5096 <function>PQfreemem</function>
5098 <primary>PQfreemem</primary>
5104 Frees memory allocated by <application>libpq</>.
5106 void PQfreemem(void *ptr);
5111 Frees memory allocated by <application>libpq</>, particularly
5112 <function>PQescapeByteaConn</function>,
5113 <function>PQescapeBytea</function>,
5114 <function>PQunescapeBytea</function>,
5115 and <function>PQnotifies</function>.
5116 It is particularly important that this function, rather than
5117 <function>free()</>, be used on Microsoft Windows. This is because
5118 allocating memory in a DLL and releasing it in the application works
5119 only if multithreaded/single-threaded, release/debug, and static/dynamic
5120 flags are the same for the DLL and the application. On non-Microsoft
5121 Windows platforms, this function is the same as the standard library
5122 function <function>free()</>.
5127 <varlistentry id="libpq-pqconninfofree">
5129 <function>PQconninfoFree</function>
5131 <primary>PQconninfoFree</primary>
5137 Frees the data structures allocated by
5138 <function>PQconndefaults</> or <function>PQconninfoParse</>.
5140 void PQconninfoFree(PQconninfoOption *connOptions);
5145 A simple <function>PQfreemem</function> will not do for this, since
5146 the array contains references to subsidiary strings.
5151 <varlistentry id="libpq-pqencryptpassword">
5153 <function>PQencryptPassword</function>
5155 <primary>PQencryptPassword</primary>
5161 Prepares the encrypted form of a <productname>PostgreSQL</> password.
5163 char * PQencryptPassword(const char *passwd, const char *user);
5165 This function is intended to be used by client applications that
5166 wish to send commands like <literal>ALTER USER joe PASSWORD
5167 'pwd'</>. It is good practice not to send the original cleartext
5168 password in such a command, because it might be exposed in command
5169 logs, activity displays, and so on. Instead, use this function to
5170 convert the password to encrypted form before it is sent. The
5171 arguments are the cleartext password, and the SQL name of the user
5172 it is for. The return value is a string allocated by
5173 <function>malloc</function>, or <symbol>NULL</symbol> if out of
5174 memory. The caller can assume the string doesn't contain any
5175 special characters that would require escaping. Use
5176 <function>PQfreemem</> to free the result when done with it.
5181 <varlistentry id="libpq-pqmakeemptypgresult">
5183 <function>PQmakeEmptyPGresult</function>
5185 <primary>PQmakeEmptyPGresult</primary>
5191 Constructs an empty <structname>PGresult</structname> object with the given status.
5193 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
5198 This is <application>libpq</>'s internal function to allocate and
5199 initialize an empty <structname>PGresult</structname> object. This
5200 function returns <symbol>NULL</> if memory could not be allocated. It is
5201 exported because some applications find it useful to generate result
5202 objects (particularly objects with error status) themselves. If
5203 <parameter>conn</parameter> is not null and <parameter>status</>
5204 indicates an error, the current error message of the specified
5205 connection is copied into the <structname>PGresult</structname>.
5206 Also, if <parameter>conn</parameter> is not null, any event procedures
5207 registered in the connection are copied into the
5208 <structname>PGresult</structname>. (They do not get
5209 <literal>PGEVT_RESULTCREATE</> calls, but see
5210 <function>PQfireResultCreateEvents</function>.)
5211 Note that <function>PQclear</function> should eventually be called
5212 on the object, just as with a <structname>PGresult</structname>
5213 returned by <application>libpq</application> itself.
5218 <varlistentry id="libpq-pqfireresultcreateevents">
5220 <function>PQfireResultCreateEvents</function>
5222 <primary>PQfireResultCreateEvents</primary>
5227 Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
5228 linkend="libpq-events">) for each event procedure registered in the
5229 <structname>PGresult</structname> object. Returns non-zero for success,
5230 zero if any event procedure fails.
5233 int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
5238 The <literal>conn</> argument is passed through to event procedures
5239 but not used directly. It can be <symbol>NULL</> if the event
5240 procedures won't use it.
5244 Event procedures that have already received a
5245 <literal>PGEVT_RESULTCREATE</> or <literal>PGEVT_RESULTCOPY</> event
5246 for this object are not fired again.
5250 The main reason that this function is separate from
5251 <function>PQmakeEmptyPGResult</function> is that it is often appropriate
5252 to create a <structname>PGresult</structname> and fill it with data
5253 before invoking the event procedures.
5258 <varlistentry id="libpq-pqcopyresult">
5260 <function>PQcopyResult</function>
5262 <primary>PQcopyResult</primary>
5268 Makes a copy of a <structname>PGresult</structname> object. The copy is
5269 not linked to the source result in any way and
5270 <function>PQclear</function> must be called when the copy is no longer
5271 needed. If the function fails, <symbol>NULL</> is returned.
5274 PGresult *PQcopyResult(const PGresult *src, int flags);
5279 This is not intended to make an exact copy. The returned result is
5280 always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
5281 copy any error message in the source. (It does copy the command status
5282 string, however.) The <parameter>flags</parameter> argument determines
5283 what else is copied. It is a bitwise OR of several flags.
5284 <literal>PG_COPYRES_ATTRS</literal> specifies copying the source
5285 result's attributes (column definitions).
5286 <literal>PG_COPYRES_TUPLES</literal> specifies copying the source
5287 result's tuples. (This implies copying the attributes, too.)
5288 <literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
5289 copying the source result's notify hooks.
5290 <literal>PG_COPYRES_EVENTS</literal> specifies copying the source
5291 result's events. (But any instance data associated with the source
5297 <varlistentry id="libpq-pqsetresultattrs">
5299 <function>PQsetResultAttrs</function>
5301 <primary>PQsetResultAttrs</primary>
5307 Sets the attributes of a <structname>PGresult</structname> object.
5309 int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
5314 The provided <parameter>attDescs</parameter> are copied into the result.
5315 If the <parameter>attDescs</parameter> pointer is <symbol>NULL</> or
5316 <parameter>numAttributes</parameter> is less than one, the request is
5317 ignored and the function succeeds. If <parameter>res</parameter>
5318 already contains attributes, the function will fail. If the function
5319 fails, the return value is zero. If the function succeeds, the return
5325 <varlistentry id="libpq-pqsetvalue">
5327 <function>PQsetvalue</function>
5329 <primary>PQsetvalue</primary>
5335 Sets a tuple field value of a <structname>PGresult</structname> object.
5337 int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
5342 The function will automatically grow the result's internal tuples array
5343 as needed. However, the <parameter>tup_num</parameter> argument must be
5344 less than or equal to <function>PQntuples</function>, meaning this
5345 function can only grow the tuples array one tuple at a time. But any
5346 field of any existing tuple can be modified in any order. If a value at
5347 <parameter>field_num</parameter> already exists, it will be overwritten.
5348 If <parameter>len</parameter> is -1 or
5349 <parameter>value</parameter> is <symbol>NULL</>, the field value
5350 will be set to an SQL null value. The
5351 <parameter>value</parameter> is copied into the result's private storage,
5352 thus is no longer needed after the function
5353 returns. If the function fails, the return value is zero. If the
5354 function succeeds, the return value is non-zero.
5359 <varlistentry id="libpq-pqresultalloc">
5361 <function>PQresultAlloc</function>
5363 <primary>PQresultAlloc</primary>
5369 Allocate subsidiary storage for a <structname>PGresult</structname> object.
5371 void *PQresultAlloc(PGresult *res, size_t nBytes);
5376 Any memory allocated with this function will be freed when
5377 <parameter>res</parameter> is cleared. If the function fails,
5378 the return value is <symbol>NULL</>. The result is
5379 guaranteed to be adequately aligned for any type of data,
5380 just as for <function>malloc</>.
5385 <varlistentry id="libpq-pqlibversion">
5387 <function>PQlibVersion</function>
5389 <primary>PQlibVersion</primary>
5390 <seealso>PQserverVersion</seealso>
5396 Return the version of <productname>libpq</> that is being used.
5398 int PQlibVersion(void);
5403 The result of this function can be used to determine, at
5404 runtime, if specific functionality is available in the currently
5405 loaded version of libpq. The function can be used, for example,
5406 to determine which connection options are available for
5407 <function>PQconnectdb</> or if the <literal>hex</> <type>bytea</>
5408 output added in PostgreSQL 9.0 is supported.
5412 The number is formed by converting the major, minor, and revision
5413 numbers into two-decimal-digit numbers and appending them together.
5414 For example, version 9.1 will be returned as 90100, and version
5415 9.1.2 will be returned as 90102 (leading zeroes are not shown).
5420 This function appeared in <productname>PostgreSQL</> version 9.1, so
5421 it cannot be used to detect required functionality in earlier
5422 versions, since linking to it will create a link dependency
5433 <sect1 id="libpq-notice-processing">
5434 <title>Notice Processing</title>
5436 <indexterm zone="libpq-notice-processing">
5437 <primary>notice processing</primary>
5438 <secondary>in libpq</secondary>
5442 Notice and warning messages generated by the server are not returned
5443 by the query execution functions, since they do not imply failure of
5444 the query. Instead they are passed to a notice handling function, and
5445 execution continues normally after the handler returns. The default
5446 notice handling function prints the message on
5447 <filename>stderr</filename>, but the application can override this
5448 behavior by supplying its own handling function.
5452 For historical reasons, there are two levels of notice handling, called
5453 the notice receiver and notice processor. The default behavior is for
5454 the notice receiver to format the notice and pass a string to the notice
5455 processor for printing. However, an application that chooses to provide
5456 its own notice receiver will typically ignore the notice processor
5457 layer and just do all the work in the notice receiver.
5461 The function <function>PQsetNoticeReceiver</function>
5462 <indexterm><primary>notice
5463 receiver</></><indexterm><primary>PQsetNoticeReceiver</></> sets or
5464 examines the current notice receiver for a connection object.
5465 Similarly, <function>PQsetNoticeProcessor</function>
5466 <indexterm><primary>notice
5467 processor</></><indexterm><primary>PQsetNoticeProcessor</></> sets or
5468 examines the current notice processor.
5471 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
5474 PQsetNoticeReceiver(PGconn *conn,
5475 PQnoticeReceiver proc,
5478 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
5481 PQsetNoticeProcessor(PGconn *conn,
5482 PQnoticeProcessor proc,
5486 Each of these functions returns the previous notice receiver or
5487 processor function pointer, and sets the new value. If you supply a
5488 null function pointer, no action is taken, but the current pointer is
5493 When a notice or warning message is received from the server, or
5494 generated internally by <application>libpq</application>, the notice
5495 receiver function is called. It is passed the message in the form of
5496 a <symbol>PGRES_NONFATAL_ERROR</symbol>
5497 <structname>PGresult</structname>. (This allows the receiver to extract
5498 individual fields using <function>PQresultErrorField</>, or the complete
5499 preformatted message using <function>PQresultErrorMessage</>.) The same
5500 void pointer passed to <function>PQsetNoticeReceiver</function> is also
5501 passed. (This pointer can be used to access application-specific state
5506 The default notice receiver simply extracts the message (using
5507 <function>PQresultErrorMessage</>) and passes it to the notice
5512 The notice processor is responsible for handling a notice or warning
5513 message given in text form. It is passed the string text of the message
5514 (including a trailing newline), plus a void pointer that is the same
5515 one passed to <function>PQsetNoticeProcessor</function>. (This pointer
5516 can be used to access application-specific state if needed.)
5520 The default notice processor is simply:
5523 defaultNoticeProcessor(void *arg, const char *message)
5525 fprintf(stderr, "%s", message);
5531 Once you have set a notice receiver or processor, you should expect
5532 that that function could be called as long as either the
5533 <structname>PGconn</> object or <structname>PGresult</> objects made
5534 from it exist. At creation of a <structname>PGresult</>, the
5535 <structname>PGconn</>'s current notice handling pointers are copied
5536 into the <structname>PGresult</> for possible use by functions like
5537 <function>PQgetvalue</function>.
5542 <sect1 id="libpq-events">
5543 <title>Event System</title>
5546 <application>libpq</application>'s event system is designed to notify
5547 registered event handlers about interesting
5548 <application>libpq</application> events, such as the creation or
5549 destruction of <structname>PGconn</structname> and
5550 <structname>PGresult</structname> objects. A principal use case is that
5551 this allows applications to associate their own data with a
5552 <structname>PGconn</structname> or <structname>PGresult</structname>
5553 and ensure that that data is freed at an appropriate time.
5557 Each registered event handler is associated with two pieces of data,
5558 known to <application>libpq</application> only as opaque <literal>void *</>
5559 pointers. There is a <firstterm>passthrough</> pointer that is provided
5560 by the application when the event handler is registered with a
5561 <structname>PGconn</>. The passthrough pointer never changes for the
5562 life of the <structname>PGconn</> and all <structname>PGresult</>s
5563 generated from it; so if used, it must point to long-lived data.
5564 In addition there is an <firstterm>instance data</> pointer, which starts
5565 out <symbol>NULL</> in every <structname>PGconn</> and <structname>PGresult</>.
5566 This pointer can be manipulated using the
5567 <function>PQinstanceData</function>,
5568 <function>PQsetInstanceData</function>,
5569 <function>PQresultInstanceData</function> and
5570 <function>PQsetResultInstanceData</function> functions. Note that
5571 unlike the passthrough pointer, instance data of a <structname>PGconn</>
5572 is not automatically inherited by <structname>PGresult</>s created from
5573 it. <application>libpq</application> does not know what passthrough
5574 and instance data pointers point to (if anything) and will never attempt
5575 to free them — that is the responsibility of the event handler.
5578 <sect2 id="libpq-events-types">
5579 <title>Event Types</title>
5582 The enum <literal>PGEventId</> names the types of events handled by
5583 the event system. All its values have names beginning with
5584 <literal>PGEVT</literal>. For each event type, there is a corresponding
5585 event info structure that carries the parameters passed to the event
5586 handlers. The event types are:
5590 <varlistentry id="libpq-pgevt-register">
5591 <term><literal>PGEVT_REGISTER</literal></term>
5594 The register event occurs when <function>PQregisterEventProc</function>
5595 is called. It is the ideal time to initialize any
5596 <literal>instanceData</literal> an event procedure may need. Only one
5597 register event will be fired per event handler per connection. If the
5598 event procedure fails, the registration is aborted.
5607 When a <literal>PGEVT_REGISTER</literal> event is received, the
5608 <parameter>evtInfo</parameter> pointer should be cast to a
5609 <structname>PGEventRegister *</structname>. This structure contains a
5610 <structname>PGconn</structname> that should be in the
5611 <literal>CONNECTION_OK</literal> status; guaranteed if one calls
5612 <function>PQregisterEventProc</function> right after obtaining a good
5613 <structname>PGconn</structname>. When returning a failure code, all
5614 cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
5620 <varlistentry id="libpq-pgevt-connreset">
5621 <term><literal>PGEVT_CONNRESET</literal></term>
5624 The connection reset event is fired on completion of
5625 <function>PQreset</function> or <function>PQresetPoll</function>. In
5626 both cases, the event is only fired if the reset was successful. If
5627 the event procedure fails, the entire connection reset will fail; the
5628 <structname>PGconn</structname> is put into
5629 <literal>CONNECTION_BAD</literal> status and
5630 <function>PQresetPoll</function> will return
5631 <literal>PGRES_POLLING_FAILED</literal>.
5640 When a <literal>PGEVT_CONNRESET</literal> event is received, the
5641 <parameter>evtInfo</parameter> pointer should be cast to a
5642 <structname>PGEventConnReset *</structname>. Although the contained
5643 <structname>PGconn</structname> was just reset, all event data remains
5644 unchanged. This event should be used to reset/reload/requery any
5645 associated <literal>instanceData</literal>. Note that even if the
5646 event procedure fails to process <literal>PGEVT_CONNRESET</>, it will
5647 still receive a <literal>PGEVT_CONNDESTROY</> event when the connection
5653 <varlistentry id="libpq-pgevt-conndestroy">
5654 <term><literal>PGEVT_CONNDESTROY</literal></term>
5657 The connection destroy event is fired in response to
5658 <function>PQfinish</function>. It is the event procedure's
5659 responsibility to properly clean up its event data as libpq has no
5660 ability to manage this memory. Failure to clean up will lead
5667 } PGEventConnDestroy;
5670 When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
5671 <parameter>evtInfo</parameter> pointer should be cast to a
5672 <structname>PGEventConnDestroy *</structname>. This event is fired
5673 prior to <function>PQfinish</function> performing any other cleanup.
5674 The return value of the event procedure is ignored since there is no
5675 way of indicating a failure from <function>PQfinish</function>. Also,
5676 an event procedure failure should not abort the process of cleaning up
5682 <varlistentry id="libpq-pgevt-resultcreate">
5683 <term><literal>PGEVT_RESULTCREATE</literal></term>
5686 The result creation event is fired in response to any query execution
5687 function that generates a result, including
5688 <function>PQgetResult</function>. This event will only be fired after
5689 the result has been created successfully.
5696 } PGEventResultCreate;
5699 When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
5700 <parameter>evtInfo</parameter> pointer should be cast to a
5701 <structname>PGEventResultCreate *</structname>. The
5702 <parameter>conn</parameter> is the connection used to generate the
5703 result. This is the ideal place to initialize any
5704 <literal>instanceData</literal> that needs to be associated with the
5705 result. If the event procedure fails, the result will be cleared and
5706 the failure will be propagated. The event procedure must not try to
5707 <function>PQclear</> the result object for itself. When returning a
5708 failure code, all cleanup must be performed as no
5709 <literal>PGEVT_RESULTDESTROY</literal> event will be sent.
5714 <varlistentry id="libpq-pgevt-resultcopy">
5715 <term><literal>PGEVT_RESULTCOPY</literal></term>
5718 The result copy event is fired in response to
5719 <function>PQcopyResult</function>. This event will only be fired after
5720 the copy is complete. Only event procedures that have
5721 successfully handled the <literal>PGEVT_RESULTCREATE</literal>
5722 or <literal>PGEVT_RESULTCOPY</literal> event for the source result
5723 will receive <literal>PGEVT_RESULTCOPY</literal> events.
5728 const PGresult *src;
5730 } PGEventResultCopy;
5733 When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
5734 <parameter>evtInfo</parameter> pointer should be cast to a
5735 <structname>PGEventResultCopy *</structname>. The
5736 <parameter>src</parameter> result is what was copied while the
5737 <parameter>dest</parameter> result is the copy destination. This event
5738 can be used to provide a deep copy of <literal>instanceData</literal>,
5739 since <literal>PQcopyResult</literal> cannot do that. If the event
5740 procedure fails, the entire copy operation will fail and the
5741 <parameter>dest</parameter> result will be cleared. When returning a
5742 failure code, all cleanup must be performed as no
5743 <literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
5749 <varlistentry id="libpq-pgevt-resultdestroy">
5750 <term><literal>PGEVT_RESULTDESTROY</literal></term>
5753 The result destroy event is fired in response to a
5754 <function>PQclear</function>. It is the event procedure's
5755 responsibility to properly clean up its event data as libpq has no
5756 ability to manage this memory. Failure to clean up will lead
5763 } PGEventResultDestroy;
5766 When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
5767 <parameter>evtInfo</parameter> pointer should be cast to a
5768 <structname>PGEventResultDestroy *</structname>. This event is fired
5769 prior to <function>PQclear</function> performing any other cleanup.
5770 The return value of the event procedure is ignored since there is no
5771 way of indicating a failure from <function>PQclear</function>. Also,
5772 an event procedure failure should not abort the process of cleaning up
5780 <sect2 id="libpq-events-proc">
5781 <title>Event Callback Procedure</title>
5784 <varlistentry id="libpq-pgeventproc">
5786 <literal>PGEventProc</literal>
5788 <primary>PGEventProc</primary>
5794 <literal>PGEventProc</literal> is a typedef for a pointer to an
5795 event procedure, that is, the user callback function that receives
5796 events from libpq. The signature of an event procedure must be
5799 int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
5802 The <parameter>evtId</parameter> parameter indicates which
5803 <literal>PGEVT</literal> event occurred. The
5804 <parameter>evtInfo</parameter> pointer must be cast to the appropriate
5805 structure type to obtain further information about the event.
5806 The <parameter>passThrough</parameter> parameter is the pointer
5807 provided to <function>PQregisterEventProc</function> when the event
5808 procedure was registered. The function should return a non-zero value
5809 if it succeeds and zero if it fails.
5813 A particular event procedure can be registered only once in any
5814 <structname>PGconn</>. This is because the address of the procedure
5815 is used as a lookup key to identify the associated instance data.
5820 On Windows, functions can have two different addresses: one visible
5821 from outside a DLL and another visible from inside the DLL. One
5822 should be careful that only one of these addresses is used with
5823 <application>libpq</>'s event-procedure functions, else confusion will
5824 result. The simplest rule for writing code that will work is to
5825 ensure that event procedures are declared <literal>static</>. If the
5826 procedure's address must be available outside its own source file,
5827 expose a separate function to return the address.
5835 <sect2 id="libpq-events-funcs">
5836 <title>Event Support Functions</title>
5839 <varlistentry id="libpq-pqregistereventproc">
5841 <function>PQregisterEventProc</function>
5843 <primary>PQregisterEventProc</primary>
5849 Registers an event callback procedure with libpq.
5852 int PQregisterEventProc(PGconn *conn, PGEventProc proc,
5853 const char *name, void *passThrough);
5858 An event procedure must be registered once on each
5859 <structname>PGconn</> you want to receive events about. There is no
5860 limit, other than memory, on the number of event procedures that
5861 can be registered with a connection. The function returns a non-zero
5862 value if it succeeds and zero if it fails.
5866 The <parameter>proc</parameter> argument will be called when a libpq
5867 event is fired. Its memory address is also used to lookup
5868 <literal>instanceData</literal>. The <parameter>name</parameter>
5869 argument is used to refer to the event procedure in error messages.
5870 This value cannot be <symbol>NULL</> or a zero-length string. The name string is
5871 copied into the <structname>PGconn</>, so what is passed need not be
5872 long-lived. The <parameter>passThrough</parameter> pointer is passed
5873 to the <parameter>proc</parameter> whenever an event occurs. This
5874 argument can be <symbol>NULL</>.
5879 <varlistentry id="libpq-pqsetinstancedata">
5881 <function>PQsetInstanceData</function>
5883 <primary>PQsetInstanceData</primary>
5888 Sets the connection <parameter>conn</>'s <literal>instanceData</>
5889 for procedure <parameter>proc</> to <parameter>data</>. This
5890 returns non-zero for success and zero for failure. (Failure is
5891 only possible if <parameter>proc</> has not been properly
5892 registered in <parameter>conn</>.)
5895 int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
5901 <varlistentry id="libpq-pqinstancedata">
5903 <function>PQinstanceData</function>
5905 <primary>PQinstanceData</primary>
5911 connection <parameter>conn</>'s <literal>instanceData</literal>
5912 associated with procedure <parameter>proc</>,
5913 or <symbol>NULL</symbol> if there is none.
5916 void *PQinstanceData(const PGconn *conn, PGEventProc proc);
5922 <varlistentry id="libpq-pqresultsetinstancedata">
5924 <function>PQresultSetInstanceData</function>
5926 <primary>PQresultSetInstanceData</primary>
5931 Sets the result's <literal>instanceData</>
5932 for <parameter>proc</> to <parameter>data</>. This returns
5933 non-zero for success and zero for failure. (Failure is only
5934 possible if <parameter>proc</> has not been properly registered
5938 int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
5944 <varlistentry id="libpq-pqresultinstancedata">
5946 <function>PQresultInstanceData</function>
5948 <primary>PQresultInstanceData</primary>
5953 Returns the result's <literal>instanceData</> associated with <parameter>proc</>, or <symbol>NULL</>
5957 void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
5965 <sect2 id="libpq-events-example">
5966 <title>Event Example</title>
5969 Here is a skeleton example of managing private data associated with
5970 libpq connections and results.
5975 /* required header for libpq events (note: includes libpq-fe.h) */
5976 #include <libpq-events.h>
5978 /* The instanceData */
5986 static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);
5993 PGconn *conn = PQconnectdb("dbname = postgres");
5995 if (PQstatus(conn) != CONNECTION_OK)
5997 fprintf(stderr, "Connection to database failed: %s",
5998 PQerrorMessage(conn));
6003 /* called once on any connection that should receive events.
6004 * Sends a PGEVT_REGISTER to myEventProc.
6006 if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
6008 fprintf(stderr, "Cannot register PGEventProc\n");
6013 /* conn instanceData is available */
6014 data = PQinstanceData(conn, myEventProc);
6016 /* Sends a PGEVT_RESULTCREATE to myEventProc */
6017 res = PQexec(conn, "SELECT 1 + 1");
6019 /* result instanceData is available */
6020 data = PQresultInstanceData(res, myEventProc);
6022 /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
6023 res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);
6025 /* result instanceData is available if PG_COPYRES_EVENTS was
6026 * used during the PQcopyResult call.
6028 data = PQresultInstanceData(res_copy, myEventProc);
6030 /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
6034 /* Sends a PGEVT_CONNDESTROY to myEventProc */
6041 myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
6045 case PGEVT_REGISTER:
6047 PGEventRegister *e = (PGEventRegister *)evtInfo;
6048 mydata *data = get_mydata(e->conn);
6050 /* associate app specific data with connection */
6051 PQsetInstanceData(e->conn, myEventProc, data);
6055 case PGEVT_CONNRESET:
6057 PGEventConnReset *e = (PGEventConnReset *)evtInfo;
6058 mydata *data = PQinstanceData(e->conn, myEventProc);
6061 memset(data, 0, sizeof(mydata));
6065 case PGEVT_CONNDESTROY:
6067 PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
6068 mydata *data = PQinstanceData(e->conn, myEventProc);
6070 /* free instance data because the conn is being destroyed */
6076 case PGEVT_RESULTCREATE:
6078 PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
6079 mydata *conn_data = PQinstanceData(e->conn, myEventProc);
6080 mydata *res_data = dup_mydata(conn_data);
6082 /* associate app specific data with result (copy it from conn) */
6083 PQsetResultInstanceData(e->result, myEventProc, res_data);
6087 case PGEVT_RESULTCOPY:
6089 PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
6090 mydata *src_data = PQresultInstanceData(e->src, myEventProc);
6091 mydata *dest_data = dup_mydata(src_data);
6093 /* associate app specific data with result (copy it from a result) */
6094 PQsetResultInstanceData(e->dest, myEventProc, dest_data);
6098 case PGEVT_RESULTDESTROY:
6100 PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
6101 mydata *data = PQresultInstanceData(e->result, myEventProc);
6103 /* free instance data because the result is being destroyed */
6109 /* unknown event id, just return TRUE. */
6114 return TRUE; /* event processing succeeded */
6121 <sect1 id="libpq-envars">
6122 <title>Environment Variables</title>
6124 <indexterm zone="libpq-envars">
6125 <primary>environment variable</primary>
6129 The following environment variables can be used to select default
6130 connection parameter values, which will be used by
6131 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
6132 <function>PQsetdb</> if no value is directly specified by the calling
6133 code. These are useful to avoid hard-coding database connection
6134 information into simple client applications, for example.
6140 <primary><envar>PGHOST</envar></primary>
6142 <envar>PGHOST</envar> behaves the same as the <xref
6143 linkend="libpq-connect-host"> connection parameter.
6150 <primary><envar>PGHOSTADDR</envar></primary>
6152 <envar>PGHOSTADDR</envar> behaves the same as the <xref
6153 linkend="libpq-connect-hostaddr"> connection parameter.
6154 This can be set instead of or in addition to <envar>PGHOST</envar>
6155 to avoid DNS lookup overhead.
6162 <primary><envar>PGPORT</envar></primary>
6164 <envar>PGPORT</envar> behaves the same as the <xref
6165 linkend="libpq-connect-port"> connection parameter.
6172 <primary><envar>PGDATABASE</envar></primary>
6174 <envar>PGDATABASE</envar> behaves the same as the <xref
6175 linkend="libpq-connect-dbname"> connection parameter.
6182 <primary><envar>PGUSER</envar></primary>
6184 <envar>PGUSER</envar> behaves the same as the <xref
6185 linkend="libpq-connect-user"> connection parameter.
6192 <primary><envar>PGPASSWORD</envar></primary>
6194 <envar>PGPASSWORD</envar> behaves the same as the <xref
6195 linkend="libpq-connect-password"> connection parameter.
6196 Use of this environment variable
6197 is not recommended for security reasons, as some operating systems
6198 allow non-root users to see process environment variables via
6199 <application>ps</>; instead consider using the
6200 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
6207 <primary><envar>PGPASSFILE</envar></primary>
6209 <envar>PGPASSFILE</envar> specifies the name of the password file to
6210 use for lookups. If not set, it defaults to <filename>~/.pgpass</>
6211 (see <xref linkend="libpq-pgpass">).
6218 <primary><envar>PGSERVICE</envar></primary>
6220 <envar>PGSERVICE</envar> behaves the same as the <xref
6221 linkend="libpq-connect-service"> connection parameter.
6228 <primary><envar>PGSERVICEFILE</envar></primary>
6230 <envar>PGSERVICEFILE</envar> specifies the name of the per-user
6231 connection service file. If not set, it defaults
6232 to <filename>~/.pg_service.conf</>
6233 (see <xref linkend="libpq-pgservice">).
6240 <primary><envar>PGREALM</envar></primary>
6242 <envar>PGREALM</envar> sets the Kerberos realm to use with
6243 <productname>PostgreSQL</productname>, if it is different from the
6244 local realm. If <envar>PGREALM</envar> is set,
6245 <application>libpq</application> applications will attempt
6246 authentication with servers for this realm and use separate ticket
6247 files to avoid conflicts with local ticket files. This
6248 environment variable is only used if Kerberos authentication is
6249 selected by the server.
6256 <primary><envar>PGOPTIONS</envar></primary>
6258 <envar>PGOPTIONS</envar> behaves the same as the <xref
6259 linkend="libpq-connect-options"> connection parameter.
6266 <primary><envar>PGAPPNAME</envar></primary>
6268 <envar>PGAPPNAME</envar> behaves the same as the <xref
6269 linkend="libpq-connect-application-name"> connection parameter.
6276 <primary><envar>PGSSLMODE</envar></primary>
6278 <envar>PGSSLMODE</envar> behaves the same as the <xref
6279 linkend="libpq-connect-sslmode"> connection parameter.
6286 <primary><envar>PGREQUIRESSL</envar></primary>
6288 <envar>PGREQUIRESSL</envar> behaves the same as the <xref
6289 linkend="libpq-connect-requiressl"> connection parameter.
6296 <primary><envar>PGSSLCERT</envar></primary>
6298 <envar>PGSSLCERT</envar> behaves the same as the <xref
6299 linkend="libpq-connect-sslcert"> connection parameter.
6306 <primary><envar>PGSSLKEY</envar></primary>
6308 <envar>PGSSLKEY</envar> behaves the same as the <xref
6309 linkend="libpq-connect-sslkey"> connection parameter.
6316 <primary><envar>PGSSLROOTCERT</envar></primary>
6318 <envar>PGSSLROOTCERT</envar> behaves the same as the <xref
6319 linkend="libpq-connect-sslrootcert"> connection parameter.
6326 <primary><envar>PGSSLCRL</envar></primary>
6328 <envar>PGSSLCRL</envar> behaves the same as the <xref
6329 linkend="libpq-connect-sslcrl"> connection parameter.
6336 <primary><envar>PGREQUIREPEER</envar></primary>
6338 <envar>PGREQUIREPEER</envar> behaves the same as the <xref
6339 linkend="libpq-connect-requirepeer"> connection parameter.
6346 <primary><envar>PGKRBSRVNAME</envar></primary>
6348 <envar>PGKRBSRVNAME</envar> behaves the same as the <xref
6349 linkend="libpq-connect-krbsrvname"> connection parameter.
6356 <primary><envar>PGGSSLIB</envar></primary>
6358 <envar>PGGSSLIB</envar> behaves the same as the <xref
6359 linkend="libpq-connect-gsslib"> connection parameter.
6366 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
6368 <envar>PGCONNECT_TIMEOUT</envar> behaves the same as the <xref
6369 linkend="libpq-connect-connect-timeout"> connection parameter.
6376 <primary><envar>PGCLIENTENCODING</envar></primary>
6378 <envar>PGCLIENTENCODING</envar> behaves the same as the <xref
6379 linkend="libpq-connect-client-encoding"> connection parameter.
6386 The following environment variables can be used to specify default
6387 behavior for each <productname>PostgreSQL</productname> session. (See
6388 also the <xref linkend="sql-alteruser">
6389 and <xref linkend="sql-alterdatabase">
6390 commands for ways to set default behavior on a per-user or per-database
6397 <primary><envar>PGDATESTYLE</envar></primary>
6399 <envar>PGDATESTYLE</envar> sets the default style of date/time
6400 representation. (Equivalent to <literal>SET datestyle TO
6408 <primary><envar>PGTZ</envar></primary>
6410 <envar>PGTZ</envar> sets the default time zone. (Equivalent to
6411 <literal>SET timezone TO ...</literal>.)
6418 <primary><envar>PGGEQO</envar></primary>
6420 <envar>PGGEQO</envar> sets the default mode for the genetic query
6421 optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
6426 Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set">
6427 for information on correct values for these
6428 environment variables.
6432 The following environment variables determine internal behavior of
6433 <application>libpq</application>; they override compiled-in defaults.
6439 <primary><envar>PGSYSCONFDIR</envar></primary>
6441 <envar>PGSYSCONFDIR</envar> sets the directory containing the
6442 <filename>pg_service.conf</> file and in a future version
6443 possibly other system-wide configuration files.
6450 <primary><envar>PGLOCALEDIR</envar></primary>
6452 <envar>PGLOCALEDIR</envar> sets the directory containing the
6453 <literal>locale</> files for message internationalization.
6462 <sect1 id="libpq-pgpass">
6463 <title>The Password File</title>
6465 <indexterm zone="libpq-pgpass">
6466 <primary>password file</primary>
6468 <indexterm zone="libpq-pgpass">
6469 <primary>.pgpass</primary>
6473 The file <filename>.pgpass</filename> in a user's home directory or the
6474 file referenced by <envar>PGPASSFILE</envar> can contain passwords to
6475 be used if the connection requires a password (and no password has been
6476 specified otherwise). On Microsoft Windows the file is named
6477 <filename>%APPDATA%\postgresql\pgpass.conf</> (where
6478 <filename>%APPDATA%</> refers to the Application Data subdirectory in
6479 the user's profile).
6483 This file should contain lines of the following format:
6485 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
6487 Each of the first four fields can be a literal value, or
6488 <literal>*</literal>, which matches anything. The password field from
6489 the first line that matches the current connection parameters will be
6490 used. (Therefore, put more-specific entries first when you are using
6491 wildcards.) If an entry needs to contain <literal>:</literal> or
6492 <literal>\</literal>, escape this character with <literal>\</literal>.
6493 A host name of <literal>localhost</> matches both TCP (host name
6494 <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
6495 or the default socket directory) connections coming from the local
6496 machine. In a standby server, a database name of <literal>replication</>
6497 matches streaming replication connections made to the master server.
6498 The <literal>database</> field is of limited usefulness because
6499 users have the same password for all databases in the same cluster.
6503 On Unix systems, the permissions on <filename>.pgpass</filename> must
6504 disallow any access to world or group; achieve this by the command
6505 <command>chmod 0600 ~/.pgpass</command>. If the permissions are less
6506 strict than this, the file will be ignored. On Microsoft Windows, it
6507 is assumed that the file is stored in a directory that is secure, so
6508 no special permissions check is made.
6513 <sect1 id="libpq-pgservice">
6514 <title>The Connection Service File</title>
6516 <indexterm zone="libpq-pgservice">
6517 <primary>connection service file</primary>
6519 <indexterm zone="libpq-pgservice">
6520 <primary>pg_service.conf</primary>
6522 <indexterm zone="libpq-pgservice">
6523 <primary>.pg_service.conf</primary>
6527 The connection service file allows libpq connection parameters to be
6528 associated with a single service name. That service name can then be
6529 specified by a libpq connection, and the associated settings will be
6530 used. This allows connection parameters to be modified without requiring
6531 a recompile of the libpq application. The service name can also be
6532 specified using the <envar>PGSERVICE</envar> environment variable.
6536 The connection service file can be a per-user service file
6537 at <filename>~/.pg_service.conf</filename> or the location
6538 specified by the environment variable <envar>PGSERVICEFILE</envar>,
6539 or it can be a system-wide file
6540 at <filename>etc/pg_service.conf</filename> or in the directory
6541 specified by the environment variable
6542 <envar>PGSYSCONFDIR</envar>. If service definitions with the same
6543 name exist in the user and the system file, the user file takes
6548 The file uses an <quote>INI file</quote> format where the section
6549 name is the service name and the parameters are connection
6550 parameters; see <xref linkend="libpq-connect"> for a list. For
6559 An example file is provided at
6560 <filename>share/pg_service.conf.sample</filename>.
6565 <sect1 id="libpq-ldap">
6566 <title>LDAP Lookup of Connection Parameters</title>
6568 <indexterm zone="libpq-ldap">
6569 <primary>LDAP connection parameter lookup</primary>
6573 If <application>libpq</application> has been compiled with LDAP support (option
6574 <literal><option>--with-ldap</option></literal> for <command>configure</command>)
6575 it is possible to retrieve connection options like <literal>host</literal>
6576 or <literal>dbname</literal> via LDAP from a central server.
6577 The advantage is that if the connection parameters for a database change,
6578 the connection information doesn't have to be updated on all client machines.
6582 LDAP connection parameter lookup uses the connection service file
6583 <filename>pg_service.conf</filename> (see <xref
6584 linkend="libpq-pgservice">). A line in a
6585 <filename>pg_service.conf</filename> stanza that starts with
6586 <literal>ldap://</literal> will be recognized as an LDAP URL and an
6587 LDAP query will be performed. The result must be a list of
6588 <literal>keyword = value</literal> pairs which will be used to set
6589 connection options. The URL must conform to RFC 1959 and be of the
6592 ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
6594 where <replaceable>hostname</replaceable> defaults to
6595 <literal>localhost</literal> and <replaceable>port</replaceable>
6600 Processing of <filename>pg_service.conf</filename> is terminated after
6601 a successful LDAP lookup, but is continued if the LDAP server cannot
6602 be contacted. This is to provide a fallback with further LDAP URL
6603 lines that point to different LDAP servers, classical <literal>keyword
6604 = value</literal> pairs, or default connection options. If you would
6605 rather get an error message in this case, add a syntactically incorrect
6606 line after the LDAP URL.
6610 A sample LDAP entry that has been created with the LDIF file
6613 dn:cn=mydatabase,dc=mycompany,dc=com
6616 objectclass:groupOfUniqueNames
6618 uniqueMember:host=dbserver.mycompany.com
6619 uniqueMember:port=5439
6620 uniqueMember:dbname=mydb
6621 uniqueMember:user=mydb_user
6622 uniqueMember:sslmode=require
6624 might be queried with the following LDAP URL:
6626 ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)
6631 You can also mix regular service file entries with LDAP lookups.
6632 A complete example for a stanza in <filename>pg_service.conf</filename>
6635 # only host and port are stored in LDAP, specify dbname and user explicitly
6639 ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
6646 <sect1 id="libpq-ssl">
6647 <title>SSL Support</title>
6649 <indexterm zone="libpq-ssl">
6650 <primary>SSL</primary>
6654 <productname>PostgreSQL</> has native support for using <acronym>SSL</>
6655 connections to encrypt client/server communications for increased
6656 security. See <xref linkend="ssl-tcp"> for details about the server-side
6657 <acronym>SSL</> functionality.
6661 <application>libpq</application> reads the system-wide
6662 <productname>OpenSSL</productname> configuration file. By default, this
6663 file is named <filename>openssl.cnf</filename> and is located in the
6664 directory reported by <literal>openssl version -d</>. This default
6665 can be overridden by setting environment variable
6666 <envar>OPENSSL_CONF</envar> to the name of the desired configuration
6670 <sect2 id="libq-ssl-certificates">
6671 <title>Client Verification of Server Certificates</title>
6674 By default, <productname>PostgreSQL</> will not perform any verification of
6675 the server certificate. This means that it is possible to spoof the server
6676 identity (for example by modifying a DNS record or by taking over the server
6677 IP address) without the client knowing. In order to prevent spoofing,
6678 <acronym>SSL</> certificate verification must be used.
6682 If the parameter <literal>sslmode</> is set to <literal>verify-ca</>,
6683 libpq will verify that the server is trustworthy by checking the
6684 certificate chain up to a trusted certificate authority
6685 (<acronym>CA</>). If <literal>sslmode</> is set to <literal>verify-full</>,
6686 libpq will <emphasis>also</> verify that the server host name matches its
6687 certificate. The SSL connection will fail if the server certificate cannot
6688 be verified. <literal>verify-full</> is recommended in most
6689 security-sensitive environments.
6693 In <literal>verify-full</> mode, the <literal>cn</> (Common Name) attribute
6694 of the certificate is matched against the host name. If the <literal>cn</>
6695 attribute starts with an asterisk (<literal>*</>), it will be treated as
6696 a wildcard, and will match all characters <emphasis>except</> a dot
6697 (<literal>.</>). This means the certificate will not match subdomains.
6698 If the connection is made using an IP address instead of a host name, the
6699 IP address will be matched (without doing any DNS lookups).
6703 To allow server certificate verification, the certificate(s) of one or more
6704 trusted <acronym>CA</>s must be
6705 placed in the file <filename>~/.postgresql/root.crt</> in the user's home
6706 directory. (On Microsoft Windows the file is named
6707 <filename>%APPDATA%\postgresql\root.crt</filename>.)
6711 Certificate Revocation List (CRL) entries are also checked
6712 if the file <filename>~/.postgresql/root.crl</filename> exists
6713 (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
6718 The location of the root certificate file and the CRL can be changed by
6720 the connection parameters <literal>sslrootcert</> and <literal>sslcrl</>
6721 or the environment variables <envar>PGSSLROOTCERT</> and <envar>PGSSLCRL</>.
6725 <sect2 id="libpq-ssl-clientcert">
6726 <title>Client Certificates</title>
6729 If the server requests a trusted client certificate,
6730 <application>libpq</application> will send the certificate stored in
6731 file <filename>~/.postgresql/postgresql.crt</> in the user's home
6732 directory. The certificate must be signed by one of the certificate
6733 authorities (<acronym>CA</acronym>) trusted by the server. A matching
6734 private key file <filename>~/.postgresql/postgresql.key</> must also
6735 be present. The private
6736 key file must not allow any access to world or group; achieve this by the
6737 command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
6738 On Microsoft Windows these files are named
6739 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
6740 <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
6741 is no special permissions check since the directory is presumed secure.
6742 The location of the certificate and key files can be overridden by the
6743 connection parameters <literal>sslcert</> and <literal>sslkey</> or the
6744 environment variables <envar>PGSSLCERT</> and <envar>PGSSLKEY</>.
6748 In some cases, the client certificate might be signed by an
6749 <quote>intermediate</> certificate authority, rather than one that is
6750 directly trusted by the server. To use such a certificate, append the
6751 certificate of the signing authority to the <filename>postgresql.crt</>
6752 file, then its parent authority's certificate, and so on up to a
6753 <quote>root</> authority that is trusted by the server. The root
6754 certificate should be included in every case where
6755 <filename>postgresql.crt</> contains more than one certificate.
6759 Note that <filename>root.crt</filename> lists the top-level CAs that are
6760 considered trusted for signing server certificates. In principle it need
6761 not list the CA that signed the client's certificate, though in most cases
6762 that CA would also be trusted for server certificates.
6767 <sect2 id="libpq-ssl-protection">
6768 <title>Protection Provided in Different Modes</title>
6771 The different values for the <literal>sslmode</> parameter provide different
6772 levels of protection. SSL can provide
6773 protection against three types of attacks:
6775 <table id="libpq-ssl-protect-attacks">
6776 <title>SSL Attacks</title>
6781 <entry>Description</entry>
6787 <entry>Eavesdropping</entry>
6788 <entry>If a third party can examine the network traffic between the
6789 client and the server, it can read both connection information (including
6790 the user name and password) and the data that is passed. <acronym>SSL</>
6791 uses encryption to prevent this.
6796 <entry>Man in the middle (<acronym>MITM</>)</entry>
6797 <entry>If a third party can modify the data while passing between the
6798 client and server, it can pretend to be the server and therefore see and
6799 modify data <emphasis>even if it is encrypted</>. The third party can then
6800 forward the connection information and data to the original server,
6801 making it impossible to detect this attack. Common vectors to do this
6802 include DNS poisoning and address hijacking, whereby the client is directed
6803 to a different server than intended. There are also several other
6804 attack methods that can accomplish this. <acronym>SSL</> uses certificate
6805 verification to prevent this, by authenticating the server to the client.
6810 <entry>Impersonation</entry>
6811 <entry>If a third party can pretend to be an authorized client, it can
6812 simply access data it should not have access to. Typically this can
6813 happen through insecure password management. <acronym>SSL</> uses
6814 client certificates to prevent this, by making sure that only holders
6815 of valid certificates can access the server.
6823 For a connection to be known secure, SSL usage must be configured
6824 on <emphasis>both the client and the server</> before the connection
6825 is made. If it is only configured on the server, the client may end up
6826 sending sensitive information (e.g. passwords) before
6827 it knows that the server requires high security. In libpq, secure
6828 connections can be ensured
6829 by setting the <literal>sslmode</> parameter to <literal>verify-full</> or
6830 <literal>verify-ca</>, and providing the system with a root certificate to
6831 verify against. This is analogous to using an <literal>https</>
6832 <acronym>URL</> for encrypted web browsing.
6836 Once the server has been authenticated, the client can pass sensitive data.
6837 This means that up until this point, the client does not need to know if
6838 certificates will be used for authentication, making it safe to specify that
6839 only in the server configuration.
6843 All <acronym>SSL</> options carry overhead in the form of encryption and
6844 key-exchange, so there is a tradeoff that has to be made between performance
6845 and security. The following table illustrates the risks the different
6846 <literal>sslmode</> values protect against, and what statement they make
6847 about security and overhead:
6850 <table id="libpq-ssl-sslmode-statements">
6851 <title>SSL Mode Descriptions</title>
6855 <entry><literal>sslmode</></entry>
6856 <entry>Eavesdropping protection</entry>
6857 <entry><acronym>MITM</> protection</entry>
6858 <entry>Statement</entry>
6864 <entry><literal>disabled</></entry>
6867 <entry>I don't care about security, and I don't want to pay the overhead
6873 <entry><literal>allow</></entry>
6874 <entry>Maybe</entry>
6876 <entry>I don't care about security, but I will pay the overhead of
6877 encryption if the server insists on it.
6882 <entry><literal>prefer</></entry>
6883 <entry>Maybe</entry>
6885 <entry>I don't care about encryption, but I wish to pay the overhead of
6886 encryption if the server supports it.
6891 <entry><literal>require</></entry>
6894 <entry>I want my data to be encrypted, and I accept the overhead. I trust
6895 that the network will make sure I always connect to the server I want.
6900 <entry><literal>verify-ca</></entry>
6902 <entry><literal>Depends on CA</>-policy</entry>
6903 <entry>I want my data encrypted, and I accept the overhead. I want to be
6904 sure that I connect to a server that I trust.
6909 <entry><literal>verify-full</></entry>
6912 <entry>I want my data encrypted, and I accept the overhead. I want to be
6913 sure that I connect to a server I trust, and that it's the one I
6923 The difference between <literal>verify-ca</> and <literal>verify-full</>
6924 depends on the policy of the root <acronym>CA</>. If a public
6925 <acronym>CA</> is used, <literal>verify-ca</> allows connections to a server
6926 that <emphasis>somebody else</> may have registered with the <acronym>CA</>.
6927 In this case, <literal>verify-full</> should always be used. If
6928 a local <acronym>CA</> is used, or even a self-signed certificate, using
6929 <literal>verify-ca</> often provides enough protection.
6933 The default value for <literal>sslmode</> is <literal>prefer</>. As is shown
6934 in the table, this makes no sense from a security point of view, and it only
6935 promises performance overhead if possible. It is only provided as the default
6936 for backward compatibility, and is not recommended in secure deployments.
6941 <sect2 id="libpq-ssl-fileusage">
6942 <title>SSL Client File Usage</title>
6943 <table id="libpq-ssl-file-usage">
6944 <title>Libpq/Client SSL File Usage</title>
6949 <entry>Contents</entry>
6950 <entry>Effect</entry>
6957 <entry><filename>~/.postgresql/postgresql.crt</></entry>
6958 <entry>client certificate</entry>
6959 <entry>requested by server</entry>
6963 <entry><filename>~/.postgresql/postgresql.key</></entry>
6964 <entry>client private key</entry>
6965 <entry>proves client certificate sent by owner; does not indicate
6966 certificate owner is trustworthy</entry>
6970 <entry><filename>~/.postgresql/root.crt</></entry>
6971 <entry>trusted certificate authorities</entry>
6972 <entry>checks that server certificate is signed by a trusted certificate
6977 <entry><filename>~/.postgresql/root.crl</></entry>
6978 <entry>certificates revoked by certificate authorities</entry>
6979 <entry>server certificate must not be on this list</entry>
6987 <sect2 id="libpq-ssl-initialize">
6988 <title>SSL Library Initialization</title>
6991 If your application initializes <literal>libssl</> and/or
6992 <literal>libcrypto</> libraries and <application>libpq</application>
6993 is built with <acronym>SSL</> support, you should call
6994 <function>PQinitOpenSSL</> to tell <application>libpq</application>
6995 that the <literal>libssl</> and/or <literal>libcrypto</> libraries
6996 have been initialized by your application, so that
6997 <application>libpq</application> will not also initialize those libraries.
6998 <!-- If this URL changes replace it with a URL to www.archive.org. -->
7000 url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
7001 for details on the SSL API.
7006 <varlistentry id="libpq-pqinitopenssl">
7008 <function>PQinitOpenSSL</function>
7010 <primary>PQinitOpenSSL</primary>
7016 Allows applications to select which security libraries to initialize.
7018 void PQinitOpenSSL(int do_ssl, int do_crypto);
7023 When <parameter>do_ssl</> is non-zero, <application>libpq</application>
7024 will initialize the <application>OpenSSL</> library before first
7025 opening a database connection. When <parameter>do_crypto</> is
7026 non-zero, the <literal>libcrypto</> library will be initialized. By
7027 default (if <function>PQinitOpenSSL</> is not called), both libraries
7028 are initialized. When SSL support is not compiled in, this function is
7029 present but does nothing.
7033 If your application uses and initializes either <application>OpenSSL</>
7034 or its underlying <literal>libcrypto</> library, you <emphasis>must</>
7035 call this function with zeroes for the appropriate parameter(s)
7036 before first opening a database connection. Also be sure that you
7037 have done that initialization before opening a database connection.
7042 <varlistentry id="libpq-pqinitssl">
7044 <function>PQinitSSL</function>
7046 <primary>PQinitSSL</primary>
7052 Allows applications to select which security libraries to initialize.
7054 void PQinitSSL(int do_ssl);
7059 This function is equivalent to
7060 <literal>PQinitOpenSSL(do_ssl, do_ssl)</>.
7061 It is sufficient for applications that initialize both or neither
7062 of <application>OpenSSL</> and <literal>libcrypto</>.
7066 <function>PQinitSSL</> has been present since
7067 <productname>PostgreSQL</> 8.0, while <function>PQinitOpenSSL</>
7068 was added in <productname>PostgreSQL</> 8.4, so <function>PQinitSSL</>
7069 might be preferable for applications that need to work with older
7070 versions of <application>libpq</application>.
7081 <sect1 id="libpq-threading">
7082 <title>Behavior in Threaded Programs</title>
7084 <indexterm zone="libpq-threading">
7085 <primary>threads</primary>
7086 <secondary>with libpq</secondary>
7090 <application>libpq</application> is reentrant and thread-safe by default.
7091 You might need to use special compiler command-line
7092 options when you compile your application code. Refer to your
7093 system's documentation for information about how to build
7094 thread-enabled applications, or look in
7095 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
7096 and <literal>PTHREAD_LIBS</>. This function allows the querying of
7097 <application>libpq</application>'s thread-safe status:
7101 <varlistentry id="libpq-pqisthreadsafe">
7103 <function>PQisthreadsafe</function>
7105 <primary>PQisthreadsafe</primary>
7111 Returns the thread safety status of the
7112 <application>libpq</application> library.
7114 int PQisthreadsafe();
7119 Returns 1 if the <application>libpq</application> is thread-safe
7127 One thread restriction is that no two threads attempt to manipulate
7128 the same <structname>PGconn</> object at the same time. In particular,
7129 you cannot issue concurrent commands from different threads through
7130 the same connection object. (If you need to run concurrent commands,
7131 use multiple connections.)
7135 <structname>PGresult</> objects are read-only after creation, and so
7136 can be passed around freely between threads.
7140 The deprecated functions <function>PQrequestCancel</function> and
7141 <function>PQoidStatus</function> are not thread-safe and should not be
7142 used in multithread programs. <function>PQrequestCancel</function>
7143 can be replaced by <function>PQcancel</function>.
7144 <function>PQoidStatus</function> can be replaced by
7145 <function>PQoidValue</function>.
7149 If you are using Kerberos inside your application (in addition to inside
7150 <application>libpq</application>), you will need to do locking around
7151 Kerberos calls because Kerberos functions are not thread-safe. See
7152 function <function>PQregisterThreadLock</> in the
7153 <application>libpq</application> source code for a way to do cooperative
7154 locking between <application>libpq</application> and your application.
7158 If you experience problems with threaded applications, run the program
7159 in <filename>src/tools/thread</> to see if your platform has
7160 thread-unsafe functions. This program is run by
7161 <filename>configure</filename>, but for binary distributions your
7162 library might not match the library used to build the binaries.
7167 <sect1 id="libpq-build">
7168 <title>Building <application>libpq</application> Programs</title>
7170 <indexterm zone="libpq-build">
7171 <primary>compiling</primary>
7172 <secondary>libpq applications</secondary>
7176 To build (i.e., compile and link) a program using
7177 <application>libpq</application> you need to do all of the following
7183 Include the <filename>libpq-fe.h</filename> header file:
7185 #include <libpq-fe.h>
7187 If you failed to do that then you will normally get error messages
7188 from your compiler similar to:
7190 foo.c: In function `main':
7191 foo.c:34: `PGconn' undeclared (first use in this function)
7192 foo.c:35: `PGresult' undeclared (first use in this function)
7193 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
7194 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
7195 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
7202 Point your compiler to the directory where the <productname>PostgreSQL</> header
7203 files were installed, by supplying the
7204 <literal>-I<replaceable>directory</replaceable></literal> option
7205 to your compiler. (In some cases the compiler will look into
7206 the directory in question by default, so you can omit this
7207 option.) For instance, your compile command line could look
7210 cc -c -I/usr/local/pgsql/include testprog.c
7212 If you are using makefiles then add the option to the
7213 <varname>CPPFLAGS</varname> variable:
7215 CPPFLAGS += -I/usr/local/pgsql/include
7220 If there is any chance that your program might be compiled by
7221 other users then you should not hardcode the directory location
7222 like that. Instead, you can run the utility
7223 <command>pg_config</command><indexterm><primary>pg_config</><secondary
7224 sortas="libpq">with libpq</></> to find out where the header
7225 files are on the local system:
7227 <prompt>$</prompt> pg_config --includedir
7228 <computeroutput>/usr/local/include</computeroutput>
7233 Failure to specify the correct option to the compiler will
7234 result in an error message such as:
7236 testlibpq.c:8:22: libpq-fe.h: No such file or directory
7243 When linking the final program, specify the option
7244 <literal>-lpq</literal> so that the <application>libpq</application>
7245 library gets pulled in, as well as the option
7246 <literal>-L<replaceable>directory</replaceable></literal> to point
7247 the compiler to the directory where the
7248 <application>libpq</application> library resides. (Again, the
7249 compiler will search some directories by default.) For maximum
7250 portability, put the <option>-L</option> option before the
7251 <option>-lpq</option> option. For example:
7253 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
7258 You can find out the library directory using
7259 <command>pg_config</command> as well:
7261 <prompt>$</prompt> pg_config --libdir
7262 <computeroutput>/usr/local/pgsql/lib</computeroutput>
7267 Error messages that point to problems in this area could look like
7270 testlibpq.o: In function `main':
7271 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
7272 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
7273 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
7275 This means you forgot <option>-lpq</option>.
7277 /usr/bin/ld: cannot find -lpq
7279 This means you forgot the <option>-L</option> option or did not
7280 specify the right directory.
7289 <sect1 id="libpq-example">
7290 <title>Example Programs</title>
7293 These examples and others can be found in the
7294 directory <filename>src/test/examples</filename> in the source code
7298 <example id="libpq-example-1">
7299 <title><application>libpq</application> Example Program 1</title>
7306 * Test the C version of libpq, the PostgreSQL frontend library.
7310 #include <libpq-fe.h>
7313 exit_nicely(PGconn *conn)
7320 main(int argc, char **argv)
7322 const char *conninfo;
7330 * If the user supplies a parameter on the command line, use it as the
7331 * conninfo string; otherwise default to setting dbname=postgres and using
7332 * environment variables or defaults for all other connection parameters.
7337 conninfo = "dbname = postgres";
7339 /* Make a connection to the database */
7340 conn = PQconnectdb(conninfo);
7342 /* Check to see that the backend connection was successfully made */
7343 if (PQstatus(conn) != CONNECTION_OK)
7345 fprintf(stderr, "Connection to database failed: %s",
7346 PQerrorMessage(conn));
7351 * Our test case here involves using a cursor, for which we must be inside
7352 * a transaction block. We could do the whole thing with a single
7353 * PQexec() of "select * from pg_database", but that's too trivial to make
7357 /* Start a transaction block */
7358 res = PQexec(conn, "BEGIN");
7359 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7361 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
7367 * Should PQclear PGresult whenever it is no longer needed to avoid memory
7373 * Fetch rows from pg_database, the system catalog of databases
7375 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
7376 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7378 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
7384 res = PQexec(conn, "FETCH ALL in myportal");
7385 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7387 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
7392 /* first, print out the attribute names */
7393 nFields = PQnfields(res);
7394 for (i = 0; i < nFields; i++)
7395 printf("%-15s", PQfname(res, i));
7398 /* next, print out the rows */
7399 for (i = 0; i < PQntuples(res); i++)
7401 for (j = 0; j < nFields; j++)
7402 printf("%-15s", PQgetvalue(res, i, j));
7408 /* close the portal ... we don't bother to check for errors ... */
7409 res = PQexec(conn, "CLOSE myportal");
7412 /* end the transaction */
7413 res = PQexec(conn, "END");
7416 /* close the connection to the database and cleanup */
7425 <example id="libpq-example-2">
7426 <title><application>libpq</application> Example Program 2</title>
7432 * Test of the asynchronous notification interface
7434 * Start this program, then from psql in another window do
7436 * Repeat four times to get this program to exit.
7438 * Or, if you want to get fancy, try this:
7439 * populate a database with the following commands
7440 * (provided in src/test/examples/testlibpq2.sql):
7442 * CREATE TABLE TBL1 (i int4);
7444 * CREATE TABLE TBL2 (i int4);
7446 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
7447 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
7449 * and do this four times:
7451 * INSERT INTO TBL1 VALUES (10);
7457 #include <sys/time.h>
7458 #include <libpq-fe.h>
7461 exit_nicely(PGconn *conn)
7468 main(int argc, char **argv)
7470 const char *conninfo;
7477 * If the user supplies a parameter on the command line, use it as the
7478 * conninfo string; otherwise default to setting dbname=postgres and using
7479 * environment variables or defaults for all other connection parameters.
7484 conninfo = "dbname = postgres";
7486 /* Make a connection to the database */
7487 conn = PQconnectdb(conninfo);
7489 /* Check to see that the backend connection was successfully made */
7490 if (PQstatus(conn) != CONNECTION_OK)
7492 fprintf(stderr, "Connection to database failed: %s",
7493 PQerrorMessage(conn));
7498 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
7500 res = PQexec(conn, "LISTEN TBL2");
7501 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7503 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
7509 * should PQclear PGresult whenever it is no longer needed to avoid memory
7514 /* Quit after four notifies are received. */
7516 while (nnotifies < 4)
7519 * Sleep until something happens on the connection. We use select(2)
7520 * to wait for input, but you could also use poll() or similar
7526 sock = PQsocket(conn);
7529 break; /* shouldn't happen */
7531 FD_ZERO(&input_mask);
7532 FD_SET(sock, &input_mask);
7534 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
7536 fprintf(stderr, "select() failed: %s\n", strerror(errno));
7540 /* Now check for input */
7541 PQconsumeInput(conn);
7542 while ((notify = PQnotifies(conn)) != NULL)
7545 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
7546 notify->relname, notify->be_pid);
7552 fprintf(stderr, "Done.\n");
7554 /* close the connection to the database and cleanup */
7563 <example id="libpq-example-3">
7564 <title><application>libpq</application> Example Program 3</title>
7570 * Test out-of-line parameters and binary I/O.
7572 * Before running this, populate a database with the following commands
7573 * (provided in src/test/examples/testlibpq3.sql):
7575 * CREATE TABLE test1 (i int4, t text, b bytea);
7577 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
7578 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
7580 * The expected output is:
7584 * t = (11 bytes) 'joe's place'
7585 * b = (5 bytes) \000\001\002\003\004
7589 * t = (8 bytes) 'ho there'
7590 * b = (5 bytes) \004\003\002\001\000
7595 #include <sys/types.h>
7596 #include <libpq-fe.h>
7598 /* for ntohl/htonl */
7599 #include <netinet/in.h>
7600 #include <arpa/inet.h>
7604 exit_nicely(PGconn *conn)
7611 * This function prints a query result that is a binary-format fetch from
7612 * a table defined as in the comment above. We split it out because the
7613 * main() function uses it twice.
7616 show_binary_results(PGresult *res)
7624 /* Use PQfnumber to avoid assumptions about field order in result */
7625 i_fnum = PQfnumber(res, "i");
7626 t_fnum = PQfnumber(res, "t");
7627 b_fnum = PQfnumber(res, "b");
7629 for (i = 0; i < PQntuples(res); i++)
7637 /* Get the field values (we ignore possibility they are null!) */
7638 iptr = PQgetvalue(res, i, i_fnum);
7639 tptr = PQgetvalue(res, i, t_fnum);
7640 bptr = PQgetvalue(res, i, b_fnum);
7643 * The binary representation of INT4 is in network byte order, which
7644 * we'd better coerce to the local byte order.
7646 ival = ntohl(*((uint32_t *) iptr));
7649 * The binary representation of TEXT is, well, text, and since libpq
7650 * was nice enough to append a zero byte to it, it'll work just fine
7653 * The binary representation of BYTEA is a bunch of bytes, which could
7654 * include embedded nulls so we have to pay attention to field length.
7656 blen = PQgetlength(res, i, b_fnum);
7658 printf("tuple %d: got\n", i);
7659 printf(" i = (%d bytes) %d\n",
7660 PQgetlength(res, i, i_fnum), ival);
7661 printf(" t = (%d bytes) '%s'\n",
7662 PQgetlength(res, i, t_fnum), tptr);
7663 printf(" b = (%d bytes) ", blen);
7664 for (j = 0; j < blen; j++)
7665 printf("\\%03o", bptr[j]);
7671 main(int argc, char **argv)
7673 const char *conninfo;
7676 const char *paramValues[1];
7677 int paramLengths[1];
7678 int paramFormats[1];
7679 uint32_t binaryIntVal;
7682 * If the user supplies a parameter on the command line, use it as the
7683 * conninfo string; otherwise default to setting dbname=postgres and using
7684 * environment variables or defaults for all other connection parameters.
7689 conninfo = "dbname = postgres";
7691 /* Make a connection to the database */
7692 conn = PQconnectdb(conninfo);
7694 /* Check to see that the backend connection was successfully made */
7695 if (PQstatus(conn) != CONNECTION_OK)
7697 fprintf(stderr, "Connection to database failed: %s",
7698 PQerrorMessage(conn));
7703 * The point of this program is to illustrate use of PQexecParams() with
7704 * out-of-line parameters, as well as binary transmission of data.
7706 * This first example transmits the parameters as text, but receives the
7707 * results in binary format. By using out-of-line parameters we can
7708 * avoid a lot of tedious mucking about with quoting and escaping, even
7709 * though the data is text. Notice how we don't have to do anything
7710 * special with the quote mark in the parameter value.
7713 /* Here is our out-of-line parameter value */
7714 paramValues[0] = "joe's place";
7716 res = PQexecParams(conn,
7717 "SELECT * FROM test1 WHERE t = $1",
7719 NULL, /* let the backend deduce param type */
7721 NULL, /* don't need param lengths since text */
7722 NULL, /* default to all text params */
7723 1); /* ask for binary results */
7725 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7727 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
7732 show_binary_results(res);
7737 * In this second example we transmit an integer parameter in binary
7738 * form, and again retrieve the results in binary form.
7740 * Although we tell PQexecParams we are letting the backend deduce
7741 * parameter type, we really force the decision by casting the parameter
7742 * symbol in the query text. This is a good safety measure when sending
7743 * binary parameters.
7746 /* Convert integer value "2" to network byte order */
7747 binaryIntVal = htonl((uint32_t) 2);
7749 /* Set up parameter arrays for PQexecParams */
7750 paramValues[0] = (char *) &binaryIntVal;
7751 paramLengths[0] = sizeof(binaryIntVal);
7752 paramFormats[0] = 1; /* binary */
7754 res = PQexecParams(conn,
7755 "SELECT * FROM test1 WHERE i = $1::int4",
7757 NULL, /* let the backend deduce param type */
7761 1); /* ask for binary results */
7763 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7765 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
7770 show_binary_results(res);
7774 /* close the connection to the database and cleanup */
OSDN Git Service
