2 $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.181 2005/05/30 19:32:44 momjian Exp $
6 <title><application>libpq</application> - C Library</title>
8 <indexterm zone="libpq">
9 <primary>libpq</primary>
12 <indexterm zone="libpq">
17 <application>libpq</application> is the <acronym>C</acronym>
18 application programmer's interface to <productname>PostgreSQL</>.
19 <application>libpq</> is a set of library functions that allow
20 client programs to pass queries to the <productname>PostgreSQL</>
21 backend server and to receive the results of these queries.
25 <application>libpq</> is also the underlying engine for several
26 other <productname>PostgreSQL</> application interfaces, including
27 those written for C++, Perl, Python, Tcl and <application>ECPG</>.
28 So some aspects of <application>libpq</>'s behavior will be
29 important to you if you use one of those packages. In particular,
30 <xref linkend="libpq-envars">,
31 <xref linkend="libpq-pgpass"> and
32 <xref linkend="libpq-ssl">
33 describe behavior that is visible to the user of any application
34 that uses <application>libpq</>.
38 Some short programs are included at the end of this chapter (<xref linkend="libpq-example">) to show how
39 to write programs that use <application>libpq</application>. There are also several
40 complete examples of <application>libpq</application> applications in the
41 directory <filename>src/test/examples</filename> in the source code distribution.
45 Client programs that use <application>libpq</application> must
46 include the header file
47 <filename>libpq-fe.h</filename><indexterm><primary>libpq-fe.h</></>
48 and must link with the <application>libpq</application> library.
51 <sect1 id="libpq-connect">
52 <title>Database Connection Control Functions</title>
55 The following functions deal with making a connection to a
56 <productname>PostgreSQL</productname> backend server. An
57 application program can have several backend connections open at
58 one time. (One reason to do that is to access more than one
59 database.) Each connection is represented by a
60 <structname>PGconn</><indexterm><primary>PGconn</></> object, which
61 is obtained from the function <function>PQconnectdb</> or
62 <function>PQsetdbLogin</>. Note that these functions will always
63 return a non-null object pointer, unless perhaps there is too
64 little memory even to allocate the <structname>PGconn</> object.
65 The <function>PQstatus</> function should be called to check
66 whether a connection was successfully made before queries are sent
67 via the connection object.
71 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
74 Makes a new connection to the database server.
76 PGconn *PQconnectdb(const char *conninfo);
81 This function opens a new database connection using the parameters taken
82 from the string <literal>conninfo</literal>. Unlike <function>PQsetdbLogin</> below,
83 the parameter set can be extended without changing the function signature,
84 so use of this function (or its nonblocking analogues <function>PQconnectStart</>
85 and <function>PQconnectPoll</function>) is preferred for new application programming.
90 can be empty to use all default parameters, or it can contain one or more
91 parameter settings separated by whitespace.
92 Each parameter setting is in the form <literal>keyword = value</literal>.
93 Spaces around the equal sign are optional.
94 To write an empty value or a value containing
95 spaces, surround it with single quotes, e.g.,
96 <literal>keyword = 'a value'</literal>.
97 Single quotes and backslashes within the value must be escaped with a
98 backslash, i.e., <literal>\'</literal> and <literal>\\</literal>.
102 The currently recognized parameter key words are:
106 <term><literal>host</literal></term>
109 Name of host to connect to.<indexterm><primary>host name</></>
110 If this begins with a slash, it specifies Unix-domain
111 communication rather than TCP/IP communication; the value is the
112 name of the directory in which the socket file is stored. The
113 default behavior when <literal>host</literal> is not specified
114 is to connect to a Unix-domain
115 socket<indexterm><primary>Unix domain socket</></> in
116 <filename>/tmp</filename> (or whatever socket directory was specified
117 when <productname>PostgreSQL</> was built). On machines without
118 Unix-domain sockets, the default is to connect to <literal>localhost</>.
124 <term><literal>hostaddr</literal></term>
127 Numeric IP address of host to connect to. This should be in the
128 standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
129 your machine supports IPv6, you can also use those addresses.
130 TCP/IP communication is
131 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 may be important in
136 applications with time constraints. However, Kerberos 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">).
154 Without either a host name or host address,
155 <application>libpq</application> will connect using a
156 local Unix-domain socket; or on machines without Unix-domain
157 sockets, it will attempt to connect to <literal>localhost</>.
163 <term><literal>port</literal></term>
166 Port number to connect to at the server host, or socket file
167 name extension for Unix-domain
168 connections.<indexterm><primary>port</></>
174 <term><literal>dbname</literal></term>
177 The database name. Defaults to be the same as the user name.
183 <term><literal>user</literal></term>
186 <productname>PostgreSQL</productname> user name to connect as.
187 Defaults to be the same as the operating system name of the user
188 running the application.
194 <term><literal>password</literal></term>
197 Password to be used if the server demands password authentication.
203 <term><literal>connect_timeout</literal></term>
206 Maximum wait for connection, in seconds (write as a decimal integer
207 string). Zero or not specified means wait indefinitely. It is not
208 recommended to use a timeout of less than 2 seconds.
214 <term><literal>options</literal></term>
217 Command-line options to be sent to the server.
223 <term><literal>tty</literal></term>
226 Ignored (formerly, this specified where to send server debug output).
232 <term><literal>sslmode</literal></term>
235 This option determines whether or with what priority an
236 <acronym>SSL</> connection will be negotiated with the
237 server. There are four modes: <literal>disable</> will attempt
238 only an unencrypted <acronym>SSL</> connection;
239 <literal>allow</> will negotiate, trying first a
240 non-<acronym>SSL</> connection, then if that fails, trying an
241 <acronym>SSL</> connection; <literal>prefer</> (the default)
242 will negotiate, trying first an <acronym>SSL</> connection,
243 then if that fails, trying a regular non-<acronym>SSL</>
244 connection; <literal>require</> will try only an
245 <acronym>SSL</> connection.
249 If <productname>PostgreSQL</> is compiled without SSL support,
250 using option <literal>require</> will cause an error, while
251 options <literal>allow</> and <literal>prefer</> will be
252 accepted but <application>libpq</> will not in fact attempt
254 connection.<indexterm><primary>SSL</><secondary
255 sortas="libpq">with libpq</></indexterm>
261 <term><literal>requiressl</literal></term>
264 This option is deprecated in favor of the <literal>sslmode</>
269 If set to 1, an <acronym>SSL</acronym> connection to the server
270 is required (this is equivalent to <literal>sslmode</>
271 <literal>require</>). <application>libpq</> will then refuse
272 to connect if the server does not accept an
273 <acronym>SSL</acronym> connection. If set to 0 (default),
274 <application>libpq</> will negotiate the connection type with
275 the server (equivalent to <literal>sslmode</>
276 <literal>prefer</>). This option is only available if
277 <productname>PostgreSQL</> is compiled with SSL support.
283 <term><literal>service</literal></term>
286 Service name to use for additional parameters. It specifies a service
287 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
288 This allows applications to specify only a service name so connection parameters
289 can be centrally maintained. See
290 <filename>share/pg_service.conf.sample</> in the installation
291 directory for information on how to set up the file.
297 If any parameter is unspecified, then the corresponding
298 environment variable (see <xref linkend="libpq-envars">)
299 is checked. If the environment variable is not set either,
300 then the indicated built-in defaults are used.
306 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
309 Makes a new connection to the database server.
311 PGconn *PQsetdbLogin(const char *pghost,
313 const char *pgoptions,
322 This is the predecessor of <function>PQconnectdb</function> with a fixed
323 set of parameters. It has the same functionality except that the
324 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
325 empty string for any one of the fixed parameters that is to be defaulted.
331 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
334 Makes a new connection to the database server.
336 PGconn *PQsetdb(char *pghost,
345 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
346 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
347 for backward compatibility with very old programs.
353 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
354 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
357 <indexterm><primary>nonblocking connection</primary></indexterm>
358 Make a connection to the database server in a nonblocking manner.
360 PGconn *PQconnectStart(const char *conninfo);
363 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
367 These two functions are used to open a connection to a database server such
368 that your application's thread of execution is not blocked on remote I/O
370 The point of this approach is that the waits for I/O to complete can occur
371 in the application's main loop, rather than down inside
372 <function>PQconnectdb</>, and so the application can manage this
373 operation in parallel with other activities.
376 The database connection is made using the parameters taken from the string
377 <literal>conninfo</literal>, passed to <function>PQconnectStart</function>. This string is in
378 the same format as described above for <function>PQconnectdb</function>.
381 Neither <function>PQconnectStart</function> nor <function>PQconnectPoll</function> will block, so long as a number of
382 restrictions are met:
386 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
387 name and reverse name queries are not made. See the documentation of
388 these parameters under <function>PQconnectdb</function> above for details.
394 If you call <function>PQtrace</function>, ensure that the stream object
395 into which you trace will not block.
401 You ensure that the socket is in the appropriate state
402 before calling <function>PQconnectPoll</function>, as described below.
409 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
410 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
411 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
412 representing a valid connection to the database). On return from
413 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
414 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
417 If <function>PQconnectStart</> succeeds, the next stage is to poll
418 <application>libpq</> so that it may proceed with the connection sequence.
419 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
420 socket underlying the database connection.
421 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
422 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
423 read (as indicated by <function>select()</>, <function>poll()</>, or
424 similar system function).
425 Then call <function>PQconnectPoll(conn)</function> again.
426 Conversely, if <function>PQconnectPoll(conn)</function> last returned
427 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
428 to write, then call <function>PQconnectPoll(conn)</function> again.
429 If you have yet to call
430 <function>PQconnectPoll</function>, i.e., just after the call to
431 <function>PQconnectStart</function>, behave as if it last returned
432 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
433 <function>PQconnectPoll(conn)</function> returns
434 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
435 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
436 has been successfully made.
440 At any time during connection, the status of the connection may be
441 checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
442 connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
443 connection is ready. Both of these states are equally detectable
444 from the return value of <function>PQconnectPoll</>, described above. Other states may also occur
445 during (and only during) an asynchronous connection procedure. These
446 indicate the current stage of the connection procedure and may be useful
447 to provide feedback to the user for example. These statuses are:
451 <term><symbol>CONNECTION_STARTED</symbol></term>
454 Waiting for connection to be made.
460 <term><symbol>CONNECTION_MADE</symbol></term>
463 Connection OK; waiting to send.
469 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
472 Waiting for a response from the server.
478 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
481 Received authentication; waiting for backend start-up to finish.
487 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
490 Negotiating SSL encryption.
496 <term><symbol>CONNECTION_SETENV</symbol></term>
499 Negotiating environment-driven parameter settings.
505 Note that, although these constants will remain (in order to maintain
506 compatibility), an application should never rely upon these occurring in a
507 particular order, or at all, or on the status always being one of these
508 documented values. An application might do something like this:
510 switch(PQstatus(conn))
512 case CONNECTION_STARTED:
513 feedback = "Connecting...";
516 case CONNECTION_MADE:
517 feedback = "Connected to server...";
523 feedback = "Connecting...";
529 The <literal>connect_timeout</literal> connection parameter is ignored
530 when using <function>PQconnectPoll</function>; it is the application's
531 responsibility to decide whether an excessive amount of time has elapsed.
532 Otherwise, <function>PQconnectStart</function> followed by a
533 <function>PQconnectPoll</function> loop is equivalent to
534 <function>PQconnectdb</function>.
538 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
539 <function>PQfinish</function> when you are finished with it, in order to dispose of
540 the structure and any associated memory blocks. This must be done even if
541 the connection attempt fails or is abandoned.
547 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
550 Returns the default connection options.
552 PQconninfoOption *PQconndefaults(void);
556 char *keyword; /* The keyword of the option */
557 char *envvar; /* Fallback environment variable name */
558 char *compiled; /* Fallback compiled in default value */
559 char *val; /* Option's current value, or NULL */
560 char *label; /* Label for field in connect dialog */
561 char *dispchar; /* Character to display for this field
562 in a connect dialog. Values are:
563 "" Display entered value as is
564 "*" Password field - hide value
565 "D" Debug option - don't show by default */
566 int dispsize; /* Field size in characters for dialog */
572 Returns a connection options array. This may
573 be used to determine all possible <function>PQconnectdb</function> options and their
574 current default values. The return value points to an array of
575 <structname>PQconninfoOption</structname> structures, which ends with an entry having a null
576 <structfield>keyword</> pointer. Note that the current default values
577 (<structfield>val</structfield> fields)
578 will depend on environment variables and other context.
579 Callers must treat the connection options data as read-only.
583 After processing the options array, free it by passing it to
584 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
585 is leaked for each call to <function>PQconndefaults</function>.
592 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
595 Closes the connection to the server. Also frees
596 memory used by the <structname>PGconn</structname> object.
598 void PQfinish(PGconn *conn);
603 Note that even if the server connection attempt fails (as
604 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
605 to free the memory used by the <structname>PGconn</structname> object.
606 The <structname>PGconn</> pointer must not be used again after
607 <function>PQfinish</function> has been called.
613 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
616 Resets the communication channel to the server.
618 void PQreset(PGconn *conn);
623 This function will close the connection
624 to the server and attempt to reestablish a new
625 connection to the same server, using all the same
626 parameters previously used. This may be useful for
627 error recovery if a working connection is lost.
633 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
634 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
637 Reset the communication channel to the server, in a nonblocking manner.
639 int PQresetStart(PGconn *conn);
642 PostgresPollingStatusType PQresetPoll(PGconn *conn);
647 These functions will close the connection to the server and attempt to
648 reestablish a new connection to the same server, using all the same
649 parameters previously used. This may be useful for error recovery if a
650 working connection is lost. They differ from <function>PQreset</function> (above) in that they
651 act in a nonblocking manner. These functions suffer from the same
652 restrictions as <function>PQconnectStart</> and <function>PQconnectPoll</>.
655 To initiate a connection reset, call <function>PQresetStart</function>. If it returns 0, the reset has failed. If it returns 1,
656 poll the reset using <function>PQresetPoll</function> in exactly the same way as you would
657 create the connection using <function>PQconnectPoll</function>.
666 <sect1 id="libpq-status">
667 <title>Connection Status Functions</title>
670 These functions may be used to interrogate the status
671 of an existing database connection object.
676 <indexterm><primary>libpq-fe.h</></>
677 <indexterm><primary>libpq-int.h</></>
678 <application>libpq</application> application programmers should be careful to
679 maintain the <structname>PGconn</structname> abstraction. Use the accessor
680 functions described below to get
681 at the contents of <structname>PGconn</structname>. Avoid directly referencing the fields of the
682 <structname>PGconn</> structure because they are subject to change in the future.
683 (Beginning in <productname>PostgreSQL</productname> release 6.4, the
684 definition of the <type>struct</type> behind <structname>PGconn</> is not even provided in <filename>libpq-fe.h</filename>.
685 If you have old code that accesses <structname>PGconn</structname> fields directly, you can keep using it
686 by including <filename>libpq-int.h</filename> too, but you are encouraged to fix the code
692 The following functions return parameter values established at connection.
693 These values are fixed for the life of the <structname>PGconn</> object.
697 <term><function>PQdb</function><indexterm><primary>PQdb</></></term>
700 Returns the database name of the connection.
702 char *PQdb(const PGconn *conn);
709 <term><function>PQuser</function><indexterm><primary>PQuser</></></term>
712 Returns the user name of the connection.
714 char *PQuser(const PGconn *conn);
721 <term><function>PQpass</function><indexterm><primary>PQpass</></></term>
724 Returns the password of the connection.
726 char *PQpass(const PGconn *conn);
733 <term><function>PQhost</function><indexterm><primary>PQhost</></></term>
736 Returns the server host name of the connection.
738 char *PQhost(const PGconn *conn);
745 <term><function>PQport</function><indexterm><primary>PQport</></></term>
748 Returns the port of the connection.
750 char *PQport(const PGconn *conn);
757 <term><function>PQtty</function><indexterm><primary>PQtty</></></term>
760 Returns the debug <acronym>TTY</acronym> of the connection.
761 (This is obsolete, since the server no longer pays attention
762 to the <acronym>TTY</acronym> setting, but the function remains
763 for backwards compatibility.)
765 char *PQtty(const PGconn *conn);
772 <term><function>PQoptions</function><indexterm><primary>PQoptions</></></term>
775 Returns the command-line options passed in the connection request.
777 char *PQoptions(const PGconn *conn);
786 The following functions return status data that can change as operations
787 are executed on the <structname>PGconn</> object.
791 <term><function>PQstatus</function><indexterm><primary>PQstatus</></></term>
794 Returns the status of the connection.
796 ConnStatusType PQstatus(const PGconn *conn);
801 The status can be one of a number of values.
802 However, only two of these are
803 seen outside of an asynchronous connection procedure:
804 <literal>CONNECTION_OK</literal> and
805 <literal>CONNECTION_BAD</literal>. A good
806 connection to the database has the status <literal>CONNECTION_OK</literal>.
808 attempt is signaled by status
809 <literal>CONNECTION_BAD</literal>.
810 Ordinarily, an OK status will remain so until
811 <function>PQfinish</function>, but a
812 communications failure might result in the status changing to
813 <literal>CONNECTION_BAD</literal> prematurely.
814 In that case the application
815 could try to recover by calling <function>PQreset</function>.
819 See the entry for <function>PQconnectStart</> and <function>PQconnectPoll</> with regards
820 to other status codes
827 <term><function>PQtransactionStatus</function><indexterm><primary>PQtransactionStatus</></></term>
830 Returns the current in-transaction status of the server.
832 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
835 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
836 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
837 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
838 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
839 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
840 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
841 has been sent to the server and not yet completed.
845 <function>PQtransactionStatus</> will give incorrect results when using
846 a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
847 set to off. The server-side autocommit feature has been
848 deprecated and does not exist in later server versions.
855 <term><function>PQparameterStatus</function><indexterm><primary>PQparameterStatus</></></term>
858 Looks up a current parameter setting of the server.
860 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
863 Certain parameter values are reported by the server automatically at
864 connection startup or whenever their values change.
865 <function>PQparameterStatus</> can be used to interrogate these settings.
866 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
867 if the parameter is not known.
871 Parameters reported as of the current release include
872 <literal>server_version</>,
873 <literal>server_encoding</>,
874 <literal>client_encoding</>,
875 <literal>is_superuser</>,
876 <literal>session_authorization</>,
877 <literal>DateStyle</>,
878 <literal>TimeZone</>, and
879 <literal>integer_datetimes</>.
880 (<literal>server_encoding</>, <literal>TimeZone</>, and
881 <literal>integer_datetimes</> were not reported by releases before 8.0.)
883 <literal>server_version</>,
884 <literal>server_encoding</> and
885 <literal>integer_datetimes</>
886 cannot change after startup.
890 Pre-3.0-protocol servers do not report parameter settings, but
891 <application>libpq</> includes logic to obtain values for
892 <literal>server_version</> and <literal>client_encoding</> anyway.
893 Applications are encouraged to use <function>PQparameterStatus</>
894 rather than <foreignphrase>ad hoc</> code to determine these values.
896 that on a pre-3.0 connection, changing <literal>client_encoding</> via
897 <command>SET</> after connection startup will not be reflected by
898 <function>PQparameterStatus</>.) For <literal>server_version</>,
899 see also <function>PQserverVersion</>, which returns the information
900 in a numeric form that is much easier to compare against.
904 Although the returned pointer is declared <literal>const</>, it in fact
905 points to mutable storage associated with the <literal>PGconn</> structure.
906 It is unwise to assume the pointer will remain valid across queries.
912 <term><function>PQprotocolVersion</function><indexterm><primary>PQprotocolVersion</></></term>
915 Interrogates the frontend/backend protocol being used.
917 int PQprotocolVersion(const PGconn *conn);
919 Applications may wish to use this to determine whether certain features
921 Currently, the possible values are 2 (2.0 protocol), 3 (3.0 protocol),
922 or zero (connection bad). This will not change after connection
923 startup is complete, but it could theoretically change during a connection
924 reset. The 3.0 protocol will normally be used when communicating with
925 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers support
926 only protocol 2.0. (Protocol 1.0 is obsolete and not supported by <application>libpq</application>.)
932 <term><function>PQserverVersion</function><indexterm><primary>PQserverVersion</></></term>
935 Returns an integer representing the backend version.
937 int PQserverVersion(const PGconn *conn);
939 Applications may use this to determine the version of the database server they
940 are connected to. The number is formed by converting the major, minor, and
941 revision numbers into two-decimal-digit numbers and appending them
942 together. For example, version 7.4.2 will be returned as 70402, and version
943 8.1 will be returned as 80100 (leading zeroes are not shown). Zero is
944 returned if the connection is bad.
950 <term><function>PQerrorMessage</function><indexterm><primary>PQerrorMessage</></></term>
953 <indexterm><primary>error message</></>
954 Returns the error message most recently generated by
955 an operation on the connection.
957 char *PQerrorMessage(const PGconn *conn);
962 Nearly all <application>libpq</> functions will set a message for
963 <function>PQerrorMessage</function> if they fail.
964 Note that by <application>libpq</application> convention, a nonempty
965 <function>PQerrorMessage</function> result will
966 include a trailing newline. The caller should not free the result
967 directly. It will be freed when the associated <structname>PGconn</>
968 handle is passed to <function>PQfinish</function>. The result string
969 should not be expected to remain the same across operations on the
970 <literal>PGconn</> structure.
976 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
979 Obtains the file descriptor number of the connection socket to
980 the server. A valid descriptor will be greater than or equal
981 to 0; a result of -1 indicates that no server connection is
982 currently open. (This will not change during normal operation,
983 but could change during connection setup or reset.)
985 int PQsocket(const PGconn *conn);
992 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
995 Returns the process <acronym>ID</acronym>
996 (PID)<indexterm><primary>PID</><secondary>determining PID of
997 server process</><tertiary>in libpq</></> of the backend server
998 process handling this connection.
1000 int PQbackendPID(const PGconn *conn);
1005 The backend <acronym>PID</acronym> is useful for debugging
1006 purposes and for comparison to <command>NOTIFY</command>
1007 messages (which include the <acronym>PID</acronym> of the
1008 notifying backend process). Note that the
1009 <acronym>PID</acronym> belongs to a process executing on the
1010 database server host, not the local host!
1016 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1019 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1020 Returns the SSL structure used in the connection, or null
1021 if SSL is not in use.
1023 SSL *PQgetssl(const PGconn *conn);
1028 This structure can be used to verify encryption levels, check
1029 server certificates, and more. Refer to the <productname>OpenSSL</> documentation
1030 for information about this structure.
1033 You must define <symbol>USE_SSL</symbol> in order to get the
1034 correct prototype for this function. Doing this will also
1035 automatically include <filename>ssl.h</filename> from <productname>OpenSSL</productname>.
1045 <sect1 id="libpq-exec">
1046 <title>Command Execution Functions</title>
1049 Once a connection to a database server has been successfully
1050 established, the functions described here are used to perform
1051 SQL queries and commands.
1054 <sect2 id="libpq-exec-main">
1055 <title>Main Functions</title>
1060 <term><function>PQexec</function><indexterm><primary>PQexec</></></term>
1063 Submits a command to the server
1064 and waits for the result.
1066 PGresult *PQexec(PGconn *conn, const char *command);
1071 Returns a <structname>PGresult</structname> pointer or possibly a null pointer.
1072 A non-null pointer will generally be returned except in
1073 out-of-memory conditions or serious errors such as inability
1074 to send the command to the server.
1075 If a null pointer is returned, it
1076 should be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result.
1077 Use <function>PQerrorMessage</function> to get more information
1084 It is allowed to include multiple SQL commands (separated by semicolons) in
1085 the command string. Multiple queries sent in a single <function>PQexec</>
1086 call are processed in a single transaction, unless there are explicit
1087 <command>BEGIN</command>/<command>COMMIT</command> commands included in the query string to divide it into multiple
1088 transactions. Note however that the returned <structname>PGresult</structname>
1089 structure describes only the result of the last command executed from the
1090 string. Should one of the commands fail, processing of the string stops with
1091 it and the returned <structname>PGresult</structname> describes the error
1098 <term><function>PQexecParams</function><indexterm><primary>PQexecParams</></></term>
1101 Submits a command to the server and waits for the result,
1102 with the ability to pass parameters separately from the SQL
1105 PGresult *PQexecParams(PGconn *conn,
1106 const char *command,
1108 const Oid *paramTypes,
1109 const char * const *paramValues,
1110 const int *paramLengths,
1111 const int *paramFormats,
1117 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1118 functionality: parameter values can be specified separately from the command
1119 string proper, and query results can be requested in either text or binary
1120 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1121 connections; it will fail when using protocol 2.0.
1125 If parameters are used, they are referred to in the command string
1126 as <literal>$1</>, <literal>$2</>, etc.
1127 <parameter>nParams</> is the number of parameters supplied; it is the length
1128 of the arrays <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1129 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1130 array pointers may be <symbol>NULL</symbol> when <parameter>nParams</> is zero.)
1131 <parameter>paramTypes[]</> specifies, by OID, the data types to be assigned to
1132 the parameter symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>, or any particular
1133 element in the array is zero, the server assigns a data type to the parameter
1134 symbol in the same way it would do for an untyped literal string.
1135 <parameter>paramValues[]</> specifies the actual values of the parameters.
1136 A null pointer in this array means the corresponding parameter is null;
1137 otherwise the pointer points to a zero-terminated text string (for text
1138 format) or binary data in the format expected by the server (for binary
1140 <parameter>paramLengths[]</> specifies the actual data lengths of
1141 binary-format parameters. It is ignored for null parameters and text-format
1142 parameters. The array pointer may be null when there are no binary
1144 <parameter>paramFormats[]</> specifies whether parameters are text (put a zero
1145 in the array) or binary (put a one in the array). If the array pointer is
1146 null then all parameters are presumed to be text.
1147 <parameter>resultFormat</> is zero to obtain results in text format, or one to
1148 obtain results in binary format. (There is not currently a provision to
1149 obtain different result columns in different formats, although that is
1150 possible in the underlying protocol.)
1156 The primary advantage of <function>PQexecParams</> over <function>PQexec</>
1157 is that parameter values may be separated from the command string, thus
1158 avoiding the need for tedious and error-prone quoting and escaping.
1160 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most one SQL
1161 command in the given string. (There can be semicolons in it, but not more
1162 than one nonempty command.) This is a limitation of the underlying protocol,
1163 but has some usefulness as an extra defense against SQL-injection attacks.
1169 <term><function>PQprepare</function><indexterm><primary>PQprepare</></></term>
1172 Submits a request to create a prepared statement with the
1173 given parameters, and waits for completion.
1175 PGresult *PQprepare(PGconn *conn,
1176 const char *stmtName,
1179 const Oid *paramTypes);
1184 <function>PQprepare</> creates a prepared statement for later execution with
1185 <function>PQexecPrepared</>.
1186 This feature allows commands
1187 that will be used repeatedly to be parsed and planned just once, rather
1188 than each time they are executed.
1189 <function>PQprepare</> is supported only in protocol 3.0 and later
1190 connections; it will fail when using protocol 2.0.
1194 The function creates a prepared statement named <parameter>stmtName</>
1195 from the <parameter>query</> string, which must contain a single SQL command.
1196 <parameter>stmtName</> may be <literal>""</> to create an unnamed statement,
1197 in which case any pre-existing unnamed statement is automatically replaced;
1198 otherwise it is an error if the statement name is already defined in the
1200 If any parameters are used, they are referred
1201 to in the query as <literal>$1</>, <literal>$2</>, etc.
1202 <parameter>nParams</> is the number of parameters for which types are
1203 pre-specified in the array <parameter>paramTypes[]</>. (The array pointer
1204 may be <symbol>NULL</symbol> when <parameter>nParams</> is zero.)
1205 <parameter>paramTypes[]</> specifies, by OID, the data types to be assigned to
1206 the parameter symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1207 or any particular element in the array is zero, the server assigns a data type
1208 to the parameter symbol in the same way it would do for an untyped literal
1209 string. Also, the query may use parameter symbols with numbers higher than
1210 <parameter>nParams</>; data types will be inferred for these symbols as
1215 As with <function>PQexec</>, the result is normally a
1216 <structname>PGresult</structname> object whose contents indicate server-side
1217 success or failure. A null result indicates out-of-memory or inability to
1218 send the command at all.
1219 Use <function>PQerrorMessage</function> to get more information
1224 At present, there is no way to determine the actual data type inferred for
1225 any parameters whose types are not specified in <parameter>paramTypes[]</>.
1226 This is a <application>libpq</> omission that will probably be rectified
1227 in a future release.
1233 Prepared statements for use with <function>PQexecPrepared</> can also be
1234 created by executing SQL <command>PREPARE</> statements. (But
1235 <function>PQprepare</> is more flexible since it does not require
1236 parameter types to be pre-specified.) Also, although there is no
1237 <application>libpq</> function for deleting a prepared statement,
1238 the SQL <command>DEALLOCATE</> statement can be used for that purpose.
1244 <term><function>PQexecPrepared</function><indexterm><primary>PQexecPrepared</></></term>
1247 Sends a request to execute a prepared statement with given
1248 parameters, and waits for the result.
1250 PGresult *PQexecPrepared(PGconn *conn,
1251 const char *stmtName,
1253 const char * const *paramValues,
1254 const int *paramLengths,
1255 const int *paramFormats,
1261 <function>PQexecPrepared</> is like <function>PQexecParams</>, but the
1262 command to be executed is specified by naming a previously-prepared
1263 statement, instead of giving a query string.
1264 This feature allows commands
1265 that will be used repeatedly to be parsed and planned just once, rather
1266 than each time they are executed.
1267 The statement must have been prepared previously in the current session.
1268 <function>PQexecPrepared</> is supported only in protocol 3.0 and later
1269 connections; it will fail when using protocol 2.0.
1273 The parameters are identical to <function>PQexecParams</>, except that the
1274 name of a prepared statement is given instead of a query string, and the
1275 <parameter>paramTypes[]</> parameter is not present (it is not needed since
1276 the prepared statement's parameter types were determined when it was created).
1285 <structname>PGresult</structname><indexterm><primary>PGresult</></>
1286 structure encapsulates the result returned by the server.
1287 <application>libpq</application> application programmers should be
1288 careful to maintain the <structname>PGresult</structname> abstraction.
1289 Use the accessor functions below to get at the contents of
1290 <structname>PGresult</structname>. Avoid directly referencing the
1291 fields of the <structname>PGresult</structname> structure because they
1292 are subject to change in the future.
1296 <term><function>PQresultStatus</function><indexterm><primary>PQresultStatus</></></term>
1299 Returns the result status of the command.
1301 ExecStatusType PQresultStatus(const PGresult *res);
1306 <function>PQresultStatus</function> can return one of the following values:
1310 <term><literal>PGRES_EMPTY_QUERY</literal></term>
1312 <para>The string sent to the server was empty.</para>
1317 <term><literal>PGRES_COMMAND_OK</literal></term>
1319 <para>Successful completion of a command returning no data.</para>
1324 <term><literal>PGRES_TUPLES_OK</literal></term>
1326 <para>Successful completion of a command returning data (such as
1327 a <command>SELECT</> or <command>SHOW</>).</para>
1332 <term><literal>PGRES_COPY_OUT</literal></term>
1334 <para>Copy Out (from server) data transfer started.</para>
1339 <term><literal>PGRES_COPY_IN</literal></term>
1341 <para>Copy In (to server) data transfer started.</para>
1346 <term><literal>PGRES_BAD_RESPONSE</literal></term>
1348 <para>The server's response was not understood.</para>
1353 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
1355 <para>A nonfatal error (a notice or warning) occurred.</para>
1360 <term><literal>PGRES_FATAL_ERROR</literal></term>
1362 <para>A fatal error occurred.</para>
1367 If the result status is <literal>PGRES_TUPLES_OK</literal>, then the
1368 functions described below can be used to retrieve the rows returned by
1369 the query. Note that a <command>SELECT</command> command that happens
1370 to retrieve zero rows still shows <literal>PGRES_TUPLES_OK</literal>.
1371 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
1372 return rows (<command>INSERT</command>, <command>UPDATE</command>,
1373 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> may indicate
1374 a bug in the client software.
1378 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will never be
1379 returned directly by <function>PQexec</function> or other query
1380 execution functions; results of this kind are instead passed to the notice
1381 processor (see <xref linkend="libpq-notice-processing">).
1387 <term><function>PQresStatus</function><indexterm><primary>PQresStatus</></></term>
1390 Converts the enumerated type returned by <function>PQresultStatus</> into
1391 a string constant describing the status code. The caller should not
1394 char *PQresStatus(ExecStatusType status);
1401 <term><function>PQresultErrorMessage</function><indexterm><primary>PQresultErrorMessage</></></term>
1404 Returns the error message associated with the command, or an empty string
1405 if there was no error.
1407 char *PQresultErrorMessage(const PGresult *res);
1409 If there was an error, the returned string will include a trailing newline.
1410 The caller should not free the result directly. It will be freed when the
1411 associated <structname>PGresult</> handle is passed to
1412 <function>PQclear</function>.
1416 Immediately following a <function>PQexec</function> or <function>PQgetResult</function>
1417 call, <function>PQerrorMessage</function> (on the connection) will return the same
1418 string as <function>PQresultErrorMessage</function> (on the result). However, a
1419 <structname>PGresult</structname> will retain its error message
1420 until destroyed, whereas the connection's error message will change when
1421 subsequent operations are done. Use <function>PQresultErrorMessage</function> when you want to
1422 know the status associated with a particular <structname>PGresult</structname>; use <function>PQerrorMessage</function>
1423 when you want to know the status from the latest operation on the connection.
1429 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
1432 Returns an individual field of an error report.
1434 char *PQresultErrorField(const PGresult *res, int fieldcode);
1436 <parameter>fieldcode</> is an error field identifier; see the symbols
1437 listed below. <symbol>NULL</symbol> is returned if the
1438 <structname>PGresult</structname> is not an error or warning result,
1439 or does not include the specified field. Field values will normally
1440 not include a trailing newline. The caller should not free the
1441 result directly. It will be freed when the
1442 associated <structname>PGresult</> handle is passed to
1443 <function>PQclear</function>.
1447 The following field codes are available:
1451 <term><symbol>PG_DIAG_SEVERITY</></term>
1454 The severity; the field contents are <literal>ERROR</>,
1455 <literal>FATAL</>, or <literal>PANIC</> (in an error message), or
1456 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
1457 <literal>INFO</>, or <literal>LOG</> (in a notice message), or a
1458 localized translation of one of these. Always present.
1465 <primary>error codes</primary>
1466 <secondary>libpq</secondary>
1468 <term><symbol>PG_DIAG_SQLSTATE</>
1472 The SQLSTATE code for the error. The SQLSTATE code identifies the type
1473 of error that has occurred; it can be used by front-end applications
1474 to perform specific operations (such as error handling) in response to
1475 a particular database error. For a list of the possible SQLSTATE
1476 codes, see <xref linkend="errcodes-appendix">. This field is not
1477 localizable, and is always present.
1483 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
1486 The primary human-readable error message (typically one line). Always
1493 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
1496 Detail: an optional secondary error message carrying more detail about
1497 the problem. May run to multiple lines.
1503 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
1506 Hint: an optional suggestion what to do about the problem. This is
1507 intended to differ from detail in that it offers advice (potentially
1508 inappropriate) rather than hard facts. May run to multiple lines.
1514 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
1517 A string containing a decimal integer indicating an error cursor
1518 position as an index into the original statement string. The first
1519 character has index 1, and positions are measured in characters not
1526 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
1529 This is defined the same as the <symbol>PG_DIAG_STATEMENT_POSITION</>
1530 field, but it is used when the cursor position refers to an internally
1531 generated command rather than the one submitted by the client.
1532 The <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when this field
1539 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
1542 The text of a failed internally-generated command.
1543 This could be, for example, a SQL query issued by a PL/pgSQL function.
1549 <term><symbol>PG_DIAG_CONTEXT</></term>
1552 An indication of the context in which the error occurred.
1553 Presently this includes a call stack traceback of active
1554 procedural language functions and internally-generated queries.
1555 The trace is one entry per line, most recent first.
1561 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
1564 The file name of the source-code location where the error was
1571 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
1574 The line number of the source-code location where the error was
1581 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
1584 The name of the source-code function reporting the error.
1592 The client is responsible for formatting displayed information to meet
1593 its needs; in particular it should break long lines as needed.
1594 Newline characters appearing in the error message fields should be
1595 treated as paragraph breaks, not line breaks.
1599 Errors generated internally by <application>libpq</application> will
1600 have severity and primary message, but typically no other fields.
1601 Errors returned by a pre-3.0-protocol server will include severity and
1602 primary message, and sometimes a detail message, but no other fields.
1606 Note that error fields are only available from
1607 <structname>PGresult</structname> objects, not
1608 <structname>PGconn</structname> objects; there is no
1609 <function>PQerrorField</function> function.
1615 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
1618 Frees the storage associated with a <structname>PGresult</structname>.
1619 Every command result should be freed via <function>PQclear</function> when
1620 it is no longer needed.
1622 void PQclear(PGresult *res);
1627 You can keep a <structname>PGresult</structname> object around for as long as you
1628 need it; it does not go away when you issue a new command,
1629 nor even if you close the connection. To get rid of it,
1630 you must call <function>PQclear</function>. Failure to do this will
1631 result in memory leaks in your application.
1637 <term><function>PQmakeEmptyPGresult</function><indexterm><primary>PQmakeEmptyPGresult</></></term>
1640 Constructs an empty <structname>PGresult</structname> object with the given status.
1642 PGresult* PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
1647 This is <application>libpq</>'s internal function to allocate and initialize an empty
1648 <structname>PGresult</structname> object. It is exported because some applications find it
1649 useful to generate result objects (particularly objects with error
1650 status) themselves. If <parameter>conn</parameter> is not null and <parameter>status</> indicates an error,
1651 the current error message of the specified connection is copied into the <structname>PGresult</structname>.
1652 Note that <function>PQclear</function> should eventually be called on the object, just
1653 as with a <structname>PGresult</structname> returned by <application>libpq</application> itself.
1661 <sect2 id="libpq-exec-select-info">
1662 <title>Retrieving Query Result Information</title>
1665 These functions are used to extract information from a
1666 <structname>PGresult</structname> object that represents a successful
1667 query result (that is, one that has status
1668 <literal>PGRES_TUPLES_OK</literal>). For objects with other status
1669 values they will act as though the result has zero rows and zero columns.
1674 <term><function>PQntuples</function><indexterm><primary>PQntuples</></></term>
1677 Returns the number of rows (tuples)
1678 in the query result.
1680 int PQntuples(const PGresult *res);
1687 <term><function>PQnfields</function><indexterm><primary>PQnfields</></></term>
1690 Returns the number of columns (fields)
1691 in each row of the query result.
1693 int PQnfields(const PGresult *res);
1700 <term><function>PQfname</function><indexterm><primary>PQfname</></></term>
1703 Returns the column name associated with the given column number.
1704 Column numbers start at 0. The caller should not free the result
1705 directly. It will be freed when the associated <structname>PGresult</>
1706 handle is passed to <function>PQclear</function>.
1708 char *PQfname(const PGresult *res,
1714 <symbol>NULL</symbol> is returned if the column number is out of range.
1720 <term><function>PQfnumber</function><indexterm><primary>PQfnumber</></></term>
1723 Returns the column number associated with the given column name.
1725 int PQfnumber(const PGresult *res,
1726 const char *column_name);
1731 -1 is returned if the given name does not match any column.
1735 The given name is treated like an identifier in an SQL command,
1736 that is, it is downcased unless double-quoted. For example,
1737 given a query result generated from the SQL command
1739 select 1 as FOO, 2 as "BAR";
1741 we would have the results:
1743 PQfname(res, 0) <lineannotation>foo</lineannotation>
1744 PQfname(res, 1) <lineannotation>BAR</lineannotation>
1745 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
1746 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
1747 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
1748 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
1755 <term><function>PQftable</function><indexterm><primary>PQftable</></></term>
1758 Returns the OID of the table from which the given column was fetched.
1759 Column numbers start at 0.
1761 Oid PQftable(const PGresult *res,
1767 <literal>InvalidOid</> is returned if the column number is out of range,
1768 or if the specified column is not a simple reference to a table column,
1769 or when using pre-3.0 protocol.
1770 You can query the system table <literal>pg_class</literal> to determine
1771 exactly which table is referenced.
1775 The type <type>Oid</type> and the constant
1776 <literal>InvalidOid</literal> will be defined when you include
1777 the <application>libpq</application> header file. They will
1778 both be some integer type.
1784 <term><function>PQftablecol</function><indexterm><primary>PQftablecol</></></term>
1787 Returns the column number (within its table) of the column making up
1788 the specified query result column.
1789 Query-result column numbers start at 0, but table columns have nonzero
1792 int PQftablecol(const PGresult *res,
1798 Zero is returned if the column number is out of range,
1799 or if the specified column is not a simple reference to a table column,
1800 or when using pre-3.0 protocol.
1806 <term><function>PQfformat</function><indexterm><primary>PQfformat</></></term>
1809 Returns the format code indicating the format of the given column.
1810 Column numbers start at 0.
1812 int PQfformat(const PGresult *res,
1818 Format code zero indicates textual data representation, while format
1819 code one indicates binary representation. (Other codes are reserved
1820 for future definition.)
1826 <term><function>PQftype</function><indexterm><primary>PQftype</></></term>
1829 Returns the data type associated with the
1830 given column number. The integer returned is the
1831 internal OID number of the type. Column numbers start
1834 Oid PQftype(const PGresult *res,
1840 You can query the system table <literal>pg_type</literal> to obtain
1841 the names and properties of the various data types. The <acronym>OID</acronym>s
1842 of the built-in data types are defined in the file <filename>src/include/catalog/pg_type.h</filename>
1849 <term><function>PQfmod</function><indexterm><primary>PQfmod</></></term>
1852 Returns the type modifier of the column
1853 associated with the given column number.
1854 Column numbers start at 0.
1856 int PQfmod(const PGresult *res,
1862 The interpretation of modifier values is type-specific; they typically
1863 indicate precision or size limits. The value -1 is used to indicate
1864 <quote>no information available</>. Most data types do not use modifiers,
1865 in which case the value is always -1.
1871 <term><function>PQfsize</function><indexterm><primary>PQfsize</></></term>
1874 Returns the size in bytes of the column
1875 associated with the given column number.
1876 Column numbers start at 0.
1878 int PQfsize(const PGresult *res,
1884 <function>PQfsize</> returns the space allocated for this column in a database
1885 row, in other words the size of the server's internal representation
1886 of the data type. (Accordingly, it is not really very useful to clients.)
1887 A negative value indicates the data type is variable-length.
1893 <term><function>PQbinaryTuples</function><indexterm><primary>PQbinaryTuples</></></term>
1896 Returns 1 if the <structname>PGresult</> contains binary data
1897 and 0 if it contains text data.
1899 int PQbinaryTuples(const PGresult *res);
1904 This function is deprecated (except for its use in connection with
1905 <command>COPY</>), because it is possible for a single
1906 <structname>PGresult</>
1907 to contain text data in some columns and binary data in others.
1908 <function>PQfformat</> is preferred. <function>PQbinaryTuples</>
1909 returns 1 only if all columns of the result are binary (format 1).
1915 <term><function>PQgetvalue</function><indexterm><primary>PQgetvalue</></></term>
1918 Returns a single field value of one row of a
1919 <structname>PGresult</structname>. Row and column numbers
1920 start at 0. The caller should not free the result
1921 directly. It will be freed when the associated
1922 <structname>PGresult</> handle is passed to
1923 <function>PQclear</function>.
1925 char *PQgetvalue(const PGresult *res,
1932 For data in text format, the value returned by <function>PQgetvalue</function>
1933 is a null-terminated character string representation
1934 of the field value. For data in binary format, the value is in the binary
1935 representation determined by the data type's <function>typsend</> and
1936 <function>typreceive</> functions. (The value is actually followed by
1937 a zero byte in this case too, but that is not ordinarily useful, since
1938 the value is likely to contain embedded nulls.)
1942 An empty string is returned if the field value is null. See
1943 <function>PQgetisnull</> to distinguish null values from empty-string values.
1948 returned by <function>PQgetvalue</function> points to storage that is
1949 part of the <structname>PGresult</structname> structure. One should not modify the data it points to,
1950 and one must explicitly
1951 copy the data into other storage if it is to
1952 be used past the lifetime of the <structname>PGresult</structname> structure itself.
1958 <term><function>PQgetisnull</function><indexterm><primary>PQgetisnull</></></term>
1959 <indexterm><primary>null value</><secondary sortas="libpq">in libpq</></indexterm><listitem>
1961 Tests a field for a null value.
1962 Row and column numbers start at 0.
1964 int PQgetisnull(const PGresult *res,
1971 This function returns 1 if the field is null and 0 if
1972 it contains a non-null value. (Note that <function>PQgetvalue</function>
1973 will return an empty string, not a null pointer, for a null field.)
1979 <term><function>PQgetlength</function><indexterm><primary>PQgetlength</></></term>
1982 Returns the actual length of a field value in bytes.
1983 Row and column numbers start at 0.
1985 int PQgetlength(const PGresult *res,
1992 This is the actual data length for the particular data value, that is, the
1993 size of the object pointed to by <function>PQgetvalue</function>. For text
1994 data format this is the same as <function>strlen()</>. For binary format
1995 this is essential information. Note that one should <emphasis>not</> rely
1996 on <function>PQfsize</function> to obtain the actual data length.
2002 <term><function>PQprint</function><indexterm><primary>PQprint</></></term>
2005 Prints out all the rows and, optionally, the
2006 column names to the specified output stream.
2008 void PQprint(FILE *fout, /* output stream */
2009 const PGresult *res,
2010 const PQprintOpt *po);
2013 pqbool header; /* print output field headings and row count */
2014 pqbool align; /* fill align the fields */
2015 pqbool standard; /* old brain dead format */
2016 pqbool html3; /* output HTML tables */
2017 pqbool expanded; /* expand tables */
2018 pqbool pager; /* use pager for output if needed */
2019 char *fieldSep; /* field separator */
2020 char *tableOpt; /* attributes for HTML table element */
2021 char *caption; /* HTML table caption */
2022 char **fieldName; /* null-terminated array of replacement field names */
2028 This function was formerly used by <application>psql</application>
2029 to print query results, but this is no longer the case. Note that it
2030 assumes all the data is in text format.
2037 <sect2 id="libpq-exec-nonselect">
2038 <title>Retrieving Result Information for Other Commands</title>
2041 These functions are used to extract information from
2042 <structname>PGresult</structname> objects that are not <command>SELECT</>
2048 <term><function>PQcmdStatus</function><indexterm><primary>PQcmdStatus</></></term>
2051 Returns the command status tag from the SQL command that
2052 generated the <structname>PGresult</structname>.
2054 char *PQcmdStatus(PGresult *res);
2058 Commonly this is just the name of the command, but it may include additional
2059 data such as the number of rows processed. The caller should
2060 not free the result directly. It will be freed when the
2061 associated <structname>PGresult</> handle is passed to
2062 <function>PQclear</function>.
2068 <term><function>PQcmdTuples</function><indexterm><primary>PQcmdTuples</></></term>
2071 Returns the number of rows affected by the SQL command.
2073 char *PQcmdTuples(PGresult *res);
2078 This function returns a string containing the number of rows
2079 affected by the <acronym>SQL</> statement that generated the
2080 <structname>PGresult</>. This function can only be used
2081 following the execution of an <command>INSERT</>,
2082 <command>UPDATE</>, <command>DELETE</>, <command>MOVE</>, or
2083 <command>FETCH</> statement, or an <command>EXECUTE</> of a
2084 prepared query that contains a <command>INSERT</>,
2085 <command>UPDATE</>, or <command>DELETE</> statement. If the
2086 command that generated the <structname>PGresult</> was
2087 anything else, <function>PQcmdTuples</> returns the empty
2088 string. The caller should not free the return value
2089 directly. It will be freed when the associated
2090 <structname>PGresult</> handle is passed to
2091 <function>PQclear</function>.
2097 <term><function>PQoidValue</function><indexterm><primary>PQoidValue</></></term>
2100 Returns the OID<indexterm><primary>OID</><secondary>in
2101 libpq</></> of the inserted row, if the <acronym>SQL</>
2102 command was an <command>INSERT</> that inserted exactly one
2103 row into a table that has OIDs, or a <command>EXECUTE</> of
2104 a prepared query containing a suitable <command>INSERT</>
2105 statement. Otherwise, this function returns
2106 <literal>InvalidOid</literal>. This function will also
2107 return <literal>InvalidOid</literal> if the table affected
2108 by the <command>INSERT</> statement does not contain OIDs.
2110 Oid PQoidValue(const PGresult *res);
2117 <term><function>PQoidStatus</function><indexterm><primary>PQoidStatus</></></term>
2120 Returns a string with the OID of the inserted row, if the
2121 <acronym>SQL</acronym> command was an
2122 <command>INSERT</command> that inserted exactly one row, or
2123 a <command>EXECUTE</command> of a prepared statement
2124 consisting of a suitable <command>INSERT</command>. (The string will be
2125 <literal>0</> if the <command>INSERT</command> did not
2126 insert exactly one row, or if the target table does not have
2127 OIDs.) If the command was not an <command>INSERT</command>,
2128 returns an empty string.
2130 char *PQoidStatus(const PGresult *res);
2135 This function is deprecated in favor of <function>PQoidValue</function>.
2136 It is not thread-safe.
2144 <sect2 id="libpq-exec-escape-string">
2145 <title>Escaping Strings for Inclusion in SQL Commands</title>
2147 <indexterm zone="libpq-exec-escape-string"><primary>PQescapeString</></>
2148 <indexterm zone="libpq-exec-escape-string"><primary>escaping strings</></>
2151 <function>PQescapeString</function> escapes a string for use within an SQL
2152 command. This is useful when inserting data values as literal constants
2153 in SQL commands. Certain characters (such as quotes and backslashes) must
2154 be escaped to prevent them from being interpreted specially by the SQL parser.
2155 <function>PQescapeString</> performs this operation.
2160 It is especially important to do proper escaping when handling strings that
2161 were received from an untrustworthy source. Otherwise there is a security
2162 risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
2163 SQL commands are fed to your database.
2168 Note that it is not necessary nor correct to do escaping when a data
2169 value is passed as a separate parameter in <function>PQexecParams</> or
2170 its sibling routines.
2173 size_t PQescapeString (char *to, const char *from, size_t length);
2178 The parameter <parameter>from</> points to the first character of the string
2179 that is to be escaped, and the <parameter>length</> parameter gives the
2180 number of characters in this string. A terminating zero byte is not
2181 required, and should not be counted in <parameter>length</>. (If
2182 a terminating zero byte is found before <parameter>length</> bytes are
2183 processed, <function>PQescapeString</> stops at the zero; the behavior
2184 is thus rather like <function>strncpy</>.)
2185 <parameter>to</> shall point to a
2186 buffer that is able to hold at least one more character than twice
2187 the value of <parameter>length</>, otherwise the behavior is
2188 undefined. A call to <function>PQescapeString</> writes an escaped
2189 version of the <parameter>from</> string to the <parameter>to</>
2190 buffer, replacing special characters so that they cannot cause any
2191 harm, and adding a terminating zero byte. The single quotes that
2192 must surround <productname>PostgreSQL</> string literals are not
2193 included in the result string; they should be provided in the SQL
2194 command that the result is inserted into.
2197 <function>PQescapeString</> returns the number of characters written
2198 to <parameter>to</>, not including the terminating zero byte.
2201 Behavior is undefined if the <parameter>to</> and <parameter>from</>
2207 <sect2 id="libpq-exec-escape-bytea">
2208 <title>Escaping Binary Strings for Inclusion in SQL Commands</title>
2210 <indexterm zone="libpq-exec-escape-bytea">
2212 <secondary sortas="libpq">in libpq</>
2217 <term><function>PQescapeBytea</function><indexterm><primary>PQescapeBytea</></></term>
2220 Escapes binary data for use within an SQL command with the type
2221 <type>bytea</type>. As with <function>PQescapeString</function>,
2222 this is only used when inserting data directly into an SQL command string.
2224 unsigned char *PQescapeBytea(const unsigned char *from,
2231 Certain byte values <emphasis>must</emphasis> be escaped (but all
2232 byte values <emphasis>can</emphasis> be escaped) when used as part
2233 of a <type>bytea</type> literal in an <acronym>SQL</acronym>
2234 statement. In general, to escape a byte, it is converted into the
2235 three digit octal number equal to the octet value, and preceded by
2236 two backslashes. The single quote (<literal>'</>) and backslash
2237 (<literal>\</>) characters have special alternative escape
2238 sequences. See <xref linkend="datatype-binary"> for more
2239 information. <function>PQescapeBytea</function> performs this
2240 operation, escaping only the minimally required bytes.
2244 The <parameter>from</parameter> parameter points to the first
2245 byte of the string that is to be escaped, and the
2246 <parameter>from_length</parameter> parameter gives the number of
2247 bytes in this binary string. (A terminating zero byte is
2248 neither necessary nor counted.) The <parameter>to_length</parameter>
2249 parameter points to a variable that will hold the resultant
2250 escaped string length. The result string length includes the terminating
2251 zero byte of the result.
2255 <function>PQescapeBytea</> returns an escaped version of the
2256 <parameter>from</parameter> parameter binary string in memory
2257 allocated with <function>malloc()</>. This memory must be freed
2258 using <function>PQfreemem</> when the result is no longer needed.
2259 The return string has all special characters replaced so that they
2260 can be properly processed by the
2261 <productname>PostgreSQL</productname> string literal parser, and
2262 the <type>bytea</type> input function. A terminating zero byte is
2263 also added. The single quotes that must surround
2264 <productname>PostgreSQL</productname> string literals are not part
2265 of the result string.
2271 <term><function>PQunescapeBytea</function><indexterm><primary>PQunescapeBytea</></></term>
2274 Converts an escaped string representation of binary data into binary
2275 data — the reverse of <function>PQescapeBytea</function>.
2276 This is needed when retrieving <type>bytea</type> data in text format,
2277 but not when retrieving it in binary format.
2280 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
2285 The <parameter>from</parameter> parameter points to an escaped string
2286 such as might be returned by <function>PQgetvalue</function> when applied to a
2287 <type>bytea</type> column. <function>PQunescapeBytea</function> converts
2288 this string representation into its binary representation.
2289 It returns a pointer to a buffer allocated with
2290 <function>malloc()</function>, or null on error, and puts the size of
2291 the buffer in <parameter>to_length</parameter>. The result must be
2292 freed using <function>PQfreemem</> when it is no longer needed.
2298 <term><function>PQfreemem</function><indexterm><primary>PQfreemem</></></term>
2301 Frees memory allocated by <application>libpq</>.
2303 void PQfreemem(void *ptr);
2308 Frees memory allocated by <application>libpq</>, particularly
2309 <function>PQescapeBytea</function>,
2310 <function>PQunescapeBytea</function>,
2311 and <function>PQnotifies</function>.
2312 It is needed by Microsoft Windows, which cannot free memory across
2313 DLLs, unless multithreaded DLLs (<option>/MD</option> in VC6) are used.
2314 On other platforms, this function is the same as the standard library function <function>free()</>.
2323 <sect1 id="libpq-async">
2324 <title>Asynchronous Command Processing</title>
2326 <indexterm zone="libpq-async"><primary>nonblocking connection</></>
2329 The <function>PQexec</function> function is adequate for submitting commands in
2331 applications. It has a couple of deficiencies, however, that can be of importance to some users:
2336 <function>PQexec</function> waits for the command to be completed. The application may have other
2337 work to do (such as maintaining a user interface), in which case it won't
2338 want to block waiting for the response.
2343 Since the execution of the client application is suspended while it
2344 waits for the result, it is hard for the application to decide that it
2345 would like to try to cancel the ongoing command. (It can be done from
2346 a signal handler, but not otherwise.)
2351 <function>PQexec</function> can return only one <structname>PGresult</structname> structure. If the submitted command
2352 string contains multiple <acronym>SQL</acronym> commands, all but the last <structname>PGresult</structname> are
2353 discarded by <function>PQexec</function>.
2360 Applications that do not like these limitations can instead use the
2361 underlying functions that <function>PQexec</function> is built from:
2362 <function>PQsendQuery</function> and <function>PQgetResult</function>.
2364 <function>PQsendQueryParams</function>,
2365 <function>PQsendPrepare</function>, and
2366 <function>PQsendQueryPrepared</function>,
2367 which can be used with <function>PQgetResult</function> to duplicate the
2369 <function>PQexecParams</function>,
2370 <function>PQprepare</function>, and
2371 <function>PQexecPrepared</function>
2376 <term><function>PQsendQuery</function><indexterm><primary>PQsendQuery</></></term>
2379 Submits a command to the server without
2380 waiting for the result(s). 1 is returned if the command was
2381 successfully dispatched and 0 if not (in which case, use
2382 <function>PQerrorMessage</> to get more information about the failure).
2384 int PQsendQuery(PGconn *conn, const char *command);
2387 After successfully calling <function>PQsendQuery</function>, call
2388 <function>PQgetResult</function> one or more
2389 times to obtain the results. <function>PQsendQuery</function> may not be called
2390 again (on the same connection) until <function>PQgetResult</function> has returned a null pointer,
2391 indicating that the command is done.
2397 <term><function>PQsendQueryParams</function><indexterm><primary>PQsendQueryParams</></></term>
2400 Submits a command and separate parameters to the server without
2401 waiting for the result(s).
2403 int PQsendQueryParams(PGconn *conn,
2404 const char *command,
2406 const Oid *paramTypes,
2407 const char * const *paramValues,
2408 const int *paramLengths,
2409 const int *paramFormats,
2413 This is equivalent to <function>PQsendQuery</function> except that
2414 query parameters can be specified separately from the query string.
2415 The function's parameters are handled identically to
2416 <function>PQexecParams</function>. Like
2417 <function>PQexecParams</function>, it will not work on 2.0-protocol
2418 connections, and it allows only one command in the query string.
2424 <term><function>PQsendPrepare</><indexterm><primary>PQsendPrepare</></></term>
2427 Sends a request to create a prepared statement with the given
2428 parameters, without waiting for completion.
2430 int PQsendPrepare(PGconn *conn,
2431 const char *stmtName,
2434 const Oid *paramTypes);
2437 This is an asynchronous version of <function>PQprepare</>: it
2438 returns 1 if it was able to dispatch the request, and 0 if not.
2439 After a successful call, call <function>PQgetResult</function>
2440 to determine whether the server successfully created the prepared
2442 The function's parameters are handled identically to
2443 <function>PQprepare</function>. Like
2444 <function>PQprepare</function>, it will not work on 2.0-protocol
2451 <term><function>PQsendQueryPrepared</function><indexterm><primary>PQsendQueryPrepared</></></term>
2454 Sends a request to execute a prepared statement with given
2455 parameters, without waiting for the result(s).
2457 int PQsendQueryPrepared(PGconn *conn,
2458 const char *stmtName,
2460 const char * const *paramValues,
2461 const int *paramLengths,
2462 const int *paramFormats,
2466 This is similar to <function>PQsendQueryParams</function>, but the
2467 command to be executed is specified by naming a previously-prepared
2468 statement, instead of giving a query string.
2469 The function's parameters are handled identically to
2470 <function>PQexecPrepared</function>. Like
2471 <function>PQexecPrepared</function>, it will not work on 2.0-protocol
2478 <term><function>PQgetResult</function><indexterm><primary>PQgetResult</></></term>
2481 Waits for the next result from a prior
2482 <function>PQsendQuery</function>,
2483 <function>PQsendQueryParams</function>,
2484 <function>PQsendPrepare</function>, or
2485 <function>PQsendQueryPrepared</function> call,
2486 and returns it. A null pointer is returned when the command is complete
2487 and there will be no more results.
2489 PGresult *PQgetResult(PGconn *conn);
2494 <function>PQgetResult</function> must be called repeatedly until it returns a null pointer,
2495 indicating that the command is done. (If called when no command is
2496 active, <function>PQgetResult</function> will just return a null pointer at once.)
2497 Each non-null result from <function>PQgetResult</function> should be processed using
2498 the same <structname>PGresult</> accessor functions previously described.
2499 Don't forget to free each result object with <function>PQclear</function> when done with it.
2500 Note that <function>PQgetResult</function> will block only if a command is active and the
2501 necessary response data has not yet been read by <function>PQconsumeInput</function>.
2509 Using <function>PQsendQuery</function> and <function>PQgetResult</function>
2510 solves one of <function>PQexec</function>'s problems:
2511 If a command string contains multiple <acronym>SQL</acronym> commands, the results of those
2512 commands can be obtained individually. (This allows a simple form of
2513 overlapped processing, by the way: the client can be handling the
2514 results of one command while the server is still working on later
2515 queries in the same command string.) However, calling <function>PQgetResult</function> will
2516 still cause the client to block until the server completes the
2517 next <acronym>SQL</acronym> command. This can be avoided by proper use of two
2522 <term><function>PQconsumeInput</function><indexterm><primary>PQconsumeInput</></></term>
2525 If input is available from the server, consume it.
2527 int PQconsumeInput(PGconn *conn);
2532 <function>PQconsumeInput</function> normally returns 1 indicating <quote>no error</quote>,
2533 but returns 0 if there was some kind of trouble (in which case
2534 <function>PQerrorMessage</function> can be consulted). Note that the result
2536 whether any input data was actually collected. After calling
2537 <function>PQconsumeInput</function>, the application may check
2538 <function>PQisBusy</function> and/or <function>PQnotifies</function> to see if
2539 their state has changed.
2542 <function>PQconsumeInput</function> may be called even if the application is not
2543 prepared to deal with a result or notification just yet. The
2544 function will read available data and save it in a buffer, thereby
2545 causing a <function>select()</function> read-ready indication to go away. The
2546 application can thus use <function>PQconsumeInput</function> to clear the
2547 <function>select()</function> condition immediately, and then examine the results at leisure.
2553 <term><function>PQisBusy</function><indexterm><primary>PQisBusy</></></term>
2556 Returns 1 if a command is busy, that is, <function>PQgetResult</function> would block
2557 waiting for input. A 0 return indicates that <function>PQgetResult</function> can
2558 be called with assurance of not blocking.
2560 int PQisBusy(PGconn *conn);
2565 <function>PQisBusy</function> will not itself attempt to read data from the server;
2566 therefore <function>PQconsumeInput</function> must be invoked first, or the busy
2567 state will never end.
2575 A typical application using these functions will have a main loop that uses
2576 <function>select()</function> or <function>poll()</> to wait for all the
2577 conditions that it must
2578 respond to. One of the conditions will be input available from the server,
2579 which in terms of <function>select()</function> means readable data on the file
2580 descriptor identified by <function>PQsocket</function>.
2581 When the main loop detects input ready, it should call
2582 <function>PQconsumeInput</function> to read the input. It can then call
2583 <function>PQisBusy</function>, followed by <function>PQgetResult</function>
2584 if <function>PQisBusy</function> returns false (0). It can also call
2585 <function>PQnotifies</function> to detect <command>NOTIFY</> messages (see <xref linkend="libpq-notify">).
2590 <function>PQsendQuery</function>/<function>PQgetResult</function> can
2591 also attempt to cancel a command that is still being processed by the
2592 server; see <xref linkend="libpq-cancel">. But regardless of the return value
2593 of <function>PQcancel</function>, the application must continue with the
2594 normal result-reading sequence using <function>PQgetResult</function>.
2595 A successful cancellation will simply cause the command to terminate
2596 sooner than it would have otherwise.
2600 By using the functions described above, it is possible to avoid blocking
2601 while waiting for input from the database server. However, it is still
2602 possible that the application will block waiting to send output to the
2603 server. This is relatively uncommon but can happen if very long SQL commands
2604 or data values are sent. (It is much more probable if the application
2605 sends data via <command>COPY IN</command>, however.) To prevent this possibility and achieve
2606 completely nonblocking database operation, the following additional
2607 functions may be used.
2611 <term><function>PQsetnonblocking</function><indexterm><primary>PQsetnonblocking</></></term>
2614 Sets the nonblocking status of the connection.
2616 int PQsetnonblocking(PGconn *conn, int arg);
2621 Sets the state of the connection to nonblocking if
2622 <parameter>arg</parameter> is 1, or
2623 blocking if <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
2626 In the nonblocking state, calls to
2627 <function>PQsendQuery</function>,
2628 <function>PQputline</function>, <function>PQputnbytes</function>,
2629 and <function>PQendcopy</function>
2630 will not block but instead return an error if they need to be called
2634 Note that <function>PQexec</function> does not honor nonblocking mode;
2635 if it is called, it will act in blocking fashion anyway.
2641 <term><function>PQisnonblocking</function><indexterm><primary>PQisnonblocking</></></term>
2644 Returns the blocking status of the database connection.
2646 int PQisnonblocking(const PGconn *conn);
2651 Returns 1 if the connection is set to nonblocking mode and
2658 <term><function>PQflush</function><indexterm><primary>PQflush</></></term>
2661 Attempts to flush any queued output data to the server.
2662 Returns 0 if successful (or if the send queue is empty), -1 if it failed for
2663 some reason, or 1 if it was unable to send all the data in the send queue yet
2664 (this case can only occur if the connection is nonblocking).
2666 int PQflush(PGconn *conn);
2675 After sending any command or data on a nonblocking connection, call
2676 <function>PQflush</function>. If it returns 1, wait for the socket to be
2677 write-ready and call it again; repeat until it returns 0. Once
2678 <function>PQflush</function> returns 0, wait for the socket to be read-ready
2679 and then read the response as described above.
2684 <sect1 id="libpq-cancel">
2685 <title>Cancelling Queries in Progress</title>
2687 <indexterm zone="libpq-cancel"><primary>canceling</><secondary>SQL command</></>
2690 A client application can request cancellation of
2691 a command that is still being processed by the
2692 server, using the functions described in this section.
2696 <term><function>PQgetCancel</function><indexterm><primary>PQgetCancel</></></term>
2699 Creates a data structure containing the information needed to cancel
2700 a command issued through a particular database connection.
2702 PGcancel *PQgetCancel(PGconn *conn);
2707 <function>PQgetCancel</function> creates a
2708 <structname>PGcancel</><indexterm><primary>PGcancel</></> object given
2709 a <structname>PGconn</> connection object. It will return NULL if the
2710 given <parameter>conn</> is NULL or an invalid connection. The
2711 <structname>PGcancel</> object is an opaque structure that is not meant
2712 to be accessed directly by the application; it can only be passed to
2713 <function>PQcancel</function> or <function>PQfreeCancel</function>.
2719 <term><function>PQfreeCancel</function><indexterm><primary>PQfreeCancel</></></term>
2722 Frees a data structure created by <function>PQgetCancel</function>.
2724 void PQfreeCancel(PGcancel *cancel);
2729 <function>PQfreeCancel</function> frees a data object previously created
2730 by <function>PQgetCancel</function>.
2736 <term><function>PQcancel</function><indexterm><primary>PQcancel</></></term>
2739 Requests that the server abandon
2740 processing of the current command.
2742 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
2747 The return value is 1 if the cancel request was successfully
2748 dispatched and 0 if not. If not, <parameter>errbuf</> is filled with an error
2749 message explaining why not. <parameter>errbuf</> must be a char array of size
2750 <parameter>errbufsize</> (the recommended size is 256 bytes).
2754 Successful dispatch is no guarantee that the request will have any effect,
2755 however. If the cancellation is effective, the current command will terminate
2756 early and return an error result. If the cancellation fails (say, because the
2757 server was already done processing the command), then there will be no visible
2762 <function>PQcancel</function> can safely be invoked from a signal handler,
2763 if the <parameter>errbuf</> is a local variable in the signal handler. The
2764 <structname>PGcancel</> object is read-only as far as
2765 <function>PQcancel</function> is concerned, so it can also be invoked from a
2766 thread that is separate from the one manipulating the <structname>PGconn</>
2775 <term><function>PQrequestCancel</function><indexterm><primary>PQrequestCancel</></></term>
2778 Requests that the server abandon
2779 processing of the current command.
2781 int PQrequestCancel(PGconn *conn);
2786 <function>PQrequestCancel</function> is a deprecated variant of
2787 <function>PQcancel</function>. It operates directly on the
2788 <structname>PGconn</> object, and in case of failure stores the
2789 error message in the <structname>PGconn</> object (whence it can be
2790 retrieved by <function>PQerrorMessage</function>). Although the
2791 functionality is the same, this approach creates hazards for multiple-thread
2792 programs and signal handlers, since it is possible that overwriting the
2793 <structname>PGconn</>'s error message will mess up the operation currently
2794 in progress on the connection.
2803 <sect1 id="libpq-fastpath">
2804 <title>The Fast-Path Interface</title>
2806 <indexterm zone="libpq-fastpath"><primary>fast path</></>
2809 <productname>PostgreSQL</productname> provides a fast-path interface to send
2810 simple function calls to the server.
2815 This interface is somewhat obsolete, as one may achieve similar performance
2816 and greater functionality by setting up a prepared statement to define the
2817 function call. Then, executing the statement with binary transmission of
2818 parameters and results substitutes for a fast-path function call.
2823 The function <function>PQfn</function><indexterm><primary>PQfn</></>
2824 requests execution of a server function via the fast-path interface:
2826 PGresult *PQfn(PGconn *conn,
2831 const PQArgBlock *args,
2846 The <parameter>fnid</> argument is the OID of the function to be
2847 executed. <parameter>args</> and <parameter>nargs</> define the
2848 parameters to be passed to the function; they must match the declared
2849 function argument list. When the <parameter>isint</> field of a
2852 the <parameter>u.integer</> value is sent to the server as an integer
2853 of the indicated length (this must be 1, 2, or 4 bytes); proper
2854 byte-swapping occurs. When <parameter>isint</> is false, the
2855 indicated number of bytes at <parameter>*u.ptr</> are sent with no
2856 processing; the data must be in the format expected by the server for
2857 binary transmission of the function's argument data type.
2858 <parameter>result_buf</parameter> is the buffer in which
2859 to place the return value. The caller must have allocated
2860 sufficient space to store the return value. (There is no check!)
2861 The actual result length will be returned in the integer pointed
2862 to by <parameter>result_len</parameter>.
2863 If a 1, 2, or 4-byte integer result is expected, set
2864 <parameter>result_is_int</parameter> to 1, otherwise set it to 0.
2865 Setting <parameter>result_is_int</parameter> to 1
2866 causes <application>libpq</> to byte-swap the value if necessary, so that
2868 delivered as a proper <type>int</type> value for the client machine. When
2869 <parameter>result_is_int</> is 0, the binary-format byte string sent by
2870 the server is returned unmodified.
2874 <function>PQfn</function> always returns a valid <structname>PGresult</structname> pointer. The result status
2875 should be checked before the result is used. The
2876 caller is responsible for freeing the <structname>PGresult</structname> with
2877 <function>PQclear</function> when it is no longer needed.
2881 Note that it is not possible to handle null arguments, null results, nor
2882 set-valued results when using this interface.
2887 <sect1 id="libpq-notify">
2888 <title>Asynchronous Notification</title>
2890 <indexterm zone="libpq-notify">
2891 <primary>NOTIFY</primary>
2892 <secondary>in libpq</secondary>
2896 <productname>PostgreSQL</productname> offers asynchronous notification via the
2897 <command>LISTEN</command> and <command>NOTIFY</command> commands. A client session registers its interest in a particular
2898 notification condition with the <command>LISTEN</command> command (and can stop listening
2899 with the <command>UNLISTEN</command> command). All sessions listening on a
2900 particular condition will be notified asynchronously when a <command>NOTIFY</command> command with that
2901 condition name is executed by any session. No additional information is
2902 passed from the notifier to the listener. Thus, typically, any actual data
2903 that needs to be communicated is transferred through a database table.
2904 Commonly, the condition name is the same as the associated table, but it is
2905 not necessary for there to be any associated table.
2909 <application>libpq</application> applications submit
2910 <command>LISTEN</command> and <command>UNLISTEN</command> commands as
2911 ordinary SQL commands. The arrival of <command>NOTIFY</command>
2912 messages can subsequently be detected by calling
2913 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
2917 The function <function>PQnotifies</function>
2918 returns the next notification from a list of unhandled
2919 notification messages received from the server. It returns a null pointer if
2920 there are no pending notifications. Once a notification is
2921 returned from <function>PQnotifies</>, it is considered handled and will be
2922 removed from the list of notifications.
2924 PGnotify *PQnotifies(PGconn *conn);
2926 typedef struct pgNotify {
2927 char *relname; /* notification condition name */
2928 int be_pid; /* process ID of server process */
2929 char *extra; /* notification parameter */
2932 After processing a <structname>PGnotify</structname> object returned by
2933 <function>PQnotifies</function>, be sure to free it with
2934 <function>PQfreemem</function>. It is sufficient to free the
2935 <structname>PGnotify</structname> pointer; the
2936 <structfield>relname</structfield> and <structfield>extra</structfield> fields
2937 do not represent separate allocations.
2938 (At present, the <structfield>extra</structfield> field is unused and will
2939 always point to an empty string.)
2944 In <productname>PostgreSQL</productname> 6.4 and later,
2945 the <structfield>be_pid</structfield> is that of the notifying server process,
2946 whereas in earlier versions it was always the <acronym>PID</acronym> of your own server process.
2951 <xref linkend="libpq-example-2"> gives a sample program that illustrates the use
2952 of asynchronous notification.
2956 <function>PQnotifies</function> does not actually read data from the server; it just
2957 returns messages previously absorbed by another <application>libpq</application>
2958 function. In prior releases of <application>libpq</application>, the only way
2959 to ensure timely receipt of <command>NOTIFY</> messages was to constantly submit commands,
2960 even empty ones, and then check <function>PQnotifies</function> after each
2961 <function>PQexec</function>. While this still works, it is
2962 deprecated as a waste of processing power.
2966 A better way to check for <command>NOTIFY</>
2967 messages when you have no useful commands to execute is to call
2968 <function>PQconsumeInput</function>, then check
2969 <function>PQnotifies</function>.
2970 You can use <function>select()</function> to wait for data to
2971 arrive from the server, thereby using no <acronym>CPU</acronym> power unless there is something
2972 to do. (See <function>PQsocket</function> to obtain the file descriptor
2973 number to use with <function>select()</function>.)
2974 Note that this will work OK whether you submit commands with
2975 <function>PQsendQuery</function>/<function>PQgetResult</function> or simply
2976 use <function>PQexec</function>. You should, however, remember to
2977 check <function>PQnotifies</function> after each
2978 <function>PQgetResult</function> or <function>PQexec</function>, to see
2979 if any notifications came in during the processing of the command.
2984 <sect1 id="libpq-copy">
2985 <title>Functions Associated with the <command>COPY</command> Command</title>
2987 <indexterm zone="libpq-copy">
2988 <primary>COPY</primary>
2989 <secondary>with libpq</secondary>
2993 The <command>COPY</command> command in <productname>PostgreSQL</productname>
2994 has options to read from or write to the network connection used by
2995 <application>libpq</application>. The functions described in this section
2996 allow applications to take advantage of this capability by supplying or
2997 consuming copied data.
3001 The overall process is that the application first issues the SQL
3002 <command>COPY</command> command via <function>PQexec</function> or one
3003 of the equivalent functions. The response to this (if there is no error
3004 in the command) will be a <structname>PGresult</> object bearing a status
3005 code of <literal>PGRES_COPY_OUT</literal> or <literal>PGRES_COPY_IN</literal>
3006 (depending on the specified copy direction). The application should then
3007 use the functions of this section to receive or transmit data rows.
3008 When the data transfer is complete, another <structname>PGresult</> object
3009 is returned to indicate success or failure of the transfer. Its status
3010 will be <literal>PGRES_COMMAND_OK</literal> for success or
3011 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
3012 At this point further SQL commands may be issued via
3013 <function>PQexec</function>. (It is not possible to execute other SQL
3014 commands using the same connection while the <command>COPY</command>
3015 operation is in progress.)
3019 If a <command>COPY</command> command is issued via
3020 <function>PQexec</function> in a string that could contain additional
3021 commands, the application must continue fetching results via
3022 <function>PQgetResult</> after completing the <command>COPY</command>
3023 sequence. Only when <function>PQgetResult</> returns <symbol>NULL</symbol> is it certain
3024 that the <function>PQexec</function> command string is done and it is
3025 safe to issue more commands.
3029 The functions of this section should be executed only after obtaining a
3030 result status of <literal>PGRES_COPY_OUT</literal> or
3031 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
3032 <function>PQgetResult</function>.
3036 A <structname>PGresult</> object bearing one of these status values
3037 carries some additional data about the <command>COPY</command> operation that
3038 is starting. This additional data is available using functions that are
3039 also used in connection with query results:
3043 <term><function>PQnfields</function><indexterm><primary>PQnfields</><secondary>with COPY</></></term>
3046 Returns the number of columns (fields) to be copied.
3052 <term><function>PQbinaryTuples</function><indexterm><primary>PQbinaryTuples</><secondary>with COPY</></></term>
3055 0 indicates the overall copy format is textual (rows
3056 separated by newlines, columns separated by separator
3058 1 indicates the overall copy format is binary.
3059 See <xref linkend="sql-copy" endterm="sql-copy-title">
3060 for more information.
3066 <term><function>PQfformat</function><indexterm><primary>PQfformat</><secondary>with COPY</></></term>
3069 Returns the format code (0 for text, 1 for binary) associated
3070 with each column of the copy operation. The per-column format
3071 codes will always be zero when the overall copy format is textual,
3072 but the binary format can support both text and binary columns.
3073 (However, as of the current implementation of <command>COPY</>,
3074 only binary columns appear in a binary copy; so the per-column
3075 formats always match the overall format at present.)
3084 These additional data values are only available when using protocol 3.0.
3085 When using protocol 2.0, all these functions will return 0.
3089 <sect2 id="libpq-copy-send">
3090 <title>Functions for Sending <command>COPY</command> Data</title>
3093 These functions are used to send data during <literal>COPY FROM STDIN</>.
3094 They will fail if called when the connection is not in <literal>COPY_IN</>
3100 <term><function>PQputCopyData</function><indexterm><primary>PQputCopyData</></></term>
3103 Sends data to the server during <literal>COPY_IN</> state.
3105 int PQputCopyData(PGconn *conn,
3112 Transmits the <command>COPY</command> data in the specified <parameter>buffer</>, of length
3113 <parameter>nbytes</>, to the server. The result is 1 if the data was sent,
3114 zero if it was not sent because the attempt would block (this case is only
3115 possible if the connection is in nonblocking mode), or -1 if an error occurred.
3116 (Use <function>PQerrorMessage</function> to retrieve details if the return
3117 value is -1. If the value is zero, wait for write-ready and try again.)
3121 The application may divide the <command>COPY</command> data stream into buffer loads of any
3122 convenient size. Buffer-load boundaries have no semantic significance when
3123 sending. The contents of the data stream must match the data format expected
3124 by the <command>COPY</> command; see
3125 <xref linkend="sql-copy" endterm="sql-copy-title"> for details.
3131 <term><function>PQputCopyEnd</function><indexterm><primary>PQputCopyEnd</></></term>
3134 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
3136 int PQputCopyEnd(PGconn *conn,
3137 const char *errormsg);
3142 Ends the <literal>COPY_IN</> operation successfully if <parameter>errormsg</>
3143 is <symbol>NULL</symbol>. If <parameter>errormsg</> is not <symbol>NULL</symbol> then the <command>COPY</>
3144 is forced to fail, with the string pointed to by <parameter>errormsg</>
3145 used as the error message. (One should not assume that this exact error
3146 message will come back from the server, however, as the server might have
3147 already failed the <command>COPY</> for its own reasons. Also note that the
3148 option to force failure does not work when using pre-3.0-protocol connections.)
3152 The result is 1 if the termination data was sent,
3153 zero if it was not sent because the attempt would block (this case is only
3154 possible if the connection is in nonblocking mode), or -1 if an error occurred.
3155 (Use <function>PQerrorMessage</function> to retrieve details if the return
3156 value is -1. If the value is zero, wait for write-ready and try again.)
3160 After successfully calling <function>PQputCopyEnd</>, call
3161 <function>PQgetResult</> to obtain the final result status of the
3162 <command>COPY</> command. One may wait for
3163 this result to be available in the usual way. Then return to normal
3172 <sect2 id="libpq-copy-receive">
3173 <title>Functions for Receiving <command>COPY</command> Data</title>
3176 These functions are used to receive data during <literal>COPY TO STDOUT</>.
3177 They will fail if called when the connection is not in <literal>COPY_OUT</>
3183 <term><function>PQgetCopyData</function><indexterm><primary>PQgetCopyData</></></term>
3186 Receives data from the server during <literal>COPY_OUT</> state.
3188 int PQgetCopyData(PGconn *conn,
3195 Attempts to obtain another row of data from the server during a <command>COPY</command>.
3196 Data is always returned one data row at a time; if only a partial row
3197 is available, it is not returned. Successful return of a data row
3198 involves allocating a chunk of memory to hold the data. The
3199 <parameter>buffer</> parameter must be non-<symbol>NULL</symbol>. <parameter>*buffer</>
3200 is set to point to the allocated memory, or to <symbol>NULL</symbol> in cases where no
3201 buffer is returned. A non-<symbol>NULL</symbol> result buffer must be freed using
3202 <function>PQfreemem</> when no longer needed.
3206 When a row is successfully returned, the return value is the number of
3207 data bytes in the row (this will always be greater than zero). The
3208 returned string is always null-terminated, though this is probably only
3209 useful for textual <command>COPY</command>. A result of zero indicates that the <command>COPY</command> is
3210 still in progress, but no row is yet available (this is only possible
3211 when <parameter>async</> is true). A
3212 result of -1 indicates that the <command>COPY</command> is done.
3213 A result of -2 indicates that an error occurred (consult
3214 <function>PQerrorMessage</> for the reason).
3218 When <parameter>async</> is true (not zero), <function>PQgetCopyData</>
3219 will not block waiting for input; it will return zero if the <command>COPY</command> is still
3220 in progress but no complete row is available. (In this case wait for
3221 read-ready before trying again; it does not matter whether you call
3222 <function>PQconsumeInput</>.) When <parameter>async</> is
3223 false (zero), <function>PQgetCopyData</> will block until data is available
3224 or the operation completes.
3228 After <function>PQgetCopyData</> returns -1, call
3229 <function>PQgetResult</> to obtain the final result status of the
3230 <command>COPY</> command. One may wait for
3231 this result to be available in the usual way. Then return to normal
3240 <sect2 id="libpq-copy-deprecated">
3241 <title>Obsolete Functions for <command>COPY</command></title>
3244 These functions represent older methods of handling <command>COPY</>.
3245 Although they still work, they are deprecated due to poor error handling,
3246 inconvenient methods of detecting end-of-data, and lack of support for binary
3247 or nonblocking transfers.
3252 <term><function>PQgetline</function><indexterm><primary>PQgetline</></></term>
3255 Reads a newline-terminated line of characters
3256 (transmitted by the server) into a buffer
3257 string of size <parameter>length</>.
3259 int PQgetline(PGconn *conn,
3266 This function copies up to <parameter>length</>-1 characters
3267 into the buffer and converts
3268 the terminating newline into a zero byte.
3269 <function>PQgetline</function> returns <symbol>EOF</symbol> at the end of input, 0 if the
3270 entire line has been read, and 1 if the buffer is full but the
3271 terminating newline has not yet been read.
3274 Note that the application must check to see if a
3275 new line consists of the two characters <literal>\.</literal>,
3276 which indicates that the server has finished sending
3277 the results of the <command>COPY</command> command.
3278 If the application might
3279 receive lines that are more than <parameter>length</>-1 characters long,
3280 care is needed to be sure it recognizes the <literal>\.</literal> line correctly
3281 (and does not, for example, mistake the end of a long data line
3282 for a terminator line).
3288 <term><function>PQgetlineAsync</function><indexterm><primary>PQgetlineAsync</></></term>
3291 Reads a row of <command>COPY</command> data
3292 (transmitted by the server) into a buffer
3295 int PQgetlineAsync(PGconn *conn,
3302 This function is similar to <function>PQgetline</function>, but it can be used
3304 that must read <command>COPY</command> data asynchronously, that is, without blocking.
3305 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
3307 application should call <function>PQconsumeInput</function> and
3308 <function>PQgetlineAsync</function> until the
3309 end-of-data signal is detected.
3312 Unlike <function>PQgetline</function>, this function takes
3313 responsibility for detecting end-of-data.
3316 On each call, <function>PQgetlineAsync</function> will return data if a
3317 complete data row is available in <application>libpq</>'s input buffer.
3318 Otherwise, no data is returned until the rest of the row arrives.
3319 The function returns -1 if the end-of-copy-data marker has been recognized,
3320 or 0 if no data is available, or a positive number giving the number of
3321 bytes of data returned. If -1 is returned, the caller must next call
3322 <function>PQendcopy</function>, and then return to normal processing.
3325 The data returned will not extend beyond a data-row boundary. If possible
3326 a whole row will be returned at one time. But if the buffer offered by
3327 the caller is too small to hold a row sent by the server, then a partial
3328 data row will be returned. With textual data this can be detected by testing
3329 whether the last returned byte is <literal>\n</literal> or not. (In a binary
3330 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
3331 equivalent determination.)
3332 The returned string is not null-terminated. (If you want to add a
3333 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
3334 than the room actually available.)
3340 <term><function>PQputline</function><indexterm><primary>PQputline</></></term>
3343 Sends a null-terminated string to the server.
3344 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
3346 int PQputline(PGconn *conn,
3347 const char *string);
3352 The <command>COPY</command> data stream sent by a series of calls to
3353 <function>PQputline</function> has the same format as that returned by
3354 <function>PQgetlineAsync</function>, except that applications are not
3355 obliged to send exactly one data row per <function>PQputline</function>
3356 call; it is okay to send a partial line or multiple lines per call.
3361 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
3362 for the application to explicitly send the two characters
3363 <literal>\.</literal> as a final line to indicate to the server that it had
3364 finished sending <command>COPY</> data. While this still works, it is deprecated and the
3365 special meaning of <literal>\.</literal> can be expected to be removed in a
3366 future release. It is sufficient to call <function>PQendcopy</function> after
3367 having sent the actual data.
3374 <term><function>PQputnbytes</function><indexterm><primary>PQputnbytes</></></term>
3377 Sends a non-null-terminated string to the server.
3378 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
3380 int PQputnbytes(PGconn *conn,
3387 This is exactly like <function>PQputline</function>, except that the data
3388 buffer need not be null-terminated since the number of bytes to send is
3389 specified directly. Use this procedure when sending binary data.
3395 <term><function>PQendcopy</function><indexterm><primary>PQendcopy</></></term>
3398 Synchronizes with the server.
3400 int PQendcopy(PGconn *conn);
3402 This function waits until
3403 the server has finished the copying. It should
3404 either be issued when the last string has been
3405 sent to the server using <function>PQputline</function> or when the
3406 last string has been received from the server
3407 using <function>PGgetline</function>. It must be issued or the server
3408 will get <quote>out of sync</quote> with the client. Upon
3409 return from this function, the server is ready to
3410 receive the next SQL command.
3411 The return value is 0 on successful completion,
3412 nonzero otherwise. (Use <function>PQerrorMessage</function> to retrieve
3413 details if the return value is nonzero.)
3417 When using <function>PQgetResult</function>, the application should respond to
3418 a <literal>PGRES_COPY_OUT</literal> result by executing <function>PQgetline</function>
3419 repeatedly, followed by <function>PQendcopy</function> after the terminator line is seen.
3420 It should then return to the <function>PQgetResult</function> loop until
3421 <function>PQgetResult</function> returns a null pointer. Similarly a <literal>PGRES_COPY_IN</literal>
3422 result is processed by a series of <function>PQputline</function> calls followed by
3423 <function>PQendcopy</function>, then return to the <function>PQgetResult</function> loop.
3424 This arrangement will ensure that
3425 a <command>COPY</command> command embedded in a series of <acronym>SQL</acronym> commands
3426 will be executed correctly.
3430 Older applications are likely to submit a <command>COPY</command>
3431 via <function>PQexec</function> and assume that the transaction is done after
3432 <function>PQendcopy</function>.
3433 This will work correctly only if the <command>COPY</command> is the only
3434 <acronym>SQL</acronym> command in the command string.
3444 <sect1 id="libpq-control">
3445 <title>Control Functions</title>
3448 These functions control miscellaneous details of
3449 <application>libpq</>'s behavior.
3454 <term><function>PQsetErrorVerbosity</function><indexterm><primary>PQsetErrorVerbosity</></></term>
3457 Determines the verbosity of messages returned by
3458 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
3466 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
3468 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
3469 the connection's previous setting. In <firstterm>TERSE</> mode,
3470 returned messages include severity, primary text, and position only;
3471 this will normally fit on a single line. The default mode produces
3472 messages that include the above plus any detail, hint, or context
3473 fields (these may span multiple lines). The <firstterm>VERBOSE</>
3474 mode includes all available fields. Changing the verbosity does not
3475 affect the messages available from already-existing
3476 <structname>PGresult</> objects, only subsequently-created ones.
3482 <term><function>PQtrace</function><indexterm><primary>PQtrace</></></term>
3485 Enables tracing of the client/server communication to a debugging file stream.
3487 void PQtrace(PGconn *conn, FILE *stream);
3494 <term><function>PQuntrace</function><indexterm><primary>PQuntrace</></></term>
3497 Disables tracing started by <function>PQtrace</function>.
3499 void PQuntrace(PGconn *conn);
3508 <sect1 id="libpq-notice-processing">
3509 <title>Notice Processing</title>
3511 <indexterm zone="libpq-notice-processing">
3512 <primary>notice processing</primary>
3513 <secondary>in libpq</secondary>
3517 Notice and warning messages generated by the server are not returned by the
3518 query execution functions, since they do not imply failure of the query.
3519 Instead they are passed to a notice handling function, and execution continues
3520 normally after the handler returns. The default notice handling function
3521 prints the message on <filename>stderr</filename>, but the application can
3522 override this behavior by supplying its own handling function.
3526 For historical reasons, there are two levels of notice handling, called the
3527 notice receiver and notice processor. The default behavior is for the notice
3528 receiver to format the notice and pass a string to the notice processor
3529 for printing. However, an application that chooses to provide its own notice
3530 receiver will typically ignore the notice processor layer and just do all the
3531 work in the notice receiver.
3535 The function <function>PQsetNoticeReceiver</function>
3536 <indexterm><primary>notice receiver</></><indexterm><primary>PQsetNoticeReceiver</></>
3537 sets or examines the current notice receiver for a connection object.
3538 Similarly, <function>PQsetNoticeProcessor</function>
3539 <indexterm><primary>notice processor</></><indexterm><primary>PQsetNoticeProcessor</></>
3540 sets or examines the current notice processor.
3543 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
3546 PQsetNoticeReceiver(PGconn *conn,
3547 PQnoticeReceiver proc,
3550 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
3553 PQsetNoticeProcessor(PGconn *conn,
3554 PQnoticeProcessor proc,
3558 Each of these functions returns the previous notice receiver or processor
3559 function pointer, and sets the new value.
3560 If you supply a null function pointer, no action is taken,
3561 but the current pointer is returned.
3565 When a notice or warning message is received from the server, or generated
3566 internally by <application>libpq</application>, the notice receiver function
3567 is called. It is passed the message in the form of a
3568 <symbol>PGRES_NONFATAL_ERROR</symbol> <structname>PGresult</structname>.
3569 (This allows the receiver to extract individual fields using
3570 <function>PQresultErrorField</>, or the complete preformatted message using
3571 <function>PQresultErrorMessage</>.)
3572 The same void pointer passed to
3573 <function>PQsetNoticeReceiver</function> is also passed.
3574 (This pointer can be used to access application-specific state if needed.)
3578 The default notice receiver simply extracts the message (using
3579 <function>PQresultErrorMessage</>) and passes it to the notice processor.
3583 The notice processor is responsible for handling a notice or warning message
3584 given in text form. It is passed the string text of the message
3585 (including a trailing newline), plus
3586 a void pointer that is the same one passed to
3587 <function>PQsetNoticeProcessor</function>.
3588 (This pointer can be used to access application-specific state if needed.)
3592 The default notice processor is simply
3595 defaultNoticeProcessor(void *arg, const char *message)
3597 fprintf(stderr, "%s", message);
3603 Once you have set a notice receiver or processor, you should expect that that
3604 function could be called as long as either the <structname>PGconn</> object or
3605 <structname>PGresult</> objects made from it exist. At creation of a
3606 <structname>PGresult</>, the <structname>PGconn</>'s current notice handling
3607 pointers are copied into the <structname>PGresult</> for possible use by
3608 functions like <function>PQgetvalue</function>.
3613 <sect1 id="libpq-envars">
3614 <title>Environment Variables</title>
3616 <indexterm zone="libpq-envars">
3617 <primary>environment variable</primary>
3621 The following environment variables can be used to select default
3622 connection parameter values, which will be used by
3623 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
3624 <function>PQsetdb</> if no value is directly specified by the calling
3625 code. These are useful to avoid hard-coding database connection
3626 information into simple client applications, for example.
3632 <primary><envar>PGHOST</envar></primary>
3634 <envar>PGHOST</envar> sets the database server name.
3635 If this begins with a slash, it specifies Unix-domain communication
3636 rather than TCP/IP communication; the value is then the name of the
3637 directory in which the socket file is stored (in a default installation
3638 setup this would be <filename>/tmp</filename>).
3644 <primary><envar>PGHOSTADDR</envar></primary>
3646 <envar>PGHOSTADDR</envar> specifies the numeric IP address of the database
3647 server. This can be set instead of or in addition to <envar>PGHOST</envar>
3648 to avoid DNS lookup overhead. See the documentation of
3649 these parameters, under <function>PQconnectdb</function> above, for details
3650 on their interaction.
3653 When neither <envar>PGHOST</envar> nor <envar>PGHOSTADDR</envar> is set,
3654 the default behavior is to connect using a local Unix-domain socket; or on
3655 machines without Unix-domain sockets, <application>libpq</application> will
3656 attempt to connect to <literal>localhost</>.
3662 <primary><envar>PGPORT</envar></primary>
3664 <envar>PGPORT</envar> sets the TCP port number or Unix-domain
3665 socket file extension for communicating with the
3666 <productname>PostgreSQL</productname> server.
3672 <primary><envar>PGDATABASE</envar></primary>
3674 <envar>PGDATABASE</envar> sets the
3675 <productname>PostgreSQL</productname> database name.
3681 <primary><envar>PGUSER</envar></primary>
3683 <envar>PGUSER</envar>
3684 sets the user name used to connect to the database.
3690 <primary><envar>PGPASSWORD</envar></primary>
3692 <envar>PGPASSWORD</envar>
3693 sets the password used if the server demands password
3694 authentication. Use of this environment variable is not
3695 recommended for security reasons (some operating systems
3696 allow non-root users to see process environment variables via
3697 <application>ps</>); instead consider using the
3698 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
3704 <primary><envar>PGSERVICE</envar></primary>
3706 <envar>PGSERVICE</envar>
3707 sets the service name to be looked up in <filename>pg_service.conf</filename>.
3708 This offers a shorthand way of setting all the parameters.
3714 <primary><envar>PGREALM</envar></primary>
3716 <envar>PGREALM</envar> sets the Kerberos realm to use with
3717 <productname>PostgreSQL</productname>, if it is different from the local realm.
3718 If <envar>PGREALM</envar> is set, <application>libpq</application>
3719 applications will attempt authentication with servers for this realm and use
3720 separate ticket files to avoid conflicts with local
3721 ticket files. This environment variable is only
3722 used if Kerberos authentication is selected by the server.
3728 <primary><envar>PGOPTIONS</envar></primary>
3730 <envar>PGOPTIONS</envar> sets additional run-time options for
3731 the <productname>PostgreSQL</productname> server.
3737 <primary><envar>PGSSLMODE</envar></primary>
3739 <envar>PGSSLMODE</envar> determines whether and with what priority an
3740 <acronym>SSL</> connection will be negotiated with the server. There are
3741 four modes: <literal>disable</> will attempt only an unencrypted
3742 <acronym>SSL</> connection; <literal>allow</> will negotiate,
3743 trying first a non-<acronym>SSL</> connection, then if that fails,
3744 trying an <acronym>SSL</> connection; <literal>prefer</>
3745 (the default) will negotiate, trying first an <acronym>SSL</>
3746 connection, then if that fails, trying a regular non-<acronym>SSL</>
3747 connection; <literal>require</> will try only an <acronym>SSL</>
3748 connection. If <productname>PostgreSQL</> is compiled without SSL support,
3749 using option <literal>require</> will cause an error, while options
3750 <literal>allow</> and <literal>prefer</> will be accepted but
3751 <application>libpq</> will not in fact attempt an <acronym>SSL</>
3758 <primary><envar>PGREQUIRESSL</envar></primary>
3760 <envar>PGREQUIRESSL</envar> sets whether or not the connection must be
3761 made over <acronym>SSL</acronym>. If set to
3762 <quote>1</quote>, <application>libpq</>
3763 will refuse to connect if the server does not accept
3764 an <acronym>SSL</acronym> connection (equivalent to <literal>sslmode</>
3765 <literal>prefer</>).
3766 This option is deprecated in favor of the <literal>sslmode</>
3767 setting, and is only available if
3768 <productname>PostgreSQL</> is compiled with SSL support.
3774 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
3776 <envar>PGCONNECT_TIMEOUT</envar> sets the maximum number of seconds
3777 that <application>libpq</application> will wait when attempting to
3778 connect to the <productname>PostgreSQL</productname> server. If unset
3779 or set to zero, <application>libpq</application> will wait indefinitely.
3780 It is not recommended to set the timeout to less than 2 seconds.
3787 The following environment variables can be used to specify default
3788 behavior for each <productname>PostgreSQL</productname> session.
3790 <xref linkend="sql-alteruser" endterm="sql-alteruser-title"> and
3791 <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">
3792 commands for ways to set default behavior on a per-user or per-database
3799 <primary><envar>PGDATESTYLE</envar></primary>
3801 <envar>PGDATESTYLE</envar>
3802 sets the default style of date/time representation.
3803 (Equivalent to <literal>SET datestyle TO ...</literal>.)
3809 <primary><envar>PGTZ</envar></primary>
3812 sets the default time zone.
3813 (Equivalent to <literal>SET timezone TO ...</literal>.)
3819 <primary><envar>PGCLIENTENCODING</envar></primary>
3821 <envar>PGCLIENTENCODING</envar>
3822 sets the default client character set encoding.
3823 (Equivalent to <literal>SET client_encoding TO ...</literal>.)
3829 <primary><envar>PGGEQO</envar></primary>
3831 <envar>PGGEQO</envar>
3832 sets the default mode for the genetic query optimizer.
3833 (Equivalent to <literal>SET geqo TO ...</literal>.)
3838 Refer to the <acronym>SQL</acronym> command
3839 <xref linkend="sql-set" endterm="sql-set-title">
3840 for information on correct values for these environment variables.
3844 The following environment variables determine internal behavior of
3845 <application>libpq</application>; they override compiled-in defaults.
3851 <primary><envar>PGSYSCONFDIR</envar></primary>
3853 <envar>PGSYSCONFDIR</envar>
3854 sets the directory containing the <filename>pg_service.conf</> file.
3860 <primary><envar>PGLOCALEDIR</envar></primary>
3862 <envar>PGLOCALEDIR</envar>
3863 sets the directory containing the <literal>locale</> files for message
3864 internationalization.
3873 <sect1 id="libpq-pgpass">
3874 <title>The Password File</title>
3876 <indexterm zone="libpq-pgpass">
3877 <primary>password file</primary>
3879 <indexterm zone="libpq-pgpass">
3880 <primary>.pgpass</primary>
3884 The file <filename>.pgpass</filename> in a user's home directory is a file
3885 that can contain passwords to be used if the connection requires a
3886 password (and no password has been specified otherwise).
3887 On Microsoft Windows the file is named
3888 <filename>%APPDATA%\postgresql\pgpass.conf</> (where <filename>%APPDATA%</>
3889 refers to the Application Data subdirectory in the user's profile).
3893 This file should contain lines of the following format:
3895 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
3897 Each of the first four fields may be a literal value, or <literal>*</literal>,
3899 matches anything. The password field from the first line that matches the
3900 current connection parameters will be used. (Therefore, put more-specific
3901 entries first when you are using wildcards.)
3902 If an entry needs to contain <literal>:</literal> or
3903 <literal>\</literal>, escape this character with <literal>\</literal>.
3907 The permissions on <filename>.pgpass</filename> must disallow any
3908 access to world or group; achieve this by the command
3909 <command>chmod 0600 ~/.pgpass</command>.
3910 If the permissions are less strict than this, the file will be ignored.
3911 (The file permissions are not currently checked on Microsoft Windows,
3917 <sect1 id="libpq-ssl">
3918 <title>SSL Support</title>
3920 <indexterm zone="libpq-ssl">
3921 <primary>SSL</primary>
3925 <productname>PostgreSQL</> has native support for using
3926 <acronym>SSL</> connections to encrypt client/server communications
3927 for increased security. See <xref linkend="ssl-tcp"> for details
3928 about the server-side <acronym>SSL</> functionality.
3932 If the server demands a client certificate,
3933 <application>libpq</application>
3934 will send the certificate stored in file
3935 <filename>~/.postgresql/postgresql.crt</> within the user's home directory.
3936 A matching private key file <filename>~/.postgresql/postgresql.key</>
3937 must also be present, and must not be world-readable.
3938 (On Microsoft Windows these files are named
3939 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
3940 <filename>%APPDATA%\postgresql\postgresql.key</filename>.)
3944 If the file <filename>~/.postgresql/root.crt</> is present in the user's
3946 <application>libpq</application> will use the certificate list stored
3947 therein to verify the server's certificate.
3948 (On Microsoft Windows the file is named
3949 <filename>%APPDATA%\postgresql\root.crt</filename>.)
3950 The SSL connection will
3951 fail if the server does not present a certificate; therefore, to
3952 use this feature the server must also have a <filename>root.crt</> file.
3957 <sect1 id="libpq-threading">
3958 <title>Behavior in Threaded Programs</title>
3960 <indexterm zone="libpq-threading">
3961 <primary>threads</primary>
3962 <secondary>with libpq</secondary>
3966 <application>libpq</application> is reentrant and thread-safe if the
3967 <filename>configure</filename> command-line option
3968 <literal>--enable-thread-safety</> was used when the
3969 <productname>PostgreSQL</productname> distribution was built. In
3970 addition, you might need to use additional compiler command-line
3971 options when you compile your application code. Refer to your
3972 system's documentation for information about how to build
3973 thread-enabled applications, or look in
3974 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
3975 and <literal>PTHREAD_LIBS</>.
3979 One restriction is that no two threads attempt to manipulate the same
3980 <structname>PGconn</> object at the same time. In particular, you cannot
3981 issue concurrent commands from different threads through the same
3982 connection object. (If you need to run concurrent commands, use
3983 multiple connections.)
3987 <structname>PGresult</> objects are read-only after creation, and so can be
3988 passed around freely between threads.
3992 The deprecated functions
3993 <function>PQrequestCancel</function>,
3994 <function>PQoidStatus</function> and
3995 <function>fe_setauthsvc</function>
3996 are not thread-safe and should not be used in multithread programs.
3997 <function>PQrequestCancel</function> can be replaced by
3998 <function>PQcancel</function>.
3999 <function>PQoidStatus</function> can be replaced by
4000 <function>PQoidValue</function>.
4001 There is no good reason to call <function>fe_setauthsvc</function> at all.
4005 <application>libpq</application> applications that use the
4006 <literal>crypt</literal> authentication method rely on the
4007 <literal>crypt()</literal> operating system function, which is often
4008 not thread-safe.<indexterm><primary>crypt</><secondary>thread
4009 safety</></> It is better to use the <literal>md5</literal> method,
4010 which is thread-safe on all platforms.
4014 If you experience problems with threaded applications, run
4015 the program in <filename>src/tools/thread</> to see if your
4016 platform has thread-unsafe functions. This program is run
4017 by <filename>configure</filename>, but for binary distributions
4018 your library might not match the library used to build the binaries.
4023 <sect1 id="libpq-build">
4024 <title>Building <application>libpq</application> Programs</title>
4026 <indexterm zone="libpq-build">
4027 <primary>compiling</primary>
4028 <secondary>libpq applications</secondary>
4032 To build (i.e., compile and link) a program using
4033 <application>libpq</application> you need to
4034 do all of the following things:
4039 Include the <filename>libpq-fe.h</filename> header file:
4041 #include <libpq-fe.h>
4043 If you failed to do that then you will normally get error
4044 messages from your compiler similar to
4046 foo.c: In function `main':
4047 foo.c:34: `PGconn' undeclared (first use in this function)
4048 foo.c:35: `PGresult' undeclared (first use in this function)
4049 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
4050 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
4051 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
4058 Point your compiler to the directory where the <productname>PostgreSQL</> header
4059 files were installed, by supplying the
4060 <literal>-I<replaceable>directory</replaceable></literal> option
4061 to your compiler. (In some cases the compiler will look into
4062 the directory in question by default, so you can omit this
4063 option.) For instance, your compile command line could look
4066 cc -c -I/usr/local/pgsql/include testprog.c
4068 If you are using makefiles then add the option to the
4069 <varname>CPPFLAGS</varname> variable:
4071 CPPFLAGS += -I/usr/local/pgsql/include
4076 If there is any chance that your program might be compiled by
4077 other users then you should not hardcode the directory location
4078 like that. Instead, you can run the utility
4079 <command>pg_config</command><indexterm><primary>pg_config</><secondary
4080 sortas="libpq">with libpq</></> to find out where the header
4081 files are on the local system:
4083 <prompt>$</prompt> pg_config --includedir
4084 <computeroutput>/usr/local/include</computeroutput>
4089 Failure to specify the correct option to the compiler will
4090 result in an error message such as
4092 testlibpq.c:8:22: libpq-fe.h: No such file or directory
4099 When linking the final program, specify the option
4100 <literal>-lpq</literal> so that the <application>libpq</application> library gets pulled
4101 in, as well as the option
4102 <literal>-L<replaceable>directory</replaceable></literal> to
4103 point the compiler to the directory where the <application>libpq</application> library resides. (Again, the
4104 compiler will search some directories by default.) For maximum
4105 portability, put the <option>-L</option> option before the
4106 <option>-lpq</option> option. For example:
4108 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
4113 You can find out the library directory using
4114 <command>pg_config</command> as well:
4116 <prompt>$</prompt> pg_config --libdir
4117 <computeroutput>/usr/local/pgsql/lib</computeroutput>
4122 Error messages that point to problems in this area could look
4125 testlibpq.o: In function `main':
4126 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
4127 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
4128 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
4130 This means you forgot <option>-lpq</option>.
4132 /usr/bin/ld: cannot find -lpq
4134 This means you forgot the <option>-L</option> option or did not specify
4135 the right directory.
4142 <indexterm><primary>libpq-int.h</></>
4143 If your codes references the header file
4144 <filename>libpq-int.h</filename> and you refuse to fix your code to
4145 not use it, starting in <productname>PostgreSQL</> 7.2, this file will be found in
4146 <filename><replaceable>includedir</replaceable>/postgresql/internal/libpq-int.h</filename>,
4147 so you need to add the appropriate <option>-I</option> option to
4148 your compiler command line.
4154 <sect1 id="libpq-example">
4155 <title>Example Programs</title>
4158 These examples and others can be found in the
4159 directory <filename>src/test/examples</filename> in the source code
4163 <example id="libpq-example-1">
4164 <title><application>libpq</application> Example Program 1</title>
4170 * Test the C version of LIBPQ, the POSTGRES frontend library.
4172 #include <stdio.h>
4173 #include <stdlib.h>
4174 #include "libpq-fe.h"
4177 exit_nicely(PGconn *conn)
4184 main(int argc, char **argv)
4186 const char *conninfo;
4194 * If the user supplies a parameter on the command line, use it as
4195 * the conninfo string; otherwise default to setting dbname=template1
4196 * and using environment variables or defaults for all other connection
4202 conninfo = "dbname = template1";
4204 /* Make a connection to the database */
4205 conn = PQconnectdb(conninfo);
4207 /* Check to see that the backend connection was successfully made */
4208 if (PQstatus(conn) != CONNECTION_OK)
4210 fprintf(stderr, "Connection to database failed: %s",
4211 PQerrorMessage(conn));
4216 * Our test case here involves using a cursor, for which we must be
4217 * inside a transaction block. We could do the whole thing with a
4218 * single PQexec() of "select * from pg_database", but that's too
4219 * trivial to make a good example.
4222 /* Start a transaction block */
4223 res = PQexec(conn, "BEGIN");
4224 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4226 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
4232 * Should PQclear PGresult whenever it is no longer needed to avoid
4238 * Fetch rows from pg_database, the system catalog of databases
4240 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
4241 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4243 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
4249 res = PQexec(conn, "FETCH ALL in myportal");
4250 if (PQresultStatus(res) != PGRES_TUPLES_OK)
4252 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
4257 /* first, print out the attribute names */
4258 nFields = PQnfields(res);
4259 for (i = 0; i < nFields; i++)
4260 printf("%-15s", PQfname(res, i));
4263 /* next, print out the rows */
4264 for (i = 0; i < PQntuples(res); i++)
4266 for (j = 0; j < nFields; j++)
4267 printf("%-15s", PQgetvalue(res, i, j));
4273 /* close the portal ... we don't bother to check for errors ... */
4274 res = PQexec(conn, "CLOSE myportal");
4277 /* end the transaction */
4278 res = PQexec(conn, "END");
4281 /* close the connection to the database and cleanup */
4289 <example id="libpq-example-2">
4290 <title><application>libpq</application> Example Program 2</title>
4295 * Test of the asynchronous notification interface
4297 * Start this program, then from psql in another window do
4299 * Repeat four times to get this program to exit.
4301 * Or, if you want to get fancy, try this:
4302 * populate a database with the following commands
4303 * (provided in src/test/examples/testlibpq2.sql):
4305 * CREATE TABLE TBL1 (i int4);
4307 * CREATE TABLE TBL2 (i int4);
4309 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
4310 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
4312 * and do this four times:
4314 * INSERT INTO TBL1 VALUES (10);
4316 #include <stdio.h>
4317 #include <stdlib.h>
4318 #include <string.h>
4319 #include <errno.h>
4320 #include <sys/time.h>
4321 #include "libpq-fe.h"
4324 exit_nicely(PGconn *conn)
4331 main(int argc, char **argv)
4333 const char *conninfo;
4340 * If the user supplies a parameter on the command line, use it as
4341 * the conninfo string; otherwise default to setting dbname=template1
4342 * and using environment variables or defaults for all other connection
4348 conninfo = "dbname = template1";
4350 /* Make a connection to the database */
4351 conn = PQconnectdb(conninfo);
4353 /* Check to see that the backend connection was successfully made */
4354 if (PQstatus(conn) != CONNECTION_OK)
4356 fprintf(stderr, "Connection to database failed: %s",
4357 PQerrorMessage(conn));
4362 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
4364 res = PQexec(conn, "LISTEN TBL2");
4365 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4367 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
4373 * should PQclear PGresult whenever it is no longer needed to avoid
4378 /* Quit after four notifies are received. */
4380 while (nnotifies < 4)
4383 * Sleep until something happens on the connection. We use select(2)
4384 * to wait for input, but you could also use poll() or similar
4390 sock = PQsocket(conn);
4393 break; /* shouldn't happen */
4395 FD_ZERO(&input_mask);
4396 FD_SET(sock, &input_mask);
4398 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
4400 fprintf(stderr, "select() failed: %s\n", strerror(errno));
4404 /* Now check for input */
4405 PQconsumeInput(conn);
4406 while ((notify = PQnotifies(conn)) != NULL)
4409 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
4410 notify->relname, notify->be_pid);
4416 fprintf(stderr, "Done.\n");
4418 /* close the connection to the database and cleanup */
4426 <example id="libpq-example-3">
4427 <title><application>libpq</application> Example Program 3</>
4432 * Test out-of-line parameters and binary I/O.
4434 * Before running this, populate a database with the following commands
4435 * (provided in src/test/examples/testlibpq3.sql):
4437 * CREATE TABLE test1 (i int4, t text, b bytea);
4439 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
4440 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
4442 * The expected output is:
4446 * t = (11 bytes) 'joe's place'
4447 * b = (5 bytes) \000\001\002\003\004
4450 #include <stdio.h>
4451 #include <stdlib.h>
4452 #include <string.h>
4453 #include <sys/types.h>
4454 #include "libpq-fe.h"
4456 /* for ntohl/htonl */
4457 #include <netinet/in.h>
4458 #include <arpa/inet.h>
4462 exit_nicely(PGconn *conn)
4469 main(int argc, char **argv)
4471 const char *conninfo;
4474 const char *paramValues[1];
4482 * If the user supplies a parameter on the command line, use it as
4483 * the conninfo string; otherwise default to setting dbname=template1
4484 * and using environment variables or defaults for all other connection
4490 conninfo = "dbname = template1";
4492 /* Make a connection to the database */
4493 conn = PQconnectdb(conninfo);
4495 /* Check to see that the backend connection was successfully made */
4496 if (PQstatus(conn) != CONNECTION_OK)
4498 fprintf(stderr, "Connection to database failed: %s",
4499 PQerrorMessage(conn));
4504 * The point of this program is to illustrate use of PQexecParams()
4505 * with out-of-line parameters, as well as binary transmission of
4506 * results. By using out-of-line parameters we can avoid a lot of
4507 * tedious mucking about with quoting and escaping. Notice how we
4508 * don't have to do anything special with the quote mark in the
4512 /* Here is our out-of-line parameter value */
4513 paramValues[0] = "joe's place";
4515 res = PQexecParams(conn,
4516 "SELECT * FROM test1 WHERE t = $1",
4518 NULL, /* let the backend deduce param type */
4520 NULL, /* don't need param lengths since text */
4521 NULL, /* default to all text params */
4522 1); /* ask for binary results */
4524 if (PQresultStatus(res) != PGRES_TUPLES_OK)
4526 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
4531 /* Use PQfnumber to avoid assumptions about field order in result */
4532 i_fnum = PQfnumber(res, "i");
4533 t_fnum = PQfnumber(res, "t");
4534 b_fnum = PQfnumber(res, "b");
4536 for (i = 0; i < PQntuples(res); i++)
4544 /* Get the field values (we ignore possibility they are null!) */
4545 iptr = PQgetvalue(res, i, i_fnum);
4546 tptr = PQgetvalue(res, i, t_fnum);
4547 bptr = PQgetvalue(res, i, b_fnum);
4550 * The binary representation of INT4 is in network byte order,
4551 * which we'd better coerce to the local byte order.
4553 ival = ntohl(*((uint32_t *) iptr));
4556 * The binary representation of TEXT is, well, text, and since
4557 * libpq was nice enough to append a zero byte to it, it'll work
4558 * just fine as a C string.
4560 * The binary representation of BYTEA is a bunch of bytes, which
4561 * could include embedded nulls so we have to pay attention to
4564 blen = PQgetlength(res, i, b_fnum);
4566 printf("tuple %d: got\n", i);
4567 printf(" i = (%d bytes) %d\n",
4568 PQgetlength(res, i, i_fnum), ival);
4569 printf(" t = (%d bytes) '%s'\n",
4570 PQgetlength(res, i, t_fnum), tptr);
4571 printf(" b = (%d bytes) ", blen);
4572 for (j = 0; j < blen; j++)
4573 printf("\\%03o", bptr[j]);
4579 /* close the connection to the database and cleanup */
4590 <!-- Keep this comment at the end of the file
4595 sgml-minimize-attributes:nil
4596 sgml-always-quote-attributes:t
4599 sgml-parent-document:nil
4600 sgml-default-dtd-file:"./reference.ced"
4601 sgml-exposed-tags:nil
4602 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4603 sgml-local-ecat-files:nil
projects / postgresql / blob