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</productname>.
17 <application>libpq</application> is a set of library functions that allow
18 client programs to pass queries to the <productname>PostgreSQL</productname>
19 backend server and to receive the results of these queries.
23 <application>libpq</application> is also the underlying engine for several
24 other <productname>PostgreSQL</productname> application interfaces, including
25 those written for C++, Perl, Python, Tcl and <application>ECPG</application>.
26 So some aspects of <application>libpq</application>'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</application>.
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</primary></indexterm>
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</structname><indexterm><primary>PGconn</primary></indexterm> object, which
59 is obtained from the function <xref linkend="libpq-PQconnectdb"/>,
60 <xref linkend="libpq-PQconnectdbParams"/>, or
61 <xref linkend="libpq-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</structname> object.
64 The <xref linkend="libpq-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 If untrusted users have access to a database that has not adopted a
71 <link linkend="ddl-schemas-patterns">secure schema usage pattern</link>,
72 begin each session by removing publicly-writable schemas from
73 <varname>search_path</varname>. One can set parameter key
74 word <literal>options</literal> to
75 value <literal>-csearch_path=</literal>. Alternately, one can
76 issue <literal>PQexec(<replaceable>conn</replaceable>, "SELECT
77 pg_catalog.set_config('search_path', '', false)")</literal> after
78 connecting. This consideration is not specific
79 to <application>libpq</application>; it applies to every interface for
80 executing arbitrary SQL commands.
86 On Unix, forking a process with open libpq connections can lead to
87 unpredictable results because the parent and child processes share
88 the same sockets and operating system resources. For this reason,
89 such usage is not recommended, though doing an <function>exec</function> from
90 the child process to load a new executable is safe.
96 On Windows, there is a way to improve performance if a single
97 database connection is repeatedly started and shutdown. Internally,
98 libpq calls <function>WSAStartup()</function> and <function>WSACleanup()</function> for connection startup
99 and shutdown, respectively. <function>WSAStartup()</function> increments an internal
100 Windows library reference count which is decremented by <function>WSACleanup()</function>.
101 When the reference count is just one, calling <function>WSACleanup()</function> frees
102 all resources and all DLLs are unloaded. This is an expensive
103 operation. To avoid this, an application can manually call
104 <function>WSAStartup()</function> so resources will not be freed when the last database
105 connection is closed.
110 <varlistentry id="libpq-PQconnectdbParams">
111 <term><function>PQconnectdbParams</function><indexterm><primary>PQconnectdbParams</primary></indexterm></term>
114 Makes a new connection to the database server.
117 PGconn *PQconnectdbParams(const char * const *keywords,
118 const char * const *values,
124 This function opens a new database connection using the parameters taken
125 from two <symbol>NULL</symbol>-terminated arrays. The first,
126 <literal>keywords</literal>, is defined as an array of strings, each one
127 being a key word. The second, <literal>values</literal>, gives the value
128 for each key word. Unlike <xref linkend="libpq-PQsetdbLogin"/> below, the parameter
129 set can be extended without changing the function signature, so use of
130 this function (or its nonblocking analogs <xref linkend="libpq-PQconnectStartParams"/>
131 and <function>PQconnectPoll</function>) is preferred for new application
136 The currently recognized parameter key words are listed in
137 <xref linkend="libpq-paramkeywords"/>.
141 When <literal>expand_dbname</literal> is non-zero, the
142 <parameter>dbname</parameter> key word value is allowed to be recognized
143 as a connection string. Only the first occurrence of
144 <parameter>dbname</parameter> is expanded this way, any subsequent
145 <parameter>dbname</parameter> value is processed as plain database name. More
146 details on the possible connection string formats appear in
147 <xref linkend="libpq-connstring"/>.
151 The passed arrays can be empty to use all default parameters, or can
152 contain one or more parameter settings. They should be matched in length.
153 Processing will stop at the first <symbol>NULL</symbol> element
154 in the <literal>keywords</literal> array.
158 If any parameter is <symbol>NULL</symbol> or an empty string, the corresponding
159 environment variable (see <xref linkend="libpq-envars"/>) is checked.
160 If the environment variable is not set either, then the indicated
161 built-in defaults are used.
165 In general key words are processed from the beginning of these arrays in index
166 order. The effect of this is that when key words are repeated, the last processed
167 value is retained. Therefore, through careful placement of the
168 <parameter>dbname</parameter> key word, it is possible to determine what may
169 be overridden by a <parameter>conninfo</parameter> string, and what may not.
175 <varlistentry id="libpq-PQconnectdb">
176 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</primary></indexterm></term>
179 Makes a new connection to the database server.
182 PGconn *PQconnectdb(const char *conninfo);
187 This function opens a new database connection using the parameters taken
188 from the string <literal>conninfo</literal>.
192 The passed string can be empty to use all default parameters, or it can
193 contain one or more parameter settings separated by whitespace,
194 or it can contain a <acronym>URI</acronym>.
195 See <xref linkend="libpq-connstring"/> for details.
202 <varlistentry id="libpq-PQsetdbLogin">
203 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</primary></indexterm></term>
206 Makes a new connection to the database server.
208 PGconn *PQsetdbLogin(const char *pghost,
210 const char *pgoptions,
219 This is the predecessor of <xref linkend="libpq-PQconnectdb"/> with a fixed
220 set of parameters. It has the same functionality except that the
221 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
222 empty string for any one of the fixed parameters that is to be defaulted.
226 If the <parameter>dbName</parameter> contains
227 an <symbol>=</symbol> sign or has a valid connection <acronym>URI</acronym> prefix, it
228 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
229 if it had been passed to <xref linkend="libpq-PQconnectdb"/>, and the remaining
230 parameters are then applied as specified for <xref linkend="libpq-PQconnectdbParams"/>.
235 <varlistentry id="libpq-PQsetdb">
236 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</primary></indexterm></term>
239 Makes a new connection to the database server.
241 PGconn *PQsetdb(char *pghost,
250 This is a macro that calls <xref linkend="libpq-PQsetdbLogin"/> with null pointers
251 for the <parameter>login</parameter> and <parameter>pwd</parameter> parameters. It is provided
252 for backward compatibility with very old programs.
257 <varlistentry id="libpq-PQconnectStartParams">
258 <term><function>PQconnectStartParams</function><indexterm><primary>PQconnectStartParams</primary></indexterm></term>
259 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</primary></indexterm></term>
260 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</primary></indexterm></term>
263 <indexterm><primary>nonblocking connection</primary></indexterm>
264 Make a connection to the database server in a nonblocking manner.
267 PGconn *PQconnectStartParams(const char * const *keywords,
268 const char * const *values,
271 PGconn *PQconnectStart(const char *conninfo);
273 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
278 These three functions are used to open a connection to a database server such
279 that your application's thread of execution is not blocked on remote I/O
280 whilst doing so. The point of this approach is that the waits for I/O to
281 complete can occur in the application's main loop, rather than down inside
282 <xref linkend="libpq-PQconnectdbParams"/> or <xref linkend="libpq-PQconnectdb"/>, and so the
283 application can manage this operation in parallel with other activities.
287 With <xref linkend="libpq-PQconnectStartParams"/>, the database connection is made
288 using the parameters taken from the <literal>keywords</literal> and
289 <literal>values</literal> arrays, and controlled by <literal>expand_dbname</literal>,
290 as described above for <xref linkend="libpq-PQconnectdbParams"/>.
294 With <function>PQconnectStart</function>, the database connection is made
295 using the parameters taken from the string <literal>conninfo</literal> as
296 described above for <xref linkend="libpq-PQconnectdb"/>.
300 Neither <xref linkend="libpq-PQconnectStartParams"/> nor <function>PQconnectStart</function>
301 nor <function>PQconnectPoll</function> will block, so long as a number of
302 restrictions are met:
306 The <literal>hostaddr</literal> parameter must be used appropriately
307 to prevent DNS queries from being made. See the documentation of
308 this parameter in <xref linkend="libpq-paramkeywords"/> for details.
314 If you call <xref linkend="libpq-PQtrace"/>, ensure that the stream object
315 into which you trace will not block.
321 You must ensure that the socket is in the appropriate state
322 before calling <function>PQconnectPoll</function>, as described below.
329 To begin a nonblocking connection request,
330 call <function>PQconnectStart</function>
331 or <xref linkend="libpq-PQconnectStartParams"/>. If the result is null,
332 then <application>libpq</application> has been unable to allocate a
333 new <structname>PGconn</structname> structure. Otherwise, a
334 valid <structname>PGconn</structname> pointer is returned (though not
335 yet representing a valid connection to the database). Next
336 call <literal>PQstatus(conn)</literal>. If the result
337 is <symbol>CONNECTION_BAD</symbol>, the connection attempt has already
338 failed, typically because of invalid connection parameters.
342 If <function>PQconnectStart</function>
343 or <xref linkend="libpq-PQconnectStartParams"/> succeeds, the next stage
344 is to poll <application>libpq</application> so that it can proceed with
345 the connection sequence.
346 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
347 socket underlying the database connection.
348 (Caution: do not assume that the socket remains the same
349 across <function>PQconnectPoll</function> calls.)
350 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
351 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
352 read (as indicated by <function>select()</function>, <function>poll()</function>, or
353 similar system function).
354 Then call <function>PQconnectPoll(conn)</function> again.
355 Conversely, if <function>PQconnectPoll(conn)</function> last returned
356 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
357 to write, then call <function>PQconnectPoll(conn)</function> again.
358 On the first iteration, i.e. if you have yet to call
359 <function>PQconnectPoll</function>, behave as if it last returned
360 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
361 <function>PQconnectPoll(conn)</function> returns
362 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
363 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
364 has been successfully made.
368 At any time during connection, the status of the connection can be
369 checked by calling <xref linkend="libpq-PQstatus"/>. If this call returns <symbol>CONNECTION_BAD</symbol>, then the
370 connection procedure has failed; if the call returns <function>CONNECTION_OK</function>, then the
371 connection is ready. Both of these states are equally detectable
372 from the return value of <function>PQconnectPoll</function>, described above. Other states might also occur
373 during (and only during) an asynchronous connection procedure. These
374 indicate the current stage of the connection procedure and might be useful
375 to provide feedback to the user for example. These statuses are:
378 <varlistentry id="libpq-connection-started">
379 <term><symbol>CONNECTION_STARTED</symbol></term>
382 Waiting for connection to be made.
387 <varlistentry id="libpq-connection-made">
388 <term><symbol>CONNECTION_MADE</symbol></term>
391 Connection OK; waiting to send.
396 <varlistentry id="libpq-connection-awaiting-response">
397 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
400 Waiting for a response from the server.
405 <varlistentry id="libpq-connection-auth-ok">
406 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
409 Received authentication; waiting for backend start-up to finish.
414 <varlistentry id="libpq-connection-ssl-startup">
415 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
418 Negotiating SSL encryption.
423 <varlistentry id="libpq-connection-setenv">
424 <term><symbol>CONNECTION_SETENV</symbol></term>
427 Negotiating environment-driven parameter settings.
432 <varlistentry id="libpq-connection-check-writable">
433 <term><symbol>CONNECTION_CHECK_WRITABLE</symbol></term>
436 Checking if connection is able to handle write transactions.
441 <varlistentry id="libpq-connection-consume">
442 <term><symbol>CONNECTION_CONSUME</symbol></term>
445 Consuming any remaining response messages on connection.
451 Note that, although these constants will remain (in order to maintain
452 compatibility), an application should never rely upon these occurring in a
453 particular order, or at all, or on the status always being one of these
454 documented values. An application might do something like this:
456 switch(PQstatus(conn))
458 case CONNECTION_STARTED:
459 feedback = "Connecting...";
462 case CONNECTION_MADE:
463 feedback = "Connected to server...";
469 feedback = "Connecting...";
475 The <literal>connect_timeout</literal> connection parameter is ignored
476 when using <function>PQconnectPoll</function>; it is the application's
477 responsibility to decide whether an excessive amount of time has elapsed.
478 Otherwise, <function>PQconnectStart</function> followed by a
479 <function>PQconnectPoll</function> loop is equivalent to
480 <xref linkend="libpq-PQconnectdb"/>.
484 Note that when <function>PQconnectStart</function>
485 or <xref linkend="libpq-PQconnectStartParams"/> returns a non-null
486 pointer, you must call <xref linkend="libpq-PQfinish"/> when you are
487 finished with it, in order to dispose of the structure and any
488 associated memory blocks. This must be done even if the connection
489 attempt fails or is abandoned.
494 <varlistentry id="libpq-PQconndefaults">
495 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</primary></indexterm></term>
498 Returns the default connection options.
500 PQconninfoOption *PQconndefaults(void);
504 char *keyword; /* The keyword of the option */
505 char *envvar; /* Fallback environment variable name */
506 char *compiled; /* Fallback compiled in default value */
507 char *val; /* Option's current value, or NULL */
508 char *label; /* Label for field in connect dialog */
509 char *dispchar; /* Indicates how to display this field
510 in a connect dialog. Values are:
511 "" Display entered value as is
512 "*" Password field - hide value
513 "D" Debug option - don't show by default */
514 int dispsize; /* Field size in characters for dialog */
520 Returns a connection options array. This can be used to determine
521 all possible <xref linkend="libpq-PQconnectdb"/> options and their
522 current default values. The return value points to an array of
523 <structname>PQconninfoOption</structname> structures, which ends
524 with an entry having a null <structfield>keyword</structfield> pointer. The
525 null pointer is returned if memory could not be allocated. Note that
526 the current default values (<structfield>val</structfield> fields)
527 will depend on environment variables and other context. A
528 missing or invalid service file will be silently ignored. Callers
529 must treat the connection options data as read-only.
533 After processing the options array, free it by passing it to
534 <xref linkend="libpq-PQconninfoFree"/>. If this is not done, a small amount of memory
535 is leaked for each call to <xref linkend="libpq-PQconndefaults"/>.
541 <varlistentry id="libpq-PQconninfo">
542 <term><function>PQconninfo</function><indexterm><primary>PQconninfo</primary></indexterm></term>
545 Returns the connection options used by a live connection.
547 PQconninfoOption *PQconninfo(PGconn *conn);
552 Returns a connection options array. This can be used to determine
553 all possible <xref linkend="libpq-PQconnectdb"/> options and the
554 values that were used to connect to the server. The return
555 value points to an array of <structname>PQconninfoOption</structname>
556 structures, which ends with an entry having a null <structfield>keyword</structfield>
557 pointer. All notes above for <xref linkend="libpq-PQconndefaults"/> also
558 apply to the result of <xref linkend="libpq-PQconninfo"/>.
565 <varlistentry id="libpq-PQconninfoParse">
566 <term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</primary></indexterm></term>
569 Returns parsed connection options from the provided connection string.
572 PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
577 Parses a connection string and returns the resulting options as an
578 array; or returns <symbol>NULL</symbol> if there is a problem with the connection
579 string. This function can be used to extract
580 the <xref linkend="libpq-PQconnectdb"/> options in the provided
581 connection string. The return value points to an array of
582 <structname>PQconninfoOption</structname> structures, which ends
583 with an entry having a null <structfield>keyword</structfield> pointer.
587 All legal options will be present in the result array, but the
588 <literal>PQconninfoOption</literal> for any option not present
589 in the connection string will have <literal>val</literal> set to
590 <literal>NULL</literal>; default values are not inserted.
594 If <literal>errmsg</literal> is not <symbol>NULL</symbol>, then <literal>*errmsg</literal> is set
595 to <symbol>NULL</symbol> on success, else to a <function>malloc</function>'d error string explaining
596 the problem. (It is also possible for <literal>*errmsg</literal> to be
597 set to <symbol>NULL</symbol> and the function to return <symbol>NULL</symbol>;
598 this indicates an out-of-memory condition.)
602 After processing the options array, free it by passing it to
603 <xref linkend="libpq-PQconninfoFree"/>. If this is not done, some memory
604 is leaked for each call to <xref linkend="libpq-PQconninfoParse"/>.
605 Conversely, if an error occurs and <literal>errmsg</literal> is not <symbol>NULL</symbol>,
606 be sure to free the error string using <xref linkend="libpq-PQfreemem"/>.
612 <varlistentry id="libpq-PQfinish">
613 <term><function>PQfinish</function><indexterm><primary>PQfinish</primary></indexterm></term>
616 Closes the connection to the server. Also frees
617 memory used by the <structname>PGconn</structname> object.
619 void PQfinish(PGconn *conn);
624 Note that even if the server connection attempt fails (as
625 indicated by <xref linkend="libpq-PQstatus"/>), the application should call <xref linkend="libpq-PQfinish"/>
626 to free the memory used by the <structname>PGconn</structname> object.
627 The <structname>PGconn</structname> pointer must not be used again after
628 <xref linkend="libpq-PQfinish"/> has been called.
633 <varlistentry id="libpq-PQreset">
634 <term><function>PQreset</function><indexterm><primary>PQreset</primary></indexterm></term>
637 Resets the communication channel to the server.
639 void PQreset(PGconn *conn);
644 This function will close the connection
645 to the server and attempt to reestablish a new
646 connection to the same server, using all the same
647 parameters previously used. This might be useful for
648 error recovery if a working connection is lost.
653 <varlistentry id="libpq-PQresetStart">
654 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</primary></indexterm></term>
655 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</primary></indexterm></term>
658 Reset the communication channel to the server, in a nonblocking manner.
661 int PQresetStart(PGconn *conn);
663 PostgresPollingStatusType PQresetPoll(PGconn *conn);
668 These functions will close the connection to the server and attempt to
669 reestablish a new connection to the same server, using all the same
670 parameters previously used. This can be useful for error recovery if a
671 working connection is lost. They differ from <xref linkend="libpq-PQreset"/> (above) in that they
672 act in a nonblocking manner. These functions suffer from the same
673 restrictions as <xref linkend="libpq-PQconnectStartParams"/>, <function>PQconnectStart</function>
674 and <function>PQconnectPoll</function>.
678 To initiate a connection reset, call
679 <xref linkend="libpq-PQresetStart"/>. If it returns 0, the reset has
680 failed. If it returns 1, poll the reset using
681 <function>PQresetPoll</function> in exactly the same way as you
682 would create the connection using <function>PQconnectPoll</function>.
687 <varlistentry id="libpq-PQpingParams">
688 <term><function>PQpingParams</function><indexterm><primary>PQpingParams</primary></indexterm></term>
691 <xref linkend="libpq-PQpingParams"/> reports the status of the
692 server. It accepts connection parameters identical to those of
693 <xref linkend="libpq-PQconnectdbParams"/>, described above. It is not
694 necessary to supply correct user name, password, or database name
695 values to obtain the server status; however, if incorrect values
696 are provided, the server will log a failed connection attempt.
699 PGPing PQpingParams(const char * const *keywords,
700 const char * const *values,
704 The function returns one of the following values:
707 <varlistentry id="libpq-PQpingParams-PQPING_OK">
708 <term><literal>PQPING_OK</literal></term>
711 The server is running and appears to be accepting connections.
716 <varlistentry id="libpq-PQpingParams-PQPING_REJECT">
717 <term><literal>PQPING_REJECT</literal></term>
720 The server is running but is in a state that disallows connections
721 (startup, shutdown, or crash recovery).
726 <varlistentry id="libpq-PQpingParams-PQPING_NO_RESPONSE">
727 <term><literal>PQPING_NO_RESPONSE</literal></term>
730 The server could not be contacted. This might indicate that the
731 server is not running, or that there is something wrong with the
732 given connection parameters (for example, wrong port number), or
733 that there is a network connectivity problem (for example, a
734 firewall blocking the connection request).
739 <varlistentry id="libpq-PQpingParams-PQPING_NO_ATTEMPT">
740 <term><literal>PQPING_NO_ATTEMPT</literal></term>
743 No attempt was made to contact the server, because the supplied
744 parameters were obviously incorrect or there was some client-side
745 problem (for example, out of memory).
756 <varlistentry id="libpq-PQping">
757 <term><function>PQping</function><indexterm><primary>PQping</primary></indexterm></term>
760 <xref linkend="libpq-PQping"/> reports the status of the
761 server. It accepts connection parameters identical to those of
762 <xref linkend="libpq-PQconnectdb"/>, described above. It is not
763 necessary to supply correct user name, password, or database name
764 values to obtain the server status; however, if incorrect values
765 are provided, the server will log a failed connection attempt.
768 PGPing PQping(const char *conninfo);
773 The return values are the same as for <xref linkend="libpq-PQpingParams"/>.
782 <sect2 id="libpq-connstring">
783 <title>Connection Strings</title>
785 <indexterm zone="libpq-connstring">
786 <primary><literal>conninfo</literal></primary>
789 <indexterm zone="libpq-connstring">
790 <primary><literal>URI</literal></primary>
794 Several <application>libpq</application> functions parse a user-specified string to obtain
795 connection parameters. There are two accepted formats for these strings:
796 plain <literal>keyword = value</literal> strings
797 and URIs. URIs generally follow
798 <ulink url="https://tools.ietf.org/html/rfc3986">RFC
799 3986</ulink>, except that multi-host connection strings are allowed
800 as further described below.
804 <title>Keyword/Value Connection Strings</title>
807 In the first format, each parameter setting is in the form
808 <literal>keyword = value</literal>. Spaces around the equal sign are
809 optional. To write an empty value, or a value containing spaces, surround it
810 with single quotes, e.g., <literal>keyword = 'a value'</literal>. Single
811 quotes and backslashes within
812 the value must be escaped with a backslash, i.e., <literal>\'</literal> and
813 <literal>\\</literal>.
819 host=localhost port=5432 dbname=mydb connect_timeout=10
824 The recognized parameter key words are listed in <xref
825 linkend="libpq-paramkeywords"/>.
830 <title>Connection URIs</title>
833 The general form for a connection <acronym>URI</acronym> is:
835 postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]
840 The <acronym>URI</acronym> scheme designator can be either
841 <literal>postgresql://</literal> or <literal>postgres://</literal>. Each
842 of the <acronym>URI</acronym> parts is optional. The following examples
843 illustrate valid <acronym>URI</acronym> syntax uses:
846 postgresql://localhost
847 postgresql://localhost:5433
848 postgresql://localhost/mydb
849 postgresql://user@localhost
850 postgresql://user:secret@localhost
851 postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
852 postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp
854 Components of the hierarchical part of the <acronym>URI</acronym> can also
855 be given as parameters. For example:
857 postgresql:///mydb?host=localhost&port=5433
862 Percent-encoding may be used to include symbols with special meaning in any
863 of the <acronym>URI</acronym> parts, e.g. replace <literal>=</literal> with
864 <literal>%3D</literal>.
869 Any connection parameters not corresponding to key words listed in <xref
870 linkend="libpq-paramkeywords"/> are ignored and a warning message about them
871 is sent to <filename>stderr</filename>.
875 For improved compatibility with JDBC connection <acronym>URI</acronym>s,
876 instances of parameter <literal>ssl=true</literal> are translated into
877 <literal>sslmode=require</literal>.
881 The host part may be either host name or an IP address. To specify an
882 IPv6 host address, enclose it in square brackets:
884 postgresql://[2001:db8::1234]/database
889 The host component is interpreted as described for the parameter <xref
890 linkend="libpq-connect-host"/>. In particular, a Unix-domain socket
891 connection is chosen if the host part is either empty or starts with a
892 slash, otherwise a TCP/IP connection is initiated. Note, however, that the
893 slash is a reserved character in the hierarchical part of the URI. So, to
894 specify a non-standard Unix-domain socket directory, either omit the host
895 specification in the URI and specify the host as a parameter, or
896 percent-encode the path in the host component of the URI:
898 postgresql:///dbname?host=/var/lib/postgresql
899 postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
904 It is possible to specify multiple host components, each with an optional
905 port component, in a single URI. A URI of the form
906 <literal>postgresql://host1:port1,host2:port2,host3:port3/</literal>
907 is equivalent to a connection string of the form
908 <literal>host=host1,host2,host3 port=port1,port2,port3</literal>. Each
909 host will be tried in turn until a connection is successfully established.
913 <sect3 id="libpq-multiple-hosts">
914 <title>Specifying Multiple Hosts</title>
917 It is possible to specify multiple hosts to connect to, so that they are
918 tried in the given order. In the Keyword/Value format, the <literal>host</literal>,
919 <literal>hostaddr</literal>, and <literal>port</literal> options accept a comma-separated
920 list of values. The same number of elements must be given in each
921 option that is specified, such
922 that e.g. the first <literal>hostaddr</literal> corresponds to the first host name,
923 the second <literal>hostaddr</literal> corresponds to the second host name, and so
924 forth. As an exception, if only one <literal>port</literal> is specified, it
925 applies to all the hosts.
929 In the connection URI format, you can list multiple <literal>host:port</literal> pairs
930 separated by commas, in the <literal>host</literal> component of the URI.
934 In either format, a single host name can translate to multiple network
935 addresses. A common example of this is a host that has both an IPv4 and
940 When multiple hosts are specified, or when a single host name is
941 translated to multiple addresses, all the hosts and addresses will be
942 tried in order, until one succeeds. If none of the hosts can be reached,
943 the connection fails. If a connection is established successfully, but
944 authentication fails, the remaining hosts in the list are not tried.
948 If a password file is used, you can have different passwords for
949 different hosts. All the other connection options are the same for every
950 host in the list; it is not possible to e.g. specify different
951 usernames for different hosts.
956 <sect2 id="libpq-paramkeywords">
957 <title>Parameter Key Words</title>
960 The currently recognized parameter key words are:
963 <varlistentry id="libpq-connect-host" xreflabel="host">
964 <term><literal>host</literal></term>
967 Name of host to connect to.<indexterm><primary>host name</primary></indexterm>
968 If a host name begins with a slash, it specifies Unix-domain
969 communication rather than TCP/IP communication; the value is the
970 name of the directory in which the socket file is stored.
971 The default behavior when <literal>host</literal> is
972 not specified, or is empty, is to connect to a Unix-domain
973 socket<indexterm><primary>Unix domain socket</primary></indexterm> in
974 <filename>/tmp</filename> (or whatever socket directory was specified
975 when <productname>PostgreSQL</productname> was built). On machines without
976 Unix-domain sockets, the default is to connect to <literal>localhost</literal>.
979 A comma-separated list of host names is also accepted, in which case
980 each host name in the list is tried in order; an empty item in the
981 list selects the default behavior as explained above. See
982 <xref linkend="libpq-multiple-hosts"/> for details.
987 <varlistentry id="libpq-connect-hostaddr" xreflabel="hostaddr">
988 <term><literal>hostaddr</literal></term>
991 Numeric IP address of host to connect to. This should be in the
992 standard IPv4 address format, e.g., <literal>172.28.40.9</literal>. If
993 your machine supports IPv6, you can also use those addresses.
994 TCP/IP communication is
995 always used when a nonempty string is specified for this parameter.
996 If this parameter is not specified, the value of <literal>host</literal>
997 will be looked up to find the corresponding IP address — or, if
998 <literal>host</literal> specifies an IP address, that value will be
1003 Using <literal>hostaddr</literal> allows the
1004 application to avoid a host name look-up, which might be important
1005 in applications with time constraints. However, a host name is
1006 required for GSSAPI or SSPI authentication
1007 methods, as well as for <literal>verify-full</literal> SSL
1008 certificate verification. The following rules are used:
1012 If <literal>host</literal> is specified
1013 without <literal>hostaddr</literal>, a host name lookup occurs.
1014 (When using <function>PQconnectPoll</function>, the lookup occurs
1015 when <function>PQconnectPoll</function> first considers this host
1016 name, and it may cause <function>PQconnectPoll</function> to block
1017 for a significant amount of time.)
1022 If <literal>hostaddr</literal> is specified without <literal>host</literal>,
1023 the value for <literal>hostaddr</literal> gives the server network address.
1024 The connection attempt will fail if the authentication
1025 method requires a host name.
1030 If both <literal>host</literal> and <literal>hostaddr</literal> are specified,
1031 the value for <literal>hostaddr</literal> gives the server network address.
1032 The value for <literal>host</literal> is ignored unless the
1033 authentication method requires it, in which case it will be
1034 used as the host name.
1038 Note that authentication is likely to fail if <literal>host</literal>
1039 is not the name of the server at network address <literal>hostaddr</literal>.
1040 Also, when both <literal>host</literal> and <literal>hostaddr</literal>
1041 are specified, <literal>host</literal>
1042 is used to identify the connection in a password file (see
1043 <xref linkend="libpq-pgpass"/>).
1047 A comma-separated list of <literal>hostaddr</literal> values is also
1048 accepted, in which case each host in the list is tried in order.
1049 An empty item in the list causes the corresponding host name to be
1050 used, or the default host name if that is empty as well. See
1051 <xref linkend="libpq-multiple-hosts"/> for details.
1054 Without either a host name or host address,
1055 <application>libpq</application> will connect using a
1056 local Unix-domain socket; or on machines without Unix-domain
1057 sockets, it will attempt to connect to <literal>localhost</literal>.
1062 <varlistentry id="libpq-connect-port" xreflabel="port">
1063 <term><literal>port</literal></term>
1066 Port number to connect to at the server host, or socket file
1067 name extension for Unix-domain
1068 connections.<indexterm><primary>port</primary></indexterm>
1069 If multiple hosts were given in the <literal>host</literal> or
1070 <literal>hostaddr</literal> parameters, this parameter may specify a
1071 comma-separated list of ports of the same length as the host list, or
1072 it may specify a single port number to be used for all hosts.
1073 An empty string, or an empty item in a comma-separated list,
1074 specifies the default port number established
1075 when <productname>PostgreSQL</productname> was built.
1080 <varlistentry id="libpq-connect-dbname" xreflabel="dbname">
1081 <term><literal>dbname</literal></term>
1084 The database name. Defaults to be the same as the user name.
1085 In certain contexts, the value is checked for extended
1086 formats; see <xref linkend="libpq-connstring"/> for more details on
1092 <varlistentry id="libpq-connect-user" xreflabel="user">
1093 <term><literal>user</literal></term>
1096 <productname>PostgreSQL</productname> user name to connect as.
1097 Defaults to be the same as the operating system name of the user
1098 running the application.
1103 <varlistentry id="libpq-connect-password" xreflabel="password">
1104 <term><literal>password</literal></term>
1107 Password to be used if the server demands password authentication.
1112 <varlistentry id="libpq-connect-passfile" xreflabel="passfile">
1113 <term><literal>passfile</literal></term>
1116 Specifies the name of the file used to store passwords
1117 (see <xref linkend="libpq-pgpass"/>).
1118 Defaults to <filename>~/.pgpass</filename>, or
1119 <filename>%APPDATA%\postgresql\pgpass.conf</filename> on Microsoft Windows.
1120 (No error is reported if this file does not exist.)
1125 <varlistentry id="libpq-connect-connect-timeout" xreflabel="connect_timeout">
1126 <term><literal>connect_timeout</literal></term>
1129 Maximum wait for connection, in seconds (write as a decimal integer,
1130 e.g. <literal>10</literal>). Zero, negative, or not specified means
1131 wait indefinitely. The minimum allowed timeout is 2 seconds, therefore
1132 a value of <literal>1</literal> is interpreted as <literal>2</literal>.
1133 This timeout applies separately to each host name or IP address.
1134 For example, if you specify two hosts and <literal>connect_timeout</literal>
1135 is 5, each host will time out if no connection is made within 5
1136 seconds, so the total time spent waiting for a connection might be
1142 <varlistentry id="libpq-connect-client-encoding" xreflabel="client_encoding">
1143 <term><literal>client_encoding</literal></term>
1146 This sets the <varname>client_encoding</varname>
1147 configuration parameter for this connection. In addition to
1148 the values accepted by the corresponding server option, you
1149 can use <literal>auto</literal> to determine the right
1150 encoding from the current locale in the client
1151 (<envar>LC_CTYPE</envar> environment variable on Unix
1157 <varlistentry id="libpq-connect-options" xreflabel="options">
1158 <term><literal>options</literal></term>
1161 Specifies command-line options to send to the server at connection
1162 start. For example, setting this to <literal>-c geqo=off</literal> sets the
1163 session's value of the <varname>geqo</varname> parameter to
1164 <literal>off</literal>. Spaces within this string are considered to
1165 separate command-line arguments, unless escaped with a backslash
1166 (<literal>\</literal>); write <literal>\\</literal> to represent a literal
1167 backslash. For a detailed discussion of the available
1168 options, consult <xref linkend="runtime-config"/>.
1173 <varlistentry id="libpq-connect-application-name" xreflabel="application_name">
1174 <term><literal>application_name</literal></term>
1177 Specifies a value for the <xref linkend="guc-application-name"/>
1178 configuration parameter.
1183 <varlistentry id="libpq-connect-fallback-application-name" xreflabel="fallback_application_name">
1184 <term><literal>fallback_application_name</literal></term>
1187 Specifies a fallback value for the <xref
1188 linkend="guc-application-name"/> configuration parameter.
1189 This value will be used if no value has been given for
1190 <literal>application_name</literal> via a connection parameter or the
1191 <envar>PGAPPNAME</envar> environment variable. Specifying
1192 a fallback name is useful in generic utility programs that
1193 wish to set a default application name but allow it to be
1194 overridden by the user.
1199 <varlistentry id="libpq-keepalives" xreflabel="keepalives">
1200 <term><literal>keepalives</literal></term>
1203 Controls whether client-side TCP keepalives are used. The default
1204 value is 1, meaning on, but you can change this to 0, meaning off,
1205 if keepalives are not wanted. This parameter is ignored for
1206 connections made via a Unix-domain socket.
1211 <varlistentry id="libpq-keepalives-idle" xreflabel="keepalives_idle">
1212 <term><literal>keepalives_idle</literal></term>
1215 Controls the number of seconds of inactivity after which TCP should
1216 send a keepalive message to the server. A value of zero uses the
1217 system default. This parameter is ignored for connections made via a
1218 Unix-domain socket, or if keepalives are disabled.
1219 It is only supported on systems where <symbol>TCP_KEEPIDLE</symbol> or
1220 an equivalent socket option is available, and on Windows; on other
1221 systems, it has no effect.
1226 <varlistentry id="libpq-keepalives-interval" xreflabel="keepalives_interval">
1227 <term><literal>keepalives_interval</literal></term>
1230 Controls the number of seconds after which a TCP keepalive message
1231 that is not acknowledged by the server should be retransmitted. A
1232 value of zero uses the system default. This parameter is ignored for
1233 connections made via a Unix-domain socket, or if keepalives are disabled.
1234 It is only supported on systems where <symbol>TCP_KEEPINTVL</symbol> or
1235 an equivalent socket option is available, and on Windows; on other
1236 systems, it has no effect.
1241 <varlistentry id="libpq-keepalives-count" xreflabel="keepalives_count">
1242 <term><literal>keepalives_count</literal></term>
1245 Controls the number of TCP keepalives that can be lost before the
1246 client's connection to the server is considered dead. A value of
1247 zero uses the system default. This parameter is ignored for
1248 connections made via a Unix-domain socket, or if keepalives are disabled.
1249 It is only supported on systems where <symbol>TCP_KEEPCNT</symbol> or
1250 an equivalent socket option is available; on other systems, it has no
1256 <varlistentry id="libpq-tcp-user-timeout" xreflabel="tcp_user_timeout">
1257 <term><literal>tcp_user_timeout</literal></term>
1260 Controls the number of milliseconds that transmitted data may
1261 remain unacknowledged before a connection is forcibly closed.
1262 A value of zero uses the system default. This parameter is
1263 ignored for connections made via a Unix-domain socket.
1264 It is only supported on systems where <symbol>TCP_USER_TIMEOUT</symbol>
1265 is available; on other systems, it has no effect.
1270 <varlistentry id="libpq-connect-tty" xreflabel="tty">
1271 <term><literal>tty</literal></term>
1274 Ignored (formerly, this specified where to send server debug output).
1279 <varlistentry id="libpq-connect-replication" xreflabel="replication">
1280 <term><literal>replication</literal></term>
1283 This option determines whether the connection should use the
1284 replication protocol instead of the normal protocol. This is what
1285 PostgreSQL replication connections as well as tools such as
1286 <application>pg_basebackup</application> use internally, but it can
1287 also be used by third-party applications. For a description of the
1288 replication protocol, consult <xref linkend="protocol-replication"/>.
1292 The following values, which are case-insensitive, are supported:
1296 <literal>true</literal>, <literal>on</literal>,
1297 <literal>yes</literal>, <literal>1</literal>
1301 The connection goes into physical replication mode.
1307 <term><literal>database</literal></term>
1310 The connection goes into logical replication mode, connecting to
1311 the database specified in the <literal>dbname</literal> parameter.
1318 <literal>false</literal>, <literal>off</literal>,
1319 <literal>no</literal>, <literal>0</literal>
1323 The connection is a regular one, which is the default behavior.
1331 In physical or logical replication mode, only the simple query protocol
1337 <varlistentry id="libpq-connect-gssencmode" xreflabel="gssencmode">
1338 <term><literal>gssencmode</literal></term>
1341 This option determines whether or with what priority a secure
1342 <acronym>GSS</acronym> TCP/IP connection will be negotiated with the
1343 server. There are three modes:
1347 <term><literal>disable</literal></term>
1350 only try a non-<acronym>GSSAPI</acronym>-encrypted connection
1356 <term><literal>prefer</literal> (default)</term>
1359 if there are <acronym>GSSAPI</acronym> credentials present (i.e.,
1360 in a credentials cache), first try
1361 a <acronym>GSSAPI</acronym>-encrypted connection; if that fails or
1362 there are no credentials, try a
1363 non-<acronym>GSSAPI</acronym>-encrypted connection. This is the
1364 default when <productname>PostgreSQL</productname> has been
1365 compiled with <acronym>GSSAPI</acronym> support.
1371 <term><literal>require</literal></term>
1374 only try a <acronym>GSSAPI</acronym>-encrypted connection
1382 <literal>gssencmode</literal> is ignored for Unix domain socket
1383 communication. If <productname>PostgreSQL</productname> is compiled
1384 without GSSAPI support, using the <literal>require</literal> option
1385 will cause an error, while <literal>prefer</literal> will be accepted
1386 but <application>libpq</application> will not actually attempt
1387 a <acronym>GSSAPI</acronym>-encrypted
1388 connection.<indexterm><primary>GSSAPI</primary><secondary sortas="libpq">with
1389 libpq</secondary></indexterm>
1394 <varlistentry id="libpq-connect-sslmode" xreflabel="sslmode">
1395 <term><literal>sslmode</literal></term>
1398 This option determines whether or with what priority a secure
1399 <acronym>SSL</acronym> TCP/IP connection will be negotiated with the
1400 server. There are six modes:
1404 <term><literal>disable</literal></term>
1407 only try a non-<acronym>SSL</acronym> connection
1413 <term><literal>allow</literal></term>
1416 first try a non-<acronym>SSL</acronym> connection; if that
1417 fails, try an <acronym>SSL</acronym> connection
1423 <term><literal>prefer</literal> (default)</term>
1426 first try an <acronym>SSL</acronym> connection; if that fails,
1427 try a non-<acronym>SSL</acronym> connection
1433 <term><literal>require</literal></term>
1436 only try an <acronym>SSL</acronym> connection. If a root CA
1437 file is present, verify the certificate in the same way as
1438 if <literal>verify-ca</literal> was specified
1444 <term><literal>verify-ca</literal></term>
1447 only try an <acronym>SSL</acronym> connection, and verify that
1448 the server certificate is issued by a trusted
1449 certificate authority (<acronym>CA</acronym>)
1455 <term><literal>verify-full</literal></term>
1458 only try an <acronym>SSL</acronym> connection, verify that the
1459 server certificate is issued by a
1460 trusted <acronym>CA</acronym> and that the requested server host name
1461 matches that in the certificate
1467 See <xref linkend="libpq-ssl"/> for a detailed description of how
1472 <literal>sslmode</literal> is ignored for Unix domain socket
1474 If <productname>PostgreSQL</productname> is compiled without SSL support,
1475 using options <literal>require</literal>, <literal>verify-ca</literal>, or
1476 <literal>verify-full</literal> will cause an error, while
1477 options <literal>allow</literal> and <literal>prefer</literal> will be
1478 accepted but <application>libpq</application> will not actually attempt
1479 an <acronym>SSL</acronym>
1480 connection.<indexterm><primary>SSL</primary><secondary
1481 sortas="libpq">with libpq</secondary></indexterm>
1486 <varlistentry id="libpq-connect-requiressl" xreflabel="requiressl">
1487 <term><literal>requiressl</literal></term>
1490 This option is deprecated in favor of the <literal>sslmode</literal>
1495 If set to 1, an <acronym>SSL</acronym> connection to the server
1496 is required (this is equivalent to <literal>sslmode</literal>
1497 <literal>require</literal>). <application>libpq</application> will then refuse
1498 to connect if the server does not accept an
1499 <acronym>SSL</acronym> connection. If set to 0 (default),
1500 <application>libpq</application> will negotiate the connection type with
1501 the server (equivalent to <literal>sslmode</literal>
1502 <literal>prefer</literal>). This option is only available if
1503 <productname>PostgreSQL</productname> is compiled with SSL support.
1508 <varlistentry id="libpq-connect-sslcompression" xreflabel="sslcompression">
1509 <term><literal>sslcompression</literal></term>
1512 If set to 1, data sent over SSL connections will be compressed. If
1513 set to 0, compression will be disabled. The default is 0. This
1514 parameter is ignored if a connection without SSL is made.
1518 SSL compression is nowadays considered insecure and its use is no
1519 longer recommended. <productname>OpenSSL</productname> 1.1.0 disables
1520 compression by default, and many operating system distributions
1521 disable it in prior versions as well, so setting this parameter to on
1522 will not have any effect if the server does not accept compression.
1523 On the other hand, <productname>OpenSSL</productname> before 1.0.0
1524 does not support disabling compression, so this parameter is ignored
1525 with those versions, and whether compression is used depends on the
1530 If security is not a primary concern, compression can improve
1531 throughput if the network is the bottleneck. Disabling compression
1532 can improve response time and throughput if CPU performance is the
1538 <varlistentry id="libpq-connect-sslcert" xreflabel="sslcert">
1539 <term><literal>sslcert</literal></term>
1542 This parameter specifies the file name of the client SSL
1543 certificate, replacing the default
1544 <filename>~/.postgresql/postgresql.crt</filename>.
1545 This parameter is ignored if an SSL connection is not made.
1550 <varlistentry id="libpq-connect-sslkey" xreflabel="sslkey">
1551 <term><literal>sslkey</literal></term>
1554 This parameter specifies the location for the secret key used for
1555 the client certificate. It can either specify a file name that will
1556 be used instead of the default
1557 <filename>~/.postgresql/postgresql.key</filename>, or it can specify a key
1558 obtained from an external <quote>engine</quote> (engines are
1559 <productname>OpenSSL</productname> loadable modules). An external engine
1560 specification should consist of a colon-separated engine name and
1561 an engine-specific key identifier. This parameter is ignored if an
1562 SSL connection is not made.
1567 <varlistentry id="libpq-connect-sslrootcert" xreflabel="sslrootcert">
1568 <term><literal>sslrootcert</literal></term>
1571 This parameter specifies the name of a file containing SSL
1572 certificate authority (<acronym>CA</acronym>) certificate(s).
1573 If the file exists, the server's certificate will be verified
1574 to be signed by one of these authorities. The default is
1575 <filename>~/.postgresql/root.crt</filename>.
1580 <varlistentry id="libpq-connect-sslcrl" xreflabel="sslcrl">
1581 <term><literal>sslcrl</literal></term>
1584 This parameter specifies the file name of the SSL certificate
1585 revocation list (CRL). Certificates listed in this file, if it
1586 exists, will be rejected while attempting to authenticate the
1587 server's certificate. The default is
1588 <filename>~/.postgresql/root.crl</filename>.
1593 <varlistentry id="libpq-connect-requirepeer" xreflabel="requirepeer">
1594 <term><literal>requirepeer</literal></term>
1597 This parameter specifies the operating-system user name of the
1598 server, for example <literal>requirepeer=postgres</literal>.
1599 When making a Unix-domain socket connection, if this
1600 parameter is set, the client checks at the beginning of the
1601 connection that the server process is running under the specified
1602 user name; if it is not, the connection is aborted with an error.
1603 This parameter can be used to provide server authentication similar
1604 to that available with SSL certificates on TCP/IP connections.
1605 (Note that if the Unix-domain socket is in
1606 <filename>/tmp</filename> or another publicly writable location,
1607 any user could start a server listening there. Use this parameter
1608 to ensure that you are connected to a server run by a trusted user.)
1609 This option is only supported on platforms for which the
1610 <literal>peer</literal> authentication method is implemented; see
1611 <xref linkend="auth-peer"/>.
1616 <varlistentry id="libpq-connect-krbsrvname" xreflabel="krbsrvname">
1617 <term><literal>krbsrvname</literal></term>
1620 Kerberos service name to use when authenticating with GSSAPI.
1621 This must match the service name specified in the server
1622 configuration for Kerberos authentication to succeed. (See also
1623 <xref linkend="gssapi-auth"/>.)
1628 <varlistentry id="libpq-connect-gsslib" xreflabel="gsslib">
1629 <term><literal>gsslib</literal></term>
1632 GSS library to use for GSSAPI authentication. Only used on Windows.
1633 Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
1634 library for authentication instead of the default SSPI.
1639 <varlistentry id="libpq-connect-service" xreflabel="service">
1640 <term><literal>service</literal></term>
1643 Service name to use for additional parameters. It specifies a service
1644 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
1645 This allows applications to specify only a service name so connection parameters
1646 can be centrally maintained. See <xref linkend="libpq-pgservice"/>.
1651 <varlistentry id="libpq-connect-target-session-attrs" xreflabel="target_session_attrs">
1652 <term><literal>target_session_attrs</literal></term>
1655 If this parameter is set to <literal>read-write</literal>, only a
1656 connection in which read-write transactions are accepted by default
1657 is considered acceptable. The query
1658 <literal>SHOW transaction_read_only</literal> will be sent upon any
1659 successful connection; if it returns <literal>on</literal>, the connection
1660 will be closed. If multiple hosts were specified in the connection
1661 string, any remaining servers will be tried just as if the connection
1662 attempt had failed. The default value of this parameter,
1663 <literal>any</literal>, regards all connections as acceptable.
1672 <sect1 id="libpq-status">
1673 <title>Connection Status Functions</title>
1676 These functions can be used to interrogate the status
1677 of an existing database connection object.
1682 <indexterm><primary>libpq-fe.h</primary></indexterm>
1683 <indexterm><primary>libpq-int.h</primary></indexterm>
1684 <application>libpq</application> application programmers should be careful to
1685 maintain the <structname>PGconn</structname> abstraction. Use the accessor
1686 functions described below to get at the contents of <structname>PGconn</structname>.
1687 Reference to internal <structname>PGconn</structname> fields using
1688 <filename>libpq-int.h</filename> is not recommended because they are subject to change
1694 The following functions return parameter values established at connection.
1695 These values are fixed for the life of the connection. If a multi-host
1696 connection string is used, the values of <xref linkend="libpq-PQhost"/>,
1697 <xref linkend="libpq-PQport"/>, and <xref linkend="libpq-PQpass"/> can change if a new connection
1698 is established using the same <structname>PGconn</structname> object. Other values
1699 are fixed for the lifetime of the <structname>PGconn</structname> object.
1702 <varlistentry id="libpq-PQdb">
1703 <term><function>PQdb</function><indexterm><primary>PQdb</primary></indexterm></term>
1707 Returns the database name of the connection.
1709 char *PQdb(const PGconn *conn);
1715 <varlistentry id="libpq-PQuser">
1716 <term><function>PQuser</function><indexterm><primary>PQuser</primary></indexterm></term>
1720 Returns the user name of the connection.
1722 char *PQuser(const PGconn *conn);
1728 <varlistentry id="libpq-PQpass">
1729 <term><function>PQpass</function><indexterm><primary>PQpass</primary></indexterm></term>
1733 Returns the password of the connection.
1735 char *PQpass(const PGconn *conn);
1740 <xref linkend="libpq-PQpass"/> will return either the password specified
1741 in the connection parameters, or if there was none and the password
1742 was obtained from the <link linkend="libpq-pgpass">password
1743 file</link>, it will return that. In the latter case,
1744 if multiple hosts were specified in the connection parameters, it is
1745 not possible to rely on the result of <xref linkend="libpq-PQpass"/> until
1746 the connection is established. The status of the connection can be
1747 checked using the function <xref linkend="libpq-PQstatus"/>.
1752 <varlistentry id="libpq-PQhost">
1753 <term><function>PQhost</function><indexterm><primary>PQhost</primary></indexterm></term>
1757 Returns the server host name of the active connection.
1758 This can be a host name, an IP address, or a directory path if the
1759 connection is via Unix socket. (The path case can be distinguished
1760 because it will always be an absolute path, beginning
1761 with <literal>/</literal>.)
1763 char *PQhost(const PGconn *conn);
1768 If the connection parameters specified both <literal>host</literal> and
1769 <literal>hostaddr</literal>, then <xref linkend="libpq-PQhost"/> will
1770 return the <literal>host</literal> information. If only
1771 <literal>hostaddr</literal> was specified, then that is returned.
1772 If multiple hosts were specified in the connection parameters,
1773 <xref linkend="libpq-PQhost"/> returns the host actually connected to.
1777 <xref linkend="libpq-PQhost"/> returns <symbol>NULL</symbol> if the
1778 <parameter>conn</parameter> argument is <symbol>NULL</symbol>.
1779 Otherwise, if there is an error producing the host information (perhaps
1780 if the connection has not been fully established or there was an
1781 error), it returns an empty string.
1785 If multiple hosts were specified in the connection parameters, it is
1786 not possible to rely on the result of <xref linkend="libpq-PQhost"/> until
1787 the connection is established. The status of the connection can be
1788 checked using the function <xref linkend="libpq-PQstatus"/>.
1794 <varlistentry id="libpq-PQhostaddr">
1795 <term><function>PQhostaddr</function><indexterm><primary>PQhostaddr</primary></indexterm></term>
1799 Returns the server IP address of the active connection.
1800 This can be the address that a host name resolved to,
1801 or an IP address provided through the <literal>hostaddr</literal>
1804 char *PQhostaddr(const PGconn *conn);
1809 <xref linkend="libpq-PQhostaddr"/> returns <symbol>NULL</symbol> if the
1810 <parameter>conn</parameter> argument is <symbol>NULL</symbol>.
1811 Otherwise, if there is an error producing the host information
1812 (perhaps if the connection has not been fully established or
1813 there was an error), it returns an empty string.
1818 <varlistentry id="libpq-PQport">
1819 <term><function>PQport</function><indexterm><primary>PQport</primary></indexterm></term>
1823 Returns the port of the active connection.
1826 char *PQport(const PGconn *conn);
1831 If multiple ports were specified in the connection parameters,
1832 <xref linkend="libpq-PQport"/> returns the port actually connected to.
1836 <xref linkend="libpq-PQport"/> returns <symbol>NULL</symbol> if the
1837 <parameter>conn</parameter> argument is <symbol>NULL</symbol>.
1838 Otherwise, if there is an error producing the port information (perhaps
1839 if the connection has not been fully established or there was an
1840 error), it returns an empty string.
1844 If multiple ports were specified in the connection parameters, it is
1845 not possible to rely on the result of <xref linkend="libpq-PQport"/> until
1846 the connection is established. The status of the connection can be
1847 checked using the function <xref linkend="libpq-PQstatus"/>.
1852 <varlistentry id="libpq-PQtty">
1853 <term><function>PQtty</function><indexterm><primary>PQtty</primary></indexterm></term>
1857 Returns the debug <acronym>TTY</acronym> of the connection.
1858 (This is obsolete, since the server no longer pays attention
1859 to the <acronym>TTY</acronym> setting, but the function remains
1860 for backward compatibility.)
1863 char *PQtty(const PGconn *conn);
1869 <varlistentry id="libpq-PQoptions">
1870 <term><function>PQoptions</function><indexterm><primary>PQoptions</primary></indexterm></term>
1874 Returns the command-line options passed in the connection request.
1876 char *PQoptions(const PGconn *conn);
1885 The following functions return status data that can change as operations
1886 are executed on the <structname>PGconn</structname> object.
1889 <varlistentry id="libpq-PQstatus">
1890 <term><function>PQstatus</function><indexterm><primary>PQstatus</primary></indexterm></term>
1894 Returns the status of the connection.
1896 ConnStatusType PQstatus(const PGconn *conn);
1901 The status can be one of a number of values. However, only two of
1902 these are seen outside of an asynchronous connection procedure:
1903 <literal>CONNECTION_OK</literal> and
1904 <literal>CONNECTION_BAD</literal>. A good connection to the database
1905 has the status <literal>CONNECTION_OK</literal>. A failed
1906 connection attempt is signaled by status
1907 <literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
1908 remain so until <xref linkend="libpq-PQfinish"/>, but a communications
1909 failure might result in the status changing to
1910 <literal>CONNECTION_BAD</literal> prematurely. In that case the
1911 application could try to recover by calling
1912 <xref linkend="libpq-PQreset"/>.
1916 See the entry for <xref linkend="libpq-PQconnectStartParams"/>, <function>PQconnectStart</function>
1917 and <function>PQconnectPoll</function> with regards to other status codes that
1923 <varlistentry id="libpq-PQtransactionStatus">
1924 <term><function>PQtransactionStatus</function><indexterm><primary>PQtransactionStatus</primary></indexterm></term>
1928 Returns the current in-transaction status of the server.
1931 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
1934 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
1935 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
1936 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
1937 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
1938 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
1939 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
1940 has been sent to the server and not yet completed.
1945 <varlistentry id="libpq-PQparameterStatus">
1946 <term><function>PQparameterStatus</function><indexterm><primary>PQparameterStatus</primary></indexterm></term>
1950 Looks up a current parameter setting of the server.
1953 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
1956 Certain parameter values are reported by the server automatically at
1957 connection startup or whenever their values change.
1958 <xref linkend="libpq-PQparameterStatus"/> can be used to interrogate these settings.
1959 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
1960 if the parameter is not known.
1964 Parameters reported as of the current release include
1965 <varname>server_version</varname>,
1966 <varname>server_encoding</varname>,
1967 <varname>client_encoding</varname>,
1968 <varname>application_name</varname>,
1969 <varname>is_superuser</varname>,
1970 <varname>session_authorization</varname>,
1971 <varname>DateStyle</varname>,
1972 <varname>IntervalStyle</varname>,
1973 <varname>TimeZone</varname>,
1974 <varname>integer_datetimes</varname>, and
1975 <varname>standard_conforming_strings</varname>.
1976 (<varname>server_encoding</varname>, <varname>TimeZone</varname>, and
1977 <varname>integer_datetimes</varname> were not reported by releases before 8.0;
1978 <varname>standard_conforming_strings</varname> was not reported by releases
1980 <varname>IntervalStyle</varname> was not reported by releases before 8.4;
1981 <varname>application_name</varname> was not reported by releases before 9.0.)
1983 <varname>server_version</varname>,
1984 <varname>server_encoding</varname> and
1985 <varname>integer_datetimes</varname>
1986 cannot change after startup.
1990 Pre-3.0-protocol servers do not report parameter settings, but
1991 <application>libpq</application> includes logic to obtain values for
1992 <varname>server_version</varname> and <varname>client_encoding</varname> anyway.
1993 Applications are encouraged to use <xref linkend="libpq-PQparameterStatus"/>
1994 rather than <foreignphrase>ad hoc</foreignphrase> code to determine these values.
1995 (Beware however that on a pre-3.0 connection, changing
1996 <varname>client_encoding</varname> via <command>SET</command> after connection
1997 startup will not be reflected by <xref linkend="libpq-PQparameterStatus"/>.)
1998 For <varname>server_version</varname>, see also
1999 <xref linkend="libpq-PQserverVersion"/>, which returns the information in a
2000 numeric form that is much easier to compare against.
2004 If no value for <varname>standard_conforming_strings</varname> is reported,
2005 applications can assume it is <literal>off</literal>, that is, backslashes
2006 are treated as escapes in string literals. Also, the presence of
2007 this parameter can be taken as an indication that the escape string
2008 syntax (<literal>E'...'</literal>) is accepted.
2012 Although the returned pointer is declared <literal>const</literal>, it in fact
2013 points to mutable storage associated with the <literal>PGconn</literal> structure.
2014 It is unwise to assume the pointer will remain valid across queries.
2019 <varlistentry id="libpq-PQprotocolVersion">
2020 <term><function>PQprotocolVersion</function><indexterm><primary>PQprotocolVersion</primary></indexterm></term>
2024 Interrogates the frontend/backend protocol being used.
2026 int PQprotocolVersion(const PGconn *conn);
2028 Applications might wish to use this function to determine whether certain
2029 features are supported. Currently, the possible values are 2 (2.0
2030 protocol), 3 (3.0 protocol), or zero (connection bad). The
2031 protocol version will
2032 not change after connection startup is complete, but it could
2033 theoretically change during a connection reset. The 3.0 protocol
2034 will normally be used when communicating with
2035 <productname>PostgreSQL</productname> 7.4 or later servers; pre-7.4 servers
2036 support only protocol 2.0. (Protocol 1.0 is obsolete and not
2037 supported by <application>libpq</application>.)
2042 <varlistentry id="libpq-PQserverVersion">
2043 <term><function>PQserverVersion</function><indexterm><primary>PQserverVersion</primary></indexterm></term>
2047 Returns an integer representing the server version.
2049 int PQserverVersion(const PGconn *conn);
2054 Applications might use this function to determine the version of the
2055 database server they are connected to. The result is formed by
2056 multiplying the server's major version number by 10000 and adding
2057 the minor version number. For example, version 10.1 will be
2058 returned as 100001, and version 11.0 will be returned as 110000.
2059 Zero is returned if the connection is bad.
2063 Prior to major version 10, <productname>PostgreSQL</productname> used
2064 three-part version numbers in which the first two parts together
2065 represented the major version. For those
2066 versions, <xref linkend="libpq-PQserverVersion"/> uses two digits for each
2067 part; for example version 9.1.5 will be returned as 90105, and
2068 version 9.2.0 will be returned as 90200.
2072 Therefore, for purposes of determining feature compatibility,
2073 applications should divide the result of <xref linkend="libpq-PQserverVersion"/>
2074 by 100 not 10000 to determine a logical major version number.
2075 In all release series, only the last two digits differ between
2076 minor releases (bug-fix releases).
2081 <varlistentry id="libpq-PQerrorMessage">
2082 <term><function>PQerrorMessage</function><indexterm><primary>PQerrorMessage</primary></indexterm></term>
2086 <indexterm><primary>error message</primary></indexterm> Returns the error message
2087 most recently generated by an operation on the connection.
2090 char *PQerrorMessage(const PGconn *conn);
2096 Nearly all <application>libpq</application> functions will set a message for
2097 <xref linkend="libpq-PQerrorMessage"/> if they fail. Note that by
2098 <application>libpq</application> convention, a nonempty
2099 <xref linkend="libpq-PQerrorMessage"/> result can consist of multiple lines,
2100 and will include a trailing newline. The caller should not free
2101 the result directly. It will be freed when the associated
2102 <structname>PGconn</structname> handle is passed to
2103 <xref linkend="libpq-PQfinish"/>. The result string should not be
2104 expected to remain the same across operations on the
2105 <literal>PGconn</literal> structure.
2110 <varlistentry id="libpq-PQsocket">
2111 <term><function>PQsocket</function><indexterm><primary>PQsocket</primary></indexterm></term>
2114 Obtains the file descriptor number of the connection socket to
2115 the server. A valid descriptor will be greater than or equal
2116 to 0; a result of -1 indicates that no server connection is
2117 currently open. (This will not change during normal operation,
2118 but could change during connection setup or reset.)
2121 int PQsocket(const PGconn *conn);
2128 <varlistentry id="libpq-PQbackendPID">
2129 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</primary></indexterm></term>
2132 Returns the process <acronym>ID</acronym> (PID)<indexterm>
2133 <primary>PID</primary>
2134 <secondary>determining PID of server process</secondary>
2135 <tertiary>in libpq</tertiary>
2137 of the backend process handling this connection.
2140 int PQbackendPID(const PGconn *conn);
2145 The backend <acronym>PID</acronym> is useful for debugging
2146 purposes and for comparison to <command>NOTIFY</command>
2147 messages (which include the <acronym>PID</acronym> of the
2148 notifying backend process). Note that the
2149 <acronym>PID</acronym> belongs to a process executing on the
2150 database server host, not the local host!
2155 <varlistentry id="libpq-PQconnectionNeedsPassword">
2156 <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</primary></indexterm></term>
2159 Returns true (1) if the connection authentication method
2160 required a password, but none was available.
2161 Returns false (0) if not.
2164 int PQconnectionNeedsPassword(const PGconn *conn);
2169 This function can be applied after a failed connection attempt
2170 to decide whether to prompt the user for a password.
2175 <varlistentry id="libpq-PQconnectionUsedPassword">
2176 <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</primary></indexterm></term>
2179 Returns true (1) if the connection authentication method
2180 used a password. Returns false (0) if not.
2183 int PQconnectionUsedPassword(const PGconn *conn);
2188 This function can be applied after either a failed or successful
2189 connection attempt to detect whether the server demanded a password.
2197 The following functions return information related to SSL. This information
2198 usually doesn't change after a connection is established.
2201 <varlistentry id="libpq-PQsslInUse">
2202 <term><function>PQsslInUse</function><indexterm><primary>PQsslInUse</primary></indexterm></term>
2205 Returns true (1) if the connection uses SSL, false (0) if not.
2208 int PQsslInUse(const PGconn *conn);
2215 <varlistentry id="libpq-PQsslAttribute">
2216 <term><function>PQsslAttribute</function><indexterm><primary>PQsslAttribute</primary></indexterm></term>
2219 Returns SSL-related information about the connection.
2222 const char *PQsslAttribute(const PGconn *conn, const char *attribute_name);
2227 The list of available attributes varies depending on the SSL library
2228 being used, and the type of connection. If an attribute is not
2229 available, returns NULL.
2233 The following attributes are commonly available:
2236 <term><literal>library</literal></term>
2239 Name of the SSL implementation in use. (Currently, only
2240 <literal>"OpenSSL"</literal> is implemented)
2245 <term><literal>protocol</literal></term>
2248 SSL/TLS version in use. Common values
2249 are <literal>"TLSv1"</literal>, <literal>"TLSv1.1"</literal>
2250 and <literal>"TLSv1.2"</literal>, but an implementation may
2251 return other strings if some other protocol is used.
2256 <term><literal>key_bits</literal></term>
2259 Number of key bits used by the encryption algorithm.
2264 <term><literal>cipher</literal></term>
2267 A short name of the ciphersuite used, e.g.
2268 <literal>"DHE-RSA-DES-CBC3-SHA"</literal>. The names are specific
2269 to each SSL implementation.
2274 <term><literal>compression</literal></term>
2277 If SSL compression is in use, returns the name of the compression
2278 algorithm, or "on" if compression is used but the algorithm is
2279 not known. If compression is not in use, returns "off".
2288 <varlistentry id="libpq-PQsslAttributeNames">
2289 <term><function>PQsslAttributeNames</function><indexterm><primary>PQsslAttributeNames</primary></indexterm></term>
2292 Return an array of SSL attribute names available. The array is terminated by a NULL pointer.
2294 const char * const * PQsslAttributeNames(const PGconn *conn);
2300 <varlistentry id="libpq-PQsslStruct">
2301 <term><function>PQsslStruct</function><indexterm><primary>PQsslStruct</primary></indexterm></term>
2304 Return a pointer to an SSL-implementation-specific object describing
2307 void *PQsslStruct(const PGconn *conn, const char *struct_name);
2311 The struct(s) available depend on the SSL implementation in use.
2312 For OpenSSL, there is one struct, available under the name "OpenSSL",
2313 and it returns a pointer to the OpenSSL <literal>SSL</literal> struct.
2314 To use this function, code along the following lines could be used:
2315 <programlisting><![CDATA[
2316 #include <libpq-fe.h>
2317 #include <openssl/ssl.h>
2323 dbconn = PQconnectdb(...);
2326 ssl = PQsslStruct(dbconn, "OpenSSL");
2329 /* use OpenSSL functions to access ssl */
2331 ]]></programlisting>
2334 This structure can be used to verify encryption levels, check server
2335 certificates, and more. Refer to the <productname>OpenSSL</productname>
2336 documentation for information about this structure.
2341 <varlistentry id="libpq-PQgetssl">
2342 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</primary></indexterm></term>
2345 <indexterm><primary>SSL</primary><secondary sortas="libpq">in libpq</secondary></indexterm>
2346 Returns the SSL structure used in the connection, or null
2347 if SSL is not in use.
2350 void *PQgetssl(const PGconn *conn);
2355 This function is equivalent to <literal>PQsslStruct(conn, "OpenSSL")</literal>. It should
2356 not be used in new applications, because the returned struct is
2357 specific to OpenSSL and will not be available if another SSL
2358 implementation is used. To check if a connection uses SSL, call
2359 <xref linkend="libpq-PQsslInUse"/> instead, and for more details about the
2360 connection, use <xref linkend="libpq-PQsslAttribute"/>.
2370 <sect1 id="libpq-exec">
2371 <title>Command Execution Functions</title>
2374 Once a connection to a database server has been successfully
2375 established, the functions described here are used to perform
2376 SQL queries and commands.
2379 <sect2 id="libpq-exec-main">
2380 <title>Main Functions</title>
2384 <varlistentry id="libpq-PQexec">
2385 <term><function>PQexec</function><indexterm><primary>PQexec</primary></indexterm></term>
2389 Submits a command to the server and waits for the result.
2392 PGresult *PQexec(PGconn *conn, const char *command);
2397 Returns a <structname>PGresult</structname> pointer or possibly a null
2398 pointer. A non-null pointer will generally be returned except in
2399 out-of-memory conditions or serious errors such as inability to send
2400 the command to the server. The <xref linkend="libpq-PQresultStatus"/> function
2401 should be called to check the return value for any errors (including
2402 the value of a null pointer, in which case it will return
2403 <symbol>PGRES_FATAL_ERROR</symbol>). Use
2404 <xref linkend="libpq-PQerrorMessage"/> to get more information about such
2411 The command string can include multiple SQL commands
2412 (separated by semicolons). Multiple queries sent in a single
2413 <xref linkend="libpq-PQexec"/> call are processed in a single transaction, unless
2414 there are explicit <command>BEGIN</command>/<command>COMMIT</command>
2415 commands included in the query string to divide it into multiple
2416 transactions. (See <xref linkend="protocol-flow-multi-statement"/>
2417 for more details about how the server handles multi-query strings.)
2418 Note however that the returned
2419 <structname>PGresult</structname> structure describes only the result
2420 of the last command executed from the string. Should one of the
2421 commands fail, processing of the string stops with it and the returned
2422 <structname>PGresult</structname> describes the error condition.
2427 <varlistentry id="libpq-PQexecParams">
2428 <term><function>PQexecParams</function><indexterm><primary>PQexecParams</primary></indexterm></term>
2432 Submits a command to the server and waits for the result,
2433 with the ability to pass parameters separately from the SQL
2437 PGresult *PQexecParams(PGconn *conn,
2438 const char *command,
2440 const Oid *paramTypes,
2441 const char * const *paramValues,
2442 const int *paramLengths,
2443 const int *paramFormats,
2449 <xref linkend="libpq-PQexecParams"/> is like <xref linkend="libpq-PQexec"/>, but offers additional
2450 functionality: parameter values can be specified separately from the command
2451 string proper, and query results can be requested in either text or binary
2452 format. <xref linkend="libpq-PQexecParams"/> is supported only in protocol 3.0 and later
2453 connections; it will fail when using protocol 2.0.
2457 The function arguments are:
2461 <term><parameter>conn</parameter></term>
2465 The connection object to send the command through.
2471 <term><parameter>command</parameter></term>
2474 The SQL command string to be executed. If parameters are used,
2475 they are referred to in the command string as <literal>$1</literal>,
2476 <literal>$2</literal>, etc.
2482 <term><parameter>nParams</parameter></term>
2485 The number of parameters supplied; it is the length of the arrays
2486 <parameter>paramTypes[]</parameter>, <parameter>paramValues[]</parameter>,
2487 <parameter>paramLengths[]</parameter>, and <parameter>paramFormats[]</parameter>. (The
2488 array pointers can be <symbol>NULL</symbol> when <parameter>nParams</parameter>
2495 <term><parameter>paramTypes[]</parameter></term>
2498 Specifies, by OID, the data types to be assigned to the
2499 parameter symbols. If <parameter>paramTypes</parameter> is
2500 <symbol>NULL</symbol>, or any particular element in the array
2501 is zero, the server infers a data type for the parameter symbol
2502 in the same way it would do for an untyped literal string.
2508 <term><parameter>paramValues[]</parameter></term>
2511 Specifies the actual values of the parameters. A null pointer
2512 in this array means the corresponding parameter is null;
2513 otherwise the pointer points to a zero-terminated text string
2514 (for text format) or binary data in the format expected by the
2515 server (for binary format).
2521 <term><parameter>paramLengths[]</parameter></term>
2524 Specifies the actual data lengths of binary-format parameters.
2525 It is ignored for null parameters and text-format parameters.
2526 The array pointer can be null when there are no binary parameters.
2532 <term><parameter>paramFormats[]</parameter></term>
2535 Specifies whether parameters are text (put a zero in the
2536 array entry for the corresponding parameter) or binary (put
2537 a one in the array entry for the corresponding parameter).
2538 If the array pointer is null then all parameters are presumed
2542 Values passed in binary format require knowledge of
2543 the internal representation expected by the backend.
2544 For example, integers must be passed in network byte
2545 order. Passing <type>numeric</type> values requires
2546 knowledge of the server storage format, as implemented
2548 <filename>src/backend/utils/adt/numeric.c::numeric_send()</filename> and
2549 <filename>src/backend/utils/adt/numeric.c::numeric_recv()</filename>.
2555 <term><parameter>resultFormat</parameter></term>
2558 Specify zero to obtain results in text format, or one to obtain
2559 results in binary format. (There is not currently a provision
2560 to obtain different result columns in different formats,
2561 although that is possible in the underlying protocol.)
2573 The primary advantage of <xref linkend="libpq-PQexecParams"/> over
2574 <xref linkend="libpq-PQexec"/> is that parameter values can be separated from the
2575 command string, thus avoiding the need for tedious and error-prone
2576 quoting and escaping.
2580 Unlike <xref linkend="libpq-PQexec"/>, <xref linkend="libpq-PQexecParams"/> allows at most
2581 one SQL command in the given string. (There can be semicolons in it,
2582 but not more than one nonempty command.) This is a limitation of the
2583 underlying protocol, but has some usefulness as an extra defense against
2584 SQL-injection attacks.
2589 Specifying parameter types via OIDs is tedious, particularly if you prefer
2590 not to hard-wire particular OID values into your program. However, you can
2591 avoid doing so even in cases where the server by itself cannot determine the
2592 type of the parameter, or chooses a different type than you want. In the
2593 SQL command text, attach an explicit cast to the parameter symbol to show what
2594 data type you will send. For example:
2596 SELECT * FROM mytable WHERE x = $1::bigint;
2598 This forces parameter <literal>$1</literal> to be treated as <type>bigint</type>, whereas
2599 by default it would be assigned the same type as <literal>x</literal>. Forcing the
2600 parameter type decision, either this way or by specifying a numeric type OID,
2601 is strongly recommended when sending parameter values in binary format, because
2602 binary format has less redundancy than text format and so there is less chance
2603 that the server will detect a type mismatch mistake for you.
2609 <varlistentry id="libpq-PQprepare">
2610 <term><function>PQprepare</function><indexterm><primary>PQprepare</primary></indexterm></term>
2614 Submits a request to create a prepared statement with the
2615 given parameters, and waits for completion.
2617 PGresult *PQprepare(PGconn *conn,
2618 const char *stmtName,
2621 const Oid *paramTypes);
2626 <xref linkend="libpq-PQprepare"/> creates a prepared statement for later
2627 execution with <xref linkend="libpq-PQexecPrepared"/>. This feature allows
2628 commands to be executed repeatedly without being parsed and
2629 planned each time; see <xref linkend="sql-prepare"/> for details.
2630 <xref linkend="libpq-PQprepare"/> is supported only in protocol 3.0 and later
2631 connections; it will fail when using protocol 2.0.
2635 The function creates a prepared statement named
2636 <parameter>stmtName</parameter> from the <parameter>query</parameter> string, which
2637 must contain a single SQL command. <parameter>stmtName</parameter> can be
2638 <literal>""</literal> to create an unnamed statement, in which case any
2639 pre-existing unnamed statement is automatically replaced; otherwise
2640 it is an error if the statement name is already defined in the
2641 current session. If any parameters are used, they are referred
2642 to in the query as <literal>$1</literal>, <literal>$2</literal>, etc.
2643 <parameter>nParams</parameter> is the number of parameters for which types
2644 are pre-specified in the array <parameter>paramTypes[]</parameter>. (The
2645 array pointer can be <symbol>NULL</symbol> when
2646 <parameter>nParams</parameter> is zero.) <parameter>paramTypes[]</parameter>
2647 specifies, by OID, the data types to be assigned to the parameter
2648 symbols. If <parameter>paramTypes</parameter> is <symbol>NULL</symbol>,
2649 or any particular element in the array is zero, the server assigns
2650 a data type to the parameter symbol in the same way it would do
2651 for an untyped literal string. Also, the query can use parameter
2652 symbols with numbers higher than <parameter>nParams</parameter>; data types
2653 will be inferred for these symbols as well. (See
2654 <xref linkend="libpq-PQdescribePrepared"/> for a means to find out
2655 what data types were inferred.)
2659 As with <xref linkend="libpq-PQexec"/>, the result is normally a
2660 <structname>PGresult</structname> object whose contents indicate
2661 server-side success or failure. A null result indicates
2662 out-of-memory or inability to send the command at all. Use
2663 <xref linkend="libpq-PQerrorMessage"/> to get more information about
2670 Prepared statements for use with <xref linkend="libpq-PQexecPrepared"/> can also
2671 be created by executing SQL <xref linkend="sql-prepare"/>
2672 statements. Also, although there is no <application>libpq</application>
2673 function for deleting a prepared statement, the SQL <xref
2674 linkend="sql-deallocate"/> statement
2675 can be used for that purpose.
2680 <varlistentry id="libpq-PQexecPrepared">
2681 <term><function>PQexecPrepared</function><indexterm><primary>PQexecPrepared</primary></indexterm></term>
2685 Sends a request to execute a prepared statement with given
2686 parameters, and waits for the result.
2688 PGresult *PQexecPrepared(PGconn *conn,
2689 const char *stmtName,
2691 const char * const *paramValues,
2692 const int *paramLengths,
2693 const int *paramFormats,
2699 <xref linkend="libpq-PQexecPrepared"/> is like <xref linkend="libpq-PQexecParams"/>,
2700 but the command to be executed is specified by naming a
2701 previously-prepared statement, instead of giving a query string.
2702 This feature allows commands that will be used repeatedly to be
2703 parsed and planned just once, rather than each time they are
2704 executed. The statement must have been prepared previously in
2705 the current session. <xref linkend="libpq-PQexecPrepared"/> is supported
2706 only in protocol 3.0 and later connections; it will fail when
2711 The parameters are identical to <xref linkend="libpq-PQexecParams"/>, except that the
2712 name of a prepared statement is given instead of a query string, and the
2713 <parameter>paramTypes[]</parameter> parameter is not present (it is not needed since
2714 the prepared statement's parameter types were determined when it was created).
2719 <varlistentry id="libpq-PQdescribePrepared">
2720 <term><function>PQdescribePrepared</function><indexterm><primary>PQdescribePrepared</primary></indexterm></term>
2724 Submits a request to obtain information about the specified
2725 prepared statement, and waits for completion.
2727 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
2732 <xref linkend="libpq-PQdescribePrepared"/> allows an application to obtain
2733 information about a previously prepared statement.
2734 <xref linkend="libpq-PQdescribePrepared"/> is supported only in protocol 3.0
2735 and later connections; it will fail when using protocol 2.0.
2739 <parameter>stmtName</parameter> can be <literal>""</literal> or <symbol>NULL</symbol> to reference
2740 the unnamed statement, otherwise it must be the name of an existing
2741 prepared statement. On success, a <structname>PGresult</structname> with
2742 status <literal>PGRES_COMMAND_OK</literal> is returned. The
2743 functions <xref linkend="libpq-PQnparams"/> and
2744 <xref linkend="libpq-PQparamtype"/> can be applied to this
2745 <structname>PGresult</structname> to obtain information about the parameters
2746 of the prepared statement, and the functions
2747 <xref linkend="libpq-PQnfields"/>, <xref linkend="libpq-PQfname"/>,
2748 <xref linkend="libpq-PQftype"/>, etc provide information about the
2749 result columns (if any) of the statement.
2754 <varlistentry id="libpq-PQdescribePortal">
2755 <term><function>PQdescribePortal</function><indexterm><primary>PQdescribePortal</primary></indexterm></term>
2759 Submits a request to obtain information about the specified
2760 portal, and waits for completion.
2762 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
2767 <xref linkend="libpq-PQdescribePortal"/> allows an application to obtain
2768 information about a previously created portal.
2769 (<application>libpq</application> does not provide any direct access to
2770 portals, but you can use this function to inspect the properties
2771 of a cursor created with a <command>DECLARE CURSOR</command> SQL command.)
2772 <xref linkend="libpq-PQdescribePortal"/> is supported only in protocol 3.0
2773 and later connections; it will fail when using protocol 2.0.
2777 <parameter>portalName</parameter> can be <literal>""</literal> or <symbol>NULL</symbol> to reference
2778 the unnamed portal, otherwise it must be the name of an existing
2779 portal. On success, a <structname>PGresult</structname> with status
2780 <literal>PGRES_COMMAND_OK</literal> is returned. The functions
2781 <xref linkend="libpq-PQnfields"/>, <xref linkend="libpq-PQfname"/>,
2782 <xref linkend="libpq-PQftype"/>, etc can be applied to the
2783 <structname>PGresult</structname> to obtain information about the result
2784 columns (if any) of the portal.
2792 The <structname>PGresult</structname><indexterm><primary>PGresult</primary></indexterm>
2793 structure encapsulates the result returned by the server.
2794 <application>libpq</application> application programmers should be
2795 careful to maintain the <structname>PGresult</structname> abstraction.
2796 Use the accessor functions below to get at the contents of
2797 <structname>PGresult</structname>. Avoid directly referencing the
2798 fields of the <structname>PGresult</structname> structure because they
2799 are subject to change in the future.
2802 <varlistentry id="libpq-PQresultStatus">
2803 <term><function>PQresultStatus</function><indexterm><primary>PQresultStatus</primary></indexterm></term>
2807 Returns the result status of the command.
2809 ExecStatusType PQresultStatus(const PGresult *res);
2814 <xref linkend="libpq-PQresultStatus"/> can return one of the following values:
2817 <varlistentry id="libpq-pgres-empty-query">
2818 <term><literal>PGRES_EMPTY_QUERY</literal></term>
2821 The string sent to the server was empty.
2826 <varlistentry id="libpq-pgres-command-ok">
2827 <term><literal>PGRES_COMMAND_OK</literal></term>
2830 Successful completion of a command returning no data.
2835 <varlistentry id="libpq-pgres-tuples-ok">
2836 <term><literal>PGRES_TUPLES_OK</literal></term>
2839 Successful completion of a command returning data (such as
2840 a <command>SELECT</command> or <command>SHOW</command>).
2845 <varlistentry id="libpq-pgres-copy-out">
2846 <term><literal>PGRES_COPY_OUT</literal></term>
2849 Copy Out (from server) data transfer started.
2854 <varlistentry id="libpq-pgres-copy-in">
2855 <term><literal>PGRES_COPY_IN</literal></term>
2858 Copy In (to server) data transfer started.
2863 <varlistentry id="libpq-pgres-bad-response">
2864 <term><literal>PGRES_BAD_RESPONSE</literal></term>
2867 The server's response was not understood.
2872 <varlistentry id="libpq-pgres-nonfatal-error">
2873 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
2876 A nonfatal error (a notice or warning) occurred.
2881 <varlistentry id="libpq-pgres-fatal-error">
2882 <term><literal>PGRES_FATAL_ERROR</literal></term>
2885 A fatal error occurred.
2890 <varlistentry id="libpq-pgres-copy-both">
2891 <term><literal>PGRES_COPY_BOTH</literal></term>
2894 Copy In/Out (to and from server) data transfer started. This
2895 feature is currently used only for streaming replication,
2896 so this status should not occur in ordinary applications.
2901 <varlistentry id="libpq-pgres-single-tuple">
2902 <term><literal>PGRES_SINGLE_TUPLE</literal></term>
2905 The <structname>PGresult</structname> contains a single result tuple
2906 from the current command. This status occurs only when
2907 single-row mode has been selected for the query
2908 (see <xref linkend="libpq-single-row-mode"/>).
2914 If the result status is <literal>PGRES_TUPLES_OK</literal> or
2915 <literal>PGRES_SINGLE_TUPLE</literal>, then
2916 the functions described below can be used to retrieve the rows
2917 returned by the query. Note that a <command>SELECT</command>
2918 command that happens to retrieve zero rows still shows
2919 <literal>PGRES_TUPLES_OK</literal>.
2920 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
2921 return rows (<command>INSERT</command> or <command>UPDATE</command>
2922 without a <literal>RETURNING</literal> clause,
2923 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
2924 indicate a bug in the client software.
2928 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
2929 never be returned directly by <xref linkend="libpq-PQexec"/> or other
2930 query execution functions; results of this kind are instead passed
2931 to the notice processor (see <xref
2932 linkend="libpq-notice-processing"/>).
2937 <varlistentry id="libpq-PQresStatus">
2938 <term><function>PQresStatus</function><indexterm><primary>PQresStatus</primary></indexterm></term>
2942 Converts the enumerated type returned by
2943 <xref linkend="libpq-PQresultStatus"/> into a string constant describing the
2944 status code. The caller should not free the result.
2947 char *PQresStatus(ExecStatusType status);
2953 <varlistentry id="libpq-PQresultErrorMessage">
2954 <term><function>PQresultErrorMessage</function><indexterm><primary>PQresultErrorMessage</primary></indexterm></term>
2958 Returns the error message associated with the command, or an empty string
2959 if there was no error.
2961 char *PQresultErrorMessage(const PGresult *res);
2963 If there was an error, the returned string will include a trailing
2964 newline. The caller should not free the result directly. It will
2965 be freed when the associated <structname>PGresult</structname> handle is
2966 passed to <xref linkend="libpq-PQclear"/>.
2970 Immediately following a <xref linkend="libpq-PQexec"/> or
2971 <xref linkend="libpq-PQgetResult"/> call,
2972 <xref linkend="libpq-PQerrorMessage"/> (on the connection) will return
2973 the same string as <xref linkend="libpq-PQresultErrorMessage"/> (on
2974 the result). However, a <structname>PGresult</structname> will
2975 retain its error message until destroyed, whereas the connection's
2976 error message will change when subsequent operations are done.
2977 Use <xref linkend="libpq-PQresultErrorMessage"/> when you want to
2978 know the status associated with a particular
2979 <structname>PGresult</structname>; use
2980 <xref linkend="libpq-PQerrorMessage"/> when you want to know the
2981 status from the latest operation on the connection.
2986 <varlistentry id="libpq-PQresultVerboseErrorMessage">
2987 <term><function>PQresultVerboseErrorMessage</function><indexterm><primary>PQresultVerboseErrorMessage</primary></indexterm></term>
2991 Returns a reformatted version of the error message associated with
2992 a <structname>PGresult</structname> object.
2994 char *PQresultVerboseErrorMessage(const PGresult *res,
2995 PGVerbosity verbosity,
2996 PGContextVisibility show_context);
2998 In some situations a client might wish to obtain a more detailed
2999 version of a previously-reported error.
3000 <xref linkend="libpq-PQresultVerboseErrorMessage"/> addresses this need
3001 by computing the message that would have been produced
3002 by <xref linkend="libpq-PQresultErrorMessage"/> if the specified
3003 verbosity settings had been in effect for the connection when the
3004 given <structname>PGresult</structname> was generated. If
3005 the <structname>PGresult</structname> is not an error result,
3006 <quote>PGresult is not an error result</quote> is reported instead.
3007 The returned string includes a trailing newline.
3011 Unlike most other functions for extracting data from
3012 a <structname>PGresult</structname>, the result of this function is a freshly
3013 allocated string. The caller must free it
3014 using <function>PQfreemem()</function> when the string is no longer needed.
3018 A NULL return is possible if there is insufficient memory.
3023 <varlistentry id="libpq-PQresultErrorField">
3024 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</primary></indexterm></term>
3027 Returns an individual field of an error report.
3029 char *PQresultErrorField(const PGresult *res, int fieldcode);
3031 <parameter>fieldcode</parameter> is an error field identifier; see the symbols
3032 listed below. <symbol>NULL</symbol> is returned if the
3033 <structname>PGresult</structname> is not an error or warning result,
3034 or does not include the specified field. Field values will normally
3035 not include a trailing newline. The caller should not free the
3036 result directly. It will be freed when the
3037 associated <structname>PGresult</structname> handle is passed to
3038 <xref linkend="libpq-PQclear"/>.
3042 The following field codes are available:
3044 <varlistentry id="libpq-pg-diag-severity">
3045 <term><symbol>PG_DIAG_SEVERITY</symbol></term>
3048 The severity; the field contents are <literal>ERROR</literal>,
3049 <literal>FATAL</literal>, or <literal>PANIC</literal> (in an error message),
3050 or <literal>WARNING</literal>, <literal>NOTICE</literal>, <literal>DEBUG</literal>,
3051 <literal>INFO</literal>, or <literal>LOG</literal> (in a notice message), or
3052 a localized translation of one of these. Always present.
3057 <varlistentry id="libpq-PG-diag-severity-nonlocalized">
3058 <term><symbol>PG_DIAG_SEVERITY_NONLOCALIZED</symbol></term>
3061 The severity; the field contents are <literal>ERROR</literal>,
3062 <literal>FATAL</literal>, or <literal>PANIC</literal> (in an error message),
3063 or <literal>WARNING</literal>, <literal>NOTICE</literal>, <literal>DEBUG</literal>,
3064 <literal>INFO</literal>, or <literal>LOG</literal> (in a notice message).
3065 This is identical to the <symbol>PG_DIAG_SEVERITY</symbol> field except
3066 that the contents are never localized. This is present only in
3067 reports generated by <productname>PostgreSQL</productname> versions 9.6
3073 <varlistentry id="libpq-pg-diag-sqlstate">
3074 <term><symbol>PG_DIAG_SQLSTATE</symbol><indexterm
3075 ><primary>error codes</primary><secondary>libpq</secondary></indexterm></term>
3078 The SQLSTATE code for the error. The SQLSTATE code identifies
3079 the type of error that has occurred; it can be used by
3080 front-end applications to perform specific operations (such
3081 as error handling) in response to a particular database error.
3082 For a list of the possible SQLSTATE codes, see <xref
3083 linkend="errcodes-appendix"/>. This field is not localizable,
3084 and is always present.
3089 <varlistentry id="libpq-pg-diag-message-primary">
3090 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</symbol></term>
3093 The primary human-readable error message (typically one line).
3099 <varlistentry id="libpq-pg-diag-message-detail">
3100 <term><symbol>PG_DIAG_MESSAGE_DETAIL</symbol></term>
3103 Detail: an optional secondary error message carrying more
3104 detail about the problem. Might run to multiple lines.
3109 <varlistentry id="libpq-pg-diag-message-hint">
3110 <term><symbol>PG_DIAG_MESSAGE_HINT</symbol></term>
3113 Hint: an optional suggestion what to do about the problem.
3114 This is intended to differ from detail in that it offers advice
3115 (potentially inappropriate) rather than hard facts. Might
3116 run to multiple lines.
3121 <varlistentry id="libpq-pg-diag-statement-position">
3122 <term><symbol>PG_DIAG_STATEMENT_POSITION</symbol></term>
3125 A string containing a decimal integer indicating an error cursor
3126 position as an index into the original statement string. The
3127 first character has index 1, and positions are measured in
3128 characters not bytes.
3133 <varlistentry id="libpq-pg-diag-internal-position">
3134 <term><symbol>PG_DIAG_INTERNAL_POSITION</symbol></term>
3137 This is defined the same as the
3138 <symbol>PG_DIAG_STATEMENT_POSITION</symbol> field, but it is used
3139 when the cursor position refers to an internally generated
3140 command rather than the one submitted by the client. The
3141 <symbol>PG_DIAG_INTERNAL_QUERY</symbol> field will always appear when
3147 <varlistentry id="libpq-pg-diag-internal-query">
3148 <term><symbol>PG_DIAG_INTERNAL_QUERY</symbol></term>
3151 The text of a failed internally-generated command. This could
3152 be, for example, a SQL query issued by a PL/pgSQL function.
3157 <varlistentry id="libpq-pg-diag-context">
3158 <term><symbol>PG_DIAG_CONTEXT</symbol></term>
3161 An indication of the context in which the error occurred.
3162 Presently this includes a call stack traceback of active
3163 procedural language functions and internally-generated queries.
3164 The trace is one entry per line, most recent first.
3169 <varlistentry id="libpq-pg-diag-schema-name">
3170 <term><symbol>PG_DIAG_SCHEMA_NAME</symbol></term>
3173 If the error was associated with a specific database object,
3174 the name of the schema containing that object, if any.
3179 <varlistentry id="libpq-pg-diag-table-name">
3180 <term><symbol>PG_DIAG_TABLE_NAME</symbol></term>
3183 If the error was associated with a specific table, the name of the
3184 table. (Refer to the schema name field for the name of the
3190 <varlistentry id="libpq-pg-diag-column-name">
3191 <term><symbol>PG_DIAG_COLUMN_NAME</symbol></term>
3194 If the error was associated with a specific table column, the name
3195 of the column. (Refer to the schema and table name fields to
3196 identify the table.)
3201 <varlistentry id="libpq-pg-diag-datatype-name">
3202 <term><symbol>PG_DIAG_DATATYPE_NAME</symbol></term>
3205 If the error was associated with a specific data type, the name of
3206 the data type. (Refer to the schema name field for the name of
3207 the data type's schema.)
3212 <varlistentry id="libpq-pg-diag-constraint-name">
3213 <term><symbol>PG_DIAG_CONSTRAINT_NAME</symbol></term>
3216 If the error was associated with a specific constraint, the name
3217 of the constraint. Refer to fields listed above for the
3218 associated table or domain. (For this purpose, indexes are
3219 treated as constraints, even if they weren't created with
3225 <varlistentry id="libpq-pg-diag-source-file">
3226 <term><symbol>PG_DIAG_SOURCE_FILE</symbol></term>
3229 The file name of the source-code location where the error was
3235 <varlistentry id="libpq-pg-diag-source-line">
3236 <term><symbol>PG_DIAG_SOURCE_LINE</symbol></term>
3239 The line number of the source-code location where the error
3245 <varlistentry id="libpq-pg-diag-source-function">
3246 <term><symbol>PG_DIAG_SOURCE_FUNCTION</symbol></term>
3249 The name of the source-code function reporting the error.
3258 The fields for schema name, table name, column name, data type name,
3259 and constraint name are supplied only for a limited number of error
3260 types; see <xref linkend="errcodes-appendix"/>. Do not assume that
3261 the presence of any of these fields guarantees the presence of
3262 another field. Core error sources observe the interrelationships
3263 noted above, but user-defined functions may use these fields in other
3264 ways. In the same vein, do not assume that these fields denote
3265 contemporary objects in the current database.
3270 The client is responsible for formatting displayed information to meet
3271 its needs; in particular it should break long lines as needed.
3272 Newline characters appearing in the error message fields should be
3273 treated as paragraph breaks, not line breaks.
3277 Errors generated internally by <application>libpq</application> will
3278 have severity and primary message, but typically no other fields.
3279 Errors returned by a pre-3.0-protocol server will include severity and
3280 primary message, and sometimes a detail message, but no other fields.
3284 Note that error fields are only available from
3285 <structname>PGresult</structname> objects, not
3286 <structname>PGconn</structname> objects; there is no
3287 <function>PQerrorField</function> function.
3292 <varlistentry id="libpq-PQclear">
3293 <term><function>PQclear</function><indexterm><primary>PQclear</primary></indexterm></term>
3296 Frees the storage associated with a
3297 <structname>PGresult</structname>. Every command result should be
3298 freed via <xref linkend="libpq-PQclear"/> when it is no longer
3302 void PQclear(PGresult *res);
3307 You can keep a <structname>PGresult</structname> object around for
3308 as long as you need it; it does not go away when you issue a new
3309 command, nor even if you close the connection. To get rid of it,
3310 you must call <xref linkend="libpq-PQclear"/>. Failure to do this
3311 will result in memory leaks in your application.
3319 <sect2 id="libpq-exec-select-info">
3320 <title>Retrieving Query Result Information</title>
3323 These functions are used to extract information from a
3324 <structname>PGresult</structname> object that represents a successful
3325 query result (that is, one that has status
3326 <literal>PGRES_TUPLES_OK</literal> or <literal>PGRES_SINGLE_TUPLE</literal>).
3327 They can also be used to extract
3328 information from a successful Describe operation: a Describe's result
3329 has all the same column information that actual execution of the query
3330 would provide, but it has zero rows. For objects with other status values,
3331 these functions will act as though the result has zero rows and zero columns.
3335 <varlistentry id="libpq-PQntuples">
3336 <term><function>PQntuples</function><indexterm><primary>PQntuples</primary></indexterm></term>
3340 Returns the number of rows (tuples) in the query result.
3341 (Note that <structname>PGresult</structname> objects are limited to no more
3342 than <literal>INT_MAX</literal> rows, so an <type>int</type> result is
3346 int PQntuples(const PGresult *res);
3353 <varlistentry id="libpq-PQnfields">
3354 <term><function>PQnfields</function><indexterm><primary>PQnfields</primary></indexterm></term>
3358 Returns the number of columns (fields) in each row of the query
3362 int PQnfields(const PGresult *res);
3368 <varlistentry id="libpq-PQfname">
3369 <term><function>PQfname</function><indexterm><primary>PQfname</primary></indexterm></term>
3373 Returns the column name associated with the given column number.
3374 Column numbers start at 0. The caller should not free the result
3375 directly. It will be freed when the associated
3376 <structname>PGresult</structname> handle is passed to
3377 <xref linkend="libpq-PQclear"/>.
3379 char *PQfname(const PGresult *res,
3385 <symbol>NULL</symbol> is returned if the column number is out of range.
3390 <varlistentry id="libpq-PQfnumber">
3391 <term><function>PQfnumber</function><indexterm><primary>PQfnumber</primary></indexterm></term>
3395 Returns the column number associated with the given column name.
3397 int PQfnumber(const PGresult *res,
3398 const char *column_name);
3403 -1 is returned if the given name does not match any column.
3407 The given name is treated like an identifier in an SQL command,
3408 that is, it is downcased unless double-quoted. For example, given
3409 a query result generated from the SQL command:
3411 SELECT 1 AS FOO, 2 AS "BAR";
3413 we would have the results:
3415 PQfname(res, 0) <lineannotation>foo</lineannotation>
3416 PQfname(res, 1) <lineannotation>BAR</lineannotation>
3417 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
3418 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
3419 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
3420 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
3426 <varlistentry id="libpq-PQftable">
3427 <term><function>PQftable</function><indexterm><primary>PQftable</primary></indexterm></term>
3431 Returns the OID of the table from which the given column was
3432 fetched. Column numbers start at 0.
3434 Oid PQftable(const PGresult *res,
3440 <literal>InvalidOid</literal> is returned if the column number is out of range,
3441 or if the specified column is not a simple reference to a table column,
3442 or when using pre-3.0 protocol.
3443 You can query the system table <literal>pg_class</literal> to determine
3444 exactly which table is referenced.
3448 The type <type>Oid</type> and the constant
3449 <literal>InvalidOid</literal> will be defined when you include
3450 the <application>libpq</application> header file. They will both
3451 be some integer type.
3456 <varlistentry id="libpq-PQftablecol">
3457 <term><function>PQftablecol</function><indexterm><primary>PQftablecol</primary></indexterm></term>
3461 Returns the column number (within its table) of the column making
3462 up the specified query result column. Query-result column numbers
3463 start at 0, but table columns have nonzero numbers.
3465 int PQftablecol(const PGresult *res,
3471 Zero is returned if the column number is out of range, or if the
3472 specified column is not a simple reference to a table column, or
3473 when using pre-3.0 protocol.
3478 <varlistentry id="libpq-PQfformat">
3479 <term><function>PQfformat</function><indexterm><primary>PQfformat</primary></indexterm></term>
3483 Returns the format code indicating the format of the given
3484 column. Column numbers start at 0.
3486 int PQfformat(const PGresult *res,
3492 Format code zero indicates textual data representation, while format
3493 code one indicates binary representation. (Other codes are reserved
3494 for future definition.)
3499 <varlistentry id="libpq-PQftype">
3500 <term><function>PQftype</function><indexterm><primary>PQftype</primary></indexterm></term>
3504 Returns the data type associated with the given column number.
3505 The integer returned is the internal OID number of the type.
3506 Column numbers start at 0.
3508 Oid PQftype(const PGresult *res,
3514 You can query the system table <literal>pg_type</literal> to
3515 obtain the names and properties of the various data types. The
3516 <acronym>OID</acronym>s of the built-in data types are defined
3517 in the file <filename>src/include/catalog/pg_type_d.h</filename>
3523 <varlistentry id="libpq-PQfmod">
3524 <term><function>PQfmod</function><indexterm><primary>PQfmod</primary></indexterm></term>
3528 Returns the type modifier of the column associated with the
3529 given column number. Column numbers start at 0.
3531 int PQfmod(const PGresult *res,
3537 The interpretation of modifier values is type-specific; they
3538 typically indicate precision or size limits. The value -1 is
3539 used to indicate <quote>no information available</quote>. Most data
3540 types do not use modifiers, in which case the value is always
3546 <varlistentry id="libpq-PQfsize">
3547 <term><function>PQfsize</function><indexterm><primary>PQfsize</primary></indexterm></term>
3551 Returns the size in bytes of the column associated with the
3552 given column number. Column numbers start at 0.
3554 int PQfsize(const PGresult *res,
3560 <xref linkend="libpq-PQfsize"/> returns the space allocated for this column
3561 in a database row, in other words the size of the server's
3562 internal representation of the data type. (Accordingly, it is
3563 not really very useful to clients.) A negative value indicates
3564 the data type is variable-length.
3569 <varlistentry id="libpq-PQbinaryTuples">
3570 <term><function>PQbinaryTuples</function><indexterm><primary>PQbinaryTuples</primary></indexterm></term>
3574 Returns 1 if the <structname>PGresult</structname> contains binary data
3575 and 0 if it contains text data.
3577 int PQbinaryTuples(const PGresult *res);
3582 This function is deprecated (except for its use in connection with
3583 <command>COPY</command>), because it is possible for a single
3584 <structname>PGresult</structname> to contain text data in some columns and
3585 binary data in others. <xref linkend="libpq-PQfformat"/> is preferred.
3586 <xref linkend="libpq-PQbinaryTuples"/> returns 1 only if all columns of the
3587 result are binary (format 1).
3592 <varlistentry id="libpq-PQgetvalue">
3593 <term><function>PQgetvalue</function><indexterm><primary>PQgetvalue</primary></indexterm></term>
3597 Returns a single field value of one row of a
3598 <structname>PGresult</structname>. Row and column numbers start
3599 at 0. The caller should not free the result directly. It will
3600 be freed when the associated <structname>PGresult</structname> handle is
3601 passed to <xref linkend="libpq-PQclear"/>.
3603 char *PQgetvalue(const PGresult *res,
3610 For data in text format, the value returned by
3611 <xref linkend="libpq-PQgetvalue"/> is a null-terminated character
3612 string representation of the field value. For data in binary
3613 format, the value is in the binary representation determined by
3614 the data type's <function>typsend</function> and <function>typreceive</function>
3615 functions. (The value is actually followed by a zero byte in
3616 this case too, but that is not ordinarily useful, since the
3617 value is likely to contain embedded nulls.)
3621 An empty string is returned if the field value is null. See
3622 <xref linkend="libpq-PQgetisnull"/> to distinguish null values from
3623 empty-string values.
3627 The pointer returned by <xref linkend="libpq-PQgetvalue"/> points
3628 to storage that is part of the <structname>PGresult</structname>
3629 structure. One should not modify the data it points to, and one
3630 must explicitly copy the data into other storage if it is to be
3631 used past the lifetime of the <structname>PGresult</structname>
3637 <varlistentry id="libpq-PQgetisnull">
3638 <term><function>PQgetisnull</function><indexterm
3639 ><primary>PQgetisnull</primary></indexterm><indexterm
3640 ><primary>null value</primary><secondary sortas="libpq">in libpq</secondary></indexterm></term>
3644 Tests a field for a null value. Row and column numbers start
3647 int PQgetisnull(const PGresult *res,
3654 This function returns 1 if the field is null and 0 if it
3655 contains a non-null value. (Note that
3656 <xref linkend="libpq-PQgetvalue"/> will return an empty string,
3657 not a null pointer, for a null field.)
3662 <varlistentry id="libpq-PQgetlength">
3663 <term><function>PQgetlength</function><indexterm><primary>PQgetlength</primary></indexterm></term>
3667 Returns the actual length of a field value in bytes. Row and
3668 column numbers start at 0.
3670 int PQgetlength(const PGresult *res,
3677 This is the actual data length for the particular data value,
3678 that is, the size of the object pointed to by
3679 <xref linkend="libpq-PQgetvalue"/>. For text data format this is
3680 the same as <function>strlen()</function>. For binary format this is
3681 essential information. Note that one should <emphasis>not</emphasis>
3682 rely on <xref linkend="libpq-PQfsize"/> to obtain the actual data
3688 <varlistentry id="libpq-PQnparams">
3689 <term><function>PQnparams</function><indexterm><primary>PQnparams</primary></indexterm></term>
3693 Returns the number of parameters of a prepared statement.
3695 int PQnparams(const PGresult *res);
3700 This function is only useful when inspecting the result of
3701 <xref linkend="libpq-PQdescribePrepared"/>. For other types of queries it
3707 <varlistentry id="libpq-PQparamtype">
3708 <term><function>PQparamtype</function><indexterm><primary>PQparamtype</primary></indexterm></term>
3712 Returns the data type of the indicated statement parameter.
3713 Parameter numbers start at 0.
3715 Oid PQparamtype(const PGresult *res, int param_number);
3720 This function is only useful when inspecting the result of
3721 <xref linkend="libpq-PQdescribePrepared"/>. For other types of queries it
3727 <varlistentry id="libpq-PQprint">
3728 <term><function>PQprint</function><indexterm><primary>PQprint</primary></indexterm></term>
3732 Prints out all the rows and, optionally, the column names to
3733 the specified output stream.
3735 void PQprint(FILE *fout, /* output stream */
3736 const PGresult *res,
3737 const PQprintOpt *po);
3740 pqbool header; /* print output field headings and row count */
3741 pqbool align; /* fill align the fields */
3742 pqbool standard; /* old brain dead format */
3743 pqbool html3; /* output HTML tables */
3744 pqbool expanded; /* expand tables */
3745 pqbool pager; /* use pager for output if needed */
3746 char *fieldSep; /* field separator */
3747 char *tableOpt; /* attributes for HTML table element */
3748 char *caption; /* HTML table caption */
3749 char **fieldName; /* null-terminated array of replacement field names */
3755 This function was formerly used by <application>psql</application>
3756 to print query results, but this is no longer the case. Note
3757 that it assumes all the data is in text format.
3764 <sect2 id="libpq-exec-nonselect">
3765 <title>Retrieving Other Result Information</title>
3768 These functions are used to extract other information from
3769 <structname>PGresult</structname> objects.
3773 <varlistentry id="libpq-PQcmdStatus">
3774 <term><function>PQcmdStatus</function><indexterm><primary>PQcmdStatus</primary></indexterm></term>
3778 Returns the command status tag from the SQL command that generated
3779 the <structname>PGresult</structname>.
3781 char *PQcmdStatus(PGresult *res);
3786 Commonly this is just the name of the command, but it might include
3787 additional data such as the number of rows processed. The caller
3788 should not free the result directly. It will be freed when the
3789 associated <structname>PGresult</structname> handle is passed to
3790 <xref linkend="libpq-PQclear"/>.
3795 <varlistentry id="libpq-PQcmdTuples">
3796 <term><function>PQcmdTuples</function><indexterm><primary>PQcmdTuples</primary></indexterm></term>
3800 Returns the number of rows affected by the SQL command.
3802 char *PQcmdTuples(PGresult *res);
3807 This function returns a string containing the number of rows
3808 affected by the <acronym>SQL</acronym> statement that generated the
3809 <structname>PGresult</structname>. This function can only be used following
3810 the execution of a <command>SELECT</command>, <command>CREATE TABLE AS</command>,
3811 <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
3812 <command>MOVE</command>, <command>FETCH</command>, or <command>COPY</command> statement,
3813 or an <command>EXECUTE</command> of a prepared query that contains an
3814 <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command> statement.
3815 If the command that generated the <structname>PGresult</structname> was anything
3816 else, <xref linkend="libpq-PQcmdTuples"/> returns an empty string. The caller
3817 should not free the return value directly. It will be freed when
3818 the associated <structname>PGresult</structname> handle is passed to
3819 <xref linkend="libpq-PQclear"/>.
3824 <varlistentry id="libpq-PQoidValue">
3825 <term><function>PQoidValue</function><indexterm><primary>PQoidValue</primary></indexterm></term>
3829 Returns the OID<indexterm><primary>OID</primary><secondary>in libpq</secondary></indexterm>
3830 of the inserted row, if the <acronym>SQL</acronym> command was an
3831 <command>INSERT</command> that inserted exactly one row into a table that
3832 has OIDs, or a <command>EXECUTE</command> of a prepared query containing
3833 a suitable <command>INSERT</command> statement. Otherwise, this function
3834 returns <literal>InvalidOid</literal>. This function will also
3835 return <literal>InvalidOid</literal> if the table affected by the
3836 <command>INSERT</command> statement does not contain OIDs.
3838 Oid PQoidValue(const PGresult *res);
3844 <varlistentry id="libpq-PQoidStatus">
3845 <term><function>PQoidStatus</function><indexterm><primary>PQoidStatus</primary></indexterm></term>
3849 This function is deprecated in favor of
3850 <xref linkend="libpq-PQoidValue"/> and is not thread-safe.
3851 It returns a string with the OID of the inserted row, while
3852 <xref linkend="libpq-PQoidValue"/> returns the OID value.
3854 char *PQoidStatus(const PGresult *res);
3864 <sect2 id="libpq-exec-escape-string">
3865 <title>Escaping Strings for Inclusion in SQL Commands</title>
3867 <indexterm zone="libpq-exec-escape-string">
3868 <primary>escaping strings</primary>
3869 <secondary>in libpq</secondary>
3873 <varlistentry id="libpq-PQescapeLiteral">
3874 <term><function>PQescapeLiteral</function><indexterm><primary>PQescapeLiteral</primary></indexterm></term>
3879 char *PQescapeLiteral(PGconn *conn, const char *str, size_t length);
3884 <xref linkend="libpq-PQescapeLiteral"/> escapes a string for
3885 use within an SQL command. This is useful when inserting data
3886 values as literal constants in SQL commands. Certain characters
3887 (such as quotes and backslashes) must be escaped to prevent them
3888 from being interpreted specially by the SQL parser.
3889 <xref linkend="libpq-PQescapeLiteral"/> performs this operation.
3893 <xref linkend="libpq-PQescapeLiteral"/> returns an escaped version of the
3894 <parameter>str</parameter> parameter in memory allocated with
3895 <function>malloc()</function>. This memory should be freed using
3896 <function>PQfreemem()</function> when the result is no longer needed.
3897 A terminating zero byte is not required, and should not be
3898 counted in <parameter>length</parameter>. (If a terminating zero byte is found
3899 before <parameter>length</parameter> bytes are processed,
3900 <xref linkend="libpq-PQescapeLiteral"/> stops at the zero; the behavior is
3901 thus rather like <function>strncpy</function>.) The
3902 return string has all special characters replaced so that they can
3903 be properly processed by the <productname>PostgreSQL</productname>
3904 string literal parser. A terminating zero byte is also added. The
3905 single quotes that must surround <productname>PostgreSQL</productname>
3906 string literals are included in the result string.
3910 On error, <xref linkend="libpq-PQescapeLiteral"/> returns <symbol>NULL</symbol> and a suitable
3911 message is stored in the <parameter>conn</parameter> object.
3916 It is especially important to do proper escaping when handling
3917 strings that were received from an untrustworthy source.
3918 Otherwise there is a security risk: you are vulnerable to
3919 <quote>SQL injection</quote> attacks wherein unwanted SQL commands are
3920 fed to your database.
3925 Note that it is neither necessary nor correct to do escaping when a data
3926 value is passed as a separate parameter in <xref linkend="libpq-PQexecParams"/> or
3927 its sibling routines.
3932 <varlistentry id="libpq-PQescapeIdentifier">
3933 <term><function>PQescapeIdentifier</function><indexterm><primary>PQescapeIdentifier</primary></indexterm></term>
3938 char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length);
3943 <xref linkend="libpq-PQescapeIdentifier"/> escapes a string for
3944 use as an SQL identifier, such as a table, column, or function name.
3945 This is useful when a user-supplied identifier might contain
3946 special characters that would otherwise not be interpreted as part
3947 of the identifier by the SQL parser, or when the identifier might
3948 contain upper case characters whose case should be preserved.
3952 <xref linkend="libpq-PQescapeIdentifier"/> returns a version of the
3953 <parameter>str</parameter> parameter escaped as an SQL identifier
3954 in memory allocated with <function>malloc()</function>. This memory must be
3955 freed using <function>PQfreemem()</function> when the result is no longer
3956 needed. A terminating zero byte is not required, and should not be
3957 counted in <parameter>length</parameter>. (If a terminating zero byte is found
3958 before <parameter>length</parameter> bytes are processed,
3959 <xref linkend="libpq-PQescapeIdentifier"/> stops at the zero; the behavior is
3960 thus rather like <function>strncpy</function>.) The
3961 return string has all special characters replaced so that it
3962 will be properly processed as an SQL identifier. A terminating zero byte
3963 is also added. The return string will also be surrounded by double
3968 On error, <xref linkend="libpq-PQescapeIdentifier"/> returns <symbol>NULL</symbol> and a suitable
3969 message is stored in the <parameter>conn</parameter> object.
3974 As with string literals, to prevent SQL injection attacks,
3975 SQL identifiers must be escaped when they are received from an
3976 untrustworthy source.
3982 <varlistentry id="libpq-PQescapeStringConn">
3983 <term><function>PQescapeStringConn</function><indexterm><primary>PQescapeStringConn</primary></indexterm></term>
3988 size_t PQescapeStringConn(PGconn *conn,
3989 char *to, const char *from, size_t length,
3995 <xref linkend="libpq-PQescapeStringConn"/> escapes string literals, much like
3996 <xref linkend="libpq-PQescapeLiteral"/>. Unlike <xref linkend="libpq-PQescapeLiteral"/>,
3997 the caller is responsible for providing an appropriately sized buffer.
3998 Furthermore, <xref linkend="libpq-PQescapeStringConn"/> does not generate the
3999 single quotes that must surround <productname>PostgreSQL</productname> string
4000 literals; they should be provided in the SQL command that the
4001 result is inserted into. The parameter <parameter>from</parameter> points to
4002 the first character of the string that is to be escaped, and the
4003 <parameter>length</parameter> parameter gives the number of bytes in this
4004 string. A terminating zero byte is not required, and should not be
4005 counted in <parameter>length</parameter>. (If a terminating zero byte is found
4006 before <parameter>length</parameter> bytes are processed,
4007 <xref linkend="libpq-PQescapeStringConn"/> stops at the zero; the behavior is
4008 thus rather like <function>strncpy</function>.) <parameter>to</parameter> shall point
4009 to a buffer that is able to hold at least one more byte than twice
4010 the value of <parameter>length</parameter>, otherwise the behavior is undefined.
4011 Behavior is likewise undefined if the <parameter>to</parameter> and
4012 <parameter>from</parameter> strings overlap.
4016 If the <parameter>error</parameter> parameter is not <symbol>NULL</symbol>, then
4017 <literal>*error</literal> is set to zero on success, nonzero on error.
4018 Presently the only possible error conditions involve invalid multibyte
4019 encoding in the source string. The output string is still generated
4020 on error, but it can be expected that the server will reject it as
4021 malformed. On error, a suitable message is stored in the
4022 <parameter>conn</parameter> object, whether or not <parameter>error</parameter> is <symbol>NULL</symbol>.
4026 <xref linkend="libpq-PQescapeStringConn"/> returns the number of bytes written
4027 to <parameter>to</parameter>, not including the terminating zero byte.
4032 <varlistentry id="libpq-PQescapeString">
4033 <term><function>PQescapeString</function><indexterm><primary>PQescapeString</primary></indexterm></term>
4037 <xref linkend="libpq-PQescapeString"/> is an older, deprecated version of
4038 <xref linkend="libpq-PQescapeStringConn"/>.
4040 size_t PQescapeString (char *to, const char *from, size_t length);
4045 The only difference from <xref linkend="libpq-PQescapeStringConn"/> is that
4046 <xref linkend="libpq-PQescapeString"/> does not take <structname>PGconn</structname>
4047 or <parameter>error</parameter> parameters.
4048 Because of this, it cannot adjust its behavior depending on the
4049 connection properties (such as character encoding) and therefore
4050 <emphasis>it might give the wrong results</emphasis>. Also, it has no way
4051 to report error conditions.
4055 <xref linkend="libpq-PQescapeString"/> can be used safely in
4056 client programs that work with only one <productname>PostgreSQL</productname>
4057 connection at a time (in this case it can find out what it needs to
4058 know <quote>behind the scenes</quote>). In other contexts it is a security
4059 hazard and should be avoided in favor of
4060 <xref linkend="libpq-PQescapeStringConn"/>.
4065 <varlistentry id="libpq-PQescapeByteaConn">
4066 <term><function>PQescapeByteaConn</function><indexterm><primary>PQescapeByteaConn</primary></indexterm></term>
4070 Escapes binary data for use within an SQL command with the type
4071 <type>bytea</type>. As with <xref linkend="libpq-PQescapeStringConn"/>,
4072 this is only used when inserting data directly into an SQL command string.
4074 unsigned char *PQescapeByteaConn(PGconn *conn,
4075 const unsigned char *from,
4082 Certain byte values must be escaped when used as part of a
4083 <type>bytea</type> literal in an <acronym>SQL</acronym> statement.
4084 <xref linkend="libpq-PQescapeByteaConn"/> escapes bytes using
4085 either hex encoding or backslash escaping. See <xref
4086 linkend="datatype-binary"/> for more information.
4090 The <parameter>from</parameter> parameter points to the first
4091 byte of the string that is to be escaped, and the
4092 <parameter>from_length</parameter> parameter gives the number of
4093 bytes in this binary string. (A terminating zero byte is
4094 neither necessary nor counted.) The <parameter>to_length</parameter>
4095 parameter points to a variable that will hold the resultant
4096 escaped string length. This result string length includes the terminating
4097 zero byte of the result.
4101 <xref linkend="libpq-PQescapeByteaConn"/> returns an escaped version of the
4102 <parameter>from</parameter> parameter binary string in memory
4103 allocated with <function>malloc()</function>. This memory should be freed using
4104 <function>PQfreemem()</function> when the result is no longer needed. The
4105 return string has all special characters replaced so that they can
4106 be properly processed by the <productname>PostgreSQL</productname>
4107 string literal parser, and the <type>bytea</type> input function. A
4108 terminating zero byte is also added. The single quotes that must
4109 surround <productname>PostgreSQL</productname> string literals are
4110 not part of the result string.
4114 On error, a null pointer is returned, and a suitable error message
4115 is stored in the <parameter>conn</parameter> object. Currently, the only
4116 possible error is insufficient memory for the result string.
4121 <varlistentry id="libpq-PQescapeBytea">
4122 <term><function>PQescapeBytea</function><indexterm><primary>PQescapeBytea</primary></indexterm></term>
4126 <xref linkend="libpq-PQescapeBytea"/> is an older, deprecated version of
4127 <xref linkend="libpq-PQescapeByteaConn"/>.
4129 unsigned char *PQescapeBytea(const unsigned char *from,
4136 The only difference from <xref linkend="libpq-PQescapeByteaConn"/> is that
4137 <xref linkend="libpq-PQescapeBytea"/> does not take a <structname>PGconn</structname>
4138 parameter. Because of this, <xref linkend="libpq-PQescapeBytea"/> can
4139 only be used safely in client programs that use a single
4140 <productname>PostgreSQL</productname> connection at a time (in this case
4141 it can find out what it needs to know <quote>behind the
4142 scenes</quote>). It <emphasis>might give the wrong results</emphasis> if
4143 used in programs that use multiple database connections (use
4144 <xref linkend="libpq-PQescapeByteaConn"/> in such cases).
4149 <varlistentry id="libpq-PQunescapeBytea">
4150 <term><function>PQunescapeBytea</function><indexterm><primary>PQunescapeBytea</primary></indexterm></term>
4154 Converts a string representation of binary data into binary data
4155 — the reverse of <xref linkend="libpq-PQescapeBytea"/>. This
4156 is needed when retrieving <type>bytea</type> data in text format,
4157 but not when retrieving it in binary format.
4160 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
4165 The <parameter>from</parameter> parameter points to a string
4166 such as might be returned by <xref linkend="libpq-PQgetvalue"/> when applied
4167 to a <type>bytea</type> column. <xref linkend="libpq-PQunescapeBytea"/>
4168 converts this string representation into its binary representation.
4169 It returns a pointer to a buffer allocated with
4170 <function>malloc()</function>, or <symbol>NULL</symbol> on error, and puts the size of
4171 the buffer in <parameter>to_length</parameter>. The result must be
4172 freed using <xref linkend="libpq-PQfreemem"/> when it is no longer needed.
4176 This conversion is not exactly the inverse of
4177 <xref linkend="libpq-PQescapeBytea"/>, because the string is not expected
4178 to be <quote>escaped</quote> when received from <xref linkend="libpq-PQgetvalue"/>.
4179 In particular this means there is no need for string quoting considerations,
4180 and so no need for a <structname>PGconn</structname> parameter.
4190 <sect1 id="libpq-async">
4191 <title>Asynchronous Command Processing</title>
4193 <indexterm zone="libpq-async">
4194 <primary>nonblocking connection</primary>
4198 The <xref linkend="libpq-PQexec"/> function is adequate for submitting
4199 commands in normal, synchronous applications. It has a few
4200 deficiencies, however, that can be of importance to some users:
4205 <xref linkend="libpq-PQexec"/> waits for the command to be completed.
4206 The application might have other work to do (such as maintaining a
4207 user interface), in which case it won't want to block waiting for
4214 Since the execution of the client application is suspended while it
4215 waits for the result, it is hard for the application to decide that
4216 it would like to try to cancel the ongoing command. (It can be done
4217 from a signal handler, but not otherwise.)
4223 <xref linkend="libpq-PQexec"/> can return only one
4224 <structname>PGresult</structname> structure. If the submitted command
4225 string contains multiple <acronym>SQL</acronym> commands, all but
4226 the last <structname>PGresult</structname> are discarded by
4227 <xref linkend="libpq-PQexec"/>.
4233 <xref linkend="libpq-PQexec"/> always collects the command's entire result,
4234 buffering it in a single <structname>PGresult</structname>. While
4235 this simplifies error-handling logic for the application, it can be
4236 impractical for results containing many rows.
4243 Applications that do not like these limitations can instead use the
4244 underlying functions that <xref linkend="libpq-PQexec"/> is built from:
4245 <xref linkend="libpq-PQsendQuery"/> and <xref linkend="libpq-PQgetResult"/>.
4247 <xref linkend="libpq-PQsendQueryParams"/>,
4248 <xref linkend="libpq-PQsendPrepare"/>,
4249 <xref linkend="libpq-PQsendQueryPrepared"/>,
4250 <xref linkend="libpq-PQsendDescribePrepared"/>, and
4251 <xref linkend="libpq-PQsendDescribePortal"/>,
4252 which can be used with <xref linkend="libpq-PQgetResult"/> to duplicate
4253 the functionality of
4254 <xref linkend="libpq-PQexecParams"/>,
4255 <xref linkend="libpq-PQprepare"/>,
4256 <xref linkend="libpq-PQexecPrepared"/>,
4257 <xref linkend="libpq-PQdescribePrepared"/>, and
4258 <xref linkend="libpq-PQdescribePortal"/>
4262 <varlistentry id="libpq-PQsendQuery">
4263 <term><function>PQsendQuery</function><indexterm><primary>PQsendQuery</primary></indexterm></term>
4267 Submits a command to the server without waiting for the result(s).
4268 1 is returned if the command was successfully dispatched and 0 if
4269 not (in which case, use <xref linkend="libpq-PQerrorMessage"/> to get more
4270 information about the failure).
4272 int PQsendQuery(PGconn *conn, const char *command);
4275 After successfully calling <xref linkend="libpq-PQsendQuery"/>, call
4276 <xref linkend="libpq-PQgetResult"/> one or more times to obtain the
4277 results. <xref linkend="libpq-PQsendQuery"/> cannot be called again
4278 (on the same connection) until <xref linkend="libpq-PQgetResult"/>
4279 has returned a null pointer, indicating that the command is done.
4284 <varlistentry id="libpq-PQsendQueryParams">
4285 <term><function>PQsendQueryParams</function><indexterm><primary>PQsendQueryParams</primary></indexterm></term>
4289 Submits a command and separate parameters to the server without
4290 waiting for the result(s).
4292 int PQsendQueryParams(PGconn *conn,
4293 const char *command,
4295 const Oid *paramTypes,
4296 const char * const *paramValues,
4297 const int *paramLengths,
4298 const int *paramFormats,
4302 This is equivalent to <xref linkend="libpq-PQsendQuery"/> except that
4303 query parameters can be specified separately from the query string.
4304 The function's parameters are handled identically to
4305 <xref linkend="libpq-PQexecParams"/>. Like
4306 <xref linkend="libpq-PQexecParams"/>, it will not work on 2.0-protocol
4307 connections, and it allows only one command in the query string.
4312 <varlistentry id="libpq-PQsendPrepare">
4313 <term><function>PQsendPrepare</function><indexterm><primary>PQsendPrepare</primary></indexterm></term>
4317 Sends a request to create a prepared statement with the given
4318 parameters, without waiting for completion.
4320 int PQsendPrepare(PGconn *conn,
4321 const char *stmtName,
4324 const Oid *paramTypes);
4327 This is an asynchronous version of <xref linkend="libpq-PQprepare"/>: it
4328 returns 1 if it was able to dispatch the request, and 0 if not.
4329 After a successful call, call <xref linkend="libpq-PQgetResult"/> to
4330 determine whether the server successfully created the prepared
4331 statement. The function's parameters are handled identically to
4332 <xref linkend="libpq-PQprepare"/>. Like
4333 <xref linkend="libpq-PQprepare"/>, it will not work on 2.0-protocol
4339 <varlistentry id="libpq-PQsendQueryPrepared">
4340 <term><function>PQsendQueryPrepared</function><indexterm><primary>PQsendQueryPrepared</primary></indexterm></term>
4344 Sends a request to execute a prepared statement with given
4345 parameters, without waiting for the result(s).
4347 int PQsendQueryPrepared(PGconn *conn,
4348 const char *stmtName,
4350 const char * const *paramValues,
4351 const int *paramLengths,
4352 const int *paramFormats,
4356 This is similar to <xref linkend="libpq-PQsendQueryParams"/>, but
4357 the command to be executed is specified by naming a
4358 previously-prepared statement, instead of giving a query string.
4359 The function's parameters are handled identically to
4360 <xref linkend="libpq-PQexecPrepared"/>. Like
4361 <xref linkend="libpq-PQexecPrepared"/>, it will not work on
4362 2.0-protocol connections.
4367 <varlistentry id="libpq-PQsendDescribePrepared">
4368 <term><function>PQsendDescribePrepared</function><indexterm><primary>PQsendDescribePrepared</primary></indexterm></term>
4372 Submits a request to obtain information about the specified
4373 prepared statement, without waiting for completion.
4375 int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
4378 This is an asynchronous version of <xref linkend="libpq-PQdescribePrepared"/>:
4379 it returns 1 if it was able to dispatch the request, and 0 if not.
4380 After a successful call, call <xref linkend="libpq-PQgetResult"/> to
4381 obtain the results. The function's parameters are handled
4382 identically to <xref linkend="libpq-PQdescribePrepared"/>. Like
4383 <xref linkend="libpq-PQdescribePrepared"/>, it will not work on
4384 2.0-protocol connections.
4389 <varlistentry id="libpq-PQsendDescribePortal">
4390 <term><function>PQsendDescribePortal</function><indexterm><primary>PQsendDescribePortal</primary></indexterm></term>
4394 Submits a request to obtain information about the specified
4395 portal, without waiting for completion.
4397 int PQsendDescribePortal(PGconn *conn, const char *portalName);
4400 This is an asynchronous version of <xref linkend="libpq-PQdescribePortal"/>:
4401 it returns 1 if it was able to dispatch the request, and 0 if not.
4402 After a successful call, call <xref linkend="libpq-PQgetResult"/> to
4403 obtain the results. The function's parameters are handled
4404 identically to <xref linkend="libpq-PQdescribePortal"/>. Like
4405 <xref linkend="libpq-PQdescribePortal"/>, it will not work on
4406 2.0-protocol connections.
4411 <varlistentry id="libpq-PQgetResult">
4412 <term><function>PQgetResult</function><indexterm><primary>PQgetResult</primary></indexterm></term>
4416 Waits for the next result from a prior
4417 <xref linkend="libpq-PQsendQuery"/>,
4418 <xref linkend="libpq-PQsendQueryParams"/>,
4419 <xref linkend="libpq-PQsendPrepare"/>,
4420 <xref linkend="libpq-PQsendQueryPrepared"/>,
4421 <xref linkend="libpq-PQsendDescribePrepared"/>, or
4422 <xref linkend="libpq-PQsendDescribePortal"/>
4423 call, and returns it.
4424 A null pointer is returned when the command is complete and there
4425 will be no more results.
4427 PGresult *PQgetResult(PGconn *conn);
4432 <xref linkend="libpq-PQgetResult"/> must be called repeatedly until
4433 it returns a null pointer, indicating that the command is done.
4434 (If called when no command is active,
4435 <xref linkend="libpq-PQgetResult"/> will just return a null pointer
4436 at once.) Each non-null result from
4437 <xref linkend="libpq-PQgetResult"/> should be processed using the
4438 same <structname>PGresult</structname> accessor functions previously
4439 described. Don't forget to free each result object with
4440 <xref linkend="libpq-PQclear"/> when done with it. Note that
4441 <xref linkend="libpq-PQgetResult"/> will block only if a command is
4442 active and the necessary response data has not yet been read by
4443 <xref linkend="libpq-PQconsumeInput"/>.
4448 Even when <xref linkend="libpq-PQresultStatus"/> indicates a fatal
4449 error, <xref linkend="libpq-PQgetResult"/> should be called until it
4450 returns a null pointer, to allow <application>libpq</application> to
4451 process the error information completely.
4460 Using <xref linkend="libpq-PQsendQuery"/> and
4461 <xref linkend="libpq-PQgetResult"/> solves one of
4462 <xref linkend="libpq-PQexec"/>'s problems: If a command string contains
4463 multiple <acronym>SQL</acronym> commands, the results of those commands
4464 can be obtained individually. (This allows a simple form of overlapped
4465 processing, by the way: the client can be handling the results of one
4466 command while the server is still working on later queries in the same
4471 Another frequently-desired feature that can be obtained with
4472 <xref linkend="libpq-PQsendQuery"/> and <xref linkend="libpq-PQgetResult"/>
4473 is retrieving large query results a row at a time. This is discussed
4474 in <xref linkend="libpq-single-row-mode"/>.
4478 By itself, calling <xref linkend="libpq-PQgetResult"/>
4479 will still cause the client to block until the server completes the
4480 next <acronym>SQL</acronym> command. This can be avoided by proper
4481 use of two more functions:
4484 <varlistentry id="libpq-PQconsumeInput">
4485 <term><function>PQconsumeInput</function><indexterm><primary>PQconsumeInput</primary></indexterm>
4490 If input is available from the server, consume it.
4492 int PQconsumeInput(PGconn *conn);
4497 <xref linkend="libpq-PQconsumeInput"/> normally returns 1 indicating
4498 <quote>no error</quote>, but returns 0 if there was some kind of
4499 trouble (in which case <xref linkend="libpq-PQerrorMessage"/> can be
4500 consulted). Note that the result does not say whether any input
4501 data was actually collected. After calling
4502 <xref linkend="libpq-PQconsumeInput"/>, the application can check
4503 <xref linkend="libpq-PQisBusy"/> and/or
4504 <function>PQnotifies</function> to see if their state has changed.
4508 <xref linkend="libpq-PQconsumeInput"/> can be called even if the
4509 application is not prepared to deal with a result or notification
4510 just yet. The function will read available data and save it in
4511 a buffer, thereby causing a <function>select()</function>
4512 read-ready indication to go away. The application can thus use
4513 <xref linkend="libpq-PQconsumeInput"/> to clear the
4514 <function>select()</function> condition immediately, and then
4515 examine the results at leisure.
4520 <varlistentry id="libpq-PQisBusy">
4521 <term><function>PQisBusy</function><indexterm><primary>PQisBusy</primary></indexterm></term>
4525 Returns 1 if a command is busy, that is,
4526 <xref linkend="libpq-PQgetResult"/> would block waiting for input.
4527 A 0 return indicates that <xref linkend="libpq-PQgetResult"/> can be
4528 called with assurance of not blocking.
4530 int PQisBusy(PGconn *conn);
4535 <xref linkend="libpq-PQisBusy"/> will not itself attempt to read data
4536 from the server; therefore <xref linkend="libpq-PQconsumeInput"/>
4537 must be invoked first, or the busy state will never end.
4545 A typical application using these functions will have a main loop that
4546 uses <function>select()</function> or <function>poll()</function> to wait for
4547 all the conditions that it must respond to. One of the conditions
4548 will be input available from the server, which in terms of
4549 <function>select()</function> means readable data on the file
4550 descriptor identified by <xref linkend="libpq-PQsocket"/>. When the main
4551 loop detects input ready, it should call
4552 <xref linkend="libpq-PQconsumeInput"/> to read the input. It can then
4553 call <xref linkend="libpq-PQisBusy"/>, followed by
4554 <xref linkend="libpq-PQgetResult"/> if <xref linkend="libpq-PQisBusy"/>
4555 returns false (0). It can also call <function>PQnotifies</function>
4556 to detect <command>NOTIFY</command> messages (see <xref
4557 linkend="libpq-notify"/>).
4562 <xref linkend="libpq-PQsendQuery"/>/<xref linkend="libpq-PQgetResult"/>
4563 can also attempt to cancel a command that is still being processed
4564 by the server; see <xref linkend="libpq-cancel"/>. But regardless of
4565 the return value of <xref linkend="libpq-PQcancel"/>, the application
4566 must continue with the normal result-reading sequence using
4567 <xref linkend="libpq-PQgetResult"/>. A successful cancellation will
4568 simply cause the command to terminate sooner than it would have
4573 By using the functions described above, it is possible to avoid
4574 blocking while waiting for input from the database server. However,
4575 it is still possible that the application will block waiting to send
4576 output to the server. This is relatively uncommon but can happen if
4577 very long SQL commands or data values are sent. (It is much more
4578 probable if the application sends data via <command>COPY IN</command>,
4579 however.) To prevent this possibility and achieve completely
4580 nonblocking database operation, the following additional functions
4584 <varlistentry id="libpq-PQsetnonblocking">
4585 <term><function>PQsetnonblocking</function><indexterm><primary>PQsetnonblocking</primary></indexterm></term>
4589 Sets the nonblocking status of the connection.
4591 int PQsetnonblocking(PGconn *conn, int arg);
4596 Sets the state of the connection to nonblocking if
4597 <parameter>arg</parameter> is 1, or blocking if
4598 <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
4602 In the nonblocking state, calls to
4603 <xref linkend="libpq-PQsendQuery"/>, <xref linkend="libpq-PQputline"/>,
4604 <xref linkend="libpq-PQputnbytes"/>, <xref linkend="libpq-PQputCopyData"/>,
4605 and <xref linkend="libpq-PQendcopy"/> will not block but instead return
4606 an error if they need to be called again.
4610 Note that <xref linkend="libpq-PQexec"/> does not honor nonblocking
4611 mode; if it is called, it will act in blocking fashion anyway.
4616 <varlistentry id="libpq-PQisnonblocking">
4617 <term><function>PQisnonblocking</function><indexterm><primary>PQisnonblocking</primary></indexterm></term>
4621 Returns the blocking status of the database connection.
4623 int PQisnonblocking(const PGconn *conn);
4628 Returns 1 if the connection is set to nonblocking mode and 0 if
4634 <varlistentry id="libpq-PQflush">
4635 <term><function>PQflush</function><indexterm><primary>PQflush</primary></indexterm></term>
4639 Attempts to flush any queued output data to the server. Returns
4640 0 if successful (or if the send queue is empty), -1 if it failed
4641 for some reason, or 1 if it was unable to send all the data in
4642 the send queue yet (this case can only occur if the connection
4645 int PQflush(PGconn *conn);
4654 After sending any command or data on a nonblocking connection, call
4655 <xref linkend="libpq-PQflush"/>. If it returns 1, wait for the socket
4656 to become read- or write-ready. If it becomes write-ready, call
4657 <xref linkend="libpq-PQflush"/> again. If it becomes read-ready, call
4658 <xref linkend="libpq-PQconsumeInput"/>, then call
4659 <xref linkend="libpq-PQflush"/> again. Repeat until
4660 <xref linkend="libpq-PQflush"/> returns 0. (It is necessary to check for
4661 read-ready and drain the input with <xref linkend="libpq-PQconsumeInput"/>,
4662 because the server can block trying to send us data, e.g. NOTICE
4663 messages, and won't read our data until we read its.) Once
4664 <xref linkend="libpq-PQflush"/> returns 0, wait for the socket to be
4665 read-ready and then read the response as described above.
4670 <sect1 id="libpq-single-row-mode">
4671 <title>Retrieving Query Results Row-by-Row</title>
4673 <indexterm zone="libpq-single-row-mode">
4674 <primary>libpq</primary>
4675 <secondary>single-row mode</secondary>
4679 Ordinarily, <application>libpq</application> collects a SQL command's
4680 entire result and returns it to the application as a single
4681 <structname>PGresult</structname>. This can be unworkable for commands
4682 that return a large number of rows. For such cases, applications can use
4683 <xref linkend="libpq-PQsendQuery"/> and <xref linkend="libpq-PQgetResult"/> in
4684 <firstterm>single-row mode</firstterm>. In this mode, the result row(s) are
4685 returned to the application one at a time, as they are received from the
4690 To enter single-row mode, call <xref linkend="libpq-PQsetSingleRowMode"/>
4691 immediately after a successful call of <xref linkend="libpq-PQsendQuery"/>
4692 (or a sibling function). This mode selection is effective only for the
4693 currently executing query. Then call <xref linkend="libpq-PQgetResult"/>
4694 repeatedly, until it returns null, as documented in <xref
4695 linkend="libpq-async"/>. If the query returns any rows, they are returned
4696 as individual <structname>PGresult</structname> objects, which look like
4697 normal query results except for having status code
4698 <literal>PGRES_SINGLE_TUPLE</literal> instead of
4699 <literal>PGRES_TUPLES_OK</literal>. After the last row, or immediately if
4700 the query returns zero rows, a zero-row object with status
4701 <literal>PGRES_TUPLES_OK</literal> is returned; this is the signal that no
4702 more rows will arrive. (But note that it is still necessary to continue
4703 calling <xref linkend="libpq-PQgetResult"/> until it returns null.) All of
4704 these <structname>PGresult</structname> objects will contain the same row
4705 description data (column names, types, etc) that an ordinary
4706 <structname>PGresult</structname> object for the query would have.
4707 Each object should be freed with <xref linkend="libpq-PQclear"/> as usual.
4712 <varlistentry id="libpq-PQsetSingleRowMode">
4713 <term><function>PQsetSingleRowMode</function><indexterm><primary>PQsetSingleRowMode</primary></indexterm></term>
4717 Select single-row mode for the currently-executing query.
4720 int PQsetSingleRowMode(PGconn *conn);
4725 This function can only be called immediately after
4726 <xref linkend="libpq-PQsendQuery"/> or one of its sibling functions,
4727 before any other operation on the connection such as
4728 <xref linkend="libpq-PQconsumeInput"/> or
4729 <xref linkend="libpq-PQgetResult"/>. If called at the correct time,
4730 the function activates single-row mode for the current query and
4731 returns 1. Otherwise the mode stays unchanged and the function
4732 returns 0. In any case, the mode reverts to normal after
4733 completion of the current query.
4742 While processing a query, the server may return some rows and then
4743 encounter an error, causing the query to be aborted. Ordinarily,
4744 <application>libpq</application> discards any such rows and reports only the
4745 error. But in single-row mode, those rows will have already been
4746 returned to the application. Hence, the application will see some
4747 <literal>PGRES_SINGLE_TUPLE</literal> <structname>PGresult</structname>
4748 objects followed by a <literal>PGRES_FATAL_ERROR</literal> object. For
4749 proper transactional behavior, the application must be designed to
4750 discard or undo whatever has been done with the previously-processed
4751 rows, if the query ultimately fails.
4757 <sect1 id="libpq-cancel">
4758 <title>Canceling Queries in Progress</title>
4760 <indexterm zone="libpq-cancel">
4761 <primary>canceling</primary>
4762 <secondary>SQL command</secondary>
4766 A client application can request cancellation of a command that is
4767 still being processed by the server, using the functions described in
4771 <varlistentry id="libpq-PQgetCancel">
4772 <term><function>PQgetCancel</function><indexterm><primary>PQgetCancel</primary></indexterm></term>
4776 Creates a data structure containing the information needed to cancel
4777 a command issued through a particular database connection.
4779 PGcancel *PQgetCancel(PGconn *conn);
4784 <xref linkend="libpq-PQgetCancel"/> creates a
4785 <structname>PGcancel</structname><indexterm><primary>PGcancel</primary></indexterm> object
4786 given a <structname>PGconn</structname> connection object. It will return
4787 <symbol>NULL</symbol> if the given <parameter>conn</parameter> is <symbol>NULL</symbol> or an invalid
4788 connection. The <structname>PGcancel</structname> object is an opaque
4789 structure that is not meant to be accessed directly by the
4790 application; it can only be passed to <xref linkend="libpq-PQcancel"/>
4791 or <xref linkend="libpq-PQfreeCancel"/>.
4796 <varlistentry id="libpq-PQfreeCancel">
4797 <term><function>PQfreeCancel</function><indexterm><primary>PQfreeCancel</primary></indexterm></term>
4801 Frees a data structure created by <xref linkend="libpq-PQgetCancel"/>.
4803 void PQfreeCancel(PGcancel *cancel);
4808 <xref linkend="libpq-PQfreeCancel"/> frees a data object previously created
4809 by <xref linkend="libpq-PQgetCancel"/>.
4814 <varlistentry id="libpq-PQcancel">
4815 <term><function>PQcancel</function><indexterm><primary>PQcancel</primary></indexterm></term>
4819 Requests that the server abandon processing of the current command.
4821 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
4826 The return value is 1 if the cancel request was successfully
4827 dispatched and 0 if not. If not, <parameter>errbuf</parameter> is filled
4828 with an explanatory error message. <parameter>errbuf</parameter>
4829 must be a char array of size <parameter>errbufsize</parameter> (the
4830 recommended size is 256 bytes).
4834 Successful dispatch is no guarantee that the request will have
4835 any effect, however. If the cancellation is effective, the current
4836 command will terminate early and return an error result. If the
4837 cancellation fails (say, because the server was already done
4838 processing the command), then there will be no visible result at
4843 <xref linkend="libpq-PQcancel"/> can safely be invoked from a signal
4844 handler, if the <parameter>errbuf</parameter> is a local variable in the
4845 signal handler. The <structname>PGcancel</structname> object is read-only
4846 as far as <xref linkend="libpq-PQcancel"/> is concerned, so it can
4847 also be invoked from a thread that is separate from the one
4848 manipulating the <structname>PGconn</structname> object.
4855 <varlistentry id="libpq-PQrequestCancel">
4856 <term><function>PQrequestCancel</function><indexterm><primary>PQrequestCancel</primary></indexterm></term>
4860 <xref linkend="libpq-PQrequestCancel"/> is a deprecated variant of
4861 <xref linkend="libpq-PQcancel"/>.
4863 int PQrequestCancel(PGconn *conn);
4868 Requests that the server abandon processing of the current
4869 command. It operates directly on the
4870 <structname>PGconn</structname> object, and in case of failure stores the
4871 error message in the <structname>PGconn</structname> object (whence it can
4872 be retrieved by <xref linkend="libpq-PQerrorMessage"/>). Although
4873 the functionality is the same, this approach creates hazards for
4874 multiple-thread programs and signal handlers, since it is possible
4875 that overwriting the <structname>PGconn</structname>'s error message will
4876 mess up the operation currently in progress on the connection.
4885 <sect1 id="libpq-fastpath">
4886 <title>The Fast-Path Interface</title>
4888 <indexterm zone="libpq-fastpath">
4889 <primary>fast path</primary>
4893 <productname>PostgreSQL</productname> provides a fast-path interface
4894 to send simple function calls to the server.
4899 This interface is somewhat obsolete, as one can achieve similar
4900 performance and greater functionality by setting up a prepared
4901 statement to define the function call. Then, executing the statement
4902 with binary transmission of parameters and results substitutes for a
4903 fast-path function call.
4908 The function <function id="libpq-PQfn">PQfn</function><indexterm><primary>PQfn</primary></indexterm>
4909 requests execution of a server function via the fast-path interface:
4911 PGresult *PQfn(PGconn *conn,
4916 const PQArgBlock *args,
4933 The <parameter>fnid</parameter> argument is the OID of the function to be
4934 executed. <parameter>args</parameter> and <parameter>nargs</parameter> define the
4935 parameters to be passed to the function; they must match the declared
4936 function argument list. When the <parameter>isint</parameter> field of a
4937 parameter structure is true, the <parameter>u.integer</parameter> value is sent
4938 to the server as an integer of the indicated length (this must be
4939 2 or 4 bytes); proper byte-swapping occurs. When <parameter>isint</parameter>
4940 is false, the indicated number of bytes at <parameter>*u.ptr</parameter> are
4941 sent with no processing; the data must be in the format expected by
4942 the server for binary transmission of the function's argument data
4943 type. (The declaration of <parameter>u.ptr</parameter> as being of
4944 type <type>int *</type> is historical; it would be better to consider
4945 it <type>void *</type>.)
4946 <parameter>result_buf</parameter> points to the buffer in which to place
4947 the function's return value. The caller must have allocated sufficient
4948 space to store the return value. (There is no check!) The actual result
4949 length in bytes will be returned in the integer pointed to by
4950 <parameter>result_len</parameter>. If a 2- or 4-byte integer result
4951 is expected, set <parameter>result_is_int</parameter> to 1, otherwise
4952 set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
4953 <application>libpq</application> to byte-swap the value if necessary, so that it
4954 is delivered as a proper <type>int</type> value for the client machine;
4955 note that a 4-byte integer is delivered into <parameter>*result_buf</parameter>
4956 for either allowed result size.
4957 When <parameter>result_is_int</parameter> is 0, the binary-format byte string
4958 sent by the server is returned unmodified. (In this case it's better
4959 to consider <parameter>result_buf</parameter> as being of
4960 type <type>void *</type>.)
4964 <function>PQfn</function> always returns a valid
4965 <structname>PGresult</structname> pointer. The result status should be
4966 checked before the result is used. The caller is responsible for
4967 freeing the <structname>PGresult</structname> with
4968 <xref linkend="libpq-PQclear"/> when it is no longer needed.
4972 Note that it is not possible to handle null arguments, null results,
4973 nor set-valued results when using this interface.
4978 <sect1 id="libpq-notify">
4979 <title>Asynchronous Notification</title>
4981 <indexterm zone="libpq-notify">
4982 <primary>NOTIFY</primary>
4983 <secondary>in libpq</secondary>
4987 <productname>PostgreSQL</productname> offers asynchronous notification
4988 via the <command>LISTEN</command> and <command>NOTIFY</command>
4989 commands. A client session registers its interest in a particular
4990 notification channel with the <command>LISTEN</command> command (and
4991 can stop listening with the <command>UNLISTEN</command> command). All
4992 sessions listening on a particular channel will be notified
4993 asynchronously when a <command>NOTIFY</command> command with that
4994 channel name is executed by any session. A <quote>payload</quote> string can
4995 be passed to communicate additional data to the listeners.
4999 <application>libpq</application> applications submit
5000 <command>LISTEN</command>, <command>UNLISTEN</command>,
5001 and <command>NOTIFY</command> commands as
5002 ordinary SQL commands. The arrival of <command>NOTIFY</command>
5003 messages can subsequently be detected by calling
5004 <function id="libpq-PQnotifies">PQnotifies</function>.<indexterm><primary>PQnotifies</primary></indexterm>
5008 The function <function>PQnotifies</function> returns the next notification
5009 from a list of unhandled notification messages received from the server.
5010 It returns a null pointer if there are no pending notifications. Once a
5011 notification is returned from <function>PQnotifies</function>, it is considered
5012 handled and will be removed from the list of notifications.
5015 PGnotify *PQnotifies(PGconn *conn);
5017 typedef struct pgNotify
5019 char *relname; /* notification channel name */
5020 int be_pid; /* process ID of notifying server process */
5021 char *extra; /* notification payload string */
5025 After processing a <structname>PGnotify</structname> object returned
5026 by <function>PQnotifies</function>, be sure to free it with
5027 <xref linkend="libpq-PQfreemem"/>. It is sufficient to free the
5028 <structname>PGnotify</structname> pointer; the
5029 <structfield>relname</structfield> and <structfield>extra</structfield>
5030 fields do not represent separate allocations. (The names of these fields
5031 are historical; in particular, channel names need not have anything to
5032 do with relation names.)
5036 <xref linkend="libpq-example-2"/> gives a sample program that illustrates
5037 the use of asynchronous notification.
5041 <function>PQnotifies</function> does not actually read data from the
5042 server; it just returns messages previously absorbed by another
5043 <application>libpq</application> function. In ancient releases of
5044 <application>libpq</application>, the only way to ensure timely receipt
5045 of <command>NOTIFY</command> messages was to constantly submit commands, even
5046 empty ones, and then check <function>PQnotifies</function> after each
5047 <xref linkend="libpq-PQexec"/>. While this still works, it is deprecated
5048 as a waste of processing power.
5052 A better way to check for <command>NOTIFY</command> messages when you have no
5053 useful commands to execute is to call
5054 <xref linkend="libpq-PQconsumeInput"/>, then check
5055 <function>PQnotifies</function>. You can use
5056 <function>select()</function> to wait for data to arrive from the
5057 server, thereby using no <acronym>CPU</acronym> power unless there is
5058 something to do. (See <xref linkend="libpq-PQsocket"/> to obtain the file
5059 descriptor number to use with <function>select()</function>.) Note that
5060 this will work OK whether you submit commands with
5061 <xref linkend="libpq-PQsendQuery"/>/<xref linkend="libpq-PQgetResult"/> or
5062 simply use <xref linkend="libpq-PQexec"/>. You should, however, remember
5063 to check <function>PQnotifies</function> after each
5064 <xref linkend="libpq-PQgetResult"/> or <xref linkend="libpq-PQexec"/>, to
5065 see if any notifications came in during the processing of the command.
5070 <sect1 id="libpq-copy">
5071 <title>Functions Associated with the <command>COPY</command> Command</title>
5073 <indexterm zone="libpq-copy">
5074 <primary>COPY</primary>
5075 <secondary>with libpq</secondary>
5079 The <command>COPY</command> command in
5080 <productname>PostgreSQL</productname> has options to read from or write
5081 to the network connection used by <application>libpq</application>.
5082 The functions described in this section allow applications to take
5083 advantage of this capability by supplying or consuming copied data.
5087 The overall process is that the application first issues the SQL
5088 <command>COPY</command> command via <xref linkend="libpq-PQexec"/> or one
5089 of the equivalent functions. The response to this (if there is no
5090 error in the command) will be a <structname>PGresult</structname> object bearing
5091 a status code of <literal>PGRES_COPY_OUT</literal> or
5092 <literal>PGRES_COPY_IN</literal> (depending on the specified copy
5093 direction). The application should then use the functions of this
5094 section to receive or transmit data rows. When the data transfer is
5095 complete, another <structname>PGresult</structname> object is returned to indicate
5096 success or failure of the transfer. Its status will be
5097 <literal>PGRES_COMMAND_OK</literal> for success or
5098 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
5099 At this point further SQL commands can be issued via
5100 <xref linkend="libpq-PQexec"/>. (It is not possible to execute other SQL
5101 commands using the same connection while the <command>COPY</command>
5102 operation is in progress.)
5106 If a <command>COPY</command> command is issued via
5107 <xref linkend="libpq-PQexec"/> in a string that could contain additional
5108 commands, the application must continue fetching results via
5109 <xref linkend="libpq-PQgetResult"/> after completing the <command>COPY</command>
5110 sequence. Only when <xref linkend="libpq-PQgetResult"/> returns
5111 <symbol>NULL</symbol> is it certain that the <xref linkend="libpq-PQexec"/>
5112 command string is done and it is safe to issue more commands.
5116 The functions of this section should be executed only after obtaining
5117 a result status of <literal>PGRES_COPY_OUT</literal> or
5118 <literal>PGRES_COPY_IN</literal> from <xref linkend="libpq-PQexec"/> or
5119 <xref linkend="libpq-PQgetResult"/>.
5123 A <structname>PGresult</structname> object bearing one of these status values
5124 carries some additional data about the <command>COPY</command> operation
5125 that is starting. This additional data is available using functions
5126 that are also used in connection with query results:
5129 <varlistentry id="libpq-PQnfields-1">
5130 <term><function>PQnfields</function><indexterm
5131 ><primary>PQnfields</primary><secondary>with COPY</secondary></indexterm></term>
5135 Returns the number of columns (fields) to be copied.
5140 <varlistentry id="libpq-PQbinaryTuples-1">
5141 <term><function>PQbinaryTuples</function><indexterm
5142 ><primary>PQbinaryTuples</primary><secondary>with COPY</secondary></indexterm></term>
5146 0 indicates the overall copy format is textual (rows separated by
5147 newlines, columns separated by separator characters, etc). 1
5148 indicates the overall copy format is binary. See <xref
5149 linkend="sql-copy"/> for more information.
5154 <varlistentry id="libpq-PQfformat-1">
5155 <term><function>PQfformat</function><indexterm
5156 ><primary>PQfformat</primary><secondary>with COPY</secondary></indexterm></term>
5160 Returns the format code (0 for text, 1 for binary) associated with
5161 each column of the copy operation. The per-column format codes
5162 will always be zero when the overall copy format is textual, but
5163 the binary format can support both text and binary columns.
5164 (However, as of the current implementation of <command>COPY</command>,
5165 only binary columns appear in a binary copy; so the per-column
5166 formats always match the overall format at present.)
5175 These additional data values are only available when using protocol
5176 3.0. When using protocol 2.0, all these functions will return 0.
5180 <sect2 id="libpq-copy-send">
5181 <title>Functions for Sending <command>COPY</command> Data</title>
5184 These functions are used to send data during <literal>COPY FROM
5185 STDIN</literal>. They will fail if called when the connection is not in
5186 <literal>COPY_IN</literal> state.
5190 <varlistentry id="libpq-PQputCopyData">
5191 <term><function>PQputCopyData</function><indexterm><primary>PQputCopyData</primary></indexterm></term>
5195 Sends data to the server during <literal>COPY_IN</literal> state.
5197 int PQputCopyData(PGconn *conn,
5204 Transmits the <command>COPY</command> data in the specified
5205 <parameter>buffer</parameter>, of length <parameter>nbytes</parameter>, to the server.
5206 The result is 1 if the data was queued, zero if it was not queued
5207 because of full buffers (this will only happen in nonblocking mode),
5208 or -1 if an error occurred.
5209 (Use <xref linkend="libpq-PQerrorMessage"/> to retrieve details if
5210 the return value is -1. If the value is zero, wait for write-ready
5215 The application can divide the <command>COPY</command> data stream
5216 into buffer loads of any convenient size. Buffer-load boundaries
5217 have no semantic significance when sending. The contents of the
5218 data stream must match the data format expected by the
5219 <command>COPY</command> command; see <xref linkend="sql-copy"/> for details.
5224 <varlistentry id="libpq-PQputCopyEnd">
5225 <term><function>PQputCopyEnd</function><indexterm><primary>PQputCopyEnd</primary></indexterm></term>
5229 Sends end-of-data indication to the server during <literal>COPY_IN</literal> state.
5231 int PQputCopyEnd(PGconn *conn,
5232 const char *errormsg);
5237 Ends the <literal>COPY_IN</literal> operation successfully if
5238 <parameter>errormsg</parameter> is <symbol>NULL</symbol>. If
5239 <parameter>errormsg</parameter> is not <symbol>NULL</symbol> then the
5240 <command>COPY</command> is forced to fail, with the string pointed to by
5241 <parameter>errormsg</parameter> used as the error message. (One should not
5242 assume that this exact error message will come back from the server,
5243 however, as the server might have already failed the
5244 <command>COPY</command> for its own reasons. Also note that the option
5245 to force failure does not work when using pre-3.0-protocol
5250 The result is 1 if the termination message was sent; or in
5251 nonblocking mode, this may only indicate that the termination
5252 message was successfully queued. (In nonblocking mode, to be
5253 certain that the data has been sent, you should next wait for
5254 write-ready and call <xref linkend="libpq-PQflush"/>, repeating until it
5255 returns zero.) Zero indicates that the function could not queue
5256 the termination message because of full buffers; this will only
5257 happen in nonblocking mode. (In this case, wait for
5258 write-ready and try the <xref linkend="libpq-PQputCopyEnd"/> call
5259 again.) If a hard error occurs, -1 is returned; you can use
5260 <xref linkend="libpq-PQerrorMessage"/> to retrieve details.
5264 After successfully calling <xref linkend="libpq-PQputCopyEnd"/>, call
5265 <xref linkend="libpq-PQgetResult"/> to obtain the final result status of the
5266 <command>COPY</command> command. One can wait for this result to be
5267 available in the usual way. Then return to normal operation.
5275 <sect2 id="libpq-copy-receive">
5276 <title>Functions for Receiving <command>COPY</command> Data</title>
5279 These functions are used to receive data during <literal>COPY TO
5280 STDOUT</literal>. They will fail if called when the connection is not in
5281 <literal>COPY_OUT</literal> state.
5285 <varlistentry id="libpq-PQgetCopyData">
5286 <term><function>PQgetCopyData</function><indexterm><primary>PQgetCopyData</primary></indexterm></term>
5290 Receives data from the server during <literal>COPY_OUT</literal> state.
5292 int PQgetCopyData(PGconn *conn,
5299 Attempts to obtain another row of data from the server during a
5300 <command>COPY</command>. Data is always returned one data row at
5301 a time; if only a partial row is available, it is not returned.
5302 Successful return of a data row involves allocating a chunk of
5303 memory to hold the data. The <parameter>buffer</parameter> parameter must
5304 be non-<symbol>NULL</symbol>. <parameter>*buffer</parameter> is set to
5305 point to the allocated memory, or to <symbol>NULL</symbol> in cases
5306 where no buffer is returned. A non-<symbol>NULL</symbol> result
5307 buffer should be freed using <xref linkend="libpq-PQfreemem"/> when no longer
5312 When a row is successfully returned, the return value is the number
5313 of data bytes in the row (this will always be greater than zero).
5314 The returned string is always null-terminated, though this is
5315 probably only useful for textual <command>COPY</command>. A result
5316 of zero indicates that the <command>COPY</command> is still in
5317 progress, but no row is yet available (this is only possible when
5318 <parameter>async</parameter> is true). A result of -1 indicates that the
5319 <command>COPY</command> is done. A result of -2 indicates that an
5320 error occurred (consult <xref linkend="libpq-PQerrorMessage"/> for the reason).
5324 When <parameter>async</parameter> is true (not zero),
5325 <xref linkend="libpq-PQgetCopyData"/> will not block waiting for input; it
5326 will return zero if the <command>COPY</command> is still in progress
5327 but no complete row is available. (In this case wait for read-ready
5328 and then call <xref linkend="libpq-PQconsumeInput"/> before calling
5329 <xref linkend="libpq-PQgetCopyData"/> again.) When <parameter>async</parameter> is
5330 false (zero), <xref linkend="libpq-PQgetCopyData"/> will block until data is
5331 available or the operation completes.
5335 After <xref linkend="libpq-PQgetCopyData"/> returns -1, call
5336 <xref linkend="libpq-PQgetResult"/> to obtain the final result status of the
5337 <command>COPY</command> command. One can wait for this result to be
5338 available in the usual way. Then return to normal operation.
5346 <sect2 id="libpq-copy-deprecated">
5347 <title>Obsolete Functions for <command>COPY</command></title>
5350 These functions represent older methods of handling <command>COPY</command>.
5351 Although they still work, they are deprecated due to poor error handling,
5352 inconvenient methods of detecting end-of-data, and lack of support for binary
5353 or nonblocking transfers.
5357 <varlistentry id="libpq-PQgetline">
5358 <term><function>PQgetline</function><indexterm><primary>PQgetline</primary></indexterm></term>
5362 Reads a newline-terminated line of characters (transmitted
5363 by the server) into a buffer string of size <parameter>length</parameter>.
5365 int PQgetline(PGconn *conn,
5372 This function copies up to <parameter>length</parameter>-1 characters into
5373 the buffer and converts the terminating newline into a zero byte.
5374 <xref linkend="libpq-PQgetline"/> returns <symbol>EOF</symbol> at the
5375 end of input, 0 if the entire line has been read, and 1 if the
5376 buffer is full but the terminating newline has not yet been read.
5379 Note that the application must check to see if a new line consists
5380 of the two characters <literal>\.</literal>, which indicates
5381 that the server has finished sending the results of the
5382 <command>COPY</command> command. If the application might receive
5383 lines that are more than <parameter>length</parameter>-1 characters long,
5384 care is needed to be sure it recognizes the <literal>\.</literal>
5385 line correctly (and does not, for example, mistake the end of a
5386 long data line for a terminator line).
5391 <varlistentry id="libpq-PQgetlineAsync">
5392 <term><function>PQgetlineAsync</function><indexterm><primary>PQgetlineAsync</primary></indexterm></term>
5396 Reads a row of <command>COPY</command> data (transmitted by the
5397 server) into a buffer without blocking.
5399 int PQgetlineAsync(PGconn *conn,
5406 This function is similar to <xref linkend="libpq-PQgetline"/>, but it can be used
5408 that must read <command>COPY</command> data asynchronously, that is, without blocking.
5409 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
5411 application should call <xref linkend="libpq-PQconsumeInput"/> and
5412 <xref linkend="libpq-PQgetlineAsync"/> until the
5413 end-of-data signal is detected.
5416 Unlike <xref linkend="libpq-PQgetline"/>, this function takes
5417 responsibility for detecting end-of-data.
5421 On each call, <xref linkend="libpq-PQgetlineAsync"/> will return data if a
5422 complete data row is available in <application>libpq</application>'s input buffer.
5423 Otherwise, no data is returned until the rest of the row arrives.
5424 The function returns -1 if the end-of-copy-data marker has been recognized,
5425 or 0 if no data is available, or a positive number giving the number of
5426 bytes of data returned. If -1 is returned, the caller must next call
5427 <xref linkend="libpq-PQendcopy"/>, and then return to normal processing.
5431 The data returned will not extend beyond a data-row boundary. If possible
5432 a whole row will be returned at one time. But if the buffer offered by
5433 the caller is too small to hold a row sent by the server, then a partial
5434 data row will be returned. With textual data this can be detected by testing
5435 whether the last returned byte is <literal>\n</literal> or not. (In a binary
5436 <command>COPY</command>, actual parsing of the <command>COPY</command> data format will be needed to make the
5437 equivalent determination.)
5438 The returned string is not null-terminated. (If you want to add a
5439 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
5440 than the room actually available.)
5445 <varlistentry id="libpq-PQputline">
5446 <term><function>PQputline</function><indexterm><primary>PQputline</primary></indexterm></term>
5450 Sends a null-terminated string to the server. Returns 0 if
5451 OK and <symbol>EOF</symbol> if unable to send the string.
5453 int PQputline(PGconn *conn,
5454 const char *string);
5459 The <command>COPY</command> data stream sent by a series of calls
5460 to <xref linkend="libpq-PQputline"/> has the same format as that
5461 returned by <xref linkend="libpq-PQgetlineAsync"/>, except that
5462 applications are not obliged to send exactly one data row per
5463 <xref linkend="libpq-PQputline"/> call; it is okay to send a partial
5464 line or multiple lines per call.
5469 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
5470 for the application to explicitly send the two characters
5471 <literal>\.</literal> as a final line to indicate to the server that it had
5472 finished sending <command>COPY</command> data. While this still works, it is deprecated and the
5473 special meaning of <literal>\.</literal> can be expected to be removed in a
5474 future release. It is sufficient to call <xref linkend="libpq-PQendcopy"/> after
5475 having sent the actual data.
5481 <varlistentry id="libpq-PQputnbytes">
5482 <term><function>PQputnbytes</function><indexterm><primary>PQputnbytes</primary></indexterm></term>
5486 Sends a non-null-terminated string to the server. Returns
5487 0 if OK and <symbol>EOF</symbol> if unable to send the string.
5489 int PQputnbytes(PGconn *conn,
5496 This is exactly like <xref linkend="libpq-PQputline"/>, except that the data
5497 buffer need not be null-terminated since the number of bytes to send is
5498 specified directly. Use this procedure when sending binary data.
5503 <varlistentry id="libpq-PQendcopy">
5504 <term><function>PQendcopy</function><indexterm><primary>PQendcopy</primary></indexterm></term>
5508 Synchronizes with the server.
5510 int PQendcopy(PGconn *conn);
5512 This function waits until the server has finished the copying.
5513 It should either be issued when the last string has been sent
5514 to the server using <xref linkend="libpq-PQputline"/> or when the
5515 last string has been received from the server using
5516 <function>PQgetline</function>. It must be issued or the server
5517 will get <quote>out of sync</quote> with the client. Upon return
5518 from this function, the server is ready to receive the next SQL
5519 command. The return value is 0 on successful completion,
5520 nonzero otherwise. (Use <xref linkend="libpq-PQerrorMessage"/> to
5521 retrieve details if the return value is nonzero.)
5525 When using <xref linkend="libpq-PQgetResult"/>, the application should
5526 respond to a <literal>PGRES_COPY_OUT</literal> result by executing
5527 <xref linkend="libpq-PQgetline"/> repeatedly, followed by
5528 <xref linkend="libpq-PQendcopy"/> after the terminator line is seen.
5529 It should then return to the <xref linkend="libpq-PQgetResult"/> loop
5530 until <xref linkend="libpq-PQgetResult"/> returns a null pointer.
5531 Similarly a <literal>PGRES_COPY_IN</literal> result is processed
5532 by a series of <xref linkend="libpq-PQputline"/> calls followed by
5533 <xref linkend="libpq-PQendcopy"/>, then return to the
5534 <xref linkend="libpq-PQgetResult"/> loop. This arrangement will
5535 ensure that a <command>COPY</command> command embedded in a series
5536 of <acronym>SQL</acronym> commands will be executed correctly.
5540 Older applications are likely to submit a <command>COPY</command>
5541 via <xref linkend="libpq-PQexec"/> and assume that the transaction
5542 is done after <xref linkend="libpq-PQendcopy"/>. This will work
5543 correctly only if the <command>COPY</command> is the only
5544 <acronym>SQL</acronym> command in the command string.
5554 <sect1 id="libpq-control">
5555 <title>Control Functions</title>
5558 These functions control miscellaneous details of <application>libpq</application>'s
5563 <varlistentry id="libpq-PQclientEncoding">
5564 <term><function>PQclientEncoding</function><indexterm><primary>PQclientEncoding</primary></indexterm></term>
5568 Returns the client encoding.
5570 int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
5573 Note that it returns the encoding ID, not a symbolic string
5574 such as <literal>EUC_JP</literal>. If unsuccessful, it returns -1.
5575 To convert an encoding ID to an encoding name, you
5579 char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
5585 <varlistentry id="libpq-PQsetClientEncoding">
5586 <term><function>PQsetClientEncoding</function><indexterm><primary>PQsetClientEncoding</primary></indexterm></term>
5590 Sets the client encoding.
5592 int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
5595 <replaceable>conn</replaceable> is a connection to the server,
5596 and <replaceable>encoding</replaceable> is the encoding you want to
5597 use. If the function successfully sets the encoding, it returns 0,
5598 otherwise -1. The current encoding for this connection can be
5599 determined by using <xref linkend="libpq-PQclientEncoding"/>.
5604 <varlistentry id="libpq-PQsetErrorVerbosity">
5605 <term><function>PQsetErrorVerbosity</function><indexterm><primary>PQsetErrorVerbosity</primary></indexterm></term>
5609 Determines the verbosity of messages returned by
5610 <xref linkend="libpq-PQerrorMessage"/> and <xref linkend="libpq-PQresultErrorMessage"/>.
5620 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
5623 <xref linkend="libpq-PQsetErrorVerbosity"/> sets the verbosity mode,
5624 returning the connection's previous setting.
5625 In <firstterm>TERSE</firstterm> mode, returned messages include
5626 severity, primary text, and position only; this will normally fit on a
5627 single line. The <firstterm>DEFAULT</firstterm> mode produces messages
5628 that include the above plus any detail, hint, or context fields (these
5629 might span multiple lines). The <firstterm>VERBOSE</firstterm> mode
5630 includes all available fields. The <firstterm>SQLSTATE</firstterm>
5631 mode includes only the error severity and the <symbol>SQLSTATE</symbol>
5632 error code, if one is available (if not, the output is like
5633 <firstterm>TERSE</firstterm> mode).
5637 Changing the verbosity setting does not affect the messages available
5638 from already-existing <structname>PGresult</structname> objects, only
5639 subsequently-created ones.
5640 (But see <xref linkend="libpq-PQresultVerboseErrorMessage"/> if you
5641 want to print a previous error with a different verbosity.)
5646 <varlistentry id="libpq-PQsetErrorContextVisibility">
5647 <term><function>PQsetErrorContextVisibility</function><indexterm><primary>PQsetErrorContextVisibility</primary></indexterm></term>
5651 Determines the handling of <literal>CONTEXT</literal> fields in messages
5652 returned by <xref linkend="libpq-PQerrorMessage"/>
5653 and <xref linkend="libpq-PQresultErrorMessage"/>.
5657 PQSHOW_CONTEXT_NEVER,
5658 PQSHOW_CONTEXT_ERRORS,
5659 PQSHOW_CONTEXT_ALWAYS
5660 } PGContextVisibility;
5662 PGContextVisibility PQsetErrorContextVisibility(PGconn *conn, PGContextVisibility show_context);
5665 <xref linkend="libpq-PQsetErrorContextVisibility"/> sets the context display mode,
5666 returning the connection's previous setting. This mode controls
5667 whether the <literal>CONTEXT</literal> field is included in messages.
5668 The <firstterm>NEVER</firstterm> mode
5669 never includes <literal>CONTEXT</literal>, while <firstterm>ALWAYS</firstterm> always
5670 includes it if available. In <firstterm>ERRORS</firstterm> mode (the
5671 default), <literal>CONTEXT</literal> fields are included only in error
5672 messages, not in notices and warnings.
5673 (However, if the verbosity setting is <firstterm>TERSE</firstterm>
5674 or <firstterm>SQLSTATE</firstterm>, <literal>CONTEXT</literal> fields
5675 are omitted regardless of the context display mode.)
5679 Changing this mode does not
5680 affect the messages available from
5681 already-existing <structname>PGresult</structname> objects, only
5682 subsequently-created ones.
5683 (But see <xref linkend="libpq-PQresultVerboseErrorMessage"/> if you
5684 want to print a previous error with a different display mode.)
5689 <varlistentry id="libpq-PQtrace">
5690 <term><function>PQtrace</function><indexterm><primary>PQtrace</primary></indexterm></term>
5694 Enables tracing of the client/server communication to a debugging file stream.
5696 void PQtrace(PGconn *conn, FILE *stream);
5702 On Windows, if the <application>libpq</application> library and an application are
5703 compiled with different flags, this function call will crash the
5704 application because the internal representation of the <literal>FILE</literal>
5705 pointers differ. Specifically, multithreaded/single-threaded,
5706 release/debug, and static/dynamic flags should be the same for the
5707 library and all applications using that library.
5714 <varlistentry id="libpq-PQuntrace">
5715 <term><function>PQuntrace</function><indexterm><primary>PQuntrace</primary></indexterm></term>
5719 Disables tracing started by <xref linkend="libpq-PQtrace"/>.
5721 void PQuntrace(PGconn *conn);
5730 <sect1 id="libpq-misc">
5731 <title>Miscellaneous Functions</title>
5734 As always, there are some functions that just don't fit anywhere.
5738 <varlistentry id="libpq-PQfreemem">
5739 <term><function>PQfreemem</function><indexterm><primary>PQfreemem</primary></indexterm></term>
5743 Frees memory allocated by <application>libpq</application>.
5745 void PQfreemem(void *ptr);
5750 Frees memory allocated by <application>libpq</application>, particularly
5751 <xref linkend="libpq-PQescapeByteaConn"/>,
5752 <xref linkend="libpq-PQescapeBytea"/>,
5753 <xref linkend="libpq-PQunescapeBytea"/>,
5754 and <function>PQnotifies</function>.
5755 It is particularly important that this function, rather than
5756 <function>free()</function>, be used on Microsoft Windows. This is because
5757 allocating memory in a DLL and releasing it in the application works
5758 only if multithreaded/single-threaded, release/debug, and static/dynamic
5759 flags are the same for the DLL and the application. On non-Microsoft
5760 Windows platforms, this function is the same as the standard library
5761 function <function>free()</function>.
5766 <varlistentry id="libpq-PQconninfoFree">
5767 <term><function>PQconninfoFree</function><indexterm><primary>PQconninfoFree</primary></indexterm></term>
5771 Frees the data structures allocated by
5772 <xref linkend="libpq-PQconndefaults"/> or <xref linkend="libpq-PQconninfoParse"/>.
5774 void PQconninfoFree(PQconninfoOption *connOptions);
5779 A simple <xref linkend="libpq-PQfreemem"/> will not do for this, since
5780 the array contains references to subsidiary strings.
5785 <varlistentry id="libpq-PQencryptPasswordConn">
5786 <term><function>PQencryptPasswordConn</function><indexterm><primary>PQencryptPasswordConn</primary></indexterm></term>
5790 Prepares the encrypted form of a <productname>PostgreSQL</productname> password.
5792 char *PQencryptPasswordConn(PGconn *conn, const char *passwd, const char *user, const char *algorithm);
5794 This function is intended to be used by client applications that
5795 wish to send commands like <literal>ALTER USER joe PASSWORD
5796 'pwd'</literal>. It is good practice not to send the original cleartext
5797 password in such a command, because it might be exposed in command
5798 logs, activity displays, and so on. Instead, use this function to
5799 convert the password to encrypted form before it is sent.
5803 The <parameter>passwd</parameter> and <parameter>user</parameter> arguments
5804 are the cleartext password, and the SQL name of the user it is for.
5805 <parameter>algorithm</parameter> specifies the encryption algorithm
5806 to use to encrypt the password. Currently supported algorithms are
5807 <literal>md5</literal> and <literal>scram-sha-256</literal> (<literal>on</literal> and
5808 <literal>off</literal> are also accepted as aliases for <literal>md5</literal>, for
5809 compatibility with older server versions). Note that support for
5810 <literal>scram-sha-256</literal> was introduced in <productname>PostgreSQL</productname>
5811 version 10, and will not work correctly with older server versions. If
5812 <parameter>algorithm</parameter> is <symbol>NULL</symbol>, this function will query
5813 the server for the current value of the
5814 <xref linkend="guc-password-encryption"/> setting. That can block, and
5815 will fail if the current transaction is aborted, or if the connection
5816 is busy executing another query. If you wish to use the default
5817 algorithm for the server but want to avoid blocking, query
5818 <varname>password_encryption</varname> yourself before calling
5819 <xref linkend="libpq-PQencryptPasswordConn"/>, and pass that value as the
5820 <parameter>algorithm</parameter>.
5824 The return value is a string allocated by <function>malloc</function>.
5825 The caller can assume the string doesn't contain any special characters
5826 that would require escaping. Use <xref linkend="libpq-PQfreemem"/> to free the
5827 result when done with it. On error, returns <symbol>NULL</symbol>, and
5828 a suitable message is stored in the connection object.
5834 <varlistentry id="libpq-PQencryptPassword">
5835 <term><function>PQencryptPassword</function><indexterm><primary>PQencryptPassword</primary></indexterm></term>
5839 Prepares the md5-encrypted form of a <productname>PostgreSQL</productname> password.
5841 char *PQencryptPassword(const char *passwd, const char *user);
5843 <xref linkend="libpq-PQencryptPassword"/> is an older, deprecated version of
5844 <xref linkend="libpq-PQencryptPasswordConn"/>. The difference is that
5845 <xref linkend="libpq-PQencryptPassword"/> does not
5846 require a connection object, and <literal>md5</literal> is always used as the
5847 encryption algorithm.
5852 <varlistentry id="libpq-PQmakeEmptyPGresult">
5853 <term><function>PQmakeEmptyPGresult</function><indexterm><primary>PQmakeEmptyPGresult</primary></indexterm></term>
5857 Constructs an empty <structname>PGresult</structname> object with the given status.
5859 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
5864 This is <application>libpq</application>'s internal function to allocate and
5865 initialize an empty <structname>PGresult</structname> object. This
5866 function returns <symbol>NULL</symbol> if memory could not be allocated. It is
5867 exported because some applications find it useful to generate result
5868 objects (particularly objects with error status) themselves. If
5869 <parameter>conn</parameter> is not null and <parameter>status</parameter>
5870 indicates an error, the current error message of the specified
5871 connection is copied into the <structname>PGresult</structname>.
5872 Also, if <parameter>conn</parameter> is not null, any event procedures
5873 registered in the connection are copied into the
5874 <structname>PGresult</structname>. (They do not get
5875 <literal>PGEVT_RESULTCREATE</literal> calls, but see
5876 <xref linkend="libpq-PQfireResultCreateEvents"/>.)
5877 Note that <xref linkend="libpq-PQclear"/> should eventually be called
5878 on the object, just as with a <structname>PGresult</structname>
5879 returned by <application>libpq</application> itself.
5884 <varlistentry id="libpq-PQfireResultCreateEvents">
5885 <term><function>PQfireResultCreateEvents</function><indexterm><primary>PQfireResultCreateEvents</primary></indexterm></term>
5888 Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
5889 linkend="libpq-events"/>) for each event procedure registered in the
5890 <structname>PGresult</structname> object. Returns non-zero for success,
5891 zero if any event procedure fails.
5894 int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
5899 The <literal>conn</literal> argument is passed through to event procedures
5900 but not used directly. It can be <symbol>NULL</symbol> if the event
5901 procedures won't use it.
5905 Event procedures that have already received a
5906 <literal>PGEVT_RESULTCREATE</literal> or <literal>PGEVT_RESULTCOPY</literal> event
5907 for this object are not fired again.
5911 The main reason that this function is separate from
5912 <xref linkend="libpq-PQmakeEmptyPGresult"/> is that it is often appropriate
5913 to create a <structname>PGresult</structname> and fill it with data
5914 before invoking the event procedures.
5919 <varlistentry id="libpq-PQcopyResult">
5920 <term><function>PQcopyResult</function><indexterm><primary>PQcopyResult</primary></indexterm></term>
5924 Makes a copy of a <structname>PGresult</structname> object. The copy is
5925 not linked to the source result in any way and
5926 <xref linkend="libpq-PQclear"/> must be called when the copy is no longer
5927 needed. If the function fails, <symbol>NULL</symbol> is returned.
5930 PGresult *PQcopyResult(const PGresult *src, int flags);
5935 This is not intended to make an exact copy. The returned result is
5936 always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
5937 copy any error message in the source. (It does copy the command status
5938 string, however.) The <parameter>flags</parameter> argument determines
5939 what else is copied. It is a bitwise OR of several flags.
5940 <literal>PG_COPYRES_ATTRS</literal> specifies copying the source
5941 result's attributes (column definitions).
5942 <literal>PG_COPYRES_TUPLES</literal> specifies copying the source
5943 result's tuples. (This implies copying the attributes, too.)
5944 <literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
5945 copying the source result's notify hooks.
5946 <literal>PG_COPYRES_EVENTS</literal> specifies copying the source
5947 result's events. (But any instance data associated with the source
5953 <varlistentry id="libpq-PQsetResultAttrs">
5954 <term><function>PQsetResultAttrs</function><indexterm><primary>PQsetResultAttrs</primary></indexterm></term>
5958 Sets the attributes of a <structname>PGresult</structname> object.
5960 int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
5965 The provided <parameter>attDescs</parameter> are copied into the result.
5966 If the <parameter>attDescs</parameter> pointer is <symbol>NULL</symbol> or
5967 <parameter>numAttributes</parameter> is less than one, the request is
5968 ignored and the function succeeds. If <parameter>res</parameter>
5969 already contains attributes, the function will fail. If the function
5970 fails, the return value is zero. If the function succeeds, the return
5976 <varlistentry id="libpq-PQsetvalue">
5977 <term><function>PQsetvalue</function><indexterm><primary>PQsetvalue</primary></indexterm></term>
5981 Sets a tuple field value of a <structname>PGresult</structname> object.
5983 int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
5988 The function will automatically grow the result's internal tuples array
5989 as needed. However, the <parameter>tup_num</parameter> argument must be
5990 less than or equal to <xref linkend="libpq-PQntuples"/>, meaning this
5991 function can only grow the tuples array one tuple at a time. But any
5992 field of any existing tuple can be modified in any order. If a value at
5993 <parameter>field_num</parameter> already exists, it will be overwritten.
5994 If <parameter>len</parameter> is -1 or
5995 <parameter>value</parameter> is <symbol>NULL</symbol>, the field value
5996 will be set to an SQL null value. The
5997 <parameter>value</parameter> is copied into the result's private storage,
5998 thus is no longer needed after the function
5999 returns. If the function fails, the return value is zero. If the
6000 function succeeds, the return value is non-zero.
6005 <varlistentry id="libpq-PQresultAlloc">
6006 <term><function>PQresultAlloc</function><indexterm><primary>PQresultAlloc</primary></indexterm></term>
6010 Allocate subsidiary storage for a <structname>PGresult</structname> object.
6012 void *PQresultAlloc(PGresult *res, size_t nBytes);
6017 Any memory allocated with this function will be freed when
6018 <parameter>res</parameter> is cleared. If the function fails,
6019 the return value is <symbol>NULL</symbol>. The result is
6020 guaranteed to be adequately aligned for any type of data,
6021 just as for <function>malloc</function>.
6026 <varlistentry id="libpq-PQresultMemorySize">
6027 <term><function>PQresultMemorySize</function><indexterm><primary>PQresultMemorySize</primary></indexterm></term>
6031 Retrieves the number of bytes allocated for
6032 a <structname>PGresult</structname> object.
6034 size_t PQresultMemorySize(const PGresult *res);
6039 This value is the sum of all <function>malloc</function> requests
6040 associated with the <structname>PGresult</structname> object, that is,
6041 all the space that will be freed by <xref linkend="libpq-PQclear"/>.
6042 This information can be useful for managing memory consumption.
6047 <varlistentry id="libpq-PQlibVersion">
6048 <term><function>PQlibVersion</function><indexterm
6049 ><primary>PQlibVersion</primary><seealso>PQserverVersion</seealso></indexterm></term>
6053 Return the version of <productname>libpq</productname> that is being used.
6055 int PQlibVersion(void);
6060 The result of this function can be used to determine, at
6061 run time, whether specific functionality is available in the currently
6062 loaded version of libpq. The function can be used, for example,
6063 to determine which connection options are available in
6064 <xref linkend="libpq-PQconnectdb"/>.
6068 The result is formed by multiplying the library's major version
6069 number by 10000 and adding the minor version number. For example,
6070 version 10.1 will be returned as 100001, and version 11.0 will be
6075 Prior to major version 10, <productname>PostgreSQL</productname> used
6076 three-part version numbers in which the first two parts together
6077 represented the major version. For those
6078 versions, <xref linkend="libpq-PQlibVersion"/> uses two digits for each
6079 part; for example version 9.1.5 will be returned as 90105, and
6080 version 9.2.0 will be returned as 90200.
6084 Therefore, for purposes of determining feature compatibility,
6085 applications should divide the result of <xref linkend="libpq-PQlibVersion"/>
6086 by 100 not 10000 to determine a logical major version number.
6087 In all release series, only the last two digits differ between
6088 minor releases (bug-fix releases).
6093 This function appeared in <productname>PostgreSQL</productname> version 9.1, so
6094 it cannot be used to detect required functionality in earlier
6095 versions, since calling it will create a link dependency
6096 on version 9.1 or later.
6106 <sect1 id="libpq-notice-processing">
6107 <title>Notice Processing</title>
6109 <indexterm zone="libpq-notice-processing">
6110 <primary>notice processing</primary>
6111 <secondary>in libpq</secondary>
6115 Notice and warning messages generated by the server are not returned
6116 by the query execution functions, since they do not imply failure of
6117 the query. Instead they are passed to a notice handling function, and
6118 execution continues normally after the handler returns. The default
6119 notice handling function prints the message on
6120 <filename>stderr</filename>, but the application can override this
6121 behavior by supplying its own handling function.
6125 For historical reasons, there are two levels of notice handling, called
6126 the notice receiver and notice processor. The default behavior is for
6127 the notice receiver to format the notice and pass a string to the notice
6128 processor for printing. However, an application that chooses to provide
6129 its own notice receiver will typically ignore the notice processor
6130 layer and just do all the work in the notice receiver.
6134 The function <function id="libpq-PQsetNoticeReceiver">PQsetNoticeReceiver</function>
6135 <indexterm><primary>notice receiver</primary></indexterm>
6136 <indexterm><primary>PQsetNoticeReceiver</primary></indexterm> sets or
6137 examines the current notice receiver for a connection object.
6138 Similarly, <function id="libpq-PQsetNoticeProcessor">PQsetNoticeProcessor</function>
6139 <indexterm><primary>notice processor</primary></indexterm>
6140 <indexterm><primary>PQsetNoticeProcessor</primary></indexterm> sets or
6141 examines the current notice processor.
6144 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
6147 PQsetNoticeReceiver(PGconn *conn,
6148 PQnoticeReceiver proc,
6151 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
6154 PQsetNoticeProcessor(PGconn *conn,
6155 PQnoticeProcessor proc,
6159 Each of these functions returns the previous notice receiver or
6160 processor function pointer, and sets the new value. If you supply a
6161 null function pointer, no action is taken, but the current pointer is
6166 When a notice or warning message is received from the server, or
6167 generated internally by <application>libpq</application>, the notice
6168 receiver function is called. It is passed the message in the form of
6169 a <symbol>PGRES_NONFATAL_ERROR</symbol>
6170 <structname>PGresult</structname>. (This allows the receiver to extract
6171 individual fields using <xref linkend="libpq-PQresultErrorField"/>, or obtain a
6172 complete preformatted message using <xref linkend="libpq-PQresultErrorMessage"/>
6173 or <xref linkend="libpq-PQresultVerboseErrorMessage"/>.) The same
6174 void pointer passed to <function>PQsetNoticeReceiver</function> is also
6175 passed. (This pointer can be used to access application-specific state
6180 The default notice receiver simply extracts the message (using
6181 <xref linkend="libpq-PQresultErrorMessage"/>) and passes it to the notice
6186 The notice processor is responsible for handling a notice or warning
6187 message given in text form. It is passed the string text of the message
6188 (including a trailing newline), plus a void pointer that is the same
6189 one passed to <function>PQsetNoticeProcessor</function>. (This pointer
6190 can be used to access application-specific state if needed.)
6194 The default notice processor is simply:
6197 defaultNoticeProcessor(void *arg, const char *message)
6199 fprintf(stderr, "%s", message);
6205 Once you have set a notice receiver or processor, you should expect
6206 that that function could be called as long as either the
6207 <structname>PGconn</structname> object or <structname>PGresult</structname> objects made
6208 from it exist. At creation of a <structname>PGresult</structname>, the
6209 <structname>PGconn</structname>'s current notice handling pointers are copied
6210 into the <structname>PGresult</structname> for possible use by functions like
6211 <xref linkend="libpq-PQgetvalue"/>.
6216 <sect1 id="libpq-events">
6217 <title>Event System</title>
6220 <application>libpq</application>'s event system is designed to notify
6221 registered event handlers about interesting
6222 <application>libpq</application> events, such as the creation or
6223 destruction of <structname>PGconn</structname> and
6224 <structname>PGresult</structname> objects. A principal use case is that
6225 this allows applications to associate their own data with a
6226 <structname>PGconn</structname> or <structname>PGresult</structname>
6227 and ensure that that data is freed at an appropriate time.
6231 Each registered event handler is associated with two pieces of data,
6232 known to <application>libpq</application> only as opaque <literal>void *</literal>
6233 pointers. There is a <firstterm>passthrough</firstterm> pointer that is provided
6234 by the application when the event handler is registered with a
6235 <structname>PGconn</structname>. The passthrough pointer never changes for the
6236 life of the <structname>PGconn</structname> and all <structname>PGresult</structname>s
6237 generated from it; so if used, it must point to long-lived data.
6238 In addition there is an <firstterm>instance data</firstterm> pointer, which starts
6239 out <symbol>NULL</symbol> in every <structname>PGconn</structname> and <structname>PGresult</structname>.
6240 This pointer can be manipulated using the
6241 <xref linkend="libpq-PQinstanceData"/>,
6242 <xref linkend="libpq-PQsetInstanceData"/>,
6243 <xref linkend="libpq-PQresultInstanceData"/> and
6244 <function>PQsetResultInstanceData</function> functions. Note that
6245 unlike the passthrough pointer, instance data of a <structname>PGconn</structname>
6246 is not automatically inherited by <structname>PGresult</structname>s created from
6247 it. <application>libpq</application> does not know what passthrough
6248 and instance data pointers point to (if anything) and will never attempt
6249 to free them — that is the responsibility of the event handler.
6252 <sect2 id="libpq-events-types">
6253 <title>Event Types</title>
6256 The enum <literal>PGEventId</literal> names the types of events handled by
6257 the event system. All its values have names beginning with
6258 <literal>PGEVT</literal>. For each event type, there is a corresponding
6259 event info structure that carries the parameters passed to the event
6260 handlers. The event types are:
6264 <varlistentry id="libpq-pgevt-register">
6265 <term><literal>PGEVT_REGISTER</literal></term>
6268 The register event occurs when <xref linkend="libpq-PQregisterEventProc"/>
6269 is called. It is the ideal time to initialize any
6270 <literal>instanceData</literal> an event procedure may need. Only one
6271 register event will be fired per event handler per connection. If the
6272 event procedure fails, the registration is aborted.
6281 When a <literal>PGEVT_REGISTER</literal> event is received, the
6282 <parameter>evtInfo</parameter> pointer should be cast to a
6283 <structname>PGEventRegister *</structname>. This structure contains a
6284 <structname>PGconn</structname> that should be in the
6285 <literal>CONNECTION_OK</literal> status; guaranteed if one calls
6286 <xref linkend="libpq-PQregisterEventProc"/> right after obtaining a good
6287 <structname>PGconn</structname>. When returning a failure code, all
6288 cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
6294 <varlistentry id="libpq-pgevt-connreset">
6295 <term><literal>PGEVT_CONNRESET</literal></term>
6298 The connection reset event is fired on completion of
6299 <xref linkend="libpq-PQreset"/> or <function>PQresetPoll</function>. In
6300 both cases, the event is only fired if the reset was successful. If
6301 the event procedure fails, the entire connection reset will fail; the
6302 <structname>PGconn</structname> is put into
6303 <literal>CONNECTION_BAD</literal> status and
6304 <function>PQresetPoll</function> will return
6305 <literal>PGRES_POLLING_FAILED</literal>.
6314 When a <literal>PGEVT_CONNRESET</literal> event is received, the
6315 <parameter>evtInfo</parameter> pointer should be cast to a
6316 <structname>PGEventConnReset *</structname>. Although the contained
6317 <structname>PGconn</structname> was just reset, all event data remains
6318 unchanged. This event should be used to reset/reload/requery any
6319 associated <literal>instanceData</literal>. Note that even if the
6320 event procedure fails to process <literal>PGEVT_CONNRESET</literal>, it will
6321 still receive a <literal>PGEVT_CONNDESTROY</literal> event when the connection
6327 <varlistentry id="libpq-pgevt-conndestroy">
6328 <term><literal>PGEVT_CONNDESTROY</literal></term>
6331 The connection destroy event is fired in response to
6332 <xref linkend="libpq-PQfinish"/>. It is the event procedure's
6333 responsibility to properly clean up its event data as libpq has no
6334 ability to manage this memory. Failure to clean up will lead
6341 } PGEventConnDestroy;
6344 When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
6345 <parameter>evtInfo</parameter> pointer should be cast to a
6346 <structname>PGEventConnDestroy *</structname>. This event is fired
6347 prior to <xref linkend="libpq-PQfinish"/> performing any other cleanup.
6348 The return value of the event procedure is ignored since there is no
6349 way of indicating a failure from <xref linkend="libpq-PQfinish"/>. Also,
6350 an event procedure failure should not abort the process of cleaning up
6356 <varlistentry id="libpq-pgevt-resultcreate">
6357 <term><literal>PGEVT_RESULTCREATE</literal></term>
6360 The result creation event is fired in response to any query execution
6361 function that generates a result, including
6362 <xref linkend="libpq-PQgetResult"/>. This event will only be fired after
6363 the result has been created successfully.
6370 } PGEventResultCreate;
6373 When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
6374 <parameter>evtInfo</parameter> pointer should be cast to a
6375 <structname>PGEventResultCreate *</structname>. The
6376 <parameter>conn</parameter> is the connection used to generate the
6377 result. This is the ideal place to initialize any
6378 <literal>instanceData</literal> that needs to be associated with the
6379 result. If the event procedure fails, the result will be cleared and
6380 the failure will be propagated. The event procedure must not try to
6381 <xref linkend="libpq-PQclear"/> the result object for itself. When returning a
6382 failure code, all cleanup must be performed as no
6383 <literal>PGEVT_RESULTDESTROY</literal> event will be sent.
6388 <varlistentry id="libpq-pgevt-resultcopy">
6389 <term><literal>PGEVT_RESULTCOPY</literal></term>
6392 The result copy event is fired in response to
6393 <xref linkend="libpq-PQcopyResult"/>. This event will only be fired after
6394 the copy is complete. Only event procedures that have
6395 successfully handled the <literal>PGEVT_RESULTCREATE</literal>
6396 or <literal>PGEVT_RESULTCOPY</literal> event for the source result
6397 will receive <literal>PGEVT_RESULTCOPY</literal> events.
6402 const PGresult *src;
6404 } PGEventResultCopy;
6407 When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
6408 <parameter>evtInfo</parameter> pointer should be cast to a
6409 <structname>PGEventResultCopy *</structname>. The
6410 <parameter>src</parameter> result is what was copied while the
6411 <parameter>dest</parameter> result is the copy destination. This event
6412 can be used to provide a deep copy of <literal>instanceData</literal>,
6413 since <literal>PQcopyResult</literal> cannot do that. If the event
6414 procedure fails, the entire copy operation will fail and the
6415 <parameter>dest</parameter> result will be cleared. When returning a
6416 failure code, all cleanup must be performed as no
6417 <literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
6423 <varlistentry id="libpq-pgevt-resultdestroy">
6424 <term><literal>PGEVT_RESULTDESTROY</literal></term>
6427 The result destroy event is fired in response to a
6428 <xref linkend="libpq-PQclear"/>. It is the event procedure's
6429 responsibility to properly clean up its event data as libpq has no
6430 ability to manage this memory. Failure to clean up will lead
6437 } PGEventResultDestroy;
6440 When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
6441 <parameter>evtInfo</parameter> pointer should be cast to a
6442 <structname>PGEventResultDestroy *</structname>. This event is fired
6443 prior to <xref linkend="libpq-PQclear"/> performing any other cleanup.
6444 The return value of the event procedure is ignored since there is no
6445 way of indicating a failure from <xref linkend="libpq-PQclear"/>. Also,
6446 an event procedure failure should not abort the process of cleaning up
6454 <sect2 id="libpq-events-proc">
6455 <title>Event Callback Procedure</title>
6458 <varlistentry id="libpq-PGEventProc">
6459 <term><literal>PGEventProc</literal><indexterm><primary>PGEventProc</primary></indexterm></term>
6463 <literal>PGEventProc</literal> is a typedef for a pointer to an
6464 event procedure, that is, the user callback function that receives
6465 events from libpq. The signature of an event procedure must be
6468 int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
6471 The <parameter>evtId</parameter> parameter indicates which
6472 <literal>PGEVT</literal> event occurred. The
6473 <parameter>evtInfo</parameter> pointer must be cast to the appropriate
6474 structure type to obtain further information about the event.
6475 The <parameter>passThrough</parameter> parameter is the pointer
6476 provided to <xref linkend="libpq-PQregisterEventProc"/> when the event
6477 procedure was registered. The function should return a non-zero value
6478 if it succeeds and zero if it fails.
6482 A particular event procedure can be registered only once in any
6483 <structname>PGconn</structname>. This is because the address of the procedure
6484 is used as a lookup key to identify the associated instance data.
6489 On Windows, functions can have two different addresses: one visible
6490 from outside a DLL and another visible from inside the DLL. One
6491 should be careful that only one of these addresses is used with
6492 <application>libpq</application>'s event-procedure functions, else confusion will
6493 result. The simplest rule for writing code that will work is to
6494 ensure that event procedures are declared <literal>static</literal>. If the
6495 procedure's address must be available outside its own source file,
6496 expose a separate function to return the address.
6504 <sect2 id="libpq-events-funcs">
6505 <title>Event Support Functions</title>
6508 <varlistentry id="libpq-PQregisterEventProc">
6509 <term><function>PQregisterEventProc</function><indexterm><primary>PQregisterEventProc</primary></indexterm></term>
6513 Registers an event callback procedure with libpq.
6516 int PQregisterEventProc(PGconn *conn, PGEventProc proc,
6517 const char *name, void *passThrough);
6522 An event procedure must be registered once on each
6523 <structname>PGconn</structname> you want to receive events about. There is no
6524 limit, other than memory, on the number of event procedures that
6525 can be registered with a connection. The function returns a non-zero
6526 value if it succeeds and zero if it fails.
6530 The <parameter>proc</parameter> argument will be called when a libpq
6531 event is fired. Its memory address is also used to lookup
6532 <literal>instanceData</literal>. The <parameter>name</parameter>
6533 argument is used to refer to the event procedure in error messages.
6534 This value cannot be <symbol>NULL</symbol> or a zero-length string. The name string is
6535 copied into the <structname>PGconn</structname>, so what is passed need not be
6536 long-lived. The <parameter>passThrough</parameter> pointer is passed
6537 to the <parameter>proc</parameter> whenever an event occurs. This
6538 argument can be <symbol>NULL</symbol>.
6543 <varlistentry id="libpq-PQsetInstanceData">
6544 <term><function>PQsetInstanceData</function><indexterm><primary>PQsetInstanceData</primary></indexterm></term>
6547 Sets the connection <parameter>conn</parameter>'s <literal>instanceData</literal>
6548 for procedure <parameter>proc</parameter> to <parameter>data</parameter>. This
6549 returns non-zero for success and zero for failure. (Failure is
6550 only possible if <parameter>proc</parameter> has not been properly
6551 registered in <parameter>conn</parameter>.)
6554 int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
6560 <varlistentry id="libpq-PQinstanceData">
6561 <term><function>PQinstanceData</function><indexterm><primary>PQinstanceData</primary></indexterm></term>
6565 connection <parameter>conn</parameter>'s <literal>instanceData</literal>
6566 associated with procedure <parameter>proc</parameter>,
6567 or <symbol>NULL</symbol> if there is none.
6570 void *PQinstanceData(const PGconn *conn, PGEventProc proc);
6576 <varlistentry id="libpq-PQresultSetInstanceData">
6577 <term><function>PQresultSetInstanceData</function><indexterm><primary>PQresultSetInstanceData</primary></indexterm></term>
6580 Sets the result's <literal>instanceData</literal>
6581 for <parameter>proc</parameter> to <parameter>data</parameter>. This returns
6582 non-zero for success and zero for failure. (Failure is only
6583 possible if <parameter>proc</parameter> has not been properly registered
6587 int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
6592 Beware that any storage represented by <parameter>data</parameter>
6593 will not be accounted for by <xref linkend="libpq-PQresultMemorySize"/>,
6594 unless it is allocated using <xref linkend="libpq-PQresultAlloc"/>.
6595 (Doing so is recommendable because it eliminates the need to free
6596 such storage explicitly when the result is destroyed.)
6601 <varlistentry id="libpq-PQresultInstanceData">
6602 <term><function>PQresultInstanceData</function><indexterm><primary>PQresultInstanceData</primary></indexterm></term>
6605 Returns the result's <literal>instanceData</literal> associated with <parameter>proc</parameter>, or <symbol>NULL</symbol>
6609 void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
6617 <sect2 id="libpq-events-example">
6618 <title>Event Example</title>
6621 Here is a skeleton example of managing private data associated with
6622 libpq connections and results.
6627 /* required header for libpq events (note: includes libpq-fe.h) */
6628 #include <libpq-events.h>
6630 /* The instanceData */
6638 static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);
6646 PQconnectdb("dbname=postgres options=-csearch_path=");
6648 if (PQstatus(conn) != CONNECTION_OK)
6650 fprintf(stderr, "Connection to database failed: %s",
6651 PQerrorMessage(conn));
6656 /* called once on any connection that should receive events.
6657 * Sends a PGEVT_REGISTER to myEventProc.
6659 if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
6661 fprintf(stderr, "Cannot register PGEventProc\n");
6666 /* conn instanceData is available */
6667 data = PQinstanceData(conn, myEventProc);
6669 /* Sends a PGEVT_RESULTCREATE to myEventProc */
6670 res = PQexec(conn, "SELECT 1 + 1");
6672 /* result instanceData is available */
6673 data = PQresultInstanceData(res, myEventProc);
6675 /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
6676 res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);
6678 /* result instanceData is available if PG_COPYRES_EVENTS was
6679 * used during the PQcopyResult call.
6681 data = PQresultInstanceData(res_copy, myEventProc);
6683 /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
6687 /* Sends a PGEVT_CONNDESTROY to myEventProc */
6694 myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
6698 case PGEVT_REGISTER:
6700 PGEventRegister *e = (PGEventRegister *)evtInfo;
6701 mydata *data = get_mydata(e->conn);
6703 /* associate app specific data with connection */
6704 PQsetInstanceData(e->conn, myEventProc, data);
6708 case PGEVT_CONNRESET:
6710 PGEventConnReset *e = (PGEventConnReset *)evtInfo;
6711 mydata *data = PQinstanceData(e->conn, myEventProc);
6714 memset(data, 0, sizeof(mydata));
6718 case PGEVT_CONNDESTROY:
6720 PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
6721 mydata *data = PQinstanceData(e->conn, myEventProc);
6723 /* free instance data because the conn is being destroyed */
6729 case PGEVT_RESULTCREATE:
6731 PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
6732 mydata *conn_data = PQinstanceData(e->conn, myEventProc);
6733 mydata *res_data = dup_mydata(conn_data);
6735 /* associate app specific data with result (copy it from conn) */
6736 PQsetResultInstanceData(e->result, myEventProc, res_data);
6740 case PGEVT_RESULTCOPY:
6742 PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
6743 mydata *src_data = PQresultInstanceData(e->src, myEventProc);
6744 mydata *dest_data = dup_mydata(src_data);
6746 /* associate app specific data with result (copy it from a result) */
6747 PQsetResultInstanceData(e->dest, myEventProc, dest_data);
6751 case PGEVT_RESULTDESTROY:
6753 PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
6754 mydata *data = PQresultInstanceData(e->result, myEventProc);
6756 /* free instance data because the result is being destroyed */
6762 /* unknown event ID, just return true. */
6767 return true; /* event processing succeeded */
6774 <sect1 id="libpq-envars">
6775 <title>Environment Variables</title>
6777 <indexterm zone="libpq-envars">
6778 <primary>environment variable</primary>
6782 The following environment variables can be used to select default
6783 connection parameter values, which will be used by
6784 <xref linkend="libpq-PQconnectdb"/>, <xref linkend="libpq-PQsetdbLogin"/> and
6785 <xref linkend="libpq-PQsetdb"/> if no value is directly specified by the calling
6786 code. These are useful to avoid hard-coding database connection
6787 information into simple client applications, for example.
6793 <primary><envar>PGHOST</envar></primary>
6795 <envar>PGHOST</envar> behaves the same as the <xref
6796 linkend="libpq-connect-host"/> connection parameter.
6803 <primary><envar>PGHOSTADDR</envar></primary>
6805 <envar>PGHOSTADDR</envar> behaves the same as the <xref
6806 linkend="libpq-connect-hostaddr"/> connection parameter.
6807 This can be set instead of or in addition to <envar>PGHOST</envar>
6808 to avoid DNS lookup overhead.
6815 <primary><envar>PGPORT</envar></primary>
6817 <envar>PGPORT</envar> behaves the same as the <xref
6818 linkend="libpq-connect-port"/> connection parameter.
6825 <primary><envar>PGDATABASE</envar></primary>
6827 <envar>PGDATABASE</envar> behaves the same as the <xref
6828 linkend="libpq-connect-dbname"/> connection parameter.
6835 <primary><envar>PGUSER</envar></primary>
6837 <envar>PGUSER</envar> behaves the same as the <xref
6838 linkend="libpq-connect-user"/> connection parameter.
6845 <primary><envar>PGPASSWORD</envar></primary>
6847 <envar>PGPASSWORD</envar> behaves the same as the <xref
6848 linkend="libpq-connect-password"/> connection parameter.
6849 Use of this environment variable
6850 is not recommended for security reasons, as some operating systems
6851 allow non-root users to see process environment variables via
6852 <application>ps</application>; instead consider using a password file
6853 (see <xref linkend="libpq-pgpass"/>).
6860 <primary><envar>PGPASSFILE</envar></primary>
6862 <envar>PGPASSFILE</envar> behaves the same as the <xref
6863 linkend="libpq-connect-passfile"/> connection parameter.
6870 <primary><envar>PGSERVICE</envar></primary>
6872 <envar>PGSERVICE</envar> behaves the same as the <xref
6873 linkend="libpq-connect-service"/> connection parameter.
6880 <primary><envar>PGSERVICEFILE</envar></primary>
6882 <envar>PGSERVICEFILE</envar> specifies the name of the per-user
6883 connection service file. If not set, it defaults
6884 to <filename>~/.pg_service.conf</filename>
6885 (see <xref linkend="libpq-pgservice"/>).
6892 <primary><envar>PGOPTIONS</envar></primary>
6894 <envar>PGOPTIONS</envar> behaves the same as the <xref
6895 linkend="libpq-connect-options"/> connection parameter.
6902 <primary><envar>PGAPPNAME</envar></primary>
6904 <envar>PGAPPNAME</envar> behaves the same as the <xref
6905 linkend="libpq-connect-application-name"/> connection parameter.
6912 <primary><envar>PGSSLMODE</envar></primary>
6914 <envar>PGSSLMODE</envar> behaves the same as the <xref
6915 linkend="libpq-connect-sslmode"/> connection parameter.
6922 <primary><envar>PGREQUIRESSL</envar></primary>
6924 <envar>PGREQUIRESSL</envar> behaves the same as the <xref
6925 linkend="libpq-connect-requiressl"/> connection parameter.
6926 This environment variable is deprecated in favor of the
6927 <envar>PGSSLMODE</envar> variable; setting both variables suppresses the
6935 <primary><envar>PGSSLCOMPRESSION</envar></primary>
6937 <envar>PGSSLCOMPRESSION</envar> behaves the same as the <xref
6938 linkend="libpq-connect-sslcompression"/> connection parameter.
6945 <primary><envar>PGSSLCERT</envar></primary>
6947 <envar>PGSSLCERT</envar> behaves the same as the <xref
6948 linkend="libpq-connect-sslcert"/> connection parameter.
6955 <primary><envar>PGSSLKEY</envar></primary>
6957 <envar>PGSSLKEY</envar> behaves the same as the <xref
6958 linkend="libpq-connect-sslkey"/> connection parameter.
6965 <primary><envar>PGSSLROOTCERT</envar></primary>
6967 <envar>PGSSLROOTCERT</envar> behaves the same as the <xref
6968 linkend="libpq-connect-sslrootcert"/> connection parameter.
6975 <primary><envar>PGSSLCRL</envar></primary>
6977 <envar>PGSSLCRL</envar> behaves the same as the <xref
6978 linkend="libpq-connect-sslcrl"/> connection parameter.
6985 <primary><envar>PGREQUIREPEER</envar></primary>
6987 <envar>PGREQUIREPEER</envar> behaves the same as the <xref
6988 linkend="libpq-connect-requirepeer"/> connection parameter.
6995 <primary><envar>PGGSSENCMODE</envar></primary>
6997 <envar>PGGSSENCMODE</envar> behaves the same as the <xref
6998 linkend="libpq-connect-gssencmode"/> connection parameter.
7005 <primary><envar>PGKRBSRVNAME</envar></primary>
7007 <envar>PGKRBSRVNAME</envar> behaves the same as the <xref
7008 linkend="libpq-connect-krbsrvname"/> connection parameter.
7015 <primary><envar>PGGSSLIB</envar></primary>
7017 <envar>PGGSSLIB</envar> behaves the same as the <xref
7018 linkend="libpq-connect-gsslib"/> connection parameter.
7025 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
7027 <envar>PGCONNECT_TIMEOUT</envar> behaves the same as the <xref
7028 linkend="libpq-connect-connect-timeout"/> connection parameter.
7035 <primary><envar>PGCLIENTENCODING</envar></primary>
7037 <envar>PGCLIENTENCODING</envar> behaves the same as the <xref
7038 linkend="libpq-connect-client-encoding"/> connection parameter.
7045 <primary><envar>PGTARGETSESSIONATTRS</envar></primary>
7047 <envar>PGTARGETSESSIONATTRS</envar> behaves the same as the <xref
7048 linkend="libpq-connect-target-session-attrs"/> connection parameter.
7055 The following environment variables can be used to specify default
7056 behavior for each <productname>PostgreSQL</productname> session. (See
7057 also the <xref linkend="sql-alterrole"/>
7058 and <xref linkend="sql-alterdatabase"/>
7059 commands for ways to set default behavior on a per-user or per-database
7066 <primary><envar>PGDATESTYLE</envar></primary>
7068 <envar>PGDATESTYLE</envar> sets the default style of date/time
7069 representation. (Equivalent to <literal>SET datestyle TO
7077 <primary><envar>PGTZ</envar></primary>
7079 <envar>PGTZ</envar> sets the default time zone. (Equivalent to
7080 <literal>SET timezone TO ...</literal>.)
7087 <primary><envar>PGGEQO</envar></primary>
7089 <envar>PGGEQO</envar> sets the default mode for the genetic query
7090 optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
7095 Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set"/>
7096 for information on correct values for these
7097 environment variables.
7101 The following environment variables determine internal behavior of
7102 <application>libpq</application>; they override compiled-in defaults.
7108 <primary><envar>PGSYSCONFDIR</envar></primary>
7110 <envar>PGSYSCONFDIR</envar> sets the directory containing the
7111 <filename>pg_service.conf</filename> file and in a future version
7112 possibly other system-wide configuration files.
7119 <primary><envar>PGLOCALEDIR</envar></primary>
7121 <envar>PGLOCALEDIR</envar> sets the directory containing the
7122 <literal>locale</literal> files for message localization.
7131 <sect1 id="libpq-pgpass">
7132 <title>The Password File</title>
7134 <indexterm zone="libpq-pgpass">
7135 <primary>password file</primary>
7137 <indexterm zone="libpq-pgpass">
7138 <primary>.pgpass</primary>
7142 The file <filename>.pgpass</filename> in a user's home directory can
7143 contain passwords to
7144 be used if the connection requires a password (and no password has been
7145 specified otherwise). On Microsoft Windows the file is named
7146 <filename>%APPDATA%\postgresql\pgpass.conf</filename> (where
7147 <filename>%APPDATA%</filename> refers to the Application Data subdirectory in
7148 the user's profile).
7149 Alternatively, a password file can be specified
7150 using the connection parameter <xref linkend="libpq-connect-passfile"/>
7151 or the environment variable <envar>PGPASSFILE</envar>.
7155 This file should contain lines of the following format:
7157 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
7159 (You can add a reminder comment to the file by copying the line above and
7160 preceding it with <literal>#</literal>.)
7161 Each of the first four fields can be a literal value, or
7162 <literal>*</literal>, which matches anything. The password field from
7163 the first line that matches the current connection parameters will be
7164 used. (Therefore, put more-specific entries first when you are using
7165 wildcards.) If an entry needs to contain <literal>:</literal> or
7166 <literal>\</literal>, escape this character with <literal>\</literal>.
7167 The host name field is matched to the <literal>host</literal> connection
7168 parameter if that is specified, otherwise to
7169 the <literal>hostaddr</literal> parameter if that is specified; if neither
7170 are given then the host name <literal>localhost</literal> is searched for.
7171 The host name <literal>localhost</literal> is also searched for when
7172 the connection is a Unix-domain socket connection and
7173 the <literal>host</literal> parameter
7174 matches <application>libpq</application>'s default socket directory path.
7175 In a standby server, a database field of <literal>replication</literal>
7176 matches streaming replication connections made to the master server.
7177 The database field is of limited usefulness otherwise, because users have
7178 the same password for all databases in the same cluster.
7182 On Unix systems, the permissions on a password file must
7183 disallow any access to world or group; achieve this by a command such as
7184 <command>chmod 0600 ~/.pgpass</command>. If the permissions are less
7185 strict than this, the file will be ignored. On Microsoft Windows, it
7186 is assumed that the file is stored in a directory that is secure, so
7187 no special permissions check is made.
7192 <sect1 id="libpq-pgservice">
7193 <title>The Connection Service File</title>
7195 <indexterm zone="libpq-pgservice">
7196 <primary>connection service file</primary>
7198 <indexterm zone="libpq-pgservice">
7199 <primary>pg_service.conf</primary>
7201 <indexterm zone="libpq-pgservice">
7202 <primary>.pg_service.conf</primary>
7206 The connection service file allows libpq connection parameters to be
7207 associated with a single service name. That service name can then be
7208 specified by a libpq connection, and the associated settings will be
7209 used. This allows connection parameters to be modified without requiring
7210 a recompile of the libpq application. The service name can also be
7211 specified using the <envar>PGSERVICE</envar> environment variable.
7215 The connection service file can be a per-user service file
7216 at <filename>~/.pg_service.conf</filename> or the location
7217 specified by the environment variable <envar>PGSERVICEFILE</envar>,
7218 or it can be a system-wide file
7219 at <filename>`pg_config --sysconfdir`/pg_service.conf</filename> or in the directory
7220 specified by the environment variable
7221 <envar>PGSYSCONFDIR</envar>. If service definitions with the same
7222 name exist in the user and the system file, the user file takes
7227 The file uses an <quote>INI file</quote> format where the section
7228 name is the service name and the parameters are connection
7229 parameters; see <xref linkend="libpq-paramkeywords"/> for a list. For
7238 An example file is provided at
7239 <filename>share/pg_service.conf.sample</filename>.
7244 <sect1 id="libpq-ldap">
7245 <title>LDAP Lookup of Connection Parameters</title>
7247 <indexterm zone="libpq-ldap">
7248 <primary>LDAP connection parameter lookup</primary>
7252 If <application>libpq</application> has been compiled with LDAP support (option
7253 <literal><option>--with-ldap</option></literal> for <command>configure</command>)
7254 it is possible to retrieve connection options like <literal>host</literal>
7255 or <literal>dbname</literal> via LDAP from a central server.
7256 The advantage is that if the connection parameters for a database change,
7257 the connection information doesn't have to be updated on all client machines.
7261 LDAP connection parameter lookup uses the connection service file
7262 <filename>pg_service.conf</filename> (see <xref
7263 linkend="libpq-pgservice"/>). A line in a
7264 <filename>pg_service.conf</filename> stanza that starts with
7265 <literal>ldap://</literal> will be recognized as an LDAP URL and an
7266 LDAP query will be performed. The result must be a list of
7267 <literal>keyword = value</literal> pairs which will be used to set
7268 connection options. The URL must conform to RFC 1959 and be of the
7271 ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
7273 where <replaceable>hostname</replaceable> defaults to
7274 <literal>localhost</literal> and <replaceable>port</replaceable>
7279 Processing of <filename>pg_service.conf</filename> is terminated after
7280 a successful LDAP lookup, but is continued if the LDAP server cannot
7281 be contacted. This is to provide a fallback with further LDAP URL
7282 lines that point to different LDAP servers, classical <literal>keyword
7283 = value</literal> pairs, or default connection options. If you would
7284 rather get an error message in this case, add a syntactically incorrect
7285 line after the LDAP URL.
7289 A sample LDAP entry that has been created with the LDIF file
7292 dn:cn=mydatabase,dc=mycompany,dc=com
7297 description:host=dbserver.mycompany.com
7298 description:port=5439
7299 description:dbname=mydb
7300 description:user=mydb_user
7301 description:sslmode=require
7303 might be queried with the following LDAP URL:
7305 ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydatabase)
7310 You can also mix regular service file entries with LDAP lookups.
7311 A complete example for a stanza in <filename>pg_service.conf</filename>
7314 # only host and port are stored in LDAP, specify dbname and user explicitly
7318 ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
7325 <sect1 id="libpq-ssl">
7326 <title>SSL Support</title>
7328 <indexterm zone="libpq-ssl">
7329 <primary>SSL</primary>
7333 <productname>PostgreSQL</productname> has native support for using <acronym>SSL</acronym>
7334 connections to encrypt client/server communications for increased
7335 security. See <xref linkend="ssl-tcp"/> for details about the server-side
7336 <acronym>SSL</acronym> functionality.
7340 <application>libpq</application> reads the system-wide
7341 <productname>OpenSSL</productname> configuration file. By default, this
7342 file is named <filename>openssl.cnf</filename> and is located in the
7343 directory reported by <literal>openssl version -d</literal>. This default
7344 can be overridden by setting environment variable
7345 <envar>OPENSSL_CONF</envar> to the name of the desired configuration
7349 <sect2 id="libq-ssl-certificates">
7350 <title>Client Verification of Server Certificates</title>
7353 By default, <productname>PostgreSQL</productname> will not perform any verification of
7354 the server certificate. This means that it is possible to spoof the server
7355 identity (for example by modifying a DNS record or by taking over the server
7356 IP address) without the client knowing. In order to prevent spoofing,
7357 the client must be able to verify the server's identity via a chain of
7358 trust. A chain of trust is established by placing a root (self-signed)
7359 certificate authority (<acronym>CA</acronym>) certificate on one
7360 computer and a leaf certificate <emphasis>signed</emphasis> by the
7361 root certificate on another computer. It is also possible to use an
7362 <quote>intermediate</quote> certificate which is signed by the root
7363 certificate and signs leaf certificates.
7367 To allow the client to verify the identity of the server, place a root
7368 certificate on the client and a leaf certificate signed by the root
7369 certificate on the server. To allow the server to verify the identity
7370 of the client, place a root certificate on the server and a leaf
7371 certificate signed by the root certificate on the client. One or more
7372 intermediate certificates (usually stored with the leaf certificate)
7373 can also be used to link the leaf certificate to the root certificate.
7377 Once a chain of trust has been established, there are two ways for
7378 the client to validate the leaf certificate sent by the server.
7379 If the parameter <literal>sslmode</literal> is set to <literal>verify-ca</literal>,
7380 libpq will verify that the server is trustworthy by checking the
7381 certificate chain up to the root certificate stored on the client.
7382 If <literal>sslmode</literal> is set to <literal>verify-full</literal>,
7383 libpq will <emphasis>also</emphasis> verify that the server host
7384 name matches the name stored in the server certificate. The
7385 SSL connection will fail if the server certificate cannot be
7386 verified. <literal>verify-full</literal> is recommended in most
7387 security-sensitive environments.
7391 In <literal>verify-full</literal> mode, the host name is matched against the
7392 certificate's Subject Alternative Name attribute(s), or against the
7393 Common Name attribute if no Subject Alternative Name of type <literal>dNSName</literal> is
7394 present. If the certificate's name attribute starts with an asterisk
7395 (<literal>*</literal>), the asterisk will be treated as
7396 a wildcard, which will match all characters <emphasis>except</emphasis> a dot
7397 (<literal>.</literal>). This means the certificate will not match subdomains.
7398 If the connection is made using an IP address instead of a host name, the
7399 IP address will be matched (without doing any DNS lookups).
7403 To allow server certificate verification, one or more root certificates
7404 must be placed in the file <filename>~/.postgresql/root.crt</filename>
7405 in the user's home directory. (On Microsoft Windows the file is named
7406 <filename>%APPDATA%\postgresql\root.crt</filename>.) Intermediate
7407 certificates should also be added to the file if they are needed to link
7408 the certificate chain sent by the server to the root certificates
7409 stored on the client.
7413 Certificate Revocation List (CRL) entries are also checked
7414 if the file <filename>~/.postgresql/root.crl</filename> exists
7415 (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
7420 The location of the root certificate file and the CRL can be changed by
7422 the connection parameters <literal>sslrootcert</literal> and <literal>sslcrl</literal>
7423 or the environment variables <envar>PGSSLROOTCERT</envar> and <envar>PGSSLCRL</envar>.
7428 For backwards compatibility with earlier versions of PostgreSQL, if a
7429 root CA file exists, the behavior of
7430 <literal>sslmode</literal>=<literal>require</literal> will be the same
7431 as that of <literal>verify-ca</literal>, meaning the server certificate
7432 is validated against the CA. Relying on this behavior is discouraged,
7433 and applications that need certificate validation should always use
7434 <literal>verify-ca</literal> or <literal>verify-full</literal>.
7439 <sect2 id="libpq-ssl-clientcert">
7440 <title>Client Certificates</title>
7443 If the server attempts to verify the identity of the
7444 client by requesting the client's leaf certificate,
7445 <application>libpq</application> will send the certificates stored in
7446 file <filename>~/.postgresql/postgresql.crt</filename> in the user's home
7447 directory. The certificates must chain to the root certificate trusted
7448 by the server. A matching
7449 private key file <filename>~/.postgresql/postgresql.key</filename> must also
7450 be present. The private
7451 key file must not allow any access to world or group; achieve this by the
7452 command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
7453 On Microsoft Windows these files are named
7454 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
7455 <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
7456 is no special permissions check since the directory is presumed secure.
7457 The location of the certificate and key files can be overridden by the
7458 connection parameters <literal>sslcert</literal> and <literal>sslkey</literal> or the
7459 environment variables <envar>PGSSLCERT</envar> and <envar>PGSSLKEY</envar>.
7463 The first certificate in <filename>postgresql.crt</filename> must be the
7464 client's certificate because it must match the client's private key.
7465 <quote>Intermediate</quote> certificates can be optionally appended
7466 to the file — doing so avoids requiring storage of intermediate
7467 certificates on the server (<xref linkend="guc-ssl-ca-file"/>).
7471 For instructions on creating certificates, see <xref
7472 linkend="ssl-certificate-creation"/>.
7476 <sect2 id="libpq-ssl-protection">
7477 <title>Protection Provided in Different Modes</title>
7480 The different values for the <literal>sslmode</literal> parameter provide different
7481 levels of protection. SSL can provide
7482 protection against three types of attacks:
7486 <term>Eavesdropping</term>
7488 <para>If a third party can examine the network traffic between the
7489 client and the server, it can read both connection information (including
7490 the user name and password) and the data that is passed. <acronym>SSL</acronym>
7491 uses encryption to prevent this.
7497 <term>Man in the middle (<acronym>MITM</acronym>)</term>
7499 <para>If a third party can modify the data while passing between the
7500 client and server, it can pretend to be the server and therefore see and
7501 modify data <emphasis>even if it is encrypted</emphasis>. The third party can then
7502 forward the connection information and data to the original server,
7503 making it impossible to detect this attack. Common vectors to do this
7504 include DNS poisoning and address hijacking, whereby the client is directed
7505 to a different server than intended. There are also several other
7506 attack methods that can accomplish this. <acronym>SSL</acronym> uses certificate
7507 verification to prevent this, by authenticating the server to the client.
7513 <term>Impersonation</term>
7515 <para>If a third party can pretend to be an authorized client, it can
7516 simply access data it should not have access to. Typically this can
7517 happen through insecure password management. <acronym>SSL</acronym> uses
7518 client certificates to prevent this, by making sure that only holders
7519 of valid certificates can access the server.
7527 For a connection to be known SSL-secured, SSL usage must be configured
7528 on <emphasis>both the client and the server</emphasis> before the connection
7529 is made. If it is only configured on the server, the client may end up
7530 sending sensitive information (e.g. passwords) before
7531 it knows that the server requires high security. In libpq, secure
7532 connections can be ensured
7533 by setting the <literal>sslmode</literal> parameter to <literal>verify-full</literal> or
7534 <literal>verify-ca</literal>, and providing the system with a root certificate to
7535 verify against. This is analogous to using an <literal>https</literal>
7536 <acronym>URL</acronym> for encrypted web browsing.
7540 Once the server has been authenticated, the client can pass sensitive data.
7541 This means that up until this point, the client does not need to know if
7542 certificates will be used for authentication, making it safe to specify that
7543 only in the server configuration.
7547 All <acronym>SSL</acronym> options carry overhead in the form of encryption and
7548 key-exchange, so there is a trade-off that has to be made between performance
7549 and security. <xref linkend="libpq-ssl-sslmode-statements"/>
7550 illustrates the risks the different <literal>sslmode</literal> values
7551 protect against, and what statement they make about security and overhead.
7554 <table id="libpq-ssl-sslmode-statements">
7555 <title>SSL Mode Descriptions</title>
7559 <entry><literal>sslmode</literal></entry>
7560 <entry>Eavesdropping protection</entry>
7561 <entry><acronym>MITM</acronym> protection</entry>
7562 <entry>Statement</entry>
7568 <entry><literal>disable</literal></entry>
7571 <entry>I don't care about security, and I don't want to pay the overhead
7577 <entry><literal>allow</literal></entry>
7578 <entry>Maybe</entry>
7580 <entry>I don't care about security, but I will pay the overhead of
7581 encryption if the server insists on it.
7586 <entry><literal>prefer</literal></entry>
7587 <entry>Maybe</entry>
7589 <entry>I don't care about encryption, but I wish to pay the overhead of
7590 encryption if the server supports it.
7595 <entry><literal>require</literal></entry>
7598 <entry>I want my data to be encrypted, and I accept the overhead. I trust
7599 that the network will make sure I always connect to the server I want.
7604 <entry><literal>verify-ca</literal></entry>
7606 <entry><literal>Depends on CA</literal>-policy</entry>
7607 <entry>I want my data encrypted, and I accept the overhead. I want to be
7608 sure that I connect to a server that I trust.
7613 <entry><literal>verify-full</literal></entry>
7616 <entry>I want my data encrypted, and I accept the overhead. I want to be
7617 sure that I connect to a server I trust, and that it's the one I
7627 The difference between <literal>verify-ca</literal> and <literal>verify-full</literal>
7628 depends on the policy of the root <acronym>CA</acronym>. If a public
7629 <acronym>CA</acronym> is used, <literal>verify-ca</literal> allows connections to a server
7630 that <emphasis>somebody else</emphasis> may have registered with the <acronym>CA</acronym>.
7631 In this case, <literal>verify-full</literal> should always be used. If
7632 a local <acronym>CA</acronym> is used, or even a self-signed certificate, using
7633 <literal>verify-ca</literal> often provides enough protection.
7637 The default value for <literal>sslmode</literal> is <literal>prefer</literal>. As is shown
7638 in the table, this makes no sense from a security point of view, and it only
7639 promises performance overhead if possible. It is only provided as the default
7640 for backward compatibility, and is not recommended in secure deployments.
7645 <sect2 id="libpq-ssl-fileusage">
7646 <title>SSL Client File Usage</title>
7649 <xref linkend="libpq-ssl-file-usage"/> summarizes the files that are
7650 relevant to the SSL setup on the client.
7653 <table id="libpq-ssl-file-usage">
7654 <title>Libpq/Client SSL File Usage</title>
7659 <entry>Contents</entry>
7660 <entry>Effect</entry>
7667 <entry><filename>~/.postgresql/postgresql.crt</filename></entry>
7668 <entry>client certificate</entry>
7669 <entry>requested by server</entry>
7673 <entry><filename>~/.postgresql/postgresql.key</filename></entry>
7674 <entry>client private key</entry>
7675 <entry>proves client certificate sent by owner; does not indicate
7676 certificate owner is trustworthy</entry>
7680 <entry><filename>~/.postgresql/root.crt</filename></entry>
7681 <entry>trusted certificate authorities</entry>
7682 <entry>checks that server certificate is signed by a trusted certificate
7687 <entry><filename>~/.postgresql/root.crl</filename></entry>
7688 <entry>certificates revoked by certificate authorities</entry>
7689 <entry>server certificate must not be on this list</entry>
7697 <sect2 id="libpq-ssl-initialize">
7698 <title>SSL Library Initialization</title>
7701 If your application initializes <literal>libssl</literal> and/or
7702 <literal>libcrypto</literal> libraries and <application>libpq</application>
7703 is built with <acronym>SSL</acronym> support, you should call
7704 <xref linkend="libpq-PQinitOpenSSL"/> to tell <application>libpq</application>
7705 that the <literal>libssl</literal> and/or <literal>libcrypto</literal> libraries
7706 have been initialized by your application, so that
7707 <application>libpq</application> will not also initialize those libraries.
7709 url="http://h41379.www4.hpe.com/doc/83final/ba554_90007/ch04.html"></ulink>
7710 for details on the SSL API.
7715 <varlistentry id="libpq-PQinitOpenSSL">
7716 <term><function>PQinitOpenSSL</function><indexterm><primary>PQinitOpenSSL</primary></indexterm></term>
7720 Allows applications to select which security libraries to initialize.
7722 void PQinitOpenSSL(int do_ssl, int do_crypto);
7727 When <parameter>do_ssl</parameter> is non-zero, <application>libpq</application>
7728 will initialize the <application>OpenSSL</application> library before first
7729 opening a database connection. When <parameter>do_crypto</parameter> is
7730 non-zero, the <literal>libcrypto</literal> library will be initialized. By
7731 default (if <xref linkend="libpq-PQinitOpenSSL"/> is not called), both libraries
7732 are initialized. When SSL support is not compiled in, this function is
7733 present but does nothing.
7737 If your application uses and initializes either <application>OpenSSL</application>
7738 or its underlying <literal>libcrypto</literal> library, you <emphasis>must</emphasis>
7739 call this function with zeroes for the appropriate parameter(s)
7740 before first opening a database connection. Also be sure that you
7741 have done that initialization before opening a database connection.
7746 <varlistentry id="libpq-PQinitSSL">
7747 <term><function>PQinitSSL</function><indexterm><primary>PQinitSSL</primary></indexterm></term><listitem>
7749 Allows applications to select which security libraries to initialize.
7751 void PQinitSSL(int do_ssl);
7756 This function is equivalent to
7757 <literal>PQinitOpenSSL(do_ssl, do_ssl)</literal>.
7758 It is sufficient for applications that initialize both or neither
7759 of <application>OpenSSL</application> and <literal>libcrypto</literal>.
7763 <xref linkend="libpq-PQinitSSL"/> has been present since
7764 <productname>PostgreSQL</productname> 8.0, while <xref linkend="libpq-PQinitOpenSSL"/>
7765 was added in <productname>PostgreSQL</productname> 8.4, so <xref linkend="libpq-PQinitSSL"/>
7766 might be preferable for applications that need to work with older
7767 versions of <application>libpq</application>.
7778 <sect1 id="libpq-threading">
7779 <title>Behavior in Threaded Programs</title>
7781 <indexterm zone="libpq-threading">
7782 <primary>threads</primary>
7783 <secondary>with libpq</secondary>
7787 <application>libpq</application> is reentrant and thread-safe by default.
7788 You might need to use special compiler command-line
7789 options when you compile your application code. Refer to your
7790 system's documentation for information about how to build
7791 thread-enabled applications, or look in
7792 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</literal>
7793 and <literal>PTHREAD_LIBS</literal>. This function allows the querying of
7794 <application>libpq</application>'s thread-safe status:
7798 <varlistentry id="libpq-PQisthreadsafe">
7799 <term><function>PQisthreadsafe</function><indexterm><primary>PQisthreadsafe</primary></indexterm></term>
7803 Returns the thread safety status of the
7804 <application>libpq</application> library.
7806 int PQisthreadsafe();
7811 Returns 1 if the <application>libpq</application> is thread-safe
7819 One thread restriction is that no two threads attempt to manipulate
7820 the same <structname>PGconn</structname> object at the same time. In particular,
7821 you cannot issue concurrent commands from different threads through
7822 the same connection object. (If you need to run concurrent commands,
7823 use multiple connections.)
7827 <structname>PGresult</structname> objects are normally read-only after creation,
7828 and so can be passed around freely between threads. However, if you use
7829 any of the <structname>PGresult</structname>-modifying functions described in
7830 <xref linkend="libpq-misc"/> or <xref linkend="libpq-events"/>, it's up
7831 to you to avoid concurrent operations on the same <structname>PGresult</structname>,
7836 The deprecated functions <xref linkend="libpq-PQrequestCancel"/> and
7837 <xref linkend="libpq-PQoidStatus"/> are not thread-safe and should not be
7838 used in multithread programs. <xref linkend="libpq-PQrequestCancel"/>
7839 can be replaced by <xref linkend="libpq-PQcancel"/>.
7840 <xref linkend="libpq-PQoidStatus"/> can be replaced by
7841 <xref linkend="libpq-PQoidValue"/>.
7845 If you are using Kerberos inside your application (in addition to inside
7846 <application>libpq</application>), you will need to do locking around
7847 Kerberos calls because Kerberos functions are not thread-safe. See
7848 function <function>PQregisterThreadLock</function> in the
7849 <application>libpq</application> source code for a way to do cooperative
7850 locking between <application>libpq</application> and your application.
7854 If you experience problems with threaded applications, run the program
7855 in <filename>src/tools/thread</filename> to see if your platform has
7856 thread-unsafe functions. This program is run by
7857 <filename>configure</filename>, but for binary distributions your
7858 library might not match the library used to build the binaries.
7863 <sect1 id="libpq-build">
7864 <title>Building <application>libpq</application> Programs</title>
7866 <indexterm zone="libpq-build">
7867 <primary>compiling</primary>
7868 <secondary>libpq applications</secondary>
7872 To build (i.e., compile and link) a program using
7873 <application>libpq</application> you need to do all of the following
7879 Include the <filename>libpq-fe.h</filename> header file:
7881 #include <libpq-fe.h>
7883 If you failed to do that then you will normally get error messages
7884 from your compiler similar to:
7886 foo.c: In function `main':
7887 foo.c:34: `PGconn' undeclared (first use in this function)
7888 foo.c:35: `PGresult' undeclared (first use in this function)
7889 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
7890 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
7891 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
7898 Point your compiler to the directory where the <productname>PostgreSQL</productname> header
7899 files were installed, by supplying the
7900 <literal>-I<replaceable>directory</replaceable></literal> option
7901 to your compiler. (In some cases the compiler will look into
7902 the directory in question by default, so you can omit this
7903 option.) For instance, your compile command line could look
7906 cc -c -I/usr/local/pgsql/include testprog.c
7908 If you are using makefiles then add the option to the
7909 <varname>CPPFLAGS</varname> variable:
7911 CPPFLAGS += -I/usr/local/pgsql/include
7916 If there is any chance that your program might be compiled by
7917 other users then you should not hardcode the directory location
7918 like that. Instead, you can run the utility
7919 <command>pg_config</command><indexterm><primary>pg_config</primary><secondary
7920 sortas="libpq">with libpq</secondary></indexterm> to find out where the header
7921 files are on the local system:
7923 <prompt>$</prompt> pg_config --includedir
7924 <computeroutput>/usr/local/include</computeroutput>
7930 have <command>pkg-config</command><indexterm><primary>pkg-config</primary><secondary sortas="libpq">with
7931 libpq</secondary></indexterm> installed, you can run instead:
7933 <prompt>$</prompt> pkg-config --cflags libpq
7934 <computeroutput>-I/usr/local/include</computeroutput>
7936 Note that this will already include the <option>-I</option> in front of
7941 Failure to specify the correct option to the compiler will
7942 result in an error message such as:
7944 testlibpq.c:8:22: libpq-fe.h: No such file or directory
7951 When linking the final program, specify the option
7952 <literal>-lpq</literal> so that the <application>libpq</application>
7953 library gets pulled in, as well as the option
7954 <literal>-L<replaceable>directory</replaceable></literal> to point
7955 the compiler to the directory where the
7956 <application>libpq</application> library resides. (Again, the
7957 compiler will search some directories by default.) For maximum
7958 portability, put the <option>-L</option> option before the
7959 <option>-lpq</option> option. For example:
7961 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
7966 You can find out the library directory using
7967 <command>pg_config</command> as well:
7969 <prompt>$</prompt> pg_config --libdir
7970 <computeroutput>/usr/local/pgsql/lib</computeroutput>
7975 Or again use <command>pkg-config</command>:
7977 <prompt>$</prompt> pkg-config --libs libpq
7978 <computeroutput>-L/usr/local/pgsql/lib -lpq</computeroutput>
7980 Note again that this prints the full options, not only the path.
7984 Error messages that point to problems in this area could look like
7987 testlibpq.o: In function `main':
7988 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
7989 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
7990 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
7992 This means you forgot <option>-lpq</option>.
7994 /usr/bin/ld: cannot find -lpq
7996 This means you forgot the <option>-L</option> option or did not
7997 specify the right directory.
8006 <sect1 id="libpq-example">
8007 <title>Example Programs</title>
8010 These examples and others can be found in the
8011 directory <filename>src/test/examples</filename> in the source code
8015 <example id="libpq-example-1">
8016 <title><application>libpq</application> Example Program 1</title>
8021 * src/test/examples/testlibpq.c
8026 * Test the C version of libpq, the PostgreSQL frontend library.
8030 #include "libpq-fe.h"
8033 exit_nicely(PGconn *conn)
8040 main(int argc, char **argv)
8042 const char *conninfo;
8050 * If the user supplies a parameter on the command line, use it as the
8051 * conninfo string; otherwise default to setting dbname=postgres and using
8052 * environment variables or defaults for all other connection parameters.
8057 conninfo = "dbname = postgres";
8059 /* Make a connection to the database */
8060 conn = PQconnectdb(conninfo);
8062 /* Check to see that the backend connection was successfully made */
8063 if (PQstatus(conn) != CONNECTION_OK)
8065 fprintf(stderr, "Connection to database failed: %s",
8066 PQerrorMessage(conn));
8070 /* Set always-secure search path, so malicious users can't take control. */
8072 "SELECT pg_catalog.set_config('search_path', '', false)");
8073 if (PQresultStatus(res) != PGRES_TUPLES_OK)
8075 fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
8081 * Should PQclear PGresult whenever it is no longer needed to avoid memory
8087 * Our test case here involves using a cursor, for which we must be inside
8088 * a transaction block. We could do the whole thing with a single
8089 * PQexec() of "select * from pg_database", but that's too trivial to make
8093 /* Start a transaction block */
8094 res = PQexec(conn, "BEGIN");
8095 if (PQresultStatus(res) != PGRES_COMMAND_OK)
8097 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
8104 * Fetch rows from pg_database, the system catalog of databases
8106 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
8107 if (PQresultStatus(res) != PGRES_COMMAND_OK)
8109 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
8115 res = PQexec(conn, "FETCH ALL in myportal");
8116 if (PQresultStatus(res) != PGRES_TUPLES_OK)
8118 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
8123 /* first, print out the attribute names */
8124 nFields = PQnfields(res);
8125 for (i = 0; i < nFields; i++)
8126 printf("%-15s", PQfname(res, i));
8129 /* next, print out the rows */
8130 for (i = 0; i < PQntuples(res); i++)
8132 for (j = 0; j < nFields; j++)
8133 printf("%-15s", PQgetvalue(res, i, j));
8139 /* close the portal ... we don't bother to check for errors ... */
8140 res = PQexec(conn, "CLOSE myportal");
8143 /* end the transaction */
8144 res = PQexec(conn, "END");
8147 /* close the connection to the database and cleanup */
8156 <example id="libpq-example-2">
8157 <title><application>libpq</application> Example Program 2</title>
8162 * src/test/examples/testlibpq2.c
8166 * Test of the asynchronous notification interface
8168 * Start this program, then from psql in another window do
8170 * Repeat four times to get this program to exit.
8172 * Or, if you want to get fancy, try this:
8173 * populate a database with the following commands
8174 * (provided in src/test/examples/testlibpq2.sql):
8176 * CREATE SCHEMA TESTLIBPQ2;
8177 * SET search_path = TESTLIBPQ2;
8178 * CREATE TABLE TBL1 (i int4);
8179 * CREATE TABLE TBL2 (i int4);
8180 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
8181 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
8183 * Start this program, then from psql do this four times:
8185 * INSERT INTO TESTLIBPQ2.TBL1 VALUES (10);
8189 #include <windows.h>
8195 #include <sys/time.h>
8196 #include <sys/types.h>
8197 #ifdef HAVE_SYS_SELECT_H
8198 #include <sys/select.h>
8201 #include "libpq-fe.h"
8204 exit_nicely(PGconn *conn)
8211 main(int argc, char **argv)
8213 const char *conninfo;
8220 * If the user supplies a parameter on the command line, use it as the
8221 * conninfo string; otherwise default to setting dbname=postgres and using
8222 * environment variables or defaults for all other connection parameters.
8227 conninfo = "dbname = postgres";
8229 /* Make a connection to the database */
8230 conn = PQconnectdb(conninfo);
8232 /* Check to see that the backend connection was successfully made */
8233 if (PQstatus(conn) != CONNECTION_OK)
8235 fprintf(stderr, "Connection to database failed: %s",
8236 PQerrorMessage(conn));
8240 /* Set always-secure search path, so malicious users can't take control. */
8242 "SELECT pg_catalog.set_config('search_path', '', false)");
8243 if (PQresultStatus(res) != PGRES_TUPLES_OK)
8245 fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
8251 * Should PQclear PGresult whenever it is no longer needed to avoid memory
8257 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
8259 res = PQexec(conn, "LISTEN TBL2");
8260 if (PQresultStatus(res) != PGRES_COMMAND_OK)
8262 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
8268 /* Quit after four notifies are received. */
8270 while (nnotifies < 4)
8273 * Sleep until something happens on the connection. We use select(2)
8274 * to wait for input, but you could also use poll() or similar
8280 sock = PQsocket(conn);
8283 break; /* shouldn't happen */
8285 FD_ZERO(&input_mask);
8286 FD_SET(sock, &input_mask);
8288 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
8290 fprintf(stderr, "select() failed: %s\n", strerror(errno));
8294 /* Now check for input */
8295 PQconsumeInput(conn);
8296 while ((notify = PQnotifies(conn)) != NULL)
8299 "ASYNC NOTIFY of '%s' received from backend PID %d\n",
8300 notify->relname, notify->be_pid);
8303 PQconsumeInput(conn);
8307 fprintf(stderr, "Done.\n");
8309 /* close the connection to the database and cleanup */
8318 <example id="libpq-example-3">
8319 <title><application>libpq</application> Example Program 3</title>
8324 * src/test/examples/testlibpq3.c
8328 * Test out-of-line parameters and binary I/O.
8330 * Before running this, populate a database with the following commands
8331 * (provided in src/test/examples/testlibpq3.sql):
8333 * CREATE SCHEMA testlibpq3;
8334 * SET search_path = testlibpq3;
8335 * CREATE TABLE test1 (i int4, t text, b bytea);
8336 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
8337 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
8339 * The expected output is:
8343 * t = (11 bytes) 'joe's place'
8344 * b = (5 bytes) \000\001\002\003\004
8348 * t = (8 bytes) 'ho there'
8349 * b = (5 bytes) \004\003\002\001\000
8353 #include <windows.h>
8360 #include <sys/types.h>
8361 #include "libpq-fe.h"
8363 /* for ntohl/htonl */
8364 #include <netinet/in.h>
8365 #include <arpa/inet.h>
8369 exit_nicely(PGconn *conn)
8376 * This function prints a query result that is a binary-format fetch from
8377 * a table defined as in the comment above. We split it out because the
8378 * main() function uses it twice.
8381 show_binary_results(PGresult *res)
8389 /* Use PQfnumber to avoid assumptions about field order in result */
8390 i_fnum = PQfnumber(res, "i");
8391 t_fnum = PQfnumber(res, "t");
8392 b_fnum = PQfnumber(res, "b");
8394 for (i = 0; i < PQntuples(res); i++)
8402 /* Get the field values (we ignore possibility they are null!) */
8403 iptr = PQgetvalue(res, i, i_fnum);
8404 tptr = PQgetvalue(res, i, t_fnum);
8405 bptr = PQgetvalue(res, i, b_fnum);
8408 * The binary representation of INT4 is in network byte order, which
8409 * we'd better coerce to the local byte order.
8411 ival = ntohl(*((uint32_t *) iptr));
8414 * The binary representation of TEXT is, well, text, and since libpq
8415 * was nice enough to append a zero byte to it, it'll work just fine
8418 * The binary representation of BYTEA is a bunch of bytes, which could
8419 * include embedded nulls so we have to pay attention to field length.
8421 blen = PQgetlength(res, i, b_fnum);
8423 printf("tuple %d: got\n", i);
8424 printf(" i = (%d bytes) %d\n",
8425 PQgetlength(res, i, i_fnum), ival);
8426 printf(" t = (%d bytes) '%s'\n",
8427 PQgetlength(res, i, t_fnum), tptr);
8428 printf(" b = (%d bytes) ", blen);
8429 for (j = 0; j < blen; j++)
8430 printf("\\%03o", bptr[j]);
8436 main(int argc, char **argv)
8438 const char *conninfo;
8441 const char *paramValues[1];
8442 int paramLengths[1];
8443 int paramFormats[1];
8444 uint32_t binaryIntVal;
8447 * If the user supplies a parameter on the command line, use it as the
8448 * conninfo string; otherwise default to setting dbname=postgres and using
8449 * environment variables or defaults for all other connection parameters.
8454 conninfo = "dbname = postgres";
8456 /* Make a connection to the database */
8457 conn = PQconnectdb(conninfo);
8459 /* Check to see that the backend connection was successfully made */
8460 if (PQstatus(conn) != CONNECTION_OK)
8462 fprintf(stderr, "Connection to database failed: %s",
8463 PQerrorMessage(conn));
8467 /* Set always-secure search path, so malicious users can't take control. */
8468 res = PQexec(conn, "SET search_path = testlibpq3");
8469 if (PQresultStatus(res) != PGRES_COMMAND_OK)
8471 fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
8478 * The point of this program is to illustrate use of PQexecParams() with
8479 * out-of-line parameters, as well as binary transmission of data.
8481 * This first example transmits the parameters as text, but receives the
8482 * results in binary format. By using out-of-line parameters we can avoid
8483 * a lot of tedious mucking about with quoting and escaping, even though
8484 * the data is text. Notice how we don't have to do anything special with
8485 * the quote mark in the parameter value.
8488 /* Here is our out-of-line parameter value */
8489 paramValues[0] = "joe's place";
8491 res = PQexecParams(conn,
8492 "SELECT * FROM test1 WHERE t = $1",
8494 NULL, /* let the backend deduce param type */
8496 NULL, /* don't need param lengths since text */
8497 NULL, /* default to all text params */
8498 1); /* ask for binary results */
8500 if (PQresultStatus(res) != PGRES_TUPLES_OK)
8502 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
8507 show_binary_results(res);
8512 * In this second example we transmit an integer parameter in binary form,
8513 * and again retrieve the results in binary form.
8515 * Although we tell PQexecParams we are letting the backend deduce
8516 * parameter type, we really force the decision by casting the parameter
8517 * symbol in the query text. This is a good safety measure when sending
8518 * binary parameters.
8521 /* Convert integer value "2" to network byte order */
8522 binaryIntVal = htonl((uint32_t) 2);
8524 /* Set up parameter arrays for PQexecParams */
8525 paramValues[0] = (char *) &binaryIntVal;
8526 paramLengths[0] = sizeof(binaryIntVal);
8527 paramFormats[0] = 1; /* binary */
8529 res = PQexecParams(conn,
8530 "SELECT * FROM test1 WHERE i = $1::int4",
8532 NULL, /* let the backend deduce param type */
8536 1); /* ask for binary results */
8538 if (PQresultStatus(res) != PGRES_TUPLES_OK)
8540 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
8545 show_binary_results(res);
8549 /* close the connection to the database and cleanup */
projects / postgresql / blob