1 <!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.313 2010/07/08 10:20:14 mha Exp $ -->
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 WSAStartup() and WSACleanup() for connection startup
83 and shutdown, respectively. WSAStartup() increments an internal
84 Windows library reference count which is decremented by WSACleanup().
85 When the reference count is just one, calling 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 WSAStartup() so resources will not be freed when the last database
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 in
166 applications with time constraints. However, Kerberos and GSSAPI authentication
167 requires the host name. The following therefore applies: If
168 <literal>host</> is specified without <literal>hostaddr</>, a host name
169 lookup occurs. If <literal>hostaddr</> is specified without
170 <literal>host</>, the value for <literal>hostaddr</> gives the remote
171 address. When Kerberos is used, a reverse name query occurs to obtain
172 the host name for Kerberos. If both
173 <literal>host</> and <literal>hostaddr</> are specified, the value for
174 <literal>hostaddr</> gives the remote address; the value for
175 <literal>host</> is ignored, unless Kerberos is used, in which case that
176 value is used for Kerberos authentication. (Note that authentication is
177 likely to fail if <application>libpq</application> is passed a host name
178 that is not the name of the machine at <literal>hostaddr</>.) Also,
179 <literal>host</> rather than <literal>hostaddr</> is used to identify
180 the connection in <filename>~/.pgpass</> (see
181 <xref linkend="libpq-pgpass">).
185 Without either a host name or host address,
186 <application>libpq</application> will connect using a
187 local Unix-domain socket; or on machines without Unix-domain
188 sockets, it will attempt to connect to <literal>localhost</>.
193 <varlistentry id="libpq-connect-port" xreflabel="port">
194 <term><literal>port</literal></term>
197 Port number to connect to at the server host, or socket file
198 name extension for Unix-domain
199 connections.<indexterm><primary>port</></>
204 <varlistentry id="libpq-connect-dbname" xreflabel="dbname">
205 <term><literal>dbname</literal></term>
208 The database name. Defaults to be the same as the user name.
213 <varlistentry id="libpq-connect-user" xreflabel="user">
214 <term><literal>user</literal></term>
217 <productname>PostgreSQL</productname> user name to connect as.
218 Defaults to be the same as the operating system name of the user
219 running the application.
224 <varlistentry id="libpq-connect-password" xreflabel="password">
225 <term><literal>password</literal></term>
228 Password to be used if the server demands password authentication.
233 <varlistentry id="libpq-connect-connect-timeout" xreflabel="connect_timeout">
234 <term><literal>connect_timeout</literal></term>
237 Maximum wait for connection, in seconds (write as a decimal integer
238 string). Zero or not specified means wait indefinitely. It is not
239 recommended to use a timeout of less than 2 seconds.
244 <varlistentry id="libpq-connect-options" xreflabel="options">
245 <term><literal>options</literal></term>
248 Adds command-line options to send to the server at run-time.
249 For example, setting this to <literal>-c geqo=off</> sets the
250 session's value of the <varname>geqo</> parameter to
251 <literal>off</>. For a detailed discussion of the available
252 options, consult <xref linkend="runtime-config">.
257 <varlistentry id="libpq-connect-application-name" xreflabel="application_name">
258 <term><literal>application_name</literal></term>
261 Specifies a value for the <xref linkend="guc-application-name">
262 configuration parameter.
267 <varlistentry id="libpq-connect-fallback-application-name" xreflabel="fallback_application_name">
268 <term><literal>fallback_application_name</literal></term>
271 Specifies a fallback value for the <xref
272 linkend="guc-application-name"> configuration parameter.
273 This value will be used if no value has been given for
274 <literal>application_name</> via a connection parameter or the
275 <envar>PGAPPNAME</envar> environment variable. Specifying
276 a fallback name is useful in generic utility programs that
277 wish to set a default application name but allow it to be
278 overridden by the user.
283 <varlistentry id="libpq-keepalives" xreflabel="keepalives">
284 <term><literal>keepalives</literal></term>
287 Controls whether client-side TCP keepalives are used. The default
288 value is 1, meaning on, but you can change this to 0, meaning off,
289 if keepalives are not wanted. This parameter is ignored for
290 connections made via a Unix-domain socket.
295 <varlistentry id="libpq-keepalives-idle" xreflabel="keepalives_idle">
296 <term><literal>keepalives_idle</literal></term>
299 Controls the number of seconds of inactivity after which TCP should
300 send a keepalive message to the server. A value of zero uses the
301 system default. This parameter is ignored for connections made via a
302 Unix-domain socket, or if keepalives are disabled. It is only supported
303 on systems where the <symbol>TCP_KEEPIDLE</> or <symbol>TCP_KEEPALIVE</>
304 socket option is available, and on Windows; on other systems, it has no
310 <varlistentry id="libpq-keepalives-interval" xreflabel="keepalives_interval">
311 <term><literal>keepalives_interval</literal></term>
314 Controls the number of seconds after which a TCP keepalive message
315 that is not acknowledged by the server should be retransmitted. A
316 value of zero uses the system default. This parameter is ignored for
317 connections made via a Unix-domain socket, or if keepalives are disabled.
318 It is only supported on systems where the <symbol>TCP_KEEPINTVL</>
319 socket option is available, and on Windows; on other systems, it has no
325 <varlistentry id="libpq-keepalives-count" xreflabel="keepalives_count">
326 <term><literal>keepalives_count</literal></term>
329 Controls the number of TCP keepalives that can be lost before the
330 client's connection to the server is considered dead. A value of
331 zero uses the system default. This parameter is ignored for
332 connections made via a Unix-domain socket, or if keepalives are disabled.
333 It is only supported on systems where the <symbol>TCP_KEEPINTVL</>
334 socket option is available; on other systems, it has no effect.
339 <varlistentry id="libpq-connect-tty" xreflabel="tty">
340 <term><literal>tty</literal></term>
343 Ignored (formerly, this specified where to send server debug output).
348 <varlistentry id="libpq-connect-sslmode" xreflabel="sslmode">
349 <term><literal>sslmode</literal></term>
352 This option determines whether or with what priority a secure
353 <acronym>SSL</> TCP/IP connection will be negotiated with the
354 server. There are six modes:
357 <table id="libpq-connect-sslmode-options">
358 <title><literal>sslmode</literal> options</title>
362 <entry>Option</entry>
363 <entry>Description</entry>
370 <entry><literal>disable</></entry>
371 <entry>only try a non-<acronym>SSL</> connection</entry>
375 <entry><literal>allow</></entry>
376 <entry>first try a non-<acronym>SSL</>
377 connection; if that fails, try an <acronym>SSL</>
382 <entry><literal>prefer</> (default)</entry>
383 <entry>first try an <acronym>SSL</> connection; if
384 that fails, try a non-<acronym>SSL</>
389 <entry><literal>require</></entry>
390 <entry>only try an <acronym>SSL</> connection</entry>
394 <entry><literal>verify-ca</></entry>
395 <entry>only try an <acronym>SSL</> connection, and verify that
396 the server certificate is issued by a trusted <acronym>CA</>
401 <entry><literal>verify-full</></entry>
402 <entry>only try an <acronym>SSL</> connection, verify that
403 the server certificate is issued by a trusted <acronym>CA</> and
404 that the server hostname matches that in the certificate</entry>
412 See <xref linkend="libpq-ssl"> for a detailed description of how
417 <literal>sslmode</> is ignored for Unix domain socket
419 If <productname>PostgreSQL</> is compiled without SSL support,
420 using options <literal>require</>, <literal>verify-ca</>, or
421 <literal>verify-full</> will cause an error, while
422 options <literal>allow</> and <literal>prefer</> will be
423 accepted but <application>libpq</> will not actually attempt
425 connection.<indexterm><primary>SSL</><secondary
426 sortas="libpq">with libpq</></indexterm>
431 <varlistentry id="libpq-connect-requiressl" xreflabel="requiressl">
432 <term><literal>requiressl</literal></term>
435 This option is deprecated in favor of the <literal>sslmode</>
440 If set to 1, an <acronym>SSL</acronym> connection to the server
441 is required (this is equivalent to <literal>sslmode</>
442 <literal>require</>). <application>libpq</> will then refuse
443 to connect if the server does not accept an
444 <acronym>SSL</acronym> connection. If set to 0 (default),
445 <application>libpq</> will negotiate the connection type with
446 the server (equivalent to <literal>sslmode</>
447 <literal>prefer</>). This option is only available if
448 <productname>PostgreSQL</> is compiled with SSL support.
453 <varlistentry id="libpq-connect-sslcert" xreflabel="sslcert">
454 <term><literal>sslcert</literal></term>
457 This parameter specifies the file name of the client SSL
458 certificate, replacing the default
459 <filename>~/.postgresql/postgresql.crt</>.
460 This parameter is ignored if an SSL connection is not made.
465 <varlistentry id="libpq-connect-sslkey" xreflabel="sslkey">
466 <term><literal>sslkey</literal></term>
469 This parameter specifies the location for the secret key used for
470 the client certificate. It can either specify a filename that will
471 be used instead of the default
472 <filename>~/.postgresql/postgresql.key</>, or it can specify a key
473 obtained from an external <quote>engine</> (engines are
474 <productname>OpenSSL</> loadable modules). An external engine
475 specification should consist of a colon-separated engine name and
476 an engine-specific key identifier. This parameter is ignored if an
477 SSL connection is not made.
482 <varlistentry id="libpq-connect-sslrootcert" xreflabel="sslrootcert">
483 <term><literal>sslrootcert</literal></term>
486 This parameter specifies the name of a file containing SSL
487 certificate authority (<acronym>CA</>) certificate(s).
488 If the file exists, the server's certificate will be verified
489 to be signed by one of these authorities. The default is
490 <filename>~/.postgresql/root.crt</>.
495 <varlistentry id="libpq-connect-sslcrl" xreflabel="sslcrl">
496 <term><literal>sslcrl</literal></term>
499 This parameter specifies the file name of the SSL certificate
500 revocation list (CRL). Certificates listed in this file, if it
501 exists, will be rejected while attempting to authenticate the
502 server's certificate. The default is
503 <filename>~/.postgresql/root.crl</>.
508 <varlistentry id="libpq-connect-krbsrvname" xreflabel="krbsrvname">
509 <term><literal>krbsrvname</literal></term>
512 Kerberos service name to use when authenticating with Kerberos 5
514 This must match the service name specified in the server
515 configuration for Kerberos authentication to succeed. (See also
516 <xref linkend="kerberos-auth"> and <xref linkend="gssapi-auth">.)
521 <varlistentry id="libpq-connect-gsslib" xreflabel="gsslib">
522 <term><literal>gsslib</literal></term>
525 GSS library to use for GSSAPI authentication. Only used on Windows.
526 Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
527 library for authentication instead of the default SSPI.
532 <varlistentry id="libpq-connect-service" xreflabel="service">
533 <term><literal>service</literal></term>
536 Service name to use for additional parameters. It specifies a service
537 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
538 This allows applications to specify only a service name so connection parameters
539 can be centrally maintained. See <xref linkend="libpq-pgservice">.
545 If any parameter is unspecified, then the corresponding
546 environment variable (see <xref linkend="libpq-envars">)
547 is checked. If the environment variable is not set either,
548 then the indicated built-in defaults are used.
552 If <literal>expand_dbname</literal> is non-zero and
553 <parameter>dbname</parameter> contains an <symbol>=</symbol> sign, it
554 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
555 if it had been passed to <function>PQconnectdb</function>(see below). Previously
556 processed key words will be overridden by key words in the
557 <parameter>conninfo</parameter> string.
561 In general key words are processed from the beginning of these arrays in index
562 order. The effect of this is that when key words are repeated, the last processed
563 value is retained. Therefore, through careful placement of the
564 <parameter>dbname</parameter> key word, it is possible to determine what may
565 be overridden by a <parameter>conninfo</parameter> string, and what may not.
572 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
575 Makes a new connection to the database server.
578 PGconn *PQconnectdb(const char *conninfo);
583 This function opens a new database connection using the parameters taken
584 from the string <literal>conninfo</literal>.
588 The passed string can be empty to use all default parameters, or it can
589 contain one or more parameter settings separated by whitespace.
590 Each parameter setting is in the form <literal>keyword = value</literal>.
591 Spaces around the equal sign are optional. To write an empty value,
592 or a value containing spaces, surround it with single quotes, e.g.,
593 <literal>keyword = 'a value'</literal>. Single quotes and backslashes
594 within the value must be escaped with a backslash, i.e.,
595 <literal>\'</literal> and <literal>\\</literal>.
599 The currently recognized parameter key words are the same as above.
605 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
608 Makes a new connection to the database server.
610 PGconn *PQsetdbLogin(const char *pghost,
612 const char *pgoptions,
621 This is the predecessor of <function>PQconnectdb</function> with a fixed
622 set of parameters. It has the same functionality except that the
623 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
624 empty string for any one of the fixed parameters that is to be defaulted.
628 If the <parameter>dbName</parameter> contains an <symbol>=</symbol> sign, it
629 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
630 if it had been passed to <function>PQconnectdb</function>, and the remaining
631 parameters are then applied as above.
637 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
640 Makes a new connection to the database server.
642 PGconn *PQsetdb(char *pghost,
651 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
652 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
653 for backward compatibility with very old programs.
659 <term><function>PQconnectStartParams</function><indexterm><primary>PQconnectStartParams</></></term>
660 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
661 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
664 <indexterm><primary>nonblocking connection</primary></indexterm>
665 Make a connection to the database server in a nonblocking manner.
668 PGconn *PQconnectStartParams(const char **keywords, const char **values, int expand_dbname);
672 PGconn *PQconnectStart(const char *conninfo);
676 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
681 These three functions are used to open a connection to a database server such
682 that your application's thread of execution is not blocked on remote I/O
683 whilst doing so. The point of this approach is that the waits for I/O to
684 complete can occur in the application's main loop, rather than down inside
685 <function>PQconnectdbParams</> or <function>PQconnectdb</>, and so the
686 application can manage this operation in parallel with other activities.
690 With <function>PQconnectStartParams</function>, the database connection is made
691 using the parameters taken from the <literal>keywords</literal> and
692 <literal>values</literal> arrays, and controlled by <literal>expand_dbname</literal>,
693 as described above for <function>PQconnectdbParams</function>.
697 With <function>PQconnectStart</function>, the database connection is made
698 using the parameters taken from the string <literal>conninfo</literal> as
699 described above for <function>PQconnectdb</function>.
703 Neither <function>PQconnectStartParams</function> nor <function>PQconnectStart</function>
704 nor <function>PQconnectPoll</function> will block, so long as a number of
705 restrictions are met:
709 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
710 name and reverse name queries are not made. See the documentation of
711 these parameters under <function>PQconnectdbParams</function> above for details.
717 If you call <function>PQtrace</function>, ensure that the stream object
718 into which you trace will not block.
724 You ensure that the socket is in the appropriate state
725 before calling <function>PQconnectPoll</function>, as described below.
732 Note: use of <function>PQconnectStartParams</> is analogous to
733 <function>PQconnectStart</> shown below.
737 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
738 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
739 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
740 representing a valid connection to the database). On return from
741 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
742 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
746 If <function>PQconnectStart</> succeeds, the next stage is to poll
747 <application>libpq</> so that it can proceed with the connection sequence.
748 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
749 socket underlying the database connection.
750 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
751 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
752 read (as indicated by <function>select()</>, <function>poll()</>, or
753 similar system function).
754 Then call <function>PQconnectPoll(conn)</function> again.
755 Conversely, if <function>PQconnectPoll(conn)</function> last returned
756 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
757 to write, then call <function>PQconnectPoll(conn)</function> again.
758 If you have yet to call
759 <function>PQconnectPoll</function>, i.e., just after the call to
760 <function>PQconnectStart</function>, behave as if it last returned
761 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
762 <function>PQconnectPoll(conn)</function> returns
763 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
764 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
765 has been successfully made.
769 At any time during connection, the status of the connection can be
770 checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
771 connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
772 connection is ready. Both of these states are equally detectable
773 from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
774 during (and only during) an asynchronous connection procedure. These
775 indicate the current stage of the connection procedure and might be useful
776 to provide feedback to the user for example. These statuses are:
780 <term><symbol>CONNECTION_STARTED</symbol></term>
783 Waiting for connection to be made.
789 <term><symbol>CONNECTION_MADE</symbol></term>
792 Connection OK; waiting to send.
798 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
801 Waiting for a response from the server.
807 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
810 Received authentication; waiting for backend start-up to finish.
816 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
819 Negotiating SSL encryption.
825 <term><symbol>CONNECTION_SETENV</symbol></term>
828 Negotiating environment-driven parameter settings.
834 Note that, although these constants will remain (in order to maintain
835 compatibility), an application should never rely upon these occurring in a
836 particular order, or at all, or on the status always being one of these
837 documented values. An application might do something like this:
839 switch(PQstatus(conn))
841 case CONNECTION_STARTED:
842 feedback = "Connecting...";
845 case CONNECTION_MADE:
846 feedback = "Connected to server...";
852 feedback = "Connecting...";
858 The <literal>connect_timeout</literal> connection parameter is ignored
859 when using <function>PQconnectPoll</function>; it is the application's
860 responsibility to decide whether an excessive amount of time has elapsed.
861 Otherwise, <function>PQconnectStart</function> followed by a
862 <function>PQconnectPoll</function> loop is equivalent to
863 <function>PQconnectdb</function>.
867 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
868 <function>PQfinish</function> when you are finished with it, in order to dispose of
869 the structure and any associated memory blocks. This must be done even if
870 the connection attempt fails or is abandoned.
876 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
879 Returns the default connection options.
881 PQconninfoOption *PQconndefaults(void);
885 char *keyword; /* The keyword of the option */
886 char *envvar; /* Fallback environment variable name */
887 char *compiled; /* Fallback compiled in default value */
888 char *val; /* Option's current value, or NULL */
889 char *label; /* Label for field in connect dialog */
890 char *dispchar; /* Indicates how to display this field
891 in a connect dialog. Values are:
892 "" Display entered value as is
893 "*" Password field - hide value
894 "D" Debug option - don't show by default */
895 int dispsize; /* Field size in characters for dialog */
901 Returns a connection options array. This can be used to determine
902 all possible <function>PQconnectdb</function> options and their
903 current default values. The return value points to an array of
904 <structname>PQconninfoOption</structname> structures, which ends
905 with an entry having a null <structfield>keyword</> pointer. The
906 null pointer is returned if memory could not be allocated. Note that
907 the current default values (<structfield>val</structfield> fields)
908 will depend on environment variables and other context. Callers
909 must treat the connection options data as read-only.
913 After processing the options array, free it by passing it to
914 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
915 is leaked for each call to <function>PQconndefaults</function>.
922 <term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</></></term>
925 Returns parsed connection options from the provided connection string.
928 PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
933 Parses a connection string and returns the resulting options as an
934 array; or returns NULL if there is a problem with the connection
935 string. This can be used to determine
936 the <function>PQconnectdb</function> options in the provided
937 connection string. The return value points to an array of
938 <structname>PQconninfoOption</structname> structures, which ends
939 with an entry having a null <structfield>keyword</> pointer.
943 Note that only options explicitly specified in the string will have
944 values set in the result array; no defaults are inserted.
948 If <literal>errmsg</> is not NULL, then <literal>*errmsg</> is set
949 to NULL on success, else to a malloc'd error string explaining
950 the problem. (It is also possible for <literal>*errmsg</> to be
951 set to NULL even when NULL is returned; this indicates an out-of-memory
956 After processing the options array, free it by passing it to
957 <function>PQconninfoFree</function>. If this is not done, some memory
958 is leaked for each call to <function>PQconninfoParse</function>.
959 Conversely, if an error occurs and <literal>errmsg</> is not NULL,
960 be sure to free the error string using <function>PQfreemem</>.
967 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
970 Closes the connection to the server. Also frees
971 memory used by the <structname>PGconn</structname> object.
973 void PQfinish(PGconn *conn);
978 Note that even if the server connection attempt fails (as
979 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
980 to free the memory used by the <structname>PGconn</structname> object.
981 The <structname>PGconn</> pointer must not be used again after
982 <function>PQfinish</function> has been called.
988 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
991 Resets the communication channel to the server.
993 void PQreset(PGconn *conn);
998 This function will close the connection
999 to the server and attempt to reestablish a new
1000 connection to the same server, using all the same
1001 parameters previously used. This might be useful for
1002 error recovery if a working connection is lost.
1008 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
1009 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
1012 Reset the communication channel to the server, in a nonblocking manner.
1015 int PQresetStart(PGconn *conn);
1018 PostgresPollingStatusType PQresetPoll(PGconn *conn);
1023 These functions will close the connection to the server and attempt to
1024 reestablish a new connection to the same server, using all the same
1025 parameters previously used. This can be useful for error recovery if a
1026 working connection is lost. They differ from <function>PQreset</function> (above) in that they
1027 act in a nonblocking manner. These functions suffer from the same
1028 restrictions as <function>PQconnectStartParams</>, <function>PQconnectStart</>
1029 and <function>PQconnectPoll</>.
1033 To initiate a connection reset, call
1034 <function>PQresetStart</function>. If it returns 0, the reset has
1035 failed. If it returns 1, poll the reset using
1036 <function>PQresetPoll</function> in exactly the same way as you
1037 would create the connection using <function>PQconnectPoll</function>.
1046 <sect1 id="libpq-status">
1047 <title>Connection Status Functions</title>
1050 These functions can be used to interrogate the status
1051 of an existing database connection object.
1056 <indexterm><primary>libpq-fe.h</></>
1057 <indexterm><primary>libpq-int.h</></>
1058 <application>libpq</application> application programmers should be careful to
1059 maintain the <structname>PGconn</structname> abstraction. Use the accessor
1060 functions described below to get at the contents of <structname>PGconn</structname>.
1061 Reference to internal <structname>PGconn</structname> fields using
1062 <filename>libpq-int.h</> is not recommended because they are subject to change
1068 The following functions return parameter values established at connection.
1069 These values are fixed for the life of the <structname>PGconn</> object.
1074 <function>PQdb</function>
1076 <primary>PQdb</primary>
1082 Returns the database name of the connection.
1084 char *PQdb(const PGconn *conn);
1092 <function>PQuser</function>
1094 <primary>PQuser</primary>
1100 Returns the user name of the connection.
1102 char *PQuser(const PGconn *conn);
1110 <function>PQpass</function>
1112 <primary>PQpass</primary>
1118 Returns the password of the connection.
1120 char *PQpass(const PGconn *conn);
1128 <function>PQhost</function>
1130 <primary>PQhost</primary>
1136 Returns the server host name of the connection.
1138 char *PQhost(const PGconn *conn);
1146 <function>PQport</function>
1148 <primary>PQport</primary>
1154 Returns the port of the connection.
1157 char *PQport(const PGconn *conn);
1165 <function>PQtty</function>
1167 <primary>PQtty</primary>
1173 Returns the debug <acronym>TTY</acronym> of the connection.
1174 (This is obsolete, since the server no longer pays attention
1175 to the <acronym>TTY</acronym> setting, but the function remains
1176 for backwards compatibility.)
1179 char *PQtty(const PGconn *conn);
1187 <function>PQoptions</function>
1189 <primary>PQoptions</primary>
1195 Returns the command-line options passed in the connection request.
1197 char *PQoptions(const PGconn *conn);
1206 The following functions return status data that can change as operations
1207 are executed on the <structname>PGconn</> object.
1212 <function>PQstatus</function>
1214 <primary>PQstatus</primary>
1220 Returns the status of the connection.
1222 ConnStatusType PQstatus(const PGconn *conn);
1227 The status can be one of a number of values. However, only two of
1228 these are seen outside of an asynchronous connection procedure:
1229 <literal>CONNECTION_OK</literal> and
1230 <literal>CONNECTION_BAD</literal>. A good connection to the database
1231 has the status <literal>CONNECTION_OK</literal>. A failed
1232 connection attempt is signaled by status
1233 <literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
1234 remain so until <function>PQfinish</function>, but a communications
1235 failure might result in the status changing to
1236 <literal>CONNECTION_BAD</literal> prematurely. In that case the
1237 application could try to recover by calling
1238 <function>PQreset</function>.
1242 See the entry for <function>PQconnectStartParams</>, <function>PQconnectStart</>
1243 and <function>PQconnectPoll</> with regards to other status codes that
1251 <function>PQtransactionStatus</function>
1253 <primary>PQtransactionStatus</primary>
1259 Returns the current in-transaction status of the server.
1262 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
1265 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
1266 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
1267 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
1268 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
1269 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
1270 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
1271 has been sent to the server and not yet completed.
1276 <function>PQtransactionStatus</> will give incorrect results when using
1277 a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
1278 set to off. The server-side autocommit feature has been
1279 deprecated and does not exist in later server versions.
1287 <function>PQparameterStatus</function>
1289 <primary>PQparameterStatus</primary>
1295 Looks up a current parameter setting of the server.
1298 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
1301 Certain parameter values are reported by the server automatically at
1302 connection startup or whenever their values change.
1303 <function>PQparameterStatus</> can be used to interrogate these settings.
1304 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
1305 if the parameter is not known.
1309 Parameters reported as of the current release include
1310 <literal>server_version</>,
1311 <literal>server_encoding</>,
1312 <literal>client_encoding</>,
1313 <literal>application_name</>,
1314 <literal>is_superuser</>,
1315 <literal>session_authorization</>,
1316 <literal>DateStyle</>,
1317 <literal>IntervalStyle</>,
1318 <literal>TimeZone</>,
1319 <literal>integer_datetimes</>, and
1320 <literal>standard_conforming_strings</>.
1321 (<literal>server_encoding</>, <literal>TimeZone</>, and
1322 <literal>integer_datetimes</> were not reported by releases before 8.0;
1323 <literal>standard_conforming_strings</> was not reported by releases
1325 <literal>IntervalStyle</> was not reported by releases before 8.4;
1326 <literal>application_name</> was not reported by releases before 9.0.)
1328 <literal>server_version</>,
1329 <literal>server_encoding</> and
1330 <literal>integer_datetimes</>
1331 cannot change after startup.
1335 Pre-3.0-protocol servers do not report parameter settings, but
1336 <application>libpq</> includes logic to obtain values for
1337 <literal>server_version</> and <literal>client_encoding</> anyway.
1338 Applications are encouraged to use <function>PQparameterStatus</>
1339 rather than <foreignphrase>ad hoc</> code to determine these values.
1340 (Beware however that on a pre-3.0 connection, changing
1341 <literal>client_encoding</> via <command>SET</> after connection
1342 startup will not be reflected by <function>PQparameterStatus</>.)
1343 For <literal>server_version</>, see also
1344 <function>PQserverVersion</>, which returns the information in a
1345 numeric form that is much easier to compare against.
1349 If no value for <literal>standard_conforming_strings</> is reported,
1350 applications can assume it is <literal>off</>, that is, backslashes
1351 are treated as escapes in string literals. Also, the presence of
1352 this parameter can be taken as an indication that the escape string
1353 syntax (<literal>E'...'</>) is accepted.
1357 Although the returned pointer is declared <literal>const</>, it in fact
1358 points to mutable storage associated with the <literal>PGconn</> structure.
1359 It is unwise to assume the pointer will remain valid across queries.
1366 <function>PQprotocolVersion</function>
1368 <primary>PQprotocolVersion</primary>
1374 Interrogates the frontend/backend protocol being used.
1376 int PQprotocolVersion(const PGconn *conn);
1378 Applications might wish to use this to determine whether certain
1379 features are supported. Currently, the possible values are 2 (2.0
1380 protocol), 3 (3.0 protocol), or zero (connection bad). This will
1381 not change after connection startup is complete, but it could
1382 theoretically change during a connection reset. The 3.0 protocol
1383 will normally be used when communicating with
1384 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
1385 support only protocol 2.0. (Protocol 1.0 is obsolete and not
1386 supported by <application>libpq</application>.)
1393 <function>PQserverVersion</function>
1395 <primary>PQserverVersion</primary>
1401 Returns an integer representing the backend version.
1403 int PQserverVersion(const PGconn *conn);
1405 Applications might use this to determine the version of the database
1406 server they are connected to. The number is formed by converting
1407 the major, minor, and revision numbers into two-decimal-digit
1408 numbers and appending them together. For example, version 8.1.5
1409 will be returned as 80105, and version 8.2 will be returned as
1410 80200 (leading zeroes are not shown). Zero is returned if the
1418 <function>PQerrorMessage</function>
1420 <primary>PQerrorMessage</primary>
1426 <indexterm><primary>error message</></> Returns the error message
1427 most recently generated by an operation on the connection.
1430 char *PQerrorMessage(const PGconn *conn);
1436 Nearly all <application>libpq</> functions will set a message for
1437 <function>PQerrorMessage</function> if they fail. Note that by
1438 <application>libpq</application> convention, a nonempty
1439 <function>PQerrorMessage</function> result can be multiple lines,
1440 and will include a trailing newline. The caller should not free
1441 the result directly. It will be freed when the associated
1442 <structname>PGconn</> handle is passed to
1443 <function>PQfinish</function>. The result string should not be
1444 expected to remain the same across operations on the
1445 <literal>PGconn</> structure.
1451 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1454 Obtains the file descriptor number of the connection socket to
1455 the server. A valid descriptor will be greater than or equal
1456 to 0; a result of -1 indicates that no server connection is
1457 currently open. (This will not change during normal operation,
1458 but could change during connection setup or reset.)
1461 int PQsocket(const PGconn *conn);
1469 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1472 Returns the process <acronym>ID</acronym>
1473 (PID)<indexterm><primary>PID</><secondary>determining PID of
1474 server process</><tertiary>in libpq</></> of the backend server
1475 process handling this connection.
1478 int PQbackendPID(const PGconn *conn);
1483 The backend <acronym>PID</acronym> is useful for debugging
1484 purposes and for comparison to <command>NOTIFY</command>
1485 messages (which include the <acronym>PID</acronym> of the
1486 notifying backend process). Note that the
1487 <acronym>PID</acronym> belongs to a process executing on the
1488 database server host, not the local host!
1494 <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
1497 Returns true (1) if the connection authentication method
1498 required a password, but none was available.
1499 Returns false (0) if not.
1502 int PQconnectionNeedsPassword(const PGconn *conn);
1507 This function can be applied after a failed connection attempt
1508 to decide whether to prompt the user for a password.
1514 <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
1517 Returns true (1) if the connection authentication method
1518 used a password. Returns false (0) if not.
1521 int PQconnectionUsedPassword(const PGconn *conn);
1526 This function can be applied after either a failed or successful
1527 connection attempt to detect whether the server demanded a password.
1533 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1536 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1537 Returns the SSL structure used in the connection, or null
1538 if SSL is not in use.
1541 SSL *PQgetssl(const PGconn *conn);
1546 This structure can be used to verify encryption levels, check server
1547 certificates, and more. Refer to the <productname>OpenSSL</>
1548 documentation for information about this structure.
1552 You must define <symbol>USE_SSL</symbol> in order to get the
1553 correct prototype for this function. Doing so will also
1554 automatically include <filename>ssl.h</filename> from
1555 <productname>OpenSSL</productname>.
1565 <sect1 id="libpq-exec">
1566 <title>Command Execution Functions</title>
1569 Once a connection to a database server has been successfully
1570 established, the functions described here are used to perform
1571 SQL queries and commands.
1574 <sect2 id="libpq-exec-main">
1575 <title>Main Functions</title>
1581 <function>PQexec</function>
1583 <primary>PQexec</primary>
1589 Submits a command to the server and waits for the result.
1592 PGresult *PQexec(PGconn *conn, const char *command);
1597 Returns a <structname>PGresult</structname> pointer or possibly a null
1598 pointer. A non-null pointer will generally be returned except in
1599 out-of-memory conditions or serious errors such as inability to send
1600 the command to the server. If a null pointer is returned, it should
1601 be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
1602 <function>PQerrorMessage</function> to get more information about such
1609 It is allowed to include multiple SQL commands (separated by semicolons)
1610 in the command string. Multiple queries sent in a single
1611 <function>PQexec</> call are processed in a single transaction, unless
1612 there are explicit <command>BEGIN</command>/<command>COMMIT</command>
1613 commands included in the query string to divide it into multiple
1614 transactions. Note however that the returned
1615 <structname>PGresult</structname> structure describes only the result
1616 of the last command executed from the string. Should one of the
1617 commands fail, processing of the string stops with it and the returned
1618 <structname>PGresult</structname> describes the error condition.
1625 <function>PQexecParams</function>
1627 <primary>PQexecParams</primary>
1633 Submits a command to the server and waits for the result,
1634 with the ability to pass parameters separately from the SQL
1638 PGresult *PQexecParams(PGconn *conn,
1639 const char *command,
1641 const Oid *paramTypes,
1642 const char * const *paramValues,
1643 const int *paramLengths,
1644 const int *paramFormats,
1650 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1651 functionality: parameter values can be specified separately from the command
1652 string proper, and query results can be requested in either text or binary
1653 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1654 connections; it will fail when using protocol 2.0.
1658 The function arguments are:
1662 <term><parameter>conn</parameter></term>
1666 The connection object to send the command through.
1672 <term><parameter>command</parameter></term>
1675 The SQL command string to be executed. If parameters are used,
1676 they are referred to in the command string as <literal>$1</>,
1677 <literal>$2</>, etc.
1683 <term><parameter>nParams</parameter></term>
1686 The number of parameters supplied; it is the length of the arrays
1687 <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1688 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1689 array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
1696 <term><parameter>paramTypes[]</parameter></term>
1699 Specifies, by OID, the data types to be assigned to the
1700 parameter symbols. If <parameter>paramTypes</> is
1701 <symbol>NULL</symbol>, or any particular element in the array
1702 is zero, the server infers a data type for the parameter symbol
1703 in the same way it would do for an untyped literal string.
1709 <term><parameter>paramValues[]</parameter></term>
1712 Specifies the actual values of the parameters. A null pointer
1713 in this array means the corresponding parameter is null;
1714 otherwise the pointer points to a zero-terminated text string
1715 (for text format) or binary data in the format expected by the
1716 server (for binary format).
1722 <term><parameter>paramLengths[]</parameter></term>
1725 Specifies the actual data lengths of binary-format parameters.
1726 It is ignored for null parameters and text-format parameters.
1727 The array pointer can be null when there are no binary parameters.
1733 <term><parameter>paramFormats[]</parameter></term>
1736 Specifies whether parameters are text (put a zero in the
1737 array entry for the corresponding parameter) or binary (put
1738 a one in the array entry for the corresponding parameter).
1739 If the array pointer is null then all parameters are presumed
1743 Values passed in binary format require knowlege of
1744 the internal representation expected by the backend.
1745 For example, integers must be passed in network byte
1746 order. Passing <type>numeric</> values requires
1747 knowledge of the server storage format, as implemented
1749 <filename>src/backend/utils/adt/numeric.c::numeric_send()</> and
1750 <filename>src/backend/utils/adt/numeric.c::numeric_recv()</>.
1756 <term><parameter>resultFormat</parameter></term>
1759 Specify zero to obtain results in text format, or one to obtain
1760 results in binary format. (There is not currently a provision
1761 to obtain different result columns in different formats,
1762 although that is possible in the underlying protocol.)
1774 The primary advantage of <function>PQexecParams</> over
1775 <function>PQexec</> is that parameter values can be separated from the
1776 command string, thus avoiding the need for tedious and error-prone
1777 quoting and escaping.
1781 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
1782 one SQL command in the given string. (There can be semicolons in it,
1783 but not more than one nonempty command.) This is a limitation of the
1784 underlying protocol, but has some usefulness as an extra defense against
1785 SQL-injection attacks.
1790 Specifying parameter types via OIDs is tedious, particularly if you prefer
1791 not to hard-wire particular OID values into your program. However, you can
1792 avoid doing so even in cases where the server by itself cannot determine the
1793 type of the parameter, or chooses a different type than you want. In the
1794 SQL command text, attach an explicit cast to the parameter symbol to show what
1795 data type you will send. For example:
1797 SELECT * FROM mytable WHERE x = $1::bigint;
1799 This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
1800 by default it would be assigned the same type as <literal>x</>. Forcing the
1801 parameter type decision, either this way or by specifying a numeric type OID,
1802 is strongly recommended when sending parameter values in binary format, because
1803 binary format has less redundancy than text format and so there is less chance
1804 that the server will detect a type mismatch mistake for you.
1811 <term><function>PQprepare</function>
1813 <primary>PQprepare</primary>
1819 Submits a request to create a prepared statement with the
1820 given parameters, and waits for completion.
1822 PGresult *PQprepare(PGconn *conn,
1823 const char *stmtName,
1826 const Oid *paramTypes);
1831 <function>PQprepare</> creates a prepared statement for later
1832 execution with <function>PQexecPrepared</>. This feature allows
1833 commands that will be used repeatedly to be parsed and planned just
1834 once, rather than each time they are executed.
1835 <function>PQprepare</> is supported only in protocol 3.0 and later
1836 connections; it will fail when using protocol 2.0.
1840 The function creates a prepared statement named
1841 <parameter>stmtName</> from the <parameter>query</> string, which
1842 must contain a single SQL command. <parameter>stmtName</> can be
1843 <literal>""</> to create an unnamed statement, in which case any
1844 pre-existing unnamed statement is automatically replaced; otherwise
1845 it is an error if the statement name is already defined in the
1846 current session. If any parameters are used, they are referred
1847 to in the query as <literal>$1</>, <literal>$2</>, etc.
1848 <parameter>nParams</> is the number of parameters for which types
1849 are pre-specified in the array <parameter>paramTypes[]</>. (The
1850 array pointer can be <symbol>NULL</symbol> when
1851 <parameter>nParams</> is zero.) <parameter>paramTypes[]</>
1852 specifies, by OID, the data types to be assigned to the parameter
1853 symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1854 or any particular element in the array is zero, the server assigns
1855 a data type to the parameter symbol in the same way it would do
1856 for an untyped literal string. Also, the query can use parameter
1857 symbols with numbers higher than <parameter>nParams</>; data types
1858 will be inferred for these symbols as well. (See
1859 <function>PQdescribePrepared</function> for a means to find out
1860 what data types were inferred.)
1864 As with <function>PQexec</>, the result is normally a
1865 <structname>PGresult</structname> object whose contents indicate
1866 server-side success or failure. A null result indicates
1867 out-of-memory or inability to send the command at all. Use
1868 <function>PQerrorMessage</function> to get more information about
1875 Prepared statements for use with <function>PQexecPrepared</> can also
1876 be created by executing SQL <xref linkend="sql-prepare">
1877 statements. (But <function>PQprepare</>
1878 is more flexible since it does not require parameter types to be
1879 pre-specified.) Also, although there is no <application>libpq</>
1880 function for deleting a prepared statement, the SQL <xref
1881 linkend="sql-deallocate"> statement
1882 can be used for that purpose.
1889 <function>PQexecPrepared</function>
1891 <primary>PQexecPrepared</primary>
1897 Sends a request to execute a prepared statement with given
1898 parameters, and waits for the result.
1900 PGresult *PQexecPrepared(PGconn *conn,
1901 const char *stmtName,
1903 const char * const *paramValues,
1904 const int *paramLengths,
1905 const int *paramFormats,
1911 <function>PQexecPrepared</> is like <function>PQexecParams</>,
1912 but the command to be executed is specified by naming a
1913 previously-prepared statement, instead of giving a query string.
1914 This feature allows commands that will be used repeatedly to be
1915 parsed and planned just once, rather than each time they are
1916 executed. The statement must have been prepared previously in
1917 the current session. <function>PQexecPrepared</> is supported
1918 only in protocol 3.0 and later connections; it will fail when
1923 The parameters are identical to <function>PQexecParams</>, except that the
1924 name of a prepared statement is given instead of a query string, and the
1925 <parameter>paramTypes[]</> parameter is not present (it is not needed since
1926 the prepared statement's parameter types were determined when it was created).
1933 <function>PQdescribePrepared</function>
1935 <primary>PQdescribePrepared</primary>
1941 Submits a request to obtain information about the specified
1942 prepared statement, and waits for completion.
1944 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
1949 <function>PQdescribePrepared</> allows an application to obtain
1950 information about a previously prepared statement.
1951 <function>PQdescribePrepared</> is supported only in protocol 3.0
1952 and later connections; it will fail when using protocol 2.0.
1956 <parameter>stmtName</> can be <literal>""</> or NULL to reference
1957 the unnamed statement, otherwise it must be the name of an existing
1958 prepared statement. On success, a <structname>PGresult</> with
1959 status <literal>PGRES_COMMAND_OK</literal> is returned. The
1960 functions <function>PQnparams</function> and
1961 <function>PQparamtype</function> can be applied to this
1962 <structname>PGresult</> to obtain information about the parameters
1963 of the prepared statement, and the functions
1964 <function>PQnfields</function>, <function>PQfname</function>,
1965 <function>PQftype</function>, etc provide information about the
1966 result columns (if any) of the statement.
1973 <function>PQdescribePortal</function>
1975 <primary>PQdescribePortal</primary>
1981 Submits a request to obtain information about the specified
1982 portal, and waits for completion.
1984 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
1989 <function>PQdescribePortal</> allows an application to obtain
1990 information about a previously created portal.
1991 (<application>libpq</> does not provide any direct access to
1992 portals, but you can use this function to inspect the properties
1993 of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
1994 <function>PQdescribePortal</> is supported only in protocol 3.0
1995 and later connections; it will fail when using protocol 2.0.
1999 <parameter>portalName</> can be <literal>""</> or NULL to reference
2000 the unnamed portal, otherwise it must be the name of an existing
2001 portal. On success, a <structname>PGresult</> with status
2002 <literal>PGRES_COMMAND_OK</literal> is returned. The functions
2003 <function>PQnfields</function>, <function>PQfname</function>,
2004 <function>PQftype</function>, etc can be applied to the
2005 <structname>PGresult</> to obtain information about the result
2006 columns (if any) of the portal.
2014 The <structname>PGresult</structname><indexterm><primary>PGresult</></>
2015 structure encapsulates the result returned by the server.
2016 <application>libpq</application> application programmers should be
2017 careful to maintain the <structname>PGresult</structname> abstraction.
2018 Use the accessor functions below to get at the contents of
2019 <structname>PGresult</structname>. Avoid directly referencing the
2020 fields of the <structname>PGresult</structname> structure because they
2021 are subject to change in the future.
2026 <function>PQresultStatus</function>
2028 <primary>PQresultStatus</primary>
2034 Returns the result status of the command.
2036 ExecStatusType PQresultStatus(const PGresult *res);
2041 <function>PQresultStatus</function> can return one of the following values:
2045 <term><literal>PGRES_EMPTY_QUERY</literal></term>
2048 The string sent to the server was empty.
2054 <term><literal>PGRES_COMMAND_OK</literal></term>
2057 Successful completion of a command returning no data.
2063 <term><literal>PGRES_TUPLES_OK</literal></term>
2066 Successful completion of a command returning data (such as
2067 a <command>SELECT</> or <command>SHOW</>).
2073 <term><literal>PGRES_COPY_OUT</literal></term>
2076 Copy Out (from server) data transfer started.
2082 <term><literal>PGRES_COPY_IN</literal></term>
2085 Copy In (to server) data transfer started.
2091 <term><literal>PGRES_BAD_RESPONSE</literal></term>
2094 The server's response was not understood.
2100 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
2103 A nonfatal error (a notice or warning) occurred.
2109 <term><literal>PGRES_FATAL_ERROR</literal></term>
2112 A fatal error occurred.
2118 If the result status is <literal>PGRES_TUPLES_OK</literal>, then
2119 the functions described below can be used to retrieve the rows
2120 returned by the query. Note that a <command>SELECT</command>
2121 command that happens to retrieve zero rows still shows
2122 <literal>PGRES_TUPLES_OK</literal>.
2123 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
2124 return rows (<command>INSERT</command>, <command>UPDATE</command>,
2125 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
2126 indicate a bug in the client software.
2130 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
2131 never be returned directly by <function>PQexec</function> or other
2132 query execution functions; results of this kind are instead passed
2133 to the notice processor (see <xref
2134 linkend="libpq-notice-processing">).
2141 <function>PQresStatus</function>
2143 <primary>PQresStatus</primary>
2149 Converts the enumerated type returned by
2150 <function>PQresultStatus</> into a string constant describing the
2151 status code. The caller should not free the result.
2154 char *PQresStatus(ExecStatusType status);
2162 <function>PQresultErrorMessage</function>
2164 <primary>PQresultErrorMessage</primary>
2170 Returns the error message associated with the command, or an empty string
2171 if there was no error.
2173 char *PQresultErrorMessage(const PGresult *res);
2175 If there was an error, the returned string will include a trailing
2176 newline. The caller should not free the result directly. It will
2177 be freed when the associated <structname>PGresult</> handle is
2178 passed to <function>PQclear</function>.
2182 Immediately following a <function>PQexec</function> or
2183 <function>PQgetResult</function> call,
2184 <function>PQerrorMessage</function> (on the connection) will return
2185 the same string as <function>PQresultErrorMessage</function> (on
2186 the result). However, a <structname>PGresult</structname> will
2187 retain its error message until destroyed, whereas the connection's
2188 error message will change when subsequent operations are done.
2189 Use <function>PQresultErrorMessage</function> when you want to
2190 know the status associated with a particular
2191 <structname>PGresult</structname>; use
2192 <function>PQerrorMessage</function> when you want to know the
2193 status from the latest operation on the connection.
2199 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
2202 Returns an individual field of an error report.
2204 char *PQresultErrorField(const PGresult *res, int fieldcode);
2206 <parameter>fieldcode</> is an error field identifier; see the symbols
2207 listed below. <symbol>NULL</symbol> is returned if the
2208 <structname>PGresult</structname> is not an error or warning result,
2209 or does not include the specified field. Field values will normally
2210 not include a trailing newline. The caller should not free the
2211 result directly. It will be freed when the
2212 associated <structname>PGresult</> handle is passed to
2213 <function>PQclear</function>.
2217 The following field codes are available:
2220 <term><symbol>PG_DIAG_SEVERITY</></term>
2223 The severity; the field contents are <literal>ERROR</>,
2224 <literal>FATAL</>, or <literal>PANIC</> (in an error message),
2225 or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
2226 <literal>INFO</>, or <literal>LOG</> (in a notice message), or
2227 a localized translation of one of these. Always present.
2234 <primary>error codes</primary>
2235 <secondary>libpq</secondary>
2237 <term><symbol>PG_DIAG_SQLSTATE</></term>
2240 The SQLSTATE code for the error. The SQLSTATE code identifies
2241 the type of error that has occurred; it can be used by
2242 front-end applications to perform specific operations (such
2243 as error handling) in response to a particular database error.
2244 For a list of the possible SQLSTATE codes, see <xref
2245 linkend="errcodes-appendix">. This field is not localizable,
2246 and is always present.
2252 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
2255 The primary human-readable error message (typically one line).
2262 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
2265 Detail: an optional secondary error message carrying more
2266 detail about the problem. Might run to multiple lines.
2272 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
2275 Hint: an optional suggestion what to do about the problem.
2276 This is intended to differ from detail in that it offers advice
2277 (potentially inappropriate) rather than hard facts. Might
2278 run to multiple lines.
2284 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
2287 A string containing a decimal integer indicating an error cursor
2288 position as an index into the original statement string. The
2289 first character has index 1, and positions are measured in
2290 characters not bytes.
2296 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
2299 This is defined the same as the
2300 <symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
2301 when the cursor position refers to an internally generated
2302 command rather than the one submitted by the client. The
2303 <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
2310 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
2313 The text of a failed internally-generated command. This could
2314 be, for example, a SQL query issued by a PL/pgSQL function.
2320 <term><symbol>PG_DIAG_CONTEXT</></term>
2323 An indication of the context in which the error occurred.
2324 Presently this includes a call stack traceback of active
2325 procedural language functions and internally-generated queries.
2326 The trace is one entry per line, most recent first.
2332 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
2335 The file name of the source-code location where the error was
2342 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
2345 The line number of the source-code location where the error
2352 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
2355 The name of the source-code function reporting the error.
2363 The client is responsible for formatting displayed information to meet
2364 its needs; in particular it should break long lines as needed.
2365 Newline characters appearing in the error message fields should be
2366 treated as paragraph breaks, not line breaks.
2370 Errors generated internally by <application>libpq</application> will
2371 have severity and primary message, but typically no other fields.
2372 Errors returned by a pre-3.0-protocol server will include severity and
2373 primary message, and sometimes a detail message, but no other fields.
2377 Note that error fields are only available from
2378 <structname>PGresult</structname> objects, not
2379 <structname>PGconn</structname> objects; there is no
2380 <function>PQerrorField</function> function.
2386 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
2389 Frees the storage associated with a
2390 <structname>PGresult</structname>. Every command result should be
2391 freed via <function>PQclear</function> when it is no longer
2395 void PQclear(PGresult *res);
2400 You can keep a <structname>PGresult</structname> object around for
2401 as long as you need it; it does not go away when you issue a new
2402 command, nor even if you close the connection. To get rid of it,
2403 you must call <function>PQclear</function>. Failure to do this
2404 will result in memory leaks in your application.
2412 <sect2 id="libpq-exec-select-info">
2413 <title>Retrieving Query Result Information</title>
2416 These functions are used to extract information from a
2417 <structname>PGresult</structname> object that represents a successful
2418 query result (that is, one that has status
2419 <literal>PGRES_TUPLES_OK</literal>). They can also be used to extract
2420 information from a successful Describe operation: a Describe's result
2421 has all the same column information that actual execution of the query
2422 would provide, but it has zero rows. For objects with other status values,
2423 these functions will act as though the result has zero rows and zero columns.
2429 <function>PQntuples</function>
2431 <primary>PQntuples</primary>
2437 Returns the number of rows (tuples) in the query result. Because
2438 it returns an integer result, large result sets might overflow the
2439 return value on 32-bit operating systems.
2442 int PQntuples(const PGresult *res);
2451 <function>PQnfields</function>
2453 <primary>PQnfields</primary>
2459 Returns the number of columns (fields) in each row of the query
2463 int PQnfields(const PGresult *res);
2471 <function>PQfname</function>
2473 <primary>PQfname</primary>
2479 Returns the column name associated with the given column number.
2480 Column numbers start at 0. The caller should not free the result
2481 directly. It will be freed when the associated
2482 <structname>PGresult</> handle is passed to
2483 <function>PQclear</function>.
2485 char *PQfname(const PGresult *res,
2491 <symbol>NULL</symbol> is returned if the column number is out of range.
2498 <function>PQfnumber</function>
2500 <primary>PQfnumber</primary>
2506 Returns the column number associated with the given column name.
2508 int PQfnumber(const PGresult *res,
2509 const char *column_name);
2514 -1 is returned if the given name does not match any column.
2518 The given name is treated like an identifier in an SQL command,
2519 that is, it is downcased unless double-quoted. For example, given
2520 a query result generated from the SQL command:
2522 SELECT 1 AS FOO, 2 AS "BAR";
2524 we would have the results:
2526 PQfname(res, 0) <lineannotation>foo</lineannotation>
2527 PQfname(res, 1) <lineannotation>BAR</lineannotation>
2528 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
2529 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
2530 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
2531 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
2539 <function>PQftable</function>
2541 <primary>PQftable</primary>
2547 Returns the OID of the table from which the given column was
2548 fetched. Column numbers start at 0.
2550 Oid PQftable(const PGresult *res,
2556 <literal>InvalidOid</> is returned if the column number is out of range,
2557 or if the specified column is not a simple reference to a table column,
2558 or when using pre-3.0 protocol.
2559 You can query the system table <literal>pg_class</literal> to determine
2560 exactly which table is referenced.
2564 The type <type>Oid</type> and the constant
2565 <literal>InvalidOid</literal> will be defined when you include
2566 the <application>libpq</application> header file. They will both
2567 be some integer type.
2574 <function>PQftablecol</function>
2576 <primary>PQftablecol</primary>
2582 Returns the column number (within its table) of the column making
2583 up the specified query result column. Query-result column numbers
2584 start at 0, but table columns have nonzero numbers.
2586 int PQftablecol(const PGresult *res,
2592 Zero is returned if the column number is out of range, or if the
2593 specified column is not a simple reference to a table column, or
2594 when using pre-3.0 protocol.
2601 <function>PQfformat</function>
2603 <primary>PQfformat</primary>
2609 Returns the format code indicating the format of the given
2610 column. Column numbers start at 0.
2612 int PQfformat(const PGresult *res,
2618 Format code zero indicates textual data representation, while format
2619 code one indicates binary representation. (Other codes are reserved
2620 for future definition.)
2627 <function>PQftype</function>
2629 <primary>PQftype</primary>
2635 Returns the data type associated with the given column number.
2636 The integer returned is the internal OID number of the type.
2637 Column numbers start at 0.
2639 Oid PQftype(const PGresult *res,
2645 You can query the system table <literal>pg_type</literal> to
2646 obtain the names and properties of the various data types. The
2647 <acronym>OID</acronym>s of the built-in data types are defined
2648 in the file <filename>src/include/catalog/pg_type.h</filename>
2656 <function>PQfmod</function>
2658 <primary>PQfmod</primary>
2664 Returns the type modifier of the column associated with the
2665 given column number. Column numbers start at 0.
2667 int PQfmod(const PGresult *res,
2673 The interpretation of modifier values is type-specific; they
2674 typically indicate precision or size limits. The value -1 is
2675 used to indicate <quote>no information available</>. Most data
2676 types do not use modifiers, in which case the value is always
2684 <function>PQfsize</function>
2686 <primary>PQfsize</primary>
2692 Returns the size in bytes of the column associated with the
2693 given column number. Column numbers start at 0.
2695 int PQfsize(const PGresult *res,
2701 <function>PQfsize</> returns the space allocated for this column
2702 in a database row, in other words the size of the server's
2703 internal representation of the data type. (Accordingly, it is
2704 not really very useful to clients.) A negative value indicates
2705 the data type is variable-length.
2712 <function>PQbinaryTuples</function>
2714 <primary>PQbinaryTuples</primary>
2720 Returns 1 if the <structname>PGresult</> contains binary data
2721 and 0 if it contains text data.
2723 int PQbinaryTuples(const PGresult *res);
2728 This function is deprecated (except for its use in connection with
2729 <command>COPY</>), because it is possible for a single
2730 <structname>PGresult</> to contain text data in some columns and
2731 binary data in others. <function>PQfformat</> is preferred.
2732 <function>PQbinaryTuples</> returns 1 only if all columns of the
2733 result are binary (format 1).
2740 <function>PQgetvalue</function>
2742 <primary>PQgetvalue</primary>
2748 Returns a single field value of one row of a
2749 <structname>PGresult</structname>. Row and column numbers start
2750 at 0. The caller should not free the result directly. It will
2751 be freed when the associated <structname>PGresult</> handle is
2752 passed to <function>PQclear</function>.
2754 char *PQgetvalue(const PGresult *res,
2761 For data in text format, the value returned by
2762 <function>PQgetvalue</function> is a null-terminated character
2763 string representation of the field value. For data in binary
2764 format, the value is in the binary representation determined by
2765 the data type's <function>typsend</> and <function>typreceive</>
2766 functions. (The value is actually followed by a zero byte in
2767 this case too, but that is not ordinarily useful, since the
2768 value is likely to contain embedded nulls.)
2772 An empty string is returned if the field value is null. See
2773 <function>PQgetisnull</> to distinguish null values from
2774 empty-string values.
2778 The pointer returned by <function>PQgetvalue</function> points
2779 to storage that is part of the <structname>PGresult</structname>
2780 structure. One should not modify the data it points to, and one
2781 must explicitly copy the data into other storage if it is to be
2782 used past the lifetime of the <structname>PGresult</structname>
2790 <function>PQgetisnull</function>
2792 <primary>PQgetisnull</primary>
2795 <primary>null value</primary>
2796 <secondary sortas="libpq">in libpq</secondary>
2802 Tests a field for a null value. Row and column numbers start
2805 int PQgetisnull(const PGresult *res,
2812 This function returns 1 if the field is null and 0 if it
2813 contains a non-null value. (Note that
2814 <function>PQgetvalue</function> will return an empty string,
2815 not a null pointer, for a null field.)
2822 <function>PQgetlength</function>
2824 <primary>PQgetlength</primary>
2829 Returns the actual length of a field value in bytes. Row and
2830 column numbers start at 0.
2832 int PQgetlength(const PGresult *res,
2839 This is the actual data length for the particular data value,
2840 that is, the size of the object pointed to by
2841 <function>PQgetvalue</function>. For text data format this is
2842 the same as <function>strlen()</>. For binary format this is
2843 essential information. Note that one should <emphasis>not</>
2844 rely on <function>PQfsize</function> to obtain the actual data
2852 <function>PQnparams</function>
2854 <primary>PQnparams</primary>
2860 Returns the number of parameters of a prepared statement.
2862 int PQnparams(const PGresult *res);
2867 This function is only useful when inspecting the result of
2868 <function>PQdescribePrepared</>. For other types of queries it
2876 <function>PQparamtype</function>
2878 <primary>PQparamtype</primary>
2884 Returns the data type of the indicated statement parameter.
2885 Parameter numbers start at 0.
2887 Oid PQparamtype(const PGresult *res, int param_number);
2892 This function is only useful when inspecting the result of
2893 <function>PQdescribePrepared</>. For other types of queries it
2901 <function>PQprint</function>
2903 <primary>PQprint</primary>
2909 Prints out all the rows and, optionally, the column names to
2910 the specified output stream.
2912 void PQprint(FILE *fout, /* output stream */
2913 const PGresult *res,
2914 const PQprintOpt *po);
2916 pqbool header; /* print output field headings and row count */
2917 pqbool align; /* fill align the fields */
2918 pqbool standard; /* old brain dead format */
2919 pqbool html3; /* output HTML tables */
2920 pqbool expanded; /* expand tables */
2921 pqbool pager; /* use pager for output if needed */
2922 char *fieldSep; /* field separator */
2923 char *tableOpt; /* attributes for HTML table element */
2924 char *caption; /* HTML table caption */
2925 char **fieldName; /* null-terminated array of replacement field names */
2931 This function was formerly used by <application>psql</application>
2932 to print query results, but this is no longer the case. Note
2933 that it assumes all the data is in text format.
2940 <sect2 id="libpq-exec-nonselect">
2941 <title>Retrieving Other Result Information</title>
2944 These functions are used to extract other information from
2945 <structname>PGresult</structname> objects.
2951 <function>PQcmdStatus</function>
2953 <primary>PQcmdStatus</primary>
2959 Returns the command status tag from the SQL command that generated
2960 the <structname>PGresult</structname>.
2962 char *PQcmdStatus(PGresult *res);
2967 Commonly this is just the name of the command, but it might include
2968 additional data such as the number of rows processed. The caller
2969 should not free the result directly. It will be freed when the
2970 associated <structname>PGresult</> handle is passed to
2971 <function>PQclear</function>.
2978 <function>PQcmdTuples</function>
2980 <primary>PQcmdTuples</primary>
2986 Returns the number of rows affected by the SQL command.
2988 char *PQcmdTuples(PGresult *res);
2993 This function returns a string containing the number of rows
2994 affected by the <acronym>SQL</> statement that generated the
2995 <structname>PGresult</>. This function can only be used following
2996 the execution of a <command>SELECT</>, <command>CREATE TABLE AS</>,
2997 <command>INSERT</>, <command>UPDATE</>, <command>DELETE</>,
2998 <command>MOVE</>, <command>FETCH</>, or <command>COPY</> statement,
2999 or an <command>EXECUTE</> of a prepared query that contains an
3000 <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</> statement.
3001 If the command that generated the <structname>PGresult</> was anything
3002 else, <function>PQcmdTuples</> returns an empty string. The caller
3003 should not free the return value directly. It will be freed when
3004 the associated <structname>PGresult</> handle is passed to
3005 <function>PQclear</function>.
3012 <function>PQoidValue</function>
3014 <primary>PQoidValue</primary>
3020 Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
3021 of the inserted row, if the <acronym>SQL</> command was an
3022 <command>INSERT</> that inserted exactly one row into a table that
3023 has OIDs, or a <command>EXECUTE</> of a prepared query containing
3024 a suitable <command>INSERT</> statement. Otherwise, this function
3025 returns <literal>InvalidOid</literal>. This function will also
3026 return <literal>InvalidOid</literal> if the table affected by the
3027 <command>INSERT</> statement does not contain OIDs.
3029 Oid PQoidValue(const PGresult *res);
3037 <function>PQoidStatus</function>
3039 <primary>PQoidStatus</primary>
3045 Returns a string with the OID of the inserted row, if the
3046 <acronym>SQL</acronym> command was an <command>INSERT</command>
3047 that inserted exactly one row, or a <command>EXECUTE</command> of
3048 a prepared statement consisting of a suitable
3049 <command>INSERT</command>. (The string will be <literal>0</> if
3050 the <command>INSERT</command> did not insert exactly one row, or
3051 if the target table does not have OIDs.) If the command was not
3052 an <command>INSERT</command>, returns an empty string.
3054 char *PQoidStatus(const PGresult *res);
3059 This function is deprecated in favor of
3060 <function>PQoidValue</function>. It is not thread-safe.
3068 <sect2 id="libpq-exec-escape-string">
3069 <title>Escaping Strings for Inclusion in SQL Commands</title>
3071 <indexterm zone="libpq-exec-escape-string">
3072 <primary>escaping strings</primary>
3073 <secondary>in libpq</secondary>
3079 <function>PQescapeLiteral</function>
3081 <primary>PQescapeLiteral</primary>
3088 size_t PQescapeLiteral(PGconn *conn, char *str, size_t len)
3093 <function>PQescapeLiteral</function> escapes a string for
3094 use within an SQL command. This is useful when inserting data
3095 values as literal constants in SQL commands. Certain characters
3096 (such as quotes and backslashes) must be escaped to prevent them
3097 from being interpreted specially by the SQL parser.
3098 <function>PQescapeLiteral</> performs this operation.
3102 <function>PQescapeLiteral</> returns an escaped version of the
3103 <parameter>str</parameter> parameter in memory allocated with
3104 <function>malloc()</>. This memory should be freed using
3105 <function>PQfreemem()</> when the result is no longer needed.
3106 A terminating zero byte is not required, and should not be
3107 counted in <parameter>length</>. (If a terminating zero byte is found
3108 before <parameter>length</> bytes are processed,
3109 <function>PQescapeLiteral</> stops at the zero; the behavior is
3110 thus rather like <function>strncpy</>.) The
3111 return string has all special characters replaced so that they can
3112 be properly processed by the <productname>PostgreSQL</productname>
3113 string literal parser. A terminating zero byte is also added. The
3114 single quotes that must surround <productname>PostgreSQL</productname>
3115 string literals are included in the result string.
3119 On error, <function>PQescapeLiteral</> returns NULL and a suitable
3120 message is stored in the <parameter>conn</> object.
3125 It is especially important to do proper escaping when handling
3126 strings that were received from an untrustworthy source.
3127 Otherwise there is a security risk: you are vulnerable to
3128 <quote>SQL injection</> attacks wherein unwanted SQL commands are
3129 fed to your database.
3134 Note that it is not necessary nor correct to do escaping when a data
3135 value is passed as a separate parameter in <function>PQexecParams</> or
3136 its sibling routines.
3143 <function>PQescapeIdentifier</function>
3145 <primary>PQescapeIdentifier</primary>
3152 size_t PQescapeIdentifier(PGconn *conn, char *str, size_t len)
3157 <function>PQescapeIndentifier</function> escapes a string for
3158 use as an SQL identifier, such as a table, column, or function name.
3159 This is useful when a user-supplied identifier might contain
3160 special characters that would otherwise not be interpreted as part
3161 of the identifier by the SQL parser, or when the identifier might
3162 contain upper case characters whose case should be preserved.
3166 <function>PQescapeIdentifier</> returns a version of the
3167 <parameter>str</parameter> parameter escaped as an SQL identifier
3168 in memory allocated with <function>malloc()</>. This memory must be
3169 freed using <function>PQfreemem()</> when the result is no longer
3170 needed. A terminating zero byte is not required, and should not be
3171 counted in <parameter>length</>. (If a terminating zero byte is found
3172 before <parameter>length</> bytes are processed,
3173 <function>PQescapeIdentifier</> stops at the zero; the behavior is
3174 thus rather like <function>strncpy</>.) The
3175 return string has all special characters replaced so that it
3176 will be properly processed as an SQL identifier. A terminating zero byte
3177 is also added. The return string will also be surrounded by double
3182 On error, <function>PQescapeIdentifier</> returns NULL and a suitable
3183 message is stored in the <parameter>conn</> object.
3188 As with string literals, to prevent SQL injection attacks,
3189 SQL identifiers must be escaped when they are received from an
3190 untrustworthy source.
3198 <function>PQescapeStringConn</function>
3200 <primary>PQescapeStringConn</primary>
3207 size_t PQescapeStringConn (PGconn *conn,
3208 char *to, const char *from, size_t length,
3214 <function>PQescapeStringConn</> escapes string literals, much like
3215 <function>PQescapeLiteral</>. Unlike <function>PQescapeLiteral</>,
3216 the caller is responsible for providing an appropriately sized buffer.
3217 Furthermore, <function>PQescapeStringConn</> does not generate the
3218 single quotes that must surround <productname>PostgreSQL</> string
3219 literals; they should be provided in the SQL command that the
3220 result is inserted into. The parameter <parameter>from</> points to
3221 the first character of the string that is to be escaped, and the
3222 <parameter>length</> parameter gives the number of bytes in this
3223 string. A terminating zero byte is not required, and should not be
3224 counted in <parameter>length</>. (If a terminating zero byte is found
3225 before <parameter>length</> bytes are processed,
3226 <function>PQescapeStringConn</> stops at the zero; the behavior is
3227 thus rather like <function>strncpy</>.) <parameter>to</> shall point
3228 to a buffer that is able to hold at least one more byte than twice
3229 the value of <parameter>length</>, otherwise the behavior is undefined.
3230 Behavior is likewise undefined if the <parameter>to</> and
3231 <parameter>from</> strings overlap.
3235 If the <parameter>error</> parameter is not NULL, then
3236 <literal>*error</> is set to zero on success, nonzero on error.
3237 Presently the only possible error conditions involve invalid multibyte
3238 encoding in the source string. The output string is still generated
3239 on error, but it can be expected that the server will reject it as
3240 malformed. On error, a suitable message is stored in the
3241 <parameter>conn</> object, whether or not <parameter>error</> is NULL.
3245 <function>PQescapeStringConn</> returns the number of bytes written
3246 to <parameter>to</>, not including the terminating zero byte.
3253 <function>PQescapeString</function>
3255 <primary>PQescapeString</primary>
3262 size_t PQescapeString (char *to, const char *from, size_t length);
3267 <function>PQescapeString</> is an older, deprecated version of
3268 <function>PQescapeStringConn</>; the difference is that it does
3269 not take <parameter>conn</> or <parameter>error</> parameters.
3270 Because of this, it cannot adjust its behavior depending on the
3271 connection properties (such as character encoding) and therefore
3272 <emphasis>it might give the wrong results</>. Also, it has no way
3273 to report error conditions.
3277 <function>PQescapeString</> can be used safely in single-threaded
3278 client programs that work with only one <productname>PostgreSQL</>
3279 connection at a time (in this case it can find out what it needs to
3280 know <quote>behind the scenes</>). In other contexts it is a security
3281 hazard and should be avoided in favor of
3282 <function>PQescapeStringConn</>.
3289 <function>PQescapeByteaConn</function>
3291 <primary>PQescapeByteaConn</primary>
3297 Escapes binary data for use within an SQL command with the type
3298 <type>bytea</type>. As with <function>PQescapeStringConn</function>,
3299 this is only used when inserting data directly into an SQL command string.
3301 unsigned char *PQescapeByteaConn(PGconn *conn,
3302 const unsigned char *from,
3309 Certain byte values <emphasis>must</emphasis> be escaped (but all
3310 byte values <emphasis>can</emphasis> be escaped) when used as part
3311 of a <type>bytea</type> literal in an <acronym>SQL</acronym>
3312 statement. In general, to escape a byte, it is converted into the
3313 three digit octal number equal to the octet value, and preceded by
3314 usually two backslashes. The single quote (<literal>'</>) and backslash
3315 (<literal>\</>) characters have special alternative escape
3316 sequences. See <xref linkend="datatype-binary"> for more
3317 information. <function>PQescapeByteaConn</function> performs this
3318 operation, escaping only the minimally required bytes.
3322 The <parameter>from</parameter> parameter points to the first
3323 byte of the string that is to be escaped, and the
3324 <parameter>from_length</parameter> parameter gives the number of
3325 bytes in this binary string. (A terminating zero byte is
3326 neither necessary nor counted.) The <parameter>to_length</parameter>
3327 parameter points to a variable that will hold the resultant
3328 escaped string length. This result string length includes the terminating
3329 zero byte of the result.
3333 <function>PQescapeByteaConn</> returns an escaped version of the
3334 <parameter>from</parameter> parameter binary string in memory
3335 allocated with <function>malloc()</>. This memory should be freed using
3336 <function>PQfreemem()</> when the result is no longer needed. The
3337 return string has all special characters replaced so that they can
3338 be properly processed by the <productname>PostgreSQL</productname>
3339 string literal parser, and the <type>bytea</type> input function. A
3340 terminating zero byte is also added. The single quotes that must
3341 surround <productname>PostgreSQL</productname> string literals are
3342 not part of the result string.
3346 On error, a NULL pointer is returned, and a suitable error message
3347 is stored in the <parameter>conn</> object. Currently, the only
3348 possible error is insufficient memory for the result string.
3355 <function>PQescapeBytea</function>
3357 <primary>PQescapeBytea</primary>
3363 <function>PQescapeBytea</> is an older, deprecated version of
3364 <function>PQescapeByteaConn</>.
3366 unsigned char *PQescapeBytea(const unsigned char *from,
3373 The only difference from <function>PQescapeByteaConn</> is that
3374 <function>PQescapeBytea</> does not take a <structname>PGconn</>
3375 parameter. Because of this, it cannot adjust its behavior
3376 depending on the connection properties (in particular, whether
3377 standard-conforming strings are enabled) and therefore
3378 <emphasis>it might give the wrong results</>. Also, it has no
3379 way to return an error message on failure.
3383 <function>PQescapeBytea</> can be used safely in single-threaded
3384 client programs that work with only one <productname>PostgreSQL</>
3385 connection at a time (in this case it can find out what it needs
3386 to know <quote>behind the scenes</>). In other contexts it is
3387 a security hazard and should be avoided in favor of
3388 <function>PQescapeByteaConn</>.
3395 <function>PQunescapeBytea</function>
3397 <primary>PQunescapeBytea</primary>
3403 Converts a string representation of binary data into binary data
3404 — the reverse of <function>PQescapeBytea</function>. This
3405 is needed when retrieving <type>bytea</type> data in text format,
3406 but not when retrieving it in binary format.
3409 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
3414 The <parameter>from</parameter> parameter points to a string
3415 such as might be returned by <function>PQgetvalue</function> when applied
3416 to a <type>bytea</type> column. <function>PQunescapeBytea</function>
3417 converts this string representation into its binary representation.
3418 It returns a pointer to a buffer allocated with
3419 <function>malloc()</function>, or null on error, and puts the size of
3420 the buffer in <parameter>to_length</parameter>. The result must be
3421 freed using <function>PQfreemem</> when it is no longer needed.
3425 This conversion is not exactly the inverse of
3426 <function>PQescapeBytea</function>, because the string is not expected
3427 to be <quote>escaped</> when received from <function>PQgetvalue</function>.
3428 In particular this means there is no need for string quoting considerations,
3429 and so no need for a <structname>PGconn</> parameter.
3439 <sect1 id="libpq-async">
3440 <title>Asynchronous Command Processing</title>
3442 <indexterm zone="libpq-async">
3443 <primary>nonblocking connection</primary>
3447 The <function>PQexec</function> function is adequate for submitting
3448 commands in normal, synchronous applications. It has a couple of
3449 deficiencies, however, that can be of importance to some users:
3454 <function>PQexec</function> waits for the command to be completed.
3455 The application might have other work to do (such as maintaining a
3456 user interface), in which case it won't want to block waiting for
3463 Since the execution of the client application is suspended while it
3464 waits for the result, it is hard for the application to decide that
3465 it would like to try to cancel the ongoing command. (It can be done
3466 from a signal handler, but not otherwise.)
3472 <function>PQexec</function> can return only one
3473 <structname>PGresult</structname> structure. If the submitted command
3474 string contains multiple <acronym>SQL</acronym> commands, all but
3475 the last <structname>PGresult</structname> are discarded by
3476 <function>PQexec</function>.
3483 Applications that do not like these limitations can instead use the
3484 underlying functions that <function>PQexec</function> is built from:
3485 <function>PQsendQuery</function> and <function>PQgetResult</function>.
3487 <function>PQsendQueryParams</function>,
3488 <function>PQsendPrepare</function>,
3489 <function>PQsendQueryPrepared</function>,
3490 <function>PQsendDescribePrepared</function>, and
3491 <function>PQsendDescribePortal</function>,
3492 which can be used with <function>PQgetResult</function> to duplicate
3493 the functionality of
3494 <function>PQexecParams</function>,
3495 <function>PQprepare</function>,
3496 <function>PQexecPrepared</function>,
3497 <function>PQdescribePrepared</function>, and
3498 <function>PQdescribePortal</function>
3504 <function>PQsendQuery</function>
3506 <primary>PQsendQuery</primary>
3512 Submits a command to the server without waiting for the result(s).
3513 1 is returned if the command was successfully dispatched and 0 if
3514 not (in which case, use <function>PQerrorMessage</> to get more
3515 information about the failure).
3517 int PQsendQuery(PGconn *conn, const char *command);
3520 After successfully calling <function>PQsendQuery</function>, call
3521 <function>PQgetResult</function> one or more times to obtain the
3522 results. <function>PQsendQuery</function> cannot be called again
3523 (on the same connection) until <function>PQgetResult</function>
3524 has returned a null pointer, indicating that the command is done.
3531 <function>PQsendQueryParams</function>
3533 <primary>PQsendQueryParams</primary>
3539 Submits a command and separate parameters to the server without
3540 waiting for the result(s).
3542 int PQsendQueryParams(PGconn *conn,
3543 const char *command,
3545 const Oid *paramTypes,
3546 const char * const *paramValues,
3547 const int *paramLengths,
3548 const int *paramFormats,
3552 This is equivalent to <function>PQsendQuery</function> except that
3553 query parameters can be specified separately from the query string.
3554 The function's parameters are handled identically to
3555 <function>PQexecParams</function>. Like
3556 <function>PQexecParams</function>, it will not work on 2.0-protocol
3557 connections, and it allows only one command in the query string.
3564 <function>PQsendPrepare</>
3566 <primary>PQsendPrepare</primary>
3572 Sends a request to create a prepared statement with the given
3573 parameters, without waiting for completion.
3575 int PQsendPrepare(PGconn *conn,
3576 const char *stmtName,
3579 const Oid *paramTypes);
3582 This is an asynchronous version of <function>PQprepare</>: it
3583 returns 1 if it was able to dispatch the request, and 0 if not.
3584 After a successful call, call <function>PQgetResult</function> to
3585 determine whether the server successfully created the prepared
3586 statement. The function's parameters are handled identically to
3587 <function>PQprepare</function>. Like
3588 <function>PQprepare</function>, it will not work on 2.0-protocol
3596 <function>PQsendQueryPrepared</function>
3598 <primary>PQsendQueryPrepared</primary>
3604 Sends a request to execute a prepared statement with given
3605 parameters, without waiting for the result(s).
3607 int PQsendQueryPrepared(PGconn *conn,
3608 const char *stmtName,
3610 const char * const *paramValues,
3611 const int *paramLengths,
3612 const int *paramFormats,
3616 This is similar to <function>PQsendQueryParams</function>, but
3617 the command to be executed is specified by naming a
3618 previously-prepared statement, instead of giving a query string.
3619 The function's parameters are handled identically to
3620 <function>PQexecPrepared</function>. Like
3621 <function>PQexecPrepared</function>, it will not work on
3622 2.0-protocol connections.
3629 <function>PQsendDescribePrepared</>
3631 <primary>PQsendDescribePrepared</primary>
3637 Submits a request to obtain information about the specified
3638 prepared statement, without waiting for completion.
3640 int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
3643 This is an asynchronous version of <function>PQdescribePrepared</>:
3644 it returns 1 if it was able to dispatch the request, and 0 if not.
3645 After a successful call, call <function>PQgetResult</function> to
3646 obtain the results. The function's parameters are handled
3647 identically to <function>PQdescribePrepared</function>. Like
3648 <function>PQdescribePrepared</function>, it will not work on
3649 2.0-protocol connections.
3656 <function>PQsendDescribePortal</>
3658 <primary>PQsendDescribePortal</primary>
3664 Submits a request to obtain information about the specified
3665 portal, without waiting for completion.
3667 int PQsendDescribePortal(PGconn *conn, const char *portalName);
3670 This is an asynchronous version of <function>PQdescribePortal</>:
3671 it returns 1 if it was able to dispatch the request, and 0 if not.
3672 After a successful call, call <function>PQgetResult</function> to
3673 obtain the results. The function's parameters are handled
3674 identically to <function>PQdescribePortal</function>. Like
3675 <function>PQdescribePortal</function>, it will not work on
3676 2.0-protocol connections.
3683 <function>PQgetResult</function>
3685 <primary>PQgetResult</primary>
3691 Waits for the next result from a prior
3692 <function>PQsendQuery</function>,
3693 <function>PQsendQueryParams</function>,
3694 <function>PQsendPrepare</function>, or
3695 <function>PQsendQueryPrepared</function> call, and returns it.
3696 A null pointer is returned when the command is complete and there
3697 will be no more results.
3699 PGresult *PQgetResult(PGconn *conn);
3704 <function>PQgetResult</function> must be called repeatedly until
3705 it returns a null pointer, indicating that the command is done.
3706 (If called when no command is active,
3707 <function>PQgetResult</function> will just return a null pointer
3708 at once.) Each non-null result from
3709 <function>PQgetResult</function> should be processed using the
3710 same <structname>PGresult</> accessor functions previously
3711 described. Don't forget to free each result object with
3712 <function>PQclear</function> when done with it. Note that
3713 <function>PQgetResult</function> will block only if a command is
3714 active and the necessary response data has not yet been read by
3715 <function>PQconsumeInput</function>.
3723 Using <function>PQsendQuery</function> and
3724 <function>PQgetResult</function> solves one of
3725 <function>PQexec</function>'s problems: If a command string contains
3726 multiple <acronym>SQL</acronym> commands, the results of those commands
3727 can be obtained individually. (This allows a simple form of overlapped
3728 processing, by the way: the client can be handling the results of one
3729 command while the server is still working on later queries in the same
3730 command string.) However, calling <function>PQgetResult</function>
3731 will still cause the client to block until the server completes the
3732 next <acronym>SQL</acronym> command. This can be avoided by proper
3733 use of two more functions:
3738 <function>PQconsumeInput</function>
3740 <primary>PQconsumeInput</primary>
3746 If input is available from the server, consume it.
3748 int PQconsumeInput(PGconn *conn);
3753 <function>PQconsumeInput</function> normally returns 1 indicating
3754 <quote>no error</quote>, but returns 0 if there was some kind of
3755 trouble (in which case <function>PQerrorMessage</function> can be
3756 consulted). Note that the result does not say whether any input
3757 data was actually collected. After calling
3758 <function>PQconsumeInput</function>, the application can check
3759 <function>PQisBusy</function> and/or
3760 <function>PQnotifies</function> to see if their state has changed.
3764 <function>PQconsumeInput</function> can be called even if the
3765 application is not prepared to deal with a result or notification
3766 just yet. The function will read available data and save it in
3767 a buffer, thereby causing a <function>select()</function>
3768 read-ready indication to go away. The application can thus use
3769 <function>PQconsumeInput</function> to clear the
3770 <function>select()</function> condition immediately, and then
3771 examine the results at leisure.
3778 <function>PQisBusy</function>
3780 <primary>PQisBusy</primary>
3786 Returns 1 if a command is busy, that is,
3787 <function>PQgetResult</function> would block waiting for input.
3788 A 0 return indicates that <function>PQgetResult</function> can be
3789 called with assurance of not blocking.
3791 int PQisBusy(PGconn *conn);
3796 <function>PQisBusy</function> will not itself attempt to read data
3797 from the server; therefore <function>PQconsumeInput</function>
3798 must be invoked first, or the busy state will never end.
3806 A typical application using these functions will have a main loop that
3807 uses <function>select()</function> or <function>poll()</> to wait for
3808 all the conditions that it must respond to. One of the conditions
3809 will be input available from the server, which in terms of
3810 <function>select()</function> means readable data on the file
3811 descriptor identified by <function>PQsocket</function>. When the main
3812 loop detects input ready, it should call
3813 <function>PQconsumeInput</function> to read the input. It can then
3814 call <function>PQisBusy</function>, followed by
3815 <function>PQgetResult</function> if <function>PQisBusy</function>
3816 returns false (0). It can also call <function>PQnotifies</function>
3817 to detect <command>NOTIFY</> messages (see <xref
3818 linkend="libpq-notify">).
3823 <function>PQsendQuery</function>/<function>PQgetResult</function>
3824 can also attempt to cancel a command that is still being processed
3825 by the server; see <xref linkend="libpq-cancel">. But regardless of
3826 the return value of <function>PQcancel</function>, the application
3827 must continue with the normal result-reading sequence using
3828 <function>PQgetResult</function>. A successful cancellation will
3829 simply cause the command to terminate sooner than it would have
3834 By using the functions described above, it is possible to avoid
3835 blocking while waiting for input from the database server. However,
3836 it is still possible that the application will block waiting to send
3837 output to the server. This is relatively uncommon but can happen if
3838 very long SQL commands or data values are sent. (It is much more
3839 probable if the application sends data via <command>COPY IN</command>,
3840 however.) To prevent this possibility and achieve completely
3841 nonblocking database operation, the following additional functions
3847 <function>PQsetnonblocking</function>
3849 <primary>PQsetnonblocking</primary>
3855 Sets the nonblocking status of the connection.
3857 int PQsetnonblocking(PGconn *conn, int arg);
3862 Sets the state of the connection to nonblocking if
3863 <parameter>arg</parameter> is 1, or blocking if
3864 <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
3868 In the nonblocking state, calls to
3869 <function>PQsendQuery</function>, <function>PQputline</function>,
3870 <function>PQputnbytes</function>, and
3871 <function>PQendcopy</function> will not block but instead return
3872 an error if they need to be called again.
3876 Note that <function>PQexec</function> does not honor nonblocking
3877 mode; if it is called, it will act in blocking fashion anyway.
3884 <function>PQisnonblocking</function>
3886 <primary>PQisnonblocking</primary>
3892 Returns the blocking status of the database connection.
3894 int PQisnonblocking(const PGconn *conn);
3899 Returns 1 if the connection is set to nonblocking mode and 0 if
3907 <function>PQflush</function>
3909 <primary>PQflush</primary>
3915 Attempts to flush any queued output data to the server. Returns
3916 0 if successful (or if the send queue is empty), -1 if it failed
3917 for some reason, or 1 if it was unable to send all the data in
3918 the send queue yet (this case can only occur if the connection
3921 int PQflush(PGconn *conn);
3930 After sending any command or data on a nonblocking connection, call
3931 <function>PQflush</function>. If it returns 1, wait for the socket
3932 to be write-ready and call it again; repeat until it returns 0. Once
3933 <function>PQflush</function> returns 0, wait for the socket to be
3934 read-ready and then read the response as described above.
3939 <sect1 id="libpq-cancel">
3940 <title>Cancelling Queries in Progress</title>
3942 <indexterm zone="libpq-cancel">
3943 <primary>canceling</primary>
3944 <secondary>SQL command</secondary>
3948 A client application can request cancellation of a command that is
3949 still being processed by the server, using the functions described in
3955 <function>PQgetCancel</function>
3957 <primary>PQgetCancel</primary>
3963 Creates a data structure containing the information needed to cancel
3964 a command issued through a particular database connection.
3966 PGcancel *PQgetCancel(PGconn *conn);
3971 <function>PQgetCancel</function> creates a
3972 <structname>PGcancel</><indexterm><primary>PGcancel</></> object
3973 given a <structname>PGconn</> connection object. It will return
3974 NULL if the given <parameter>conn</> is NULL or an invalid
3975 connection. The <structname>PGcancel</> object is an opaque
3976 structure that is not meant to be accessed directly by the
3977 application; it can only be passed to <function>PQcancel</function>
3978 or <function>PQfreeCancel</function>.
3985 <function>PQfreeCancel</function>
3987 <primary>PQfreeCancel</primary>
3993 Frees a data structure created by <function>PQgetCancel</function>.
3995 void PQfreeCancel(PGcancel *cancel);
4000 <function>PQfreeCancel</function> frees a data object previously created
4001 by <function>PQgetCancel</function>.
4008 <function>PQcancel</function>
4010 <primary>PQcancel</primary>
4016 Requests that the server abandon processing of the current command.
4018 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
4023 The return value is 1 if the cancel request was successfully
4024 dispatched and 0 if not. If not, <parameter>errbuf</> is filled
4025 with an error message explaining why not. <parameter>errbuf</>
4026 must be a char array of size <parameter>errbufsize</> (the
4027 recommended size is 256 bytes).
4031 Successful dispatch is no guarantee that the request will have
4032 any effect, however. If the cancellation is effective, the current
4033 command will terminate early and return an error result. If the
4034 cancellation fails (say, because the server was already done
4035 processing the command), then there will be no visible result at
4040 <function>PQcancel</function> can safely be invoked from a signal
4041 handler, if the <parameter>errbuf</> is a local variable in the
4042 signal handler. The <structname>PGcancel</> object is read-only
4043 as far as <function>PQcancel</function> is concerned, so it can
4044 also be invoked from a thread that is separate from the one
4045 manipulating the <structname>PGconn</> object.
4054 <function>PQrequestCancel</function>
4056 <primary>PQrequestCancel</primary>
4062 Requests that the server abandon processing of the current
4065 int PQrequestCancel(PGconn *conn);
4070 <function>PQrequestCancel</function> is a deprecated variant of
4071 <function>PQcancel</function>. It operates directly on the
4072 <structname>PGconn</> object, and in case of failure stores the
4073 error message in the <structname>PGconn</> object (whence it can
4074 be retrieved by <function>PQerrorMessage</function>). Although
4075 the functionality is the same, this approach creates hazards for
4076 multiple-thread programs and signal handlers, since it is possible
4077 that overwriting the <structname>PGconn</>'s error message will
4078 mess up the operation currently in progress on the connection.
4087 <sect1 id="libpq-fastpath">
4088 <title>The Fast-Path Interface</title>
4090 <indexterm zone="libpq-fastpath">
4091 <primary>fast path</primary>
4095 <productname>PostgreSQL</productname> provides a fast-path interface
4096 to send simple function calls to the server.
4101 This interface is somewhat obsolete, as one can achieve similar
4102 performance and greater functionality by setting up a prepared
4103 statement to define the function call. Then, executing the statement
4104 with binary transmission of parameters and results substitutes for a
4105 fast-path function call.
4110 The function <function>PQfn</function><indexterm><primary>PQfn</></>
4111 requests execution of a server function via the fast-path interface:
4113 PGresult *PQfn(PGconn *conn,
4118 const PQArgBlock *args,
4133 The <parameter>fnid</> argument is the OID of the function to be
4134 executed. <parameter>args</> and <parameter>nargs</> define the
4135 parameters to be passed to the function; they must match the declared
4136 function argument list. When the <parameter>isint</> field of a
4137 parameter structure is true, the <parameter>u.integer</> value is sent
4138 to the server as an integer of the indicated length (this must be 1,
4139 2, or 4 bytes); proper byte-swapping occurs. When <parameter>isint</>
4140 is false, the indicated number of bytes at <parameter>*u.ptr</> are
4141 sent with no processing; the data must be in the format expected by
4142 the server for binary transmission of the function's argument data
4143 type. <parameter>result_buf</parameter> is the buffer in which to
4144 place the return value. The caller must have allocated sufficient
4145 space to store the return value. (There is no check!) The actual result
4146 length will be returned in the integer pointed to by
4147 <parameter>result_len</parameter>. If a 1, 2, or 4-byte integer result
4148 is expected, set <parameter>result_is_int</parameter> to 1, otherwise
4149 set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
4150 <application>libpq</> to byte-swap the value if necessary, so that it
4151 is delivered as a proper <type>int</type> value for the client machine.
4152 When <parameter>result_is_int</> is 0, the binary-format byte string
4153 sent by the server is returned unmodified.
4157 <function>PQfn</function> always returns a valid
4158 <structname>PGresult</structname> pointer. The result status should be
4159 checked before the result is used. The caller is responsible for
4160 freeing the <structname>PGresult</structname> with
4161 <function>PQclear</function> when it is no longer needed.
4165 Note that it is not possible to handle null arguments, null results,
4166 nor set-valued results when using this interface.
4171 <sect1 id="libpq-notify">
4172 <title>Asynchronous Notification</title>
4174 <indexterm zone="libpq-notify">
4175 <primary>NOTIFY</primary>
4176 <secondary>in libpq</secondary>
4180 <productname>PostgreSQL</productname> offers asynchronous notification
4181 via the <command>LISTEN</command> and <command>NOTIFY</command>
4182 commands. A client session registers its interest in a particular
4183 notification channel with the <command>LISTEN</command> command (and
4184 can stop listening with the <command>UNLISTEN</command> command). All
4185 sessions listening on a particular channel will be notified
4186 asynchronously when a <command>NOTIFY</command> command with that
4187 channel name is executed by any session. A <quote>payload</> string can
4188 be passed to communicate additional data to the listeners.
4192 <application>libpq</application> applications submit
4193 <command>LISTEN</command>, <command>UNLISTEN</command>,
4194 and <command>NOTIFY</command> commands as
4195 ordinary SQL commands. The arrival of <command>NOTIFY</command>
4196 messages can subsequently be detected by calling
4197 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
4201 The function <function>PQnotifies</function> returns the next notification
4202 from a list of unhandled notification messages received from the server.
4203 It returns a null pointer if there are no pending notifications. Once a
4204 notification is returned from <function>PQnotifies</>, it is considered
4205 handled and will be removed from the list of notifications.
4208 PGnotify *PQnotifies(PGconn *conn);
4210 typedef struct pgNotify {
4211 char *relname; /* notification channel name */
4212 int be_pid; /* process ID of notifying server process */
4213 char *extra; /* notification payload string */
4217 After processing a <structname>PGnotify</structname> object returned
4218 by <function>PQnotifies</function>, be sure to free it with
4219 <function>PQfreemem</function>. It is sufficient to free the
4220 <structname>PGnotify</structname> pointer; the
4221 <structfield>relname</structfield> and <structfield>extra</structfield>
4222 fields do not represent separate allocations. (The names of these fields
4223 are historical; in particular, channel names need not have anything to
4224 do with relation names.)
4228 <xref linkend="libpq-example-2"> gives a sample program that illustrates
4229 the use of asynchronous notification.
4233 <function>PQnotifies</function> does not actually read data from the
4234 server; it just returns messages previously absorbed by another
4235 <application>libpq</application> function. In prior releases of
4236 <application>libpq</application>, the only way to ensure timely receipt
4237 of <command>NOTIFY</> messages was to constantly submit commands, even
4238 empty ones, and then check <function>PQnotifies</function> after each
4239 <function>PQexec</function>. While this still works, it is deprecated
4240 as a waste of processing power.
4244 A better way to check for <command>NOTIFY</> messages when you have no
4245 useful commands to execute is to call
4246 <function>PQconsumeInput</function>, then check
4247 <function>PQnotifies</function>. You can use
4248 <function>select()</function> to wait for data to arrive from the
4249 server, thereby using no <acronym>CPU</acronym> power unless there is
4250 something to do. (See <function>PQsocket</function> to obtain the file
4251 descriptor number to use with <function>select()</function>.) Note that
4252 this will work OK whether you submit commands with
4253 <function>PQsendQuery</function>/<function>PQgetResult</function> or
4254 simply use <function>PQexec</function>. You should, however, remember
4255 to check <function>PQnotifies</function> after each
4256 <function>PQgetResult</function> or <function>PQexec</function>, to
4257 see if any notifications came in during the processing of the command.
4262 <sect1 id="libpq-copy">
4263 <title>Functions Associated with the <command>COPY</command> Command</title>
4265 <indexterm zone="libpq-copy">
4266 <primary>COPY</primary>
4267 <secondary>with libpq</secondary>
4271 The <command>COPY</command> command in
4272 <productname>PostgreSQL</productname> has options to read from or write
4273 to the network connection used by <application>libpq</application>.
4274 The functions described in this section allow applications to take
4275 advantage of this capability by supplying or consuming copied data.
4279 The overall process is that the application first issues the SQL
4280 <command>COPY</command> command via <function>PQexec</function> or one
4281 of the equivalent functions. The response to this (if there is no
4282 error in the command) will be a <structname>PGresult</> object bearing
4283 a status code of <literal>PGRES_COPY_OUT</literal> or
4284 <literal>PGRES_COPY_IN</literal> (depending on the specified copy
4285 direction). The application should then use the functions of this
4286 section to receive or transmit data rows. When the data transfer is
4287 complete, another <structname>PGresult</> object is returned to indicate
4288 success or failure of the transfer. Its status will be
4289 <literal>PGRES_COMMAND_OK</literal> for success or
4290 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
4291 At this point further SQL commands can be issued via
4292 <function>PQexec</function>. (It is not possible to execute other SQL
4293 commands using the same connection while the <command>COPY</command>
4294 operation is in progress.)
4298 If a <command>COPY</command> command is issued via
4299 <function>PQexec</function> in a string that could contain additional
4300 commands, the application must continue fetching results via
4301 <function>PQgetResult</> after completing the <command>COPY</command>
4302 sequence. Only when <function>PQgetResult</> returns
4303 <symbol>NULL</symbol> is it certain that the <function>PQexec</function>
4304 command string is done and it is safe to issue more commands.
4308 The functions of this section should be executed only after obtaining
4309 a result status of <literal>PGRES_COPY_OUT</literal> or
4310 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
4311 <function>PQgetResult</function>.
4315 A <structname>PGresult</> object bearing one of these status values
4316 carries some additional data about the <command>COPY</command> operation
4317 that is starting. This additional data is available using functions
4318 that are also used in connection with query results:
4323 <function>PQnfields</function>
4325 <primary>PQnfields</primary>
4326 <secondary>with COPY</secondary>
4332 Returns the number of columns (fields) to be copied.
4339 <function>PQbinaryTuples</function>
4341 <primary>PQbinaryTuples</primary>
4342 <secondary>with COPY</secondary>
4348 0 indicates the overall copy format is textual (rows separated by
4349 newlines, columns separated by separator characters, etc). 1
4350 indicates the overall copy format is binary. See <xref
4351 linkend="sql-copy"> for more information.
4358 <function>PQfformat</function>
4360 <primary>PQfformat</primary>
4361 <secondary>with COPY</secondary>
4367 Returns the format code (0 for text, 1 for binary) associated with
4368 each column of the copy operation. The per-column format codes
4369 will always be zero when the overall copy format is textual, but
4370 the binary format can support both text and binary columns.
4371 (However, as of the current implementation of <command>COPY</>,
4372 only binary columns appear in a binary copy; so the per-column
4373 formats always match the overall format at present.)
4382 These additional data values are only available when using protocol
4383 3.0. When using protocol 2.0, all these functions will return 0.
4387 <sect2 id="libpq-copy-send">
4388 <title>Functions for Sending <command>COPY</command> Data</title>
4391 These functions are used to send data during <literal>COPY FROM
4392 STDIN</>. They will fail if called when the connection is not in
4393 <literal>COPY_IN</> state.
4399 <function>PQputCopyData</function>
4401 <primary>PQputCopyData</primary>
4407 Sends data to the server during <literal>COPY_IN</> state.
4409 int PQputCopyData(PGconn *conn,
4416 Transmits the <command>COPY</command> data in the specified
4417 <parameter>buffer</>, of length <parameter>nbytes</>, to the server.
4418 The result is 1 if the data was sent, zero if it was not sent
4419 because the attempt would block (this case is only possible if the
4420 connection is in nonblocking mode), or -1 if an error occurred.
4421 (Use <function>PQerrorMessage</function> to retrieve details if
4422 the return value is -1. If the value is zero, wait for write-ready
4427 The application can divide the <command>COPY</command> data stream
4428 into buffer loads of any convenient size. Buffer-load boundaries
4429 have no semantic significance when sending. The contents of the
4430 data stream must match the data format expected by the
4431 <command>COPY</> command; see <xref linkend="sql-copy"> for details.
4438 <function>PQputCopyEnd</function>
4440 <primary>PQputCopyEnd</primary>
4446 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
4448 int PQputCopyEnd(PGconn *conn,
4449 const char *errormsg);
4454 Ends the <literal>COPY_IN</> operation successfully if
4455 <parameter>errormsg</> is <symbol>NULL</symbol>. If
4456 <parameter>errormsg</> is not <symbol>NULL</symbol> then the
4457 <command>COPY</> is forced to fail, with the string pointed to by
4458 <parameter>errormsg</> used as the error message. (One should not
4459 assume that this exact error message will come back from the server,
4460 however, as the server might have already failed the
4461 <command>COPY</> for its own reasons. Also note that the option
4462 to force failure does not work when using pre-3.0-protocol
4467 The result is 1 if the termination data was sent, zero if it was
4468 not sent because the attempt would block (this case is only possible
4469 if the connection is in nonblocking mode), or -1 if an error
4470 occurred. (Use <function>PQerrorMessage</function> to retrieve
4471 details if the return value is -1. If the value is zero, wait for
4472 write-ready and try again.)
4476 After successfully calling <function>PQputCopyEnd</>, call
4477 <function>PQgetResult</> to obtain the final result status of the
4478 <command>COPY</> command. One can wait for this result to be
4479 available in the usual way. Then return to normal operation.
4487 <sect2 id="libpq-copy-receive">
4488 <title>Functions for Receiving <command>COPY</command> Data</title>
4491 These functions are used to receive data during <literal>COPY TO
4492 STDOUT</>. They will fail if called when the connection is not in
4493 <literal>COPY_OUT</> state.
4499 <function>PQgetCopyData</function>
4501 <primary>PQgetCopyData</primary>
4507 Receives data from the server during <literal>COPY_OUT</> state.
4509 int PQgetCopyData(PGconn *conn,
4516 Attempts to obtain another row of data from the server during a
4517 <command>COPY</command>. Data is always returned one data row at
4518 a time; if only a partial row is available, it is not returned.
4519 Successful return of a data row involves allocating a chunk of
4520 memory to hold the data. The <parameter>buffer</> parameter must
4521 be non-<symbol>NULL</symbol>. <parameter>*buffer</> is set to
4522 point to the allocated memory, or to <symbol>NULL</symbol> in cases
4523 where no buffer is returned. A non-<symbol>NULL</symbol> result
4524 buffer should be freed using <function>PQfreemem</> when no longer
4529 When a row is successfully returned, the return value is the number
4530 of data bytes in the row (this will always be greater than zero).
4531 The returned string is always null-terminated, though this is
4532 probably only useful for textual <command>COPY</command>. A result
4533 of zero indicates that the <command>COPY</command> is still in
4534 progress, but no row is yet available (this is only possible when
4535 <parameter>async</> is true). A result of -1 indicates that the
4536 <command>COPY</command> is done. A result of -2 indicates that an
4537 error occurred (consult <function>PQerrorMessage</> for the reason).
4541 When <parameter>async</> is true (not zero),
4542 <function>PQgetCopyData</> will not block waiting for input; it
4543 will return zero if the <command>COPY</command> is still in progress
4544 but no complete row is available. (In this case wait for read-ready
4545 and then call <function>PQconsumeInput</> before calling
4546 <function>PQgetCopyData</> again.) When <parameter>async</> is
4547 false (zero), <function>PQgetCopyData</> will block until data is
4548 available or the operation completes.
4552 After <function>PQgetCopyData</> returns -1, call
4553 <function>PQgetResult</> to obtain the final result status of the
4554 <command>COPY</> command. One can wait for this result to be
4555 available in the usual way. Then return to normal operation.
4563 <sect2 id="libpq-copy-deprecated">
4564 <title>Obsolete Functions for <command>COPY</command></title>
4567 These functions represent older methods of handling <command>COPY</>.
4568 Although they still work, they are deprecated due to poor error handling,
4569 inconvenient methods of detecting end-of-data, and lack of support for binary
4570 or nonblocking transfers.
4576 <function>PQgetline</function>
4578 <primary>PQgetline</primary>
4584 Reads a newline-terminated line of characters (transmitted
4585 by the server) into a buffer string of size <parameter>length</>.
4587 int PQgetline(PGconn *conn,
4594 This function copies up to <parameter>length</>-1 characters into
4595 the buffer and converts the terminating newline into a zero byte.
4596 <function>PQgetline</function> returns <symbol>EOF</symbol> at the
4597 end of input, 0 if the entire line has been read, and 1 if the
4598 buffer is full but the terminating newline has not yet been read.
4601 Note that the application must check to see if a new line consists
4602 of the two characters <literal>\.</literal>, which indicates
4603 that the server has finished sending the results of the
4604 <command>COPY</command> command. If the application might receive
4605 lines that are more than <parameter>length</>-1 characters long,
4606 care is needed to be sure it recognizes the <literal>\.</literal>
4607 line correctly (and does not, for example, mistake the end of a
4608 long data line for a terminator line).
4615 <function>PQgetlineAsync</function>
4617 <primary>PQgetlineAsync</primary>
4623 Reads a row of <command>COPY</command> data (transmitted by the
4624 server) into a buffer without blocking.
4626 int PQgetlineAsync(PGconn *conn,
4633 This function is similar to <function>PQgetline</function>, but it can be used
4635 that must read <command>COPY</command> data asynchronously, that is, without blocking.
4636 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
4638 application should call <function>PQconsumeInput</function> and
4639 <function>PQgetlineAsync</function> until the
4640 end-of-data signal is detected.
4643 Unlike <function>PQgetline</function>, this function takes
4644 responsibility for detecting end-of-data.
4648 On each call, <function>PQgetlineAsync</function> will return data if a
4649 complete data row is available in <application>libpq</>'s input buffer.
4650 Otherwise, no data is returned until the rest of the row arrives.
4651 The function returns -1 if the end-of-copy-data marker has been recognized,
4652 or 0 if no data is available, or a positive number giving the number of
4653 bytes of data returned. If -1 is returned, the caller must next call
4654 <function>PQendcopy</function>, and then return to normal processing.
4658 The data returned will not extend beyond a data-row boundary. If possible
4659 a whole row will be returned at one time. But if the buffer offered by
4660 the caller is too small to hold a row sent by the server, then a partial
4661 data row will be returned. With textual data this can be detected by testing
4662 whether the last returned byte is <literal>\n</literal> or not. (In a binary
4663 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
4664 equivalent determination.)
4665 The returned string is not null-terminated. (If you want to add a
4666 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
4667 than the room actually available.)
4674 <function>PQputline</function>
4676 <primary>PQputline</primary>
4682 Sends a null-terminated string to the server. Returns 0 if
4683 OK and <symbol>EOF</symbol> if unable to send the string.
4685 int PQputline(PGconn *conn,
4686 const char *string);
4691 The <command>COPY</command> data stream sent by a series of calls
4692 to <function>PQputline</function> has the same format as that
4693 returned by <function>PQgetlineAsync</function>, except that
4694 applications are not obliged to send exactly one data row per
4695 <function>PQputline</function> call; it is okay to send a partial
4696 line or multiple lines per call.
4701 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
4702 for the application to explicitly send the two characters
4703 <literal>\.</literal> as a final line to indicate to the server that it had
4704 finished sending <command>COPY</> data. While this still works, it is deprecated and the
4705 special meaning of <literal>\.</literal> can be expected to be removed in a
4706 future release. It is sufficient to call <function>PQendcopy</function> after
4707 having sent the actual data.
4715 <function>PQputnbytes</function>
4717 <primary>PQputnbytes</primary>
4723 Sends a non-null-terminated string to the server. Returns
4724 0 if OK and <symbol>EOF</symbol> if unable to send the string.
4726 int PQputnbytes(PGconn *conn,
4733 This is exactly like <function>PQputline</function>, except that the data
4734 buffer need not be null-terminated since the number of bytes to send is
4735 specified directly. Use this procedure when sending binary data.
4742 <function>PQendcopy</function>
4744 <primary>PQendcopy</primary>
4750 Synchronizes with the server.
4752 int PQendcopy(PGconn *conn);
4754 This function waits until the server has finished the copying.
4755 It should either be issued when the last string has been sent
4756 to the server using <function>PQputline</function> or when the
4757 last string has been received from the server using
4758 <function>PGgetline</function>. It must be issued or the server
4759 will get <quote>out of sync</quote> with the client. Upon return
4760 from this function, the server is ready to receive the next SQL
4761 command. The return value is 0 on successful completion,
4762 nonzero otherwise. (Use <function>PQerrorMessage</function> to
4763 retrieve details if the return value is nonzero.)
4767 When using <function>PQgetResult</function>, the application should
4768 respond to a <literal>PGRES_COPY_OUT</literal> result by executing
4769 <function>PQgetline</function> repeatedly, followed by
4770 <function>PQendcopy</function> after the terminator line is seen.
4771 It should then return to the <function>PQgetResult</function> loop
4772 until <function>PQgetResult</function> returns a null pointer.
4773 Similarly a <literal>PGRES_COPY_IN</literal> result is processed
4774 by a series of <function>PQputline</function> calls followed by
4775 <function>PQendcopy</function>, then return to the
4776 <function>PQgetResult</function> loop. This arrangement will
4777 ensure that a <command>COPY</command> command embedded in a series
4778 of <acronym>SQL</acronym> commands will be executed correctly.
4782 Older applications are likely to submit a <command>COPY</command>
4783 via <function>PQexec</function> and assume that the transaction
4784 is done after <function>PQendcopy</function>. This will work
4785 correctly only if the <command>COPY</command> is the only
4786 <acronym>SQL</acronym> command in the command string.
4796 <sect1 id="libpq-control">
4797 <title>Control Functions</title>
4800 These functions control miscellaneous details of <application>libpq</>'s
4807 <function>PQclientEncoding</function>
4809 <primary>PQclientEncoding</primary>
4815 Returns the client encoding.
4817 int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
4820 Note that it returns the encoding ID, not a symbolic string
4821 such as <literal>EUC_JP</literal>. To convert an encoding ID to an encoding name, you
4825 char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
4833 <function>PQsetClientEncoding</function>
4835 <primary>PQsetClientEncoding</primary>
4841 Sets the client encoding.
4843 int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
4846 <replaceable>conn</replaceable> is a connection to the server,
4847 and <replaceable>encoding</replaceable> is the encoding you want to
4848 use. If the function successfully sets the encoding, it returns 0,
4849 otherwise -1. The current encoding for this connection can be
4850 determined by using <function>PQclientEncoding</>.
4857 <function>PQsetErrorVerbosity</function>
4859 <primary>PQsetErrorVerbosity</primary>
4865 Determines the verbosity of messages returned by
4866 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
4874 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
4877 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
4878 the connection's previous setting. In <firstterm>TERSE</> mode,
4879 returned messages include severity, primary text, and position only;
4880 this will normally fit on a single line. The default mode produces
4881 messages that include the above plus any detail, hint, or context
4882 fields (these might span multiple lines). The <firstterm>VERBOSE</>
4883 mode includes all available fields. Changing the verbosity does not
4884 affect the messages available from already-existing
4885 <structname>PGresult</> objects, only subsequently-created ones.
4892 <function>PQtrace</function>
4894 <primary>PQtrace</primary>
4900 Enables tracing of the client/server communication to a debugging file stream.
4902 void PQtrace(PGconn *conn, FILE *stream);
4908 On Windows, if the <application>libpq</> library and an application are
4909 compiled with different flags, this function call will crash the
4910 application because the internal representation of the <literal>FILE</>
4911 pointers differ. Specifically, multithreaded/single-threaded,
4912 release/debug, and static/dynamic flags should be the same for the
4913 library and all applications using that library.
4922 <function>PQuntrace</function>
4924 <primary>PQuntrace</primary>
4930 Disables tracing started by <function>PQtrace</function>.
4932 void PQuntrace(PGconn *conn);
4941 <sect1 id="libpq-misc">
4942 <title>Miscellaneous Functions</title>
4945 As always, there are some functions that just don't fit anywhere.
4951 <function>PQfreemem</function>
4953 <primary>PQfreemem</primary>
4959 Frees memory allocated by <application>libpq</>.
4961 void PQfreemem(void *ptr);
4966 Frees memory allocated by <application>libpq</>, particularly
4967 <function>PQescapeByteaConn</function>,
4968 <function>PQescapeBytea</function>,
4969 <function>PQunescapeBytea</function>,
4970 and <function>PQnotifies</function>.
4971 It is particularly important that this function, rather than
4972 <function>free()</>, be used on Microsoft Windows. This is because
4973 allocating memory in a DLL and releasing it in the application works
4974 only if multithreaded/single-threaded, release/debug, and static/dynamic
4975 flags are the same for the DLL and the application. On non-Microsoft
4976 Windows platforms, this function is the same as the standard library
4977 function <function>free()</>.
4984 <function>PQconninfoFree</function>
4986 <primary>PQconninfoFree</primary>
4992 Frees the data structures allocated by
4993 <function>PQconndefaults</> or <function>PQconninfoParse</>.
4995 void PQconninfoFree(PQconninfoOption *connOptions);
5000 A simple <function>PQfreemem</function> will not do for this, since
5001 the array contains references to subsidiary strings.
5008 <function>PQencryptPassword</function>
5010 <primary>PQencryptPassword</primary>
5016 Prepares the encrypted form of a <productname>PostgreSQL</> password.
5018 char * PQencryptPassword(const char *passwd, const char *user);
5020 This function is intended to be used by client applications that
5021 wish to send commands like <literal>ALTER USER joe PASSWORD
5022 'pwd'</>. It is good practice not to send the original cleartext
5023 password in such a command, because it might be exposed in command
5024 logs, activity displays, and so on. Instead, use this function to
5025 convert the password to encrypted form before it is sent. The
5026 arguments are the cleartext password, and the SQL name of the user
5027 it is for. The return value is a string allocated by
5028 <function>malloc</function>, or <symbol>NULL</symbol> if out of
5029 memory. The caller can assume the string doesn't contain any
5030 special characters that would require escaping. Use
5031 <function>PQfreemem</> to free the result when done with it.
5038 <function>PQmakeEmptyPGresult</function>
5040 <primary>PQmakeEmptyPGresult</primary>
5046 Constructs an empty <structname>PGresult</structname> object with the given status.
5048 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
5053 This is <application>libpq</>'s internal function to allocate and
5054 initialize an empty <structname>PGresult</structname> object. This
5055 function returns NULL if memory could not be allocated. It is
5056 exported because some applications find it useful to generate result
5057 objects (particularly objects with error status) themselves. If
5058 <parameter>conn</parameter> is not null and <parameter>status</>
5059 indicates an error, the current error message of the specified
5060 connection is copied into the <structname>PGresult</structname>.
5061 Also, if <parameter>conn</parameter> is not null, any event procedures
5062 registered in the connection are copied into the
5063 <structname>PGresult</structname>. (They do not get
5064 <literal>PGEVT_RESULTCREATE</> calls, but see
5065 <function>PQfireResultCreateEvents</function>.)
5066 Note that <function>PQclear</function> should eventually be called
5067 on the object, just as with a <structname>PGresult</structname>
5068 returned by <application>libpq</application> itself.
5075 <function>PQfireResultCreateEvents</function>
5077 <primary>PQfireResultCreateEvents</primary>
5082 Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
5083 linkend="libpq-events">) for each event procedure registered in the
5084 <structname>PGresult</structname> object. Returns non-zero for success,
5085 zero if any event procedure fails.
5088 int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
5093 The <literal>conn</> argument is passed through to event procedures
5094 but not used directly. It can be <literal>NULL</> if the event
5095 procedures won't use it.
5099 Event procedures that have already received a
5100 <literal>PGEVT_RESULTCREATE</> or <literal>PGEVT_RESULTCOPY</> event
5101 for this object are not fired again.
5105 The main reason that this function is separate from
5106 <function>PQmakeEmptyPGResult</function> is that it is often appropriate
5107 to create a <structname>PGresult</structname> and fill it with data
5108 before invoking the event procedures.
5115 <function>PQcopyResult</function>
5117 <primary>PQcopyResult</primary>
5123 Makes a copy of a <structname>PGresult</structname> object. The copy is
5124 not linked to the source result in any way and
5125 <function>PQclear</function> must be called when the copy is no longer
5126 needed. If the function fails, NULL is returned.
5129 PGresult *PQcopyResult(const PGresult *src, int flags);
5134 This is not intended to make an exact copy. The returned result is
5135 always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
5136 copy any error message in the source. (It does copy the command status
5137 string, however.) The <parameter>flags</parameter> argument determines
5138 what else is copied. It is a bitwise OR of several flags.
5139 <literal>PG_COPYRES_ATTRS</literal> specifies copying the source
5140 result's attributes (column definitions).
5141 <literal>PG_COPYRES_TUPLES</literal> specifies copying the source
5142 result's tuples. (This implies copying the attributes, too.)
5143 <literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
5144 copying the source result's notify hooks.
5145 <literal>PG_COPYRES_EVENTS</literal> specifies copying the source
5146 result's events. (But any instance data associated with the source
5154 <function>PQsetResultAttrs</function>
5156 <primary>PQsetResultAttrs</primary>
5162 Sets the attributes of a <structname>PGresult</structname> object.
5164 int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
5169 The provided <parameter>attDescs</parameter> are copied into the result.
5170 If the <parameter>attDescs</parameter> pointer is NULL or
5171 <parameter>numAttributes</parameter> is less than one, the request is
5172 ignored and the function succeeds. If <parameter>res</parameter>
5173 already contains attributes, the function will fail. If the function
5174 fails, the return value is zero. If the function succeeds, the return
5182 <function>PQsetvalue</function>
5184 <primary>PQsetvalue</primary>
5190 Sets a tuple field value of a <structname>PGresult</structname> object.
5192 int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
5197 The function will automatically grow the result's internal tuples array
5198 as needed. However, the <parameter>tup_num</parameter> argument must be
5199 less than or equal to <function>PQntuples</function>, meaning this
5200 function can only grow the tuples array one tuple at a time. But any
5201 field of any existing tuple can be modified in any order. If a value at
5202 <parameter>field_num</parameter> already exists, it will be overwritten.
5203 If <parameter>len</parameter> is <literal>-1</literal> or
5204 <parameter>value</parameter> is <literal>NULL</literal>, the field value
5205 will be set to an SQL <literal>NULL</literal>. The
5206 <parameter>value</parameter> is copied into the result's private storage,
5207 thus is no longer needed after the function
5208 returns. If the function fails, the return value is zero. If the
5209 function succeeds, the return value is non-zero.
5216 <function>PQresultAlloc</function>
5218 <primary>PQresultAlloc</primary>
5224 Allocate subsidiary storage for a <structname>PGresult</structname> object.
5226 void *PQresultAlloc(PGresult *res, size_t nBytes);
5231 Any memory allocated with this function will be freed when
5232 <parameter>res</parameter> is cleared. If the function fails,
5233 the return value is <literal>NULL</literal>. The result is
5234 guaranteed to be adequately aligned for any type of data,
5235 just as for <function>malloc</>.
5244 <sect1 id="libpq-notice-processing">
5245 <title>Notice Processing</title>
5247 <indexterm zone="libpq-notice-processing">
5248 <primary>notice processing</primary>
5249 <secondary>in libpq</secondary>
5253 Notice and warning messages generated by the server are not returned
5254 by the query execution functions, since they do not imply failure of
5255 the query. Instead they are passed to a notice handling function, and
5256 execution continues normally after the handler returns. The default
5257 notice handling function prints the message on
5258 <filename>stderr</filename>, but the application can override this
5259 behavior by supplying its own handling function.
5263 For historical reasons, there are two levels of notice handling, called
5264 the notice receiver and notice processor. The default behavior is for
5265 the notice receiver to format the notice and pass a string to the notice
5266 processor for printing. However, an application that chooses to provide
5267 its own notice receiver will typically ignore the notice processor
5268 layer and just do all the work in the notice receiver.
5272 The function <function>PQsetNoticeReceiver</function>
5273 <indexterm><primary>notice
5274 receiver</></><indexterm><primary>PQsetNoticeReceiver</></> sets or
5275 examines the current notice receiver for a connection object.
5276 Similarly, <function>PQsetNoticeProcessor</function>
5277 <indexterm><primary>notice
5278 processor</></><indexterm><primary>PQsetNoticeProcessor</></> sets or
5279 examines the current notice processor.
5282 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
5285 PQsetNoticeReceiver(PGconn *conn,
5286 PQnoticeReceiver proc,
5289 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
5292 PQsetNoticeProcessor(PGconn *conn,
5293 PQnoticeProcessor proc,
5297 Each of these functions returns the previous notice receiver or
5298 processor function pointer, and sets the new value. If you supply a
5299 null function pointer, no action is taken, but the current pointer is
5304 When a notice or warning message is received from the server, or
5305 generated internally by <application>libpq</application>, the notice
5306 receiver function is called. It is passed the message in the form of
5307 a <symbol>PGRES_NONFATAL_ERROR</symbol>
5308 <structname>PGresult</structname>. (This allows the receiver to extract
5309 individual fields using <function>PQresultErrorField</>, or the complete
5310 preformatted message using <function>PQresultErrorMessage</>.) The same
5311 void pointer passed to <function>PQsetNoticeReceiver</function> is also
5312 passed. (This pointer can be used to access application-specific state
5317 The default notice receiver simply extracts the message (using
5318 <function>PQresultErrorMessage</>) and passes it to the notice
5323 The notice processor is responsible for handling a notice or warning
5324 message given in text form. It is passed the string text of the message
5325 (including a trailing newline), plus a void pointer that is the same
5326 one passed to <function>PQsetNoticeProcessor</function>. (This pointer
5327 can be used to access application-specific state if needed.)
5331 The default notice processor is simply:
5334 defaultNoticeProcessor(void *arg, const char *message)
5336 fprintf(stderr, "%s", message);
5342 Once you have set a notice receiver or processor, you should expect
5343 that that function could be called as long as either the
5344 <structname>PGconn</> object or <structname>PGresult</> objects made
5345 from it exist. At creation of a <structname>PGresult</>, the
5346 <structname>PGconn</>'s current notice handling pointers are copied
5347 into the <structname>PGresult</> for possible use by functions like
5348 <function>PQgetvalue</function>.
5353 <sect1 id="libpq-events">
5354 <title>Event System</title>
5357 <application>libpq</application>'s event system is designed to notify
5358 registered event handlers about interesting
5359 <application>libpq</application> events, such as the creation or
5360 destruction of <structname>PGconn</structname> and
5361 <structname>PGresult</structname> objects. A principal use case is that
5362 this allows applications to associate their own data with a
5363 <structname>PGconn</structname> or <structname>PGresult</structname>
5364 and ensure that that data is freed at an appropriate time.
5368 Each registered event handler is associated with two pieces of data,
5369 known to <application>libpq</application> only as opaque <literal>void *</>
5370 pointers. There is a <firstterm>passthrough</> pointer that is provided
5371 by the application when the event handler is registered with a
5372 <structname>PGconn</>. The passthrough pointer never changes for the
5373 life of the <structname>PGconn</> and all <structname>PGresult</>s
5374 generated from it; so if used, it must point to long-lived data.
5375 In addition there is an <firstterm>instance data</> pointer, which starts
5376 out NULL in every <structname>PGconn</> and <structname>PGresult</>.
5377 This pointer can be manipulated using the
5378 <function>PQinstanceData</function>,
5379 <function>PQsetInstanceData</function>,
5380 <function>PQresultInstanceData</function> and
5381 <function>PQsetResultInstanceData</function> functions. Note that
5382 unlike the passthrough pointer, instance data of a <structname>PGconn</>
5383 is not automatically inherited by <structname>PGresult</>s created from
5384 it. <application>libpq</application> does not know what passthrough
5385 and instance data pointers point to (if anything) and will never attempt
5386 to free them — that is the responsibility of the event handler.
5389 <sect2 id="libpq-events-types">
5390 <title>Event Types</title>
5393 The enum <literal>PGEventId</> names the types of events handled by
5394 the event system. All its values have names beginning with
5395 <literal>PGEVT</literal>. For each event type, there is a corresponding
5396 event info structure that carries the parameters passed to the event
5397 handlers. The event types are:
5402 <term><literal>PGEVT_REGISTER</literal></term>
5405 The register event occurs when <function>PQregisterEventProc</function>
5406 is called. It is the ideal time to initialize any
5407 <literal>instanceData</literal> an event procedure may need. Only one
5408 register event will be fired per event handler per connection. If the
5409 event procedure fails, the registration is aborted.
5418 When a <literal>PGEVT_REGISTER</literal> event is received, the
5419 <parameter>evtInfo</parameter> pointer should be cast to a
5420 <structname>PGEventRegister *</structname>. This structure contains a
5421 <structname>PGconn</structname> that should be in the
5422 <literal>CONNECTION_OK</literal> status; guaranteed if one calls
5423 <function>PQregisterEventProc</function> right after obtaining a good
5424 <structname>PGconn</structname>. When returning a failure code, all
5425 cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
5432 <term><literal>PGEVT_CONNRESET</literal></term>
5435 The connection reset event is fired on completion of
5436 <function>PQreset</function> or <function>PQresetPoll</function>. In
5437 both cases, the event is only fired if the reset was successful. If
5438 the event procedure fails, the entire connection reset will fail; the
5439 <structname>PGconn</structname> is put into
5440 <literal>CONNECTION_BAD</literal> status and
5441 <function>PQresetPoll</function> will return
5442 <literal>PGRES_POLLING_FAILED</literal>.
5451 When a <literal>PGEVT_CONNRESET</literal> event is received, the
5452 <parameter>evtInfo</parameter> pointer should be cast to a
5453 <structname>PGEventConnReset *</structname>. Although the contained
5454 <structname>PGconn</structname> was just reset, all event data remains
5455 unchanged. This event should be used to reset/reload/requery any
5456 associated <literal>instanceData</literal>. Note that even if the
5457 event procedure fails to process <literal>PGEVT_CONNRESET</>, it will
5458 still receive a <literal>PGEVT_CONNDESTROY</> event when the connection
5465 <term><literal>PGEVT_CONNDESTROY</literal></term>
5468 The connection destroy event is fired in response to
5469 <function>PQfinish</function>. It is the event procedure's
5470 responsibility to properly clean up its event data as libpq has no
5471 ability to manage this memory. Failure to clean up will lead
5478 } PGEventConnDestroy;
5481 When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
5482 <parameter>evtInfo</parameter> pointer should be cast to a
5483 <structname>PGEventConnDestroy *</structname>. This event is fired
5484 prior to <function>PQfinish</function> performing any other cleanup.
5485 The return value of the event procedure is ignored since there is no
5486 way of indicating a failure from <function>PQfinish</function>. Also,
5487 an event procedure failure should not abort the process of cleaning up
5494 <term><literal>PGEVT_RESULTCREATE</literal></term>
5497 The result creation event is fired in response to any query execution
5498 function that generates a result, including
5499 <function>PQgetResult</function>. This event will only be fired after
5500 the result has been created successfully.
5507 } PGEventResultCreate;
5510 When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
5511 <parameter>evtInfo</parameter> pointer should be cast to a
5512 <structname>PGEventResultCreate *</structname>. The
5513 <parameter>conn</parameter> is the connection used to generate the
5514 result. This is the ideal place to initialize any
5515 <literal>instanceData</literal> that needs to be associated with the
5516 result. If the event procedure fails, the result will be cleared and
5517 the failure will be propagated. The event procedure must not try to
5518 <function>PQclear</> the result object for itself. When returning a
5519 failure code, all cleanup must be performed as no
5520 <literal>PGEVT_RESULTDESTROY</literal> event will be sent.
5526 <term><literal>PGEVT_RESULTCOPY</literal></term>
5529 The result copy event is fired in response to
5530 <function>PQcopyResult</function>. This event will only be fired after
5531 the copy is complete. Only event procedures that have
5532 successfully handled the <literal>PGEVT_RESULTCREATE</literal>
5533 or <literal>PGEVT_RESULTCOPY</literal> event for the source result
5534 will receive <literal>PGEVT_RESULTCOPY</literal> events.
5539 const PGresult *src;
5541 } PGEventResultCopy;
5544 When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
5545 <parameter>evtInfo</parameter> pointer should be cast to a
5546 <structname>PGEventResultCopy *</structname>. The
5547 <parameter>src</parameter> result is what was copied while the
5548 <parameter>dest</parameter> result is the copy destination. This event
5549 can be used to provide a deep copy of <literal>instanceData</literal>,
5550 since <literal>PQcopyResult</literal> cannot do that. If the event
5551 procedure fails, the entire copy operation will fail and the
5552 <parameter>dest</parameter> result will be cleared. When returning a
5553 failure code, all cleanup must be performed as no
5554 <literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
5561 <term><literal>PGEVT_RESULTDESTROY</literal></term>
5564 The result destroy event is fired in response to a
5565 <function>PQclear</function>. It is the event procedure's
5566 responsibility to properly clean up its event data as libpq has no
5567 ability to manage this memory. Failure to clean up will lead
5574 } PGEventResultDestroy;
5577 When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
5578 <parameter>evtInfo</parameter> pointer should be cast to a
5579 <structname>PGEventResultDestroy *</structname>. This event is fired
5580 prior to <function>PQclear</function> performing any other cleanup.
5581 The return value of the event procedure is ignored since there is no
5582 way of indicating a failure from <function>PQclear</function>. Also,
5583 an event procedure failure should not abort the process of cleaning up
5591 <sect2 id="libpq-events-proc">
5592 <title>Event Callback Procedure</title>
5597 <literal>PGEventProc</literal>
5599 <primary>PGEventProc</primary>
5605 <literal>PGEventProc</literal> is a typedef for a pointer to an
5606 event procedure, that is, the user callback function that receives
5607 events from libpq. The signature of an event procedure must be
5610 int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
5613 The <parameter>evtId</parameter> parameter indicates which
5614 <literal>PGEVT</literal> event occurred. The
5615 <parameter>evtInfo</parameter> pointer must be cast to the appropriate
5616 structure type to obtain further information about the event.
5617 The <parameter>passThrough</parameter> parameter is the pointer
5618 provided to <function>PQregisterEventProc</function> when the event
5619 procedure was registered. The function should return a non-zero value
5620 if it succeeds and zero if it fails.
5624 A particular event procedure can be registered only once in any
5625 <structname>PGconn</>. This is because the address of the procedure
5626 is used as a lookup key to identify the associated instance data.
5631 On Windows, functions can have two different addresses: one visible
5632 from outside a DLL and another visible from inside the DLL. One
5633 should be careful that only one of these addresses is used with
5634 <application>libpq</>'s event-procedure functions, else confusion will
5635 result. The simplest rule for writing code that will work is to
5636 ensure that event procedures are declared <literal>static</>. If the
5637 procedure's address must be available outside its own source file,
5638 expose a separate function to return the address.
5646 <sect2 id="libpq-events-funcs">
5647 <title>Event Support Functions</title>
5652 <function>PQregisterEventProc</function>
5654 <primary>PQregisterEventProc</primary>
5660 Registers an event callback procedure with libpq.
5663 int PQregisterEventProc(PGconn *conn, PGEventProc proc,
5664 const char *name, void *passThrough);
5669 An event procedure must be registered once on each
5670 <structname>PGconn</> you want to receive events about. There is no
5671 limit, other than memory, on the number of event procedures that
5672 can be registered with a connection. The function returns a non-zero
5673 value if it succeeds and zero if it fails.
5677 The <parameter>proc</parameter> argument will be called when a libpq
5678 event is fired. Its memory address is also used to lookup
5679 <literal>instanceData</literal>. The <parameter>name</parameter>
5680 argument is used to refer to the event procedure in error messages.
5681 This value cannot be NULL or a zero-length string. The name string is
5682 copied into the <structname>PGconn</>, so what is passed need not be
5683 long-lived. The <parameter>passThrough</parameter> pointer is passed
5684 to the <parameter>proc</parameter> whenever an event occurs. This
5685 argument can be NULL.
5692 <function>PQsetInstanceData</function>
5694 <primary>PQsetInstanceData</primary>
5699 Sets the conn's instanceData for proc to data. This returns non-zero
5700 for success and zero for failure. (Failure is only possible if
5701 the proc has not been properly registered in the conn.)
5704 int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
5712 <function>PQinstanceData</function>
5714 <primary>PQinstanceData</primary>
5719 Returns the conn's instanceData associated with proc, or NULL
5723 void *PQinstanceData(const PGconn *conn, PGEventProc proc);
5731 <function>PQresultSetInstanceData</function>
5733 <primary>PQresultSetInstanceData</primary>
5738 Sets the result's instanceData for proc to data. This returns non-zero
5739 for success and zero for failure. (Failure is only possible if the
5740 proc has not been properly registered in the result.)
5743 int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
5751 <function>PQresultInstanceData</function>
5753 <primary>PQresultInstanceData</primary>
5758 Returns the result's instanceData associated with proc, or NULL
5762 void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
5770 <sect2 id="libpq-events-example">
5771 <title>Event Example</title>
5774 Here is a skeleton example of managing private data associated with
5775 libpq connections and results.
5780 /* required header for libpq events (note: includes libpq-fe.h) */
5781 #include <libpq-events.h>
5783 /* The instanceData */
5791 static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);
5798 PGconn *conn = PQconnectdb("dbname = postgres");
5800 if (PQstatus(conn) != CONNECTION_OK)
5802 fprintf(stderr, "Connection to database failed: %s",
5803 PQerrorMessage(conn));
5808 /* called once on any connection that should receive events.
5809 * Sends a PGEVT_REGISTER to myEventProc.
5811 if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
5813 fprintf(stderr, "Cannot register PGEventProc\n");
5818 /* conn instanceData is available */
5819 data = PQinstanceData(conn, myEventProc);
5821 /* Sends a PGEVT_RESULTCREATE to myEventProc */
5822 res = PQexec(conn, "SELECT 1 + 1");
5824 /* result instanceData is available */
5825 data = PQresultInstanceData(res, myEventProc);
5827 /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
5828 res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);
5830 /* result instanceData is available if PG_COPYRES_EVENTS was
5831 * used during the PQcopyResult call.
5833 data = PQresultInstanceData(res_copy, myEventProc);
5835 /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
5839 /* Sends a PGEVT_CONNDESTROY to myEventProc */
5846 myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
5850 case PGEVT_REGISTER:
5852 PGEventRegister *e = (PGEventRegister *)evtInfo;
5853 mydata *data = get_mydata(e->conn);
5855 /* associate app specific data with connection */
5856 PQsetInstanceData(e->conn, myEventProc, data);
5860 case PGEVT_CONNRESET:
5862 PGEventConnReset *e = (PGEventConnReset *)evtInfo;
5863 mydata *data = PQinstanceData(e->conn, myEventProc);
5866 memset(data, 0, sizeof(mydata));
5870 case PGEVT_CONNDESTROY:
5872 PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
5873 mydata *data = PQinstanceData(e->conn, myEventProc);
5875 /* free instance data because the conn is being destroyed */
5881 case PGEVT_RESULTCREATE:
5883 PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
5884 mydata *conn_data = PQinstanceData(e->conn, myEventProc);
5885 mydata *res_data = dup_mydata(conn_data);
5887 /* associate app specific data with result (copy it from conn) */
5888 PQsetResultInstanceData(e->result, myEventProc, res_data);
5892 case PGEVT_RESULTCOPY:
5894 PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
5895 mydata *src_data = PQresultInstanceData(e->src, myEventProc);
5896 mydata *dest_data = dup_mydata(src_data);
5898 /* associate app specific data with result (copy it from a result) */
5899 PQsetResultInstanceData(e->dest, myEventProc, dest_data);
5903 case PGEVT_RESULTDESTROY:
5905 PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
5906 mydata *data = PQresultInstanceData(e->result, myEventProc);
5908 /* free instance data because the result is being destroyed */
5914 /* unknown event id, just return TRUE. */
5919 return TRUE; /* event processing succeeded */
5926 <sect1 id="libpq-envars">
5927 <title>Environment Variables</title>
5929 <indexterm zone="libpq-envars">
5930 <primary>environment variable</primary>
5934 The following environment variables can be used to select default
5935 connection parameter values, which will be used by
5936 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
5937 <function>PQsetdb</> if no value is directly specified by the calling
5938 code. These are useful to avoid hard-coding database connection
5939 information into simple client applications, for example.
5945 <primary><envar>PGHOST</envar></primary>
5947 <envar>PGHOST</envar> behaves the same as the <xref
5948 linkend="libpq-connect-host"> connection parameter.
5955 <primary><envar>PGHOSTADDR</envar></primary>
5957 <envar>PGHOSTADDR</envar> behaves the same as the <xref
5958 linkend="libpq-connect-hostaddr"> connection parameter.
5959 This can be set instead of or in addition to <envar>PGHOST</envar>
5960 to avoid DNS lookup overhead.
5967 <primary><envar>PGPORT</envar></primary>
5969 <envar>PGPORT</envar> behaves the same as the <xref
5970 linkend="libpq-connect-port"> connection parameter.
5977 <primary><envar>PGDATABASE</envar></primary>
5979 <envar>PGDATABASE</envar> behaves the same as the <xref
5980 linkend="libpq-connect-dbname"> connection parameter.
5987 <primary><envar>PGUSER</envar></primary>
5989 <envar>PGUSER</envar> behaves the same as the <xref
5990 linkend="libpq-connect-user"> connection parameter.
5997 <primary><envar>PGPASSWORD</envar></primary>
5999 <envar>PGPASSWORD</envar> behaves the same as the <xref
6000 linkend="libpq-connect-password"> connection parameter.
6001 Use of this environment variable
6002 is not recommended for security reasons, as some operating systems
6003 allow non-root users to see process environment variables via
6004 <application>ps</>; instead consider using the
6005 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
6012 <primary><envar>PGPASSFILE</envar></primary>
6014 <envar>PGPASSFILE</envar> specifies the name of the password file to
6015 use for lookups. If not set, it defaults to <filename>~/.pgpass</>
6016 (see <xref linkend="libpq-pgpass">).
6023 <primary><envar>PGSERVICE</envar></primary>
6025 <envar>PGSERVICE</envar> behaves the same as the <xref
6026 linkend="libpq-connect-service"> connection parameter.
6033 <primary><envar>PGSERVICEFILE</envar></primary>
6035 <envar>PGSERVICEFILE</envar> specifies the name of the per-user
6036 connection service file. If not set, it defaults
6037 to <filename>~/.pg_service.conf</>
6038 (see <xref linkend="libpq-pgservice">).
6045 <primary><envar>PGREALM</envar></primary>
6047 <envar>PGREALM</envar> sets the Kerberos realm to use with
6048 <productname>PostgreSQL</productname>, if it is different from the
6049 local realm. If <envar>PGREALM</envar> is set,
6050 <application>libpq</application> applications will attempt
6051 authentication with servers for this realm and use separate ticket
6052 files to avoid conflicts with local ticket files. This
6053 environment variable is only used if Kerberos authentication is
6054 selected by the server.
6061 <primary><envar>PGOPTIONS</envar></primary>
6063 <envar>PGOPTIONS</envar> behaves the same as the <xref
6064 linkend="libpq-connect-options"> connection parameter.
6071 <primary><envar>PGAPPNAME</envar></primary>
6073 <envar>PGAPPNAME</envar> behaves the same as the <xref
6074 linkend="libpq-connect-application-name"> connection parameter.
6081 <primary><envar>PGSSLMODE</envar></primary>
6083 <envar>PGSSLMODE</envar> behaves the same as the <xref
6084 linkend="libpq-connect-sslmode"> connection parameter.
6091 <primary><envar>PGREQUIRESSL</envar></primary>
6093 <envar>PGREQUIRESSL</envar> behaves the same as the <xref
6094 linkend="libpq-connect-requiressl"> connection parameter.
6101 <primary><envar>PGSSLCERT</envar></primary>
6103 <envar>PGSSLCERT</envar> behaves the same as the <xref
6104 linkend="libpq-connect-sslcert"> connection parameter.
6111 <primary><envar>PGSSLKEY</envar></primary>
6113 <envar>PGSSLKEY</envar> behaves the same as the <xref
6114 linkend="libpq-connect-sslkey"> connection parameter.
6121 <primary><envar>PGSSLROOTCERT</envar></primary>
6123 <envar>PGSSLROOTCERT</envar> behaves the same as the <xref
6124 linkend="libpq-connect-sslrootcert"> connection parameter.
6131 <primary><envar>PGSSLCRL</envar></primary>
6133 <envar>PGSSLCRL</envar> behaves the same as the <xref
6134 linkend="libpq-connect-sslcrl"> connection parameter.
6141 <primary><envar>PGKRBSRVNAME</envar></primary>
6143 <envar>PGKRBSRVNAME</envar> behaves the same as the <xref
6144 linkend="libpq-connect-krbsrvname"> connection parameter.
6151 <primary><envar>PGGSSLIB</envar></primary>
6153 <envar>PGGSSLIB</envar> behaves the same as the <xref
6154 linkend="libpq-connect-gsslib"> connection parameter.
6161 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
6163 <envar>PGCONNECT_TIMEOUT</envar> behaves the same as the <xref
6164 linkend="libpq-connect-connect-timeout"> connection parameter.
6171 The following environment variables can be used to specify default
6172 behavior for each <productname>PostgreSQL</productname> session. (See
6173 also the <xref linkend="sql-alteruser">
6174 and <xref linkend="sql-alterdatabase">
6175 commands for ways to set default behavior on a per-user or per-database
6182 <primary><envar>PGDATESTYLE</envar></primary>
6184 <envar>PGDATESTYLE</envar> sets the default style of date/time
6185 representation. (Equivalent to <literal>SET datestyle TO
6193 <primary><envar>PGTZ</envar></primary>
6195 <envar>PGTZ</envar> sets the default time zone. (Equivalent to
6196 <literal>SET timezone TO ...</literal>.)
6203 <primary><envar>PGCLIENTENCODING</envar></primary>
6205 <envar>PGCLIENTENCODING</envar> sets the default client character
6206 set encoding. (Equivalent to <literal>SET client_encoding TO
6214 <primary><envar>PGGEQO</envar></primary>
6216 <envar>PGGEQO</envar> sets the default mode for the genetic query
6217 optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
6222 Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set">
6223 for information on correct values for these
6224 environment variables.
6228 The following environment variables determine internal behavior of
6229 <application>libpq</application>; they override compiled-in defaults.
6235 <primary><envar>PGSYSCONFDIR</envar></primary>
6237 <envar>PGSYSCONFDIR</envar> sets the directory containing the
6238 <filename>pg_service.conf</> file and in a future version
6239 possibly other system-wide configuration files.
6246 <primary><envar>PGLOCALEDIR</envar></primary>
6248 <envar>PGLOCALEDIR</envar> sets the directory containing the
6249 <literal>locale</> files for message internationalization.
6258 <sect1 id="libpq-pgpass">
6259 <title>The Password File</title>
6261 <indexterm zone="libpq-pgpass">
6262 <primary>password file</primary>
6264 <indexterm zone="libpq-pgpass">
6265 <primary>.pgpass</primary>
6269 The file <filename>.pgpass</filename> in a user's home directory or the
6270 file referenced by <envar>PGPASSFILE</envar> can contain passwords to
6271 be used if the connection requires a password (and no password has been
6272 specified otherwise). On Microsoft Windows the file is named
6273 <filename>%APPDATA%\postgresql\pgpass.conf</> (where
6274 <filename>%APPDATA%</> refers to the Application Data subdirectory in
6275 the user's profile).
6279 This file should contain lines of the following format:
6281 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
6283 Each of the first four fields can be a literal value, or
6284 <literal>*</literal>, which matches anything. The password field from
6285 the first line that matches the current connection parameters will be
6286 used. (Therefore, put more-specific entries first when you are using
6287 wildcards.) If an entry needs to contain <literal>:</literal> or
6288 <literal>\</literal>, escape this character with <literal>\</literal>.
6289 A host name of <literal>localhost</> matches both TCP (host name
6290 <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
6291 or the default socket directory) connections coming from the local
6292 machine. In a standby server, a database name of <literal>replication</>
6293 matches streaming replication connections made to the master server.
6297 On Unix systems, the permissions on <filename>.pgpass</filename> must
6298 disallow any access to world or group; achieve this by the command
6299 <command>chmod 0600 ~/.pgpass</command>. If the permissions are less
6300 strict than this, the file will be ignored. On Microsoft Windows, it
6301 is assumed that the file is stored in a directory that is secure, so
6302 no special permissions check is made.
6307 <sect1 id="libpq-pgservice">
6308 <title>The Connection Service File</title>
6310 <indexterm zone="libpq-pgservice">
6311 <primary>connection service file</primary>
6313 <indexterm zone="libpq-pgservice">
6314 <primary>pg_service.conf</primary>
6316 <indexterm zone="libpq-pgservice">
6317 <primary>.pg_service.conf</primary>
6321 The connection service file allows libpq connection parameters to be
6322 associated with a single service name. That service name can then be
6323 specified by a libpq connection, and the associated settings will be
6324 used. This allows connection parameters to be modified without requiring
6325 a recompile of the libpq application. The service name can also be
6326 specified using the <envar>PGSERVICE</envar> environment variable.
6330 The connection service file can be a per-user service file
6331 at <filename>~/.pg_service.conf</filename> or the location
6332 specified by the environment variable <envar>PGSERVICEFILE</envar>,
6333 or it can be a system-wide file
6334 at <filename>etc/pg_service.conf</filename> or in the directory
6335 specified by the environment variable
6336 <envar>PGSYSCONFDIR</envar>. If service definitions with the same
6337 name exist in the user and the system file, the user file takes
6342 The file uses an <quote>INI file</quote> format where the section
6343 name is the service name and the parameters are connection
6344 parameters; see <xref linkend="libpq-connect"> for a list. For
6353 An example file is provided at
6354 <filename>share/pg_service.conf.sample</filename>.
6359 <sect1 id="libpq-ldap">
6360 <title>LDAP Lookup of Connection Parameters</title>
6362 <indexterm zone="libpq-ldap">
6363 <primary>LDAP connection parameter lookup</primary>
6367 If <application>libpq</application> has been compiled with LDAP support (option
6368 <literal><option>--with-ldap</option></literal> for <command>configure</command>)
6369 it is possible to retrieve connection options like <literal>host</literal>
6370 or <literal>dbname</literal> via LDAP from a central server.
6371 The advantage is that if the connection parameters for a database change,
6372 the connection information doesn't have to be updated on all client machines.
6376 LDAP connection parameter lookup uses the connection service file
6377 <filename>pg_service.conf</filename> (see <xref
6378 linkend="libpq-pgservice">). A line in a
6379 <filename>pg_service.conf</filename> stanza that starts with
6380 <literal>ldap://</literal> will be recognized as an LDAP URL and an
6381 LDAP query will be performed. The result must be a list of
6382 <literal>keyword = value</literal> pairs which will be used to set
6383 connection options. The URL must conform to RFC 1959 and be of the
6386 ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
6388 where <replaceable>hostname</replaceable> defaults to
6389 <literal>localhost</literal> and <replaceable>port</replaceable>
6394 Processing of <filename>pg_service.conf</filename> is terminated after
6395 a successful LDAP lookup, but is continued if the LDAP server cannot
6396 be contacted. This is to provide a fallback with further LDAP URL
6397 lines that point to different LDAP servers, classical <literal>keyword
6398 = value</literal> pairs, or default connection options. If you would
6399 rather get an error message in this case, add a syntactically incorrect
6400 line after the LDAP URL.
6404 A sample LDAP entry that has been created with the LDIF file
6407 dn:cn=mydatabase,dc=mycompany,dc=com
6410 objectclass:groupOfUniqueNames
6412 uniqueMember:host=dbserver.mycompany.com
6413 uniqueMember:port=5439
6414 uniqueMember:dbname=mydb
6415 uniqueMember:user=mydb_user
6416 uniqueMember:sslmode=require
6418 might be queried with the following LDAP URL:
6420 ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)
6425 You can also mix regular service file entries with LDAP lookups.
6426 A complete example for a stanza in <filename>pg_service.conf</filename>
6429 # only host and port are stored in LDAP, specify dbname and user explicitly
6433 ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
6440 <sect1 id="libpq-ssl">
6441 <title>SSL Support</title>
6443 <indexterm zone="libpq-ssl">
6444 <primary>SSL</primary>
6448 <productname>PostgreSQL</> has native support for using <acronym>SSL</>
6449 connections to encrypt client/server communications for increased
6450 security. See <xref linkend="ssl-tcp"> for details about the server-side
6451 <acronym>SSL</> functionality.
6455 <application>libpq</application> reads the system-wide
6456 <productname>OpenSSL</productname> configuration file. By default, this
6457 file is named <filename>openssl.cnf</filename> and is located in the
6458 directory reported by <literal>openssl version -d</>. This default
6459 can be overridden by setting environment variable
6460 <envar>OPENSSL_CONF</envar> to the name of the desired configuration
6464 <sect2 id="libq-ssl-certificates">
6465 <title>Certificate verification</title>
6468 By default, <productname>PostgreSQL</> will not perform any verification of
6469 the server certificate. This means that it is possible to spoof the server
6470 identity (for example by modifying a DNS record or by taking over the server
6471 IP address) without the client knowing. In order to prevent spoofing,
6472 <acronym>SSL</> certificate verification must be used.
6476 If the parameter <literal>sslmode</> is set to <literal>verify-ca</>,
6477 libpq will verify that the server is trustworthy by checking the
6478 certificate chain up to a trusted certificate authority
6479 (<acronym>CA</>). If <literal>sslmode</> is set to <literal>verify-full</>,
6480 libpq will <emphasis>also</> verify that the server hostname matches its
6481 certificate. The SSL connection will fail if the server certificate cannot
6482 be verified. <literal>verify-full</> is recommended in most
6483 security-sensitive environments.
6487 In <literal>verify-full</> mode, the <literal>cn</> (Common Name) attribute
6488 of the certificate is matched against the hostname. If the <literal>cn</>
6489 attribute starts with an asterisk (<literal>*</>), it will be treated as
6490 a wildcard, and will match all characters <emphasis>except</> a dot
6491 (<literal>.</>). This means the certificate will not match subdomains.
6492 If the connection is made using an IP address instead of a hostname, the
6493 IP address will be matched (without doing any DNS lookups).
6497 To allow server certificate verification, the certificate(s) of one or more
6498 trusted <acronym>CA</>s must be
6499 placed in the file <filename>~/.postgresql/root.crt</> in the user's home
6500 directory. (On Microsoft Windows the file is named
6501 <filename>%APPDATA%\postgresql\root.crt</filename>.)
6505 Certificate Revocation List (CRL) entries are also checked
6506 if the file <filename>~/.postgresql/root.crl</filename> exists
6507 (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
6512 The location of the root certificate file and the CRL can be changed by
6514 the connection parameters <literal>sslrootcert</> and <literal>sslcrl</>
6515 or the environment variables <envar>PGSSLROOTCERT</> and <envar>PGSSLCRL</>.
6519 <sect2 id="libpq-ssl-clientcert">
6520 <title>Client certificates</title>
6523 If the server requests a trusted client certificate,
6524 <application>libpq</application> will send the certificate stored in
6525 file <filename>~/.postgresql/postgresql.crt</> in the user's home
6526 directory. The certificate must be signed by one of the certificate
6527 authorities (<acronym>CA</acronym>) trusted by the server. A matching
6528 private key file <filename>~/.postgresql/postgresql.key</> must also
6529 be present. The private
6530 key file must not allow any access to world or group; achieve this by the
6531 command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
6532 On Microsoft Windows these files are named
6533 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
6534 <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
6535 is no special permissions check since the directory is presumed secure.
6536 The location of the certificate and key files can be overridden by the
6537 connection parameters <literal>sslcert</> and <literal>sslkey</> or the
6538 environment variables <envar>PGSSLCERT</> and <envar>PGSSLKEY</>.
6542 In some cases, the client certificate might be signed by an
6543 <quote>intermediate</> certificate authority, rather than one that is
6544 directly trusted by the server. To use such a certificate, append the
6545 certificate of the signing authority to the <filename>postgresql.crt</>
6546 file, then its parent authority's certificate, and so on up to a
6547 <quote>root</> authority that is trusted by the server. The root
6548 certificate should be included in every case where
6549 <filename>postgresql.crt</> contains more than one certificate.
6553 Note that <filename>root.crt</filename> lists the top-level CAs that are
6554 considered trusted for signing server certificates. In principle it need
6555 not list the CA that signed the client's certificate, though in most cases
6556 that CA would also be trusted for server certificates.
6561 <sect2 id="libpq-ssl-protection">
6562 <title>Protection provided in different modes</title>
6565 The different values for the <literal>sslmode</> parameter provide different
6566 levels of protection. SSL can provide
6567 protection against three types of attacks:
6569 <table id="libpq-ssl-protect-attacks">
6570 <title>SSL attacks</title>
6575 <entry>Description</entry>
6581 <entry>Eavesdropping</entry>
6582 <entry>If a third party can examine the network traffic between the
6583 client and the server, it can read both connection information (including
6584 the username and password) and the data that is passed. <acronym>SSL</>
6585 uses encryption to prevent this.
6590 <entry>Man in the middle (<acronym>MITM</>)</entry>
6591 <entry>If a third party can modify the data while passing between the
6592 client and server, it can pretend to be the server and therefore see and
6593 modify data <emphasis>even if it is encrypted</>. The third party can then
6594 forward the connection information and data to the original server,
6595 making it impossible to detect this attack. Common vectors to do this
6596 include DNS poisoning and address hijacking, whereby the client is directed
6597 to a different server than intended. There are also several other
6598 attack methods that can accomplish this. <acronym>SSL</> uses certificate
6599 verification to prevent this, by authenticating the server to the client.
6604 <entry>Impersonation</entry>
6605 <entry>If a third party can pretend to be an authorized client, it can
6606 simply access data it should not have access to. Typically this can
6607 happen through insecure password management. <acronym>SSL</> uses
6608 client certificates to prevent this, by making sure that only holders
6609 of valid certificates can access the server.
6617 For a connection to be known secure, SSL usage must be configured
6618 on <emphasis>both the client and the server</> before the connection
6619 is made. If it is only configured on the server, the client may end up
6620 sending sensitive information (e.g. passwords) before
6621 it knows that the server requires high security. In libpq, secure
6622 connections can be ensured
6623 by setting the <literal>sslmode</> parameter to <literal>verify-full</> or
6624 <literal>verify-ca</>, and providing the system with a root certificate to
6625 verify against. This is analogous to using an <literal>https</>
6626 <acronym>URL</> for encrypted web browsing.
6630 Once the server has been authenticated, the client can pass sensitive data.
6631 This means that up until this point, the client does not need to know if
6632 certificates will be used for authentication, making it safe to specify that
6633 only in the server configuration.
6637 All <acronym>SSL</> options carry overhead in the form of encryption and
6638 key-exchange, so there is a tradeoff that has to be made between performance
6639 and security. The following table illustrates the risks the different
6640 <literal>sslmode</> values protect against, and what statement they make
6641 about security and overhead:
6644 <table id="libpq-ssl-sslmode-statements">
6645 <title>SSL mode descriptions</title>
6649 <entry><literal>sslmode</></entry>
6650 <entry>Eavesdropping protection</entry>
6651 <entry><acronym>MITM</> protection</entry>
6652 <entry>Statement</entry>
6658 <entry><literal>disabled</></entry>
6661 <entry>I don't care about security, and I don't want to pay the overhead
6667 <entry><literal>allow</></entry>
6668 <entry>Maybe</entry>
6670 <entry>I don't care about security, but I will pay the overhead of
6671 encryption if the server insists on it.
6676 <entry><literal>prefer</></entry>
6677 <entry>Maybe</entry>
6679 <entry>I don't care about encryption, but I wish to pay the overhead of
6680 encryption if the server supports it.
6685 <entry><literal>require</></entry>
6688 <entry>I want my data to be encrypted, and I accept the overhead. I trust
6689 that the network will make sure I always connect to the server I want.
6694 <entry><literal>verify-ca</></entry>
6696 <entry><literal>Depends on CA</>-policy</entry>
6697 <entry>I want my data encrypted, and I accept the overhead. I want to be
6698 sure that I connect to a server that I trust.
6703 <entry><literal>verify-full</></entry>
6706 <entry>I want my data encrypted, and I accept the overhead. I want to be
6707 sure that I connect to a server I trust, and that it's the one I
6717 The difference between <literal>verify-ca</> and <literal>verify-full</>
6718 depends on the policy of the root <acronym>CA</>. If a public
6719 <acronym>CA</> is used, <literal>verify-ca</> allows connections to a server
6720 that <emphasis>somebody else</> may have registered with the <acronym>CA</>.
6721 In this case, <literal>verify-full</> should always be used. If
6722 a local <acronym>CA</> is used, or even a self-signed certificate, using
6723 <literal>verify-ca</> often provides enough protection.
6727 The default value for <literal>sslmode</> is <literal>prefer</>. As is shown
6728 in the table, this makes no sense from a security point of view, and it only
6729 promises performance overhead if possible. It is only provided as the default
6730 for backwards compatibility, and is not recommended in secure deployments.
6735 <sect2 id="libpq-ssl-fileusage">
6736 <title>SSL File Usage</title>
6737 <table id="libpq-ssl-file-usage">
6738 <title>Libpq/Client SSL File Usage</title>
6743 <entry>Contents</entry>
6744 <entry>Effect</entry>
6751 <entry><filename>~/.postgresql/postgresql.crt</></entry>
6752 <entry>client certificate</entry>
6753 <entry>requested by server</entry>
6757 <entry><filename>~/.postgresql/postgresql.key</></entry>
6758 <entry>client private key</entry>
6759 <entry>proves client certificate sent by owner; does not indicate
6760 certificate owner is trustworthy</entry>
6764 <entry><filename>~/.postgresql/root.crt</></entry>
6765 <entry>trusted certificate authorities</entry>
6766 <entry>checks that server certificate is signed by a trusted certificate
6771 <entry><filename>~/.postgresql/root.crl</></entry>
6772 <entry>certificates revoked by certificate authorities</entry>
6773 <entry>server certificate must not be on this list</entry>
6781 <sect2 id="libpq-ssl-initialize">
6782 <title>SSL library initialization</title>
6785 If your application initializes <literal>libssl</> and/or
6786 <literal>libcrypto</> libraries and <application>libpq</application>
6787 is built with <acronym>SSL</> support, you should call
6788 <function>PQinitOpenSSL</> to tell <application>libpq</application>
6789 that the <literal>libssl</> and/or <literal>libcrypto</> libraries
6790 have been initialized by your application, so that
6791 <application>libpq</application> will not also initialize those libraries.
6792 <!-- If this URL changes replace it with a URL to www.archive.org. -->
6794 url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
6795 for details on the SSL API.
6802 <function>PQinitOpenSSL</function>
6804 <primary>PQinitOpenSSL</primary>
6810 Allows applications to select which security libraries to initialize.
6812 void PQinitOpenSSL(int do_ssl, int do_crypto);
6817 When <parameter>do_ssl</> is non-zero, <application>libpq</application>
6818 will initialize the <application>OpenSSL</> library before first
6819 opening a database connection. When <parameter>do_crypto</> is
6820 non-zero, the <literal>libcrypto</> library will be initialized. By
6821 default (if <function>PQinitOpenSSL</> is not called), both libraries
6822 are initialized. When SSL support is not compiled in, this function is
6823 present but does nothing.
6827 If your application uses and initializes either <application>OpenSSL</>
6828 or its underlying <literal>libcrypto</> library, you <emphasis>must</>
6829 call this function with zeroes for the appropriate parameter(s)
6830 before first opening a database connection. Also be sure that you
6831 have done that initialization before opening a database connection.
6838 <function>PQinitSSL</function>
6840 <primary>PQinitSSL</primary>
6846 Allows applications to select which security libraries to initialize.
6848 void PQinitSSL(int do_ssl);
6853 This function is equivalent to
6854 <literal>PQinitOpenSSL(do_ssl, do_ssl)</>.
6855 It is sufficient for applications that initialize both or neither
6856 of <application>OpenSSL</> and <literal>libcrypto</>.
6860 <function>PQinitSSL</> has been present since
6861 <productname>PostgreSQL</> 8.0, while <function>PQinitOpenSSL</>
6862 was added in <productname>PostgreSQL</> 8.4, so <function>PQinitSSL</>
6863 might be preferable for applications that need to work with older
6864 versions of <application>libpq</application>.
6875 <sect1 id="libpq-threading">
6876 <title>Behavior in Threaded Programs</title>
6878 <indexterm zone="libpq-threading">
6879 <primary>threads</primary>
6880 <secondary>with libpq</secondary>
6884 <application>libpq</application> is reentrant and thread-safe by default.
6885 You might need to use special compiler command-line
6886 options when you compile your application code. Refer to your
6887 system's documentation for information about how to build
6888 thread-enabled applications, or look in
6889 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
6890 and <literal>PTHREAD_LIBS</>. This function allows the querying of
6891 <application>libpq</application>'s thread-safe status:
6897 <function>PQisthreadsafe</function>
6899 <primary>PQisthreadsafe</primary>
6905 Returns the thread safety status of the
6906 <application>libpq</application> library.
6908 int PQisthreadsafe();
6913 Returns 1 if the <application>libpq</application> is thread-safe
6921 One thread restriction is that no two threads attempt to manipulate
6922 the same <structname>PGconn</> object at the same time. In particular,
6923 you cannot issue concurrent commands from different threads through
6924 the same connection object. (If you need to run concurrent commands,
6925 use multiple connections.)
6929 <structname>PGresult</> objects are read-only after creation, and so
6930 can be passed around freely between threads.
6934 The deprecated functions <function>PQrequestCancel</function> and
6935 <function>PQoidStatus</function> are not thread-safe and should not be
6936 used in multithread programs. <function>PQrequestCancel</function>
6937 can be replaced by <function>PQcancel</function>.
6938 <function>PQoidStatus</function> can be replaced by
6939 <function>PQoidValue</function>.
6943 If you are using Kerberos inside your application (in addition to inside
6944 <application>libpq</application>), you will need to do locking around
6945 Kerberos calls because Kerberos functions are not thread-safe. See
6946 function <function>PQregisterThreadLock</> in the
6947 <application>libpq</application> source code for a way to do cooperative
6948 locking between <application>libpq</application> and your application.
6952 If you experience problems with threaded applications, run the program
6953 in <filename>src/tools/thread</> to see if your platform has
6954 thread-unsafe functions. This program is run by
6955 <filename>configure</filename>, but for binary distributions your
6956 library might not match the library used to build the binaries.
6961 <sect1 id="libpq-build">
6962 <title>Building <application>libpq</application> Programs</title>
6964 <indexterm zone="libpq-build">
6965 <primary>compiling</primary>
6966 <secondary>libpq applications</secondary>
6970 To build (i.e., compile and link) a program using
6971 <application>libpq</application> you need to do all of the following
6977 Include the <filename>libpq-fe.h</filename> header file:
6979 #include <libpq-fe.h>
6981 If you failed to do that then you will normally get error messages
6982 from your compiler similar to:
6984 foo.c: In function `main':
6985 foo.c:34: `PGconn' undeclared (first use in this function)
6986 foo.c:35: `PGresult' undeclared (first use in this function)
6987 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
6988 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
6989 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
6996 Point your compiler to the directory where the <productname>PostgreSQL</> header
6997 files were installed, by supplying the
6998 <literal>-I<replaceable>directory</replaceable></literal> option
6999 to your compiler. (In some cases the compiler will look into
7000 the directory in question by default, so you can omit this
7001 option.) For instance, your compile command line could look
7004 cc -c -I/usr/local/pgsql/include testprog.c
7006 If you are using makefiles then add the option to the
7007 <varname>CPPFLAGS</varname> variable:
7009 CPPFLAGS += -I/usr/local/pgsql/include
7014 If there is any chance that your program might be compiled by
7015 other users then you should not hardcode the directory location
7016 like that. Instead, you can run the utility
7017 <command>pg_config</command><indexterm><primary>pg_config</><secondary
7018 sortas="libpq">with libpq</></> to find out where the header
7019 files are on the local system:
7021 <prompt>$</prompt> pg_config --includedir
7022 <computeroutput>/usr/local/include</computeroutput>
7027 Failure to specify the correct option to the compiler will
7028 result in an error message such as:
7030 testlibpq.c:8:22: libpq-fe.h: No such file or directory
7037 When linking the final program, specify the option
7038 <literal>-lpq</literal> so that the <application>libpq</application>
7039 library gets pulled in, as well as the option
7040 <literal>-L<replaceable>directory</replaceable></literal> to point
7041 the compiler to the directory where the
7042 <application>libpq</application> library resides. (Again, the
7043 compiler will search some directories by default.) For maximum
7044 portability, put the <option>-L</option> option before the
7045 <option>-lpq</option> option. For example:
7047 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
7052 You can find out the library directory using
7053 <command>pg_config</command> as well:
7055 <prompt>$</prompt> pg_config --libdir
7056 <computeroutput>/usr/local/pgsql/lib</computeroutput>
7061 Error messages that point to problems in this area could look like
7064 testlibpq.o: In function `main':
7065 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
7066 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
7067 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
7069 This means you forgot <option>-lpq</option>.
7071 /usr/bin/ld: cannot find -lpq
7073 This means you forgot the <option>-L</option> option or did not
7074 specify the right directory.
7083 <sect1 id="libpq-example">
7084 <title>Example Programs</title>
7087 These examples and others can be found in the
7088 directory <filename>src/test/examples</filename> in the source code
7092 <example id="libpq-example-1">
7093 <title><application>libpq</application> Example Program 1</title>
7100 * Test the C version of libpq, the PostgreSQL frontend library.
7104 #include <libpq-fe.h>
7107 exit_nicely(PGconn *conn)
7114 main(int argc, char **argv)
7116 const char *conninfo;
7124 * If the user supplies a parameter on the command line, use it as the
7125 * conninfo string; otherwise default to setting dbname=postgres and using
7126 * environment variables or defaults for all other connection parameters.
7131 conninfo = "dbname = postgres";
7133 /* Make a connection to the database */
7134 conn = PQconnectdb(conninfo);
7136 /* Check to see that the backend connection was successfully made */
7137 if (PQstatus(conn) != CONNECTION_OK)
7139 fprintf(stderr, "Connection to database failed: %s",
7140 PQerrorMessage(conn));
7145 * Our test case here involves using a cursor, for which we must be inside
7146 * a transaction block. We could do the whole thing with a single
7147 * PQexec() of "select * from pg_database", but that's too trivial to make
7151 /* Start a transaction block */
7152 res = PQexec(conn, "BEGIN");
7153 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7155 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
7161 * Should PQclear PGresult whenever it is no longer needed to avoid memory
7167 * Fetch rows from pg_database, the system catalog of databases
7169 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
7170 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7172 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
7178 res = PQexec(conn, "FETCH ALL in myportal");
7179 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7181 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
7186 /* first, print out the attribute names */
7187 nFields = PQnfields(res);
7188 for (i = 0; i < nFields; i++)
7189 printf("%-15s", PQfname(res, i));
7192 /* next, print out the rows */
7193 for (i = 0; i < PQntuples(res); i++)
7195 for (j = 0; j < nFields; j++)
7196 printf("%-15s", PQgetvalue(res, i, j));
7202 /* close the portal ... we don't bother to check for errors ... */
7203 res = PQexec(conn, "CLOSE myportal");
7206 /* end the transaction */
7207 res = PQexec(conn, "END");
7210 /* close the connection to the database and cleanup */
7219 <example id="libpq-example-2">
7220 <title><application>libpq</application> Example Program 2</title>
7226 * Test of the asynchronous notification interface
7228 * Start this program, then from psql in another window do
7230 * Repeat four times to get this program to exit.
7232 * Or, if you want to get fancy, try this:
7233 * populate a database with the following commands
7234 * (provided in src/test/examples/testlibpq2.sql):
7236 * CREATE TABLE TBL1 (i int4);
7238 * CREATE TABLE TBL2 (i int4);
7240 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
7241 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
7243 * and do this four times:
7245 * INSERT INTO TBL1 VALUES (10);
7251 #include <sys/time.h>
7252 #include <libpq-fe.h>
7255 exit_nicely(PGconn *conn)
7262 main(int argc, char **argv)
7264 const char *conninfo;
7271 * If the user supplies a parameter on the command line, use it as the
7272 * conninfo string; otherwise default to setting dbname=postgres and using
7273 * environment variables or defaults for all other connection parameters.
7278 conninfo = "dbname = postgres";
7280 /* Make a connection to the database */
7281 conn = PQconnectdb(conninfo);
7283 /* Check to see that the backend connection was successfully made */
7284 if (PQstatus(conn) != CONNECTION_OK)
7286 fprintf(stderr, "Connection to database failed: %s",
7287 PQerrorMessage(conn));
7292 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
7294 res = PQexec(conn, "LISTEN TBL2");
7295 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7297 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
7303 * should PQclear PGresult whenever it is no longer needed to avoid memory
7308 /* Quit after four notifies are received. */
7310 while (nnotifies < 4)
7313 * Sleep until something happens on the connection. We use select(2)
7314 * to wait for input, but you could also use poll() or similar
7320 sock = PQsocket(conn);
7323 break; /* shouldn't happen */
7325 FD_ZERO(&input_mask);
7326 FD_SET(sock, &input_mask);
7328 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
7330 fprintf(stderr, "select() failed: %s\n", strerror(errno));
7334 /* Now check for input */
7335 PQconsumeInput(conn);
7336 while ((notify = PQnotifies(conn)) != NULL)
7339 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
7340 notify->relname, notify->be_pid);
7346 fprintf(stderr, "Done.\n");
7348 /* close the connection to the database and cleanup */
7357 <example id="libpq-example-3">
7358 <title><application>libpq</application> Example Program 3</>
7364 * Test out-of-line parameters and binary I/O.
7366 * Before running this, populate a database with the following commands
7367 * (provided in src/test/examples/testlibpq3.sql):
7369 * CREATE TABLE test1 (i int4, t text, b bytea);
7371 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
7372 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
7374 * The expected output is:
7378 * t = (11 bytes) 'joe's place'
7379 * b = (5 bytes) \000\001\002\003\004
7383 * t = (8 bytes) 'ho there'
7384 * b = (5 bytes) \004\003\002\001\000
7389 #include <sys/types.h>
7390 #include <libpq-fe.h>
7392 /* for ntohl/htonl */
7393 #include <netinet/in.h>
7394 #include <arpa/inet.h>
7398 exit_nicely(PGconn *conn)
7405 * This function prints a query result that is a binary-format fetch from
7406 * a table defined as in the comment above. We split it out because the
7407 * main() function uses it twice.
7410 show_binary_results(PGresult *res)
7418 /* Use PQfnumber to avoid assumptions about field order in result */
7419 i_fnum = PQfnumber(res, "i");
7420 t_fnum = PQfnumber(res, "t");
7421 b_fnum = PQfnumber(res, "b");
7423 for (i = 0; i < PQntuples(res); i++)
7431 /* Get the field values (we ignore possibility they are null!) */
7432 iptr = PQgetvalue(res, i, i_fnum);
7433 tptr = PQgetvalue(res, i, t_fnum);
7434 bptr = PQgetvalue(res, i, b_fnum);
7437 * The binary representation of INT4 is in network byte order, which
7438 * we'd better coerce to the local byte order.
7440 ival = ntohl(*((uint32_t *) iptr));
7443 * The binary representation of TEXT is, well, text, and since libpq
7444 * was nice enough to append a zero byte to it, it'll work just fine
7447 * The binary representation of BYTEA is a bunch of bytes, which could
7448 * include embedded nulls so we have to pay attention to field length.
7450 blen = PQgetlength(res, i, b_fnum);
7452 printf("tuple %d: got\n", i);
7453 printf(" i = (%d bytes) %d\n",
7454 PQgetlength(res, i, i_fnum), ival);
7455 printf(" t = (%d bytes) '%s'\n",
7456 PQgetlength(res, i, t_fnum), tptr);
7457 printf(" b = (%d bytes) ", blen);
7458 for (j = 0; j < blen; j++)
7459 printf("\\%03o", bptr[j]);
7465 main(int argc, char **argv)
7467 const char *conninfo;
7470 const char *paramValues[1];
7471 int paramLengths[1];
7472 int paramFormats[1];
7473 uint32_t binaryIntVal;
7476 * If the user supplies a parameter on the command line, use it as the
7477 * conninfo string; otherwise default to setting dbname=postgres and using
7478 * environment variables or defaults for all other connection parameters.
7483 conninfo = "dbname = postgres";
7485 /* Make a connection to the database */
7486 conn = PQconnectdb(conninfo);
7488 /* Check to see that the backend connection was successfully made */
7489 if (PQstatus(conn) != CONNECTION_OK)
7491 fprintf(stderr, "Connection to database failed: %s",
7492 PQerrorMessage(conn));
7497 * The point of this program is to illustrate use of PQexecParams() with
7498 * out-of-line parameters, as well as binary transmission of data.
7500 * This first example transmits the parameters as text, but receives the
7501 * results in binary format. By using out-of-line parameters we can
7502 * avoid a lot of tedious mucking about with quoting and escaping, even
7503 * though the data is text. Notice how we don't have to do anything
7504 * special with the quote mark in the parameter value.
7507 /* Here is our out-of-line parameter value */
7508 paramValues[0] = "joe's place";
7510 res = PQexecParams(conn,
7511 "SELECT * FROM test1 WHERE t = $1",
7513 NULL, /* let the backend deduce param type */
7515 NULL, /* don't need param lengths since text */
7516 NULL, /* default to all text params */
7517 1); /* ask for binary results */
7519 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7521 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
7526 show_binary_results(res);
7531 * In this second example we transmit an integer parameter in binary
7532 * form, and again retrieve the results in binary form.
7534 * Although we tell PQexecParams we are letting the backend deduce
7535 * parameter type, we really force the decision by casting the parameter
7536 * symbol in the query text. This is a good safety measure when sending
7537 * binary parameters.
7540 /* Convert integer value "2" to network byte order */
7541 binaryIntVal = htonl((uint32_t) 2);
7543 /* Set up parameter arrays for PQexecParams */
7544 paramValues[0] = (char *) &binaryIntVal;
7545 paramLengths[0] = sizeof(binaryIntVal);
7546 paramFormats[0] = 1; /* binary */
7548 res = PQexecParams(conn,
7549 "SELECT * FROM test1 WHERE i = $1::int4",
7551 NULL, /* let the backend deduce param type */
7555 1); /* ask for binary results */
7557 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7559 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
7564 show_binary_results(res);
7568 /* close the connection to the database and cleanup */
projects / postgresql / blob