1 <!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.269 2008/11/13 09:45:24 mha Exp $ -->
4 <title><application>libpq</application> - C Library</title>
6 <indexterm zone="libpq">
7 <primary>libpq</primary>
10 <indexterm zone="libpq">
15 <application>libpq</application> is the <acronym>C</acronym>
16 application programmer's interface to <productname>PostgreSQL</>.
17 <application>libpq</> is a set of library functions that allow
18 client programs to pass queries to the <productname>PostgreSQL</>
19 backend server and to receive the results of these queries.
23 <application>libpq</> is also the underlying engine for several
24 other <productname>PostgreSQL</> application interfaces, including
25 those written for C++, Perl, Python, Tcl and <application>ECPG</>.
26 So some aspects of <application>libpq</>'s behavior will be
27 important to you if you use one of those packages. In particular,
28 <xref linkend="libpq-envars">,
29 <xref linkend="libpq-pgpass"> and
30 <xref linkend="libpq-ssl">
31 describe behavior that is visible to the user of any application
32 that uses <application>libpq</>.
36 Some short programs are included at the end of this chapter (<xref linkend="libpq-example">) to show how
37 to write programs that use <application>libpq</application>. There are also several
38 complete examples of <application>libpq</application> applications in the
39 directory <filename>src/test/examples</filename> in the source code distribution.
43 Client programs that use <application>libpq</application> must
44 include the header file
45 <filename>libpq-fe.h</filename><indexterm><primary>libpq-fe.h</></>
46 and must link with the <application>libpq</application> library.
49 <sect1 id="libpq-connect">
50 <title>Database Connection Control Functions</title>
53 The following functions deal with making a connection to a
54 <productname>PostgreSQL</productname> backend server. An
55 application program can have several backend connections open at
56 one time. (One reason to do that is to access more than one
57 database.) Each connection is represented by a
58 <structname>PGconn</><indexterm><primary>PGconn</></> object, which
59 is obtained from the function <function>PQconnectdb</> or
60 <function>PQsetdbLogin</>. Note that these functions will always
61 return a non-null object pointer, unless perhaps there is too
62 little memory even to allocate the <structname>PGconn</> object.
63 The <function>PQstatus</> function should be called to check
64 whether a connection was successfully made before queries are sent
65 via the connection object.
69 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
72 Makes a new connection to the database server.
75 PGconn *PQconnectdb(const char *conninfo);
80 This function opens a new database connection using the parameters taken
81 from the string <literal>conninfo</literal>. Unlike <function>PQsetdbLogin</> below,
82 the parameter set can be extended without changing the function signature,
83 so use of this function (or its nonblocking analogues <function>PQconnectStart</>
84 and <function>PQconnectPoll</function>) is preferred for new application programming.
89 can be empty to use all default parameters, or it can contain one or more
90 parameter settings separated by whitespace.
91 Each parameter setting is in the form <literal>keyword = value</literal>.
92 Spaces around the equal sign are optional.
93 To write an empty value or a value containing
94 spaces, surround it with single quotes, e.g.,
95 <literal>keyword = 'a value'</literal>.
96 Single quotes and backslashes within the value must be escaped with a
97 backslash, i.e., <literal>\'</literal> and <literal>\\</literal>.
101 The currently recognized parameter key words are:
105 <term><literal>host</literal></term>
108 Name of host to connect to.<indexterm><primary>host name</></>
109 If this begins with a slash, it specifies Unix-domain
110 communication rather than TCP/IP communication; the value is the
111 name of the directory in which the socket file is stored. The
112 default behavior when <literal>host</literal> is not specified
113 is to connect to a Unix-domain
114 socket<indexterm><primary>Unix domain socket</></> in
115 <filename>/tmp</filename> (or whatever socket directory was specified
116 when <productname>PostgreSQL</> was built). On machines without
117 Unix-domain sockets, the default is to connect to <literal>localhost</>.
123 <term><literal>hostaddr</literal></term>
126 Numeric IP address of host to connect to. This should be in the
127 standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
128 your machine supports IPv6, you can also use those addresses.
129 TCP/IP communication is
130 always used when a nonempty string is specified for this parameter.
134 Using <literal>hostaddr</> instead of <literal>host</> allows the
135 application to avoid a host name look-up, which might be important in
136 applications with time constraints. However, Kerberos and GSSAPI authentication
137 requires the host name. The following therefore applies: If
138 <literal>host</> is specified without <literal>hostaddr</>, a host name
139 lookup occurs. If <literal>hostaddr</> is specified without
140 <literal>host</>, the value for <literal>hostaddr</> gives the remote
141 address. When Kerberos is used, a reverse name query occurs to obtain
142 the host name for Kerberos. If both
143 <literal>host</> and <literal>hostaddr</> are specified, the value for
144 <literal>hostaddr</> gives the remote address; the value for
145 <literal>host</> is ignored, unless Kerberos is used, in which case that
146 value is used for Kerberos authentication. (Note that authentication is
147 likely to fail if <application>libpq</application> is passed a host name
148 that is not the name of the machine at <literal>hostaddr</>.) Also,
149 <literal>host</> rather than <literal>hostaddr</> is used to identify
150 the connection in <filename>~/.pgpass</> (see
151 <xref linkend="libpq-pgpass">).
155 Without either a host name or host address,
156 <application>libpq</application> will connect using a
157 local Unix-domain socket; or on machines without Unix-domain
158 sockets, it will attempt to connect to <literal>localhost</>.
164 <term><literal>port</literal></term>
167 Port number to connect to at the server host, or socket file
168 name extension for Unix-domain
169 connections.<indexterm><primary>port</></>
175 <term><literal>dbname</literal></term>
178 The database name. Defaults to be the same as the user name.
184 <term><literal>user</literal></term>
187 <productname>PostgreSQL</productname> user name to connect as.
188 Defaults to be the same as the operating system name of the user
189 running the application.
195 <term><literal>password</literal></term>
198 Password to be used if the server demands password authentication.
204 <term><literal>connect_timeout</literal></term>
207 Maximum wait for connection, in seconds (write as a decimal integer
208 string). Zero or not specified means wait indefinitely. It is not
209 recommended to use a timeout of less than 2 seconds.
215 <term><literal>options</literal></term>
218 Command-line options to be sent to the server.
224 <term><literal>tty</literal></term>
227 Ignored (formerly, this specified where to send server debug output).
233 <term><literal>sslmode</literal></term>
236 This option determines whether or with what priority a
237 <acronym>SSL</> TCP/IP connection will be negotiated with the
238 server. There are four modes: <literal>disable</> will attempt
239 only an unencrypted <acronym>SSL</> connection;
240 <literal>allow</> will negotiate, trying first a
241 non-<acronym>SSL</> connection, then if that fails, trying an
242 <acronym>SSL</> connection; <literal>prefer</> (the default)
243 will negotiate, trying first an <acronym>SSL</> connection,
244 then if that fails, trying a regular non-<acronym>SSL</>
245 connection; <literal>require</> will try only an
246 <acronym>SSL</> connection. <literal>sslmode</> is ignored
247 for Unix domain socket communication.
251 If <productname>PostgreSQL</> is compiled without SSL support,
252 using option <literal>require</> will cause an error, while
253 options <literal>allow</> and <literal>prefer</> will be
254 accepted but <application>libpq</> will not in fact attempt
256 connection.<indexterm><primary>SSL</><secondary
257 sortas="libpq">with libpq</></indexterm>
263 <term><literal>sslverify</literal></term>
266 This option controls how libpq verifies the certificate on the
267 server when performing an <acronym>SSL</> connection. There are
268 three options: <literal>none</> disables verification completely
269 (not recommended!); <literal>cert</> enables verification that
270 the certificate chains to a known CA only; <literal>cn</> will
271 both verify that the certificate chains to a known CA and that
272 the <literal>cn</> attribute of the certificate matches the
273 hostname the connection is being made to (default).
277 It is always recommended to use the <literal>cn</> value for
278 this parameter, since this is the only option that prevents
279 man-in-the-middle attacks. Note that this requires the server
280 name on the certificate to match exactly with the host name
281 used for the connection, and therefore does not support connections
282 to aliased names. It can be used with pure IP address connections
283 only if the certificate also has just the IP address in the
284 <literal>cn</> field.
290 <term><literal>requiressl</literal></term>
293 This option is deprecated in favor of the <literal>sslmode</>
298 If set to 1, an <acronym>SSL</acronym> connection to the server
299 is required (this is equivalent to <literal>sslmode</>
300 <literal>require</>). <application>libpq</> will then refuse
301 to connect if the server does not accept an
302 <acronym>SSL</acronym> connection. If set to 0 (default),
303 <application>libpq</> will negotiate the connection type with
304 the server (equivalent to <literal>sslmode</>
305 <literal>prefer</>). This option is only available if
306 <productname>PostgreSQL</> is compiled with SSL support.
312 <term><literal>krbsrvname</literal></term>
315 Kerberos service name to use when authenticating with Kerberos 5
317 This must match the service name specified in the server
318 configuration for Kerberos authentication to succeed. (See also
319 <xref linkend="kerberos-auth"> and <xref linkend="gssapi-auth">.)
325 <term><literal>gsslib</literal></term>
328 GSS library to use for GSSAPI authentication. Only used on Windows.
329 Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
330 library for authentication instead of the default SSPI.
336 <term><literal>service</literal></term>
339 Service name to use for additional parameters. It specifies a service
340 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
341 This allows applications to specify only a service name so connection parameters
342 can be centrally maintained. See <xref linkend="libpq-pgservice">.
348 If any parameter is unspecified, then the corresponding
349 environment variable (see <xref linkend="libpq-envars">)
350 is checked. If the environment variable is not set either,
351 then the indicated built-in defaults are used.
357 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
360 Makes a new connection to the database server.
362 PGconn *PQsetdbLogin(const char *pghost,
364 const char *pgoptions,
373 This is the predecessor of <function>PQconnectdb</function> with a fixed
374 set of parameters. It has the same functionality except that the
375 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
376 empty string for any one of the fixed parameters that is to be defaulted.
380 If the <parameter>dbName</parameter> contains an <symbol>=</symbol> sign, it
381 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
382 if it had been passed to <function>PQconnectdb</function>, and the remaining
383 parameters are then applied as above.
389 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
392 Makes a new connection to the database server.
394 PGconn *PQsetdb(char *pghost,
403 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
404 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
405 for backward compatibility with very old programs.
411 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
412 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
415 <indexterm><primary>nonblocking connection</primary></indexterm>
416 Make a connection to the database server in a nonblocking manner.
419 PGconn *PQconnectStart(const char *conninfo);
423 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
428 These two functions are used to open a connection to a database server such
429 that your application's thread of execution is not blocked on remote I/O
431 The point of this approach is that the waits for I/O to complete can occur
432 in the application's main loop, rather than down inside
433 <function>PQconnectdb</>, and so the application can manage this
434 operation in parallel with other activities.
438 The database connection is made using the parameters taken from the string
439 <literal>conninfo</literal>, passed to <function>PQconnectStart</function>. This string is in
440 the same format as described above for <function>PQconnectdb</function>.
443 Neither <function>PQconnectStart</function> nor <function>PQconnectPoll</function> will block, so long as a number of
444 restrictions are met:
448 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
449 name and reverse name queries are not made. See the documentation of
450 these parameters under <function>PQconnectdb</function> above for details.
456 If you call <function>PQtrace</function>, ensure that the stream object
457 into which you trace will not block.
463 You ensure that the socket is in the appropriate state
464 before calling <function>PQconnectPoll</function>, as described below.
471 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
472 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
473 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
474 representing a valid connection to the database). On return from
475 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
476 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
480 If <function>PQconnectStart</> succeeds, the next stage is to poll
481 <application>libpq</> so that it can proceed with the connection sequence.
482 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
483 socket underlying the database connection.
484 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
485 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
486 read (as indicated by <function>select()</>, <function>poll()</>, or
487 similar system function).
488 Then call <function>PQconnectPoll(conn)</function> again.
489 Conversely, if <function>PQconnectPoll(conn)</function> last returned
490 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
491 to write, then call <function>PQconnectPoll(conn)</function> again.
492 If you have yet to call
493 <function>PQconnectPoll</function>, i.e., just after the call to
494 <function>PQconnectStart</function>, behave as if it last returned
495 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
496 <function>PQconnectPoll(conn)</function> returns
497 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
498 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
499 has been successfully made.
503 At any time during connection, the status of the connection can be
504 checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
505 connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
506 connection is ready. Both of these states are equally detectable
507 from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
508 during (and only during) an asynchronous connection procedure. These
509 indicate the current stage of the connection procedure and might be useful
510 to provide feedback to the user for example. These statuses are:
514 <term><symbol>CONNECTION_STARTED</symbol></term>
517 Waiting for connection to be made.
523 <term><symbol>CONNECTION_MADE</symbol></term>
526 Connection OK; waiting to send.
532 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
535 Waiting for a response from the server.
541 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
544 Received authentication; waiting for backend start-up to finish.
550 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
553 Negotiating SSL encryption.
559 <term><symbol>CONNECTION_SETENV</symbol></term>
562 Negotiating environment-driven parameter settings.
568 Note that, although these constants will remain (in order to maintain
569 compatibility), an application should never rely upon these occurring in a
570 particular order, or at all, or on the status always being one of these
571 documented values. An application might do something like this:
573 switch(PQstatus(conn))
575 case CONNECTION_STARTED:
576 feedback = "Connecting...";
579 case CONNECTION_MADE:
580 feedback = "Connected to server...";
586 feedback = "Connecting...";
592 The <literal>connect_timeout</literal> connection parameter is ignored
593 when using <function>PQconnectPoll</function>; it is the application's
594 responsibility to decide whether an excessive amount of time has elapsed.
595 Otherwise, <function>PQconnectStart</function> followed by a
596 <function>PQconnectPoll</function> loop is equivalent to
597 <function>PQconnectdb</function>.
601 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
602 <function>PQfinish</function> when you are finished with it, in order to dispose of
603 the structure and any associated memory blocks. This must be done even if
604 the connection attempt fails or is abandoned.
610 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
613 Returns the default connection options.
615 PQconninfoOption *PQconndefaults(void);
619 char *keyword; /* The keyword of the option */
620 char *envvar; /* Fallback environment variable name */
621 char *compiled; /* Fallback compiled in default value */
622 char *val; /* Option's current value, or NULL */
623 char *label; /* Label for field in connect dialog */
624 char *dispchar; /* Indicates how to display this field
625 in a connect dialog. Values are:
626 "" Display entered value as is
627 "*" Password field - hide value
628 "D" Debug option - don't show by default */
629 int dispsize; /* Field size in characters for dialog */
635 Returns a connection options array. This can be used to determine
636 all possible <function>PQconnectdb</function> options and their
637 current default values. The return value points to an array of
638 <structname>PQconninfoOption</structname> structures, which ends
639 with an entry having a null <structfield>keyword</> pointer. The
640 null pointer is returned if memory could not be allocated. Note that
641 the current default values (<structfield>val</structfield> fields)
642 will depend on environment variables and other context. Callers
643 must treat the connection options data as read-only.
647 After processing the options array, free it by passing it to
648 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
649 is leaked for each call to <function>PQconndefaults</function>.
656 <term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</></></term>
659 Returns parsed connection options from the provided connection string.
662 PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
667 Parses a connection string and returns the resulting options as an
668 array; or returns NULL if there is a problem with the connection
669 string. This can be used to determine
670 the <function>PQconnectdb</function> options in the provided
671 connection string. The return value points to an array of
672 <structname>PQconninfoOption</structname> structures, which ends
673 with an entry having a null <structfield>keyword</> pointer.
677 Note that only options explicitly specified in the string will have
678 values set in the result array; no defaults are inserted.
682 If <literal>errmsg</> is not NULL, then <literal>*errmsg</> is set
683 to NULL on success, else to a malloc'd error string explaining
684 the problem. (It is also possible for <literal>*errmsg</> to be
685 set to NULL even when NULL is returned; this indicates an out-of-memory
690 After processing the options array, free it by passing it to
691 <function>PQconninfoFree</function>. If this is not done, some memory
692 is leaked for each call to <function>PQconninfoParse</function>.
693 Conversely, if an error occurs and <literal>errmsg</> is not NULL,
694 be sure to free the error string using <function>PQfreemem</>.
701 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
704 Closes the connection to the server. Also frees
705 memory used by the <structname>PGconn</structname> object.
707 void PQfinish(PGconn *conn);
712 Note that even if the server connection attempt fails (as
713 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
714 to free the memory used by the <structname>PGconn</structname> object.
715 The <structname>PGconn</> pointer must not be used again after
716 <function>PQfinish</function> has been called.
722 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
725 Resets the communication channel to the server.
727 void PQreset(PGconn *conn);
732 This function will close the connection
733 to the server and attempt to reestablish a new
734 connection to the same server, using all the same
735 parameters previously used. This might be useful for
736 error recovery if a working connection is lost.
742 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
743 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
746 Reset the communication channel to the server, in a nonblocking manner.
749 int PQresetStart(PGconn *conn);
752 PostgresPollingStatusType PQresetPoll(PGconn *conn);
757 These functions will close the connection to the server and attempt to
758 reestablish a new connection to the same server, using all the same
759 parameters previously used. This can be useful for error recovery if a
760 working connection is lost. They differ from <function>PQreset</function> (above) in that they
761 act in a nonblocking manner. These functions suffer from the same
762 restrictions as <function>PQconnectStart</> and <function>PQconnectPoll</>.
766 To initiate a connection reset, call
767 <function>PQresetStart</function>. If it returns 0, the reset has
768 failed. If it returns 1, poll the reset using
769 <function>PQresetPoll</function> in exactly the same way as you
770 would create the connection using <function>PQconnectPoll</function>.
779 <sect1 id="libpq-status">
780 <title>Connection Status Functions</title>
783 These functions can be used to interrogate the status
784 of an existing database connection object.
789 <indexterm><primary>libpq-fe.h</></>
790 <indexterm><primary>libpq-int.h</></>
791 <application>libpq</application> application programmers should be careful to
792 maintain the <structname>PGconn</structname> abstraction. Use the accessor
793 functions described below to get at the contents of <structname>PGconn</structname>.
794 Reference to internal <structname>PGconn</structname> fields using
795 <filename>libpq-int.h</> is not recommended because they are subject to change
801 The following functions return parameter values established at connection.
802 These values are fixed for the life of the <structname>PGconn</> object.
807 <function>PQdb</function>
809 <primary>PQdb</primary>
815 Returns the database name of the connection.
817 char *PQdb(const PGconn *conn);
825 <function>PQuser</function>
827 <primary>PQuser</primary>
833 Returns the user name of the connection.
835 char *PQuser(const PGconn *conn);
843 <function>PQpass</function>
845 <primary>PQpass</primary>
851 Returns the password of the connection.
853 char *PQpass(const PGconn *conn);
861 <function>PQhost</function>
863 <primary>PQhost</primary>
869 Returns the server host name of the connection.
871 char *PQhost(const PGconn *conn);
879 <function>PQport</function>
881 <primary>PQport</primary>
887 Returns the port of the connection.
890 char *PQport(const PGconn *conn);
898 <function>PQtty</function>
900 <primary>PQtty</primary>
906 Returns the debug <acronym>TTY</acronym> of the connection.
907 (This is obsolete, since the server no longer pays attention
908 to the <acronym>TTY</acronym> setting, but the function remains
909 for backwards compatibility.)
912 char *PQtty(const PGconn *conn);
920 <function>PQoptions</function>
922 <primary>PQoptions</primary>
928 Returns the command-line options passed in the connection request.
930 char *PQoptions(const PGconn *conn);
939 The following functions return status data that can change as operations
940 are executed on the <structname>PGconn</> object.
945 <function>PQstatus</function>
947 <primary>PQstatus</primary>
953 Returns the status of the connection.
955 ConnStatusType PQstatus(const PGconn *conn);
960 The status can be one of a number of values. However, only two of
961 these are seen outside of an asynchronous connection procedure:
962 <literal>CONNECTION_OK</literal> and
963 <literal>CONNECTION_BAD</literal>. A good connection to the database
964 has the status <literal>CONNECTION_OK</literal>. A failed
965 connection attempt is signaled by status
966 <literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
967 remain so until <function>PQfinish</function>, but a communications
968 failure might result in the status changing to
969 <literal>CONNECTION_BAD</literal> prematurely. In that case the
970 application could try to recover by calling
971 <function>PQreset</function>.
975 See the entry for <function>PQconnectStart</> and <function>PQconnectPoll</> with regards
976 to other status codes
984 <function>PQtransactionStatus</function>
986 <primary>PQtransactionStatus</primary>
992 Returns the current in-transaction status of the server.
995 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
998 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
999 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
1000 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
1001 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
1002 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
1003 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
1004 has been sent to the server and not yet completed.
1009 <function>PQtransactionStatus</> will give incorrect results when using
1010 a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
1011 set to off. The server-side autocommit feature has been
1012 deprecated and does not exist in later server versions.
1020 <function>PQparameterStatus</function>
1022 <primary>PQparameterStatus</primary>
1028 Looks up a current parameter setting of the server.
1031 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
1034 Certain parameter values are reported by the server automatically at
1035 connection startup or whenever their values change.
1036 <function>PQparameterStatus</> can be used to interrogate these settings.
1037 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
1038 if the parameter is not known.
1042 Parameters reported as of the current release include
1043 <literal>server_version</>,
1044 <literal>server_encoding</>,
1045 <literal>client_encoding</>,
1046 <literal>is_superuser</>,
1047 <literal>session_authorization</>,
1048 <literal>DateStyle</>,
1049 <literal>IntervalStyle</>,
1050 <literal>TimeZone</>,
1051 <literal>integer_datetimes</>, and
1052 <literal>standard_conforming_strings</>.
1053 (<literal>server_encoding</>, <literal>TimeZone</>, and
1054 <literal>integer_datetimes</> were not reported by releases before 8.0;
1055 <literal>standard_conforming_strings</> was not reported by releases
1056 before 8.1; <literal>IntervalStyle</> was not reported by releases
1059 <literal>server_version</>,
1060 <literal>server_encoding</> and
1061 <literal>integer_datetimes</>
1062 cannot change after startup.
1066 Pre-3.0-protocol servers do not report parameter settings, but
1067 <application>libpq</> includes logic to obtain values for
1068 <literal>server_version</> and <literal>client_encoding</> anyway.
1069 Applications are encouraged to use <function>PQparameterStatus</>
1070 rather than <foreignphrase>ad hoc</> code to determine these values.
1071 (Beware however that on a pre-3.0 connection, changing
1072 <literal>client_encoding</> via <command>SET</> after connection
1073 startup will not be reflected by <function>PQparameterStatus</>.)
1074 For <literal>server_version</>, see also
1075 <function>PQserverVersion</>, which returns the information in a
1076 numeric form that is much easier to compare against.
1080 If no value for <literal>standard_conforming_strings</> is reported,
1081 applications can assume it is <literal>off</>, that is, backslashes
1082 are treated as escapes in string literals. Also, the presence of
1083 this parameter can be taken as an indication that the escape string
1084 syntax (<literal>E'...'</>) is accepted.
1088 Although the returned pointer is declared <literal>const</>, it in fact
1089 points to mutable storage associated with the <literal>PGconn</> structure.
1090 It is unwise to assume the pointer will remain valid across queries.
1097 <function>PQprotocolVersion</function>
1099 <primary>PQprotocolVersion</primary>
1105 Interrogates the frontend/backend protocol being used.
1107 int PQprotocolVersion(const PGconn *conn);
1109 Applications might wish to use this to determine whether certain
1110 features are supported. Currently, the possible values are 2 (2.0
1111 protocol), 3 (3.0 protocol), or zero (connection bad). This will
1112 not change after connection startup is complete, but it could
1113 theoretically change during a connection reset. The 3.0 protocol
1114 will normally be used when communicating with
1115 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
1116 support only protocol 2.0. (Protocol 1.0 is obsolete and not
1117 supported by <application>libpq</application>.)
1124 <function>PQserverVersion</function>
1126 <primary>PQserverVersion</primary>
1132 Returns an integer representing the backend version.
1134 int PQserverVersion(const PGconn *conn);
1136 Applications might use this to determine the version of the database
1137 server they are connected to. The number is formed by converting
1138 the major, minor, and revision numbers into two-decimal-digit
1139 numbers and appending them together. For example, version 8.1.5
1140 will be returned as 80105, and version 8.2 will be returned as
1141 80200 (leading zeroes are not shown). Zero is returned if the
1149 <function>PQerrorMessage</function>
1151 <primary>PQerrorMessage</primary>
1157 <indexterm><primary>error message</></> Returns the error message
1158 most recently generated by an operation on the connection.
1161 char *PQerrorMessage(const PGconn *conn);
1167 Nearly all <application>libpq</> functions will set a message for
1168 <function>PQerrorMessage</function> if they fail. Note that by
1169 <application>libpq</application> convention, a nonempty
1170 <function>PQerrorMessage</function> result can be multiple lines,
1171 and will include a trailing newline. The caller should not free
1172 the result directly. It will be freed when the associated
1173 <structname>PGconn</> handle is passed to
1174 <function>PQfinish</function>. The result string should not be
1175 expected to remain the same across operations on the
1176 <literal>PGconn</> structure.
1182 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1185 Obtains the file descriptor number of the connection socket to
1186 the server. A valid descriptor will be greater than or equal
1187 to 0; a result of -1 indicates that no server connection is
1188 currently open. (This will not change during normal operation,
1189 but could change during connection setup or reset.)
1192 int PQsocket(const PGconn *conn);
1200 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1203 Returns the process <acronym>ID</acronym>
1204 (PID)<indexterm><primary>PID</><secondary>determining PID of
1205 server process</><tertiary>in libpq</></> of the backend server
1206 process handling this connection.
1209 int PQbackendPID(const PGconn *conn);
1214 The backend <acronym>PID</acronym> is useful for debugging
1215 purposes and for comparison to <command>NOTIFY</command>
1216 messages (which include the <acronym>PID</acronym> of the
1217 notifying backend process). Note that the
1218 <acronym>PID</acronym> belongs to a process executing on the
1219 database server host, not the local host!
1225 <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
1228 Returns true (1) if the connection authentication method
1229 required a password, but none was available.
1230 Returns false (0) if not.
1233 int PQconnectionNeedsPassword(const PGconn *conn);
1238 This function can be applied after a failed connection attempt
1239 to decide whether to prompt the user for a password.
1245 <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
1248 Returns true (1) if the connection authentication method
1249 used a password. Returns false (0) if not.
1252 int PQconnectionUsedPassword(const PGconn *conn);
1257 This function can be applied after either a failed or successful
1258 connection attempt to detect whether the server demanded a password.
1264 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1267 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1268 Returns the SSL structure used in the connection, or null
1269 if SSL is not in use.
1272 SSL *PQgetssl(const PGconn *conn);
1277 This structure can be used to verify encryption levels, check server
1278 certificates, and more. Refer to the <productname>OpenSSL</>
1279 documentation for information about this structure.
1283 You must define <symbol>USE_SSL</symbol> in order to get the
1284 correct prototype for this function. Doing this will also
1285 automatically include <filename>ssl.h</filename> from <productname>OpenSSL</productname>.
1295 <sect1 id="libpq-exec">
1296 <title>Command Execution Functions</title>
1299 Once a connection to a database server has been successfully
1300 established, the functions described here are used to perform
1301 SQL queries and commands.
1304 <sect2 id="libpq-exec-main">
1305 <title>Main Functions</title>
1311 <function>PQexec</function>
1313 <primary>PQexec</primary>
1319 Submits a command to the server and waits for the result.
1322 PGresult *PQexec(PGconn *conn, const char *command);
1327 Returns a <structname>PGresult</structname> pointer or possibly a null
1328 pointer. A non-null pointer will generally be returned except in
1329 out-of-memory conditions or serious errors such as inability to send
1330 the command to the server. If a null pointer is returned, it should
1331 be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
1332 <function>PQerrorMessage</function> to get more information about such
1339 It is allowed to include multiple SQL commands (separated by semicolons)
1340 in the command string. Multiple queries sent in a single
1341 <function>PQexec</> call are processed in a single transaction, unless
1342 there are explicit <command>BEGIN</command>/<command>COMMIT</command>
1343 commands included in the query string to divide it into multiple
1344 transactions. Note however that the returned
1345 <structname>PGresult</structname> structure describes only the result
1346 of the last command executed from the string. Should one of the
1347 commands fail, processing of the string stops with it and the returned
1348 <structname>PGresult</structname> describes the error condition.
1355 <function>PQexecParams</function>
1357 <primary>PQexecParams</primary>
1363 Submits a command to the server and waits for the result,
1364 with the ability to pass parameters separately from the SQL
1368 PGresult *PQexecParams(PGconn *conn,
1369 const char *command,
1371 const Oid *paramTypes,
1372 const char * const *paramValues,
1373 const int *paramLengths,
1374 const int *paramFormats,
1380 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1381 functionality: parameter values can be specified separately from the command
1382 string proper, and query results can be requested in either text or binary
1383 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1384 connections; it will fail when using protocol 2.0.
1388 The function arguments are:
1392 <term><parameter>conn</parameter></term>
1396 The connection object to send the command through.
1402 <term><parameter>command</parameter></term>
1405 The SQL command string to be executed. If parameters are used,
1406 they are referred to in the command string as <literal>$1</>,
1407 <literal>$2</>, etc.
1413 <term><parameter>nParams</parameter></term>
1416 The number of parameters supplied; it is the length of the arrays
1417 <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1418 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1419 array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
1426 <term><parameter>paramTypes[]</parameter></term>
1429 Specifies, by OID, the data types to be assigned to the
1430 parameter symbols. If <parameter>paramTypes</> is
1431 <symbol>NULL</symbol>, or any particular element in the array
1432 is zero, the server infers a data type for the parameter symbol
1433 in the same way it would do for an untyped literal string.
1439 <term><parameter>paramValues[]</parameter></term>
1442 Specifies the actual values of the parameters. A null pointer
1443 in this array means the corresponding parameter is null;
1444 otherwise the pointer points to a zero-terminated text string
1445 (for text format) or binary data in the format expected by the
1446 server (for binary format).
1452 <term><parameter>paramLengths[]</parameter></term>
1455 Specifies the actual data lengths of binary-format parameters.
1456 It is ignored for null parameters and text-format parameters.
1457 The array pointer can be null when there are no binary parameters.
1463 <term><parameter>paramFormats[]</parameter></term>
1466 Specifies whether parameters are text (put a zero in the
1467 array entry for the corresponding parameter) or binary (put
1468 a one in the array entry for the corresponding parameter).
1469 If the array pointer is null then all parameters are presumed
1473 Values passed in binary format require knowlege of
1474 the internal representation expected by the backend.
1475 For example, integers must be passed in network byte
1476 order. Passing <type>numeric</> values requires
1477 knowledge of the server storage format, as implemented
1479 <filename>src/backend/utils/adt/numeric.c::numeric_send()</> and
1480 <filename>src/backend/utils/adt/numeric.c::numeric_recv()</>.
1486 <term><parameter>resultFormat</parameter></term>
1489 Specify zero to obtain results in text format, or one to obtain
1490 results in binary format. (There is not currently a provision
1491 to obtain different result columns in different formats,
1492 although that is possible in the underlying protocol.)
1504 The primary advantage of <function>PQexecParams</> over
1505 <function>PQexec</> is that parameter values can be separated from the
1506 command string, thus avoiding the need for tedious and error-prone
1507 quoting and escaping.
1511 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
1512 one SQL command in the given string. (There can be semicolons in it,
1513 but not more than one nonempty command.) This is a limitation of the
1514 underlying protocol, but has some usefulness as an extra defense against
1515 SQL-injection attacks.
1520 Specifying parameter types via OIDs is tedious, particularly if you prefer
1521 not to hard-wire particular OID values into your program. However, you can
1522 avoid doing so even in cases where the server by itself cannot determine the
1523 type of the parameter, or chooses a different type than you want. In the
1524 SQL command text, attach an explicit cast to the parameter symbol to show what
1525 data type you will send. For example:
1527 SELECT * FROM mytable WHERE x = $1::bigint;
1529 This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
1530 by default it would be assigned the same type as <literal>x</>. Forcing the
1531 parameter type decision, either this way or by specifying a numeric type OID,
1532 is strongly recommended when sending parameter values in binary format, because
1533 binary format has less redundancy than text format and so there is less chance
1534 that the server will detect a type mismatch mistake for you.
1541 <term><function>PQprepare</function>
1543 <primary>PQprepare</primary>
1549 Submits a request to create a prepared statement with the
1550 given parameters, and waits for completion.
1552 PGresult *PQprepare(PGconn *conn,
1553 const char *stmtName,
1556 const Oid *paramTypes);
1561 <function>PQprepare</> creates a prepared statement for later
1562 execution with <function>PQexecPrepared</>. This feature allows
1563 commands that will be used repeatedly to be parsed and planned just
1564 once, rather than each time they are executed.
1565 <function>PQprepare</> is supported only in protocol 3.0 and later
1566 connections; it will fail when using protocol 2.0.
1570 The function creates a prepared statement named
1571 <parameter>stmtName</> from the <parameter>query</> string, which
1572 must contain a single SQL command. <parameter>stmtName</> can be
1573 <literal>""</> to create an unnamed statement, in which case any
1574 pre-existing unnamed statement is automatically replaced; otherwise
1575 it is an error if the statement name is already defined in the
1576 current session. If any parameters are used, they are referred
1577 to in the query as <literal>$1</>, <literal>$2</>, etc.
1578 <parameter>nParams</> is the number of parameters for which types
1579 are pre-specified in the array <parameter>paramTypes[]</>. (The
1580 array pointer can be <symbol>NULL</symbol> when
1581 <parameter>nParams</> is zero.) <parameter>paramTypes[]</>
1582 specifies, by OID, the data types to be assigned to the parameter
1583 symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1584 or any particular element in the array is zero, the server assigns
1585 a data type to the parameter symbol in the same way it would do
1586 for an untyped literal string. Also, the query can use parameter
1587 symbols with numbers higher than <parameter>nParams</>; data types
1588 will be inferred for these symbols as well. (See
1589 <function>PQdescribePrepared</function> for a means to find out
1590 what data types were inferred.)
1594 As with <function>PQexec</>, the result is normally a
1595 <structname>PGresult</structname> object whose contents indicate
1596 server-side success or failure. A null result indicates
1597 out-of-memory or inability to send the command at all. Use
1598 <function>PQerrorMessage</function> to get more information about
1605 Prepared statements for use with <function>PQexecPrepared</> can also
1606 be created by executing SQL <xref linkend="sql-prepare"
1607 endterm="sql-prepare-title"> statements. (But <function>PQprepare</>
1608 is more flexible since it does not require parameter types to be
1609 pre-specified.) Also, although there is no <application>libpq</>
1610 function for deleting a prepared statement, the SQL <xref
1611 linkend="sql-deallocate" endterm="sql-deallocate-title"> statement
1612 can be used for that purpose.
1619 <function>PQexecPrepared</function>
1621 <primary>PQexecPrepared</primary>
1627 Sends a request to execute a prepared statement with given
1628 parameters, and waits for the result.
1630 PGresult *PQexecPrepared(PGconn *conn,
1631 const char *stmtName,
1633 const char * const *paramValues,
1634 const int *paramLengths,
1635 const int *paramFormats,
1641 <function>PQexecPrepared</> is like <function>PQexecParams</>,
1642 but the command to be executed is specified by naming a
1643 previously-prepared statement, instead of giving a query string.
1644 This feature allows commands that will be used repeatedly to be
1645 parsed and planned just once, rather than each time they are
1646 executed. The statement must have been prepared previously in
1647 the current session. <function>PQexecPrepared</> is supported
1648 only in protocol 3.0 and later connections; it will fail when
1653 The parameters are identical to <function>PQexecParams</>, except that the
1654 name of a prepared statement is given instead of a query string, and the
1655 <parameter>paramTypes[]</> parameter is not present (it is not needed since
1656 the prepared statement's parameter types were determined when it was created).
1663 <function>PQdescribePrepared</function>
1665 <primary>PQdescribePrepared</primary>
1671 Submits a request to obtain information about the specified
1672 prepared statement, and waits for completion.
1674 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
1679 <function>PQdescribePrepared</> allows an application to obtain
1680 information about a previously prepared statement.
1681 <function>PQdescribePrepared</> is supported only in protocol 3.0
1682 and later connections; it will fail when using protocol 2.0.
1686 <parameter>stmtName</> can be <literal>""</> or NULL to reference
1687 the unnamed statement, otherwise it must be the name of an existing
1688 prepared statement. On success, a <structname>PGresult</> with
1689 status <literal>PGRES_COMMAND_OK</literal> is returned. The
1690 functions <function>PQnparams</function> and
1691 <function>PQparamtype</function> can be applied to this
1692 <structname>PGresult</> to obtain information about the parameters
1693 of the prepared statement, and the functions
1694 <function>PQnfields</function>, <function>PQfname</function>,
1695 <function>PQftype</function>, etc provide information about the
1696 result columns (if any) of the statement.
1703 <function>PQdescribePortal</function>
1705 <primary>PQdescribePortal</primary>
1711 Submits a request to obtain information about the specified
1712 portal, and waits for completion.
1714 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
1719 <function>PQdescribePortal</> allows an application to obtain
1720 information about a previously created portal.
1721 (<application>libpq</> does not provide any direct access to
1722 portals, but you can use this function to inspect the properties
1723 of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
1724 <function>PQdescribePortal</> is supported only in protocol 3.0
1725 and later connections; it will fail when using protocol 2.0.
1729 <parameter>portalName</> can be <literal>""</> or NULL to reference
1730 the unnamed portal, otherwise it must be the name of an existing
1731 portal. On success, a <structname>PGresult</> with status
1732 <literal>PGRES_COMMAND_OK</literal> is returned. The functions
1733 <function>PQnfields</function>, <function>PQfname</function>,
1734 <function>PQftype</function>, etc can be applied to the
1735 <structname>PGresult</> to obtain information about the result
1736 columns (if any) of the portal.
1744 The <structname>PGresult</structname><indexterm><primary>PGresult</></>
1745 structure encapsulates the result returned by the server.
1746 <application>libpq</application> application programmers should be
1747 careful to maintain the <structname>PGresult</structname> abstraction.
1748 Use the accessor functions below to get at the contents of
1749 <structname>PGresult</structname>. Avoid directly referencing the
1750 fields of the <structname>PGresult</structname> structure because they
1751 are subject to change in the future.
1756 <function>PQresultStatus</function>
1758 <primary>PQresultStatus</primary>
1764 Returns the result status of the command.
1766 ExecStatusType PQresultStatus(const PGresult *res);
1771 <function>PQresultStatus</function> can return one of the following values:
1775 <term><literal>PGRES_EMPTY_QUERY</literal></term>
1778 The string sent to the server was empty.
1784 <term><literal>PGRES_COMMAND_OK</literal></term>
1787 Successful completion of a command returning no data.
1793 <term><literal>PGRES_TUPLES_OK</literal></term>
1796 Successful completion of a command returning data (such as
1797 a <command>SELECT</> or <command>SHOW</>).
1803 <term><literal>PGRES_COPY_OUT</literal></term>
1806 Copy Out (from server) data transfer started.
1812 <term><literal>PGRES_COPY_IN</literal></term>
1815 Copy In (to server) data transfer started.
1821 <term><literal>PGRES_BAD_RESPONSE</literal></term>
1824 The server's response was not understood.
1830 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
1833 A nonfatal error (a notice or warning) occurred.
1839 <term><literal>PGRES_FATAL_ERROR</literal></term>
1842 A fatal error occurred.
1848 If the result status is <literal>PGRES_TUPLES_OK</literal>, then
1849 the functions described below can be used to retrieve the rows
1850 returned by the query. Note that a <command>SELECT</command>
1851 command that happens to retrieve zero rows still shows
1852 <literal>PGRES_TUPLES_OK</literal>.
1853 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
1854 return rows (<command>INSERT</command>, <command>UPDATE</command>,
1855 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
1856 indicate a bug in the client software.
1860 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
1861 never be returned directly by <function>PQexec</function> or other
1862 query execution functions; results of this kind are instead passed
1863 to the notice processor (see <xref
1864 linkend="libpq-notice-processing">).
1871 <function>PQresStatus</function>
1873 <primary>PQresStatus</primary>
1879 Converts the enumerated type returned by
1880 <function>PQresultStatus</> into a string constant describing the
1881 status code. The caller should not free the result.
1884 char *PQresStatus(ExecStatusType status);
1892 <function>PQresultErrorMessage</function>
1894 <primary>PQresultErrorMessage</primary>
1900 Returns the error message associated with the command, or an empty string
1901 if there was no error.
1903 char *PQresultErrorMessage(const PGresult *res);
1905 If there was an error, the returned string will include a trailing
1906 newline. The caller should not free the result directly. It will
1907 be freed when the associated <structname>PGresult</> handle is
1908 passed to <function>PQclear</function>.
1912 Immediately following a <function>PQexec</function> or
1913 <function>PQgetResult</function> call,
1914 <function>PQerrorMessage</function> (on the connection) will return
1915 the same string as <function>PQresultErrorMessage</function> (on
1916 the result). However, a <structname>PGresult</structname> will
1917 retain its error message until destroyed, whereas the connection's
1918 error message will change when subsequent operations are done.
1919 Use <function>PQresultErrorMessage</function> when you want to
1920 know the status associated with a particular
1921 <structname>PGresult</structname>; use
1922 <function>PQerrorMessage</function> when you want to know the
1923 status from the latest operation on the connection.
1929 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
1932 Returns an individual field of an error report.
1934 char *PQresultErrorField(const PGresult *res, int fieldcode);
1936 <parameter>fieldcode</> is an error field identifier; see the symbols
1937 listed below. <symbol>NULL</symbol> is returned if the
1938 <structname>PGresult</structname> is not an error or warning result,
1939 or does not include the specified field. Field values will normally
1940 not include a trailing newline. The caller should not free the
1941 result directly. It will be freed when the
1942 associated <structname>PGresult</> handle is passed to
1943 <function>PQclear</function>.
1947 The following field codes are available:
1950 <term><symbol>PG_DIAG_SEVERITY</></term>
1953 The severity; the field contents are <literal>ERROR</>,
1954 <literal>FATAL</>, or <literal>PANIC</> (in an error message),
1955 or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
1956 <literal>INFO</>, or <literal>LOG</> (in a notice message), or
1957 a localized translation of one of these. Always present.
1964 <primary>error codes</primary>
1965 <secondary>libpq</secondary>
1967 <term><symbol>PG_DIAG_SQLSTATE</></term>
1970 The SQLSTATE code for the error. The SQLSTATE code identifies
1971 the type of error that has occurred; it can be used by
1972 front-end applications to perform specific operations (such
1973 as error handling) in response to a particular database error.
1974 For a list of the possible SQLSTATE codes, see <xref
1975 linkend="errcodes-appendix">. This field is not localizable,
1976 and is always present.
1982 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
1985 The primary human-readable error message (typically one line).
1992 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
1995 Detail: an optional secondary error message carrying more
1996 detail about the problem. Might run to multiple lines.
2002 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
2005 Hint: an optional suggestion what to do about the problem.
2006 This is intended to differ from detail in that it offers advice
2007 (potentially inappropriate) rather than hard facts. Might
2008 run to multiple lines.
2014 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
2017 A string containing a decimal integer indicating an error cursor
2018 position as an index into the original statement string. The
2019 first character has index 1, and positions are measured in
2020 characters not bytes.
2026 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
2029 This is defined the same as the
2030 <symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
2031 when the cursor position refers to an internally generated
2032 command rather than the one submitted by the client. The
2033 <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
2040 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
2043 The text of a failed internally-generated command. This could
2044 be, for example, a SQL query issued by a PL/pgSQL function.
2050 <term><symbol>PG_DIAG_CONTEXT</></term>
2053 An indication of the context in which the error occurred.
2054 Presently this includes a call stack traceback of active
2055 procedural language functions and internally-generated queries.
2056 The trace is one entry per line, most recent first.
2062 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
2065 The file name of the source-code location where the error was
2072 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
2075 The line number of the source-code location where the error
2082 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
2085 The name of the source-code function reporting the error.
2093 The client is responsible for formatting displayed information to meet
2094 its needs; in particular it should break long lines as needed.
2095 Newline characters appearing in the error message fields should be
2096 treated as paragraph breaks, not line breaks.
2100 Errors generated internally by <application>libpq</application> will
2101 have severity and primary message, but typically no other fields.
2102 Errors returned by a pre-3.0-protocol server will include severity and
2103 primary message, and sometimes a detail message, but no other fields.
2107 Note that error fields are only available from
2108 <structname>PGresult</structname> objects, not
2109 <structname>PGconn</structname> objects; there is no
2110 <function>PQerrorField</function> function.
2116 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
2119 Frees the storage associated with a
2120 <structname>PGresult</structname>. Every command result should be
2121 freed via <function>PQclear</function> when it is no longer
2125 void PQclear(PGresult *res);
2130 You can keep a <structname>PGresult</structname> object around for
2131 as long as you need it; it does not go away when you issue a new
2132 command, nor even if you close the connection. To get rid of it,
2133 you must call <function>PQclear</function>. Failure to do this
2134 will result in memory leaks in your application.
2142 <sect2 id="libpq-exec-select-info">
2143 <title>Retrieving Query Result Information</title>
2146 These functions are used to extract information from a
2147 <structname>PGresult</structname> object that represents a successful
2148 query result (that is, one that has status
2149 <literal>PGRES_TUPLES_OK</literal>). They can also be used to extract
2150 information from a successful Describe operation: a Describe's result
2151 has all the same column information that actual execution of the query
2152 would provide, but it has zero rows. For objects with other status values,
2153 these functions will act as though the result has zero rows and zero columns.
2159 <function>PQntuples</function>
2161 <primary>PQntuples</primary>
2167 Returns the number of rows (tuples) in the query result. Because
2168 it returns an integer result, large result sets might overflow the
2169 return value on 32-bit operating systems.
2172 int PQntuples(const PGresult *res);
2181 <function>PQnfields</function>
2183 <primary>PQnfields</primary>
2189 Returns the number of columns (fields) in each row of the query
2193 int PQnfields(const PGresult *res);
2201 <function>PQfname</function>
2203 <primary>PQfname</primary>
2209 Returns the column name associated with the given column number.
2210 Column numbers start at 0. The caller should not free the result
2211 directly. It will be freed when the associated
2212 <structname>PGresult</> handle is passed to
2213 <function>PQclear</function>.
2215 char *PQfname(const PGresult *res,
2221 <symbol>NULL</symbol> is returned if the column number is out of range.
2228 <function>PQfnumber</function>
2230 <primary>PQfnumber</primary>
2236 Returns the column number associated with the given column name.
2238 int PQfnumber(const PGresult *res,
2239 const char *column_name);
2244 -1 is returned if the given name does not match any column.
2248 The given name is treated like an identifier in an SQL command,
2249 that is, it is downcased unless double-quoted. For example, given
2250 a query result generated from the SQL command:
2252 SELECT 1 AS FOO, 2 AS "BAR";
2254 we would have the results:
2256 PQfname(res, 0) <lineannotation>foo</lineannotation>
2257 PQfname(res, 1) <lineannotation>BAR</lineannotation>
2258 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
2259 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
2260 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
2261 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
2269 <function>PQftable</function>
2271 <primary>PQftable</primary>
2277 Returns the OID of the table from which the given column was
2278 fetched. Column numbers start at 0.
2280 Oid PQftable(const PGresult *res,
2286 <literal>InvalidOid</> is returned if the column number is out of range,
2287 or if the specified column is not a simple reference to a table column,
2288 or when using pre-3.0 protocol.
2289 You can query the system table <literal>pg_class</literal> to determine
2290 exactly which table is referenced.
2294 The type <type>Oid</type> and the constant
2295 <literal>InvalidOid</literal> will be defined when you include
2296 the <application>libpq</application> header file. They will both
2297 be some integer type.
2304 <function>PQftablecol</function>
2306 <primary>PQftablecol</primary>
2312 Returns the column number (within its table) of the column making
2313 up the specified query result column. Query-result column numbers
2314 start at 0, but table columns have nonzero numbers.
2316 int PQftablecol(const PGresult *res,
2322 Zero is returned if the column number is out of range, or if the
2323 specified column is not a simple reference to a table column, or
2324 when using pre-3.0 protocol.
2331 <function>PQfformat</function>
2333 <primary>PQfformat</primary>
2339 Returns the format code indicating the format of the given
2340 column. Column numbers start at 0.
2342 int PQfformat(const PGresult *res,
2348 Format code zero indicates textual data representation, while format
2349 code one indicates binary representation. (Other codes are reserved
2350 for future definition.)
2357 <function>PQftype</function>
2359 <primary>PQftype</primary>
2365 Returns the data type associated with the given column number.
2366 The integer returned is the internal OID number of the type.
2367 Column numbers start at 0.
2369 Oid PQftype(const PGresult *res,
2375 You can query the system table <literal>pg_type</literal> to
2376 obtain the names and properties of the various data types. The
2377 <acronym>OID</acronym>s of the built-in data types are defined
2378 in the file <filename>src/include/catalog/pg_type.h</filename>
2386 <function>PQfmod</function>
2388 <primary>PQfmod</primary>
2394 Returns the type modifier of the column associated with the
2395 given column number. Column numbers start at 0.
2397 int PQfmod(const PGresult *res,
2403 The interpretation of modifier values is type-specific; they
2404 typically indicate precision or size limits. The value -1 is
2405 used to indicate <quote>no information available</>. Most data
2406 types do not use modifiers, in which case the value is always
2414 <function>PQfsize</function>
2416 <primary>PQfsize</primary>
2422 Returns the size in bytes of the column associated with the
2423 given column number. Column numbers start at 0.
2425 int PQfsize(const PGresult *res,
2431 <function>PQfsize</> returns the space allocated for this column
2432 in a database row, in other words the size of the server's
2433 internal representation of the data type. (Accordingly, it is
2434 not really very useful to clients.) A negative value indicates
2435 the data type is variable-length.
2442 <function>PQbinaryTuples</function>
2444 <primary>PQbinaryTuples</primary>
2450 Returns 1 if the <structname>PGresult</> contains binary data
2451 and 0 if it contains text data.
2453 int PQbinaryTuples(const PGresult *res);
2458 This function is deprecated (except for its use in connection with
2459 <command>COPY</>), because it is possible for a single
2460 <structname>PGresult</> to contain text data in some columns and
2461 binary data in others. <function>PQfformat</> is preferred.
2462 <function>PQbinaryTuples</> returns 1 only if all columns of the
2463 result are binary (format 1).
2470 <function>PQgetvalue</function>
2472 <primary>PQgetvalue</primary>
2478 Returns a single field value of one row of a
2479 <structname>PGresult</structname>. Row and column numbers start
2480 at 0. The caller should not free the result directly. It will
2481 be freed when the associated <structname>PGresult</> handle is
2482 passed to <function>PQclear</function>.
2484 char *PQgetvalue(const PGresult *res,
2491 For data in text format, the value returned by
2492 <function>PQgetvalue</function> is a null-terminated character
2493 string representation of the field value. For data in binary
2494 format, the value is in the binary representation determined by
2495 the data type's <function>typsend</> and <function>typreceive</>
2496 functions. (The value is actually followed by a zero byte in
2497 this case too, but that is not ordinarily useful, since the
2498 value is likely to contain embedded nulls.)
2502 An empty string is returned if the field value is null. See
2503 <function>PQgetisnull</> to distinguish null values from
2504 empty-string values.
2508 The pointer returned by <function>PQgetvalue</function> points
2509 to storage that is part of the <structname>PGresult</structname>
2510 structure. One should not modify the data it points to, and one
2511 must explicitly copy the data into other storage if it is to be
2512 used past the lifetime of the <structname>PGresult</structname>
2520 <function>PQgetisnull</function>
2522 <primary>PQgetisnull</primary>
2525 <primary>null value</primary>
2526 <secondary sortas="libpq">in libpq</secondary>
2532 Tests a field for a null value. Row and column numbers start
2535 int PQgetisnull(const PGresult *res,
2542 This function returns 1 if the field is null and 0 if it
2543 contains a non-null value. (Note that
2544 <function>PQgetvalue</function> will return an empty string,
2545 not a null pointer, for a null field.)
2552 <function>PQgetlength</function>
2554 <primary>PQgetlength</primary>
2559 Returns the actual length of a field value in bytes. Row and
2560 column numbers start at 0.
2562 int PQgetlength(const PGresult *res,
2569 This is the actual data length for the particular data value,
2570 that is, the size of the object pointed to by
2571 <function>PQgetvalue</function>. For text data format this is
2572 the same as <function>strlen()</>. For binary format this is
2573 essential information. Note that one should <emphasis>not</>
2574 rely on <function>PQfsize</function> to obtain the actual data
2582 <function>PQnparams</function>
2584 <primary>PQnparams</primary>
2590 Returns the number of parameters of a prepared statement.
2592 int PQnparams(const PGresult *res);
2597 This function is only useful when inspecting the result of
2598 <function>PQdescribePrepared</>. For other types of queries it
2606 <function>PQparamtype</function>
2608 <primary>PQparamtype</primary>
2614 Returns the data type of the indicated statement parameter.
2615 Parameter numbers start at 0.
2617 Oid PQparamtype(const PGresult *res, int param_number);
2622 This function is only useful when inspecting the result of
2623 <function>PQdescribePrepared</>. For other types of queries it
2631 <function>PQprint</function>
2633 <primary>PQprint</primary>
2639 Prints out all the rows and, optionally, the column names to
2640 the specified output stream.
2642 void PQprint(FILE *fout, /* output stream */
2643 const PGresult *res,
2644 const PQprintOpt *po);
2646 pqbool header; /* print output field headings and row count */
2647 pqbool align; /* fill align the fields */
2648 pqbool standard; /* old brain dead format */
2649 pqbool html3; /* output HTML tables */
2650 pqbool expanded; /* expand tables */
2651 pqbool pager; /* use pager for output if needed */
2652 char *fieldSep; /* field separator */
2653 char *tableOpt; /* attributes for HTML table element */
2654 char *caption; /* HTML table caption */
2655 char **fieldName; /* null-terminated array of replacement field names */
2661 This function was formerly used by <application>psql</application>
2662 to print query results, but this is no longer the case. Note
2663 that it assumes all the data is in text format.
2670 <sect2 id="libpq-exec-nonselect">
2671 <title>Retrieving Result Information for Other Commands</title>
2674 These functions are used to extract information from
2675 <structname>PGresult</structname> objects that are not
2676 <command>SELECT</> results.
2682 <function>PQcmdStatus</function>
2684 <primary>PQcmdStatus</primary>
2690 Returns the command status tag from the SQL command that generated
2691 the <structname>PGresult</structname>.
2693 char *PQcmdStatus(PGresult *res);
2698 Commonly this is just the name of the command, but it might include
2699 additional data such as the number of rows processed. The caller
2700 should not free the result directly. It will be freed when the
2701 associated <structname>PGresult</> handle is passed to
2702 <function>PQclear</function>.
2709 <function>PQcmdTuples</function>
2711 <primary>PQcmdTuples</primary>
2717 Returns the number of rows affected by the SQL command.
2719 char *PQcmdTuples(PGresult *res);
2724 This function returns a string containing the number of rows
2725 affected by the <acronym>SQL</> statement that generated the
2726 <structname>PGresult</>. This function can only be used following
2727 the execution of an <command>INSERT</>, <command>UPDATE</>,
2728 <command>DELETE</>, <command>MOVE</>, <command>FETCH</>, or
2729 <command>COPY</> statement, or an <command>EXECUTE</> of a
2730 prepared query that contains an <command>INSERT</>,
2731 <command>UPDATE</>, or <command>DELETE</> statement. If the
2732 command that generated the <structname>PGresult</> was anything
2733 else, <function>PQcmdTuples</> returns an empty string. The caller
2734 should not free the return value directly. It will be freed when
2735 the associated <structname>PGresult</> handle is passed to
2736 <function>PQclear</function>.
2743 <function>PQoidValue</function>
2745 <primary>PQoidValue</primary>
2751 Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
2752 of the inserted row, if the <acronym>SQL</> command was an
2753 <command>INSERT</> that inserted exactly one row into a table that
2754 has OIDs, or a <command>EXECUTE</> of a prepared query containing
2755 a suitable <command>INSERT</> statement. Otherwise, this function
2756 returns <literal>InvalidOid</literal>. This function will also
2757 return <literal>InvalidOid</literal> if the table affected by the
2758 <command>INSERT</> statement does not contain OIDs.
2760 Oid PQoidValue(const PGresult *res);
2768 <function>PQoidStatus</function>
2770 <primary>PQoidStatus</primary>
2776 Returns a string with the OID of the inserted row, if the
2777 <acronym>SQL</acronym> command was an <command>INSERT</command>
2778 that inserted exactly one row, or a <command>EXECUTE</command> of
2779 a prepared statement consisting of a suitable
2780 <command>INSERT</command>. (The string will be <literal>0</> if
2781 the <command>INSERT</command> did not insert exactly one row, or
2782 if the target table does not have OIDs.) If the command was not
2783 an <command>INSERT</command>, returns an empty string.
2785 char *PQoidStatus(const PGresult *res);
2790 This function is deprecated in favor of
2791 <function>PQoidValue</function>. It is not thread-safe.
2799 <sect2 id="libpq-exec-escape-string">
2800 <title>Escaping Strings for Inclusion in SQL Commands</title>
2802 <indexterm zone="libpq-exec-escape-string">
2803 <primary>PQescapeStringConn</primary>
2805 <indexterm zone="libpq-exec-escape-string">
2806 <primary>PQescapeString</primary>
2808 <indexterm zone="libpq-exec-escape-string">
2809 <primary>escaping strings</primary>
2810 <secondary>in libpq</secondary>
2814 <function>PQescapeStringConn</function> escapes a string for use within an SQL
2815 command. This is useful when inserting data values as literal constants
2816 in SQL commands. Certain characters (such as quotes and backslashes) must
2817 be escaped to prevent them from being interpreted specially by the SQL parser.
2818 <function>PQescapeStringConn</> performs this operation.
2823 It is especially important to do proper escaping when handling strings that
2824 were received from an untrustworthy source. Otherwise there is a security
2825 risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
2826 SQL commands are fed to your database.
2831 Note that it is not necessary nor correct to do escaping when a data
2832 value is passed as a separate parameter in <function>PQexecParams</> or
2833 its sibling routines.
2836 size_t PQescapeStringConn (PGconn *conn,
2837 char *to, const char *from, size_t length,
2843 <function>PQescapeStringConn</> writes an escaped version of the
2844 <parameter>from</> string to the <parameter>to</> buffer, escaping
2845 special characters so that they cannot cause any harm, and adding a
2846 terminating zero byte. The single quotes that must surround
2847 <productname>PostgreSQL</> string literals are not included in the
2848 result string; they should be provided in the SQL command that the
2849 result is inserted into. The parameter <parameter>from</> points to
2850 the first character of the string that is to be escaped, and the
2851 <parameter>length</> parameter gives the number of bytes in this
2852 string. A terminating zero byte is not required, and should not be
2853 counted in <parameter>length</>. (If a terminating zero byte is found
2854 before <parameter>length</> bytes are processed,
2855 <function>PQescapeStringConn</> stops at the zero; the behavior is
2856 thus rather like <function>strncpy</>.) <parameter>to</> shall point
2857 to a buffer that is able to hold at least one more byte than twice
2858 the value of <parameter>length</>, otherwise the behavior is undefined.
2859 Behavior is likewise undefined if the <parameter>to</> and
2860 <parameter>from</> strings overlap.
2864 If the <parameter>error</> parameter is not NULL, then
2865 <literal>*error</> is set to zero on success, nonzero on error.
2866 Presently the only possible error conditions involve invalid multibyte
2867 encoding in the source string. The output string is still generated
2868 on error, but it can be expected that the server will reject it as
2869 malformed. On error, a suitable message is stored in the
2870 <parameter>conn</> object, whether or not <parameter>error</> is NULL.
2874 <function>PQescapeStringConn</> returns the number of bytes written
2875 to <parameter>to</>, not including the terminating zero byte.
2880 size_t PQescapeString (char *to, const char *from, size_t length);
2885 <function>PQescapeString</> is an older, deprecated version of
2886 <function>PQescapeStringConn</>; the difference is that it does
2887 not take <parameter>conn</> or <parameter>error</> parameters.
2888 Because of this, it cannot adjust its behavior depending on the
2889 connection properties (such as character encoding) and therefore
2890 <emphasis>it might give the wrong results</>. Also, it has no way
2891 to report error conditions.
2895 <function>PQescapeString</> can be used safely in single-threaded
2896 client programs that work with only one <productname>PostgreSQL</>
2897 connection at a time (in this case it can find out what it needs to
2898 know <quote>behind the scenes</>). In other contexts it is a security
2899 hazard and should be avoided in favor of
2900 <function>PQescapeStringConn</>.
2905 <sect2 id="libpq-exec-escape-bytea">
2906 <title>Escaping Binary Strings for Inclusion in SQL Commands</title>
2908 <indexterm zone="libpq-exec-escape-bytea">
2909 <primary>bytea</primary>
2910 <secondary sortas="libpq">in libpq</secondary>
2916 <function>PQescapeByteaConn</function>
2918 <primary>PQescapeByteaConn</primary>
2924 Escapes binary data for use within an SQL command with the type
2925 <type>bytea</type>. As with <function>PQescapeStringConn</function>,
2926 this is only used when inserting data directly into an SQL command string.
2928 unsigned char *PQescapeByteaConn(PGconn *conn,
2929 const unsigned char *from,
2936 Certain byte values <emphasis>must</emphasis> be escaped (but all
2937 byte values <emphasis>can</emphasis> be escaped) when used as part
2938 of a <type>bytea</type> literal in an <acronym>SQL</acronym>
2939 statement. In general, to escape a byte, it is converted into the
2940 three digit octal number equal to the octet value, and preceded by
2941 usually two backslashes. The single quote (<literal>'</>) and backslash
2942 (<literal>\</>) characters have special alternative escape
2943 sequences. See <xref linkend="datatype-binary"> for more
2944 information. <function>PQescapeByteaConn</function> performs this
2945 operation, escaping only the minimally required bytes.
2949 The <parameter>from</parameter> parameter points to the first
2950 byte of the string that is to be escaped, and the
2951 <parameter>from_length</parameter> parameter gives the number of
2952 bytes in this binary string. (A terminating zero byte is
2953 neither necessary nor counted.) The <parameter>to_length</parameter>
2954 parameter points to a variable that will hold the resultant
2955 escaped string length. This result string length includes the terminating
2956 zero byte of the result.
2960 <function>PQescapeByteaConn</> returns an escaped version of the
2961 <parameter>from</parameter> parameter binary string in memory
2962 allocated with <function>malloc()</>. This memory must be freed using
2963 <function>PQfreemem()</> when the result is no longer needed. The
2964 return string has all special characters replaced so that they can
2965 be properly processed by the <productname>PostgreSQL</productname>
2966 string literal parser, and the <type>bytea</type> input function. A
2967 terminating zero byte is also added. The single quotes that must
2968 surround <productname>PostgreSQL</productname> string literals are
2969 not part of the result string.
2973 On error, a NULL pointer is returned, and a suitable error message
2974 is stored in the <parameter>conn</> object. Currently, the only
2975 possible error is insufficient memory for the result string.
2982 <function>PQescapeBytea</function>
2984 <primary>PQescapeBytea</primary>
2990 <function>PQescapeBytea</> is an older, deprecated version of
2991 <function>PQescapeByteaConn</>.
2993 unsigned char *PQescapeBytea(const unsigned char *from,
3000 The only difference from <function>PQescapeByteaConn</> is that
3001 <function>PQescapeBytea</> does not take a <structname>PGconn</>
3002 parameter. Because of this, it cannot adjust its behavior
3003 depending on the connection properties (in particular, whether
3004 standard-conforming strings are enabled) and therefore
3005 <emphasis>it might give the wrong results</>. Also, it has no
3006 way to return an error message on failure.
3010 <function>PQescapeBytea</> can be used safely in single-threaded
3011 client programs that work with only one <productname>PostgreSQL</>
3012 connection at a time (in this case it can find out what it needs
3013 to know <quote>behind the scenes</>). In other contexts it is
3014 a security hazard and should be avoided in favor of
3015 <function>PQescapeByteaConn</>.
3022 <function>PQunescapeBytea</function>
3024 <primary>PQunescapeBytea</primary>
3030 Converts a string representation of binary data into binary data
3031 — the reverse of <function>PQescapeBytea</function>. This
3032 is needed when retrieving <type>bytea</type> data in text format,
3033 but not when retrieving it in binary format.
3036 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
3041 The <parameter>from</parameter> parameter points to a string
3042 such as might be returned by <function>PQgetvalue</function> when applied
3043 to a <type>bytea</type> column. <function>PQunescapeBytea</function>
3044 converts this string representation into its binary representation.
3045 It returns a pointer to a buffer allocated with
3046 <function>malloc()</function>, or null on error, and puts the size of
3047 the buffer in <parameter>to_length</parameter>. The result must be
3048 freed using <function>PQfreemem</> when it is no longer needed.
3052 This conversion is not exactly the inverse of
3053 <function>PQescapeBytea</function>, because the string is not expected
3054 to be <quote>escaped</> when received from <function>PQgetvalue</function>.
3055 In particular this means there is no need for string quoting considerations,
3056 and so no need for a <structname>PGconn</> parameter.
3066 <sect1 id="libpq-async">
3067 <title>Asynchronous Command Processing</title>
3069 <indexterm zone="libpq-async">
3070 <primary>nonblocking connection</primary>
3074 The <function>PQexec</function> function is adequate for submitting
3075 commands in normal, synchronous applications. It has a couple of
3076 deficiencies, however, that can be of importance to some users:
3081 <function>PQexec</function> waits for the command to be completed.
3082 The application might have other work to do (such as maintaining a
3083 user interface), in which case it won't want to block waiting for
3090 Since the execution of the client application is suspended while it
3091 waits for the result, it is hard for the application to decide that
3092 it would like to try to cancel the ongoing command. (It can be done
3093 from a signal handler, but not otherwise.)
3099 <function>PQexec</function> can return only one
3100 <structname>PGresult</structname> structure. If the submitted command
3101 string contains multiple <acronym>SQL</acronym> commands, all but
3102 the last <structname>PGresult</structname> are discarded by
3103 <function>PQexec</function>.
3110 Applications that do not like these limitations can instead use the
3111 underlying functions that <function>PQexec</function> is built from:
3112 <function>PQsendQuery</function> and <function>PQgetResult</function>.
3114 <function>PQsendQueryParams</function>,
3115 <function>PQsendPrepare</function>,
3116 <function>PQsendQueryPrepared</function>,
3117 <function>PQsendDescribePrepared</function>, and
3118 <function>PQsendDescribePortal</function>,
3119 which can be used with <function>PQgetResult</function> to duplicate
3120 the functionality of
3121 <function>PQexecParams</function>,
3122 <function>PQprepare</function>,
3123 <function>PQexecPrepared</function>,
3124 <function>PQdescribePrepared</function>, and
3125 <function>PQdescribePortal</function>
3131 <function>PQsendQuery</function>
3133 <primary>PQsendQuery</primary>
3139 Submits a command to the server without waiting for the result(s).
3140 1 is returned if the command was successfully dispatched and 0 if
3141 not (in which case, use <function>PQerrorMessage</> to get more
3142 information about the failure).
3144 int PQsendQuery(PGconn *conn, const char *command);
3147 After successfully calling <function>PQsendQuery</function>, call
3148 <function>PQgetResult</function> one or more times to obtain the
3149 results. <function>PQsendQuery</function> cannot be called again
3150 (on the same connection) until <function>PQgetResult</function>
3151 has returned a null pointer, indicating that the command is done.
3158 <function>PQsendQueryParams</function>
3160 <primary>PQsendQueryParams</primary>
3166 Submits a command and separate parameters to the server without
3167 waiting for the result(s).
3169 int PQsendQueryParams(PGconn *conn,
3170 const char *command,
3172 const Oid *paramTypes,
3173 const char * const *paramValues,
3174 const int *paramLengths,
3175 const int *paramFormats,
3179 This is equivalent to <function>PQsendQuery</function> except that
3180 query parameters can be specified separately from the query string.
3181 The function's parameters are handled identically to
3182 <function>PQexecParams</function>. Like
3183 <function>PQexecParams</function>, it will not work on 2.0-protocol
3184 connections, and it allows only one command in the query string.
3191 <function>PQsendPrepare</>
3193 <primary>PQsendPrepare</primary>
3199 Sends a request to create a prepared statement with the given
3200 parameters, without waiting for completion.
3202 int PQsendPrepare(PGconn *conn,
3203 const char *stmtName,
3206 const Oid *paramTypes);
3209 This is an asynchronous version of <function>PQprepare</>: it
3210 returns 1 if it was able to dispatch the request, and 0 if not.
3211 After a successful call, call <function>PQgetResult</function> to
3212 determine whether the server successfully created the prepared
3213 statement. The function's parameters are handled identically to
3214 <function>PQprepare</function>. Like
3215 <function>PQprepare</function>, it will not work on 2.0-protocol
3223 <function>PQsendQueryPrepared</function>
3225 <primary>PQsendQueryPrepared</primary>
3231 Sends a request to execute a prepared statement with given
3232 parameters, without waiting for the result(s).
3234 int PQsendQueryPrepared(PGconn *conn,
3235 const char *stmtName,
3237 const char * const *paramValues,
3238 const int *paramLengths,
3239 const int *paramFormats,
3243 This is similar to <function>PQsendQueryParams</function>, but
3244 the command to be executed is specified by naming a
3245 previously-prepared statement, instead of giving a query string.
3246 The function's parameters are handled identically to
3247 <function>PQexecPrepared</function>. Like
3248 <function>PQexecPrepared</function>, it will not work on
3249 2.0-protocol connections.
3256 <function>PQsendDescribePrepared</>
3258 <primary>PQsendDescribePrepared</primary>
3264 Submits a request to obtain information about the specified
3265 prepared statement, without waiting for completion.
3267 int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
3270 This is an asynchronous version of <function>PQdescribePrepared</>:
3271 it returns 1 if it was able to dispatch the request, and 0 if not.
3272 After a successful call, call <function>PQgetResult</function> to
3273 obtain the results. The function's parameters are handled
3274 identically to <function>PQdescribePrepared</function>. Like
3275 <function>PQdescribePrepared</function>, it will not work on
3276 2.0-protocol connections.
3283 <function>PQsendDescribePortal</>
3285 <primary>PQsendDescribePortal</primary>
3291 Submits a request to obtain information about the specified
3292 portal, without waiting for completion.
3294 int PQsendDescribePortal(PGconn *conn, const char *portalName);
3297 This is an asynchronous version of <function>PQdescribePortal</>:
3298 it returns 1 if it was able to dispatch the request, and 0 if not.
3299 After a successful call, call <function>PQgetResult</function> to
3300 obtain the results. The function's parameters are handled
3301 identically to <function>PQdescribePortal</function>. Like
3302 <function>PQdescribePortal</function>, it will not work on
3303 2.0-protocol connections.
3310 <function>PQgetResult</function>
3312 <primary>PQgetResult</primary>
3318 Waits for the next result from a prior
3319 <function>PQsendQuery</function>,
3320 <function>PQsendQueryParams</function>,
3321 <function>PQsendPrepare</function>, or
3322 <function>PQsendQueryPrepared</function> call, and returns it.
3323 A null pointer is returned when the command is complete and there
3324 will be no more results.
3326 PGresult *PQgetResult(PGconn *conn);
3331 <function>PQgetResult</function> must be called repeatedly until
3332 it returns a null pointer, indicating that the command is done.
3333 (If called when no command is active,
3334 <function>PQgetResult</function> will just return a null pointer
3335 at once.) Each non-null result from
3336 <function>PQgetResult</function> should be processed using the
3337 same <structname>PGresult</> accessor functions previously
3338 described. Don't forget to free each result object with
3339 <function>PQclear</function> when done with it. Note that
3340 <function>PQgetResult</function> will block only if a command is
3341 active and the necessary response data has not yet been read by
3342 <function>PQconsumeInput</function>.
3350 Using <function>PQsendQuery</function> and
3351 <function>PQgetResult</function> solves one of
3352 <function>PQexec</function>'s problems: If a command string contains
3353 multiple <acronym>SQL</acronym> commands, the results of those commands
3354 can be obtained individually. (This allows a simple form of overlapped
3355 processing, by the way: the client can be handling the results of one
3356 command while the server is still working on later queries in the same
3357 command string.) However, calling <function>PQgetResult</function>
3358 will still cause the client to block until the server completes the
3359 next <acronym>SQL</acronym> command. This can be avoided by proper
3360 use of two more functions:
3365 <function>PQconsumeInput</function>
3367 <primary>PQconsumeInput</primary>
3373 If input is available from the server, consume it.
3375 int PQconsumeInput(PGconn *conn);
3380 <function>PQconsumeInput</function> normally returns 1 indicating
3381 <quote>no error</quote>, but returns 0 if there was some kind of
3382 trouble (in which case <function>PQerrorMessage</function> can be
3383 consulted). Note that the result does not say whether any input
3384 data was actually collected. After calling
3385 <function>PQconsumeInput</function>, the application can check
3386 <function>PQisBusy</function> and/or
3387 <function>PQnotifies</function> to see if their state has changed.
3391 <function>PQconsumeInput</function> can be called even if the
3392 application is not prepared to deal with a result or notification
3393 just yet. The function will read available data and save it in
3394 a buffer, thereby causing a <function>select()</function>
3395 read-ready indication to go away. The application can thus use
3396 <function>PQconsumeInput</function> to clear the
3397 <function>select()</function> condition immediately, and then
3398 examine the results at leisure.
3405 <function>PQisBusy</function>
3407 <primary>PQisBusy</primary>
3413 Returns 1 if a command is busy, that is,
3414 <function>PQgetResult</function> would block waiting for input.
3415 A 0 return indicates that <function>PQgetResult</function> can be
3416 called with assurance of not blocking.
3418 int PQisBusy(PGconn *conn);
3423 <function>PQisBusy</function> will not itself attempt to read data
3424 from the server; therefore <function>PQconsumeInput</function>
3425 must be invoked first, or the busy state will never end.
3433 A typical application using these functions will have a main loop that
3434 uses <function>select()</function> or <function>poll()</> to wait for
3435 all the conditions that it must respond to. One of the conditions
3436 will be input available from the server, which in terms of
3437 <function>select()</function> means readable data on the file
3438 descriptor identified by <function>PQsocket</function>. When the main
3439 loop detects input ready, it should call
3440 <function>PQconsumeInput</function> to read the input. It can then
3441 call <function>PQisBusy</function>, followed by
3442 <function>PQgetResult</function> if <function>PQisBusy</function>
3443 returns false (0). It can also call <function>PQnotifies</function>
3444 to detect <command>NOTIFY</> messages (see <xref
3445 linkend="libpq-notify">).
3450 <function>PQsendQuery</function>/<function>PQgetResult</function>
3451 can also attempt to cancel a command that is still being processed
3452 by the server; see <xref linkend="libpq-cancel">. But regardless of
3453 the return value of <function>PQcancel</function>, the application
3454 must continue with the normal result-reading sequence using
3455 <function>PQgetResult</function>. A successful cancellation will
3456 simply cause the command to terminate sooner than it would have
3461 By using the functions described above, it is possible to avoid
3462 blocking while waiting for input from the database server. However,
3463 it is still possible that the application will block waiting to send
3464 output to the server. This is relatively uncommon but can happen if
3465 very long SQL commands or data values are sent. (It is much more
3466 probable if the application sends data via <command>COPY IN</command>,
3467 however.) To prevent this possibility and achieve completely
3468 nonblocking database operation, the following additional functions
3474 <function>PQsetnonblocking</function>
3476 <primary>PQsetnonblocking</primary>
3482 Sets the nonblocking status of the connection.
3484 int PQsetnonblocking(PGconn *conn, int arg);
3489 Sets the state of the connection to nonblocking if
3490 <parameter>arg</parameter> is 1, or blocking if
3491 <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
3495 In the nonblocking state, calls to
3496 <function>PQsendQuery</function>, <function>PQputline</function>,
3497 <function>PQputnbytes</function>, and
3498 <function>PQendcopy</function> will not block but instead return
3499 an error if they need to be called again.
3503 Note that <function>PQexec</function> does not honor nonblocking
3504 mode; if it is called, it will act in blocking fashion anyway.
3511 <function>PQisnonblocking</function>
3513 <primary>PQisnonblocking</primary>
3519 Returns the blocking status of the database connection.
3521 int PQisnonblocking(const PGconn *conn);
3526 Returns 1 if the connection is set to nonblocking mode and 0 if
3534 <function>PQflush</function>
3536 <primary>PQflush</primary>
3542 Attempts to flush any queued output data to the server. Returns
3543 0 if successful (or if the send queue is empty), -1 if it failed
3544 for some reason, or 1 if it was unable to send all the data in
3545 the send queue yet (this case can only occur if the connection
3548 int PQflush(PGconn *conn);
3557 After sending any command or data on a nonblocking connection, call
3558 <function>PQflush</function>. If it returns 1, wait for the socket
3559 to be write-ready and call it again; repeat until it returns 0. Once
3560 <function>PQflush</function> returns 0, wait for the socket to be
3561 read-ready and then read the response as described above.
3566 <sect1 id="libpq-cancel">
3567 <title>Cancelling Queries in Progress</title>
3569 <indexterm zone="libpq-cancel">
3570 <primary>canceling</primary>
3571 <secondary>SQL command</secondary>
3575 A client application can request cancellation of a command that is
3576 still being processed by the server, using the functions described in
3582 <function>PQgetCancel</function>
3584 <primary>PQgetCancel</primary>
3590 Creates a data structure containing the information needed to cancel
3591 a command issued through a particular database connection.
3593 PGcancel *PQgetCancel(PGconn *conn);
3598 <function>PQgetCancel</function> creates a
3599 <structname>PGcancel</><indexterm><primary>PGcancel</></> object
3600 given a <structname>PGconn</> connection object. It will return
3601 NULL if the given <parameter>conn</> is NULL or an invalid
3602 connection. The <structname>PGcancel</> object is an opaque
3603 structure that is not meant to be accessed directly by the
3604 application; it can only be passed to <function>PQcancel</function>
3605 or <function>PQfreeCancel</function>.
3612 <function>PQfreeCancel</function>
3614 <primary>PQfreeCancel</primary>
3620 Frees a data structure created by <function>PQgetCancel</function>.
3622 void PQfreeCancel(PGcancel *cancel);
3627 <function>PQfreeCancel</function> frees a data object previously created
3628 by <function>PQgetCancel</function>.
3635 <function>PQcancel</function>
3637 <primary>PQcancel</primary>
3643 Requests that the server abandon processing of the current command.
3645 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
3650 The return value is 1 if the cancel request was successfully
3651 dispatched and 0 if not. If not, <parameter>errbuf</> is filled
3652 with an error message explaining why not. <parameter>errbuf</>
3653 must be a char array of size <parameter>errbufsize</> (the
3654 recommended size is 256 bytes).
3658 Successful dispatch is no guarantee that the request will have
3659 any effect, however. If the cancellation is effective, the current
3660 command will terminate early and return an error result. If the
3661 cancellation fails (say, because the server was already done
3662 processing the command), then there will be no visible result at
3667 <function>PQcancel</function> can safely be invoked from a signal
3668 handler, if the <parameter>errbuf</> is a local variable in the
3669 signal handler. The <structname>PGcancel</> object is read-only
3670 as far as <function>PQcancel</function> is concerned, so it can
3671 also be invoked from a thread that is separate from the one
3672 manipulating the <structname>PGconn</> object.
3681 <function>PQrequestCancel</function>
3683 <primary>PQrequestCancel</primary>
3689 Requests that the server abandon processing of the current
3692 int PQrequestCancel(PGconn *conn);
3697 <function>PQrequestCancel</function> is a deprecated variant of
3698 <function>PQcancel</function>. It operates directly on the
3699 <structname>PGconn</> object, and in case of failure stores the
3700 error message in the <structname>PGconn</> object (whence it can
3701 be retrieved by <function>PQerrorMessage</function>). Although
3702 the functionality is the same, this approach creates hazards for
3703 multiple-thread programs and signal handlers, since it is possible
3704 that overwriting the <structname>PGconn</>'s error message will
3705 mess up the operation currently in progress on the connection.
3714 <sect1 id="libpq-fastpath">
3715 <title>The Fast-Path Interface</title>
3717 <indexterm zone="libpq-fastpath">
3718 <primary>fast path</primary>
3722 <productname>PostgreSQL</productname> provides a fast-path interface
3723 to send simple function calls to the server.
3728 This interface is somewhat obsolete, as one can achieve similar
3729 performance and greater functionality by setting up a prepared
3730 statement to define the function call. Then, executing the statement
3731 with binary transmission of parameters and results substitutes for a
3732 fast-path function call.
3737 The function <function>PQfn</function><indexterm><primary>PQfn</></>
3738 requests execution of a server function via the fast-path interface:
3740 PGresult *PQfn(PGconn *conn,
3745 const PQArgBlock *args,
3760 The <parameter>fnid</> argument is the OID of the function to be
3761 executed. <parameter>args</> and <parameter>nargs</> define the
3762 parameters to be passed to the function; they must match the declared
3763 function argument list. When the <parameter>isint</> field of a
3764 parameter structure is true, the <parameter>u.integer</> value is sent
3765 to the server as an integer of the indicated length (this must be 1,
3766 2, or 4 bytes); proper byte-swapping occurs. When <parameter>isint</>
3767 is false, the indicated number of bytes at <parameter>*u.ptr</> are
3768 sent with no processing; the data must be in the format expected by
3769 the server for binary transmission of the function's argument data
3770 type. <parameter>result_buf</parameter> is the buffer in which to
3771 place the return value. The caller must have allocated sufficient
3772 space to store the return value. (There is no check!) The actual result
3773 length will be returned in the integer pointed to by
3774 <parameter>result_len</parameter>. If a 1, 2, or 4-byte integer result
3775 is expected, set <parameter>result_is_int</parameter> to 1, otherwise
3776 set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
3777 <application>libpq</> to byte-swap the value if necessary, so that it
3778 is delivered as a proper <type>int</type> value for the client machine.
3779 When <parameter>result_is_int</> is 0, the binary-format byte string
3780 sent by the server is returned unmodified.
3784 <function>PQfn</function> always returns a valid
3785 <structname>PGresult</structname> pointer. The result status should be
3786 checked before the result is used. The caller is responsible for
3787 freeing the <structname>PGresult</structname> with
3788 <function>PQclear</function> when it is no longer needed.
3792 Note that it is not possible to handle null arguments, null results,
3793 nor set-valued results when using this interface.
3798 <sect1 id="libpq-notify">
3799 <title>Asynchronous Notification</title>
3801 <indexterm zone="libpq-notify">
3802 <primary>NOTIFY</primary>
3803 <secondary>in libpq</secondary>
3807 <productname>PostgreSQL</productname> offers asynchronous notification
3808 via the <command>LISTEN</command> and <command>NOTIFY</command>
3809 commands. A client session registers its interest in a particular
3810 notification condition with the <command>LISTEN</command> command (and
3811 can stop listening with the <command>UNLISTEN</command> command). All
3812 sessions listening on a particular condition will be notified
3813 asynchronously when a <command>NOTIFY</command> command with that
3814 condition name is executed by any session. No additional information
3815 is passed from the notifier to the listener. Thus, typically, any
3816 actual data that needs to be communicated is transferred through a
3817 database table. Commonly, the condition name is the same as the
3818 associated table, but it is not necessary for there to be any associated
3823 <application>libpq</application> applications submit
3824 <command>LISTEN</command> and <command>UNLISTEN</command> commands as
3825 ordinary SQL commands. The arrival of <command>NOTIFY</command>
3826 messages can subsequently be detected by calling
3827 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
3831 The function <function>PQnotifies</function>
3832 returns the next notification from a list of unhandled
3833 notification messages received from the server. It returns a null pointer if
3834 there are no pending notifications. Once a notification is
3835 returned from <function>PQnotifies</>, it is considered handled and will be
3836 removed from the list of notifications.
3838 PGnotify *PQnotifies(PGconn *conn);
3840 typedef struct pgNotify {
3841 char *relname; /* notification condition name */
3842 int be_pid; /* process ID of notifying server process */
3843 char *extra; /* notification parameter */
3846 After processing a <structname>PGnotify</structname> object returned
3847 by <function>PQnotifies</function>, be sure to free it with
3848 <function>PQfreemem</function>. It is sufficient to free the
3849 <structname>PGnotify</structname> pointer; the
3850 <structfield>relname</structfield> and <structfield>extra</structfield>
3851 fields do not represent separate allocations. (At present, the
3852 <structfield>extra</structfield> field is unused and will always point
3853 to an empty string.)
3857 <xref linkend="libpq-example-2"> gives a sample program that illustrates
3858 the use of asynchronous notification.
3862 <function>PQnotifies</function> does not actually read data from the
3863 server; it just returns messages previously absorbed by another
3864 <application>libpq</application> function. In prior releases of
3865 <application>libpq</application>, the only way to ensure timely receipt
3866 of <command>NOTIFY</> messages was to constantly submit commands, even
3867 empty ones, and then check <function>PQnotifies</function> after each
3868 <function>PQexec</function>. While this still works, it is deprecated
3869 as a waste of processing power.
3873 A better way to check for <command>NOTIFY</> messages when you have no
3874 useful commands to execute is to call
3875 <function>PQconsumeInput</function>, then check
3876 <function>PQnotifies</function>. You can use
3877 <function>select()</function> to wait for data to arrive from the
3878 server, thereby using no <acronym>CPU</acronym> power unless there is
3879 something to do. (See <function>PQsocket</function> to obtain the file
3880 descriptor number to use with <function>select()</function>.) Note that
3881 this will work OK whether you submit commands with
3882 <function>PQsendQuery</function>/<function>PQgetResult</function> or
3883 simply use <function>PQexec</function>. You should, however, remember
3884 to check <function>PQnotifies</function> after each
3885 <function>PQgetResult</function> or <function>PQexec</function>, to
3886 see if any notifications came in during the processing of the command.
3891 <sect1 id="libpq-copy">
3892 <title>Functions Associated with the <command>COPY</command> Command</title>
3894 <indexterm zone="libpq-copy">
3895 <primary>COPY</primary>
3896 <secondary>with libpq</secondary>
3900 The <command>COPY</command> command in
3901 <productname>PostgreSQL</productname> has options to read from or write
3902 to the network connection used by <application>libpq</application>.
3903 The functions described in this section allow applications to take
3904 advantage of this capability by supplying or consuming copied data.
3908 The overall process is that the application first issues the SQL
3909 <command>COPY</command> command via <function>PQexec</function> or one
3910 of the equivalent functions. The response to this (if there is no
3911 error in the command) will be a <structname>PGresult</> object bearing
3912 a status code of <literal>PGRES_COPY_OUT</literal> or
3913 <literal>PGRES_COPY_IN</literal> (depending on the specified copy
3914 direction). The application should then use the functions of this
3915 section to receive or transmit data rows. When the data transfer is
3916 complete, another <structname>PGresult</> object is returned to indicate
3917 success or failure of the transfer. Its status will be
3918 <literal>PGRES_COMMAND_OK</literal> for success or
3919 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
3920 At this point further SQL commands can be issued via
3921 <function>PQexec</function>. (It is not possible to execute other SQL
3922 commands using the same connection while the <command>COPY</command>
3923 operation is in progress.)
3927 If a <command>COPY</command> command is issued via
3928 <function>PQexec</function> in a string that could contain additional
3929 commands, the application must continue fetching results via
3930 <function>PQgetResult</> after completing the <command>COPY</command>
3931 sequence. Only when <function>PQgetResult</> returns
3932 <symbol>NULL</symbol> is it certain that the <function>PQexec</function>
3933 command string is done and it is safe to issue more commands.
3937 The functions of this section should be executed only after obtaining
3938 a result status of <literal>PGRES_COPY_OUT</literal> or
3939 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
3940 <function>PQgetResult</function>.
3944 A <structname>PGresult</> object bearing one of these status values
3945 carries some additional data about the <command>COPY</command> operation
3946 that is starting. This additional data is available using functions
3947 that are also used in connection with query results:
3952 <function>PQnfields</function>
3954 <primary>PQnfields</primary>
3955 <secondary>with COPY</secondary>
3961 Returns the number of columns (fields) to be copied.
3968 <function>PQbinaryTuples</function>
3970 <primary>PQbinaryTuples</primary>
3971 <secondary>with COPY</secondary>
3977 0 indicates the overall copy format is textual (rows separated by
3978 newlines, columns separated by separator characters, etc). 1
3979 indicates the overall copy format is binary. See <xref
3980 linkend="sql-copy" endterm="sql-copy-title"> for more information.
3987 <function>PQfformat</function>
3989 <primary>PQfformat</primary>
3990 <secondary>with COPY</secondary>
3996 Returns the format code (0 for text, 1 for binary) associated with
3997 each column of the copy operation. The per-column format codes
3998 will always be zero when the overall copy format is textual, but
3999 the binary format can support both text and binary columns.
4000 (However, as of the current implementation of <command>COPY</>,
4001 only binary columns appear in a binary copy; so the per-column
4002 formats always match the overall format at present.)
4011 These additional data values are only available when using protocol
4012 3.0. When using protocol 2.0, all these functions will return 0.
4016 <sect2 id="libpq-copy-send">
4017 <title>Functions for Sending <command>COPY</command> Data</title>
4020 These functions are used to send data during <literal>COPY FROM
4021 STDIN</>. They will fail if called when the connection is not in
4022 <literal>COPY_IN</> state.
4028 <function>PQputCopyData</function>
4030 <primary>PQputCopyData</primary>
4036 Sends data to the server during <literal>COPY_IN</> state.
4038 int PQputCopyData(PGconn *conn,
4045 Transmits the <command>COPY</command> data in the specified
4046 <parameter>buffer</>, of length <parameter>nbytes</>, to the server.
4047 The result is 1 if the data was sent, zero if it was not sent
4048 because the attempt would block (this case is only possible if the
4049 connection is in nonblocking mode), or -1 if an error occurred.
4050 (Use <function>PQerrorMessage</function> to retrieve details if
4051 the return value is -1. If the value is zero, wait for write-ready
4056 The application can divide the <command>COPY</command> data stream
4057 into buffer loads of any convenient size. Buffer-load boundaries
4058 have no semantic significance when sending. The contents of the
4059 data stream must match the data format expected by the
4060 <command>COPY</> command; see <xref linkend="sql-copy"
4061 endterm="sql-copy-title"> for details.
4068 <function>PQputCopyEnd</function>
4070 <primary>PQputCopyEnd</primary>
4076 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
4078 int PQputCopyEnd(PGconn *conn,
4079 const char *errormsg);
4084 Ends the <literal>COPY_IN</> operation successfully if
4085 <parameter>errormsg</> is <symbol>NULL</symbol>. If
4086 <parameter>errormsg</> is not <symbol>NULL</symbol> then the
4087 <command>COPY</> is forced to fail, with the string pointed to by
4088 <parameter>errormsg</> used as the error message. (One should not
4089 assume that this exact error message will come back from the server,
4090 however, as the server might have already failed the
4091 <command>COPY</> for its own reasons. Also note that the option
4092 to force failure does not work when using pre-3.0-protocol
4097 The result is 1 if the termination data was sent, zero if it was
4098 not sent because the attempt would block (this case is only possible
4099 if the connection is in nonblocking mode), or -1 if an error
4100 occurred. (Use <function>PQerrorMessage</function> to retrieve
4101 details if the return value is -1. If the value is zero, wait for
4102 write-ready and try again.)
4106 After successfully calling <function>PQputCopyEnd</>, call
4107 <function>PQgetResult</> to obtain the final result status of the
4108 <command>COPY</> command. One can wait for this result to be
4109 available in the usual way. Then return to normal operation.
4117 <sect2 id="libpq-copy-receive">
4118 <title>Functions for Receiving <command>COPY</command> Data</title>
4121 These functions are used to receive data during <literal>COPY TO
4122 STDOUT</>. They will fail if called when the connection is not in
4123 <literal>COPY_OUT</> state.
4129 <function>PQgetCopyData</function>
4131 <primary>PQgetCopyData</primary>
4137 Receives data from the server during <literal>COPY_OUT</> state.
4139 int PQgetCopyData(PGconn *conn,
4146 Attempts to obtain another row of data from the server during a
4147 <command>COPY</command>. Data is always returned one data row at
4148 a time; if only a partial row is available, it is not returned.
4149 Successful return of a data row involves allocating a chunk of
4150 memory to hold the data. The <parameter>buffer</> parameter must
4151 be non-<symbol>NULL</symbol>. <parameter>*buffer</> is set to
4152 point to the allocated memory, or to <symbol>NULL</symbol> in cases
4153 where no buffer is returned. A non-<symbol>NULL</symbol> result
4154 buffer must be freed using <function>PQfreemem</> when no longer
4159 When a row is successfully returned, the return value is the number
4160 of data bytes in the row (this will always be greater than zero).
4161 The returned string is always null-terminated, though this is
4162 probably only useful for textual <command>COPY</command>. A result
4163 of zero indicates that the <command>COPY</command> is still in
4164 progress, but no row is yet available (this is only possible when
4165 <parameter>async</> is true). A result of -1 indicates that the
4166 <command>COPY</command> is done. A result of -2 indicates that an
4167 error occurred (consult <function>PQerrorMessage</> for the reason).
4171 When <parameter>async</> is true (not zero),
4172 <function>PQgetCopyData</> will not block waiting for input; it
4173 will return zero if the <command>COPY</command> is still in progress
4174 but no complete row is available. (In this case wait for read-ready
4175 and then call <function>PQconsumeInput</> before calling
4176 <function>PQgetCopyData</> again.) When <parameter>async</> is
4177 false (zero), <function>PQgetCopyData</> will block until data is
4178 available or the operation completes.
4182 After <function>PQgetCopyData</> returns -1, call
4183 <function>PQgetResult</> to obtain the final result status of the
4184 <command>COPY</> command. One can wait for this result to be
4185 available in the usual way. Then return to normal operation.
4193 <sect2 id="libpq-copy-deprecated">
4194 <title>Obsolete Functions for <command>COPY</command></title>
4197 These functions represent older methods of handling <command>COPY</>.
4198 Although they still work, they are deprecated due to poor error handling,
4199 inconvenient methods of detecting end-of-data, and lack of support for binary
4200 or nonblocking transfers.
4206 <function>PQgetline</function>
4208 <primary>PQgetline</primary>
4214 Reads a newline-terminated line of characters (transmitted
4215 by the server) into a buffer string of size <parameter>length</>.
4217 int PQgetline(PGconn *conn,
4224 This function copies up to <parameter>length</>-1 characters into
4225 the buffer and converts the terminating newline into a zero byte.
4226 <function>PQgetline</function> returns <symbol>EOF</symbol> at the
4227 end of input, 0 if the entire line has been read, and 1 if the
4228 buffer is full but the terminating newline has not yet been read.
4231 Note that the application must check to see if a new line consists
4232 of the two characters <literal>\.</literal>, which indicates
4233 that the server has finished sending the results of the
4234 <command>COPY</command> command. If the application might receive
4235 lines that are more than <parameter>length</>-1 characters long,
4236 care is needed to be sure it recognizes the <literal>\.</literal>
4237 line correctly (and does not, for example, mistake the end of a
4238 long data line for a terminator line).
4245 <function>PQgetlineAsync</function>
4247 <primary>PQgetlineAsync</primary>
4253 Reads a row of <command>COPY</command> data (transmitted by the
4254 server) into a buffer without blocking.
4256 int PQgetlineAsync(PGconn *conn,
4263 This function is similar to <function>PQgetline</function>, but it can be used
4265 that must read <command>COPY</command> data asynchronously, that is, without blocking.
4266 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
4268 application should call <function>PQconsumeInput</function> and
4269 <function>PQgetlineAsync</function> until the
4270 end-of-data signal is detected.
4273 Unlike <function>PQgetline</function>, this function takes
4274 responsibility for detecting end-of-data.
4278 On each call, <function>PQgetlineAsync</function> will return data if a
4279 complete data row is available in <application>libpq</>'s input buffer.
4280 Otherwise, no data is returned until the rest of the row arrives.
4281 The function returns -1 if the end-of-copy-data marker has been recognized,
4282 or 0 if no data is available, or a positive number giving the number of
4283 bytes of data returned. If -1 is returned, the caller must next call
4284 <function>PQendcopy</function>, and then return to normal processing.
4288 The data returned will not extend beyond a data-row boundary. If possible
4289 a whole row will be returned at one time. But if the buffer offered by
4290 the caller is too small to hold a row sent by the server, then a partial
4291 data row will be returned. With textual data this can be detected by testing
4292 whether the last returned byte is <literal>\n</literal> or not. (In a binary
4293 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
4294 equivalent determination.)
4295 The returned string is not null-terminated. (If you want to add a
4296 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
4297 than the room actually available.)
4304 <function>PQputline</function>
4306 <primary>PQputline</primary>
4312 Sends a null-terminated string to the server. Returns 0 if
4313 OK and <symbol>EOF</symbol> if unable to send the string.
4315 int PQputline(PGconn *conn,
4316 const char *string);
4321 The <command>COPY</command> data stream sent by a series of calls
4322 to <function>PQputline</function> has the same format as that
4323 returned by <function>PQgetlineAsync</function>, except that
4324 applications are not obliged to send exactly one data row per
4325 <function>PQputline</function> call; it is okay to send a partial
4326 line or multiple lines per call.
4331 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
4332 for the application to explicitly send the two characters
4333 <literal>\.</literal> as a final line to indicate to the server that it had
4334 finished sending <command>COPY</> data. While this still works, it is deprecated and the
4335 special meaning of <literal>\.</literal> can be expected to be removed in a
4336 future release. It is sufficient to call <function>PQendcopy</function> after
4337 having sent the actual data.
4345 <function>PQputnbytes</function>
4347 <primary>PQputnbytes</primary>
4353 Sends a non-null-terminated string to the server. Returns
4354 0 if OK and <symbol>EOF</symbol> if unable to send the string.
4356 int PQputnbytes(PGconn *conn,
4363 This is exactly like <function>PQputline</function>, except that the data
4364 buffer need not be null-terminated since the number of bytes to send is
4365 specified directly. Use this procedure when sending binary data.
4372 <function>PQendcopy</function>
4374 <primary>PQendcopy</primary>
4380 Synchronizes with the server.
4382 int PQendcopy(PGconn *conn);
4384 This function waits until the server has finished the copying.
4385 It should either be issued when the last string has been sent
4386 to the server using <function>PQputline</function> or when the
4387 last string has been received from the server using
4388 <function>PGgetline</function>. It must be issued or the server
4389 will get <quote>out of sync</quote> with the client. Upon return
4390 from this function, the server is ready to receive the next SQL
4391 command. The return value is 0 on successful completion,
4392 nonzero otherwise. (Use <function>PQerrorMessage</function> to
4393 retrieve details if the return value is nonzero.)
4397 When using <function>PQgetResult</function>, the application should
4398 respond to a <literal>PGRES_COPY_OUT</literal> result by executing
4399 <function>PQgetline</function> repeatedly, followed by
4400 <function>PQendcopy</function> after the terminator line is seen.
4401 It should then return to the <function>PQgetResult</function> loop
4402 until <function>PQgetResult</function> returns a null pointer.
4403 Similarly a <literal>PGRES_COPY_IN</literal> result is processed
4404 by a series of <function>PQputline</function> calls followed by
4405 <function>PQendcopy</function>, then return to the
4406 <function>PQgetResult</function> loop. This arrangement will
4407 ensure that a <command>COPY</command> command embedded in a series
4408 of <acronym>SQL</acronym> commands will be executed correctly.
4412 Older applications are likely to submit a <command>COPY</command>
4413 via <function>PQexec</function> and assume that the transaction
4414 is done after <function>PQendcopy</function>. This will work
4415 correctly only if the <command>COPY</command> is the only
4416 <acronym>SQL</acronym> command in the command string.
4426 <sect1 id="libpq-control">
4427 <title>Control Functions</title>
4430 These functions control miscellaneous details of <application>libpq</>'s
4437 <function>PQclientEncoding</function>
4439 <primary>PQclientEncoding</primary>
4445 Returns the client encoding.
4447 int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
4450 Note that it returns the encoding ID, not a symbolic string
4451 such as <literal>EUC_JP</literal>. To convert an encoding ID to an encoding name, you
4455 char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
4463 <function>PQsetClientEncoding</function>
4465 <primary>PQsetClientEncoding</primary>
4471 Sets the client encoding.
4473 int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
4476 <replaceable>conn</replaceable> is a connection to the server,
4477 and <replaceable>encoding</replaceable> is the encoding you want to
4478 use. If the function successfully sets the encoding, it returns 0,
4479 otherwise -1. The current encoding for this connection can be
4480 determined by using <function>PQclientEncoding</>.
4487 <function>PQsetErrorVerbosity</function>
4489 <primary>PQsetErrorVerbosity</primary>
4495 Determines the verbosity of messages returned by
4496 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
4504 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
4507 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
4508 the connection's previous setting. In <firstterm>TERSE</> mode,
4509 returned messages include severity, primary text, and position only;
4510 this will normally fit on a single line. The default mode produces
4511 messages that include the above plus any detail, hint, or context
4512 fields (these might span multiple lines). The <firstterm>VERBOSE</>
4513 mode includes all available fields. Changing the verbosity does not
4514 affect the messages available from already-existing
4515 <structname>PGresult</> objects, only subsequently-created ones.
4522 <function>PQtrace</function>
4524 <primary>PQtrace</primary>
4530 Enables tracing of the client/server communication to a debugging file stream.
4532 void PQtrace(PGconn *conn, FILE *stream);
4538 On Windows, if the <application>libpq</> library and an application are
4539 compiled with different flags, this function call will crash the
4540 application because the internal representation of the <literal>FILE</>
4541 pointers differ. Specifically, multithreaded/single-threaded,
4542 release/debug, and static/dynamic flags should be the same for the
4543 library and all applications using that library.
4552 <function>PQuntrace</function>
4554 <primary>PQuntrace</primary>
4560 Disables tracing started by <function>PQtrace</function>.
4562 void PQuntrace(PGconn *conn);
4571 <sect1 id="libpq-misc">
4572 <title>Miscellaneous Functions</title>
4575 As always, there are some functions that just don't fit anywhere.
4581 <function>PQfreemem</function>
4583 <primary>PQfreemem</primary>
4589 Frees memory allocated by <application>libpq</>.
4591 void PQfreemem(void *ptr);
4596 Frees memory allocated by <application>libpq</>, particularly
4597 <function>PQescapeByteaConn</function>,
4598 <function>PQescapeBytea</function>,
4599 <function>PQunescapeBytea</function>,
4600 and <function>PQnotifies</function>.
4601 It is particularly important that this function, rather than
4602 <function>free()</>, be used on Microsoft Windows. This is because
4603 allocating memory in a DLL and releasing it in the application works
4604 only if multithreaded/single-threaded, release/debug, and static/dynamic
4605 flags are the same for the DLL and the application. On non-Microsoft
4606 Windows platforms, this function is the same as the standard library
4607 function <function>free()</>.
4614 <function>PQconninfoFree</function>
4616 <primary>PQconninfoFree</primary>
4622 Frees the data structures allocated by
4623 <function>PQconndefaults</> or <function>PQconninfoParse</>.
4625 void PQconninfoFree(PQconninfoOption *connOptions);
4630 A simple <function>PQfreemem</function> will not do for this, since
4631 the array contains references to subsidiary strings.
4638 <function>PQencryptPassword</function>
4640 <primary>PQencryptPassword</primary>
4646 Prepares the encrypted form of a <productname>PostgreSQL</> password.
4648 char * PQencryptPassword(const char *passwd, const char *user);
4650 This function is intended to be used by client applications that
4651 wish to send commands like <literal>ALTER USER joe PASSWORD
4652 'pwd'</>. It is good practice not to send the original cleartext
4653 password in such a command, because it might be exposed in command
4654 logs, activity displays, and so on. Instead, use this function to
4655 convert the password to encrypted form before it is sent. The
4656 arguments are the cleartext password, and the SQL name of the user
4657 it is for. The return value is a string allocated by
4658 <function>malloc</function>, or <symbol>NULL</symbol> if out of
4659 memory. The caller can assume the string doesn't contain any
4660 special characters that would require escaping. Use
4661 <function>PQfreemem</> to free the result when done with it.
4668 <function>PQmakeEmptyPGresult</function>
4670 <primary>PQmakeEmptyPGresult</primary>
4676 Constructs an empty <structname>PGresult</structname> object with the given status.
4678 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
4683 This is <application>libpq</>'s internal function to allocate and
4684 initialize an empty <structname>PGresult</structname> object. This
4685 function returns NULL if memory could not be allocated. It is
4686 exported because some applications find it useful to generate result
4687 objects (particularly objects with error status) themselves. If
4688 <parameter>conn</parameter> is not null and <parameter>status</>
4689 indicates an error, the current error message of the specified
4690 connection is copied into the <structname>PGresult</structname>.
4691 Also, if <parameter>conn</parameter> is not null, any event procedures
4692 registered in the connection are copied into the
4693 <structname>PGresult</structname>. (They do not get
4694 <literal>PGEVT_RESULTCREATE</> calls, but see
4695 <function>PQfireResultCreateEvents</function>.)
4696 Note that <function>PQclear</function> should eventually be called
4697 on the object, just as with a <structname>PGresult</structname>
4698 returned by <application>libpq</application> itself.
4705 <function>PQfireResultCreateEvents</function>
4707 <primary>PQfireResultCreateEvents</primary>
4712 Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
4713 linkend="libpq-events">) for each event procedure registered in the
4714 <structname>PGresult</structname> object. Returns non-zero for success,
4715 zero if any event procedure fails.
4718 int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
4723 The <literal>conn</> argument is passed through to event procedures
4724 but not used directly. It can be <literal>NULL</> if the event
4725 procedures won't use it.
4729 Event procedures that have already received a
4730 <literal>PGEVT_RESULTCREATE</> or <literal>PGEVT_RESULTCOPY</> event
4731 for this object are not fired again.
4735 The main reason that this function is separate from
4736 <function>PQmakeEmptyPGResult</function> is that it is often appropriate
4737 to create a <structname>PGresult</structname> and fill it with data
4738 before invoking the event procedures.
4745 <function>PQcopyResult</function>
4747 <primary>PQcopyResult</primary>
4753 Makes a copy of a <structname>PGresult</structname> object. The copy is
4754 not linked to the source result in any way and
4755 <function>PQclear</function> must be called when the copy is no longer
4756 needed. If the function fails, NULL is returned.
4759 PGresult *PQcopyResult(const PGresult *src, int flags);
4764 This is not intended to make an exact copy. The returned result is
4765 always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
4766 copy any error message in the source. (It does copy the command status
4767 string, however.) The <parameter>flags</parameter> argument determines
4768 what else is copied. It is a bitwise OR of several flags.
4769 <literal>PG_COPYRES_ATTRS</literal> specifies copying the source
4770 result's attributes (column definitions).
4771 <literal>PG_COPYRES_TUPLES</literal> specifies copying the source
4772 result's tuples. (This implies copying the attributes, too.)
4773 <literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
4774 copying the source result's notify hooks.
4775 <literal>PG_COPYRES_EVENTS</literal> specifies copying the source
4776 result's events. (But any instance data associated with the source
4784 <function>PQsetResultAttrs</function>
4786 <primary>PQsetResultAttrs</primary>
4792 Sets the attributes of a <structname>PGresult</structname> object.
4794 int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
4799 The provided <parameter>attDescs</parameter> are copied into the result.
4800 If the <parameter>attDescs</parameter> pointer is NULL or
4801 <parameter>numAttributes</parameter> is less than one, the request is
4802 ignored and the function succeeds. If <parameter>res</parameter>
4803 already contains attributes, the function will fail. If the function
4804 fails, the return value is zero. If the function succeeds, the return
4812 <function>PQsetvalue</function>
4814 <primary>PQsetvalue</primary>
4820 Sets a tuple field value of a <structname>PGresult</structname> object.
4822 int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
4827 The function will automatically grow the result's internal tuples array
4828 as needed. However, the <parameter>tup_num</parameter> argument must be
4829 less than or equal to <function>PQntuples</function>, meaning this
4830 function can only grow the tuples array one tuple at a time. But any
4831 field of any existing tuple can be modified in any order. If a value at
4832 <parameter>field_num</parameter> already exists, it will be overwritten.
4833 If <parameter>len</parameter> is <literal>-1</literal> or
4834 <parameter>value</parameter> is <literal>NULL</literal>, the field value
4835 will be set to an SQL <literal>NULL</literal>. The
4836 <parameter>value</parameter> is copied into the result's private storage,
4837 thus is no longer needed after the function
4838 returns. If the function fails, the return value is zero. If the
4839 function succeeds, the return value is non-zero.
4846 <function>PQresultAlloc</function>
4848 <primary>PQresultAlloc</primary>
4854 Allocate subsidiary storage for a <structname>PGresult</structname> object.
4856 void *PQresultAlloc(PGresult *res, size_t nBytes);
4861 Any memory allocated with this function will be freed when
4862 <parameter>res</parameter> is cleared. If the function fails,
4863 the return value is <literal>NULL</literal>. The result is
4864 guaranteed to be adequately aligned for any type of data,
4865 just as for <function>malloc</>.
4874 <sect1 id="libpq-notice-processing">
4875 <title>Notice Processing</title>
4877 <indexterm zone="libpq-notice-processing">
4878 <primary>notice processing</primary>
4879 <secondary>in libpq</secondary>
4883 Notice and warning messages generated by the server are not returned
4884 by the query execution functions, since they do not imply failure of
4885 the query. Instead they are passed to a notice handling function, and
4886 execution continues normally after the handler returns. The default
4887 notice handling function prints the message on
4888 <filename>stderr</filename>, but the application can override this
4889 behavior by supplying its own handling function.
4893 For historical reasons, there are two levels of notice handling, called
4894 the notice receiver and notice processor. The default behavior is for
4895 the notice receiver to format the notice and pass a string to the notice
4896 processor for printing. However, an application that chooses to provide
4897 its own notice receiver will typically ignore the notice processor
4898 layer and just do all the work in the notice receiver.
4902 The function <function>PQsetNoticeReceiver</function>
4903 <indexterm><primary>notice
4904 receiver</></><indexterm><primary>PQsetNoticeReceiver</></> sets or
4905 examines the current notice receiver for a connection object.
4906 Similarly, <function>PQsetNoticeProcessor</function>
4907 <indexterm><primary>notice
4908 processor</></><indexterm><primary>PQsetNoticeProcessor</></> sets or
4909 examines the current notice processor.
4912 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
4915 PQsetNoticeReceiver(PGconn *conn,
4916 PQnoticeReceiver proc,
4919 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
4922 PQsetNoticeProcessor(PGconn *conn,
4923 PQnoticeProcessor proc,
4927 Each of these functions returns the previous notice receiver or
4928 processor function pointer, and sets the new value. If you supply a
4929 null function pointer, no action is taken, but the current pointer is
4934 When a notice or warning message is received from the server, or
4935 generated internally by <application>libpq</application>, the notice
4936 receiver function is called. It is passed the message in the form of
4937 a <symbol>PGRES_NONFATAL_ERROR</symbol>
4938 <structname>PGresult</structname>. (This allows the receiver to extract
4939 individual fields using <function>PQresultErrorField</>, or the complete
4940 preformatted message using <function>PQresultErrorMessage</>.) The same
4941 void pointer passed to <function>PQsetNoticeReceiver</function> is also
4942 passed. (This pointer can be used to access application-specific state
4947 The default notice receiver simply extracts the message (using
4948 <function>PQresultErrorMessage</>) and passes it to the notice
4953 The notice processor is responsible for handling a notice or warning
4954 message given in text form. It is passed the string text of the message
4955 (including a trailing newline), plus a void pointer that is the same
4956 one passed to <function>PQsetNoticeProcessor</function>. (This pointer
4957 can be used to access application-specific state if needed.)
4961 The default notice processor is simply:
4964 defaultNoticeProcessor(void *arg, const char *message)
4966 fprintf(stderr, "%s", message);
4972 Once you have set a notice receiver or processor, you should expect
4973 that that function could be called as long as either the
4974 <structname>PGconn</> object or <structname>PGresult</> objects made
4975 from it exist. At creation of a <structname>PGresult</>, the
4976 <structname>PGconn</>'s current notice handling pointers are copied
4977 into the <structname>PGresult</> for possible use by functions like
4978 <function>PQgetvalue</function>.
4983 <sect1 id="libpq-events">
4984 <title>Event System</title>
4987 <application>libpq</application>'s event system is designed to notify
4988 registered event handlers about interesting
4989 <application>libpq</application> events, such as the creation or
4990 destruction of <structname>PGconn</structname> and
4991 <structname>PGresult</structname> objects. A principal use case is that
4992 this allows applications to associate their own data with a
4993 <structname>PGconn</structname> or <structname>PGresult</structname>
4994 and ensure that that data is freed at an appropriate time.
4998 Each registered event handler is associated with two pieces of data,
4999 known to <application>libpq</application> only as opaque <literal>void *</>
5000 pointers. There is a <firstterm>passthrough</> pointer that is provided
5001 by the application when the event handler is registered with a
5002 <structname>PGconn</>. The passthrough pointer never changes for the
5003 life of the <structname>PGconn</> and all <structname>PGresult</>s
5004 generated from it; so if used, it must point to long-lived data.
5005 In addition there is an <firstterm>instance data</> pointer, which starts
5006 out NULL in every <structname>PGconn</> and <structname>PGresult</>.
5007 This pointer can be manipulated using the
5008 <function>PQinstanceData</function>,
5009 <function>PQsetInstanceData</function>,
5010 <function>PQresultInstanceData</function> and
5011 <function>PQsetResultInstanceData</function> functions. Note that
5012 unlike the passthrough pointer, instance data of a <structname>PGconn</>
5013 is not automatically inherited by <structname>PGresult</>s created from
5014 it. <application>libpq</application> does not know what passthrough
5015 and instance data pointers point to (if anything) and will never attempt
5016 to free them — that is the responsibility of the event handler.
5019 <sect2 id="libpq-events-types">
5020 <title>Event Types</title>
5023 The enum <literal>PGEventId</> names the types of events handled by
5024 the event system. All its values have names beginning with
5025 <literal>PGEVT</literal>. For each event type, there is a corresponding
5026 event info structure that carries the parameters passed to the event
5027 handlers. The event types are:
5032 <term><literal>PGEVT_REGISTER</literal></term>
5035 The register event occurs when <function>PQregisterEventProc</function>
5036 is called. It is the ideal time to initialize any
5037 <literal>instanceData</literal> an event procedure may need. Only one
5038 register event will be fired per event handler per connection. If the
5039 event procedure fails, the registration is aborted.
5048 When a <literal>PGEVT_REGISTER</literal> event is received, the
5049 <parameter>evtInfo</parameter> pointer should be cast to a
5050 <structname>PGEventRegister *</structname>. This structure contains a
5051 <structname>PGconn</structname> that should be in the
5052 <literal>CONNECTION_OK</literal> status; guaranteed if one calls
5053 <function>PQregisterEventProc</function> right after obtaining a good
5054 <structname>PGconn</structname>. When returning a failure code, all
5055 cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
5062 <term><literal>PGEVT_CONNRESET</literal></term>
5065 The connection reset event is fired on completion of
5066 <function>PQreset</function> or <function>PQresetPoll</function>. In
5067 both cases, the event is only fired if the reset was successful. If
5068 the event procedure fails, the entire connection reset will fail; the
5069 <structname>PGconn</structname> is put into
5070 <literal>CONNECTION_BAD</literal> status and
5071 <function>PQresetPoll</function> will return
5072 <literal>PGRES_POLLING_FAILED</literal>.
5081 When a <literal>PGEVT_CONNRESET</literal> event is received, the
5082 <parameter>evtInfo</parameter> pointer should be cast to a
5083 <structname>PGEventConnReset *</structname>. Although the contained
5084 <structname>PGconn</structname> was just reset, all event data remains
5085 unchanged. This event should be used to reset/reload/requery any
5086 associated <literal>instanceData</literal>. Note that even if the
5087 event procedure fails to process <literal>PGEVT_CONNRESET</>, it will
5088 still receive a <literal>PGEVT_CONNDESTROY</> event when the connection
5095 <term><literal>PGEVT_CONNDESTROY</literal></term>
5098 The connection destroy event is fired in response to
5099 <function>PQfinish</function>. It is the event procedure's
5100 responsibility to properly clean up its event data as libpq has no
5101 ability to manage this memory. Failure to clean up will lead
5108 } PGEventConnDestroy;
5111 When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
5112 <parameter>evtInfo</parameter> pointer should be cast to a
5113 <structname>PGEventConnDestroy *</structname>. This event is fired
5114 prior to <function>PQfinish</function> performing any other cleanup.
5115 The return value of the event procedure is ignored since there is no
5116 way of indicating a failure from <function>PQfinish</function>. Also,
5117 an event procedure failure should not abort the process of cleaning up
5124 <term><literal>PGEVT_RESULTCREATE</literal></term>
5127 The result creation event is fired in response to any query execution
5128 function that generates a result, including
5129 <function>PQgetResult</function>. This event will only be fired after
5130 the result has been created successfully.
5137 } PGEventResultCreate;
5140 When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
5141 <parameter>evtInfo</parameter> pointer should be cast to a
5142 <structname>PGEventResultCreate *</structname>. The
5143 <parameter>conn</parameter> is the connection used to generate the
5144 result. This is the ideal place to initialize any
5145 <literal>instanceData</literal> that needs to be associated with the
5146 result. If the event procedure fails, the result will be cleared and
5147 the failure will be propagated. The event procedure must not try to
5148 <function>PQclear</> the result object for itself. When returning a
5149 failure code, all cleanup must be performed as no
5150 <literal>PGEVT_RESULTDESTROY</literal> event will be sent.
5156 <term><literal>PGEVT_RESULTCOPY</literal></term>
5159 The result copy event is fired in response to
5160 <function>PQcopyResult</function>. This event will only be fired after
5161 the copy is complete. Only event procedures that have
5162 successfully handled the <literal>PGEVT_RESULTCREATE</literal>
5163 or <literal>PGEVT_RESULTCOPY</literal> event for the source result
5164 will receive <literal>PGEVT_RESULTCOPY</literal> events.
5169 const PGresult *src;
5171 } PGEventResultCopy;
5174 When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
5175 <parameter>evtInfo</parameter> pointer should be cast to a
5176 <structname>PGEventResultCopy *</structname>. The
5177 <parameter>src</parameter> result is what was copied while the
5178 <parameter>dest</parameter> result is the copy destination. This event
5179 can be used to provide a deep copy of <literal>instanceData</literal>,
5180 since <literal>PQcopyResult</literal> cannot do that. If the event
5181 procedure fails, the entire copy operation will fail and the
5182 <parameter>dest</parameter> result will be cleared. When returning a
5183 failure code, all cleanup must be performed as no
5184 <literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
5191 <term><literal>PGEVT_RESULTDESTROY</literal></term>
5194 The result destroy event is fired in response to a
5195 <function>PQclear</function>. It is the event procedure's
5196 responsibility to properly clean up its event data as libpq has no
5197 ability to manage this memory. Failure to clean up will lead
5204 } PGEventResultDestroy;
5207 When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
5208 <parameter>evtInfo</parameter> pointer should be cast to a
5209 <structname>PGEventResultDestroy *</structname>. This event is fired
5210 prior to <function>PQclear</function> performing any other cleanup.
5211 The return value of the event procedure is ignored since there is no
5212 way of indicating a failure from <function>PQclear</function>. Also,
5213 an event procedure failure should not abort the process of cleaning up
5221 <sect2 id="libpq-events-proc">
5222 <title>Event Callback Procedure</title>
5227 <literal>PGEventProc</literal>
5229 <primary>PGEventProc</primary>
5235 <literal>PGEventProc</literal> is a typedef for a pointer to an
5236 event procedure, that is, the user callback function that receives
5237 events from libpq. The signature of an event procedure must be
5240 int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
5243 The <parameter>evtId</parameter> parameter indicates which
5244 <literal>PGEVT</literal> event occurred. The
5245 <parameter>evtInfo</parameter> pointer must be cast to the appropriate
5246 structure type to obtain further information about the event.
5247 The <parameter>passThrough</parameter> parameter is the pointer
5248 provided to <function>PQregisterEventProc</function> when the event
5249 procedure was registered. The function should return a non-zero value
5250 if it succeeds and zero if it fails.
5254 A particular event procedure can be registered only once in any
5255 <structname>PGconn</>. This is because the address of the procedure
5256 is used as a lookup key to identify the associated instance data.
5263 <sect2 id="libpq-events-funcs">
5264 <title>Event Support Functions</title>
5269 <function>PQregisterEventProc</function>
5271 <primary>PQregisterEventProc</primary>
5277 Registers an event callback procedure with libpq.
5280 int PQregisterEventProc(PGconn *conn, PGEventProc proc,
5281 const char *name, void *passThrough);
5286 An event procedure must be registered once on each
5287 <structname>PGconn</> you want to receive events about. There is no
5288 limit, other than memory, on the number of event procedures that
5289 can be registered with a connection. The function returns a non-zero
5290 value if it succeeds and zero if it fails.
5294 The <parameter>proc</parameter> argument will be called when a libpq
5295 event is fired. Its memory address is also used to lookup
5296 <literal>instanceData</literal>. The <parameter>name</parameter>
5297 argument is used to refer to the event procedure in error messages.
5298 This value cannot be NULL or a zero-length string. The name string is
5299 copied into the <structname>PGconn</>, so what is passed need not be
5300 long-lived. The <parameter>passThrough</parameter> pointer is passed
5301 to the <parameter>proc</parameter> whenever an event occurs. This
5302 argument can be NULL.
5309 <function>PQsetInstanceData</function>
5311 <primary>PQsetInstanceData</primary>
5316 Sets the conn's instanceData for proc to data. This returns non-zero
5317 for success and zero for failure. (Failure is only possible if
5318 the proc has not been properly registered in the conn.)
5321 int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
5329 <function>PQinstanceData</function>
5331 <primary>PQinstanceData</primary>
5336 Returns the conn's instanceData associated with proc, or NULL
5340 void *PQinstanceData(const PGconn *conn, PGEventProc proc);
5348 <function>PQresultSetInstanceData</function>
5350 <primary>PQresultSetInstanceData</primary>
5355 Sets the result's instanceData for proc to data. This returns non-zero
5356 for success and zero for failure. (Failure is only possible if the
5357 proc has not been properly registered in the result.)
5360 int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
5368 <function>PQresultInstanceData</function>
5370 <primary>PQresultInstanceData</primary>
5375 Returns the result's instanceData associated with proc, or NULL
5379 void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
5387 <sect2 id="libpq-events-example">
5388 <title>Event Example</title>
5391 Here is a skeleton example of managing private data associated with
5392 libpq connections and results.
5396 /* required header for libpq events (note: includes libpq-fe.h) */
5397 #include <libpq-events.h>
5399 /* The instanceData */
5407 static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);
5414 PGconn *conn = PQconnectdb("dbname = postgres");
5416 if (PQstatus(conn) != CONNECTION_OK)
5418 fprintf(stderr, "Connection to database failed: %s",
5419 PQerrorMessage(conn));
5424 /* called once on any connection that should receive events.
5425 * Sends a PGEVT_REGISTER to myEventProc.
5427 if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
5429 fprintf(stderr, "Cannot register PGEventProc\n");
5434 /* conn instanceData is available */
5435 data = PQinstanceData(conn, myEventProc);
5437 /* Sends a PGEVT_RESULTCREATE to myEventProc */
5438 res = PQexec(conn, "SELECT 1 + 1");
5440 /* result instanceData is available */
5441 data = PQresultInstanceData(res, myEventProc);
5443 /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
5444 res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);
5446 /* result instanceData is available if PG_COPYRES_EVENTS was
5447 * used during the PQcopyResult call.
5449 data = PQresultInstanceData(res_copy, myEventProc);
5451 /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
5455 /* Sends a PGEVT_CONNDESTROY to myEventProc */
5462 myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
5466 case PGEVT_REGISTER:
5468 PGEventRegister *e = (PGEventRegister *)evtInfo;
5469 mydata *data = get_mydata(e->conn);
5471 /* associate app specific data with connection */
5472 PQsetInstanceData(e->conn, myEventProc, data);
5476 case PGEVT_CONNRESET:
5478 PGEventConnReset *e = (PGEventConnReset *)evtInfo;
5479 mydata *data = PQinstanceData(e->conn, myEventProc);
5482 memset(data, 0, sizeof(mydata));
5486 case PGEVT_CONNDESTROY:
5488 PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
5489 mydata *data = PQinstanceData(e->conn, myEventProc);
5491 /* free instance data because the conn is being destroyed */
5497 case PGEVT_RESULTCREATE:
5499 PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
5500 mydata *conn_data = PQinstanceData(e->conn, myEventProc);
5501 mydata *res_data = dup_mydata(conn_data);
5503 /* associate app specific data with result (copy it from conn) */
5504 PQsetResultInstanceData(e->result, myEventProc, res_data);
5508 case PGEVT_RESULTCOPY:
5510 PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
5511 mydata *src_data = PQresultInstanceData(e->src, myEventProc);
5512 mydata *dest_data = dup_mydata(src_data);
5514 /* associate app specific data with result (copy it from a result) */
5515 PQsetResultInstanceData(e->dest, myEventProc, dest_data);
5519 case PGEVT_RESULTDESTROY:
5521 PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
5522 mydata *data = PQresultInstanceData(e->result, myEventProc);
5524 /* free instance data because the result is being destroyed */
5530 /* unknown event id, just return TRUE. */
5535 return TRUE; /* event processing succeeded */
5541 <sect1 id="libpq-envars">
5542 <title>Environment Variables</title>
5544 <indexterm zone="libpq-envars">
5545 <primary>environment variable</primary>
5549 The following environment variables can be used to select default
5550 connection parameter values, which will be used by
5551 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
5552 <function>PQsetdb</> if no value is directly specified by the calling
5553 code. These are useful to avoid hard-coding database connection
5554 information into simple client applications, for example.
5560 <primary><envar>PGHOST</envar></primary>
5562 <envar>PGHOST</envar> sets the database server name.
5563 If this begins with a slash, it specifies Unix-domain communication
5564 rather than TCP/IP communication; the value is then the name of the
5565 directory in which the socket file is stored (in a default installation
5566 setup this would be <filename>/tmp</filename>).
5573 <primary><envar>PGHOSTADDR</envar></primary>
5575 <envar>PGHOSTADDR</envar> specifies the numeric IP address of the database
5576 server. This can be set instead of or in addition to <envar>PGHOST</envar>
5577 to avoid DNS lookup overhead. See the documentation of
5578 these parameters, under <function>PQconnectdb</function> above, for details
5579 on their interaction.
5582 When neither <envar>PGHOST</envar> nor <envar>PGHOSTADDR</envar> is set,
5583 the default behavior is to connect using a local Unix-domain socket; or on
5584 machines without Unix-domain sockets, <application>libpq</application> will
5585 attempt to connect to <literal>localhost</>.
5592 <primary><envar>PGPORT</envar></primary>
5594 <envar>PGPORT</envar> sets the TCP port number or Unix-domain socket
5595 file extension for communicating with the
5596 <productname>PostgreSQL</productname> server.
5603 <primary><envar>PGDATABASE</envar></primary>
5605 <envar>PGDATABASE</envar> sets the
5606 <productname>PostgreSQL</productname> database name.
5613 <primary><envar>PGUSER</envar></primary>
5615 <envar>PGUSER</envar> sets the user name used to connect to the
5623 <primary><envar>PGPASSWORD</envar></primary>
5625 <envar>PGPASSWORD</envar> sets the password used if the server
5626 demands password authentication. Use of this environment variable
5627 is not recommended for security reasons (some operating systems
5628 allow non-root users to see process environment variables via
5629 <application>ps</>); instead consider using the
5630 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
5637 <primary><envar>PGPASSFILE</envar></primary>
5639 <envar>PGPASSFILE</envar> specifies the name of the password file to
5640 use for lookups. If not set, it defaults to <filename>~/.pgpass</>
5641 (see <xref linkend="libpq-pgpass">).
5648 <primary><envar>PGSERVICE</envar></primary>
5650 <envar>PGSERVICE</envar>
5651 sets the service name to be looked up in
5652 <filename>pg_service.conf</filename>. This offers a shorthand way
5653 of setting all the parameters.
5660 <primary><envar>PGREALM</envar></primary>
5662 <envar>PGREALM</envar> sets the Kerberos realm to use with
5663 <productname>PostgreSQL</productname>, if it is different from the
5664 local realm. If <envar>PGREALM</envar> is set,
5665 <application>libpq</application> applications will attempt
5666 authentication with servers for this realm and use separate ticket
5667 files to avoid conflicts with local ticket files. This
5668 environment variable is only used if Kerberos authentication is
5669 selected by the server.
5676 <primary><envar>PGOPTIONS</envar></primary>
5678 <envar>PGOPTIONS</envar> sets additional run-time options for the
5679 <productname>PostgreSQL</productname> server. For example, setting
5680 <envar>PGOPTIONS</envar> to <literal>-c geqo=off</> sets the session's
5681 value of the <varname>geqo</> parameter to <literal>off</>.
5682 For a detailed discussion of the available options consult <xref
5683 linkend="runtime-config">.
5690 <primary><envar>PGSSLMODE</envar></primary>
5692 <envar>PGSSLMODE</envar> determines whether and with what priority
5693 an <acronym>SSL</> connection will be negotiated with the server.
5694 There are four modes: <literal>disable</> will attempt only an
5695 unencrypted <acronym>SSL</> connection; <literal>allow</> will
5696 negotiate, trying first a non-<acronym>SSL</> connection, then if
5697 that fails, trying an <acronym>SSL</> connection; <literal>prefer</>
5698 (the default) will negotiate, trying first an <acronym>SSL</>
5699 connection, then if that fails, trying a regular non-<acronym>SSL</>
5700 connection; <literal>require</> will try only an <acronym>SSL</>
5701 connection. If <productname>PostgreSQL</> is compiled without SSL
5702 support, using option <literal>require</> will cause an error, while
5703 options <literal>allow</> and <literal>prefer</> will be accepted
5704 but <application>libpq</> will not in fact attempt an <acronym>SSL</>
5712 <primary><envar>PGSSLVERIFY</envar></primary>
5714 <envar>PGSSLVERIFY</envar> controls how libpq verifies the certificate on the
5715 server when performing an <acronym>SSL</> connection. There are
5716 three options: <literal>none</> disables verification completely
5717 (not recommended!); <literal>cert</> enables verification that
5718 the certificate chains to a known CA only; <literal>cn</> will
5719 both verify that the certificate chains to a known CA and that
5720 the <literal>cn</> attribute of the certificate matches the
5721 hostname the connection is being made to (default).
5728 <primary><envar>PGREQUIRESSL</envar></primary>
5730 <envar>PGREQUIRESSL</envar> sets whether or not the connection must
5731 be made over <acronym>SSL</acronym>. If set to <quote>1</quote>,
5732 <application>libpq</> will refuse to connect if the server does not
5733 accept an <acronym>SSL</acronym> connection (equivalent to
5734 <literal>sslmode</> <literal>prefer</>). This option is deprecated
5735 in favor of the <literal>sslmode</> setting, and is only available
5736 if <productname>PostgreSQL</> is compiled with SSL support.
5743 <primary><envar>PGSSLKEY</envar></primary>
5745 <envar>PGSSLKEY</envar> specifies the hardware token that stores the
5746 secret key for the client certificate. The value of this variable
5747 should consist of a colon-separated engine name (engines are
5748 <productname>OpenSSL</> loadable modules) and an engine-specific key
5749 identifier. If this is not set, the secret key must be kept in a
5757 <primary><envar>PGKRBSRVNAME</envar></primary>
5759 <envar>PGKRBSRVNAME</envar> sets the Kerberos service name to use
5760 when authenticating with Kerberos 5 or GSSAPI.
5767 <primary><envar>PGGSSLIB</envar></primary>
5769 <envar>PGGSSLIB</envar> sets the GSS library to use for GSSAPI
5777 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
5779 <envar>PGCONNECT_TIMEOUT</envar> sets the maximum number of seconds
5780 that <application>libpq</application> will wait when attempting to
5781 connect to the <productname>PostgreSQL</productname> server. If
5782 unset or set to zero, <application>libpq</application> will wait
5783 indefinitely. It is not recommended to set the timeout to less than
5791 The following environment variables can be used to specify default
5792 behavior for each <productname>PostgreSQL</productname> session. (See
5793 also the <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
5794 and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">
5795 commands for ways to set default behavior on a per-user or per-database
5802 <primary><envar>PGDATESTYLE</envar></primary>
5804 <envar>PGDATESTYLE</envar> sets the default style of date/time
5805 representation. (Equivalent to <literal>SET datestyle TO
5813 <primary><envar>PGINTERVALSTYLE</envar></primary>
5815 <envar>PGINTERVALSTYLE</envar> sets the default style of interval
5816 representation. (Equivalent to <literal>SET intervalstyle TO
5824 <primary><envar>PGTZ</envar></primary>
5826 <envar>PGTZ</envar> sets the default time zone. (Equivalent to
5827 <literal>SET timezone TO ...</literal>.)
5834 <primary><envar>PGCLIENTENCODING</envar></primary>
5836 <envar>PGCLIENTENCODING</envar> sets the default client character
5837 set encoding. (Equivalent to <literal>SET client_encoding TO
5845 <primary><envar>PGGEQO</envar></primary>
5847 <envar>PGGEQO</envar> sets the default mode for the genetic query
5848 optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
5853 Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set"
5854 endterm="sql-set-title"> for information on correct values for these
5855 environment variables.
5859 The following environment variables determine internal behavior of
5860 <application>libpq</application>; they override compiled-in defaults.
5866 <primary><envar>PGSYSCONFDIR</envar></primary>
5868 <envar>PGSYSCONFDIR</envar> sets the directory containing the
5869 <filename>pg_service.conf</> file.
5876 <primary><envar>PGLOCALEDIR</envar></primary>
5878 <envar>PGLOCALEDIR</envar> sets the directory containing the
5879 <literal>locale</> files for message internationalization.
5888 <sect1 id="libpq-pgpass">
5889 <title>The Password File</title>
5891 <indexterm zone="libpq-pgpass">
5892 <primary>password file</primary>
5894 <indexterm zone="libpq-pgpass">
5895 <primary>.pgpass</primary>
5899 The file <filename>.pgpass</filename> in a user's home directory or the
5900 file referenced by <envar>PGPASSFILE</envar> can contain passwords to
5901 be used if the connection requires a password (and no password has been
5902 specified otherwise). On Microsoft Windows the file is named
5903 <filename>%APPDATA%\postgresql\pgpass.conf</> (where
5904 <filename>%APPDATA%</> refers to the Application Data subdirectory in
5905 the user's profile).
5909 This file should contain lines of the following format:
5911 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
5913 Each of the first four fields can be a literal value, or
5914 <literal>*</literal>, which matches anything. The password field from
5915 the first line that matches the current connection parameters will be
5916 used. (Therefore, put more-specific entries first when you are using
5917 wildcards.) If an entry needs to contain <literal>:</literal> or
5918 <literal>\</literal>, escape this character with <literal>\</literal>.
5919 A host name of <literal>localhost</> matches both TCP (host name
5920 <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
5921 or the default socket directory) connections coming from the local
5926 On Unix systems, the permissions on <filename>.pgpass</filename> must
5927 disallow any access to world or group; achieve this by the command
5928 <command>chmod 0600 ~/.pgpass</command>. If the permissions are less
5929 strict than this, the file will be ignored. On Microsoft Windows, it
5930 is assumed that the file is stored in a directory that is secure, so
5931 no special permissions check is made.
5936 <sect1 id="libpq-pgservice">
5937 <title>The Connection Service File</title>
5939 <indexterm zone="libpq-pgservice">
5940 <primary>connection service file</primary>
5942 <indexterm zone="libpq-pgservice">
5943 <primary>pg_service.conf</primary>
5947 The connection service file allows libpq connection parameters to be
5948 associated with a single service name. That service name can then be
5949 specified by a libpq connection, and the associated settings will be
5950 used. This allows connection parameters to be modified without requiring
5951 a recompile of the libpq application. The service name can also be
5952 specified using the <envar>PGSERVICE</envar> environment variable.
5956 To use this feature, copy
5957 <filename>share/pg_service.conf.sample</filename> to
5958 <filename>etc/pg_service.conf</filename> and edit the file to add
5959 service names and parameters. This file can be used for client-only
5960 installs too. The file's location can also be specified by the
5961 <envar>PGSYSCONFDIR</envar> environment variable.
5966 <sect1 id="libpq-ldap">
5967 <title>LDAP Lookup of Connection Parameters</title>
5969 <indexterm zone="libpq-ldap">
5970 <primary>LDAP connection parameter lookup</primary>
5974 If <application>libpq</application> has been compiled with LDAP support (option
5975 <literal><option>--with-ldap</option></literal> for <command>configure</command>)
5976 it is possible to retrieve connection options like <literal>host</literal>
5977 or <literal>dbname</literal> via LDAP from a central server.
5978 The advantage is that if the connection parameters for a database change,
5979 the connection information doesn't have to be updated on all client machines.
5983 LDAP connection parameter lookup uses the connection service file
5984 <filename>pg_service.conf</filename> (see <xref
5985 linkend="libpq-pgservice">). A line in a
5986 <filename>pg_service.conf</filename> stanza that starts with
5987 <literal>ldap://</literal> will be recognized as an LDAP URL and an
5988 LDAP query will be performed. The result must be a list of
5989 <literal>keyword = value</literal> pairs which will be used to set
5990 connection options. The URL must conform to RFC 1959 and be of the
5993 ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
5995 where <replaceable>hostname</replaceable> defaults to
5996 <literal>localhost</literal> and <replaceable>port</replaceable>
6001 Processing of <filename>pg_service.conf</filename> is terminated after
6002 a successful LDAP lookup, but is continued if the LDAP server cannot
6003 be contacted. This is to provide a fallback with further LDAP URL
6004 lines that point to different LDAP servers, classical <literal>keyword
6005 = value</literal> pairs, or default connection options. If you would
6006 rather get an error message in this case, add a syntactically incorrect
6007 line after the LDAP URL.
6011 A sample LDAP entry that has been created with the LDIF file
6014 dn:cn=mydatabase,dc=mycompany,dc=com
6017 objectclass:groupOfUniqueNames
6019 uniqueMember:host=dbserver.mycompany.com
6020 uniqueMember:port=5439
6021 uniqueMember:dbname=mydb
6022 uniqueMember:user=mydb_user
6023 uniqueMember:sslmode=require
6025 might be queried with the following LDAP URL:
6027 ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)
6032 You can also mix regular service file entries with LDAP lookups.
6033 A complete example for a stanza in <filename>pg_service.conf</filename>
6036 # only host and port are stored in LDAP, specify dbname and user explicitly
6040 ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
6047 <sect1 id="libpq-ssl">
6048 <title>SSL Support</title>
6050 <indexterm zone="libpq-ssl">
6051 <primary>SSL</primary>
6055 <productname>PostgreSQL</> has native support for using <acronym>SSL</>
6056 connections to encrypt client/server communications for increased
6057 security. See <xref linkend="ssl-tcp"> for details about the server-side
6058 <acronym>SSL</> functionality.
6062 <application>libpq</application> reads the system-wide
6063 <productname>OpenSSL</productname> configuration file. By default, this
6064 file is named <filename>openssl.cnf</filename> and is located in the
6065 directory reported by <literal>openssl version -d</>. This default
6066 can be overridden by setting environment variable
6067 <envar>OPENSSL_CONF</envar> to the name of the desired configuration
6072 When the <literal>sslverify</> parameter is set to <literal>cn</> or
6073 <literal>cert</>, libpq will verify that the server certificate is
6074 trustworthy by checking the certificate chain up to a <acronym>CA</>.
6075 For this to work, place the certificate of a trusted <acronym>CA</>
6076 in the file <filename>~/.postgresql/root.crt</> in the user's home directory.
6077 (On Microsoft Windows the file is named
6078 <filename>%APPDATA%\postgresql\root.crt</filename>.)
6079 <application>libpq</application> will then verify that the server's
6080 certificate is signed by one of the trusted certificate authorities.
6081 The SSL connection will fail if the server does not present a trusted
6082 certificate. Certificate Revocation List (CRL) entries are also checked
6083 if the file <filename>~/.postgresql/root.crl</filename> exists
6084 (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
6089 If the server requests a trusted client certificate,
6090 <application>libpq</application> will send the certificate stored in
6091 file <filename>~/.postgresql/postgresql.crt</> in the user's home
6092 directory. The certificate must be signed by one of the certificate
6093 authorities (<acronym>CA</acronym>) trusted by the server. A matching
6094 private key file <filename>~/.postgresql/postgresql.key</> must also
6095 be present, unless the secret key for the certificate is stored in a
6096 hardware token, as specified by <envar>PGSSLKEY</envar>. The private
6097 key file must not allow any access to world or group; achieve this by the
6098 command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
6099 On Microsoft Windows these files are named
6100 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
6101 <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
6102 is no special permissions check since the directory is presumed secure.
6106 If the environment variable <envar>PGSSLKEY</envar> is set, its value
6107 should consist of a colon-separated engine name and key identifier. In
6108 this case, <application>libpq</application> will load the specified
6109 engine, i.e. the <productname>OpenSSL</> module which supports special
6110 hardware, and reference the key with the specified identifier.
6111 Identifiers are engine-specific. Typically, cryptography hardware tokens
6112 do not reveal secret keys to the application. Instead, applications
6113 delegate all cryptography operations which require the secret key to
6118 If you are using <acronym>SSL</> inside your application (in addition
6119 to inside <application>libpq</application>), you can use
6120 <function>PQinitSSL(int)</> to tell <application>libpq</application>
6121 that the <acronym>SSL</> library has already been initialized by your
6123 <!-- If this URL changes replace it with a URL to www.archive.org. -->
6125 url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
6126 for details on the SSL API.
6129 <table id="libpq-ssl-file-usage">
6130 <title>Libpq/Client SSL File Usage</title>
6135 <entry>Contents</entry>
6136 <entry>Effect</entry>
6143 <entry><filename>~/.postgresql/postgresql.crt</></entry>
6144 <entry>client certificate</entry>
6145 <entry>requested by server</entry>
6149 <entry><filename>~/.postgresql/postgresql.key</></entry>
6150 <entry>client private key</entry>
6151 <entry>proves client certificate sent by owner; does not indicate
6152 certificate owner is trustworthy</entry>
6156 <entry><filename>~/.postgresql/root.crt</></entry>
6157 <entry>trusted certificate authorities</entry>
6158 <entry>checks server certificate is signed by a trusted certificate
6163 <entry><filename>~/.postgresql/root.crl</></entry>
6164 <entry>certificates revoked by certificate authorities</entry>
6165 <entry>server certificate must not be on this list</entry>
6175 <sect1 id="libpq-threading">
6176 <title>Behavior in Threaded Programs</title>
6178 <indexterm zone="libpq-threading">
6179 <primary>threads</primary>
6180 <secondary>with libpq</secondary>
6184 <application>libpq</application> is reentrant and thread-safe if the
6185 <filename>configure</filename> command-line option
6186 <literal>--enable-thread-safety</> was used when the
6187 <productname>PostgreSQL</productname> distribution was built. In
6188 addition, you might need to use additional compiler command-line
6189 options when you compile your application code. Refer to your
6190 system's documentation for information about how to build
6191 thread-enabled applications, or look in
6192 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
6193 and <literal>PTHREAD_LIBS</>. This function allows the querying of
6194 <application>libpq</application>'s thread-safe status:
6200 <function>PQisthreadsafe</function>
6202 <primary>PQisthreadsafe</primary>
6208 Returns the thread safety status of the
6209 <application>libpq</application> library.
6211 int PQisthreadsafe();
6216 Returns 1 if the <application>libpq</application> is thread-safe
6224 One thread restriction is that no two threads attempt to manipulate
6225 the same <structname>PGconn</> object at the same time. In particular,
6226 you cannot issue concurrent commands from different threads through
6227 the same connection object. (If you need to run concurrent commands,
6228 use multiple connections.)
6232 <structname>PGresult</> objects are read-only after creation, and so
6233 can be passed around freely between threads.
6237 The deprecated functions <function>PQrequestCancel</function> and
6238 <function>PQoidStatus</function> are not thread-safe and should not be
6239 used in multithread programs. <function>PQrequestCancel</function>
6240 can be replaced by <function>PQcancel</function>.
6241 <function>PQoidStatus</function> can be replaced by
6242 <function>PQoidValue</function>.
6246 If you are using Kerberos inside your application (in addition to inside
6247 <application>libpq</application>), you will need to do locking around
6248 Kerberos calls because Kerberos functions are not thread-safe. See
6249 function <function>PQregisterThreadLock</> in the
6250 <application>libpq</application> source code for a way to do cooperative
6251 locking between <application>libpq</application> and your application.
6255 If you experience problems with threaded applications, run the program
6256 in <filename>src/tools/thread</> to see if your platform has
6257 thread-unsafe functions. This program is run by
6258 <filename>configure</filename>, but for binary distributions your
6259 library might not match the library used to build the binaries.
6264 <sect1 id="libpq-build">
6265 <title>Building <application>libpq</application> Programs</title>
6267 <indexterm zone="libpq-build">
6268 <primary>compiling</primary>
6269 <secondary>libpq applications</secondary>
6273 To build (i.e., compile and link) a program using
6274 <application>libpq</application> you need to do all of the following
6280 Include the <filename>libpq-fe.h</filename> header file:
6282 #include <libpq-fe.h>
6284 If you failed to do that then you will normally get error messages
6285 from your compiler similar to
6287 foo.c: In function `main':
6288 foo.c:34: `PGconn' undeclared (first use in this function)
6289 foo.c:35: `PGresult' undeclared (first use in this function)
6290 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
6291 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
6292 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
6299 Point your compiler to the directory where the <productname>PostgreSQL</> header
6300 files were installed, by supplying the
6301 <literal>-I<replaceable>directory</replaceable></literal> option
6302 to your compiler. (In some cases the compiler will look into
6303 the directory in question by default, so you can omit this
6304 option.) For instance, your compile command line could look
6307 cc -c -I/usr/local/pgsql/include testprog.c
6309 If you are using makefiles then add the option to the
6310 <varname>CPPFLAGS</varname> variable:
6312 CPPFLAGS += -I/usr/local/pgsql/include
6317 If there is any chance that your program might be compiled by
6318 other users then you should not hardcode the directory location
6319 like that. Instead, you can run the utility
6320 <command>pg_config</command><indexterm><primary>pg_config</><secondary
6321 sortas="libpq">with libpq</></> to find out where the header
6322 files are on the local system:
6324 <prompt>$</prompt> pg_config --includedir
6325 <computeroutput>/usr/local/include</computeroutput>
6330 Failure to specify the correct option to the compiler will
6331 result in an error message such as
6333 testlibpq.c:8:22: libpq-fe.h: No such file or directory
6340 When linking the final program, specify the option
6341 <literal>-lpq</literal> so that the <application>libpq</application>
6342 library gets pulled in, as well as the option
6343 <literal>-L<replaceable>directory</replaceable></literal> to point
6344 the compiler to the directory where the
6345 <application>libpq</application> library resides. (Again, the
6346 compiler will search some directories by default.) For maximum
6347 portability, put the <option>-L</option> option before the
6348 <option>-lpq</option> option. For example:
6350 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
6355 You can find out the library directory using
6356 <command>pg_config</command> as well:
6358 <prompt>$</prompt> pg_config --libdir
6359 <computeroutput>/usr/local/pgsql/lib</computeroutput>
6364 Error messages that point to problems in this area could look like
6367 testlibpq.o: In function `main':
6368 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
6369 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
6370 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
6372 This means you forgot <option>-lpq</option>.
6374 /usr/bin/ld: cannot find -lpq
6376 This means you forgot the <option>-L</option> option or did not
6377 specify the right directory.
6386 <sect1 id="libpq-example">
6387 <title>Example Programs</title>
6390 These examples and others can be found in the
6391 directory <filename>src/test/examples</filename> in the source code
6395 <example id="libpq-example-1">
6396 <title><application>libpq</application> Example Program 1</title>
6402 * Test the C version of libpq, the PostgreSQL frontend library.
6404 #include <stdio.h>
6405 #include <stdlib.h>
6406 #include "libpq-fe.h"
6409 exit_nicely(PGconn *conn)
6416 main(int argc, char **argv)
6418 const char *conninfo;
6426 * If the user supplies a parameter on the command line, use it as the
6427 * conninfo string; otherwise default to setting dbname=postgres and using
6428 * environment variables or defaults for all other connection parameters.
6433 conninfo = "dbname = postgres";
6435 /* Make a connection to the database */
6436 conn = PQconnectdb(conninfo);
6438 /* Check to see that the backend connection was successfully made */
6439 if (PQstatus(conn) != CONNECTION_OK)
6441 fprintf(stderr, "Connection to database failed: %s",
6442 PQerrorMessage(conn));
6447 * Our test case here involves using a cursor, for which we must be inside
6448 * a transaction block. We could do the whole thing with a single
6449 * PQexec() of "select * from pg_database", but that's too trivial to make
6453 /* Start a transaction block */
6454 res = PQexec(conn, "BEGIN");
6455 if (PQresultStatus(res) != PGRES_COMMAND_OK)
6457 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
6463 * Should PQclear PGresult whenever it is no longer needed to avoid memory
6469 * Fetch rows from pg_database, the system catalog of databases
6471 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
6472 if (PQresultStatus(res) != PGRES_COMMAND_OK)
6474 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
6480 res = PQexec(conn, "FETCH ALL in myportal");
6481 if (PQresultStatus(res) != PGRES_TUPLES_OK)
6483 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
6488 /* first, print out the attribute names */
6489 nFields = PQnfields(res);
6490 for (i = 0; i < nFields; i++)
6491 printf("%-15s", PQfname(res, i));
6494 /* next, print out the rows */
6495 for (i = 0; i < PQntuples(res); i++)
6497 for (j = 0; j < nFields; j++)
6498 printf("%-15s", PQgetvalue(res, i, j));
6504 /* close the portal ... we don't bother to check for errors ... */
6505 res = PQexec(conn, "CLOSE myportal");
6508 /* end the transaction */
6509 res = PQexec(conn, "END");
6512 /* close the connection to the database and cleanup */
6520 <example id="libpq-example-2">
6521 <title><application>libpq</application> Example Program 2</title>
6526 * Test of the asynchronous notification interface
6528 * Start this program, then from psql in another window do
6530 * Repeat four times to get this program to exit.
6532 * Or, if you want to get fancy, try this:
6533 * populate a database with the following commands
6534 * (provided in src/test/examples/testlibpq2.sql):
6536 * CREATE TABLE TBL1 (i int4);
6538 * CREATE TABLE TBL2 (i int4);
6540 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
6541 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
6543 * and do this four times:
6545 * INSERT INTO TBL1 VALUES (10);
6547 #include <stdio.h>
6548 #include <stdlib.h>
6549 #include <string.h>
6550 #include <errno.h>
6551 #include <sys/time.h>
6552 #include "libpq-fe.h"
6555 exit_nicely(PGconn *conn)
6562 main(int argc, char **argv)
6564 const char *conninfo;
6571 * If the user supplies a parameter on the command line, use it as the
6572 * conninfo string; otherwise default to setting dbname=postgres and using
6573 * environment variables or defaults for all other connection parameters.
6578 conninfo = "dbname = postgres";
6580 /* Make a connection to the database */
6581 conn = PQconnectdb(conninfo);
6583 /* Check to see that the backend connection was successfully made */
6584 if (PQstatus(conn) != CONNECTION_OK)
6586 fprintf(stderr, "Connection to database failed: %s",
6587 PQerrorMessage(conn));
6592 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
6594 res = PQexec(conn, "LISTEN TBL2");
6595 if (PQresultStatus(res) != PGRES_COMMAND_OK)
6597 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
6603 * should PQclear PGresult whenever it is no longer needed to avoid memory
6608 /* Quit after four notifies are received. */
6610 while (nnotifies < 4)
6613 * Sleep until something happens on the connection. We use select(2)
6614 * to wait for input, but you could also use poll() or similar
6620 sock = PQsocket(conn);
6623 break; /* shouldn't happen */
6625 FD_ZERO(&input_mask);
6626 FD_SET(sock, &input_mask);
6628 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
6630 fprintf(stderr, "select() failed: %s\n", strerror(errno));
6634 /* Now check for input */
6635 PQconsumeInput(conn);
6636 while ((notify = PQnotifies(conn)) != NULL)
6639 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
6640 notify->relname, notify->be_pid);
6646 fprintf(stderr, "Done.\n");
6648 /* close the connection to the database and cleanup */
6656 <example id="libpq-example-3">
6657 <title><application>libpq</application> Example Program 3</>
6662 * Test out-of-line parameters and binary I/O.
6664 * Before running this, populate a database with the following commands
6665 * (provided in src/test/examples/testlibpq3.sql):
6667 * CREATE TABLE test1 (i int4, t text, b bytea);
6669 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
6670 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
6672 * The expected output is:
6676 * t = (11 bytes) 'joe's place'
6677 * b = (5 bytes) \000\001\002\003\004
6681 * t = (8 bytes) 'ho there'
6682 * b = (5 bytes) \004\003\002\001\000
6684 #include <stdio.h>
6685 #include <stdlib.h>
6686 #include <string.h>
6687 #include <sys/types.h>
6688 #include "libpq-fe.h"
6690 /* for ntohl/htonl */
6691 #include <netinet/in.h>
6692 #include <arpa/inet.h>
6696 exit_nicely(PGconn *conn)
6703 * This function prints a query result that is a binary-format fetch from
6704 * a table defined as in the comment above. We split it out because the
6705 * main() function uses it twice.
6708 show_binary_results(PGresult *res)
6716 /* Use PQfnumber to avoid assumptions about field order in result */
6717 i_fnum = PQfnumber(res, "i");
6718 t_fnum = PQfnumber(res, "t");
6719 b_fnum = PQfnumber(res, "b");
6721 for (i = 0; i < PQntuples(res); i++)
6729 /* Get the field values (we ignore possibility they are null!) */
6730 iptr = PQgetvalue(res, i, i_fnum);
6731 tptr = PQgetvalue(res, i, t_fnum);
6732 bptr = PQgetvalue(res, i, b_fnum);
6735 * The binary representation of INT4 is in network byte order, which
6736 * we'd better coerce to the local byte order.
6738 ival = ntohl(*((uint32_t *) iptr));
6741 * The binary representation of TEXT is, well, text, and since libpq
6742 * was nice enough to append a zero byte to it, it'll work just fine
6745 * The binary representation of BYTEA is a bunch of bytes, which could
6746 * include embedded nulls so we have to pay attention to field length.
6748 blen = PQgetlength(res, i, b_fnum);
6750 printf("tuple %d: got\n", i);
6751 printf(" i = (%d bytes) %d\n",
6752 PQgetlength(res, i, i_fnum), ival);
6753 printf(" t = (%d bytes) '%s'\n",
6754 PQgetlength(res, i, t_fnum), tptr);
6755 printf(" b = (%d bytes) ", blen);
6756 for (j = 0; j < blen; j++)
6757 printf("\\%03o", bptr[j]);
6763 main(int argc, char **argv)
6765 const char *conninfo;
6768 const char *paramValues[1];
6769 int paramLengths[1];
6770 int paramFormats[1];
6771 uint32_t binaryIntVal;
6774 * If the user supplies a parameter on the command line, use it as the
6775 * conninfo string; otherwise default to setting dbname=postgres and using
6776 * environment variables or defaults for all other connection parameters.
6781 conninfo = "dbname = postgres";
6783 /* Make a connection to the database */
6784 conn = PQconnectdb(conninfo);
6786 /* Check to see that the backend connection was successfully made */
6787 if (PQstatus(conn) != CONNECTION_OK)
6789 fprintf(stderr, "Connection to database failed: %s",
6790 PQerrorMessage(conn));
6795 * The point of this program is to illustrate use of PQexecParams() with
6796 * out-of-line parameters, as well as binary transmission of data.
6798 * This first example transmits the parameters as text, but receives the
6799 * results in binary format. By using out-of-line parameters we can
6800 * avoid a lot of tedious mucking about with quoting and escaping, even
6801 * though the data is text. Notice how we don't have to do anything
6802 * special with the quote mark in the parameter value.
6805 /* Here is our out-of-line parameter value */
6806 paramValues[0] = "joe's place";
6808 res = PQexecParams(conn,
6809 "SELECT * FROM test1 WHERE t = $1",
6811 NULL, /* let the backend deduce param type */
6813 NULL, /* don't need param lengths since text */
6814 NULL, /* default to all text params */
6815 1); /* ask for binary results */
6817 if (PQresultStatus(res) != PGRES_TUPLES_OK)
6819 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
6824 show_binary_results(res);
6829 * In this second example we transmit an integer parameter in binary
6830 * form, and again retrieve the results in binary form.
6832 * Although we tell PQexecParams we are letting the backend deduce
6833 * parameter type, we really force the decision by casting the parameter
6834 * symbol in the query text. This is a good safety measure when sending
6835 * binary parameters.
6838 /* Convert integer value "2" to network byte order */
6839 binaryIntVal = htonl((uint32_t) 2);
6841 /* Set up parameter arrays for PQexecParams */
6842 paramValues[0] = (char *) &binaryIntVal;
6843 paramLengths[0] = sizeof(binaryIntVal);
6844 paramFormats[0] = 1; /* binary */
6846 res = PQexecParams(conn,
6847 "SELECT * FROM test1 WHERE i = $1::int4",
6849 NULL, /* let the backend deduce param type */
6853 1); /* ask for binary results */
6855 if (PQresultStatus(res) != PGRES_TUPLES_OK)
6857 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
6862 show_binary_results(res);
6866 /* close the connection to the database and cleanup */
projects / postgresql / blob