1 <!-- doc/src/sgml/libpq.sgml -->
4 <title><application>libpq</application> - C Library</title>
6 <indexterm zone="libpq">
7 <primary>libpq</primary>
10 <indexterm zone="libpq">
15 <application>libpq</application> is the <acronym>C</acronym>
16 application programmer's interface to <productname>PostgreSQL</>.
17 <application>libpq</> is a set of library functions that allow
18 client programs to pass queries to the <productname>PostgreSQL</>
19 backend server and to receive the results of these queries.
23 <application>libpq</> is also the underlying engine for several
24 other <productname>PostgreSQL</> application interfaces, including
25 those written for C++, Perl, Python, Tcl and <application>ECPG</>.
26 So some aspects of <application>libpq</>'s behavior will be
27 important to you if you use one of those packages. In particular,
28 <xref linkend="libpq-envars">,
29 <xref linkend="libpq-pgpass"> and
30 <xref linkend="libpq-ssl">
31 describe behavior that is visible to the user of any application
32 that uses <application>libpq</>.
36 Some short programs are included at the end of this chapter (<xref linkend="libpq-example">) to show how
37 to write programs that use <application>libpq</application>. There are also several
38 complete examples of <application>libpq</application> applications in the
39 directory <filename>src/test/examples</filename> in the source code distribution.
43 Client programs that use <application>libpq</application> must
44 include the header file
45 <filename>libpq-fe.h</filename><indexterm><primary>libpq-fe.h</></>
46 and must link with the <application>libpq</application> library.
49 <sect1 id="libpq-connect">
50 <title>Database Connection Control Functions</title>
53 The following functions deal with making a connection to a
54 <productname>PostgreSQL</productname> backend server. An
55 application program can have several backend connections open at
56 one time. (One reason to do that is to access more than one
57 database.) Each connection is represented by a
58 <structname>PGconn</><indexterm><primary>PGconn</></> object, which
59 is obtained from the function <function>PQconnectdb</>,
60 <function>PQconnectdbParams</>, or
61 <function>PQsetdbLogin</>. Note that these functions will always
62 return a non-null object pointer, unless perhaps there is too
63 little memory even to allocate the <structname>PGconn</> object.
64 The <function>PQstatus</> function should be called to check
65 the return value for a successful connection before queries are sent
66 via the connection object.
70 On Unix, forking a process with open libpq connections can lead to
71 unpredictable results because the parent and child processes share
72 the same sockets and operating system resources. For this reason,
73 such usage is not recommended, though doing an <function>exec</> from
74 the child process to load a new executable is safe.
80 On Windows, there is a way to improve performance if a single
81 database connection is repeatedly started and shutdown. Internally,
82 libpq calls <function>WSAStartup()</> and <function>WSACleanup()</> for connection startup
83 and shutdown, respectively. <function>WSAStartup()</> increments an internal
84 Windows library reference count which is decremented by <function>WSACleanup()</>.
85 When the reference count is just one, calling <function>WSACleanup()</> frees
86 all resources and all DLLs are unloaded. This is an expensive
87 operation. To avoid this, an application can manually call
88 <function>WSAStartup()</> so resources will not be freed when the last database
94 <varlistentry id="libpq-pqconnectdbparams">
95 <term><function>PQconnectdbParams</function><indexterm><primary>PQconnectdbParams</></></term>
98 Makes a new connection to the database server.
101 PGconn *PQconnectdbParams(const char * const *keywords,
102 const char * const *values,
108 This function opens a new database connection using the parameters taken
109 from two <symbol>NULL</symbol>-terminated arrays. The first,
110 <literal>keywords</literal>, is defined as an array of strings, each one
111 being a key word. The second, <literal>values</literal>, gives the value
112 for each key word. Unlike <function>PQsetdbLogin</> below, the parameter
113 set can be extended without changing the function signature, so use of
114 this function (or its nonblocking analogs <function>PQconnectStartParams</>
115 and <function>PQconnectPoll</function>) is preferred for new application
120 The currently recognized parameter key words are listed in
121 <xref linkend="libpq-paramkeywords">.
125 When <literal>expand_dbname</literal> is non-zero, the
126 <parameter>dbname</parameter> key word value is allowed to be recognized
127 as a connection string. Only the first occurrence of
128 <parameter>dbname</parameter> is expanded this way, any subsequent
129 <parameter>dbname</parameter> value is processed as plain database name. More
130 details on the possible connection string formats appear in
131 <xref linkend="libpq-connstring">.
135 The passed arrays can be empty to use all default parameters, or can
136 contain one or more parameter settings. They should be matched in length.
137 Processing will stop at the first <symbol>NULL</symbol> element
138 in the <literal>keywords</literal> array.
142 If any parameter is <symbol>NULL</symbol> or an emptry string, the corresponding
143 environment variable (see <xref linkend="libpq-envars">) is checked.
144 If the environment variable is not set either, then the indicated
145 built-in defaults are used.
149 In general key words are processed from the beginning of these arrays in index
150 order. The effect of this is that when key words are repeated, the last processed
151 value is retained. Therefore, through careful placement of the
152 <parameter>dbname</parameter> key word, it is possible to determine what may
153 be overridden by a <parameter>conninfo</parameter> string, and what may not.
159 <varlistentry id="libpq-pqconnectdb">
160 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
163 Makes a new connection to the database server.
166 PGconn *PQconnectdb(const char *conninfo);
171 This function opens a new database connection using the parameters taken
172 from the string <literal>conninfo</literal>.
176 The passed string can be empty to use all default parameters, or it can
177 contain one or more parameter settings separated by whitespace,
178 or it can contain a <acronym>URI</acronym>.
179 See <xref linkend="libpq-connstring"> for details.
186 <varlistentry id="libpq-pqsetdblogin">
187 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
190 Makes a new connection to the database server.
192 PGconn *PQsetdbLogin(const char *pghost,
194 const char *pgoptions,
203 This is the predecessor of <function>PQconnectdb</function> with a fixed
204 set of parameters. It has the same functionality except that the
205 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
206 empty string for any one of the fixed parameters that is to be defaulted.
210 If the <parameter>dbName</parameter> contains
211 an <symbol>=</symbol> sign or has a valid connection <acronym>URI</acronym> prefix, it
212 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
213 if it had been passed to <function>PQconnectdb</function>, and the remaining
214 parameters are then applied as specified for <function>PQconnectdbParams</>.
219 <varlistentry id="libpq-pqsetdb">
220 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
223 Makes a new connection to the database server.
225 PGconn *PQsetdb(char *pghost,
234 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
235 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
236 for backward compatibility with very old programs.
241 <varlistentry id="libpq-pqconnectstartparams">
242 <term><function>PQconnectStartParams</function><indexterm><primary>PQconnectStartParams</></></term>
243 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
244 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
247 <indexterm><primary>nonblocking connection</primary></indexterm>
248 Make a connection to the database server in a nonblocking manner.
251 PGconn *PQconnectStartParams(const char * const *keywords,
252 const char * const *values,
255 PGconn *PQconnectStart(const char *conninfo);
257 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
262 These three functions are used to open a connection to a database server such
263 that your application's thread of execution is not blocked on remote I/O
264 whilst doing so. The point of this approach is that the waits for I/O to
265 complete can occur in the application's main loop, rather than down inside
266 <function>PQconnectdbParams</> or <function>PQconnectdb</>, and so the
267 application can manage this operation in parallel with other activities.
271 With <function>PQconnectStartParams</function>, the database connection is made
272 using the parameters taken from the <literal>keywords</literal> and
273 <literal>values</literal> arrays, and controlled by <literal>expand_dbname</literal>,
274 as described above for <function>PQconnectdbParams</function>.
278 With <function>PQconnectStart</function>, the database connection is made
279 using the parameters taken from the string <literal>conninfo</literal> as
280 described above for <function>PQconnectdb</function>.
284 Neither <function>PQconnectStartParams</function> nor <function>PQconnectStart</function>
285 nor <function>PQconnectPoll</function> will block, so long as a number of
286 restrictions are met:
290 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
291 name and reverse name queries are not made. See the documentation of
292 these parameters in <xref linkend="libpq-paramkeywords"> for details.
298 If you call <function>PQtrace</function>, ensure that the stream object
299 into which you trace will not block.
305 You ensure that the socket is in the appropriate state
306 before calling <function>PQconnectPoll</function>, as described below.
313 Note: use of <function>PQconnectStartParams</> is analogous to
314 <function>PQconnectStart</> shown below.
318 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
319 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
320 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
321 representing a valid connection to the database). On return from
322 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
323 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
327 If <function>PQconnectStart</> succeeds, the next stage is to poll
328 <application>libpq</> so that it can proceed with the connection sequence.
329 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
330 socket underlying the database connection.
331 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
332 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
333 read (as indicated by <function>select()</>, <function>poll()</>, or
334 similar system function).
335 Then call <function>PQconnectPoll(conn)</function> again.
336 Conversely, if <function>PQconnectPoll(conn)</function> last returned
337 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
338 to write, then call <function>PQconnectPoll(conn)</function> again.
339 If you have yet to call
340 <function>PQconnectPoll</function>, i.e., just after the call to
341 <function>PQconnectStart</function>, behave as if it last returned
342 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
343 <function>PQconnectPoll(conn)</function> returns
344 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
345 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
346 has been successfully made.
350 At any time during connection, the status of the connection can be
351 checked by calling <function>PQstatus</>. If this call returns <symbol>CONNECTION_BAD</>, then the
352 connection procedure has failed; if the call returns <function>CONNECTION_OK</>, then the
353 connection is ready. Both of these states are equally detectable
354 from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
355 during (and only during) an asynchronous connection procedure. These
356 indicate the current stage of the connection procedure and might be useful
357 to provide feedback to the user for example. These statuses are:
360 <varlistentry id="libpq-connection-started">
361 <term><symbol>CONNECTION_STARTED</symbol></term>
364 Waiting for connection to be made.
369 <varlistentry id="libpq-connection-made">
370 <term><symbol>CONNECTION_MADE</symbol></term>
373 Connection OK; waiting to send.
378 <varlistentry id="libpq-connection-awaiting-response">
379 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
382 Waiting for a response from the server.
387 <varlistentry id="libpq-connection-auth-ok">
388 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
391 Received authentication; waiting for backend start-up to finish.
396 <varlistentry id="libpq-connection-ssl-startup">
397 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
400 Negotiating SSL encryption.
405 <varlistentry id="libpq-connection-setenv">
406 <term><symbol>CONNECTION_SETENV</symbol></term>
409 Negotiating environment-driven parameter settings.
415 Note that, although these constants will remain (in order to maintain
416 compatibility), an application should never rely upon these occurring in a
417 particular order, or at all, or on the status always being one of these
418 documented values. An application might do something like this:
420 switch(PQstatus(conn))
422 case CONNECTION_STARTED:
423 feedback = "Connecting...";
426 case CONNECTION_MADE:
427 feedback = "Connected to server...";
433 feedback = "Connecting...";
439 The <literal>connect_timeout</literal> connection parameter is ignored
440 when using <function>PQconnectPoll</function>; it is the application's
441 responsibility to decide whether an excessive amount of time has elapsed.
442 Otherwise, <function>PQconnectStart</function> followed by a
443 <function>PQconnectPoll</function> loop is equivalent to
444 <function>PQconnectdb</function>.
448 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
449 <function>PQfinish</function> when you are finished with it, in order to dispose of
450 the structure and any associated memory blocks. This must be done even if
451 the connection attempt fails or is abandoned.
456 <varlistentry id="libpq-pqconndefaults">
457 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
460 Returns the default connection options.
462 PQconninfoOption *PQconndefaults(void);
466 char *keyword; /* The keyword of the option */
467 char *envvar; /* Fallback environment variable name */
468 char *compiled; /* Fallback compiled in default value */
469 char *val; /* Option's current value, or NULL */
470 char *label; /* Label for field in connect dialog */
471 char *dispchar; /* Indicates how to display this field
472 in a connect dialog. Values are:
473 "" Display entered value as is
474 "*" Password field - hide value
475 "D" Debug option - don't show by default */
476 int dispsize; /* Field size in characters for dialog */
482 Returns a connection options array. This can be used to determine
483 all possible <function>PQconnectdb</function> options and their
484 current default values. The return value points to an array of
485 <structname>PQconninfoOption</structname> structures, which ends
486 with an entry having a null <structfield>keyword</> pointer. The
487 null pointer is returned if memory could not be allocated. Note that
488 the current default values (<structfield>val</structfield> fields)
489 will depend on environment variables and other context. A
490 missing or invalid service file will be silently ignored. Callers
491 must treat the connection options data as read-only.
495 After processing the options array, free it by passing it to
496 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
497 is leaked for each call to <function>PQconndefaults</function>.
503 <varlistentry id="libpq-pqconninfo">
504 <term><function>PQconninfo</function><indexterm><primary>PQconninfo</></></term>
507 Returns the connection options used by a live connection.
509 PQconninfoOption *PQconninfo(PGconn *conn);
514 Returns a connection options array. This can be used to determine
515 all possible <function>PQconnectdb</function> options and the
516 values that were used to connect to the server. The return
517 value points to an array of <structname>PQconninfoOption</structname>
518 structures, which ends with an entry having a null <structfield>keyword</>
519 pointer. All notes above for <function>PQconndefaults</function> also
520 apply to the result of <function>PQconninfo</function>.
527 <varlistentry id="libpq-pqconninfoparse">
528 <term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</></></term>
531 Returns parsed connection options from the provided connection string.
534 PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
539 Parses a connection string and returns the resulting options as an
540 array; or returns <symbol>NULL</> if there is a problem with the connection
541 string. This function can be used to extract
542 the <function>PQconnectdb</function> options in the provided
543 connection string. The return value points to an array of
544 <structname>PQconninfoOption</structname> structures, which ends
545 with an entry having a null <structfield>keyword</> pointer.
549 All legal options will be present in the result array, but the
550 <literal>PQconninfoOption</literal> for any option not present
551 in the connection string will have <literal>val</literal> set to
552 <literal>NULL</literal>; default values are not inserted.
556 If <literal>errmsg</> is not <symbol>NULL</>, then <literal>*errmsg</> is set
557 to <symbol>NULL</> on success, else to a <function>malloc</>'d error string explaining
558 the problem. (It is also possible for <literal>*errmsg</> to be
559 set to <symbol>NULL</> and the function to return <symbol>NULL</>;
560 this indicates an out-of-memory condition.)
564 After processing the options array, free it by passing it to
565 <function>PQconninfoFree</function>. If this is not done, some memory
566 is leaked for each call to <function>PQconninfoParse</function>.
567 Conversely, if an error occurs and <literal>errmsg</> is not <symbol>NULL</>,
568 be sure to free the error string using <function>PQfreemem</>.
574 <varlistentry id="libpq-pqfinish">
575 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
578 Closes the connection to the server. Also frees
579 memory used by the <structname>PGconn</structname> object.
581 void PQfinish(PGconn *conn);
586 Note that even if the server connection attempt fails (as
587 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
588 to free the memory used by the <structname>PGconn</structname> object.
589 The <structname>PGconn</> pointer must not be used again after
590 <function>PQfinish</function> has been called.
595 <varlistentry id="libpq-pqreset">
596 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
599 Resets the communication channel to the server.
601 void PQreset(PGconn *conn);
606 This function will close the connection
607 to the server and attempt to reestablish a new
608 connection to the same server, using all the same
609 parameters previously used. This might be useful for
610 error recovery if a working connection is lost.
615 <varlistentry id="libpq-pqresetstart">
616 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
617 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
620 Reset the communication channel to the server, in a nonblocking manner.
623 int PQresetStart(PGconn *conn);
625 PostgresPollingStatusType PQresetPoll(PGconn *conn);
630 These functions will close the connection to the server and attempt to
631 reestablish a new connection to the same server, using all the same
632 parameters previously used. This can be useful for error recovery if a
633 working connection is lost. They differ from <function>PQreset</function> (above) in that they
634 act in a nonblocking manner. These functions suffer from the same
635 restrictions as <function>PQconnectStartParams</>, <function>PQconnectStart</>
636 and <function>PQconnectPoll</>.
640 To initiate a connection reset, call
641 <function>PQresetStart</function>. If it returns 0, the reset has
642 failed. If it returns 1, poll the reset using
643 <function>PQresetPoll</function> in exactly the same way as you
644 would create the connection using <function>PQconnectPoll</function>.
649 <varlistentry id="libpq-pqpingparams">
650 <term><function>PQpingParams</function><indexterm><primary>PQpingParams</></></term>
653 <function>PQpingParams</function> reports the status of the
654 server. It accepts connection parameters identical to those of
655 <function>PQconnectdbParams</>, described above. It is not
656 necessary to supply correct user name, password, or database name
657 values to obtain the server status; however, if incorrect values
658 are provided, the server will log a failed connection attempt.
661 PGPing PQpingParams(const char * const *keywords,
662 const char * const *values,
666 The function returns one of the following values:
669 <varlistentry id="libpq-pqpingparams-pqping-ok">
670 <term><literal>PQPING_OK</literal></term>
673 The server is running and appears to be accepting connections.
678 <varlistentry id="libpq-pqpingparams-pqping-reject">
679 <term><literal>PQPING_REJECT</literal></term>
682 The server is running but is in a state that disallows connections
683 (startup, shutdown, or crash recovery).
688 <varlistentry id="libpq-pqpingparams-pqping-no-response">
689 <term><literal>PQPING_NO_RESPONSE</literal></term>
692 The server could not be contacted. This might indicate that the
693 server is not running, or that there is something wrong with the
694 given connection parameters (for example, wrong port number), or
695 that there is a network connectivity problem (for example, a
696 firewall blocking the connection request).
701 <varlistentry id="libpq-pqpingparams-pqping-no-attempt">
702 <term><literal>PQPING_NO_ATTEMPT</literal></term>
705 No attempt was made to contact the server, because the supplied
706 parameters were obviously incorrect or there was some client-side
707 problem (for example, out of memory).
718 <varlistentry id="libpq-pqping">
719 <term><function>PQping</function><indexterm><primary>PQping</></></term>
722 <function>PQping</function> reports the status of the
723 server. It accepts connection parameters identical to those of
724 <function>PQconnectdb</>, described above. It is not
725 necessary to supply correct user name, password, or database name
726 values to obtain the server status; however, if incorrect values
727 are provided, the server will log a failed connection attempt.
730 PGPing PQping(const char *conninfo);
735 The return values are the same as for <function>PQpingParams</>.
744 <sect2 id="libpq-connstring">
745 <title>Connection Strings</title>
747 <indexterm zone="libpq-connstring">
748 <primary><literal>conninfo</literal></primary>
751 <indexterm zone="libpq-connstring">
752 <primary><literal>URI</literal></primary>
756 Several <application>libpq</> functions parse a user-specified string to obtain
757 connection parameters. There are two accepted formats for these strings:
758 plain <literal>keyword = value</literal> strings
759 and <ulink url="http://www.ietf.org/rfc/rfc3986.txt">RFC
764 <title>Keyword/Value Connection Strings</title>
767 In the first format, each parameter setting is in the form
768 <literal>keyword = value</literal>. Spaces around the equal sign are
769 optional. To write an empty value, or a value containing spaces, surround it
770 with single quotes, e.g., <literal>keyword = 'a value'</literal>. Single
771 quotes and backslashes within
772 the value must be escaped with a backslash, i.e., <literal>\'</literal> and
773 <literal>\\</literal>.
779 host=localhost port=5432 dbname=mydb connect_timeout=10
784 The recognized parameter key words are listed in <xref
785 linkend="libpq-paramkeywords">.
790 <title>Connection URIs</title>
793 The general form for a connection <acronym>URI</acronym> is:
795 postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
800 The <acronym>URI</acronym> scheme designator can be either
801 <literal>postgresql://</literal> or <literal>postgres://</literal>. Each
802 of the <acronym>URI</acronym> parts is optional. The following examples
803 illustrate valid <acronym>URI</acronym> syntax uses:
806 postgresql://localhost
807 postgresql://localhost:5433
808 postgresql://localhost/mydb
809 postgresql://user@localhost
810 postgresql://user:secret@localhost
811 postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
813 Components of the hierarchical part of the <acronym>URI</acronym> can also
814 be given as parameters. For example:
816 postgresql:///mydb?host=localhost&port=5433
821 Percent-encoding may be used to include symbols with special meaning in any
822 of the <acronym>URI</acronym> parts.
826 Any connection parameters not corresponding to key words listed in <xref
827 linkend="libpq-paramkeywords"> are ignored and a warning message about them
828 is sent to <filename>stderr</filename>.
832 For improved compatibility with JDBC connection <acronym>URI</acronym>s,
833 instances of parameter <literal>ssl=true</literal> are translated into
834 <literal>sslmode=require</literal>.
838 The host part may be either host name or an IP address. To specify an
839 IPv6 host address, enclose it in square brackets:
841 postgresql://[2001:db8::1234]/database
846 The host component is interpreted as described for the parameter <xref
847 linkend="libpq-connect-host">. In particular, a Unix-domain socket
848 connection is chosen if the host part is either empty or starts with a
849 slash, otherwise a TCP/IP connection is initiated. Note, however, that the
850 slash is a reserved character in the hierarchical part of the URI. So, to
851 specify a non-standard Unix-domain socket directory, either omit the host
852 specification in the URI and specify the host as a parameter, or
853 percent-encode the path in the host component of the URI:
855 postgresql:///dbname?host=/var/lib/postgresql
856 postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
862 <sect2 id="libpq-paramkeywords">
863 <title>Parameter Key Words</title>
866 The currently recognized parameter key words are:
869 <varlistentry id="libpq-connect-host" xreflabel="host">
870 <term><literal>host</literal></term>
873 Name of host to connect to.<indexterm><primary>host name</></>
874 If this begins with a slash, it specifies Unix-domain
875 communication rather than TCP/IP communication; the value is the
876 name of the directory in which the socket file is stored. The
877 default behavior when <literal>host</literal> is not specified
878 is to connect to a Unix-domain
879 socket<indexterm><primary>Unix domain socket</></> in
880 <filename>/tmp</filename> (or whatever socket directory was specified
881 when <productname>PostgreSQL</> was built). On machines without
882 Unix-domain sockets, the default is to connect to <literal>localhost</>.
887 <varlistentry id="libpq-connect-hostaddr" xreflabel="hostaddr">
888 <term><literal>hostaddr</literal></term>
891 Numeric IP address of host to connect to. This should be in the
892 standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
893 your machine supports IPv6, you can also use those addresses.
894 TCP/IP communication is
895 always used when a nonempty string is specified for this parameter.
899 Using <literal>hostaddr</> instead of <literal>host</> allows the
900 application to avoid a host name look-up, which might be important
901 in applications with time constraints. However, a host name is
902 required for GSSAPI or SSPI authentication
903 methods, as well as for <literal>verify-full</> SSL
904 certificate verification. The following rules are used:
908 If <literal>host</> is specified without <literal>hostaddr</>,
909 a host name lookup occurs.
914 If <literal>hostaddr</> is specified without <literal>host</>,
915 the value for <literal>hostaddr</> gives the server network address.
916 The connection attempt will fail if the authentication
917 method requires a host name.
922 If both <literal>host</> and <literal>hostaddr</> are specified,
923 the value for <literal>hostaddr</> gives the server network address.
924 The value for <literal>host</> is ignored unless the
925 authentication method requires it, in which case it will be
926 used as the host name.
930 Note that authentication is likely to fail if <literal>host</>
931 is not the name of the server at network address <literal>hostaddr</>.
932 Also, note that <literal>host</> rather than <literal>hostaddr</>
933 is used to identify the connection in <filename>~/.pgpass</> (see
934 <xref linkend="libpq-pgpass">).
938 Without either a host name or host address,
939 <application>libpq</application> will connect using a
940 local Unix-domain socket; or on machines without Unix-domain
941 sockets, it will attempt to connect to <literal>localhost</>.
946 <varlistentry id="libpq-connect-port" xreflabel="port">
947 <term><literal>port</literal></term>
950 Port number to connect to at the server host, or socket file
951 name extension for Unix-domain
952 connections.<indexterm><primary>port</></>
957 <varlistentry id="libpq-connect-dbname" xreflabel="dbname">
958 <term><literal>dbname</literal></term>
961 The database name. Defaults to be the same as the user name.
962 In certain contexts, the value is checked for extended
963 formats; see <xref linkend="libpq-connstring"> for more details on
969 <varlistentry id="libpq-connect-user" xreflabel="user">
970 <term><literal>user</literal></term>
973 <productname>PostgreSQL</productname> user name to connect as.
974 Defaults to be the same as the operating system name of the user
975 running the application.
980 <varlistentry id="libpq-connect-password" xreflabel="password">
981 <term><literal>password</literal></term>
984 Password to be used if the server demands password authentication.
989 <varlistentry id="libpq-connect-connect-timeout" xreflabel="connect_timeout">
990 <term><literal>connect_timeout</literal></term>
993 Maximum wait for connection, in seconds (write as a decimal integer
994 string). Zero or not specified means wait indefinitely. It is not
995 recommended to use a timeout of less than 2 seconds.
1000 <varlistentry id="libpq-connect-client-encoding" xreflabel="client_encoding">
1001 <term><literal>client_encoding</literal></term>
1004 This sets the <varname>client_encoding</varname>
1005 configuration parameter for this connection. In addition to
1006 the values accepted by the corresponding server option, you
1007 can use <literal>auto</literal> to determine the right
1008 encoding from the current locale in the client
1009 (<envar>LC_CTYPE</envar> environment variable on Unix
1015 <varlistentry id="libpq-connect-options" xreflabel="options">
1016 <term><literal>options</literal></term>
1019 Adds command-line options to send to the server at run-time.
1020 For example, setting this to <literal>-c geqo=off</> sets the
1021 session's value of the <varname>geqo</> parameter to
1022 <literal>off</>. For a detailed discussion of the available
1023 options, consult <xref linkend="runtime-config">.
1028 <varlistentry id="libpq-connect-application-name" xreflabel="application_name">
1029 <term><literal>application_name</literal></term>
1032 Specifies a value for the <xref linkend="guc-application-name">
1033 configuration parameter.
1038 <varlistentry id="libpq-connect-fallback-application-name" xreflabel="fallback_application_name">
1039 <term><literal>fallback_application_name</literal></term>
1042 Specifies a fallback value for the <xref
1043 linkend="guc-application-name"> configuration parameter.
1044 This value will be used if no value has been given for
1045 <literal>application_name</> via a connection parameter or the
1046 <envar>PGAPPNAME</envar> environment variable. Specifying
1047 a fallback name is useful in generic utility programs that
1048 wish to set a default application name but allow it to be
1049 overridden by the user.
1054 <varlistentry id="libpq-keepalives" xreflabel="keepalives">
1055 <term><literal>keepalives</literal></term>
1058 Controls whether client-side TCP keepalives are used. The default
1059 value is 1, meaning on, but you can change this to 0, meaning off,
1060 if keepalives are not wanted. This parameter is ignored for
1061 connections made via a Unix-domain socket.
1066 <varlistentry id="libpq-keepalives-idle" xreflabel="keepalives_idle">
1067 <term><literal>keepalives_idle</literal></term>
1070 Controls the number of seconds of inactivity after which TCP should
1071 send a keepalive message to the server. A value of zero uses the
1072 system default. This parameter is ignored for connections made via a
1073 Unix-domain socket, or if keepalives are disabled. It is only supported
1074 on systems where the <symbol>TCP_KEEPIDLE</> or <symbol>TCP_KEEPALIVE</>
1075 socket option is available, and on Windows; on other systems, it has no
1081 <varlistentry id="libpq-keepalives-interval" xreflabel="keepalives_interval">
1082 <term><literal>keepalives_interval</literal></term>
1085 Controls the number of seconds after which a TCP keepalive message
1086 that is not acknowledged by the server should be retransmitted. A
1087 value of zero uses the system default. This parameter is ignored for
1088 connections made via a Unix-domain socket, or if keepalives are disabled.
1089 It is only supported on systems where the <symbol>TCP_KEEPINTVL</>
1090 socket option is available, and on Windows; on other systems, it has no
1096 <varlistentry id="libpq-keepalives-count" xreflabel="keepalives_count">
1097 <term><literal>keepalives_count</literal></term>
1100 Controls the number of TCP keepalives that can be lost before the
1101 client's connection to the server is considered dead. A value of
1102 zero uses the system default. This parameter is ignored for
1103 connections made via a Unix-domain socket, or if keepalives are disabled.
1104 It is only supported on systems where the <symbol>TCP_KEEPCNT</>
1105 socket option is available; on other systems, it has no effect.
1110 <varlistentry id="libpq-connect-tty" xreflabel="tty">
1111 <term><literal>tty</literal></term>
1114 Ignored (formerly, this specified where to send server debug output).
1119 <varlistentry id="libpq-connect-sslmode" xreflabel="sslmode">
1120 <term><literal>sslmode</literal></term>
1123 This option determines whether or with what priority a secure
1124 <acronym>SSL</> TCP/IP connection will be negotiated with the
1125 server. There are six modes:
1129 <term><literal>disable</literal></term>
1132 only try a non-<acronym>SSL</> connection
1138 <term><literal>allow</literal></term>
1141 first try a non-<acronym>SSL</> connection; if that
1142 fails, try an <acronym>SSL</> connection
1148 <term><literal>prefer</literal> (default)</term>
1151 first try an <acronym>SSL</> connection; if that fails,
1152 try a non-<acronym>SSL</> connection
1158 <term><literal>require</literal></term>
1161 only try an <acronym>SSL</> connection. If a root CA
1162 file is present, verify the certificate in the same way as
1163 if <literal>verify-ca</literal> was specified
1169 <term><literal>verify-ca</literal></term>
1172 only try an <acronym>SSL</> connection, and verify that
1173 the server certificate is issued by a trusted
1174 certificate authority (<acronym>CA</>)
1180 <term><literal>verify-full</literal></term>
1183 only try an <acronym>SSL</> connection, verify that the
1184 server certificate is issued by a
1185 trusted <acronym>CA</> and that the requested server host name
1186 matches that in the certificate
1192 See <xref linkend="libpq-ssl"> for a detailed description of how
1197 <literal>sslmode</> is ignored for Unix domain socket
1199 If <productname>PostgreSQL</> is compiled without SSL support,
1200 using options <literal>require</>, <literal>verify-ca</>, or
1201 <literal>verify-full</> will cause an error, while
1202 options <literal>allow</> and <literal>prefer</> will be
1203 accepted but <application>libpq</> will not actually attempt
1205 connection.<indexterm><primary>SSL</><secondary
1206 sortas="libpq">with libpq</></indexterm>
1211 <varlistentry id="libpq-connect-requiressl" xreflabel="requiressl">
1212 <term><literal>requiressl</literal></term>
1215 This option is deprecated in favor of the <literal>sslmode</>
1220 If set to 1, an <acronym>SSL</acronym> connection to the server
1221 is required (this is equivalent to <literal>sslmode</>
1222 <literal>require</>). <application>libpq</> will then refuse
1223 to connect if the server does not accept an
1224 <acronym>SSL</acronym> connection. If set to 0 (default),
1225 <application>libpq</> will negotiate the connection type with
1226 the server (equivalent to <literal>sslmode</>
1227 <literal>prefer</>). This option is only available if
1228 <productname>PostgreSQL</> is compiled with SSL support.
1233 <varlistentry id="libpq-connect-sslcompression" xreflabel="sslcompression">
1234 <term><literal>sslcompression</literal></term>
1237 If set to 1 (default), data sent over SSL connections will be
1238 compressed (this requires <productname>OpenSSL</> version
1240 If set to 0, compression will be disabled (this requires
1241 <productname>OpenSSL</> 1.0.0 or later).
1242 This parameter is ignored if a connection without SSL is made,
1243 or if the version of <productname>OpenSSL</> used does not support
1247 Compression uses CPU time, but can improve throughput if
1248 the network is the bottleneck.
1249 Disabling compression can improve response time and throughput
1250 if CPU performance is the limiting factor.
1255 <varlistentry id="libpq-connect-sslcert" xreflabel="sslcert">
1256 <term><literal>sslcert</literal></term>
1259 This parameter specifies the file name of the client SSL
1260 certificate, replacing the default
1261 <filename>~/.postgresql/postgresql.crt</>.
1262 This parameter is ignored if an SSL connection is not made.
1267 <varlistentry id="libpq-connect-sslkey" xreflabel="sslkey">
1268 <term><literal>sslkey</literal></term>
1271 This parameter specifies the location for the secret key used for
1272 the client certificate. It can either specify a file name that will
1273 be used instead of the default
1274 <filename>~/.postgresql/postgresql.key</>, or it can specify a key
1275 obtained from an external <quote>engine</> (engines are
1276 <productname>OpenSSL</> loadable modules). An external engine
1277 specification should consist of a colon-separated engine name and
1278 an engine-specific key identifier. This parameter is ignored if an
1279 SSL connection is not made.
1284 <varlistentry id="libpq-connect-sslrootcert" xreflabel="sslrootcert">
1285 <term><literal>sslrootcert</literal></term>
1288 This parameter specifies the name of a file containing SSL
1289 certificate authority (<acronym>CA</>) certificate(s).
1290 If the file exists, the server's certificate will be verified
1291 to be signed by one of these authorities. The default is
1292 <filename>~/.postgresql/root.crt</>.
1297 <varlistentry id="libpq-connect-sslcrl" xreflabel="sslcrl">
1298 <term><literal>sslcrl</literal></term>
1301 This parameter specifies the file name of the SSL certificate
1302 revocation list (CRL). Certificates listed in this file, if it
1303 exists, will be rejected while attempting to authenticate the
1304 server's certificate. The default is
1305 <filename>~/.postgresql/root.crl</>.
1310 <varlistentry id="libpq-connect-requirepeer" xreflabel="requirepeer">
1311 <term><literal>requirepeer</literal></term>
1314 This parameter specifies the operating-system user name of the
1315 server, for example <literal>requirepeer=postgres</literal>.
1316 When making a Unix-domain socket connection, if this
1317 parameter is set, the client checks at the beginning of the
1318 connection that the server process is running under the specified
1319 user name; if it is not, the connection is aborted with an error.
1320 This parameter can be used to provide server authentication similar
1321 to that available with SSL certificates on TCP/IP connections.
1322 (Note that if the Unix-domain socket is in
1323 <filename>/tmp</filename> or another publicly writable location,
1324 any user could start a server listening there. Use this parameter
1325 to ensure that you are connected to a server run by a trusted user.)
1326 This option is only supported on platforms for which the
1327 <literal>peer</> authentication method is implemented; see
1328 <xref linkend="auth-peer">.
1333 <varlistentry id="libpq-connect-krbsrvname" xreflabel="krbsrvname">
1334 <term><literal>krbsrvname</literal></term>
1337 Kerberos service name to use when authenticating with GSSAPI.
1338 This must match the service name specified in the server
1339 configuration for Kerberos authentication to succeed. (See also
1340 <xref linkend="gssapi-auth">.)
1345 <varlistentry id="libpq-connect-gsslib" xreflabel="gsslib">
1346 <term><literal>gsslib</literal></term>
1349 GSS library to use for GSSAPI authentication. Only used on Windows.
1350 Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
1351 library for authentication instead of the default SSPI.
1356 <varlistentry id="libpq-connect-service" xreflabel="service">
1357 <term><literal>service</literal></term>
1360 Service name to use for additional parameters. It specifies a service
1361 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
1362 This allows applications to specify only a service name so connection parameters
1363 can be centrally maintained. See <xref linkend="libpq-pgservice">.
1372 <sect1 id="libpq-status">
1373 <title>Connection Status Functions</title>
1376 These functions can be used to interrogate the status
1377 of an existing database connection object.
1382 <indexterm><primary>libpq-fe.h</></>
1383 <indexterm><primary>libpq-int.h</></>
1384 <application>libpq</application> application programmers should be careful to
1385 maintain the <structname>PGconn</structname> abstraction. Use the accessor
1386 functions described below to get at the contents of <structname>PGconn</structname>.
1387 Reference to internal <structname>PGconn</structname> fields using
1388 <filename>libpq-int.h</> is not recommended because they are subject to change
1394 The following functions return parameter values established at connection.
1395 These values are fixed for the life of the <structname>PGconn</> object.
1398 <varlistentry id="libpq-pqdb">
1400 <function>PQdb</function>
1402 <primary>PQdb</primary>
1408 Returns the database name of the connection.
1410 char *PQdb(const PGconn *conn);
1416 <varlistentry id="libpq-pquser">
1418 <function>PQuser</function>
1420 <primary>PQuser</primary>
1426 Returns the user name of the connection.
1428 char *PQuser(const PGconn *conn);
1434 <varlistentry id="libpq-pqpass">
1436 <function>PQpass</function>
1438 <primary>PQpass</primary>
1444 Returns the password of the connection.
1446 char *PQpass(const PGconn *conn);
1452 <varlistentry id="libpq-pqhost">
1454 <function>PQhost</function>
1456 <primary>PQhost</primary>
1462 Returns the server host name of the connection.
1464 char *PQhost(const PGconn *conn);
1470 <varlistentry id="libpq-pqport">
1472 <function>PQport</function>
1474 <primary>PQport</primary>
1480 Returns the port of the connection.
1483 char *PQport(const PGconn *conn);
1489 <varlistentry id="libpq-pqtty">
1491 <function>PQtty</function>
1493 <primary>PQtty</primary>
1499 Returns the debug <acronym>TTY</acronym> of the connection.
1500 (This is obsolete, since the server no longer pays attention
1501 to the <acronym>TTY</acronym> setting, but the function remains
1502 for backward compatibility.)
1505 char *PQtty(const PGconn *conn);
1511 <varlistentry id="libpq-pqoptions">
1513 <function>PQoptions</function>
1515 <primary>PQoptions</primary>
1521 Returns the command-line options passed in the connection request.
1523 char *PQoptions(const PGconn *conn);
1532 The following functions return status data that can change as operations
1533 are executed on the <structname>PGconn</> object.
1536 <varlistentry id="libpq-pqstatus">
1538 <function>PQstatus</function>
1540 <primary>PQstatus</primary>
1546 Returns the status of the connection.
1548 ConnStatusType PQstatus(const PGconn *conn);
1553 The status can be one of a number of values. However, only two of
1554 these are seen outside of an asynchronous connection procedure:
1555 <literal>CONNECTION_OK</literal> and
1556 <literal>CONNECTION_BAD</literal>. A good connection to the database
1557 has the status <literal>CONNECTION_OK</literal>. A failed
1558 connection attempt is signaled by status
1559 <literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
1560 remain so until <function>PQfinish</function>, but a communications
1561 failure might result in the status changing to
1562 <literal>CONNECTION_BAD</literal> prematurely. In that case the
1563 application could try to recover by calling
1564 <function>PQreset</function>.
1568 See the entry for <function>PQconnectStartParams</>, <function>PQconnectStart</>
1569 and <function>PQconnectPoll</> with regards to other status codes that
1575 <varlistentry id="libpq-pqtransactionstatus">
1577 <function>PQtransactionStatus</function>
1579 <primary>PQtransactionStatus</primary>
1585 Returns the current in-transaction status of the server.
1588 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
1591 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
1592 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
1593 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
1594 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
1595 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
1596 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
1597 has been sent to the server and not yet completed.
1602 <varlistentry id="libpq-pqparameterstatus">
1604 <function>PQparameterStatus</function>
1606 <primary>PQparameterStatus</primary>
1612 Looks up a current parameter setting of the server.
1615 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
1618 Certain parameter values are reported by the server automatically at
1619 connection startup or whenever their values change.
1620 <function>PQparameterStatus</> can be used to interrogate these settings.
1621 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
1622 if the parameter is not known.
1626 Parameters reported as of the current release include
1627 <varname>server_version</>,
1628 <varname>server_encoding</>,
1629 <varname>client_encoding</>,
1630 <varname>application_name</>,
1631 <varname>is_superuser</>,
1632 <varname>session_authorization</>,
1633 <varname>DateStyle</>,
1634 <varname>IntervalStyle</>,
1635 <varname>TimeZone</>,
1636 <varname>integer_datetimes</>, and
1637 <varname>standard_conforming_strings</>.
1638 (<varname>server_encoding</>, <varname>TimeZone</>, and
1639 <varname>integer_datetimes</> were not reported by releases before 8.0;
1640 <varname>standard_conforming_strings</> was not reported by releases
1642 <varname>IntervalStyle</> was not reported by releases before 8.4;
1643 <varname>application_name</> was not reported by releases before 9.0.)
1645 <varname>server_version</>,
1646 <varname>server_encoding</> and
1647 <varname>integer_datetimes</>
1648 cannot change after startup.
1652 Pre-3.0-protocol servers do not report parameter settings, but
1653 <application>libpq</> includes logic to obtain values for
1654 <varname>server_version</> and <varname>client_encoding</> anyway.
1655 Applications are encouraged to use <function>PQparameterStatus</>
1656 rather than <foreignphrase>ad hoc</> code to determine these values.
1657 (Beware however that on a pre-3.0 connection, changing
1658 <varname>client_encoding</> via <command>SET</> after connection
1659 startup will not be reflected by <function>PQparameterStatus</>.)
1660 For <varname>server_version</>, see also
1661 <function>PQserverVersion</>, which returns the information in a
1662 numeric form that is much easier to compare against.
1666 If no value for <varname>standard_conforming_strings</> is reported,
1667 applications can assume it is <literal>off</>, that is, backslashes
1668 are treated as escapes in string literals. Also, the presence of
1669 this parameter can be taken as an indication that the escape string
1670 syntax (<literal>E'...'</>) is accepted.
1674 Although the returned pointer is declared <literal>const</>, it in fact
1675 points to mutable storage associated with the <literal>PGconn</> structure.
1676 It is unwise to assume the pointer will remain valid across queries.
1681 <varlistentry id="libpq-pqprotocolversion">
1683 <function>PQprotocolVersion</function>
1685 <primary>PQprotocolVersion</primary>
1691 Interrogates the frontend/backend protocol being used.
1693 int PQprotocolVersion(const PGconn *conn);
1695 Applications might wish to use this function to determine whether certain
1696 features are supported. Currently, the possible values are 2 (2.0
1697 protocol), 3 (3.0 protocol), or zero (connection bad). The
1698 protocol version will
1699 not change after connection startup is complete, but it could
1700 theoretically change during a connection reset. The 3.0 protocol
1701 will normally be used when communicating with
1702 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
1703 support only protocol 2.0. (Protocol 1.0 is obsolete and not
1704 supported by <application>libpq</application>.)
1709 <varlistentry id="libpq-pqserverversion">
1711 <function>PQserverVersion</function>
1713 <primary>PQserverVersion</primary>
1719 Returns an integer representing the backend version.
1721 int PQserverVersion(const PGconn *conn);
1723 Applications might use this function to determine the version of the database
1724 server they are connected to. The number is formed by converting
1725 the major, minor, and revision numbers into two-decimal-digit
1726 numbers and appending them together. For example, version 8.1.5
1727 will be returned as 80105, and version 8.2 will be returned as
1728 80200 (leading zeroes are not shown). Zero is returned if the
1734 <varlistentry id="libpq-pqerrormessage">
1736 <function>PQerrorMessage</function>
1738 <primary>PQerrorMessage</primary>
1744 <indexterm><primary>error message</></> Returns the error message
1745 most recently generated by an operation on the connection.
1748 char *PQerrorMessage(const PGconn *conn);
1754 Nearly all <application>libpq</> functions will set a message for
1755 <function>PQerrorMessage</function> if they fail. Note that by
1756 <application>libpq</application> convention, a nonempty
1757 <function>PQerrorMessage</function> result can consist of multiple lines,
1758 and will include a trailing newline. The caller should not free
1759 the result directly. It will be freed when the associated
1760 <structname>PGconn</> handle is passed to
1761 <function>PQfinish</function>. The result string should not be
1762 expected to remain the same across operations on the
1763 <literal>PGconn</> structure.
1768 <varlistentry id="libpq-pqsocket">
1769 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1772 Obtains the file descriptor number of the connection socket to
1773 the server. A valid descriptor will be greater than or equal
1774 to 0; a result of -1 indicates that no server connection is
1775 currently open. (This will not change during normal operation,
1776 but could change during connection setup or reset.)
1779 int PQsocket(const PGconn *conn);
1786 <varlistentry id="libpq-pqbackendpid">
1787 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1790 Returns the process <acronym>ID</acronym> (PID)<indexterm>
1792 <secondary>determining PID of server process</>
1793 <tertiary>in libpq</>
1795 of the backend process handling this connection.
1798 int PQbackendPID(const PGconn *conn);
1803 The backend <acronym>PID</acronym> is useful for debugging
1804 purposes and for comparison to <command>NOTIFY</command>
1805 messages (which include the <acronym>PID</acronym> of the
1806 notifying backend process). Note that the
1807 <acronym>PID</acronym> belongs to a process executing on the
1808 database server host, not the local host!
1813 <varlistentry id="libpq-pqconnectionneedspassword">
1814 <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
1817 Returns true (1) if the connection authentication method
1818 required a password, but none was available.
1819 Returns false (0) if not.
1822 int PQconnectionNeedsPassword(const PGconn *conn);
1827 This function can be applied after a failed connection attempt
1828 to decide whether to prompt the user for a password.
1833 <varlistentry id="libpq-pqconnectionusedpassword">
1834 <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
1837 Returns true (1) if the connection authentication method
1838 used a password. Returns false (0) if not.
1841 int PQconnectionUsedPassword(const PGconn *conn);
1846 This function can be applied after either a failed or successful
1847 connection attempt to detect whether the server demanded a password.
1852 <varlistentry id="libpq-pqgetssl">
1853 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1856 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1857 Returns the SSL structure used in the connection, or null
1858 if SSL is not in use.
1861 void *PQgetssl(const PGconn *conn);
1866 This structure can be used to verify encryption levels, check server
1867 certificates, and more. Refer to the <productname>OpenSSL</>
1868 documentation for information about this structure.
1872 The actual return value is of type <type>SSL *</type>,
1873 where <type>SSL</type> is a type defined by
1874 the <productname>OpenSSL</productname> library, but it is not declared
1875 this way to avoid requiring the <productname>OpenSSL</productname>
1876 header files. To use this function, code along the following lines
1878 <programlisting><![CDATA[
1879 #include <libpq-fe.h>
1880 #include <openssl/ssl.h>
1886 dbconn = PQconnectdb(...);
1889 ssl = PQgetssl(dbconn);
1892 /* use OpenSSL functions to access ssl */
1894 ]]></programlisting>
1904 <sect1 id="libpq-exec">
1905 <title>Command Execution Functions</title>
1908 Once a connection to a database server has been successfully
1909 established, the functions described here are used to perform
1910 SQL queries and commands.
1913 <sect2 id="libpq-exec-main">
1914 <title>Main Functions</title>
1918 <varlistentry id="libpq-pqexec">
1920 <function>PQexec</function>
1922 <primary>PQexec</primary>
1928 Submits a command to the server and waits for the result.
1931 PGresult *PQexec(PGconn *conn, const char *command);
1936 Returns a <structname>PGresult</structname> pointer or possibly a null
1937 pointer. A non-null pointer will generally be returned except in
1938 out-of-memory conditions or serious errors such as inability to send
1939 the command to the server. The <function>PQresultStatus</> function
1940 should be called to check the return value for any errors (including
1941 the value of a null pointer, in which case it will return
1942 <symbol>PGRES_FATAL_ERROR</symbol>). Use
1943 <function>PQerrorMessage</function> to get more information about such
1950 The command string can include multiple SQL commands
1951 (separated by semicolons). Multiple queries sent in a single
1952 <function>PQexec</> call are processed in a single transaction, unless
1953 there are explicit <command>BEGIN</command>/<command>COMMIT</command>
1954 commands included in the query string to divide it into multiple
1955 transactions. Note however that the returned
1956 <structname>PGresult</structname> structure describes only the result
1957 of the last command executed from the string. Should one of the
1958 commands fail, processing of the string stops with it and the returned
1959 <structname>PGresult</structname> describes the error condition.
1964 <varlistentry id="libpq-pqexecparams">
1966 <function>PQexecParams</function>
1968 <primary>PQexecParams</primary>
1974 Submits a command to the server and waits for the result,
1975 with the ability to pass parameters separately from the SQL
1979 PGresult *PQexecParams(PGconn *conn,
1980 const char *command,
1982 const Oid *paramTypes,
1983 const char * const *paramValues,
1984 const int *paramLengths,
1985 const int *paramFormats,
1991 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1992 functionality: parameter values can be specified separately from the command
1993 string proper, and query results can be requested in either text or binary
1994 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1995 connections; it will fail when using protocol 2.0.
1999 The function arguments are:
2003 <term><parameter>conn</parameter></term>
2007 The connection object to send the command through.
2013 <term><parameter>command</parameter></term>
2016 The SQL command string to be executed. If parameters are used,
2017 they are referred to in the command string as <literal>$1</>,
2018 <literal>$2</>, etc.
2024 <term><parameter>nParams</parameter></term>
2027 The number of parameters supplied; it is the length of the arrays
2028 <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
2029 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
2030 array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
2037 <term><parameter>paramTypes[]</parameter></term>
2040 Specifies, by OID, the data types to be assigned to the
2041 parameter symbols. If <parameter>paramTypes</> is
2042 <symbol>NULL</symbol>, or any particular element in the array
2043 is zero, the server infers a data type for the parameter symbol
2044 in the same way it would do for an untyped literal string.
2050 <term><parameter>paramValues[]</parameter></term>
2053 Specifies the actual values of the parameters. A null pointer
2054 in this array means the corresponding parameter is null;
2055 otherwise the pointer points to a zero-terminated text string
2056 (for text format) or binary data in the format expected by the
2057 server (for binary format).
2063 <term><parameter>paramLengths[]</parameter></term>
2066 Specifies the actual data lengths of binary-format parameters.
2067 It is ignored for null parameters and text-format parameters.
2068 The array pointer can be null when there are no binary parameters.
2074 <term><parameter>paramFormats[]</parameter></term>
2077 Specifies whether parameters are text (put a zero in the
2078 array entry for the corresponding parameter) or binary (put
2079 a one in the array entry for the corresponding parameter).
2080 If the array pointer is null then all parameters are presumed
2084 Values passed in binary format require knowledge of
2085 the internal representation expected by the backend.
2086 For example, integers must be passed in network byte
2087 order. Passing <type>numeric</> values requires
2088 knowledge of the server storage format, as implemented
2090 <filename>src/backend/utils/adt/numeric.c::numeric_send()</> and
2091 <filename>src/backend/utils/adt/numeric.c::numeric_recv()</>.
2097 <term><parameter>resultFormat</parameter></term>
2100 Specify zero to obtain results in text format, or one to obtain
2101 results in binary format. (There is not currently a provision
2102 to obtain different result columns in different formats,
2103 although that is possible in the underlying protocol.)
2115 The primary advantage of <function>PQexecParams</> over
2116 <function>PQexec</> is that parameter values can be separated from the
2117 command string, thus avoiding the need for tedious and error-prone
2118 quoting and escaping.
2122 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
2123 one SQL command in the given string. (There can be semicolons in it,
2124 but not more than one nonempty command.) This is a limitation of the
2125 underlying protocol, but has some usefulness as an extra defense against
2126 SQL-injection attacks.
2131 Specifying parameter types via OIDs is tedious, particularly if you prefer
2132 not to hard-wire particular OID values into your program. However, you can
2133 avoid doing so even in cases where the server by itself cannot determine the
2134 type of the parameter, or chooses a different type than you want. In the
2135 SQL command text, attach an explicit cast to the parameter symbol to show what
2136 data type you will send. For example:
2138 SELECT * FROM mytable WHERE x = $1::bigint;
2140 This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
2141 by default it would be assigned the same type as <literal>x</>. Forcing the
2142 parameter type decision, either this way or by specifying a numeric type OID,
2143 is strongly recommended when sending parameter values in binary format, because
2144 binary format has less redundancy than text format and so there is less chance
2145 that the server will detect a type mismatch mistake for you.
2151 <varlistentry id="libpq-pqprepare">
2152 <term><function>PQprepare</function>
2154 <primary>PQprepare</primary>
2160 Submits a request to create a prepared statement with the
2161 given parameters, and waits for completion.
2163 PGresult *PQprepare(PGconn *conn,
2164 const char *stmtName,
2167 const Oid *paramTypes);
2172 <function>PQprepare</> creates a prepared statement for later
2173 execution with <function>PQexecPrepared</>. This feature allows
2174 commands that will be used repeatedly to be parsed and planned just
2175 once, rather than each time they are executed.
2176 <function>PQprepare</> is supported only in protocol 3.0 and later
2177 connections; it will fail when using protocol 2.0.
2181 The function creates a prepared statement named
2182 <parameter>stmtName</> from the <parameter>query</> string, which
2183 must contain a single SQL command. <parameter>stmtName</> can be
2184 <literal>""</> to create an unnamed statement, in which case any
2185 pre-existing unnamed statement is automatically replaced; otherwise
2186 it is an error if the statement name is already defined in the
2187 current session. If any parameters are used, they are referred
2188 to in the query as <literal>$1</>, <literal>$2</>, etc.
2189 <parameter>nParams</> is the number of parameters for which types
2190 are pre-specified in the array <parameter>paramTypes[]</>. (The
2191 array pointer can be <symbol>NULL</symbol> when
2192 <parameter>nParams</> is zero.) <parameter>paramTypes[]</>
2193 specifies, by OID, the data types to be assigned to the parameter
2194 symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
2195 or any particular element in the array is zero, the server assigns
2196 a data type to the parameter symbol in the same way it would do
2197 for an untyped literal string. Also, the query can use parameter
2198 symbols with numbers higher than <parameter>nParams</>; data types
2199 will be inferred for these symbols as well. (See
2200 <function>PQdescribePrepared</function> for a means to find out
2201 what data types were inferred.)
2205 As with <function>PQexec</>, the result is normally a
2206 <structname>PGresult</structname> object whose contents indicate
2207 server-side success or failure. A null result indicates
2208 out-of-memory or inability to send the command at all. Use
2209 <function>PQerrorMessage</function> to get more information about
2216 Prepared statements for use with <function>PQexecPrepared</> can also
2217 be created by executing SQL <xref linkend="sql-prepare">
2218 statements. Also, although there is no <application>libpq</>
2219 function for deleting a prepared statement, the SQL <xref
2220 linkend="sql-deallocate"> statement
2221 can be used for that purpose.
2226 <varlistentry id="libpq-pqexecprepared">
2228 <function>PQexecPrepared</function>
2230 <primary>PQexecPrepared</primary>
2236 Sends a request to execute a prepared statement with given
2237 parameters, and waits for the result.
2239 PGresult *PQexecPrepared(PGconn *conn,
2240 const char *stmtName,
2242 const char * const *paramValues,
2243 const int *paramLengths,
2244 const int *paramFormats,
2250 <function>PQexecPrepared</> is like <function>PQexecParams</>,
2251 but the command to be executed is specified by naming a
2252 previously-prepared statement, instead of giving a query string.
2253 This feature allows commands that will be used repeatedly to be
2254 parsed and planned just once, rather than each time they are
2255 executed. The statement must have been prepared previously in
2256 the current session. <function>PQexecPrepared</> is supported
2257 only in protocol 3.0 and later connections; it will fail when
2262 The parameters are identical to <function>PQexecParams</>, except that the
2263 name of a prepared statement is given instead of a query string, and the
2264 <parameter>paramTypes[]</> parameter is not present (it is not needed since
2265 the prepared statement's parameter types were determined when it was created).
2270 <varlistentry id="libpq-pqdescribeprepared">
2272 <function>PQdescribePrepared</function>
2274 <primary>PQdescribePrepared</primary>
2280 Submits a request to obtain information about the specified
2281 prepared statement, and waits for completion.
2283 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
2288 <function>PQdescribePrepared</> allows an application to obtain
2289 information about a previously prepared statement.
2290 <function>PQdescribePrepared</> is supported only in protocol 3.0
2291 and later connections; it will fail when using protocol 2.0.
2295 <parameter>stmtName</> can be <literal>""</> or <symbol>NULL</> to reference
2296 the unnamed statement, otherwise it must be the name of an existing
2297 prepared statement. On success, a <structname>PGresult</> with
2298 status <literal>PGRES_COMMAND_OK</literal> is returned. The
2299 functions <function>PQnparams</function> and
2300 <function>PQparamtype</function> can be applied to this
2301 <structname>PGresult</> to obtain information about the parameters
2302 of the prepared statement, and the functions
2303 <function>PQnfields</function>, <function>PQfname</function>,
2304 <function>PQftype</function>, etc provide information about the
2305 result columns (if any) of the statement.
2310 <varlistentry id="libpq-pqdescribeportal">
2312 <function>PQdescribePortal</function>
2314 <primary>PQdescribePortal</primary>
2320 Submits a request to obtain information about the specified
2321 portal, and waits for completion.
2323 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
2328 <function>PQdescribePortal</> allows an application to obtain
2329 information about a previously created portal.
2330 (<application>libpq</> does not provide any direct access to
2331 portals, but you can use this function to inspect the properties
2332 of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
2333 <function>PQdescribePortal</> is supported only in protocol 3.0
2334 and later connections; it will fail when using protocol 2.0.
2338 <parameter>portalName</> can be <literal>""</> or <symbol>NULL</> to reference
2339 the unnamed portal, otherwise it must be the name of an existing
2340 portal. On success, a <structname>PGresult</> with status
2341 <literal>PGRES_COMMAND_OK</literal> is returned. The functions
2342 <function>PQnfields</function>, <function>PQfname</function>,
2343 <function>PQftype</function>, etc can be applied to the
2344 <structname>PGresult</> to obtain information about the result
2345 columns (if any) of the portal.
2353 The <structname>PGresult</structname><indexterm><primary>PGresult</></>
2354 structure encapsulates the result returned by the server.
2355 <application>libpq</application> application programmers should be
2356 careful to maintain the <structname>PGresult</structname> abstraction.
2357 Use the accessor functions below to get at the contents of
2358 <structname>PGresult</structname>. Avoid directly referencing the
2359 fields of the <structname>PGresult</structname> structure because they
2360 are subject to change in the future.
2363 <varlistentry id="libpq-pqresultstatus">
2365 <function>PQresultStatus</function>
2367 <primary>PQresultStatus</primary>
2373 Returns the result status of the command.
2375 ExecStatusType PQresultStatus(const PGresult *res);
2380 <function>PQresultStatus</function> can return one of the following values:
2383 <varlistentry id="libpq-pgres-empty-query">
2384 <term><literal>PGRES_EMPTY_QUERY</literal></term>
2387 The string sent to the server was empty.
2392 <varlistentry id="libpq-pgres-command-ok">
2393 <term><literal>PGRES_COMMAND_OK</literal></term>
2396 Successful completion of a command returning no data.
2401 <varlistentry id="libpq-pgres-tuples-ok">
2402 <term><literal>PGRES_TUPLES_OK</literal></term>
2405 Successful completion of a command returning data (such as
2406 a <command>SELECT</> or <command>SHOW</>).
2411 <varlistentry id="libpq-pgres-copy-out">
2412 <term><literal>PGRES_COPY_OUT</literal></term>
2415 Copy Out (from server) data transfer started.
2420 <varlistentry id="libpq-pgres-copy-in">
2421 <term><literal>PGRES_COPY_IN</literal></term>
2424 Copy In (to server) data transfer started.
2429 <varlistentry id="libpq-pgres-bad-response">
2430 <term><literal>PGRES_BAD_RESPONSE</literal></term>
2433 The server's response was not understood.
2438 <varlistentry id="libpq-pgres-nonfatal-error">
2439 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
2442 A nonfatal error (a notice or warning) occurred.
2447 <varlistentry id="libpq-pgres-fatal-error">
2448 <term><literal>PGRES_FATAL_ERROR</literal></term>
2451 A fatal error occurred.
2456 <varlistentry id="libpq-pgres-copy-both">
2457 <term><literal>PGRES_COPY_BOTH</literal></term>
2460 Copy In/Out (to and from server) data transfer started. This
2461 feature is currently used only for streaming replication,
2462 so this status should not occur in ordinary applications.
2467 <varlistentry id="libpq-pgres-single-tuple">
2468 <term><literal>PGRES_SINGLE_TUPLE</literal></term>
2471 The <structname>PGresult</> contains a single result tuple
2472 from the current command. This status occurs only when
2473 single-row mode has been selected for the query
2474 (see <xref linkend="libpq-single-row-mode">).
2480 If the result status is <literal>PGRES_TUPLES_OK</literal> or
2481 <literal>PGRES_SINGLE_TUPLE</literal>, then
2482 the functions described below can be used to retrieve the rows
2483 returned by the query. Note that a <command>SELECT</command>
2484 command that happens to retrieve zero rows still shows
2485 <literal>PGRES_TUPLES_OK</literal>.
2486 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
2487 return rows (<command>INSERT</command> or <command>UPDATE</command>
2488 without a <literal>RETURNING</literal> clause,
2489 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
2490 indicate a bug in the client software.
2494 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
2495 never be returned directly by <function>PQexec</function> or other
2496 query execution functions; results of this kind are instead passed
2497 to the notice processor (see <xref
2498 linkend="libpq-notice-processing">).
2503 <varlistentry id="libpq-pqresstatus">
2505 <function>PQresStatus</function>
2507 <primary>PQresStatus</primary>
2513 Converts the enumerated type returned by
2514 <function>PQresultStatus</> into a string constant describing the
2515 status code. The caller should not free the result.
2518 char *PQresStatus(ExecStatusType status);
2524 <varlistentry id="libpq-pqresulterrormessage">
2526 <function>PQresultErrorMessage</function>
2528 <primary>PQresultErrorMessage</primary>
2534 Returns the error message associated with the command, or an empty string
2535 if there was no error.
2537 char *PQresultErrorMessage(const PGresult *res);
2539 If there was an error, the returned string will include a trailing
2540 newline. The caller should not free the result directly. It will
2541 be freed when the associated <structname>PGresult</> handle is
2542 passed to <function>PQclear</function>.
2546 Immediately following a <function>PQexec</function> or
2547 <function>PQgetResult</function> call,
2548 <function>PQerrorMessage</function> (on the connection) will return
2549 the same string as <function>PQresultErrorMessage</function> (on
2550 the result). However, a <structname>PGresult</structname> will
2551 retain its error message until destroyed, whereas the connection's
2552 error message will change when subsequent operations are done.
2553 Use <function>PQresultErrorMessage</function> when you want to
2554 know the status associated with a particular
2555 <structname>PGresult</structname>; use
2556 <function>PQerrorMessage</function> when you want to know the
2557 status from the latest operation on the connection.
2562 <varlistentry id="libpq-pqresulterrorfield">
2563 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
2566 Returns an individual field of an error report.
2568 char *PQresultErrorField(const PGresult *res, int fieldcode);
2570 <parameter>fieldcode</> is an error field identifier; see the symbols
2571 listed below. <symbol>NULL</symbol> is returned if the
2572 <structname>PGresult</structname> is not an error or warning result,
2573 or does not include the specified field. Field values will normally
2574 not include a trailing newline. The caller should not free the
2575 result directly. It will be freed when the
2576 associated <structname>PGresult</> handle is passed to
2577 <function>PQclear</function>.
2581 The following field codes are available:
2583 <varlistentry id="libpq-pg-diag-severity">
2584 <term><symbol>PG_DIAG_SEVERITY</></term>
2587 The severity; the field contents are <literal>ERROR</>,
2588 <literal>FATAL</>, or <literal>PANIC</> (in an error message),
2589 or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
2590 <literal>INFO</>, or <literal>LOG</> (in a notice message), or
2591 a localized translation of one of these. Always present.
2596 <varlistentry id="libpq-pg-diag-sqlstate">
2598 <symbol>PG_DIAG_SQLSTATE</>
2600 <primary>error codes</primary>
2601 <secondary>libpq</secondary>
2606 The SQLSTATE code for the error. The SQLSTATE code identifies
2607 the type of error that has occurred; it can be used by
2608 front-end applications to perform specific operations (such
2609 as error handling) in response to a particular database error.
2610 For a list of the possible SQLSTATE codes, see <xref
2611 linkend="errcodes-appendix">. This field is not localizable,
2612 and is always present.
2617 <varlistentry id="libpq-pg-diag-message-primary">
2618 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
2621 The primary human-readable error message (typically one line).
2627 <varlistentry id="libpq-pg-diag-message-detail">
2628 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
2631 Detail: an optional secondary error message carrying more
2632 detail about the problem. Might run to multiple lines.
2637 <varlistentry id="libpq-pg-diag-message-hint">
2638 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
2641 Hint: an optional suggestion what to do about the problem.
2642 This is intended to differ from detail in that it offers advice
2643 (potentially inappropriate) rather than hard facts. Might
2644 run to multiple lines.
2649 <varlistentry id="libpq-pg-diag-statement-position">
2650 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
2653 A string containing a decimal integer indicating an error cursor
2654 position as an index into the original statement string. The
2655 first character has index 1, and positions are measured in
2656 characters not bytes.
2661 <varlistentry id="libpq-pg-diag-internal-position">
2662 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
2665 This is defined the same as the
2666 <symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
2667 when the cursor position refers to an internally generated
2668 command rather than the one submitted by the client. The
2669 <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
2675 <varlistentry id="libpq-pg-diag-internal-query">
2676 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
2679 The text of a failed internally-generated command. This could
2680 be, for example, a SQL query issued by a PL/pgSQL function.
2685 <varlistentry id="libpq-pg-diag-context">
2686 <term><symbol>PG_DIAG_CONTEXT</></term>
2689 An indication of the context in which the error occurred.
2690 Presently this includes a call stack traceback of active
2691 procedural language functions and internally-generated queries.
2692 The trace is one entry per line, most recent first.
2697 <varlistentry id="libpq-pg-diag-schema-name">
2698 <term><symbol>PG_DIAG_SCHEMA_NAME</></term>
2701 If the error was associated with a specific database object,
2702 the name of the schema containing that object, if any.
2707 <varlistentry id="libpq-pg-diag-table-name">
2708 <term><symbol>PG_DIAG_TABLE_NAME</></term>
2711 If the error was associated with a specific table, the name of the
2712 table. (Refer to the schema name field for the name of the
2718 <varlistentry id="libpq-pg-diag-column-name">
2719 <term><symbol>PG_DIAG_COLUMN_NAME</></term>
2722 If the error was associated with a specific table column, the name
2723 of the column. (Refer to the schema and table name fields to
2724 identify the table.)
2729 <varlistentry id="libpq-pg-diag-datatype-name">
2730 <term><symbol>PG_DIAG_DATATYPE_NAME</></term>
2733 If the error was associated with a specific data type, the name of
2734 the data type. (Refer to the schema name field for the name of
2735 the data type's schema.)
2740 <varlistentry id="libpq-pg-diag-constraint-name">
2741 <term><symbol>PG_DIAG_CONSTRAINT_NAME</></term>
2744 If the error was associated with a specific constraint, the name
2745 of the constraint. Refer to fields listed above for the
2746 associated table or domain. (For this purpose, indexes are
2747 treated as constraints, even if they weren't created with
2753 <varlistentry id="libpq-pg-diag-source-file">
2754 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
2757 The file name of the source-code location where the error was
2763 <varlistentry id="libpq-pg-diag-source-line">
2764 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
2767 The line number of the source-code location where the error
2773 <varlistentry id="libpq-pg-diag-source-function">
2774 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
2777 The name of the source-code function reporting the error.
2786 The fields for schema name, table name, column name, data type name,
2787 and constraint name are supplied only for a limited number of error
2788 types; see <xref linkend="errcodes-appendix">. Do not assume that
2789 the presence of any of these fields guarantees the presence of
2790 another field. Core error sources observe the interrelationships
2791 noted above, but user-defined functions may use these fields in other
2792 ways. In the same vein, do not assume that these fields denote
2793 contemporary objects in the current database.
2798 The client is responsible for formatting displayed information to meet
2799 its needs; in particular it should break long lines as needed.
2800 Newline characters appearing in the error message fields should be
2801 treated as paragraph breaks, not line breaks.
2805 Errors generated internally by <application>libpq</application> will
2806 have severity and primary message, but typically no other fields.
2807 Errors returned by a pre-3.0-protocol server will include severity and
2808 primary message, and sometimes a detail message, but no other fields.
2812 Note that error fields are only available from
2813 <structname>PGresult</structname> objects, not
2814 <structname>PGconn</structname> objects; there is no
2815 <function>PQerrorField</function> function.
2820 <varlistentry id="libpq-pqclear">
2821 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
2824 Frees the storage associated with a
2825 <structname>PGresult</structname>. Every command result should be
2826 freed via <function>PQclear</function> when it is no longer
2830 void PQclear(PGresult *res);
2835 You can keep a <structname>PGresult</structname> object around for
2836 as long as you need it; it does not go away when you issue a new
2837 command, nor even if you close the connection. To get rid of it,
2838 you must call <function>PQclear</function>. Failure to do this
2839 will result in memory leaks in your application.
2847 <sect2 id="libpq-exec-select-info">
2848 <title>Retrieving Query Result Information</title>
2851 These functions are used to extract information from a
2852 <structname>PGresult</structname> object that represents a successful
2853 query result (that is, one that has status
2854 <literal>PGRES_TUPLES_OK</literal> or <literal>PGRES_SINGLE_TUPLE</>).
2855 They can also be used to extract
2856 information from a successful Describe operation: a Describe's result
2857 has all the same column information that actual execution of the query
2858 would provide, but it has zero rows. For objects with other status values,
2859 these functions will act as though the result has zero rows and zero columns.
2863 <varlistentry id="libpq-pqntuples">
2865 <function>PQntuples</function>
2867 <primary>PQntuples</primary>
2873 Returns the number of rows (tuples) in the query result. Because
2874 it returns an integer result, large result sets might overflow the
2875 return value on 32-bit operating systems.
2878 int PQntuples(const PGresult *res);
2885 <varlistentry id="libpq-pqnfields">
2887 <function>PQnfields</function>
2889 <primary>PQnfields</primary>
2895 Returns the number of columns (fields) in each row of the query
2899 int PQnfields(const PGresult *res);
2905 <varlistentry id="libpq-pqfname">
2907 <function>PQfname</function>
2909 <primary>PQfname</primary>
2915 Returns the column name associated with the given column number.
2916 Column numbers start at 0. The caller should not free the result
2917 directly. It will be freed when the associated
2918 <structname>PGresult</> handle is passed to
2919 <function>PQclear</function>.
2921 char *PQfname(const PGresult *res,
2927 <symbol>NULL</symbol> is returned if the column number is out of range.
2932 <varlistentry id="libpq-pqfnumber">
2934 <function>PQfnumber</function>
2936 <primary>PQfnumber</primary>
2942 Returns the column number associated with the given column name.
2944 int PQfnumber(const PGresult *res,
2945 const char *column_name);
2950 -1 is returned if the given name does not match any column.
2954 The given name is treated like an identifier in an SQL command,
2955 that is, it is downcased unless double-quoted. For example, given
2956 a query result generated from the SQL command:
2958 SELECT 1 AS FOO, 2 AS "BAR";
2960 we would have the results:
2962 PQfname(res, 0) <lineannotation>foo</lineannotation>
2963 PQfname(res, 1) <lineannotation>BAR</lineannotation>
2964 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
2965 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
2966 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
2967 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
2973 <varlistentry id="libpq-pqftable">
2975 <function>PQftable</function>
2977 <primary>PQftable</primary>
2983 Returns the OID of the table from which the given column was
2984 fetched. Column numbers start at 0.
2986 Oid PQftable(const PGresult *res,
2992 <literal>InvalidOid</> is returned if the column number is out of range,
2993 or if the specified column is not a simple reference to a table column,
2994 or when using pre-3.0 protocol.
2995 You can query the system table <literal>pg_class</literal> to determine
2996 exactly which table is referenced.
3000 The type <type>Oid</type> and the constant
3001 <literal>InvalidOid</literal> will be defined when you include
3002 the <application>libpq</application> header file. They will both
3003 be some integer type.
3008 <varlistentry id="libpq-pqftablecol">
3010 <function>PQftablecol</function>
3012 <primary>PQftablecol</primary>
3018 Returns the column number (within its table) of the column making
3019 up the specified query result column. Query-result column numbers
3020 start at 0, but table columns have nonzero numbers.
3022 int PQftablecol(const PGresult *res,
3028 Zero is returned if the column number is out of range, or if the
3029 specified column is not a simple reference to a table column, or
3030 when using pre-3.0 protocol.
3035 <varlistentry id="libpq-pqfformat">
3037 <function>PQfformat</function>
3039 <primary>PQfformat</primary>
3045 Returns the format code indicating the format of the given
3046 column. Column numbers start at 0.
3048 int PQfformat(const PGresult *res,
3054 Format code zero indicates textual data representation, while format
3055 code one indicates binary representation. (Other codes are reserved
3056 for future definition.)
3061 <varlistentry id="libpq-pqftype">
3063 <function>PQftype</function>
3065 <primary>PQftype</primary>
3071 Returns the data type associated with the given column number.
3072 The integer returned is the internal OID number of the type.
3073 Column numbers start at 0.
3075 Oid PQftype(const PGresult *res,
3081 You can query the system table <literal>pg_type</literal> to
3082 obtain the names and properties of the various data types. The
3083 <acronym>OID</acronym>s of the built-in data types are defined
3084 in the file <filename>src/include/catalog/pg_type.h</filename>
3090 <varlistentry id="libpq-pqfmod">
3092 <function>PQfmod</function>
3094 <primary>PQfmod</primary>
3100 Returns the type modifier of the column associated with the
3101 given column number. Column numbers start at 0.
3103 int PQfmod(const PGresult *res,
3109 The interpretation of modifier values is type-specific; they
3110 typically indicate precision or size limits. The value -1 is
3111 used to indicate <quote>no information available</>. Most data
3112 types do not use modifiers, in which case the value is always
3118 <varlistentry id="libpq-pqfsize">
3120 <function>PQfsize</function>
3122 <primary>PQfsize</primary>
3128 Returns the size in bytes of the column associated with the
3129 given column number. Column numbers start at 0.
3131 int PQfsize(const PGresult *res,
3137 <function>PQfsize</> returns the space allocated for this column
3138 in a database row, in other words the size of the server's
3139 internal representation of the data type. (Accordingly, it is
3140 not really very useful to clients.) A negative value indicates
3141 the data type is variable-length.
3146 <varlistentry id="libpq-pqbinarytuples">
3148 <function>PQbinaryTuples</function>
3150 <primary>PQbinaryTuples</primary>
3156 Returns 1 if the <structname>PGresult</> contains binary data
3157 and 0 if it contains text data.
3159 int PQbinaryTuples(const PGresult *res);
3164 This function is deprecated (except for its use in connection with
3165 <command>COPY</>), because it is possible for a single
3166 <structname>PGresult</> to contain text data in some columns and
3167 binary data in others. <function>PQfformat</> is preferred.
3168 <function>PQbinaryTuples</> returns 1 only if all columns of the
3169 result are binary (format 1).
3174 <varlistentry id="libpq-pqgetvalue">
3176 <function>PQgetvalue</function>
3178 <primary>PQgetvalue</primary>
3184 Returns a single field value of one row of a
3185 <structname>PGresult</structname>. Row and column numbers start
3186 at 0. The caller should not free the result directly. It will
3187 be freed when the associated <structname>PGresult</> handle is
3188 passed to <function>PQclear</function>.
3190 char *PQgetvalue(const PGresult *res,
3197 For data in text format, the value returned by
3198 <function>PQgetvalue</function> is a null-terminated character
3199 string representation of the field value. For data in binary
3200 format, the value is in the binary representation determined by
3201 the data type's <function>typsend</> and <function>typreceive</>
3202 functions. (The value is actually followed by a zero byte in
3203 this case too, but that is not ordinarily useful, since the
3204 value is likely to contain embedded nulls.)
3208 An empty string is returned if the field value is null. See
3209 <function>PQgetisnull</> to distinguish null values from
3210 empty-string values.
3214 The pointer returned by <function>PQgetvalue</function> points
3215 to storage that is part of the <structname>PGresult</structname>
3216 structure. One should not modify the data it points to, and one
3217 must explicitly copy the data into other storage if it is to be
3218 used past the lifetime of the <structname>PGresult</structname>
3224 <varlistentry id="libpq-pqgetisnull">
3226 <function>PQgetisnull</function>
3228 <primary>PQgetisnull</primary>
3231 <primary>null value</primary>
3232 <secondary sortas="libpq">in libpq</secondary>
3238 Tests a field for a null value. Row and column numbers start
3241 int PQgetisnull(const PGresult *res,
3248 This function returns 1 if the field is null and 0 if it
3249 contains a non-null value. (Note that
3250 <function>PQgetvalue</function> will return an empty string,
3251 not a null pointer, for a null field.)
3256 <varlistentry id="libpq-pqgetlength">
3258 <function>PQgetlength</function>
3260 <primary>PQgetlength</primary>
3265 Returns the actual length of a field value in bytes. Row and
3266 column numbers start at 0.
3268 int PQgetlength(const PGresult *res,
3275 This is the actual data length for the particular data value,
3276 that is, the size of the object pointed to by
3277 <function>PQgetvalue</function>. For text data format this is
3278 the same as <function>strlen()</>. For binary format this is
3279 essential information. Note that one should <emphasis>not</>
3280 rely on <function>PQfsize</function> to obtain the actual data
3286 <varlistentry id="libpq-pqnparams">
3288 <function>PQnparams</function>
3290 <primary>PQnparams</primary>
3296 Returns the number of parameters of a prepared statement.
3298 int PQnparams(const PGresult *res);
3303 This function is only useful when inspecting the result of
3304 <function>PQdescribePrepared</>. For other types of queries it
3310 <varlistentry id="libpq-pqparamtype">
3312 <function>PQparamtype</function>
3314 <primary>PQparamtype</primary>
3320 Returns the data type of the indicated statement parameter.
3321 Parameter numbers start at 0.
3323 Oid PQparamtype(const PGresult *res, int param_number);
3328 This function is only useful when inspecting the result of
3329 <function>PQdescribePrepared</>. For other types of queries it
3335 <varlistentry id="libpq-pqprint">
3337 <function>PQprint</function>
3339 <primary>PQprint</primary>
3345 Prints out all the rows and, optionally, the column names to
3346 the specified output stream.
3348 void PQprint(FILE *fout, /* output stream */
3349 const PGresult *res,
3350 const PQprintOpt *po);
3353 pqbool header; /* print output field headings and row count */
3354 pqbool align; /* fill align the fields */
3355 pqbool standard; /* old brain dead format */
3356 pqbool html3; /* output HTML tables */
3357 pqbool expanded; /* expand tables */
3358 pqbool pager; /* use pager for output if needed */
3359 char *fieldSep; /* field separator */
3360 char *tableOpt; /* attributes for HTML table element */
3361 char *caption; /* HTML table caption */
3362 char **fieldName; /* null-terminated array of replacement field names */
3368 This function was formerly used by <application>psql</application>
3369 to print query results, but this is no longer the case. Note
3370 that it assumes all the data is in text format.
3377 <sect2 id="libpq-exec-nonselect">
3378 <title>Retrieving Other Result Information</title>
3381 These functions are used to extract other information from
3382 <structname>PGresult</structname> objects.
3386 <varlistentry id="libpq-pqcmdstatus">
3388 <function>PQcmdStatus</function>
3390 <primary>PQcmdStatus</primary>
3396 Returns the command status tag from the SQL command that generated
3397 the <structname>PGresult</structname>.
3399 char *PQcmdStatus(PGresult *res);
3404 Commonly this is just the name of the command, but it might include
3405 additional data such as the number of rows processed. The caller
3406 should not free the result directly. It will be freed when the
3407 associated <structname>PGresult</> handle is passed to
3408 <function>PQclear</function>.
3413 <varlistentry id="libpq-pqcmdtuples">
3415 <function>PQcmdTuples</function>
3417 <primary>PQcmdTuples</primary>
3423 Returns the number of rows affected by the SQL command.
3425 char *PQcmdTuples(PGresult *res);
3430 This function returns a string containing the number of rows
3431 affected by the <acronym>SQL</> statement that generated the
3432 <structname>PGresult</>. This function can only be used following
3433 the execution of a <command>SELECT</>, <command>CREATE TABLE AS</>,
3434 <command>INSERT</>, <command>UPDATE</>, <command>DELETE</>,
3435 <command>MOVE</>, <command>FETCH</>, or <command>COPY</> statement,
3436 or an <command>EXECUTE</> of a prepared query that contains an
3437 <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</> statement.
3438 If the command that generated the <structname>PGresult</> was anything
3439 else, <function>PQcmdTuples</> returns an empty string. The caller
3440 should not free the return value directly. It will be freed when
3441 the associated <structname>PGresult</> handle is passed to
3442 <function>PQclear</function>.
3447 <varlistentry id="libpq-pqoidvalue">
3449 <function>PQoidValue</function>
3451 <primary>PQoidValue</primary>
3457 Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
3458 of the inserted row, if the <acronym>SQL</> command was an
3459 <command>INSERT</> that inserted exactly one row into a table that
3460 has OIDs, or a <command>EXECUTE</> of a prepared query containing
3461 a suitable <command>INSERT</> statement. Otherwise, this function
3462 returns <literal>InvalidOid</literal>. This function will also
3463 return <literal>InvalidOid</literal> if the table affected by the
3464 <command>INSERT</> statement does not contain OIDs.
3466 Oid PQoidValue(const PGresult *res);
3472 <varlistentry id="libpq-pqoidstatus">
3474 <function>PQoidStatus</function>
3476 <primary>PQoidStatus</primary>
3482 This function is deprecated in favor of
3483 <function>PQoidValue</function> and is not thread-safe.
3484 It returns a string with the OID of the inserted row, while
3485 <function>PQoidValue</function> returns the OID value.
3487 char *PQoidStatus(const PGresult *res);
3497 <sect2 id="libpq-exec-escape-string">
3498 <title>Escaping Strings for Inclusion in SQL Commands</title>
3500 <indexterm zone="libpq-exec-escape-string">
3501 <primary>escaping strings</primary>
3502 <secondary>in libpq</secondary>
3506 <varlistentry id="libpq-pqescapeliteral">
3508 <function>PQescapeLiteral</function>
3510 <primary>PQescapeLiteral</primary>
3517 char *PQescapeLiteral(PGconn *conn, const char *str, size_t length);
3522 <function>PQescapeLiteral</function> escapes a string for
3523 use within an SQL command. This is useful when inserting data
3524 values as literal constants in SQL commands. Certain characters
3525 (such as quotes and backslashes) must be escaped to prevent them
3526 from being interpreted specially by the SQL parser.
3527 <function>PQescapeLiteral</> performs this operation.
3531 <function>PQescapeLiteral</> returns an escaped version of the
3532 <parameter>str</parameter> parameter in memory allocated with
3533 <function>malloc()</>. This memory should be freed using
3534 <function>PQfreemem()</> when the result is no longer needed.
3535 A terminating zero byte is not required, and should not be
3536 counted in <parameter>length</>. (If a terminating zero byte is found
3537 before <parameter>length</> bytes are processed,
3538 <function>PQescapeLiteral</> stops at the zero; the behavior is
3539 thus rather like <function>strncpy</>.) The
3540 return string has all special characters replaced so that they can
3541 be properly processed by the <productname>PostgreSQL</productname>
3542 string literal parser. A terminating zero byte is also added. The
3543 single quotes that must surround <productname>PostgreSQL</productname>
3544 string literals are included in the result string.
3548 On error, <function>PQescapeLiteral</> returns <symbol>NULL</> and a suitable
3549 message is stored in the <parameter>conn</> object.
3554 It is especially important to do proper escaping when handling
3555 strings that were received from an untrustworthy source.
3556 Otherwise there is a security risk: you are vulnerable to
3557 <quote>SQL injection</> attacks wherein unwanted SQL commands are
3558 fed to your database.
3563 Note that it is not necessary nor correct to do escaping when a data
3564 value is passed as a separate parameter in <function>PQexecParams</> or
3565 its sibling routines.
3570 <varlistentry id="libpq-pqescapeidentifier">
3572 <function>PQescapeIdentifier</function>
3574 <primary>PQescapeIdentifier</primary>
3581 char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length);
3586 <function>PQescapeIdentifier</function> escapes a string for
3587 use as an SQL identifier, such as a table, column, or function name.
3588 This is useful when a user-supplied identifier might contain
3589 special characters that would otherwise not be interpreted as part
3590 of the identifier by the SQL parser, or when the identifier might
3591 contain upper case characters whose case should be preserved.
3595 <function>PQescapeIdentifier</> returns a version of the
3596 <parameter>str</parameter> parameter escaped as an SQL identifier
3597 in memory allocated with <function>malloc()</>. This memory must be
3598 freed using <function>PQfreemem()</> when the result is no longer
3599 needed. A terminating zero byte is not required, and should not be
3600 counted in <parameter>length</>. (If a terminating zero byte is found
3601 before <parameter>length</> bytes are processed,
3602 <function>PQescapeIdentifier</> stops at the zero; the behavior is
3603 thus rather like <function>strncpy</>.) The
3604 return string has all special characters replaced so that it
3605 will be properly processed as an SQL identifier. A terminating zero byte
3606 is also added. The return string will also be surrounded by double
3611 On error, <function>PQescapeIdentifier</> returns <symbol>NULL</> and a suitable
3612 message is stored in the <parameter>conn</> object.
3617 As with string literals, to prevent SQL injection attacks,
3618 SQL identifiers must be escaped when they are received from an
3619 untrustworthy source.
3625 <varlistentry id="libpq-pqescapestringconn">
3627 <function>PQescapeStringConn</function>
3629 <primary>PQescapeStringConn</primary>
3636 size_t PQescapeStringConn(PGconn *conn,
3637 char *to, const char *from, size_t length,
3643 <function>PQescapeStringConn</> escapes string literals, much like
3644 <function>PQescapeLiteral</>. Unlike <function>PQescapeLiteral</>,
3645 the caller is responsible for providing an appropriately sized buffer.
3646 Furthermore, <function>PQescapeStringConn</> does not generate the
3647 single quotes that must surround <productname>PostgreSQL</> string
3648 literals; they should be provided in the SQL command that the
3649 result is inserted into. The parameter <parameter>from</> points to
3650 the first character of the string that is to be escaped, and the
3651 <parameter>length</> parameter gives the number of bytes in this
3652 string. A terminating zero byte is not required, and should not be
3653 counted in <parameter>length</>. (If a terminating zero byte is found
3654 before <parameter>length</> bytes are processed,
3655 <function>PQescapeStringConn</> stops at the zero; the behavior is
3656 thus rather like <function>strncpy</>.) <parameter>to</> shall point
3657 to a buffer that is able to hold at least one more byte than twice
3658 the value of <parameter>length</>, otherwise the behavior is undefined.
3659 Behavior is likewise undefined if the <parameter>to</> and
3660 <parameter>from</> strings overlap.
3664 If the <parameter>error</> parameter is not <symbol>NULL</>, then
3665 <literal>*error</> is set to zero on success, nonzero on error.
3666 Presently the only possible error conditions involve invalid multibyte
3667 encoding in the source string. The output string is still generated
3668 on error, but it can be expected that the server will reject it as
3669 malformed. On error, a suitable message is stored in the
3670 <parameter>conn</> object, whether or not <parameter>error</> is <symbol>NULL</>.
3674 <function>PQescapeStringConn</> returns the number of bytes written
3675 to <parameter>to</>, not including the terminating zero byte.
3680 <varlistentry id="libpq-pqescapestring">
3682 <function>PQescapeString</function>
3684 <primary>PQescapeString</primary>
3690 <function>PQescapeString</> is an older, deprecated version of
3691 <function>PQescapeStringConn</>.
3693 size_t PQescapeString (char *to, const char *from, size_t length);
3698 The only difference from <function>PQescapeStringConn</> is that
3699 <function>PQescapeString</> does not take <structname>PGconn</>
3700 or <parameter>error</> parameters.
3701 Because of this, it cannot adjust its behavior depending on the
3702 connection properties (such as character encoding) and therefore
3703 <emphasis>it might give the wrong results</>. Also, it has no way
3704 to report error conditions.
3708 <function>PQescapeString</> can be used safely in
3709 client programs that work with only one <productname>PostgreSQL</>
3710 connection at a time (in this case it can find out what it needs to
3711 know <quote>behind the scenes</>). In other contexts it is a security
3712 hazard and should be avoided in favor of
3713 <function>PQescapeStringConn</>.
3718 <varlistentry id="libpq-pqescapebyteaconn">
3720 <function>PQescapeByteaConn</function>
3722 <primary>PQescapeByteaConn</primary>
3728 Escapes binary data for use within an SQL command with the type
3729 <type>bytea</type>. As with <function>PQescapeStringConn</function>,
3730 this is only used when inserting data directly into an SQL command string.
3732 unsigned char *PQescapeByteaConn(PGconn *conn,
3733 const unsigned char *from,
3740 Certain byte values must be escaped when used as part of a
3741 <type>bytea</type> literal in an <acronym>SQL</acronym> statement.
3742 <function>PQescapeByteaConn</function> escapes bytes using
3743 either hex encoding or backslash escaping. See <xref
3744 linkend="datatype-binary"> for more information.
3748 The <parameter>from</parameter> parameter points to the first
3749 byte of the string that is to be escaped, and the
3750 <parameter>from_length</parameter> parameter gives the number of
3751 bytes in this binary string. (A terminating zero byte is
3752 neither necessary nor counted.) The <parameter>to_length</parameter>
3753 parameter points to a variable that will hold the resultant
3754 escaped string length. This result string length includes the terminating
3755 zero byte of the result.
3759 <function>PQescapeByteaConn</> returns an escaped version of the
3760 <parameter>from</parameter> parameter binary string in memory
3761 allocated with <function>malloc()</>. This memory should be freed using
3762 <function>PQfreemem()</> when the result is no longer needed. The
3763 return string has all special characters replaced so that they can
3764 be properly processed by the <productname>PostgreSQL</productname>
3765 string literal parser, and the <type>bytea</type> input function. A
3766 terminating zero byte is also added. The single quotes that must
3767 surround <productname>PostgreSQL</productname> string literals are
3768 not part of the result string.
3772 On error, a null pointer is returned, and a suitable error message
3773 is stored in the <parameter>conn</> object. Currently, the only
3774 possible error is insufficient memory for the result string.
3779 <varlistentry id="libpq-pqescapebytea">
3781 <function>PQescapeBytea</function>
3783 <primary>PQescapeBytea</primary>
3789 <function>PQescapeBytea</> is an older, deprecated version of
3790 <function>PQescapeByteaConn</>.
3792 unsigned char *PQescapeBytea(const unsigned char *from,
3799 The only difference from <function>PQescapeByteaConn</> is that
3800 <function>PQescapeBytea</> does not take a <structname>PGconn</>
3801 parameter. Because of this, <function>PQescapeBytea</> can
3802 only be used safely in client programs that use a single
3803 <productname>PostgreSQL</> connection at a time (in this case
3804 it can find out what it needs to know <quote>behind the
3805 scenes</>). It <emphasis>might give the wrong results</> if
3806 used in programs that use multiple database connections (use
3807 <function>PQescapeByteaConn</> in such cases).
3812 <varlistentry id="libpq-pqunescapebytea">
3814 <function>PQunescapeBytea</function>
3816 <primary>PQunescapeBytea</primary>
3822 Converts a string representation of binary data into binary data
3823 — the reverse of <function>PQescapeBytea</function>. This
3824 is needed when retrieving <type>bytea</type> data in text format,
3825 but not when retrieving it in binary format.
3828 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
3833 The <parameter>from</parameter> parameter points to a string
3834 such as might be returned by <function>PQgetvalue</function> when applied
3835 to a <type>bytea</type> column. <function>PQunescapeBytea</function>
3836 converts this string representation into its binary representation.
3837 It returns a pointer to a buffer allocated with
3838 <function>malloc()</function>, or <symbol>NULL</> on error, and puts the size of
3839 the buffer in <parameter>to_length</parameter>. The result must be
3840 freed using <function>PQfreemem</> when it is no longer needed.
3844 This conversion is not exactly the inverse of
3845 <function>PQescapeBytea</function>, because the string is not expected
3846 to be <quote>escaped</> when received from <function>PQgetvalue</function>.
3847 In particular this means there is no need for string quoting considerations,
3848 and so no need for a <structname>PGconn</> parameter.
3858 <sect1 id="libpq-async">
3859 <title>Asynchronous Command Processing</title>
3861 <indexterm zone="libpq-async">
3862 <primary>nonblocking connection</primary>
3866 The <function>PQexec</function> function is adequate for submitting
3867 commands in normal, synchronous applications. It has a few
3868 deficiencies, however, that can be of importance to some users:
3873 <function>PQexec</function> waits for the command to be completed.
3874 The application might have other work to do (such as maintaining a
3875 user interface), in which case it won't want to block waiting for
3882 Since the execution of the client application is suspended while it
3883 waits for the result, it is hard for the application to decide that
3884 it would like to try to cancel the ongoing command. (It can be done
3885 from a signal handler, but not otherwise.)
3891 <function>PQexec</function> can return only one
3892 <structname>PGresult</structname> structure. If the submitted command
3893 string contains multiple <acronym>SQL</acronym> commands, all but
3894 the last <structname>PGresult</structname> are discarded by
3895 <function>PQexec</function>.
3901 <function>PQexec</function> always collects the command's entire result,
3902 buffering it in a single <structname>PGresult</structname>. While
3903 this simplifies error-handling logic for the application, it can be
3904 impractical for results containing many rows.
3911 Applications that do not like these limitations can instead use the
3912 underlying functions that <function>PQexec</function> is built from:
3913 <function>PQsendQuery</function> and <function>PQgetResult</function>.
3915 <function>PQsendQueryParams</function>,
3916 <function>PQsendPrepare</function>,
3917 <function>PQsendQueryPrepared</function>,
3918 <function>PQsendDescribePrepared</function>, and
3919 <function>PQsendDescribePortal</function>,
3920 which can be used with <function>PQgetResult</function> to duplicate
3921 the functionality of
3922 <function>PQexecParams</function>,
3923 <function>PQprepare</function>,
3924 <function>PQexecPrepared</function>,
3925 <function>PQdescribePrepared</function>, and
3926 <function>PQdescribePortal</function>
3930 <varlistentry id="libpq-pqsendquery">
3932 <function>PQsendQuery</function>
3934 <primary>PQsendQuery</primary>
3940 Submits a command to the server without waiting for the result(s).
3941 1 is returned if the command was successfully dispatched and 0 if
3942 not (in which case, use <function>PQerrorMessage</> to get more
3943 information about the failure).
3945 int PQsendQuery(PGconn *conn, const char *command);
3948 After successfully calling <function>PQsendQuery</function>, call
3949 <function>PQgetResult</function> one or more times to obtain the
3950 results. <function>PQsendQuery</function> cannot be called again
3951 (on the same connection) until <function>PQgetResult</function>
3952 has returned a null pointer, indicating that the command is done.
3957 <varlistentry id="libpq-pqsendqueryparams">
3959 <function>PQsendQueryParams</function>
3961 <primary>PQsendQueryParams</primary>
3967 Submits a command and separate parameters to the server without
3968 waiting for the result(s).
3970 int PQsendQueryParams(PGconn *conn,
3971 const char *command,
3973 const Oid *paramTypes,
3974 const char * const *paramValues,
3975 const int *paramLengths,
3976 const int *paramFormats,
3980 This is equivalent to <function>PQsendQuery</function> except that
3981 query parameters can be specified separately from the query string.
3982 The function's parameters are handled identically to
3983 <function>PQexecParams</function>. Like
3984 <function>PQexecParams</function>, it will not work on 2.0-protocol
3985 connections, and it allows only one command in the query string.
3990 <varlistentry id="libpq-pqsendprepare">
3992 <function>PQsendPrepare</>
3994 <primary>PQsendPrepare</primary>
4000 Sends a request to create a prepared statement with the given
4001 parameters, without waiting for completion.
4003 int PQsendPrepare(PGconn *conn,
4004 const char *stmtName,
4007 const Oid *paramTypes);
4010 This is an asynchronous version of <function>PQprepare</>: it
4011 returns 1 if it was able to dispatch the request, and 0 if not.
4012 After a successful call, call <function>PQgetResult</function> to
4013 determine whether the server successfully created the prepared
4014 statement. The function's parameters are handled identically to
4015 <function>PQprepare</function>. Like
4016 <function>PQprepare</function>, it will not work on 2.0-protocol
4022 <varlistentry id="libpq-pqsendqueryprepared">
4024 <function>PQsendQueryPrepared</function>
4026 <primary>PQsendQueryPrepared</primary>
4032 Sends a request to execute a prepared statement with given
4033 parameters, without waiting for the result(s).
4035 int PQsendQueryPrepared(PGconn *conn,
4036 const char *stmtName,
4038 const char * const *paramValues,
4039 const int *paramLengths,
4040 const int *paramFormats,
4044 This is similar to <function>PQsendQueryParams</function>, but
4045 the command to be executed is specified by naming a
4046 previously-prepared statement, instead of giving a query string.
4047 The function's parameters are handled identically to
4048 <function>PQexecPrepared</function>. Like
4049 <function>PQexecPrepared</function>, it will not work on
4050 2.0-protocol connections.
4055 <varlistentry id="libpq-pqsenddescribeprepared">
4057 <function>PQsendDescribePrepared</>
4059 <primary>PQsendDescribePrepared</primary>
4065 Submits a request to obtain information about the specified
4066 prepared statement, without waiting for completion.
4068 int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
4071 This is an asynchronous version of <function>PQdescribePrepared</>:
4072 it returns 1 if it was able to dispatch the request, and 0 if not.
4073 After a successful call, call <function>PQgetResult</function> to
4074 obtain the results. The function's parameters are handled
4075 identically to <function>PQdescribePrepared</function>. Like
4076 <function>PQdescribePrepared</function>, it will not work on
4077 2.0-protocol connections.
4082 <varlistentry id="libpq-pqsenddescribeportal">
4084 <function>PQsendDescribePortal</>
4086 <primary>PQsendDescribePortal</primary>
4092 Submits a request to obtain information about the specified
4093 portal, without waiting for completion.
4095 int PQsendDescribePortal(PGconn *conn, const char *portalName);
4098 This is an asynchronous version of <function>PQdescribePortal</>:
4099 it returns 1 if it was able to dispatch the request, and 0 if not.
4100 After a successful call, call <function>PQgetResult</function> to
4101 obtain the results. The function's parameters are handled
4102 identically to <function>PQdescribePortal</function>. Like
4103 <function>PQdescribePortal</function>, it will not work on
4104 2.0-protocol connections.
4109 <varlistentry id="libpq-pqgetresult">
4111 <function>PQgetResult</function>
4113 <primary>PQgetResult</primary>
4119 Waits for the next result from a prior
4120 <function>PQsendQuery</function>,
4121 <function>PQsendQueryParams</function>,
4122 <function>PQsendPrepare</function>,
4123 <function>PQsendQueryPrepared</function>,
4124 <function>PQsendDescribePrepared</function>, or
4125 <function>PQsendDescribePortal</function>
4126 call, and returns it.
4127 A null pointer is returned when the command is complete and there
4128 will be no more results.
4130 PGresult *PQgetResult(PGconn *conn);
4135 <function>PQgetResult</function> must be called repeatedly until
4136 it returns a null pointer, indicating that the command is done.
4137 (If called when no command is active,
4138 <function>PQgetResult</function> will just return a null pointer
4139 at once.) Each non-null result from
4140 <function>PQgetResult</function> should be processed using the
4141 same <structname>PGresult</> accessor functions previously
4142 described. Don't forget to free each result object with
4143 <function>PQclear</function> when done with it. Note that
4144 <function>PQgetResult</function> will block only if a command is
4145 active and the necessary response data has not yet been read by
4146 <function>PQconsumeInput</function>.
4151 Even when <function>PQresultStatus</function> indicates a fatal
4152 error, <function>PQgetResult</function> should be called until it
4153 returns a null pointer, to allow <application>libpq</> to
4154 process the error information completely.
4163 Using <function>PQsendQuery</function> and
4164 <function>PQgetResult</function> solves one of
4165 <function>PQexec</function>'s problems: If a command string contains
4166 multiple <acronym>SQL</acronym> commands, the results of those commands
4167 can be obtained individually. (This allows a simple form of overlapped
4168 processing, by the way: the client can be handling the results of one
4169 command while the server is still working on later queries in the same
4174 Another frequently-desired feature that can be obtained with
4175 <function>PQsendQuery</function> and <function>PQgetResult</function>
4176 is retrieving large query results a row at a time. This is discussed
4177 in <xref linkend="libpq-single-row-mode">.
4181 By itself, calling <function>PQgetResult</function>
4182 will still cause the client to block until the server completes the
4183 next <acronym>SQL</acronym> command. This can be avoided by proper
4184 use of two more functions:
4187 <varlistentry id="libpq-pqconsumeinput">
4189 <function>PQconsumeInput</function>
4191 <primary>PQconsumeInput</primary>
4197 If input is available from the server, consume it.
4199 int PQconsumeInput(PGconn *conn);
4204 <function>PQconsumeInput</function> normally returns 1 indicating
4205 <quote>no error</quote>, but returns 0 if there was some kind of
4206 trouble (in which case <function>PQerrorMessage</function> can be
4207 consulted). Note that the result does not say whether any input
4208 data was actually collected. After calling
4209 <function>PQconsumeInput</function>, the application can check
4210 <function>PQisBusy</function> and/or
4211 <function>PQnotifies</function> to see if their state has changed.
4215 <function>PQconsumeInput</function> can be called even if the
4216 application is not prepared to deal with a result or notification
4217 just yet. The function will read available data and save it in
4218 a buffer, thereby causing a <function>select()</function>
4219 read-ready indication to go away. The application can thus use
4220 <function>PQconsumeInput</function> to clear the
4221 <function>select()</function> condition immediately, and then
4222 examine the results at leisure.
4227 <varlistentry id="libpq-pqisbusy">
4229 <function>PQisBusy</function>
4231 <primary>PQisBusy</primary>
4237 Returns 1 if a command is busy, that is,
4238 <function>PQgetResult</function> would block waiting for input.
4239 A 0 return indicates that <function>PQgetResult</function> can be
4240 called with assurance of not blocking.
4242 int PQisBusy(PGconn *conn);
4247 <function>PQisBusy</function> will not itself attempt to read data
4248 from the server; therefore <function>PQconsumeInput</function>
4249 must be invoked first, or the busy state will never end.
4257 A typical application using these functions will have a main loop that
4258 uses <function>select()</function> or <function>poll()</> to wait for
4259 all the conditions that it must respond to. One of the conditions
4260 will be input available from the server, which in terms of
4261 <function>select()</function> means readable data on the file
4262 descriptor identified by <function>PQsocket</function>. When the main
4263 loop detects input ready, it should call
4264 <function>PQconsumeInput</function> to read the input. It can then
4265 call <function>PQisBusy</function>, followed by
4266 <function>PQgetResult</function> if <function>PQisBusy</function>
4267 returns false (0). It can also call <function>PQnotifies</function>
4268 to detect <command>NOTIFY</> messages (see <xref
4269 linkend="libpq-notify">).
4274 <function>PQsendQuery</function>/<function>PQgetResult</function>
4275 can also attempt to cancel a command that is still being processed
4276 by the server; see <xref linkend="libpq-cancel">. But regardless of
4277 the return value of <function>PQcancel</function>, the application
4278 must continue with the normal result-reading sequence using
4279 <function>PQgetResult</function>. A successful cancellation will
4280 simply cause the command to terminate sooner than it would have
4285 By using the functions described above, it is possible to avoid
4286 blocking while waiting for input from the database server. However,
4287 it is still possible that the application will block waiting to send
4288 output to the server. This is relatively uncommon but can happen if
4289 very long SQL commands or data values are sent. (It is much more
4290 probable if the application sends data via <command>COPY IN</command>,
4291 however.) To prevent this possibility and achieve completely
4292 nonblocking database operation, the following additional functions
4296 <varlistentry id="libpq-pqsetnonblocking">
4298 <function>PQsetnonblocking</function>
4300 <primary>PQsetnonblocking</primary>
4306 Sets the nonblocking status of the connection.
4308 int PQsetnonblocking(PGconn *conn, int arg);
4313 Sets the state of the connection to nonblocking if
4314 <parameter>arg</parameter> is 1, or blocking if
4315 <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
4319 In the nonblocking state, calls to
4320 <function>PQsendQuery</function>, <function>PQputline</function>,
4321 <function>PQputnbytes</function>, <function>PQputCopyData</function>,
4322 and <function>PQendcopy</function> will not block but instead return
4323 an error if they need to be called again.
4327 Note that <function>PQexec</function> does not honor nonblocking
4328 mode; if it is called, it will act in blocking fashion anyway.
4333 <varlistentry id="libpq-pqisnonblocking">
4335 <function>PQisnonblocking</function>
4337 <primary>PQisnonblocking</primary>
4343 Returns the blocking status of the database connection.
4345 int PQisnonblocking(const PGconn *conn);
4350 Returns 1 if the connection is set to nonblocking mode and 0 if
4356 <varlistentry id="libpq-pqflush">
4358 <function>PQflush</function>
4360 <primary>PQflush</primary>
4366 Attempts to flush any queued output data to the server. Returns
4367 0 if successful (or if the send queue is empty), -1 if it failed
4368 for some reason, or 1 if it was unable to send all the data in
4369 the send queue yet (this case can only occur if the connection
4372 int PQflush(PGconn *conn);
4381 After sending any command or data on a nonblocking connection, call
4382 <function>PQflush</function>. If it returns 1, wait for the socket
4383 to be write-ready and call it again; repeat until it returns 0. Once
4384 <function>PQflush</function> returns 0, wait for the socket to be
4385 read-ready and then read the response as described above.
4390 <sect1 id="libpq-single-row-mode">
4391 <title>Retrieving Query Results Row-By-Row</title>
4393 <indexterm zone="libpq-single-row-mode">
4394 <primary>libpq</primary>
4395 <secondary>single-row mode</secondary>
4399 Ordinarily, <application>libpq</> collects a SQL command's
4400 entire result and returns it to the application as a single
4401 <structname>PGresult</structname>. This can be unworkable for commands
4402 that return a large number of rows. For such cases, applications can use
4403 <function>PQsendQuery</function> and <function>PQgetResult</function> in
4404 <firstterm>single-row mode</>. In this mode, the result row(s) are
4405 returned to the application one at a time, as they are received from the
4410 To enter single-row mode, call <function>PQsetSingleRowMode</function>
4411 immediately after a successful call of <function>PQsendQuery</function>
4412 (or a sibling function). This mode selection is effective only for the
4413 currently executing query. Then call <function>PQgetResult</function>
4414 repeatedly, until it returns null, as documented in <xref
4415 linkend="libpq-async">. If the query returns any rows, they are returned
4416 as individual <structname>PGresult</structname> objects, which look like
4417 normal query results except for having status code
4418 <literal>PGRES_SINGLE_TUPLE</literal> instead of
4419 <literal>PGRES_TUPLES_OK</literal>. After the last row, or immediately if
4420 the query returns zero rows, a zero-row object with status
4421 <literal>PGRES_TUPLES_OK</literal> is returned; this is the signal that no
4422 more rows will arrive. (But note that it is still necessary to continue
4423 calling <function>PQgetResult</function> until it returns null.) All of
4424 these <structname>PGresult</structname> objects will contain the same row
4425 description data (column names, types, etc) that an ordinary
4426 <structname>PGresult</structname> object for the query would have.
4427 Each object should be freed with <function>PQclear</function> as usual.
4432 <varlistentry id="libpq-pqsetsinglerowmode">
4434 <function>PQsetSingleRowMode</function>
4436 <primary>PQsetSingleRowMode</primary>
4442 Select single-row mode for the currently-executing query.
4445 int PQsetSingleRowMode(PGconn *conn);
4450 This function can only be called immediately after
4451 <function>PQsendQuery</function> or one of its sibling functions,
4452 before any other operation on the connection such as
4453 <function>PQconsumeInput</function> or
4454 <function>PQgetResult</function>. If called at the correct time,
4455 the function activates single-row mode for the current query and
4456 returns 1. Otherwise the mode stays unchanged and the function
4457 returns 0. In any case, the mode reverts to normal after
4458 completion of the current query.
4467 While processing a query, the server may return some rows and then
4468 encounter an error, causing the query to be aborted. Ordinarily,
4469 <application>libpq</> discards any such rows and reports only the
4470 error. But in single-row mode, those rows will have already been
4471 returned to the application. Hence, the application will see some
4472 <literal>PGRES_SINGLE_TUPLE</literal> <structname>PGresult</structname>
4473 objects followed by a <literal>PGRES_FATAL_ERROR</literal> object. For
4474 proper transactional behavior, the application must be designed to
4475 discard or undo whatever has been done with the previously-processed
4476 rows, if the query ultimately fails.
4482 <sect1 id="libpq-cancel">
4483 <title>Canceling Queries in Progress</title>
4485 <indexterm zone="libpq-cancel">
4486 <primary>canceling</primary>
4487 <secondary>SQL command</secondary>
4491 A client application can request cancellation of a command that is
4492 still being processed by the server, using the functions described in
4496 <varlistentry id="libpq-pqgetcancel">
4498 <function>PQgetCancel</function>
4500 <primary>PQgetCancel</primary>
4506 Creates a data structure containing the information needed to cancel
4507 a command issued through a particular database connection.
4509 PGcancel *PQgetCancel(PGconn *conn);
4514 <function>PQgetCancel</function> creates a
4515 <structname>PGcancel</><indexterm><primary>PGcancel</></> object
4516 given a <structname>PGconn</> connection object. It will return
4517 <symbol>NULL</> if the given <parameter>conn</> is <symbol>NULL</> or an invalid
4518 connection. The <structname>PGcancel</> object is an opaque
4519 structure that is not meant to be accessed directly by the
4520 application; it can only be passed to <function>PQcancel</function>
4521 or <function>PQfreeCancel</function>.
4526 <varlistentry id="libpq-pqfreecancel">
4528 <function>PQfreeCancel</function>
4530 <primary>PQfreeCancel</primary>
4536 Frees a data structure created by <function>PQgetCancel</function>.
4538 void PQfreeCancel(PGcancel *cancel);
4543 <function>PQfreeCancel</function> frees a data object previously created
4544 by <function>PQgetCancel</function>.
4549 <varlistentry id="libpq-pqcancel">
4551 <function>PQcancel</function>
4553 <primary>PQcancel</primary>
4559 Requests that the server abandon processing of the current command.
4561 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
4566 The return value is 1 if the cancel request was successfully
4567 dispatched and 0 if not. If not, <parameter>errbuf</> is filled
4568 with an explanatory error message. <parameter>errbuf</>
4569 must be a char array of size <parameter>errbufsize</> (the
4570 recommended size is 256 bytes).
4574 Successful dispatch is no guarantee that the request will have
4575 any effect, however. If the cancellation is effective, the current
4576 command will terminate early and return an error result. If the
4577 cancellation fails (say, because the server was already done
4578 processing the command), then there will be no visible result at
4583 <function>PQcancel</function> can safely be invoked from a signal
4584 handler, if the <parameter>errbuf</> is a local variable in the
4585 signal handler. The <structname>PGcancel</> object is read-only
4586 as far as <function>PQcancel</function> is concerned, so it can
4587 also be invoked from a thread that is separate from the one
4588 manipulating the <structname>PGconn</> object.
4595 <varlistentry id="libpq-pqrequestcancel">
4597 <function>PQrequestCancel</function>
4599 <primary>PQrequestCancel</primary>
4605 <function>PQrequestCancel</function> is a deprecated variant of
4606 <function>PQcancel</function>.
4608 int PQrequestCancel(PGconn *conn);
4613 Requests that the server abandon processing of the current
4614 command. It operates directly on the
4615 <structname>PGconn</> object, and in case of failure stores the
4616 error message in the <structname>PGconn</> object (whence it can
4617 be retrieved by <function>PQerrorMessage</function>). Although
4618 the functionality is the same, this approach creates hazards for
4619 multiple-thread programs and signal handlers, since it is possible
4620 that overwriting the <structname>PGconn</>'s error message will
4621 mess up the operation currently in progress on the connection.
4630 <sect1 id="libpq-fastpath">
4631 <title>The Fast-Path Interface</title>
4633 <indexterm zone="libpq-fastpath">
4634 <primary>fast path</primary>
4638 <productname>PostgreSQL</productname> provides a fast-path interface
4639 to send simple function calls to the server.
4644 This interface is somewhat obsolete, as one can achieve similar
4645 performance and greater functionality by setting up a prepared
4646 statement to define the function call. Then, executing the statement
4647 with binary transmission of parameters and results substitutes for a
4648 fast-path function call.
4653 The function <function>PQfn</function><indexterm><primary>PQfn</></>
4654 requests execution of a server function via the fast-path interface:
4656 PGresult *PQfn(PGconn *conn,
4661 const PQArgBlock *args,
4678 The <parameter>fnid</> argument is the OID of the function to be
4679 executed. <parameter>args</> and <parameter>nargs</> define the
4680 parameters to be passed to the function; they must match the declared
4681 function argument list. When the <parameter>isint</> field of a
4682 parameter structure is true, the <parameter>u.integer</> value is sent
4683 to the server as an integer of the indicated length (this must be 1,
4684 2, or 4 bytes); proper byte-swapping occurs. When <parameter>isint</>
4685 is false, the indicated number of bytes at <parameter>*u.ptr</> are
4686 sent with no processing; the data must be in the format expected by
4687 the server for binary transmission of the function's argument data
4688 type. <parameter>result_buf</parameter> is the buffer in which to
4689 place the return value. The caller must have allocated sufficient
4690 space to store the return value. (There is no check!) The actual result
4691 length will be returned in the integer pointed to by
4692 <parameter>result_len</parameter>. If a 1, 2, or 4-byte integer result
4693 is expected, set <parameter>result_is_int</parameter> to 1, otherwise
4694 set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
4695 <application>libpq</> to byte-swap the value if necessary, so that it
4696 is delivered as a proper <type>int</type> value for the client machine.
4697 When <parameter>result_is_int</> is 0, the binary-format byte string
4698 sent by the server is returned unmodified.
4702 <function>PQfn</function> always returns a valid
4703 <structname>PGresult</structname> pointer. The result status should be
4704 checked before the result is used. The caller is responsible for
4705 freeing the <structname>PGresult</structname> with
4706 <function>PQclear</function> when it is no longer needed.
4710 Note that it is not possible to handle null arguments, null results,
4711 nor set-valued results when using this interface.
4716 <sect1 id="libpq-notify">
4717 <title>Asynchronous Notification</title>
4719 <indexterm zone="libpq-notify">
4720 <primary>NOTIFY</primary>
4721 <secondary>in libpq</secondary>
4725 <productname>PostgreSQL</productname> offers asynchronous notification
4726 via the <command>LISTEN</command> and <command>NOTIFY</command>
4727 commands. A client session registers its interest in a particular
4728 notification channel with the <command>LISTEN</command> command (and
4729 can stop listening with the <command>UNLISTEN</command> command). All
4730 sessions listening on a particular channel will be notified
4731 asynchronously when a <command>NOTIFY</command> command with that
4732 channel name is executed by any session. A <quote>payload</> string can
4733 be passed to communicate additional data to the listeners.
4737 <application>libpq</application> applications submit
4738 <command>LISTEN</command>, <command>UNLISTEN</command>,
4739 and <command>NOTIFY</command> commands as
4740 ordinary SQL commands. The arrival of <command>NOTIFY</command>
4741 messages can subsequently be detected by calling
4742 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
4746 The function <function>PQnotifies</function> returns the next notification
4747 from a list of unhandled notification messages received from the server.
4748 It returns a null pointer if there are no pending notifications. Once a
4749 notification is returned from <function>PQnotifies</>, it is considered
4750 handled and will be removed from the list of notifications.
4753 PGnotify *PQnotifies(PGconn *conn);
4755 typedef struct pgNotify
4757 char *relname; /* notification channel name */
4758 int be_pid; /* process ID of notifying server process */
4759 char *extra; /* notification payload string */
4763 After processing a <structname>PGnotify</structname> object returned
4764 by <function>PQnotifies</function>, be sure to free it with
4765 <function>PQfreemem</function>. It is sufficient to free the
4766 <structname>PGnotify</structname> pointer; the
4767 <structfield>relname</structfield> and <structfield>extra</structfield>
4768 fields do not represent separate allocations. (The names of these fields
4769 are historical; in particular, channel names need not have anything to
4770 do with relation names.)
4774 <xref linkend="libpq-example-2"> gives a sample program that illustrates
4775 the use of asynchronous notification.
4779 <function>PQnotifies</function> does not actually read data from the
4780 server; it just returns messages previously absorbed by another
4781 <application>libpq</application> function. In prior releases of
4782 <application>libpq</application>, the only way to ensure timely receipt
4783 of <command>NOTIFY</> messages was to constantly submit commands, even
4784 empty ones, and then check <function>PQnotifies</function> after each
4785 <function>PQexec</function>. While this still works, it is deprecated
4786 as a waste of processing power.
4790 A better way to check for <command>NOTIFY</> messages when you have no
4791 useful commands to execute is to call
4792 <function>PQconsumeInput</function>, then check
4793 <function>PQnotifies</function>. You can use
4794 <function>select()</function> to wait for data to arrive from the
4795 server, thereby using no <acronym>CPU</acronym> power unless there is
4796 something to do. (See <function>PQsocket</function> to obtain the file
4797 descriptor number to use with <function>select()</function>.) Note that
4798 this will work OK whether you submit commands with
4799 <function>PQsendQuery</function>/<function>PQgetResult</function> or
4800 simply use <function>PQexec</function>. You should, however, remember
4801 to check <function>PQnotifies</function> after each
4802 <function>PQgetResult</function> or <function>PQexec</function>, to
4803 see if any notifications came in during the processing of the command.
4808 <sect1 id="libpq-copy">
4809 <title>Functions Associated with the <command>COPY</command> Command</title>
4811 <indexterm zone="libpq-copy">
4812 <primary>COPY</primary>
4813 <secondary>with libpq</secondary>
4817 The <command>COPY</command> command in
4818 <productname>PostgreSQL</productname> has options to read from or write
4819 to the network connection used by <application>libpq</application>.
4820 The functions described in this section allow applications to take
4821 advantage of this capability by supplying or consuming copied data.
4825 The overall process is that the application first issues the SQL
4826 <command>COPY</command> command via <function>PQexec</function> or one
4827 of the equivalent functions. The response to this (if there is no
4828 error in the command) will be a <structname>PGresult</> object bearing
4829 a status code of <literal>PGRES_COPY_OUT</literal> or
4830 <literal>PGRES_COPY_IN</literal> (depending on the specified copy
4831 direction). The application should then use the functions of this
4832 section to receive or transmit data rows. When the data transfer is
4833 complete, another <structname>PGresult</> object is returned to indicate
4834 success or failure of the transfer. Its status will be
4835 <literal>PGRES_COMMAND_OK</literal> for success or
4836 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
4837 At this point further SQL commands can be issued via
4838 <function>PQexec</function>. (It is not possible to execute other SQL
4839 commands using the same connection while the <command>COPY</command>
4840 operation is in progress.)
4844 If a <command>COPY</command> command is issued via
4845 <function>PQexec</function> in a string that could contain additional
4846 commands, the application must continue fetching results via
4847 <function>PQgetResult</> after completing the <command>COPY</command>
4848 sequence. Only when <function>PQgetResult</> returns
4849 <symbol>NULL</symbol> is it certain that the <function>PQexec</function>
4850 command string is done and it is safe to issue more commands.
4854 The functions of this section should be executed only after obtaining
4855 a result status of <literal>PGRES_COPY_OUT</literal> or
4856 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
4857 <function>PQgetResult</function>.
4861 A <structname>PGresult</> object bearing one of these status values
4862 carries some additional data about the <command>COPY</command> operation
4863 that is starting. This additional data is available using functions
4864 that are also used in connection with query results:
4867 <varlistentry id="libpq-pqnfields-1">
4869 <function>PQnfields</function>
4871 <primary>PQnfields</primary>
4872 <secondary>with COPY</secondary>
4878 Returns the number of columns (fields) to be copied.
4883 <varlistentry id="libpq-pqbinarytuples-1">
4885 <function>PQbinaryTuples</function>
4887 <primary>PQbinaryTuples</primary>
4888 <secondary>with COPY</secondary>
4894 0 indicates the overall copy format is textual (rows separated by
4895 newlines, columns separated by separator characters, etc). 1
4896 indicates the overall copy format is binary. See <xref
4897 linkend="sql-copy"> for more information.
4902 <varlistentry id="libpq-pqfformat-1">
4904 <function>PQfformat</function>
4906 <primary>PQfformat</primary>
4907 <secondary>with COPY</secondary>
4913 Returns the format code (0 for text, 1 for binary) associated with
4914 each column of the copy operation. The per-column format codes
4915 will always be zero when the overall copy format is textual, but
4916 the binary format can support both text and binary columns.
4917 (However, as of the current implementation of <command>COPY</>,
4918 only binary columns appear in a binary copy; so the per-column
4919 formats always match the overall format at present.)
4928 These additional data values are only available when using protocol
4929 3.0. When using protocol 2.0, all these functions will return 0.
4933 <sect2 id="libpq-copy-send">
4934 <title>Functions for Sending <command>COPY</command> Data</title>
4937 These functions are used to send data during <literal>COPY FROM
4938 STDIN</>. They will fail if called when the connection is not in
4939 <literal>COPY_IN</> state.
4943 <varlistentry id="libpq-pqputcopydata">
4945 <function>PQputCopyData</function>
4947 <primary>PQputCopyData</primary>
4953 Sends data to the server during <literal>COPY_IN</> state.
4955 int PQputCopyData(PGconn *conn,
4962 Transmits the <command>COPY</command> data in the specified
4963 <parameter>buffer</>, of length <parameter>nbytes</>, to the server.
4964 The result is 1 if the data was queued, zero if it was not queued
4965 because of full buffers (this will only happen in nonblocking mode),
4966 or -1 if an error occurred.
4967 (Use <function>PQerrorMessage</function> to retrieve details if
4968 the return value is -1. If the value is zero, wait for write-ready
4973 The application can divide the <command>COPY</command> data stream
4974 into buffer loads of any convenient size. Buffer-load boundaries
4975 have no semantic significance when sending. The contents of the
4976 data stream must match the data format expected by the
4977 <command>COPY</> command; see <xref linkend="sql-copy"> for details.
4982 <varlistentry id="libpq-pqputcopyend">
4984 <function>PQputCopyEnd</function>
4986 <primary>PQputCopyEnd</primary>
4992 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
4994 int PQputCopyEnd(PGconn *conn,
4995 const char *errormsg);
5000 Ends the <literal>COPY_IN</> operation successfully if
5001 <parameter>errormsg</> is <symbol>NULL</symbol>. If
5002 <parameter>errormsg</> is not <symbol>NULL</symbol> then the
5003 <command>COPY</> is forced to fail, with the string pointed to by
5004 <parameter>errormsg</> used as the error message. (One should not
5005 assume that this exact error message will come back from the server,
5006 however, as the server might have already failed the
5007 <command>COPY</> for its own reasons. Also note that the option
5008 to force failure does not work when using pre-3.0-protocol
5013 The result is 1 if the termination message was sent; or in
5014 nonblocking mode, this may only indicate that the termination
5015 message was successfully queued. (In nonblocking mode, to be
5016 certain that the data has been sent, you should next wait for
5017 write-ready and call <function>PQflush</>, repeating until it
5018 returns zero.) Zero indicates that the function could not queue
5019 the termination message because of full buffers; this will only
5020 happen in nonblocking mode. (In this case, wait for
5021 write-ready and try the <function>PQputCopyEnd</> call
5022 again.) If a hard error occurs, -1 is returned; you can use
5023 <function>PQerrorMessage</function> to retrieve details.
5027 After successfully calling <function>PQputCopyEnd</>, call
5028 <function>PQgetResult</> to obtain the final result status of the
5029 <command>COPY</> command. One can wait for this result to be
5030 available in the usual way. Then return to normal operation.
5038 <sect2 id="libpq-copy-receive">
5039 <title>Functions for Receiving <command>COPY</command> Data</title>
5042 These functions are used to receive data during <literal>COPY TO
5043 STDOUT</>. They will fail if called when the connection is not in
5044 <literal>COPY_OUT</> state.
5048 <varlistentry id="libpq-pqgetcopydata">
5050 <function>PQgetCopyData</function>
5052 <primary>PQgetCopyData</primary>
5058 Receives data from the server during <literal>COPY_OUT</> state.
5060 int PQgetCopyData(PGconn *conn,
5067 Attempts to obtain another row of data from the server during a
5068 <command>COPY</command>. Data is always returned one data row at
5069 a time; if only a partial row is available, it is not returned.
5070 Successful return of a data row involves allocating a chunk of
5071 memory to hold the data. The <parameter>buffer</> parameter must
5072 be non-<symbol>NULL</symbol>. <parameter>*buffer</> is set to
5073 point to the allocated memory, or to <symbol>NULL</symbol> in cases
5074 where no buffer is returned. A non-<symbol>NULL</symbol> result
5075 buffer should be freed using <function>PQfreemem</> when no longer
5080 When a row is successfully returned, the return value is the number
5081 of data bytes in the row (this will always be greater than zero).
5082 The returned string is always null-terminated, though this is
5083 probably only useful for textual <command>COPY</command>. A result
5084 of zero indicates that the <command>COPY</command> is still in
5085 progress, but no row is yet available (this is only possible when
5086 <parameter>async</> is true). A result of -1 indicates that the
5087 <command>COPY</command> is done. A result of -2 indicates that an
5088 error occurred (consult <function>PQerrorMessage</> for the reason).
5092 When <parameter>async</> is true (not zero),
5093 <function>PQgetCopyData</> will not block waiting for input; it
5094 will return zero if the <command>COPY</command> is still in progress
5095 but no complete row is available. (In this case wait for read-ready
5096 and then call <function>PQconsumeInput</> before calling
5097 <function>PQgetCopyData</> again.) When <parameter>async</> is
5098 false (zero), <function>PQgetCopyData</> will block until data is
5099 available or the operation completes.
5103 After <function>PQgetCopyData</> returns -1, call
5104 <function>PQgetResult</> to obtain the final result status of the
5105 <command>COPY</> command. One can wait for this result to be
5106 available in the usual way. Then return to normal operation.
5114 <sect2 id="libpq-copy-deprecated">
5115 <title>Obsolete Functions for <command>COPY</command></title>
5118 These functions represent older methods of handling <command>COPY</>.
5119 Although they still work, they are deprecated due to poor error handling,
5120 inconvenient methods of detecting end-of-data, and lack of support for binary
5121 or nonblocking transfers.
5125 <varlistentry id="libpq-pqgetline">
5127 <function>PQgetline</function>
5129 <primary>PQgetline</primary>
5135 Reads a newline-terminated line of characters (transmitted
5136 by the server) into a buffer string of size <parameter>length</>.
5138 int PQgetline(PGconn *conn,
5145 This function copies up to <parameter>length</>-1 characters into
5146 the buffer and converts the terminating newline into a zero byte.
5147 <function>PQgetline</function> returns <symbol>EOF</symbol> at the
5148 end of input, 0 if the entire line has been read, and 1 if the
5149 buffer is full but the terminating newline has not yet been read.
5152 Note that the application must check to see if a new line consists
5153 of the two characters <literal>\.</literal>, which indicates
5154 that the server has finished sending the results of the
5155 <command>COPY</command> command. If the application might receive
5156 lines that are more than <parameter>length</>-1 characters long,
5157 care is needed to be sure it recognizes the <literal>\.</literal>
5158 line correctly (and does not, for example, mistake the end of a
5159 long data line for a terminator line).
5164 <varlistentry id="libpq-pqgetlineasync">
5166 <function>PQgetlineAsync</function>
5168 <primary>PQgetlineAsync</primary>
5174 Reads a row of <command>COPY</command> data (transmitted by the
5175 server) into a buffer without blocking.
5177 int PQgetlineAsync(PGconn *conn,
5184 This function is similar to <function>PQgetline</function>, but it can be used
5186 that must read <command>COPY</command> data asynchronously, that is, without blocking.
5187 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
5189 application should call <function>PQconsumeInput</function> and
5190 <function>PQgetlineAsync</function> until the
5191 end-of-data signal is detected.
5194 Unlike <function>PQgetline</function>, this function takes
5195 responsibility for detecting end-of-data.
5199 On each call, <function>PQgetlineAsync</function> will return data if a
5200 complete data row is available in <application>libpq</>'s input buffer.
5201 Otherwise, no data is returned until the rest of the row arrives.
5202 The function returns -1 if the end-of-copy-data marker has been recognized,
5203 or 0 if no data is available, or a positive number giving the number of
5204 bytes of data returned. If -1 is returned, the caller must next call
5205 <function>PQendcopy</function>, and then return to normal processing.
5209 The data returned will not extend beyond a data-row boundary. If possible
5210 a whole row will be returned at one time. But if the buffer offered by
5211 the caller is too small to hold a row sent by the server, then a partial
5212 data row will be returned. With textual data this can be detected by testing
5213 whether the last returned byte is <literal>\n</literal> or not. (In a binary
5214 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
5215 equivalent determination.)
5216 The returned string is not null-terminated. (If you want to add a
5217 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
5218 than the room actually available.)
5223 <varlistentry id="libpq-pqputline">
5225 <function>PQputline</function>
5227 <primary>PQputline</primary>
5233 Sends a null-terminated string to the server. Returns 0 if
5234 OK and <symbol>EOF</symbol> if unable to send the string.
5236 int PQputline(PGconn *conn,
5237 const char *string);
5242 The <command>COPY</command> data stream sent by a series of calls
5243 to <function>PQputline</function> has the same format as that
5244 returned by <function>PQgetlineAsync</function>, except that
5245 applications are not obliged to send exactly one data row per
5246 <function>PQputline</function> call; it is okay to send a partial
5247 line or multiple lines per call.
5252 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
5253 for the application to explicitly send the two characters
5254 <literal>\.</literal> as a final line to indicate to the server that it had
5255 finished sending <command>COPY</> data. While this still works, it is deprecated and the
5256 special meaning of <literal>\.</literal> can be expected to be removed in a
5257 future release. It is sufficient to call <function>PQendcopy</function> after
5258 having sent the actual data.
5264 <varlistentry id="libpq-pqputnbytes">
5266 <function>PQputnbytes</function>
5268 <primary>PQputnbytes</primary>
5274 Sends a non-null-terminated string to the server. Returns
5275 0 if OK and <symbol>EOF</symbol> if unable to send the string.
5277 int PQputnbytes(PGconn *conn,
5284 This is exactly like <function>PQputline</function>, except that the data
5285 buffer need not be null-terminated since the number of bytes to send is
5286 specified directly. Use this procedure when sending binary data.
5291 <varlistentry id="libpq-pqendcopy">
5293 <function>PQendcopy</function>
5295 <primary>PQendcopy</primary>
5301 Synchronizes with the server.
5303 int PQendcopy(PGconn *conn);
5305 This function waits until the server has finished the copying.
5306 It should either be issued when the last string has been sent
5307 to the server using <function>PQputline</function> or when the
5308 last string has been received from the server using
5309 <function>PGgetline</function>. It must be issued or the server
5310 will get <quote>out of sync</quote> with the client. Upon return
5311 from this function, the server is ready to receive the next SQL
5312 command. The return value is 0 on successful completion,
5313 nonzero otherwise. (Use <function>PQerrorMessage</function> to
5314 retrieve details if the return value is nonzero.)
5318 When using <function>PQgetResult</function>, the application should
5319 respond to a <literal>PGRES_COPY_OUT</literal> result by executing
5320 <function>PQgetline</function> repeatedly, followed by
5321 <function>PQendcopy</function> after the terminator line is seen.
5322 It should then return to the <function>PQgetResult</function> loop
5323 until <function>PQgetResult</function> returns a null pointer.
5324 Similarly a <literal>PGRES_COPY_IN</literal> result is processed
5325 by a series of <function>PQputline</function> calls followed by
5326 <function>PQendcopy</function>, then return to the
5327 <function>PQgetResult</function> loop. This arrangement will
5328 ensure that a <command>COPY</command> command embedded in a series
5329 of <acronym>SQL</acronym> commands will be executed correctly.
5333 Older applications are likely to submit a <command>COPY</command>
5334 via <function>PQexec</function> and assume that the transaction
5335 is done after <function>PQendcopy</function>. This will work
5336 correctly only if the <command>COPY</command> is the only
5337 <acronym>SQL</acronym> command in the command string.
5347 <sect1 id="libpq-control">
5348 <title>Control Functions</title>
5351 These functions control miscellaneous details of <application>libpq</>'s
5356 <varlistentry id="libpq-pqclientencoding">
5358 <function>PQclientEncoding</function>
5360 <primary>PQclientEncoding</primary>
5366 Returns the client encoding.
5368 int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
5371 Note that it returns the encoding ID, not a symbolic string
5372 such as <literal>EUC_JP</literal>. If unsuccessful, it returns -1.
5373 To convert an encoding ID to an encoding name, you
5377 char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
5383 <varlistentry id="libpq-pqsetclientencoding">
5385 <function>PQsetClientEncoding</function>
5387 <primary>PQsetClientEncoding</primary>
5393 Sets the client encoding.
5395 int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
5398 <replaceable>conn</replaceable> is a connection to the server,
5399 and <replaceable>encoding</replaceable> is the encoding you want to
5400 use. If the function successfully sets the encoding, it returns 0,
5401 otherwise -1. The current encoding for this connection can be
5402 determined by using <function>PQclientEncoding</>.
5407 <varlistentry id="libpq-pqseterrorverbosity">
5409 <function>PQsetErrorVerbosity</function>
5411 <primary>PQsetErrorVerbosity</primary>
5417 Determines the verbosity of messages returned by
5418 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
5427 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
5430 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
5431 the connection's previous setting. In <firstterm>TERSE</> mode,
5432 returned messages include severity, primary text, and position only;
5433 this will normally fit on a single line. The default mode produces
5434 messages that include the above plus any detail, hint, or context
5435 fields (these might span multiple lines). The <firstterm>VERBOSE</>
5436 mode includes all available fields. Changing the verbosity does not
5437 affect the messages available from already-existing
5438 <structname>PGresult</> objects, only subsequently-created ones.
5443 <varlistentry id="libpq-pqtrace">
5445 <function>PQtrace</function>
5447 <primary>PQtrace</primary>
5453 Enables tracing of the client/server communication to a debugging file stream.
5455 void PQtrace(PGconn *conn, FILE *stream);
5461 On Windows, if the <application>libpq</> library and an application are
5462 compiled with different flags, this function call will crash the
5463 application because the internal representation of the <literal>FILE</>
5464 pointers differ. Specifically, multithreaded/single-threaded,
5465 release/debug, and static/dynamic flags should be the same for the
5466 library and all applications using that library.
5473 <varlistentry id="libpq-pquntrace">
5475 <function>PQuntrace</function>
5477 <primary>PQuntrace</primary>
5483 Disables tracing started by <function>PQtrace</function>.
5485 void PQuntrace(PGconn *conn);
5494 <sect1 id="libpq-misc">
5495 <title>Miscellaneous Functions</title>
5498 As always, there are some functions that just don't fit anywhere.
5502 <varlistentry id="libpq-pqfreemem">
5504 <function>PQfreemem</function>
5506 <primary>PQfreemem</primary>
5512 Frees memory allocated by <application>libpq</>.
5514 void PQfreemem(void *ptr);
5519 Frees memory allocated by <application>libpq</>, particularly
5520 <function>PQescapeByteaConn</function>,
5521 <function>PQescapeBytea</function>,
5522 <function>PQunescapeBytea</function>,
5523 and <function>PQnotifies</function>.
5524 It is particularly important that this function, rather than
5525 <function>free()</>, be used on Microsoft Windows. This is because
5526 allocating memory in a DLL and releasing it in the application works
5527 only if multithreaded/single-threaded, release/debug, and static/dynamic
5528 flags are the same for the DLL and the application. On non-Microsoft
5529 Windows platforms, this function is the same as the standard library
5530 function <function>free()</>.
5535 <varlistentry id="libpq-pqconninfofree">
5537 <function>PQconninfoFree</function>
5539 <primary>PQconninfoFree</primary>
5545 Frees the data structures allocated by
5546 <function>PQconndefaults</> or <function>PQconninfoParse</>.
5548 void PQconninfoFree(PQconninfoOption *connOptions);
5553 A simple <function>PQfreemem</function> will not do for this, since
5554 the array contains references to subsidiary strings.
5559 <varlistentry id="libpq-pqencryptpassword">
5561 <function>PQencryptPassword</function>
5563 <primary>PQencryptPassword</primary>
5569 Prepares the encrypted form of a <productname>PostgreSQL</> password.
5571 char * PQencryptPassword(const char *passwd, const char *user);
5573 This function is intended to be used by client applications that
5574 wish to send commands like <literal>ALTER USER joe PASSWORD
5575 'pwd'</>. It is good practice not to send the original cleartext
5576 password in such a command, because it might be exposed in command
5577 logs, activity displays, and so on. Instead, use this function to
5578 convert the password to encrypted form before it is sent. The
5579 arguments are the cleartext password, and the SQL name of the user
5580 it is for. The return value is a string allocated by
5581 <function>malloc</function>, or <symbol>NULL</symbol> if out of
5582 memory. The caller can assume the string doesn't contain any
5583 special characters that would require escaping. Use
5584 <function>PQfreemem</> to free the result when done with it.
5589 <varlistentry id="libpq-pqmakeemptypgresult">
5591 <function>PQmakeEmptyPGresult</function>
5593 <primary>PQmakeEmptyPGresult</primary>
5599 Constructs an empty <structname>PGresult</structname> object with the given status.
5601 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
5606 This is <application>libpq</>'s internal function to allocate and
5607 initialize an empty <structname>PGresult</structname> object. This
5608 function returns <symbol>NULL</> if memory could not be allocated. It is
5609 exported because some applications find it useful to generate result
5610 objects (particularly objects with error status) themselves. If
5611 <parameter>conn</parameter> is not null and <parameter>status</>
5612 indicates an error, the current error message of the specified
5613 connection is copied into the <structname>PGresult</structname>.
5614 Also, if <parameter>conn</parameter> is not null, any event procedures
5615 registered in the connection are copied into the
5616 <structname>PGresult</structname>. (They do not get
5617 <literal>PGEVT_RESULTCREATE</> calls, but see
5618 <function>PQfireResultCreateEvents</function>.)
5619 Note that <function>PQclear</function> should eventually be called
5620 on the object, just as with a <structname>PGresult</structname>
5621 returned by <application>libpq</application> itself.
5626 <varlistentry id="libpq-pqfireresultcreateevents">
5628 <function>PQfireResultCreateEvents</function>
5630 <primary>PQfireResultCreateEvents</primary>
5635 Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
5636 linkend="libpq-events">) for each event procedure registered in the
5637 <structname>PGresult</structname> object. Returns non-zero for success,
5638 zero if any event procedure fails.
5641 int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
5646 The <literal>conn</> argument is passed through to event procedures
5647 but not used directly. It can be <symbol>NULL</> if the event
5648 procedures won't use it.
5652 Event procedures that have already received a
5653 <literal>PGEVT_RESULTCREATE</> or <literal>PGEVT_RESULTCOPY</> event
5654 for this object are not fired again.
5658 The main reason that this function is separate from
5659 <function>PQmakeEmptyPGResult</function> is that it is often appropriate
5660 to create a <structname>PGresult</structname> and fill it with data
5661 before invoking the event procedures.
5666 <varlistentry id="libpq-pqcopyresult">
5668 <function>PQcopyResult</function>
5670 <primary>PQcopyResult</primary>
5676 Makes a copy of a <structname>PGresult</structname> object. The copy is
5677 not linked to the source result in any way and
5678 <function>PQclear</function> must be called when the copy is no longer
5679 needed. If the function fails, <symbol>NULL</> is returned.
5682 PGresult *PQcopyResult(const PGresult *src, int flags);
5687 This is not intended to make an exact copy. The returned result is
5688 always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
5689 copy any error message in the source. (It does copy the command status
5690 string, however.) The <parameter>flags</parameter> argument determines
5691 what else is copied. It is a bitwise OR of several flags.
5692 <literal>PG_COPYRES_ATTRS</literal> specifies copying the source
5693 result's attributes (column definitions).
5694 <literal>PG_COPYRES_TUPLES</literal> specifies copying the source
5695 result's tuples. (This implies copying the attributes, too.)
5696 <literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
5697 copying the source result's notify hooks.
5698 <literal>PG_COPYRES_EVENTS</literal> specifies copying the source
5699 result's events. (But any instance data associated with the source
5705 <varlistentry id="libpq-pqsetresultattrs">
5707 <function>PQsetResultAttrs</function>
5709 <primary>PQsetResultAttrs</primary>
5715 Sets the attributes of a <structname>PGresult</structname> object.
5717 int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
5722 The provided <parameter>attDescs</parameter> are copied into the result.
5723 If the <parameter>attDescs</parameter> pointer is <symbol>NULL</> or
5724 <parameter>numAttributes</parameter> is less than one, the request is
5725 ignored and the function succeeds. If <parameter>res</parameter>
5726 already contains attributes, the function will fail. If the function
5727 fails, the return value is zero. If the function succeeds, the return
5733 <varlistentry id="libpq-pqsetvalue">
5735 <function>PQsetvalue</function>
5737 <primary>PQsetvalue</primary>
5743 Sets a tuple field value of a <structname>PGresult</structname> object.
5745 int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
5750 The function will automatically grow the result's internal tuples array
5751 as needed. However, the <parameter>tup_num</parameter> argument must be
5752 less than or equal to <function>PQntuples</function>, meaning this
5753 function can only grow the tuples array one tuple at a time. But any
5754 field of any existing tuple can be modified in any order. If a value at
5755 <parameter>field_num</parameter> already exists, it will be overwritten.
5756 If <parameter>len</parameter> is -1 or
5757 <parameter>value</parameter> is <symbol>NULL</>, the field value
5758 will be set to an SQL null value. The
5759 <parameter>value</parameter> is copied into the result's private storage,
5760 thus is no longer needed after the function
5761 returns. If the function fails, the return value is zero. If the
5762 function succeeds, the return value is non-zero.
5767 <varlistentry id="libpq-pqresultalloc">
5769 <function>PQresultAlloc</function>
5771 <primary>PQresultAlloc</primary>
5777 Allocate subsidiary storage for a <structname>PGresult</structname> object.
5779 void *PQresultAlloc(PGresult *res, size_t nBytes);
5784 Any memory allocated with this function will be freed when
5785 <parameter>res</parameter> is cleared. If the function fails,
5786 the return value is <symbol>NULL</>. The result is
5787 guaranteed to be adequately aligned for any type of data,
5788 just as for <function>malloc</>.
5793 <varlistentry id="libpq-pqlibversion">
5795 <function>PQlibVersion</function>
5797 <primary>PQlibVersion</primary>
5798 <seealso>PQserverVersion</seealso>
5804 Return the version of <productname>libpq</> that is being used.
5806 int PQlibVersion(void);
5811 The result of this function can be used to determine, at
5812 run time, if specific functionality is available in the currently
5813 loaded version of libpq. The function can be used, for example,
5814 to determine which connection options are available for
5815 <function>PQconnectdb</> or if the <literal>hex</> <type>bytea</>
5816 output added in PostgreSQL 9.0 is supported.
5820 The number is formed by converting the major, minor, and revision
5821 numbers into two-decimal-digit numbers and appending them together.
5822 For example, version 9.1 will be returned as 90100, and version
5823 9.1.2 will be returned as 90102 (leading zeroes are not shown).
5828 This function appeared in <productname>PostgreSQL</> version 9.1, so
5829 it cannot be used to detect required functionality in earlier
5830 versions, since linking to it will create a link dependency
5841 <sect1 id="libpq-notice-processing">
5842 <title>Notice Processing</title>
5844 <indexterm zone="libpq-notice-processing">
5845 <primary>notice processing</primary>
5846 <secondary>in libpq</secondary>
5850 Notice and warning messages generated by the server are not returned
5851 by the query execution functions, since they do not imply failure of
5852 the query. Instead they are passed to a notice handling function, and
5853 execution continues normally after the handler returns. The default
5854 notice handling function prints the message on
5855 <filename>stderr</filename>, but the application can override this
5856 behavior by supplying its own handling function.
5860 For historical reasons, there are two levels of notice handling, called
5861 the notice receiver and notice processor. The default behavior is for
5862 the notice receiver to format the notice and pass a string to the notice
5863 processor for printing. However, an application that chooses to provide
5864 its own notice receiver will typically ignore the notice processor
5865 layer and just do all the work in the notice receiver.
5869 The function <function>PQsetNoticeReceiver</function>
5870 <indexterm><primary>notice receiver</></>
5871 <indexterm><primary>PQsetNoticeReceiver</></> sets or
5872 examines the current notice receiver for a connection object.
5873 Similarly, <function>PQsetNoticeProcessor</function>
5874 <indexterm><primary>notice processor</></>
5875 <indexterm><primary>PQsetNoticeProcessor</></> sets or
5876 examines the current notice processor.
5879 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
5882 PQsetNoticeReceiver(PGconn *conn,
5883 PQnoticeReceiver proc,
5886 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
5889 PQsetNoticeProcessor(PGconn *conn,
5890 PQnoticeProcessor proc,
5894 Each of these functions returns the previous notice receiver or
5895 processor function pointer, and sets the new value. If you supply a
5896 null function pointer, no action is taken, but the current pointer is
5901 When a notice or warning message is received from the server, or
5902 generated internally by <application>libpq</application>, the notice
5903 receiver function is called. It is passed the message in the form of
5904 a <symbol>PGRES_NONFATAL_ERROR</symbol>
5905 <structname>PGresult</structname>. (This allows the receiver to extract
5906 individual fields using <function>PQresultErrorField</>, or the complete
5907 preformatted message using <function>PQresultErrorMessage</>.) The same
5908 void pointer passed to <function>PQsetNoticeReceiver</function> is also
5909 passed. (This pointer can be used to access application-specific state
5914 The default notice receiver simply extracts the message (using
5915 <function>PQresultErrorMessage</>) and passes it to the notice
5920 The notice processor is responsible for handling a notice or warning
5921 message given in text form. It is passed the string text of the message
5922 (including a trailing newline), plus a void pointer that is the same
5923 one passed to <function>PQsetNoticeProcessor</function>. (This pointer
5924 can be used to access application-specific state if needed.)
5928 The default notice processor is simply:
5931 defaultNoticeProcessor(void *arg, const char *message)
5933 fprintf(stderr, "%s", message);
5939 Once you have set a notice receiver or processor, you should expect
5940 that that function could be called as long as either the
5941 <structname>PGconn</> object or <structname>PGresult</> objects made
5942 from it exist. At creation of a <structname>PGresult</>, the
5943 <structname>PGconn</>'s current notice handling pointers are copied
5944 into the <structname>PGresult</> for possible use by functions like
5945 <function>PQgetvalue</function>.
5950 <sect1 id="libpq-events">
5951 <title>Event System</title>
5954 <application>libpq</application>'s event system is designed to notify
5955 registered event handlers about interesting
5956 <application>libpq</application> events, such as the creation or
5957 destruction of <structname>PGconn</structname> and
5958 <structname>PGresult</structname> objects. A principal use case is that
5959 this allows applications to associate their own data with a
5960 <structname>PGconn</structname> or <structname>PGresult</structname>
5961 and ensure that that data is freed at an appropriate time.
5965 Each registered event handler is associated with two pieces of data,
5966 known to <application>libpq</application> only as opaque <literal>void *</>
5967 pointers. There is a <firstterm>passthrough</> pointer that is provided
5968 by the application when the event handler is registered with a
5969 <structname>PGconn</>. The passthrough pointer never changes for the
5970 life of the <structname>PGconn</> and all <structname>PGresult</>s
5971 generated from it; so if used, it must point to long-lived data.
5972 In addition there is an <firstterm>instance data</> pointer, which starts
5973 out <symbol>NULL</> in every <structname>PGconn</> and <structname>PGresult</>.
5974 This pointer can be manipulated using the
5975 <function>PQinstanceData</function>,
5976 <function>PQsetInstanceData</function>,
5977 <function>PQresultInstanceData</function> and
5978 <function>PQsetResultInstanceData</function> functions. Note that
5979 unlike the passthrough pointer, instance data of a <structname>PGconn</>
5980 is not automatically inherited by <structname>PGresult</>s created from
5981 it. <application>libpq</application> does not know what passthrough
5982 and instance data pointers point to (if anything) and will never attempt
5983 to free them — that is the responsibility of the event handler.
5986 <sect2 id="libpq-events-types">
5987 <title>Event Types</title>
5990 The enum <literal>PGEventId</> names the types of events handled by
5991 the event system. All its values have names beginning with
5992 <literal>PGEVT</literal>. For each event type, there is a corresponding
5993 event info structure that carries the parameters passed to the event
5994 handlers. The event types are:
5998 <varlistentry id="libpq-pgevt-register">
5999 <term><literal>PGEVT_REGISTER</literal></term>
6002 The register event occurs when <function>PQregisterEventProc</function>
6003 is called. It is the ideal time to initialize any
6004 <literal>instanceData</literal> an event procedure may need. Only one
6005 register event will be fired per event handler per connection. If the
6006 event procedure fails, the registration is aborted.
6015 When a <literal>PGEVT_REGISTER</literal> event is received, the
6016 <parameter>evtInfo</parameter> pointer should be cast to a
6017 <structname>PGEventRegister *</structname>. This structure contains a
6018 <structname>PGconn</structname> that should be in the
6019 <literal>CONNECTION_OK</literal> status; guaranteed if one calls
6020 <function>PQregisterEventProc</function> right after obtaining a good
6021 <structname>PGconn</structname>. When returning a failure code, all
6022 cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
6028 <varlistentry id="libpq-pgevt-connreset">
6029 <term><literal>PGEVT_CONNRESET</literal></term>
6032 The connection reset event is fired on completion of
6033 <function>PQreset</function> or <function>PQresetPoll</function>. In
6034 both cases, the event is only fired if the reset was successful. If
6035 the event procedure fails, the entire connection reset will fail; the
6036 <structname>PGconn</structname> is put into
6037 <literal>CONNECTION_BAD</literal> status and
6038 <function>PQresetPoll</function> will return
6039 <literal>PGRES_POLLING_FAILED</literal>.
6048 When a <literal>PGEVT_CONNRESET</literal> event is received, the
6049 <parameter>evtInfo</parameter> pointer should be cast to a
6050 <structname>PGEventConnReset *</structname>. Although the contained
6051 <structname>PGconn</structname> was just reset, all event data remains
6052 unchanged. This event should be used to reset/reload/requery any
6053 associated <literal>instanceData</literal>. Note that even if the
6054 event procedure fails to process <literal>PGEVT_CONNRESET</>, it will
6055 still receive a <literal>PGEVT_CONNDESTROY</> event when the connection
6061 <varlistentry id="libpq-pgevt-conndestroy">
6062 <term><literal>PGEVT_CONNDESTROY</literal></term>
6065 The connection destroy event is fired in response to
6066 <function>PQfinish</function>. It is the event procedure's
6067 responsibility to properly clean up its event data as libpq has no
6068 ability to manage this memory. Failure to clean up will lead
6075 } PGEventConnDestroy;
6078 When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
6079 <parameter>evtInfo</parameter> pointer should be cast to a
6080 <structname>PGEventConnDestroy *</structname>. This event is fired
6081 prior to <function>PQfinish</function> performing any other cleanup.
6082 The return value of the event procedure is ignored since there is no
6083 way of indicating a failure from <function>PQfinish</function>. Also,
6084 an event procedure failure should not abort the process of cleaning up
6090 <varlistentry id="libpq-pgevt-resultcreate">
6091 <term><literal>PGEVT_RESULTCREATE</literal></term>
6094 The result creation event is fired in response to any query execution
6095 function that generates a result, including
6096 <function>PQgetResult</function>. This event will only be fired after
6097 the result has been created successfully.
6104 } PGEventResultCreate;
6107 When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
6108 <parameter>evtInfo</parameter> pointer should be cast to a
6109 <structname>PGEventResultCreate *</structname>. The
6110 <parameter>conn</parameter> is the connection used to generate the
6111 result. This is the ideal place to initialize any
6112 <literal>instanceData</literal> that needs to be associated with the
6113 result. If the event procedure fails, the result will be cleared and
6114 the failure will be propagated. The event procedure must not try to
6115 <function>PQclear</> the result object for itself. When returning a
6116 failure code, all cleanup must be performed as no
6117 <literal>PGEVT_RESULTDESTROY</literal> event will be sent.
6122 <varlistentry id="libpq-pgevt-resultcopy">
6123 <term><literal>PGEVT_RESULTCOPY</literal></term>
6126 The result copy event is fired in response to
6127 <function>PQcopyResult</function>. This event will only be fired after
6128 the copy is complete. Only event procedures that have
6129 successfully handled the <literal>PGEVT_RESULTCREATE</literal>
6130 or <literal>PGEVT_RESULTCOPY</literal> event for the source result
6131 will receive <literal>PGEVT_RESULTCOPY</literal> events.
6136 const PGresult *src;
6138 } PGEventResultCopy;
6141 When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
6142 <parameter>evtInfo</parameter> pointer should be cast to a
6143 <structname>PGEventResultCopy *</structname>. The
6144 <parameter>src</parameter> result is what was copied while the
6145 <parameter>dest</parameter> result is the copy destination. This event
6146 can be used to provide a deep copy of <literal>instanceData</literal>,
6147 since <literal>PQcopyResult</literal> cannot do that. If the event
6148 procedure fails, the entire copy operation will fail and the
6149 <parameter>dest</parameter> result will be cleared. When returning a
6150 failure code, all cleanup must be performed as no
6151 <literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
6157 <varlistentry id="libpq-pgevt-resultdestroy">
6158 <term><literal>PGEVT_RESULTDESTROY</literal></term>
6161 The result destroy event is fired in response to a
6162 <function>PQclear</function>. It is the event procedure's
6163 responsibility to properly clean up its event data as libpq has no
6164 ability to manage this memory. Failure to clean up will lead
6171 } PGEventResultDestroy;
6174 When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
6175 <parameter>evtInfo</parameter> pointer should be cast to a
6176 <structname>PGEventResultDestroy *</structname>. This event is fired
6177 prior to <function>PQclear</function> performing any other cleanup.
6178 The return value of the event procedure is ignored since there is no
6179 way of indicating a failure from <function>PQclear</function>. Also,
6180 an event procedure failure should not abort the process of cleaning up
6188 <sect2 id="libpq-events-proc">
6189 <title>Event Callback Procedure</title>
6192 <varlistentry id="libpq-pgeventproc">
6194 <literal>PGEventProc</literal>
6196 <primary>PGEventProc</primary>
6202 <literal>PGEventProc</literal> is a typedef for a pointer to an
6203 event procedure, that is, the user callback function that receives
6204 events from libpq. The signature of an event procedure must be
6207 int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
6210 The <parameter>evtId</parameter> parameter indicates which
6211 <literal>PGEVT</literal> event occurred. The
6212 <parameter>evtInfo</parameter> pointer must be cast to the appropriate
6213 structure type to obtain further information about the event.
6214 The <parameter>passThrough</parameter> parameter is the pointer
6215 provided to <function>PQregisterEventProc</function> when the event
6216 procedure was registered. The function should return a non-zero value
6217 if it succeeds and zero if it fails.
6221 A particular event procedure can be registered only once in any
6222 <structname>PGconn</>. This is because the address of the procedure
6223 is used as a lookup key to identify the associated instance data.
6228 On Windows, functions can have two different addresses: one visible
6229 from outside a DLL and another visible from inside the DLL. One
6230 should be careful that only one of these addresses is used with
6231 <application>libpq</>'s event-procedure functions, else confusion will
6232 result. The simplest rule for writing code that will work is to
6233 ensure that event procedures are declared <literal>static</>. If the
6234 procedure's address must be available outside its own source file,
6235 expose a separate function to return the address.
6243 <sect2 id="libpq-events-funcs">
6244 <title>Event Support Functions</title>
6247 <varlistentry id="libpq-pqregistereventproc">
6249 <function>PQregisterEventProc</function>
6251 <primary>PQregisterEventProc</primary>
6257 Registers an event callback procedure with libpq.
6260 int PQregisterEventProc(PGconn *conn, PGEventProc proc,
6261 const char *name, void *passThrough);
6266 An event procedure must be registered once on each
6267 <structname>PGconn</> you want to receive events about. There is no
6268 limit, other than memory, on the number of event procedures that
6269 can be registered with a connection. The function returns a non-zero
6270 value if it succeeds and zero if it fails.
6274 The <parameter>proc</parameter> argument will be called when a libpq
6275 event is fired. Its memory address is also used to lookup
6276 <literal>instanceData</literal>. The <parameter>name</parameter>
6277 argument is used to refer to the event procedure in error messages.
6278 This value cannot be <symbol>NULL</> or a zero-length string. The name string is
6279 copied into the <structname>PGconn</>, so what is passed need not be
6280 long-lived. The <parameter>passThrough</parameter> pointer is passed
6281 to the <parameter>proc</parameter> whenever an event occurs. This
6282 argument can be <symbol>NULL</>.
6287 <varlistentry id="libpq-pqsetinstancedata">
6289 <function>PQsetInstanceData</function>
6291 <primary>PQsetInstanceData</primary>
6296 Sets the connection <parameter>conn</>'s <literal>instanceData</>
6297 for procedure <parameter>proc</> to <parameter>data</>. This
6298 returns non-zero for success and zero for failure. (Failure is
6299 only possible if <parameter>proc</> has not been properly
6300 registered in <parameter>conn</>.)
6303 int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
6309 <varlistentry id="libpq-pqinstancedata">
6311 <function>PQinstanceData</function>
6313 <primary>PQinstanceData</primary>
6319 connection <parameter>conn</>'s <literal>instanceData</literal>
6320 associated with procedure <parameter>proc</>,
6321 or <symbol>NULL</symbol> if there is none.
6324 void *PQinstanceData(const PGconn *conn, PGEventProc proc);
6330 <varlistentry id="libpq-pqresultsetinstancedata">
6332 <function>PQresultSetInstanceData</function>
6334 <primary>PQresultSetInstanceData</primary>
6339 Sets the result's <literal>instanceData</>
6340 for <parameter>proc</> to <parameter>data</>. This returns
6341 non-zero for success and zero for failure. (Failure is only
6342 possible if <parameter>proc</> has not been properly registered
6346 int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
6352 <varlistentry id="libpq-pqresultinstancedata">
6354 <function>PQresultInstanceData</function>
6356 <primary>PQresultInstanceData</primary>
6361 Returns the result's <literal>instanceData</> associated with <parameter>proc</>, or <symbol>NULL</>
6365 void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
6373 <sect2 id="libpq-events-example">
6374 <title>Event Example</title>
6377 Here is a skeleton example of managing private data associated with
6378 libpq connections and results.
6383 /* required header for libpq events (note: includes libpq-fe.h) */
6384 #include <libpq-events.h>
6386 /* The instanceData */
6394 static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);
6401 PGconn *conn = PQconnectdb("dbname = postgres");
6403 if (PQstatus(conn) != CONNECTION_OK)
6405 fprintf(stderr, "Connection to database failed: %s",
6406 PQerrorMessage(conn));
6411 /* called once on any connection that should receive events.
6412 * Sends a PGEVT_REGISTER to myEventProc.
6414 if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
6416 fprintf(stderr, "Cannot register PGEventProc\n");
6421 /* conn instanceData is available */
6422 data = PQinstanceData(conn, myEventProc);
6424 /* Sends a PGEVT_RESULTCREATE to myEventProc */
6425 res = PQexec(conn, "SELECT 1 + 1");
6427 /* result instanceData is available */
6428 data = PQresultInstanceData(res, myEventProc);
6430 /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
6431 res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);
6433 /* result instanceData is available if PG_COPYRES_EVENTS was
6434 * used during the PQcopyResult call.
6436 data = PQresultInstanceData(res_copy, myEventProc);
6438 /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
6442 /* Sends a PGEVT_CONNDESTROY to myEventProc */
6449 myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
6453 case PGEVT_REGISTER:
6455 PGEventRegister *e = (PGEventRegister *)evtInfo;
6456 mydata *data = get_mydata(e->conn);
6458 /* associate app specific data with connection */
6459 PQsetInstanceData(e->conn, myEventProc, data);
6463 case PGEVT_CONNRESET:
6465 PGEventConnReset *e = (PGEventConnReset *)evtInfo;
6466 mydata *data = PQinstanceData(e->conn, myEventProc);
6469 memset(data, 0, sizeof(mydata));
6473 case PGEVT_CONNDESTROY:
6475 PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
6476 mydata *data = PQinstanceData(e->conn, myEventProc);
6478 /* free instance data because the conn is being destroyed */
6484 case PGEVT_RESULTCREATE:
6486 PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
6487 mydata *conn_data = PQinstanceData(e->conn, myEventProc);
6488 mydata *res_data = dup_mydata(conn_data);
6490 /* associate app specific data with result (copy it from conn) */
6491 PQsetResultInstanceData(e->result, myEventProc, res_data);
6495 case PGEVT_RESULTCOPY:
6497 PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
6498 mydata *src_data = PQresultInstanceData(e->src, myEventProc);
6499 mydata *dest_data = dup_mydata(src_data);
6501 /* associate app specific data with result (copy it from a result) */
6502 PQsetResultInstanceData(e->dest, myEventProc, dest_data);
6506 case PGEVT_RESULTDESTROY:
6508 PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
6509 mydata *data = PQresultInstanceData(e->result, myEventProc);
6511 /* free instance data because the result is being destroyed */
6517 /* unknown event ID, just return TRUE. */
6522 return TRUE; /* event processing succeeded */
6529 <sect1 id="libpq-envars">
6530 <title>Environment Variables</title>
6532 <indexterm zone="libpq-envars">
6533 <primary>environment variable</primary>
6537 The following environment variables can be used to select default
6538 connection parameter values, which will be used by
6539 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
6540 <function>PQsetdb</> if no value is directly specified by the calling
6541 code. These are useful to avoid hard-coding database connection
6542 information into simple client applications, for example.
6548 <primary><envar>PGHOST</envar></primary>
6550 <envar>PGHOST</envar> behaves the same as the <xref
6551 linkend="libpq-connect-host"> connection parameter.
6558 <primary><envar>PGHOSTADDR</envar></primary>
6560 <envar>PGHOSTADDR</envar> behaves the same as the <xref
6561 linkend="libpq-connect-hostaddr"> connection parameter.
6562 This can be set instead of or in addition to <envar>PGHOST</envar>
6563 to avoid DNS lookup overhead.
6570 <primary><envar>PGPORT</envar></primary>
6572 <envar>PGPORT</envar> behaves the same as the <xref
6573 linkend="libpq-connect-port"> connection parameter.
6580 <primary><envar>PGDATABASE</envar></primary>
6582 <envar>PGDATABASE</envar> behaves the same as the <xref
6583 linkend="libpq-connect-dbname"> connection parameter.
6590 <primary><envar>PGUSER</envar></primary>
6592 <envar>PGUSER</envar> behaves the same as the <xref
6593 linkend="libpq-connect-user"> connection parameter.
6600 <primary><envar>PGPASSWORD</envar></primary>
6602 <envar>PGPASSWORD</envar> behaves the same as the <xref
6603 linkend="libpq-connect-password"> connection parameter.
6604 Use of this environment variable
6605 is not recommended for security reasons, as some operating systems
6606 allow non-root users to see process environment variables via
6607 <application>ps</>; instead consider using the
6608 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
6615 <primary><envar>PGPASSFILE</envar></primary>
6617 <envar>PGPASSFILE</envar> specifies the name of the password file to
6618 use for lookups. If not set, it defaults to <filename>~/.pgpass</>
6619 (see <xref linkend="libpq-pgpass">).
6626 <primary><envar>PGSERVICE</envar></primary>
6628 <envar>PGSERVICE</envar> behaves the same as the <xref
6629 linkend="libpq-connect-service"> connection parameter.
6636 <primary><envar>PGSERVICEFILE</envar></primary>
6638 <envar>PGSERVICEFILE</envar> specifies the name of the per-user
6639 connection service file. If not set, it defaults
6640 to <filename>~/.pg_service.conf</>
6641 (see <xref linkend="libpq-pgservice">).
6648 <primary><envar>PGREALM</envar></primary>
6650 <envar>PGREALM</envar> sets the Kerberos realm to use with
6651 <productname>PostgreSQL</productname>, if it is different from the
6652 local realm. If <envar>PGREALM</envar> is set,
6653 <application>libpq</application> applications will attempt
6654 authentication with servers for this realm and use separate ticket
6655 files to avoid conflicts with local ticket files. This
6656 environment variable is only used if GSSAPI authentication is
6657 selected by the server.
6664 <primary><envar>PGOPTIONS</envar></primary>
6666 <envar>PGOPTIONS</envar> behaves the same as the <xref
6667 linkend="libpq-connect-options"> connection parameter.
6674 <primary><envar>PGAPPNAME</envar></primary>
6676 <envar>PGAPPNAME</envar> behaves the same as the <xref
6677 linkend="libpq-connect-application-name"> connection parameter.
6684 <primary><envar>PGSSLMODE</envar></primary>
6686 <envar>PGSSLMODE</envar> behaves the same as the <xref
6687 linkend="libpq-connect-sslmode"> connection parameter.
6694 <primary><envar>PGREQUIRESSL</envar></primary>
6696 <envar>PGREQUIRESSL</envar> behaves the same as the <xref
6697 linkend="libpq-connect-requiressl"> connection parameter.
6704 <primary><envar>PGSSLCOMPRESSION</envar></primary>
6706 <envar>PGSSLCOMPRESSION</envar> behaves the same as the <xref
6707 linkend="libpq-connect-sslcompression"> connection parameter.
6714 <primary><envar>PGSSLCERT</envar></primary>
6716 <envar>PGSSLCERT</envar> behaves the same as the <xref
6717 linkend="libpq-connect-sslcert"> connection parameter.
6724 <primary><envar>PGSSLKEY</envar></primary>
6726 <envar>PGSSLKEY</envar> behaves the same as the <xref
6727 linkend="libpq-connect-sslkey"> connection parameter.
6734 <primary><envar>PGSSLROOTCERT</envar></primary>
6736 <envar>PGSSLROOTCERT</envar> behaves the same as the <xref
6737 linkend="libpq-connect-sslrootcert"> connection parameter.
6744 <primary><envar>PGSSLCRL</envar></primary>
6746 <envar>PGSSLCRL</envar> behaves the same as the <xref
6747 linkend="libpq-connect-sslcrl"> connection parameter.
6754 <primary><envar>PGREQUIREPEER</envar></primary>
6756 <envar>PGREQUIREPEER</envar> behaves the same as the <xref
6757 linkend="libpq-connect-requirepeer"> connection parameter.
6764 <primary><envar>PGKRBSRVNAME</envar></primary>
6766 <envar>PGKRBSRVNAME</envar> behaves the same as the <xref
6767 linkend="libpq-connect-krbsrvname"> connection parameter.
6774 <primary><envar>PGGSSLIB</envar></primary>
6776 <envar>PGGSSLIB</envar> behaves the same as the <xref
6777 linkend="libpq-connect-gsslib"> connection parameter.
6784 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
6786 <envar>PGCONNECT_TIMEOUT</envar> behaves the same as the <xref
6787 linkend="libpq-connect-connect-timeout"> connection parameter.
6794 <primary><envar>PGCLIENTENCODING</envar></primary>
6796 <envar>PGCLIENTENCODING</envar> behaves the same as the <xref
6797 linkend="libpq-connect-client-encoding"> connection parameter.
6804 The following environment variables can be used to specify default
6805 behavior for each <productname>PostgreSQL</productname> session. (See
6806 also the <xref linkend="sql-alterrole">
6807 and <xref linkend="sql-alterdatabase">
6808 commands for ways to set default behavior on a per-user or per-database
6815 <primary><envar>PGDATESTYLE</envar></primary>
6817 <envar>PGDATESTYLE</envar> sets the default style of date/time
6818 representation. (Equivalent to <literal>SET datestyle TO
6826 <primary><envar>PGTZ</envar></primary>
6828 <envar>PGTZ</envar> sets the default time zone. (Equivalent to
6829 <literal>SET timezone TO ...</literal>.)
6836 <primary><envar>PGGEQO</envar></primary>
6838 <envar>PGGEQO</envar> sets the default mode for the genetic query
6839 optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
6844 Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set">
6845 for information on correct values for these
6846 environment variables.
6850 The following environment variables determine internal behavior of
6851 <application>libpq</application>; they override compiled-in defaults.
6857 <primary><envar>PGSYSCONFDIR</envar></primary>
6859 <envar>PGSYSCONFDIR</envar> sets the directory containing the
6860 <filename>pg_service.conf</> file and in a future version
6861 possibly other system-wide configuration files.
6868 <primary><envar>PGLOCALEDIR</envar></primary>
6870 <envar>PGLOCALEDIR</envar> sets the directory containing the
6871 <literal>locale</> files for message localization.
6880 <sect1 id="libpq-pgpass">
6881 <title>The Password File</title>
6883 <indexterm zone="libpq-pgpass">
6884 <primary>password file</primary>
6886 <indexterm zone="libpq-pgpass">
6887 <primary>.pgpass</primary>
6891 The file <filename>.pgpass</filename> in a user's home directory or the
6892 file referenced by <envar>PGPASSFILE</envar> can contain passwords to
6893 be used if the connection requires a password (and no password has been
6894 specified otherwise). On Microsoft Windows the file is named
6895 <filename>%APPDATA%\postgresql\pgpass.conf</> (where
6896 <filename>%APPDATA%</> refers to the Application Data subdirectory in
6897 the user's profile).
6901 This file should contain lines of the following format:
6903 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
6905 (You can add a reminder comment to the file by copying the line above and
6906 preceding it with <literal>#</>.)
6907 Each of the first four fields can be a literal value, or
6908 <literal>*</literal>, which matches anything. The password field from
6909 the first line that matches the current connection parameters will be
6910 used. (Therefore, put more-specific entries first when you are using
6911 wildcards.) If an entry needs to contain <literal>:</literal> or
6912 <literal>\</literal>, escape this character with <literal>\</literal>.
6913 A host name of <literal>localhost</> matches both TCP (host name
6914 <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
6915 or the default socket directory) connections coming from the local
6916 machine. In a standby server, a database name of <literal>replication</>
6917 matches streaming replication connections made to the master server.
6918 The <literal>database</> field is of limited usefulness because
6919 users have the same password for all databases in the same cluster.
6923 On Unix systems, the permissions on <filename>.pgpass</filename> must
6924 disallow any access to world or group; achieve this by the command
6925 <command>chmod 0600 ~/.pgpass</command>. If the permissions are less
6926 strict than this, the file will be ignored. On Microsoft Windows, it
6927 is assumed that the file is stored in a directory that is secure, so
6928 no special permissions check is made.
6933 <sect1 id="libpq-pgservice">
6934 <title>The Connection Service File</title>
6936 <indexterm zone="libpq-pgservice">
6937 <primary>connection service file</primary>
6939 <indexterm zone="libpq-pgservice">
6940 <primary>pg_service.conf</primary>
6942 <indexterm zone="libpq-pgservice">
6943 <primary>.pg_service.conf</primary>
6947 The connection service file allows libpq connection parameters to be
6948 associated with a single service name. That service name can then be
6949 specified by a libpq connection, and the associated settings will be
6950 used. This allows connection parameters to be modified without requiring
6951 a recompile of the libpq application. The service name can also be
6952 specified using the <envar>PGSERVICE</envar> environment variable.
6956 The connection service file can be a per-user service file
6957 at <filename>~/.pg_service.conf</filename> or the location
6958 specified by the environment variable <envar>PGSERVICEFILE</envar>,
6959 or it can be a system-wide file
6960 at <filename>`pg_config --sysconfdir`/pg_service.conf</filename> or in the directory
6961 specified by the environment variable
6962 <envar>PGSYSCONFDIR</envar>. If service definitions with the same
6963 name exist in the user and the system file, the user file takes
6968 The file uses an <quote>INI file</quote> format where the section
6969 name is the service name and the parameters are connection
6970 parameters; see <xref linkend="libpq-paramkeywords"> for a list. For
6979 An example file is provided at
6980 <filename>share/pg_service.conf.sample</filename>.
6985 <sect1 id="libpq-ldap">
6986 <title>LDAP Lookup of Connection Parameters</title>
6988 <indexterm zone="libpq-ldap">
6989 <primary>LDAP connection parameter lookup</primary>
6993 If <application>libpq</application> has been compiled with LDAP support (option
6994 <literal><option>--with-ldap</option></literal> for <command>configure</command>)
6995 it is possible to retrieve connection options like <literal>host</literal>
6996 or <literal>dbname</literal> via LDAP from a central server.
6997 The advantage is that if the connection parameters for a database change,
6998 the connection information doesn't have to be updated on all client machines.
7002 LDAP connection parameter lookup uses the connection service file
7003 <filename>pg_service.conf</filename> (see <xref
7004 linkend="libpq-pgservice">). A line in a
7005 <filename>pg_service.conf</filename> stanza that starts with
7006 <literal>ldap://</literal> will be recognized as an LDAP URL and an
7007 LDAP query will be performed. The result must be a list of
7008 <literal>keyword = value</literal> pairs which will be used to set
7009 connection options. The URL must conform to RFC 1959 and be of the
7012 ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
7014 where <replaceable>hostname</replaceable> defaults to
7015 <literal>localhost</literal> and <replaceable>port</replaceable>
7020 Processing of <filename>pg_service.conf</filename> is terminated after
7021 a successful LDAP lookup, but is continued if the LDAP server cannot
7022 be contacted. This is to provide a fallback with further LDAP URL
7023 lines that point to different LDAP servers, classical <literal>keyword
7024 = value</literal> pairs, or default connection options. If you would
7025 rather get an error message in this case, add a syntactically incorrect
7026 line after the LDAP URL.
7030 A sample LDAP entry that has been created with the LDIF file
7033 dn:cn=mydatabase,dc=mycompany,dc=com
7038 description:host=dbserver.mycompany.com
7039 description:port=5439
7040 description:dbname=mydb
7041 description:user=mydb_user
7042 description:sslmode=require
7044 might be queried with the following LDAP URL:
7046 ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydatabase)
7051 You can also mix regular service file entries with LDAP lookups.
7052 A complete example for a stanza in <filename>pg_service.conf</filename>
7055 # only host and port are stored in LDAP, specify dbname and user explicitly
7059 ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
7066 <sect1 id="libpq-ssl">
7067 <title>SSL Support</title>
7069 <indexterm zone="libpq-ssl">
7070 <primary>SSL</primary>
7074 <productname>PostgreSQL</> has native support for using <acronym>SSL</>
7075 connections to encrypt client/server communications for increased
7076 security. See <xref linkend="ssl-tcp"> for details about the server-side
7077 <acronym>SSL</> functionality.
7081 <application>libpq</application> reads the system-wide
7082 <productname>OpenSSL</productname> configuration file. By default, this
7083 file is named <filename>openssl.cnf</filename> and is located in the
7084 directory reported by <literal>openssl version -d</>. This default
7085 can be overridden by setting environment variable
7086 <envar>OPENSSL_CONF</envar> to the name of the desired configuration
7090 <sect2 id="libq-ssl-certificates">
7091 <title>Client Verification of Server Certificates</title>
7094 By default, <productname>PostgreSQL</> will not perform any verification of
7095 the server certificate. This means that it is possible to spoof the server
7096 identity (for example by modifying a DNS record or by taking over the server
7097 IP address) without the client knowing. In order to prevent spoofing,
7098 <acronym>SSL</> certificate verification must be used.
7102 If the parameter <literal>sslmode</> is set to <literal>verify-ca</>,
7103 libpq will verify that the server is trustworthy by checking the
7104 certificate chain up to a trusted certificate authority
7105 (<acronym>CA</>). If <literal>sslmode</> is set to <literal>verify-full</>,
7106 libpq will <emphasis>also</> verify that the server host name matches its
7107 certificate. The SSL connection will fail if the server certificate cannot
7108 be verified. <literal>verify-full</> is recommended in most
7109 security-sensitive environments.
7113 In <literal>verify-full</> mode, the <literal>cn</> (Common Name) attribute
7114 of the certificate is matched against the host name. If the <literal>cn</>
7115 attribute starts with an asterisk (<literal>*</>), it will be treated as
7116 a wildcard, and will match all characters <emphasis>except</> a dot
7117 (<literal>.</>). This means the certificate will not match subdomains.
7118 If the connection is made using an IP address instead of a host name, the
7119 IP address will be matched (without doing any DNS lookups).
7123 To allow server certificate verification, the certificate(s) of one or more
7124 trusted <acronym>CA</>s must be
7125 placed in the file <filename>~/.postgresql/root.crt</> in the user's home
7126 directory. If intermediate <acronym>CA</>s appear in
7127 <filename>root.crt</filename>, the file must also contain certificate
7128 chains to their root <acronym>CA</>s. (On Microsoft Windows the file is named
7129 <filename>%APPDATA%\postgresql\root.crt</filename>.)
7133 Certificate Revocation List (CRL) entries are also checked
7134 if the file <filename>~/.postgresql/root.crl</filename> exists
7135 (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
7140 The location of the root certificate file and the CRL can be changed by
7142 the connection parameters <literal>sslrootcert</> and <literal>sslcrl</>
7143 or the environment variables <envar>PGSSLROOTCERT</> and <envar>PGSSLCRL</>.
7148 For backwards compatibility with earlier versions of PostgreSQL, if a
7149 root CA file exists, the behavior of
7150 <literal>sslmode</literal>=<literal>require</literal> will be the same
7151 as that of <literal>verify-ca</literal>, meaning the server certificate
7152 is validated against the CA. Relying on this behavior is discouraged,
7153 and applications that need certificate validation should always use
7154 <literal>verify-ca</literal> or <literal>verify-full</literal>.
7159 <sect2 id="libpq-ssl-clientcert">
7160 <title>Client Certificates</title>
7163 If the server requests a trusted client certificate,
7164 <application>libpq</application> will send the certificate stored in
7165 file <filename>~/.postgresql/postgresql.crt</> in the user's home
7166 directory. The certificate must be signed by one of the certificate
7167 authorities (<acronym>CA</acronym>) trusted by the server. A matching
7168 private key file <filename>~/.postgresql/postgresql.key</> must also
7169 be present. The private
7170 key file must not allow any access to world or group; achieve this by the
7171 command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
7172 On Microsoft Windows these files are named
7173 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
7174 <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
7175 is no special permissions check since the directory is presumed secure.
7176 The location of the certificate and key files can be overridden by the
7177 connection parameters <literal>sslcert</> and <literal>sslkey</> or the
7178 environment variables <envar>PGSSLCERT</> and <envar>PGSSLKEY</>.
7182 In some cases, the client certificate might be signed by an
7183 <quote>intermediate</> certificate authority, rather than one that is
7184 directly trusted by the server. To use such a certificate, append the
7185 certificate of the signing authority to the <filename>postgresql.crt</>
7186 file, then its parent authority's certificate, and so on up to a certificate
7187 authority, <quote>root</> or <quote>intermediate</>, that is trusted by
7188 the server, i.e. signed by a certificate in the server's
7189 <filename>root.crt</filename> file.
7193 Note that the client's <filename>~/.postgresql/root.crt</> lists the top-level CAs
7194 that are considered trusted for signing server certificates. In principle it need
7195 not list the CA that signed the client's certificate, though in most cases
7196 that CA would also be trusted for server certificates.
7201 <sect2 id="libpq-ssl-protection">
7202 <title>Protection Provided in Different Modes</title>
7205 The different values for the <literal>sslmode</> parameter provide different
7206 levels of protection. SSL can provide
7207 protection against three types of attacks:
7211 <term>Eavesdropping</term>
7213 <para>If a third party can examine the network traffic between the
7214 client and the server, it can read both connection information (including
7215 the user name and password) and the data that is passed. <acronym>SSL</>
7216 uses encryption to prevent this.
7222 <term>Man in the middle (<acronym>MITM</>)</term>
7224 <para>If a third party can modify the data while passing between the
7225 client and server, it can pretend to be the server and therefore see and
7226 modify data <emphasis>even if it is encrypted</>. The third party can then
7227 forward the connection information and data to the original server,
7228 making it impossible to detect this attack. Common vectors to do this
7229 include DNS poisoning and address hijacking, whereby the client is directed
7230 to a different server than intended. There are also several other
7231 attack methods that can accomplish this. <acronym>SSL</> uses certificate
7232 verification to prevent this, by authenticating the server to the client.
7238 <term>Impersonation</term>
7240 <para>If a third party can pretend to be an authorized client, it can
7241 simply access data it should not have access to. Typically this can
7242 happen through insecure password management. <acronym>SSL</> uses
7243 client certificates to prevent this, by making sure that only holders
7244 of valid certificates can access the server.
7252 For a connection to be known secure, SSL usage must be configured
7253 on <emphasis>both the client and the server</> before the connection
7254 is made. If it is only configured on the server, the client may end up
7255 sending sensitive information (e.g. passwords) before
7256 it knows that the server requires high security. In libpq, secure
7257 connections can be ensured
7258 by setting the <literal>sslmode</> parameter to <literal>verify-full</> or
7259 <literal>verify-ca</>, and providing the system with a root certificate to
7260 verify against. This is analogous to using an <literal>https</>
7261 <acronym>URL</> for encrypted web browsing.
7265 Once the server has been authenticated, the client can pass sensitive data.
7266 This means that up until this point, the client does not need to know if
7267 certificates will be used for authentication, making it safe to specify that
7268 only in the server configuration.
7272 All <acronym>SSL</> options carry overhead in the form of encryption and
7273 key-exchange, so there is a tradeoff that has to be made between performance
7274 and security. <xref linkend="libpq-ssl-sslmode-statements">
7275 illustrates the risks the different <literal>sslmode</> values
7276 protect against, and what statement they make about security and overhead.
7279 <table id="libpq-ssl-sslmode-statements">
7280 <title>SSL Mode Descriptions</title>
7284 <entry><literal>sslmode</></entry>
7285 <entry>Eavesdropping protection</entry>
7286 <entry><acronym>MITM</> protection</entry>
7287 <entry>Statement</entry>
7293 <entry><literal>disable</></entry>
7296 <entry>I don't care about security, and I don't want to pay the overhead
7302 <entry><literal>allow</></entry>
7303 <entry>Maybe</entry>
7305 <entry>I don't care about security, but I will pay the overhead of
7306 encryption if the server insists on it.
7311 <entry><literal>prefer</></entry>
7312 <entry>Maybe</entry>
7314 <entry>I don't care about encryption, but I wish to pay the overhead of
7315 encryption if the server supports it.
7320 <entry><literal>require</></entry>
7323 <entry>I want my data to be encrypted, and I accept the overhead. I trust
7324 that the network will make sure I always connect to the server I want.
7329 <entry><literal>verify-ca</></entry>
7331 <entry><literal>Depends on CA</>-policy</entry>
7332 <entry>I want my data encrypted, and I accept the overhead. I want to be
7333 sure that I connect to a server that I trust.
7338 <entry><literal>verify-full</></entry>
7341 <entry>I want my data encrypted, and I accept the overhead. I want to be
7342 sure that I connect to a server I trust, and that it's the one I
7352 The difference between <literal>verify-ca</> and <literal>verify-full</>
7353 depends on the policy of the root <acronym>CA</>. If a public
7354 <acronym>CA</> is used, <literal>verify-ca</> allows connections to a server
7355 that <emphasis>somebody else</> may have registered with the <acronym>CA</>.
7356 In this case, <literal>verify-full</> should always be used. If
7357 a local <acronym>CA</> is used, or even a self-signed certificate, using
7358 <literal>verify-ca</> often provides enough protection.
7362 The default value for <literal>sslmode</> is <literal>prefer</>. As is shown
7363 in the table, this makes no sense from a security point of view, and it only
7364 promises performance overhead if possible. It is only provided as the default
7365 for backward compatibility, and is not recommended in secure deployments.
7370 <sect2 id="libpq-ssl-fileusage">
7371 <title>SSL Client File Usage</title>
7374 <xref linkend="libpq-ssl-file-usage"> summarizes the files that are
7375 relevant to the SSL setup on the client.
7378 <table id="libpq-ssl-file-usage">
7379 <title>Libpq/Client SSL File Usage</title>
7384 <entry>Contents</entry>
7385 <entry>Effect</entry>
7392 <entry><filename>~/.postgresql/postgresql.crt</></entry>
7393 <entry>client certificate</entry>
7394 <entry>requested by server</entry>
7398 <entry><filename>~/.postgresql/postgresql.key</></entry>
7399 <entry>client private key</entry>
7400 <entry>proves client certificate sent by owner; does not indicate
7401 certificate owner is trustworthy</entry>
7405 <entry><filename>~/.postgresql/root.crt</></entry>
7406 <entry>trusted certificate authorities</entry>
7407 <entry>checks that server certificate is signed by a trusted certificate
7412 <entry><filename>~/.postgresql/root.crl</></entry>
7413 <entry>certificates revoked by certificate authorities</entry>
7414 <entry>server certificate must not be on this list</entry>
7422 <sect2 id="libpq-ssl-initialize">
7423 <title>SSL Library Initialization</title>
7426 If your application initializes <literal>libssl</> and/or
7427 <literal>libcrypto</> libraries and <application>libpq</application>
7428 is built with <acronym>SSL</> support, you should call
7429 <function>PQinitOpenSSL</> to tell <application>libpq</application>
7430 that the <literal>libssl</> and/or <literal>libcrypto</> libraries
7431 have been initialized by your application, so that
7432 <application>libpq</application> will not also initialize those libraries.
7433 <!-- If this URL changes replace it with a URL to www.archive.org. -->
7435 url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
7436 for details on the SSL API.
7441 <varlistentry id="libpq-pqinitopenssl">
7443 <function>PQinitOpenSSL</function>
7445 <primary>PQinitOpenSSL</primary>
7451 Allows applications to select which security libraries to initialize.
7453 void PQinitOpenSSL(int do_ssl, int do_crypto);
7458 When <parameter>do_ssl</> is non-zero, <application>libpq</application>
7459 will initialize the <application>OpenSSL</> library before first
7460 opening a database connection. When <parameter>do_crypto</> is
7461 non-zero, the <literal>libcrypto</> library will be initialized. By
7462 default (if <function>PQinitOpenSSL</> is not called), both libraries
7463 are initialized. When SSL support is not compiled in, this function is
7464 present but does nothing.
7468 If your application uses and initializes either <application>OpenSSL</>
7469 or its underlying <literal>libcrypto</> library, you <emphasis>must</>
7470 call this function with zeroes for the appropriate parameter(s)
7471 before first opening a database connection. Also be sure that you
7472 have done that initialization before opening a database connection.
7477 <varlistentry id="libpq-pqinitssl">
7479 <function>PQinitSSL</function>
7481 <primary>PQinitSSL</primary>
7487 Allows applications to select which security libraries to initialize.
7489 void PQinitSSL(int do_ssl);
7494 This function is equivalent to
7495 <literal>PQinitOpenSSL(do_ssl, do_ssl)</>.
7496 It is sufficient for applications that initialize both or neither
7497 of <application>OpenSSL</> and <literal>libcrypto</>.
7501 <function>PQinitSSL</> has been present since
7502 <productname>PostgreSQL</> 8.0, while <function>PQinitOpenSSL</>
7503 was added in <productname>PostgreSQL</> 8.4, so <function>PQinitSSL</>
7504 might be preferable for applications that need to work with older
7505 versions of <application>libpq</application>.
7516 <sect1 id="libpq-threading">
7517 <title>Behavior in Threaded Programs</title>
7519 <indexterm zone="libpq-threading">
7520 <primary>threads</primary>
7521 <secondary>with libpq</secondary>
7525 <application>libpq</application> is reentrant and thread-safe by default.
7526 You might need to use special compiler command-line
7527 options when you compile your application code. Refer to your
7528 system's documentation for information about how to build
7529 thread-enabled applications, or look in
7530 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
7531 and <literal>PTHREAD_LIBS</>. This function allows the querying of
7532 <application>libpq</application>'s thread-safe status:
7536 <varlistentry id="libpq-pqisthreadsafe">
7538 <function>PQisthreadsafe</function>
7540 <primary>PQisthreadsafe</primary>
7546 Returns the thread safety status of the
7547 <application>libpq</application> library.
7549 int PQisthreadsafe();
7554 Returns 1 if the <application>libpq</application> is thread-safe
7562 One thread restriction is that no two threads attempt to manipulate
7563 the same <structname>PGconn</> object at the same time. In particular,
7564 you cannot issue concurrent commands from different threads through
7565 the same connection object. (If you need to run concurrent commands,
7566 use multiple connections.)
7570 <structname>PGresult</> objects are normally read-only after creation,
7571 and so can be passed around freely between threads. However, if you use
7572 any of the <structname>PGresult</>-modifying functions described in
7573 <xref linkend="libpq-misc"> or <xref linkend="libpq-events">, it's up
7574 to you to avoid concurrent operations on the same <structname>PGresult</>,
7579 The deprecated functions <function>PQrequestCancel</function> and
7580 <function>PQoidStatus</function> are not thread-safe and should not be
7581 used in multithread programs. <function>PQrequestCancel</function>
7582 can be replaced by <function>PQcancel</function>.
7583 <function>PQoidStatus</function> can be replaced by
7584 <function>PQoidValue</function>.
7588 If you are using Kerberos inside your application (in addition to inside
7589 <application>libpq</application>), you will need to do locking around
7590 Kerberos calls because Kerberos functions are not thread-safe. See
7591 function <function>PQregisterThreadLock</> in the
7592 <application>libpq</application> source code for a way to do cooperative
7593 locking between <application>libpq</application> and your application.
7597 If you experience problems with threaded applications, run the program
7598 in <filename>src/tools/thread</> to see if your platform has
7599 thread-unsafe functions. This program is run by
7600 <filename>configure</filename>, but for binary distributions your
7601 library might not match the library used to build the binaries.
7606 <sect1 id="libpq-build">
7607 <title>Building <application>libpq</application> Programs</title>
7609 <indexterm zone="libpq-build">
7610 <primary>compiling</primary>
7611 <secondary>libpq applications</secondary>
7615 To build (i.e., compile and link) a program using
7616 <application>libpq</application> you need to do all of the following
7622 Include the <filename>libpq-fe.h</filename> header file:
7624 #include <libpq-fe.h>
7626 If you failed to do that then you will normally get error messages
7627 from your compiler similar to:
7629 foo.c: In function `main':
7630 foo.c:34: `PGconn' undeclared (first use in this function)
7631 foo.c:35: `PGresult' undeclared (first use in this function)
7632 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
7633 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
7634 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
7641 Point your compiler to the directory where the <productname>PostgreSQL</> header
7642 files were installed, by supplying the
7643 <literal>-I<replaceable>directory</replaceable></literal> option
7644 to your compiler. (In some cases the compiler will look into
7645 the directory in question by default, so you can omit this
7646 option.) For instance, your compile command line could look
7649 cc -c -I/usr/local/pgsql/include testprog.c
7651 If you are using makefiles then add the option to the
7652 <varname>CPPFLAGS</varname> variable:
7654 CPPFLAGS += -I/usr/local/pgsql/include
7659 If there is any chance that your program might be compiled by
7660 other users then you should not hardcode the directory location
7661 like that. Instead, you can run the utility
7662 <command>pg_config</command><indexterm><primary>pg_config</><secondary
7663 sortas="libpq">with libpq</></> to find out where the header
7664 files are on the local system:
7666 <prompt>$</prompt> pg_config --includedir
7667 <computeroutput>/usr/local/include</computeroutput>
7673 have <command>pkg-config</command><indexterm><primary>pkg-config</primary><secondary sortas="libpq">with
7674 libpq</secondary></indexterm> installed, you can run instead:
7676 <prompt>$</prompt> pkg-config --cflags libpq
7677 <computeroutput>-I/usr/local/include</computeroutput>
7679 Note that this will already include the <option>-I</option> in front of
7684 Failure to specify the correct option to the compiler will
7685 result in an error message such as:
7687 testlibpq.c:8:22: libpq-fe.h: No such file or directory
7694 When linking the final program, specify the option
7695 <literal>-lpq</literal> so that the <application>libpq</application>
7696 library gets pulled in, as well as the option
7697 <literal>-L<replaceable>directory</replaceable></literal> to point
7698 the compiler to the directory where the
7699 <application>libpq</application> library resides. (Again, the
7700 compiler will search some directories by default.) For maximum
7701 portability, put the <option>-L</option> option before the
7702 <option>-lpq</option> option. For example:
7704 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
7709 You can find out the library directory using
7710 <command>pg_config</command> as well:
7712 <prompt>$</prompt> pg_config --libdir
7713 <computeroutput>/usr/local/pgsql/lib</computeroutput>
7718 Or again use <command>pkg-config</command>:
7720 <prompt>$</prompt> pkg-config --libs libpq
7721 <computeroutput>-L/usr/local/pgsql/lib -lpq</computeroutput>
7723 Note again that this prints the full options, not only the path.
7727 Error messages that point to problems in this area could look like
7730 testlibpq.o: In function `main':
7731 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
7732 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
7733 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
7735 This means you forgot <option>-lpq</option>.
7737 /usr/bin/ld: cannot find -lpq
7739 This means you forgot the <option>-L</option> option or did not
7740 specify the right directory.
7749 <sect1 id="libpq-example">
7750 <title>Example Programs</title>
7753 These examples and others can be found in the
7754 directory <filename>src/test/examples</filename> in the source code
7758 <example id="libpq-example-1">
7759 <title><application>libpq</application> Example Program 1</title>
7766 * Test the C version of libpq, the PostgreSQL frontend library.
7770 #include <libpq-fe.h>
7773 exit_nicely(PGconn *conn)
7780 main(int argc, char **argv)
7782 const char *conninfo;
7790 * If the user supplies a parameter on the command line, use it as the
7791 * conninfo string; otherwise default to setting dbname=postgres and using
7792 * environment variables or defaults for all other connection parameters.
7797 conninfo = "dbname = postgres";
7799 /* Make a connection to the database */
7800 conn = PQconnectdb(conninfo);
7802 /* Check to see that the backend connection was successfully made */
7803 if (PQstatus(conn) != CONNECTION_OK)
7805 fprintf(stderr, "Connection to database failed: %s",
7806 PQerrorMessage(conn));
7811 * Our test case here involves using a cursor, for which we must be inside
7812 * a transaction block. We could do the whole thing with a single
7813 * PQexec() of "select * from pg_database", but that's too trivial to make
7817 /* Start a transaction block */
7818 res = PQexec(conn, "BEGIN");
7819 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7821 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
7827 * Should PQclear PGresult whenever it is no longer needed to avoid memory
7833 * Fetch rows from pg_database, the system catalog of databases
7835 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
7836 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7838 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
7844 res = PQexec(conn, "FETCH ALL in myportal");
7845 if (PQresultStatus(res) != PGRES_TUPLES_OK)
7847 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
7852 /* first, print out the attribute names */
7853 nFields = PQnfields(res);
7854 for (i = 0; i < nFields; i++)
7855 printf("%-15s", PQfname(res, i));
7858 /* next, print out the rows */
7859 for (i = 0; i < PQntuples(res); i++)
7861 for (j = 0; j < nFields; j++)
7862 printf("%-15s", PQgetvalue(res, i, j));
7868 /* close the portal ... we don't bother to check for errors ... */
7869 res = PQexec(conn, "CLOSE myportal");
7872 /* end the transaction */
7873 res = PQexec(conn, "END");
7876 /* close the connection to the database and cleanup */
7885 <example id="libpq-example-2">
7886 <title><application>libpq</application> Example Program 2</title>
7892 * Test of the asynchronous notification interface
7894 * Start this program, then from psql in another window do
7896 * Repeat four times to get this program to exit.
7898 * Or, if you want to get fancy, try this:
7899 * populate a database with the following commands
7900 * (provided in src/test/examples/testlibpq2.sql):
7902 * CREATE TABLE TBL1 (i int4);
7904 * CREATE TABLE TBL2 (i int4);
7906 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
7907 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
7909 * and do this four times:
7911 * INSERT INTO TBL1 VALUES (10);
7915 #include <windows.h>
7921 #include <sys/time.h>
7922 #include <sys/types.h>
7923 #include "libpq-fe.h"
7926 exit_nicely(PGconn *conn)
7933 main(int argc, char **argv)
7935 const char *conninfo;
7942 * If the user supplies a parameter on the command line, use it as the
7943 * conninfo string; otherwise default to setting dbname=postgres and using
7944 * environment variables or defaults for all other connection parameters.
7949 conninfo = "dbname = postgres";
7951 /* Make a connection to the database */
7952 conn = PQconnectdb(conninfo);
7954 /* Check to see that the backend connection was successfully made */
7955 if (PQstatus(conn) != CONNECTION_OK)
7957 fprintf(stderr, "Connection to database failed: %s",
7958 PQerrorMessage(conn));
7963 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
7965 res = PQexec(conn, "LISTEN TBL2");
7966 if (PQresultStatus(res) != PGRES_COMMAND_OK)
7968 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
7974 * should PQclear PGresult whenever it is no longer needed to avoid memory
7979 /* Quit after four notifies are received. */
7981 while (nnotifies < 4)
7984 * Sleep until something happens on the connection. We use select(2)
7985 * to wait for input, but you could also use poll() or similar
7991 sock = PQsocket(conn);
7994 break; /* shouldn't happen */
7996 FD_ZERO(&input_mask);
7997 FD_SET(sock, &input_mask);
7999 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
8001 fprintf(stderr, "select() failed: %s\n", strerror(errno));
8005 /* Now check for input */
8006 PQconsumeInput(conn);
8007 while ((notify = PQnotifies(conn)) != NULL)
8010 "ASYNC NOTIFY of '%s' received from backend PID %d\n",
8011 notify->relname, notify->be_pid);
8017 fprintf(stderr, "Done.\n");
8019 /* close the connection to the database and cleanup */
8028 <example id="libpq-example-3">
8029 <title><application>libpq</application> Example Program 3</title>
8035 * Test out-of-line parameters and binary I/O.
8037 * Before running this, populate a database with the following commands
8038 * (provided in src/test/examples/testlibpq3.sql):
8040 * CREATE TABLE test1 (i int4, t text, b bytea);
8042 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
8043 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
8045 * The expected output is:
8049 * t = (11 bytes) 'joe's place'
8050 * b = (5 bytes) \000\001\002\003\004
8054 * t = (8 bytes) 'ho there'
8055 * b = (5 bytes) \004\003\002\001\000
8059 #include <windows.h>
8066 #include <sys/types.h>
8067 #include "libpq-fe.h"
8069 /* for ntohl/htonl */
8070 #include <netinet/in.h>
8071 #include <arpa/inet.h>
8075 exit_nicely(PGconn *conn)
8082 * This function prints a query result that is a binary-format fetch from
8083 * a table defined as in the comment above. We split it out because the
8084 * main() function uses it twice.
8087 show_binary_results(PGresult *res)
8095 /* Use PQfnumber to avoid assumptions about field order in result */
8096 i_fnum = PQfnumber(res, "i");
8097 t_fnum = PQfnumber(res, "t");
8098 b_fnum = PQfnumber(res, "b");
8100 for (i = 0; i < PQntuples(res); i++)
8108 /* Get the field values (we ignore possibility they are null!) */
8109 iptr = PQgetvalue(res, i, i_fnum);
8110 tptr = PQgetvalue(res, i, t_fnum);
8111 bptr = PQgetvalue(res, i, b_fnum);
8114 * The binary representation of INT4 is in network byte order, which
8115 * we'd better coerce to the local byte order.
8117 ival = ntohl(*((uint32_t *) iptr));
8120 * The binary representation of TEXT is, well, text, and since libpq
8121 * was nice enough to append a zero byte to it, it'll work just fine
8124 * The binary representation of BYTEA is a bunch of bytes, which could
8125 * include embedded nulls so we have to pay attention to field length.
8127 blen = PQgetlength(res, i, b_fnum);
8129 printf("tuple %d: got\n", i);
8130 printf(" i = (%d bytes) %d\n",
8131 PQgetlength(res, i, i_fnum), ival);
8132 printf(" t = (%d bytes) '%s'\n",
8133 PQgetlength(res, i, t_fnum), tptr);
8134 printf(" b = (%d bytes) ", blen);
8135 for (j = 0; j < blen; j++)
8136 printf("\\%03o", bptr[j]);
8142 main(int argc, char **argv)
8144 const char *conninfo;
8147 const char *paramValues[1];
8148 int paramLengths[1];
8149 int paramFormats[1];
8150 uint32_t binaryIntVal;
8153 * If the user supplies a parameter on the command line, use it as the
8154 * conninfo string; otherwise default to setting dbname=postgres and using
8155 * environment variables or defaults for all other connection parameters.
8160 conninfo = "dbname = postgres";
8162 /* Make a connection to the database */
8163 conn = PQconnectdb(conninfo);
8165 /* Check to see that the backend connection was successfully made */
8166 if (PQstatus(conn) != CONNECTION_OK)
8168 fprintf(stderr, "Connection to database failed: %s",
8169 PQerrorMessage(conn));
8174 * The point of this program is to illustrate use of PQexecParams() with
8175 * out-of-line parameters, as well as binary transmission of data.
8177 * This first example transmits the parameters as text, but receives the
8178 * results in binary format. By using out-of-line parameters we can avoid
8179 * a lot of tedious mucking about with quoting and escaping, even though
8180 * the data is text. Notice how we don't have to do anything special with
8181 * the quote mark in the parameter value.
8184 /* Here is our out-of-line parameter value */
8185 paramValues[0] = "joe's place";
8187 res = PQexecParams(conn,
8188 "SELECT * FROM test1 WHERE t = $1",
8190 NULL, /* let the backend deduce param type */
8192 NULL, /* don't need param lengths since text */
8193 NULL, /* default to all text params */
8194 1); /* ask for binary results */
8196 if (PQresultStatus(res) != PGRES_TUPLES_OK)
8198 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
8203 show_binary_results(res);
8208 * In this second example we transmit an integer parameter in binary form,
8209 * and again retrieve the results in binary form.
8211 * Although we tell PQexecParams we are letting the backend deduce
8212 * parameter type, we really force the decision by casting the parameter
8213 * symbol in the query text. This is a good safety measure when sending
8214 * binary parameters.
8217 /* Convert integer value "2" to network byte order */
8218 binaryIntVal = htonl((uint32_t) 2);
8220 /* Set up parameter arrays for PQexecParams */
8221 paramValues[0] = (char *) &binaryIntVal;
8222 paramLengths[0] = sizeof(binaryIntVal);
8223 paramFormats[0] = 1; /* binary */
8225 res = PQexecParams(conn,
8226 "SELECT * FROM test1 WHERE i = $1::int4",
8228 NULL, /* let the backend deduce param type */
8232 1); /* ask for binary results */
8234 if (PQresultStatus(res) != PGRES_TUPLES_OK)
8236 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
8241 show_binary_results(res);
8245 /* close the connection to the database and cleanup */