2 $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.176 2005/01/06 21:20:43 tgl 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 <function>PQexecPrepared</> is supported only in protocol 3.0 and later
1268 connections; it will fail when using protocol 2.0.
1272 The parameters are identical to <function>PQexecParams</>, except that the
1273 name of a prepared statement is given instead of a query string, and the
1274 <parameter>paramTypes[]</> parameter is not present (it is not needed since
1275 the prepared statement's parameter types were determined when it was created).
1284 <structname>PGresult</structname><indexterm><primary>PGresult</></>
1285 structure encapsulates the result returned by the server.
1286 <application>libpq</application> application programmers should be
1287 careful to maintain the <structname>PGresult</structname> abstraction.
1288 Use the accessor functions below to get at the contents of
1289 <structname>PGresult</structname>. Avoid directly referencing the
1290 fields of the <structname>PGresult</structname> structure because they
1291 are subject to change in the future.
1295 <term><function>PQresultStatus</function><indexterm><primary>PQresultStatus</></></term>
1298 Returns the result status of the command.
1300 ExecStatusType PQresultStatus(const PGresult *res);
1305 <function>PQresultStatus</function> can return one of the following values:
1309 <term><literal>PGRES_EMPTY_QUERY</literal></term>
1311 <para>The string sent to the server was empty.</para>
1316 <term><literal>PGRES_COMMAND_OK</literal></term>
1318 <para>Successful completion of a command returning no data.</para>
1323 <term><literal>PGRES_TUPLES_OK</literal></term>
1325 <para>Successful completion of a command returning data (such as
1326 a <command>SELECT</> or <command>SHOW</>).</para>
1331 <term><literal>PGRES_COPY_OUT</literal></term>
1333 <para>Copy Out (from server) data transfer started.</para>
1338 <term><literal>PGRES_COPY_IN</literal></term>
1340 <para>Copy In (to server) data transfer started.</para>
1345 <term><literal>PGRES_BAD_RESPONSE</literal></term>
1347 <para>The server's response was not understood.</para>
1352 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
1354 <para>A nonfatal error (a notice or warning) occurred.</para>
1359 <term><literal>PGRES_FATAL_ERROR</literal></term>
1361 <para>A fatal error occurred.</para>
1366 If the result status is <literal>PGRES_TUPLES_OK</literal>, then the
1367 functions described below can be used to retrieve the rows returned by
1368 the query. Note that a <command>SELECT</command> command that happens
1369 to retrieve zero rows still shows <literal>PGRES_TUPLES_OK</literal>.
1370 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
1371 return rows (<command>INSERT</command>, <command>UPDATE</command>,
1372 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> may indicate
1373 a bug in the client software.
1377 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will never be
1378 returned directly by <function>PQexec</function> or other query
1379 execution functions; results of this kind are instead passed to the notice
1380 processor (see <xref linkend="libpq-notice-processing">).
1386 <term><function>PQresStatus</function><indexterm><primary>PQresStatus</></></term>
1389 Converts the enumerated type returned by <function>PQresultStatus</> into
1390 a string constant describing the status code. The caller should not
1393 char *PQresStatus(ExecStatusType status);
1400 <term><function>PQresultErrorMessage</function><indexterm><primary>PQresultErrorMessage</></></term>
1403 Returns the error message associated with the command, or an empty string
1404 if there was no error.
1406 char *PQresultErrorMessage(const PGresult *res);
1408 If there was an error, the returned string will include a trailing newline.
1409 The caller should not free the result directly. It will be freed when the
1410 associated <structname>PGresult</> handle is passed to
1411 <function>PQclear</function>.
1415 Immediately following a <function>PQexec</function> or <function>PQgetResult</function>
1416 call, <function>PQerrorMessage</function> (on the connection) will return the same
1417 string as <function>PQresultErrorMessage</function> (on the result). However, a
1418 <structname>PGresult</structname> will retain its error message
1419 until destroyed, whereas the connection's error message will change when
1420 subsequent operations are done. Use <function>PQresultErrorMessage</function> when you want to
1421 know the status associated with a particular <structname>PGresult</structname>; use <function>PQerrorMessage</function>
1422 when you want to know the status from the latest operation on the connection.
1428 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
1431 Returns an individual field of an error report.
1433 char *PQresultErrorField(const PGresult *res, int fieldcode);
1435 <parameter>fieldcode</> is an error field identifier; see the symbols
1436 listed below. <symbol>NULL</symbol> is returned if the
1437 <structname>PGresult</structname> is not an error or warning result,
1438 or does not include the specified field. Field values will normally
1439 not include a trailing newline. The caller should not free the
1440 result directly. It will be freed when the
1441 associated <structname>PGresult</> handle is passed to
1442 <function>PQclear</function>.
1446 The following field codes are available:
1450 <term><symbol>PG_DIAG_SEVERITY</></term>
1453 The severity; the field contents are <literal>ERROR</>,
1454 <literal>FATAL</>, or <literal>PANIC</> (in an error message), or
1455 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
1456 <literal>INFO</>, or <literal>LOG</> (in a notice message), or a
1457 localized translation of one of these. Always present.
1464 <primary>error codes</primary>
1465 <secondary>libpq</secondary>
1467 <term><symbol>PG_DIAG_SQLSTATE</>
1471 The SQLSTATE code for the error. The SQLSTATE code identifies the type
1472 of error that has occurred; it can be used by front-end applications
1473 to perform specific operations (such as error handling) in response to
1474 a particular database error. For a list of the possible SQLSTATE
1475 codes, see <xref linkend="errcodes-appendix">. This field is not
1476 localizable, and is always present.
1482 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
1485 The primary human-readable error message (typically one line). Always
1492 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
1495 Detail: an optional secondary error message carrying more detail about
1496 the problem. May run to multiple lines.
1502 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
1505 Hint: an optional suggestion what to do about the problem. This is
1506 intended to differ from detail in that it offers advice (potentially
1507 inappropriate) rather than hard facts. May run to multiple lines.
1513 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
1516 A string containing a decimal integer indicating an error cursor
1517 position as an index into the original statement string. The first
1518 character has index 1, and positions are measured in characters not
1525 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
1528 This is defined the same as the <symbol>PG_DIAG_STATEMENT_POSITION</>
1529 field, but it is used when the cursor position refers to an internally
1530 generated command rather than the one submitted by the client.
1531 The <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when this field
1538 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
1541 The text of a failed internally-generated command.
1542 This could be, for example, a SQL query issued by a PL/pgSQL function.
1548 <term><symbol>PG_DIAG_CONTEXT</></term>
1551 An indication of the context in which the error occurred.
1552 Presently this includes a call stack traceback of active
1553 procedural language functions and internally-generated queries.
1554 The trace is one entry per line, most recent first.
1560 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
1563 The file name of the source-code location where the error was
1570 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
1573 The line number of the source-code location where the error was
1580 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
1583 The name of the source-code function reporting the error.
1591 The client is responsible for formatting displayed information to meet
1592 its needs; in particular it should break long lines as needed.
1593 Newline characters appearing in the error message fields should be
1594 treated as paragraph breaks, not line breaks.
1598 Errors generated internally by <application>libpq</application> will
1599 have severity and primary message, but typically no other fields.
1600 Errors returned by a pre-3.0-protocol server will include severity and
1601 primary message, and sometimes a detail message, but no other fields.
1605 Note that error fields are only available from
1606 <structname>PGresult</structname> objects, not
1607 <structname>PGconn</structname> objects; there is no
1608 <function>PQerrorField</function> function.
1614 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
1617 Frees the storage associated with a <structname>PGresult</structname>.
1618 Every command result should be freed via <function>PQclear</function> when
1619 it is no longer needed.
1621 void PQclear(PGresult *res);
1626 You can keep a <structname>PGresult</structname> object around for as long as you
1627 need it; it does not go away when you issue a new command,
1628 nor even if you close the connection. To get rid of it,
1629 you must call <function>PQclear</function>. Failure to do this will
1630 result in memory leaks in your application.
1636 <term><function>PQmakeEmptyPGresult</function><indexterm><primary>PQmakeEmptyPGresult</></></term>
1639 Constructs an empty <structname>PGresult</structname> object with the given status.
1641 PGresult* PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
1646 This is <application>libpq</>'s internal function to allocate and initialize an empty
1647 <structname>PGresult</structname> object. It is exported because some applications find it
1648 useful to generate result objects (particularly objects with error
1649 status) themselves. If <parameter>conn</parameter> is not null and <parameter>status</> indicates an error,
1650 the current error message of the specified connection is copied into the <structname>PGresult</structname>.
1651 Note that <function>PQclear</function> should eventually be called on the object, just
1652 as with a <structname>PGresult</structname> returned by <application>libpq</application> itself.
1660 <sect2 id="libpq-exec-select-info">
1661 <title>Retrieving Query Result Information</title>
1664 These functions are used to extract information from a
1665 <structname>PGresult</structname> object that represents a successful
1666 query result (that is, one that has status
1667 <literal>PGRES_TUPLES_OK</literal>). For objects with other status
1668 values they will act as though the result has zero rows and zero columns.
1673 <term><function>PQntuples</function><indexterm><primary>PQntuples</></></term>
1676 Returns the number of rows (tuples)
1677 in the query result.
1679 int PQntuples(const PGresult *res);
1686 <term><function>PQnfields</function><indexterm><primary>PQnfields</></></term>
1689 Returns the number of columns (fields)
1690 in each row of the query result.
1692 int PQnfields(const PGresult *res);
1699 <term><function>PQfname</function><indexterm><primary>PQfname</></></term>
1702 Returns the column name associated with the given column number.
1703 Column numbers start at 0. The caller should not free the result
1704 directly. It will be freed when the associated <structname>PGresult</>
1705 handle is passed to <function>PQclear</function>.
1707 char *PQfname(const PGresult *res,
1713 <symbol>NULL</symbol> is returned if the column number is out of range.
1719 <term><function>PQfnumber</function><indexterm><primary>PQfnumber</></></term>
1722 Returns the column number associated with the given column name.
1724 int PQfnumber(const PGresult *res,
1725 const char *column_name);
1730 -1 is returned if the given name does not match any column.
1734 The given name is treated like an identifier in an SQL command,
1735 that is, it is downcased unless double-quoted. For example,
1736 given a query result generated from the SQL command
1738 select 1 as FOO, 2 as "BAR";
1740 we would have the results:
1742 PQfname(res, 0) <lineannotation>foo</lineannotation>
1743 PQfname(res, 1) <lineannotation>BAR</lineannotation>
1744 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
1745 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
1746 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
1747 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
1754 <term><function>PQftable</function><indexterm><primary>PQftable</></></term>
1757 Returns the OID of the table from which the given column was fetched.
1758 Column numbers start at 0.
1760 Oid PQftable(const PGresult *res,
1766 <literal>InvalidOid</> is returned if the column number is out of range,
1767 or if the specified column is not a simple reference to a table column,
1768 or when using pre-3.0 protocol.
1769 You can query the system table <literal>pg_class</literal> to determine
1770 exactly which table is referenced.
1774 The type <type>Oid</type> and the constant
1775 <literal>InvalidOid</literal> will be defined when you include
1776 the <application>libpq</application> header file. They will
1777 both be some integer type.
1783 <term><function>PQftablecol</function><indexterm><primary>PQftablecol</></></term>
1786 Returns the column number (within its table) of the column making up
1787 the specified query result column.
1788 Query-result column numbers start at 0, but table columns have nonzero
1791 int PQftablecol(const PGresult *res,
1797 Zero is returned if the column number is out of range,
1798 or if the specified column is not a simple reference to a table column,
1799 or when using pre-3.0 protocol.
1805 <term><function>PQfformat</function><indexterm><primary>PQfformat</></></term>
1808 Returns the format code indicating the format of the given column.
1809 Column numbers start at 0.
1811 int PQfformat(const PGresult *res,
1817 Format code zero indicates textual data representation, while format
1818 code one indicates binary representation. (Other codes are reserved
1819 for future definition.)
1825 <term><function>PQftype</function><indexterm><primary>PQftype</></></term>
1828 Returns the data type associated with the
1829 given column number. The integer returned is the
1830 internal OID number of the type. Column numbers start
1833 Oid PQftype(const PGresult *res,
1839 You can query the system table <literal>pg_type</literal> to obtain
1840 the names and properties of the various data types. The <acronym>OID</acronym>s
1841 of the built-in data types are defined in the file <filename>src/include/catalog/pg_type.h</filename>
1848 <term><function>PQfmod</function><indexterm><primary>PQfmod</></></term>
1851 Returns the type modifier of the column
1852 associated with the given column number.
1853 Column numbers start at 0.
1855 int PQfmod(const PGresult *res,
1861 The interpretation of modifier values is type-specific; they typically
1862 indicate precision or size limits. The value -1 is used to indicate
1863 <quote>no information available</>. Most data types do not use modifiers,
1864 in which case the value is always -1.
1870 <term><function>PQfsize</function><indexterm><primary>PQfsize</></></term>
1873 Returns the size in bytes of the column
1874 associated with the given column number.
1875 Column numbers start at 0.
1877 int PQfsize(const PGresult *res,
1883 <function>PQfsize</> returns the space allocated for this column in a database
1884 row, in other words the size of the server's internal representation
1885 of the data type. (Accordingly, it is not really very useful to clients.)
1886 A negative value indicates the data type is variable-length.
1892 <term><function>PQbinaryTuples</function><indexterm><primary>PQbinaryTuples</></></term>
1895 Returns 1 if the <structname>PGresult</> contains binary data
1896 and 0 if it contains text data.
1898 int PQbinaryTuples(const PGresult *res);
1903 This function is deprecated (except for its use in connection with
1904 <command>COPY</>), because it is possible for a single
1905 <structname>PGresult</>
1906 to contain text data in some columns and binary data in others.
1907 <function>PQfformat</> is preferred. <function>PQbinaryTuples</>
1908 returns 1 only if all columns of the result are binary (format 1).
1914 <term><function>PQgetvalue</function><indexterm><primary>PQgetvalue</></></term>
1917 Returns a single field value of one row of a
1918 <structname>PGresult</structname>. Row and column numbers
1919 start at 0. The caller should not free the result
1920 directly. It will be freed when the associated
1921 <structname>PGresult</> handle is passed to
1922 <function>PQclear</function>.
1924 char *PQgetvalue(const PGresult *res,
1931 For data in text format, the value returned by <function>PQgetvalue</function>
1932 is a null-terminated character string representation
1933 of the field value. For data in binary format, the value is in the binary
1934 representation determined by the data type's <function>typsend</> and
1935 <function>typreceive</> functions. (The value is actually followed by
1936 a zero byte in this case too, but that is not ordinarily useful, since
1937 the value is likely to contain embedded nulls.)
1941 An empty string is returned if the field value is null. See
1942 <function>PQgetisnull</> to distinguish null values from empty-string values.
1947 returned by <function>PQgetvalue</function> points to storage that is
1948 part of the <structname>PGresult</structname> structure. One should not modify the data it points to,
1949 and one must explicitly
1950 copy the data into other storage if it is to
1951 be used past the lifetime of the <structname>PGresult</structname> structure itself.
1957 <term><function>PQgetisnull</function><indexterm><primary>PQgetisnull</></></term>
1958 <indexterm><primary>null value</><secondary sortas="libpq">in libpq</></indexterm><listitem>
1960 Tests a field for a null value.
1961 Row and column numbers start at 0.
1963 int PQgetisnull(const PGresult *res,
1970 This function returns 1 if the field is null and 0 if
1971 it contains a non-null value. (Note that <function>PQgetvalue</function>
1972 will return an empty string, not a null pointer, for a null field.)
1978 <term><function>PQgetlength</function><indexterm><primary>PQgetlength</></></term>
1981 Returns the actual length of a field value in bytes.
1982 Row and column numbers start at 0.
1984 int PQgetlength(const PGresult *res,
1991 This is the actual data length for the particular data value, that is, the
1992 size of the object pointed to by <function>PQgetvalue</function>. For text
1993 data format this is the same as <function>strlen()</>. For binary format
1994 this is essential information. Note that one should <emphasis>not</> rely
1995 on <function>PQfsize</function> to obtain the actual data length.
2001 <term><function>PQprint</function><indexterm><primary>PQprint</></></term>
2004 Prints out all the rows and, optionally, the
2005 column names to the specified output stream.
2007 void PQprint(FILE *fout, /* output stream */
2008 const PGresult *res,
2009 const PQprintOpt *po);
2012 pqbool header; /* print output field headings and row count */
2013 pqbool align; /* fill align the fields */
2014 pqbool standard; /* old brain dead format */
2015 pqbool html3; /* output HTML tables */
2016 pqbool expanded; /* expand tables */
2017 pqbool pager; /* use pager for output if needed */
2018 char *fieldSep; /* field separator */
2019 char *tableOpt; /* attributes for HTML table element */
2020 char *caption; /* HTML table caption */
2021 char **fieldName; /* null-terminated array of replacement field names */
2027 This function was formerly used by <application>psql</application>
2028 to print query results, but this is no longer the case. Note that it
2029 assumes all the data is in text format.
2036 <sect2 id="libpq-exec-nonselect">
2037 <title>Retrieving Result Information for Other Commands</title>
2040 These functions are used to extract information from
2041 <structname>PGresult</structname> objects that are not <command>SELECT</>
2047 <term><function>PQcmdStatus</function><indexterm><primary>PQcmdStatus</></></term>
2050 Returns the command status tag from the SQL command that
2051 generated the <structname>PGresult</structname>.
2053 char *PQcmdStatus(PGresult *res);
2057 Commonly this is just the name of the command, but it may include additional
2058 data such as the number of rows processed. The caller should
2059 not free the result directly. It will be freed when the
2060 associated <structname>PGresult</> handle is passed to
2061 <function>PQclear</function>.
2067 <term><function>PQcmdTuples</function><indexterm><primary>PQcmdTuples</></></term>
2070 Returns the number of rows affected by the SQL command.
2072 char *PQcmdTuples(PGresult *res);
2077 This function returns a string containing the number of rows
2078 affected by the <acronym>SQL</> statement that generated the
2079 <structname>PGresult</>. This function can only be used
2080 following the execution of an <command>INSERT</>,
2081 <command>UPDATE</>, <command>DELETE</>, <command>MOVE</>, or
2082 <command>FETCH</> statement, or an <command>EXECUTE</> of a
2083 prepared query that contains a <command>INSERT</>,
2084 <command>UPDATE</>, or <command>DELETE</> statement. If the
2085 command that generated the <structname>PGresult</> was
2086 anything else, <function>PQcmdTuples</> returns the empty
2087 string. The caller should not free the return value
2088 directly. It will be freed when the associated
2089 <structname>PGresult</> handle is passed to
2090 <function>PQclear</function>.
2096 <term><function>PQoidValue</function><indexterm><primary>PQoidValue</></></term>
2099 Returns the OID<indexterm><primary>OID</><secondary>in
2100 libpq</></> of the inserted row, if the <acronym>SQL</>
2101 command was an <command>INSERT</> that inserted exactly one
2102 row into a table that has OIDs, or a <command>EXECUTE</> of
2103 a prepared query containing a suitable <command>INSERT</>
2104 statement. Otherwise, this function returns
2105 <literal>InvalidOid</literal>. This function will also
2106 return <literal>InvalidOid</literal> if the table affected
2107 by the <command>INSERT</> statement does not contain OIDs.
2109 Oid PQoidValue(const PGresult *res);
2116 <term><function>PQoidStatus</function><indexterm><primary>PQoidStatus</></></term>
2119 Returns a string with the OID of the inserted row, if the
2120 <acronym>SQL</acronym> command was an
2121 <command>INSERT</command> that inserted exactly one row, or
2122 a <command>EXECUTE</command> of a prepared statement
2123 consisting of a suitable <command>INSERT</command>. (The string will be
2124 <literal>0</> if the <command>INSERT</command> did not
2125 insert exactly one row, or if the target table does not have
2126 OIDs.) If the command was not an <command>INSERT</command>,
2127 returns an empty string.
2129 char *PQoidStatus(const PGresult *res);
2134 This function is deprecated in favor of <function>PQoidValue</function>.
2135 It is not thread-safe.
2143 <sect2 id="libpq-exec-escape-string">
2144 <title>Escaping Strings for Inclusion in SQL Commands</title>
2146 <indexterm zone="libpq-exec-escape-string"><primary>PQescapeString</></>
2147 <indexterm zone="libpq-exec-escape-string"><primary>escaping strings</></>
2150 <function>PQescapeString</function> escapes a string for use within an SQL
2151 command. This is useful when inserting data values as literal constants
2152 in SQL commands. Certain characters (such as quotes and backslashes) must
2153 be escaped to prevent them from being interpreted specially by the SQL parser.
2154 <function>PQescapeString</> performs this operation.
2159 It is especially important to do proper escaping when handling strings that
2160 were received from an untrustworthy source. Otherwise there is a security
2161 risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
2162 SQL commands are fed to your database.
2167 Note that it is not necessary nor correct to do escaping when a data
2168 value is passed as a separate parameter in <function>PQexecParams</> or
2169 its sibling routines.
2172 size_t PQescapeString (char *to, const char *from, size_t length);
2177 The parameter <parameter>from</> points to the first character of the string
2178 that is to be escaped, and the <parameter>length</> parameter gives the
2179 number of characters in this string. A terminating zero byte is not
2180 required, and should not be counted in <parameter>length</>. (If
2181 a terminating zero byte is found before <parameter>length</> bytes are
2182 processed, <function>PQescapeString</> stops at the zero; the behavior
2183 is thus rather like <function>strncpy</>.)
2184 <parameter>to</> shall point to a
2185 buffer that is able to hold at least one more character than twice
2186 the value of <parameter>length</>, otherwise the behavior is
2187 undefined. A call to <function>PQescapeString</> writes an escaped
2188 version of the <parameter>from</> string to the <parameter>to</>
2189 buffer, replacing special characters so that they cannot cause any
2190 harm, and adding a terminating zero byte. The single quotes that
2191 must surround <productname>PostgreSQL</> string literals are not
2192 included in the result string; they should be provided in the SQL
2193 command that the result is inserted into.
2196 <function>PQescapeString</> returns the number of characters written
2197 to <parameter>to</>, not including the terminating zero byte.
2200 Behavior is undefined if the <parameter>to</> and <parameter>from</>
2206 <sect2 id="libpq-exec-escape-bytea">
2207 <title>Escaping Binary Strings for Inclusion in SQL Commands</title>
2209 <indexterm zone="libpq-exec-escape-bytea">
2211 <secondary sortas="libpq">in libpq</>
2216 <term><function>PQescapeBytea</function><indexterm><primary>PQescapeBytea</></></term>
2219 Escapes binary data for use within an SQL command with the type
2220 <type>bytea</type>. As with <function>PQescapeString</function>,
2221 this is only used when inserting data directly into an SQL command string.
2223 unsigned char *PQescapeBytea(const unsigned char *from,
2230 Certain byte values <emphasis>must</emphasis> be escaped (but all
2231 byte values <emphasis>may</emphasis> be escaped) when used as part
2232 of a <type>bytea</type> literal in an <acronym>SQL</acronym>
2233 statement. In general, to escape a byte, it is converted into the
2234 three digit octal number equal to the octet value, and preceded by
2235 two backslashes. The single quote (<literal>'</>) and backslash
2236 (<literal>\</>) characters have special alternative escape
2237 sequences. See <xref linkend="datatype-binary"> for more
2238 information. <function>PQescapeBytea</function> performs this
2239 operation, escaping only the minimally required bytes.
2243 The <parameter>from</parameter> parameter points to the first
2244 byte of the string that is to be escaped, and the
2245 <parameter>from_length</parameter> parameter gives the number of
2246 bytes in this binary string. (A terminating zero byte is
2247 neither necessary nor counted.) The <parameter>to_length</parameter>
2248 parameter points to a variable that will hold the resultant
2249 escaped string length. The result string length includes the terminating
2250 zero byte of the result.
2254 <function>PQescapeBytea</> returns an escaped version of the
2255 <parameter>from</parameter> parameter binary string in memory
2256 allocated with <function>malloc()</>. This memory must be freed
2257 using <function>PQfreemem</> when the result is no longer needed.
2258 The return string has all special characters replaced so that they
2259 can be properly processed by the
2260 <productname>PostgreSQL</productname> string literal parser, and
2261 the <type>bytea</type> input function. A terminating zero byte is
2262 also added. The single quotes that must surround
2263 <productname>PostgreSQL</productname> string literals are not part
2264 of the result string.
2270 <term><function>PQunescapeBytea</function><indexterm><primary>PQunescapeBytea</></></term>
2273 Converts an escaped string representation of binary data into binary
2274 data — the reverse of <function>PQescapeBytea</function>.
2275 This is needed when retrieving <type>bytea</type> data in text format,
2276 but not when retrieving it in binary format.
2279 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
2284 The <parameter>from</parameter> parameter points to an escaped string
2285 such as might be returned by <function>PQgetvalue</function> when applied to a
2286 <type>bytea</type> column. <function>PQunescapeBytea</function> converts
2287 this string representation into its binary representation.
2288 It returns a pointer to a buffer allocated with
2289 <function>malloc()</function>, or null on error, and puts the size of
2290 the buffer in <parameter>to_length</parameter>. The result must be
2291 freed using <function>PQfreemem</> when it is no longer needed.
2297 <term><function>PQfreemem</function><indexterm><primary>PQfreemem</></></term>
2300 Frees memory allocated by <application>libpq</>.
2302 void PQfreemem(void *ptr);
2307 Frees memory allocated by <application>libpq</>, particularly
2308 <function>PQescapeBytea</function>,
2309 <function>PQunescapeBytea</function>,
2310 and <function>PQnotifies</function>.
2311 It is needed by Microsoft Windows, which cannot free memory across
2312 DLLs, unless multithreaded DLLs (<option>/MD</option> in VC6) are used.
2313 On other platforms, this function is the same as the standard library function <function>free()</>.
2322 <sect1 id="libpq-async">
2323 <title>Asynchronous Command Processing</title>
2325 <indexterm zone="libpq-async"><primary>nonblocking connection</></>
2328 The <function>PQexec</function> function is adequate for submitting commands in
2330 applications. It has a couple of deficiencies, however, that can be of importance to some users:
2335 <function>PQexec</function> waits for the command to be completed. The application may have other
2336 work to do (such as maintaining a user interface), in which case it won't
2337 want to block waiting for the response.
2342 Since the execution of the client application is suspended while it
2343 waits for the result, it is hard for the application to decide that it
2344 would like to try to cancel the ongoing command. (It can be done from
2345 a signal handler, but not otherwise.)
2350 <function>PQexec</function> can return only one <structname>PGresult</structname> structure. If the submitted command
2351 string contains multiple <acronym>SQL</acronym> commands, all but the last <structname>PGresult</structname> are
2352 discarded by <function>PQexec</function>.
2359 Applications that do not like these limitations can instead use the
2360 underlying functions that <function>PQexec</function> is built from:
2361 <function>PQsendQuery</function> and <function>PQgetResult</function>.
2363 <function>PQsendQueryParams</function>,
2364 <function>PQsendPrepare</function>, and
2365 <function>PQsendQueryPrepared</function>,
2366 which can be used with <function>PQgetResult</function> to duplicate the
2368 <function>PQexecParams</function>,
2369 <function>PQprepare</function>, and
2370 <function>PQexecPrepared</function>
2375 <term><function>PQsendQuery</function><indexterm><primary>PQsendQuery</></></term>
2378 Submits a command to the server without
2379 waiting for the result(s). 1 is returned if the command was
2380 successfully dispatched and 0 if not (in which case, use
2381 <function>PQerrorMessage</> to get more information about the failure).
2383 int PQsendQuery(PGconn *conn, const char *command);
2386 After successfully calling <function>PQsendQuery</function>, call
2387 <function>PQgetResult</function> one or more
2388 times to obtain the results. <function>PQsendQuery</function> may not be called
2389 again (on the same connection) until <function>PQgetResult</function> has returned a null pointer,
2390 indicating that the command is done.
2396 <term><function>PQsendQueryParams</function><indexterm><primary>PQsendQueryParams</></></term>
2399 Submits a command and separate parameters to the server without
2400 waiting for the result(s).
2402 int PQsendQueryParams(PGconn *conn,
2403 const char *command,
2405 const Oid *paramTypes,
2406 const char * const *paramValues,
2407 const int *paramLengths,
2408 const int *paramFormats,
2412 This is equivalent to <function>PQsendQuery</function> except that
2413 query parameters can be specified separately from the query string.
2414 The function's parameters are handled identically to
2415 <function>PQexecParams</function>. Like
2416 <function>PQexecParams</function>, it will not work on 2.0-protocol
2417 connections, and it allows only one command in the query string.
2423 <term><function>PQsendPrepare</><indexterm><primary>PQsendPrepare</></></term>
2426 Sends a request to create a prepared statement with the given
2427 parameters, without waiting for completion.
2429 int PQsendPrepare(PGconn *conn,
2430 const char *stmtName,
2433 const Oid *paramTypes);
2436 This is an asynchronous version of <function>PQprepare</>: it
2437 returns 1 if it was able to dispatch the request, and 0 if not.
2438 After a successful call, call <function>PQgetResult</function>
2439 to determine whether the server successfully created the prepared
2441 The function's parameters are handled identically to
2442 <function>PQprepare</function>. Like
2443 <function>PQprepare</function>, it will not work on 2.0-protocol
2450 <term><function>PQsendQueryPrepared</function><indexterm><primary>PQsendQueryPrepared</></></term>
2453 Sends a request to execute a prepared statement with given
2454 parameters, without waiting for the result(s).
2456 int PQsendQueryPrepared(PGconn *conn,
2457 const char *stmtName,
2459 const char * const *paramValues,
2460 const int *paramLengths,
2461 const int *paramFormats,
2465 This is similar to <function>PQsendQueryParams</function>, but the
2466 command to be executed is specified by naming a previously-prepared
2467 statement, instead of giving a query string.
2468 The function's parameters are handled identically to
2469 <function>PQexecPrepared</function>. Like
2470 <function>PQexecPrepared</function>, it will not work on 2.0-protocol
2477 <term><function>PQgetResult</function><indexterm><primary>PQgetResult</></></term>
2480 Waits for the next result from a prior
2481 <function>PQsendQuery</function>,
2482 <function>PQsendQueryParams</function>,
2483 <function>PQsendPrepare</function>, or
2484 <function>PQsendQueryPrepared</function> call,
2485 and returns it. A null pointer is returned when the command is complete
2486 and there will be no more results.
2488 PGresult *PQgetResult(PGconn *conn);
2493 <function>PQgetResult</function> must be called repeatedly until it returns a null pointer,
2494 indicating that the command is done. (If called when no command is
2495 active, <function>PQgetResult</function> will just return a null pointer at once.)
2496 Each non-null result from <function>PQgetResult</function> should be processed using
2497 the same <structname>PGresult</> accessor functions previously described.
2498 Don't forget to free each result object with <function>PQclear</function> when done with it.
2499 Note that <function>PQgetResult</function> will block only if a command is active and the
2500 necessary response data has not yet been read by <function>PQconsumeInput</function>.
2508 Using <function>PQsendQuery</function> and <function>PQgetResult</function>
2509 solves one of <function>PQexec</function>'s problems:
2510 If a command string contains multiple <acronym>SQL</acronym> commands, the results of those
2511 commands can be obtained individually. (This allows a simple form of
2512 overlapped processing, by the way: the client can be handling the
2513 results of one command while the server is still working on later
2514 queries in the same command string.) However, calling <function>PQgetResult</function> will
2515 still cause the client to block until the server completes the
2516 next <acronym>SQL</acronym> command. This can be avoided by proper use of two
2521 <term><function>PQconsumeInput</function><indexterm><primary>PQconsumeInput</></></term>
2524 If input is available from the server, consume it.
2526 int PQconsumeInput(PGconn *conn);
2531 <function>PQconsumeInput</function> normally returns 1 indicating <quote>no error</quote>,
2532 but returns 0 if there was some kind of trouble (in which case
2533 <function>PQerrorMessage</function> can be consulted). Note that the result
2535 whether any input data was actually collected. After calling
2536 <function>PQconsumeInput</function>, the application may check
2537 <function>PQisBusy</function> and/or <function>PQnotifies</function> to see if
2538 their state has changed.
2541 <function>PQconsumeInput</function> may be called even if the application is not
2542 prepared to deal with a result or notification just yet. The
2543 function will read available data and save it in a buffer, thereby
2544 causing a <function>select()</function> read-ready indication to go away. The
2545 application can thus use <function>PQconsumeInput</function> to clear the
2546 <function>select()</function> condition immediately, and then examine the results at leisure.
2552 <term><function>PQisBusy</function><indexterm><primary>PQisBusy</></></term>
2555 Returns 1 if a command is busy, that is, <function>PQgetResult</function> would block
2556 waiting for input. A 0 return indicates that <function>PQgetResult</function> can
2557 be called with assurance of not blocking.
2559 int PQisBusy(PGconn *conn);
2564 <function>PQisBusy</function> will not itself attempt to read data from the server;
2565 therefore <function>PQconsumeInput</function> must be invoked first, or the busy
2566 state will never end.
2574 A typical application using these functions will have a main loop that uses
2575 <function>select()</function> or <function>poll()</> to wait for all the
2576 conditions that it must
2577 respond to. One of the conditions will be input available from the server,
2578 which in terms of <function>select()</function> means readable data on the file
2579 descriptor identified by <function>PQsocket</function>.
2580 When the main loop detects input ready, it should call
2581 <function>PQconsumeInput</function> to read the input. It can then call
2582 <function>PQisBusy</function>, followed by <function>PQgetResult</function>
2583 if <function>PQisBusy</function> returns false (0). It can also call
2584 <function>PQnotifies</function> to detect <command>NOTIFY</> messages (see <xref linkend="libpq-notify">).
2589 <function>PQsendQuery</function>/<function>PQgetResult</function> can
2590 also attempt to cancel a command that is still being processed by the
2591 server; see <xref linkend="libpq-cancel">. But regardless of the return value
2592 of <function>PQcancel</function>, the application must continue with the
2593 normal result-reading sequence using <function>PQgetResult</function>.
2594 A successful cancellation will simply cause the command to terminate
2595 sooner than it would have otherwise.
2599 By using the functions described above, it is possible to avoid blocking
2600 while waiting for input from the database server. However, it is still
2601 possible that the application will block waiting to send output to the
2602 server. This is relatively uncommon but can happen if very long SQL commands
2603 or data values are sent. (It is much more probable if the application
2604 sends data via <command>COPY IN</command>, however.) To prevent this possibility and achieve
2605 completely nonblocking database operation, the following additional
2606 functions may be used.
2610 <term><function>PQsetnonblocking</function><indexterm><primary>PQsetnonblocking</></></term>
2613 Sets the nonblocking status of the connection.
2615 int PQsetnonblocking(PGconn *conn, int arg);
2620 Sets the state of the connection to nonblocking if
2621 <parameter>arg</parameter> is 1, or
2622 blocking if <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
2625 In the nonblocking state, calls to
2626 <function>PQsendQuery</function>,
2627 <function>PQputline</function>, <function>PQputnbytes</function>,
2628 and <function>PQendcopy</function>
2629 will not block but instead return an error if they need to be called
2633 Note that <function>PQexec</function> does not honor nonblocking mode;
2634 if it is called, it will act in blocking fashion anyway.
2640 <term><function>PQisnonblocking</function><indexterm><primary>PQisnonblocking</></></term>
2643 Returns the blocking status of the database connection.
2645 int PQisnonblocking(const PGconn *conn);
2650 Returns 1 if the connection is set to nonblocking mode and
2657 <term><function>PQflush</function><indexterm><primary>PQflush</></></term>
2660 Attempts to flush any queued output data to the server.
2661 Returns 0 if successful (or if the send queue is empty), -1 if it failed for
2662 some reason, or 1 if it was unable to send all the data in the send queue yet
2663 (this case can only occur if the connection is nonblocking).
2665 int PQflush(PGconn *conn);
2674 After sending any command or data on a nonblocking connection, call
2675 <function>PQflush</function>. If it returns 1, wait for the socket to be
2676 write-ready and call it again; repeat until it returns 0. Once
2677 <function>PQflush</function> returns 0, wait for the socket to be read-ready
2678 and then read the response as described above.
2683 <sect1 id="libpq-cancel">
2684 <title>Cancelling Queries in Progress</title>
2686 <indexterm zone="libpq-cancel"><primary>canceling</><secondary>SQL command</></>
2689 A client application can request cancellation of
2690 a command that is still being processed by the
2691 server, using the functions described in this section.
2695 <term><function>PQgetCancel</function><indexterm><primary>PQgetCancel</></></term>
2698 Creates a data structure containing the information needed to cancel
2699 a command issued through a particular database connection.
2701 PGcancel *PQgetCancel(PGconn *conn);
2706 <function>PQgetCancel</function> creates a
2707 <structname>PGcancel</><indexterm><primary>PGcancel</></> object given
2708 a <structname>PGconn</> connection object. It will return NULL if the
2709 given <parameter>conn</> is NULL or an invalid connection. The
2710 <structname>PGcancel</> object is an opaque structure that is not meant
2711 to be accessed directly by the application; it can only be passed to
2712 <function>PQcancel</function> or <function>PQfreeCancel</function>.
2718 <term><function>PQfreeCancel</function><indexterm><primary>PQfreeCancel</></></term>
2721 Frees a data structure created by <function>PQgetCancel</function>.
2723 void PQfreeCancel(PGcancel *cancel);
2728 <function>PQfreeCancel</function> frees a data object previously created
2729 by <function>PQgetCancel</function>.
2735 <term><function>PQcancel</function><indexterm><primary>PQcancel</></></term>
2738 Requests that the server abandon
2739 processing of the current command.
2741 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
2746 The return value is 1 if the cancel request was successfully
2747 dispatched and 0 if not. If not, <parameter>errbuf</> is filled with an error
2748 message explaining why not. <parameter>errbuf</> must be a char array of size
2749 <parameter>errbufsize</> (the recommended size is 256 bytes).
2753 Successful dispatch is no guarantee that the request will have any effect,
2754 however. If the cancellation is effective, the current command will terminate
2755 early and return an error result. If the cancellation fails (say, because the
2756 server was already done processing the command), then there will be no visible
2761 <function>PQcancel</function> can safely be invoked from a signal handler,
2762 if the <parameter>errbuf</> is a local variable in the signal handler. The
2763 <structname>PGcancel</> object is read-only as far as
2764 <function>PQcancel</function> is concerned, so it can also be invoked from a
2765 thread that is separate from the one manipulating the <structname>PGconn</>
2774 <term><function>PQrequestCancel</function><indexterm><primary>PQrequestCancel</></></term>
2777 Requests that the server abandon
2778 processing of the current command.
2780 int PQrequestCancel(PGconn *conn);
2785 <function>PQrequestCancel</function> is a deprecated variant of
2786 <function>PQcancel</function>. It operates directly on the
2787 <structname>PGconn</> object, and in case of failure stores the
2788 error message in the <structname>PGconn</> object (whence it can be
2789 retrieved by <function>PQerrorMessage</function>). Although the
2790 functionality is the same, this approach creates hazards for multiple-thread
2791 programs and signal handlers, since it is possible that overwriting the
2792 <structname>PGconn</>'s error message will mess up the operation currently
2793 in progress on the connection.
2802 <sect1 id="libpq-fastpath">
2803 <title>The Fast-Path Interface</title>
2805 <indexterm zone="libpq-fastpath"><primary>fast path</></>
2808 <productname>PostgreSQL</productname> provides a fast-path interface to send
2809 simple function calls to the server.
2814 This interface is somewhat obsolete, as one may achieve similar performance
2815 and greater functionality by setting up a prepared statement to define the
2816 function call. Then, executing the statement with binary transmission of
2817 parameters and results substitutes for a fast-path function call.
2822 The function <function>PQfn</function><indexterm><primary>PQfn</></>
2823 requests execution of a server function via the fast-path interface:
2825 PGresult *PQfn(PGconn *conn,
2830 const PQArgBlock *args,
2845 The <parameter>fnid</> argument is the OID of the function to be
2846 executed. <parameter>args</> and <parameter>nargs</> define the
2847 parameters to be passed to the function; they must match the declared
2848 function argument list. When the <parameter>isint</> field of a
2851 the <parameter>u.integer</> value is sent to the server as an integer
2852 of the indicated length (this must be 1, 2, or 4 bytes); proper
2853 byte-swapping occurs. When <parameter>isint</> is false, the
2854 indicated number of bytes at <parameter>*u.ptr</> are sent with no
2855 processing; the data must be in the format expected by the server for
2856 binary transmission of the function's argument data type.
2857 <parameter>result_buf</parameter> is the buffer in which
2858 to place the return value. The caller must have allocated
2859 sufficient space to store the return value. (There is no check!)
2860 The actual result length will be returned in the integer pointed
2861 to by <parameter>result_len</parameter>.
2862 If a 1, 2, or 4-byte integer result is expected, set
2863 <parameter>result_is_int</parameter> to 1, otherwise set it to 0.
2864 Setting <parameter>result_is_int</parameter> to 1
2865 causes <application>libpq</> to byte-swap the value if necessary, so that
2867 delivered as a proper <type>int</type> value for the client machine. When
2868 <parameter>result_is_int</> is 0, the binary-format byte string sent by
2869 the server is returned unmodified.
2873 <function>PQfn</function> always returns a valid <structname>PGresult</structname> pointer. The result status
2874 should be checked before the result is used. The
2875 caller is responsible for freeing the <structname>PGresult</structname> with
2876 <function>PQclear</function> when it is no longer needed.
2880 Note that it is not possible to handle null arguments, null results, nor
2881 set-valued results when using this interface.
2886 <sect1 id="libpq-notify">
2887 <title>Asynchronous Notification</title>
2889 <indexterm zone="libpq-notify">
2890 <primary>NOTIFY</primary>
2891 <secondary>in libpq</secondary>
2895 <productname>PostgreSQL</productname> offers asynchronous notification via the
2896 <command>LISTEN</command> and <command>NOTIFY</command> commands. A client session registers its interest in a particular
2897 notification condition with the <command>LISTEN</command> command (and can stop listening
2898 with the <command>UNLISTEN</command> command). All sessions listening on a
2899 particular condition will be notified asynchronously when a <command>NOTIFY</command> command with that
2900 condition name is executed by any session. No additional information is
2901 passed from the notifier to the listener. Thus, typically, any actual data
2902 that needs to be communicated is transferred through a database table.
2903 Commonly, the condition name is the same as the associated table, but it is
2904 not necessary for there to be any associated table.
2908 <application>libpq</application> applications submit
2909 <command>LISTEN</command> and <command>UNLISTEN</command> commands as
2910 ordinary SQL commands. The arrival of <command>NOTIFY</command>
2911 messages can subsequently be detected by calling
2912 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
2916 The function <function>PQnotifies</function>
2917 returns the next notification from a list of unhandled
2918 notification messages received from the server. It returns a null pointer if
2919 there are no pending notifications. Once a notification is
2920 returned from <function>PQnotifies</>, it is considered handled and will be
2921 removed from the list of notifications.
2923 PGnotify *PQnotifies(PGconn *conn);
2925 typedef struct pgNotify {
2926 char *relname; /* notification condition name */
2927 int be_pid; /* process ID of server process */
2928 char *extra; /* notification parameter */
2931 After processing a <structname>PGnotify</structname> object returned by
2932 <function>PQnotifies</function>, be sure to free it with
2933 <function>PQfreemem</function>. It is sufficient to free the
2934 <structname>PGnotify</structname> pointer; the
2935 <structfield>relname</structfield> and <structfield>extra</structfield> fields
2936 do not represent separate allocations.
2937 (At present, the <structfield>extra</structfield> field is unused and will
2938 always point to an empty string.)
2943 In <productname>PostgreSQL</productname> 6.4 and later,
2944 the <structfield>be_pid</structfield> is that of the notifying server process,
2945 whereas in earlier versions it was always the <acronym>PID</acronym> of your own server process.
2950 <xref linkend="libpq-example-2"> gives a sample program that illustrates the use
2951 of asynchronous notification.
2955 <function>PQnotifies</function> does not actually read data from the server; it just
2956 returns messages previously absorbed by another <application>libpq</application>
2957 function. In prior releases of <application>libpq</application>, the only way
2958 to ensure timely receipt of <command>NOTIFY</> messages was to constantly submit commands,
2959 even empty ones, and then check <function>PQnotifies</function> after each
2960 <function>PQexec</function>. While this still works, it is
2961 deprecated as a waste of processing power.
2965 A better way to check for <command>NOTIFY</>
2966 messages when you have no useful commands to execute is to call
2967 <function>PQconsumeInput</function>, then check
2968 <function>PQnotifies</function>.
2969 You can use <function>select()</function> to wait for data to
2970 arrive from the server, thereby using no <acronym>CPU</acronym> power unless there is something
2971 to do. (See <function>PQsocket</function> to obtain the file descriptor
2972 number to use with <function>select()</function>.)
2973 Note that this will work OK whether you submit commands with
2974 <function>PQsendQuery</function>/<function>PQgetResult</function> or simply
2975 use <function>PQexec</function>. You should, however, remember to
2976 check <function>PQnotifies</function> after each
2977 <function>PQgetResult</function> or <function>PQexec</function>, to see
2978 if any notifications came in during the processing of the command.
2983 <sect1 id="libpq-copy">
2984 <title>Functions Associated with the <command>COPY</command> Command</title>
2986 <indexterm zone="libpq-copy">
2987 <primary>COPY</primary>
2988 <secondary>with libpq</secondary>
2992 The <command>COPY</command> command in <productname>PostgreSQL</productname>
2993 has options to read from or write to the network connection used by
2994 <application>libpq</application>. The functions described in this section
2995 allow applications to take advantage of this capability by supplying or
2996 consuming copied data.
3000 The overall process is that the application first issues the SQL
3001 <command>COPY</command> command via <function>PQexec</function> or one
3002 of the equivalent functions. The response to this (if there is no error
3003 in the command) will be a <structname>PGresult</> object bearing a status
3004 code of <literal>PGRES_COPY_OUT</literal> or <literal>PGRES_COPY_IN</literal>
3005 (depending on the specified copy direction). The application should then
3006 use the functions of this section to receive or transmit data rows.
3007 When the data transfer is complete, another <structname>PGresult</> object
3008 is returned to indicate success or failure of the transfer. Its status
3009 will be <literal>PGRES_COMMAND_OK</literal> for success or
3010 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
3011 At this point further SQL commands may be issued via
3012 <function>PQexec</function>. (It is not possible to execute other SQL
3013 commands using the same connection while the <command>COPY</command>
3014 operation is in progress.)
3018 If a <command>COPY</command> command is issued via
3019 <function>PQexec</function> in a string that could contain additional
3020 commands, the application must continue fetching results via
3021 <function>PQgetResult</> after completing the <command>COPY</command>
3022 sequence. Only when <function>PQgetResult</> returns <symbol>NULL</symbol> is it certain
3023 that the <function>PQexec</function> command string is done and it is
3024 safe to issue more commands.
3028 The functions of this section should be executed only after obtaining a
3029 result status of <literal>PGRES_COPY_OUT</literal> or
3030 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
3031 <function>PQgetResult</function>.
3035 A <structname>PGresult</> object bearing one of these status values
3036 carries some additional data about the <command>COPY</command> operation that
3037 is starting. This additional data is available using functions that are
3038 also used in connection with query results:
3042 <term><function>PQnfields</function><indexterm><primary>PQnfields</><secondary>with COPY</></></term>
3045 Returns the number of columns (fields) to be copied.
3051 <term><function>PQbinaryTuples</function><indexterm><primary>PQbinaryTuples</><secondary>with COPY</></></term>
3054 0 indicates the overall copy format is textual (rows
3055 separated by newlines, columns separated by separator
3057 1 indicates the overall copy format is binary.
3058 See <xref linkend="sql-copy" endterm="sql-copy-title">
3059 for more information.
3065 <term><function>PQfformat</function><indexterm><primary>PQfformat</><secondary>with COPY</></></term>
3068 Returns the format code (0 for text, 1 for binary) associated
3069 with each column of the copy operation. The per-column format
3070 codes will always be zero when the overall copy format is textual,
3071 but the binary format can support both text and binary columns.
3072 (However, as of the current implementation of <command>COPY</>,
3073 only binary columns appear in a binary copy; so the per-column
3074 formats always match the overall format at present.)
3083 These additional data values are only available when using protocol 3.0.
3084 When using protocol 2.0, all these functions will return 0.
3088 <sect2 id="libpq-copy-send">
3089 <title>Functions for Sending <command>COPY</command> Data</title>
3092 These functions are used to send data during <literal>COPY FROM STDIN</>.
3093 They will fail if called when the connection is not in <literal>COPY_IN</>
3099 <term><function>PQputCopyData</function><indexterm><primary>PQputCopyData</></></term>
3102 Sends data to the server during <literal>COPY_IN</> state.
3104 int PQputCopyData(PGconn *conn,
3111 Transmits the <command>COPY</command> data in the specified <parameter>buffer</>, of length
3112 <parameter>nbytes</>, to the server. The result is 1 if the data was sent,
3113 zero if it was not sent because the attempt would block (this case is only
3114 possible if the connection is in nonblocking mode), or -1 if an error occurred.
3115 (Use <function>PQerrorMessage</function> to retrieve details if the return
3116 value is -1. If the value is zero, wait for write-ready and try again.)
3120 The application may divide the <command>COPY</command> data stream into buffer loads of any
3121 convenient size. Buffer-load boundaries have no semantic significance when
3122 sending. The contents of the data stream must match the data format expected
3123 by the <command>COPY</> command; see
3124 <xref linkend="sql-copy" endterm="sql-copy-title"> for details.
3130 <term><function>PQputCopyEnd</function><indexterm><primary>PQputCopyEnd</></></term>
3133 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
3135 int PQputCopyEnd(PGconn *conn,
3136 const char *errormsg);
3141 Ends the <literal>COPY_IN</> operation successfully if <parameter>errormsg</>
3142 is <symbol>NULL</symbol>. If <parameter>errormsg</> is not <symbol>NULL</symbol> then the <command>COPY</>
3143 is forced to fail, with the string pointed to by <parameter>errormsg</>
3144 used as the error message. (One should not assume that this exact error
3145 message will come back from the server, however, as the server might have
3146 already failed the <command>COPY</> for its own reasons. Also note that the
3147 option to force failure does not work when using pre-3.0-protocol connections.)
3151 The result is 1 if the termination data was sent,
3152 zero if it was not sent because the attempt would block (this case is only
3153 possible if the connection is in nonblocking mode), or -1 if an error occurred.
3154 (Use <function>PQerrorMessage</function> to retrieve details if the return
3155 value is -1. If the value is zero, wait for write-ready and try again.)
3159 After successfully calling <function>PQputCopyEnd</>, call
3160 <function>PQgetResult</> to obtain the final result status of the
3161 <command>COPY</> command. One may wait for
3162 this result to be available in the usual way. Then return to normal
3171 <sect2 id="libpq-copy-receive">
3172 <title>Functions for Receiving <command>COPY</command> Data</title>
3175 These functions are used to receive data during <literal>COPY TO STDOUT</>.
3176 They will fail if called when the connection is not in <literal>COPY_OUT</>
3182 <term><function>PQgetCopyData</function><indexterm><primary>PQgetCopyData</></></term>
3185 Receives data from the server during <literal>COPY_OUT</> state.
3187 int PQgetCopyData(PGconn *conn,
3194 Attempts to obtain another row of data from the server during a <command>COPY</command>.
3195 Data is always returned one data row at a time; if only a partial row
3196 is available, it is not returned. Successful return of a data row
3197 involves allocating a chunk of memory to hold the data. The
3198 <parameter>buffer</> parameter must be non-<symbol>NULL</symbol>. <parameter>*buffer</>
3199 is set to point to the allocated memory, or to <symbol>NULL</symbol> in cases where no
3200 buffer is returned. A non-<symbol>NULL</symbol> result buffer must be freed using
3201 <function>PQfreemem</> when no longer needed.
3205 When a row is successfully returned, the return value is the number of
3206 data bytes in the row (this will always be greater than zero). The
3207 returned string is always null-terminated, though this is probably only
3208 useful for textual <command>COPY</command>. A result of zero indicates that the <command>COPY</command> is
3209 still in progress, but no row is yet available (this is only possible
3210 when <parameter>async</> is true). A
3211 result of -1 indicates that the <command>COPY</command> is done.
3212 A result of -2 indicates that an error occurred (consult
3213 <function>PQerrorMessage</> for the reason).
3217 When <parameter>async</> is true (not zero), <function>PQgetCopyData</>
3218 will not block waiting for input; it will return zero if the <command>COPY</command> is still
3219 in progress but no complete row is available. (In this case wait for
3220 read-ready before trying again; it does not matter whether you call
3221 <function>PQconsumeInput</>.) When <parameter>async</> is
3222 false (zero), <function>PQgetCopyData</> will block until data is available
3223 or the operation completes.
3227 After <function>PQgetCopyData</> returns -1, call
3228 <function>PQgetResult</> to obtain the final result status of the
3229 <command>COPY</> command. One may wait for
3230 this result to be available in the usual way. Then return to normal
3239 <sect2 id="libpq-copy-deprecated">
3240 <title>Obsolete Functions for <command>COPY</command></title>
3243 These functions represent older methods of handling <command>COPY</>.
3244 Although they still work, they are deprecated due to poor error handling,
3245 inconvenient methods of detecting end-of-data, and lack of support for binary
3246 or nonblocking transfers.
3251 <term><function>PQgetline</function><indexterm><primary>PQgetline</></></term>
3254 Reads a newline-terminated line of characters
3255 (transmitted by the server) into a buffer
3256 string of size <parameter>length</>.
3258 int PQgetline(PGconn *conn,
3265 This function copies up to <parameter>length</>-1 characters
3266 into the buffer and converts
3267 the terminating newline into a zero byte.
3268 <function>PQgetline</function> returns <symbol>EOF</symbol> at the end of input, 0 if the
3269 entire line has been read, and 1 if the buffer is full but the
3270 terminating newline has not yet been read.
3273 Note that the application must check to see if a
3274 new line consists of the two characters <literal>\.</literal>,
3275 which indicates that the server has finished sending
3276 the results of the <command>COPY</command> command.
3277 If the application might
3278 receive lines that are more than <parameter>length</>-1 characters long,
3279 care is needed to be sure it recognizes the <literal>\.</literal> line correctly
3280 (and does not, for example, mistake the end of a long data line
3281 for a terminator line).
3287 <term><function>PQgetlineAsync</function><indexterm><primary>PQgetlineAsync</></></term>
3290 Reads a row of <command>COPY</command> data
3291 (transmitted by the server) into a buffer
3294 int PQgetlineAsync(PGconn *conn,
3301 This function is similar to <function>PQgetline</function>, but it can be used
3303 that must read <command>COPY</command> data asynchronously, that is, without blocking.
3304 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
3306 application should call <function>PQconsumeInput</function> and
3307 <function>PQgetlineAsync</function> until the
3308 end-of-data signal is detected.
3311 Unlike <function>PQgetline</function>, this function takes
3312 responsibility for detecting end-of-data.
3315 On each call, <function>PQgetlineAsync</function> will return data if a
3316 complete data row is available in <application>libpq</>'s input buffer.
3317 Otherwise, no data is returned until the rest of the row arrives.
3318 The function returns -1 if the end-of-copy-data marker has been recognized,
3319 or 0 if no data is available, or a positive number giving the number of
3320 bytes of data returned. If -1 is returned, the caller must next call
3321 <function>PQendcopy</function>, and then return to normal processing.
3324 The data returned will not extend beyond a data-row boundary. If possible
3325 a whole row will be returned at one time. But if the buffer offered by
3326 the caller is too small to hold a row sent by the server, then a partial
3327 data row will be returned. With textual data this can be detected by testing
3328 whether the last returned byte is <literal>\n</literal> or not. (In a binary
3329 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
3330 equivalent determination.)
3331 The returned string is not null-terminated. (If you want to add a
3332 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
3333 than the room actually available.)
3339 <term><function>PQputline</function><indexterm><primary>PQputline</></></term>
3342 Sends a null-terminated string to the server.
3343 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
3345 int PQputline(PGconn *conn,
3346 const char *string);
3351 The <command>COPY</command> data stream sent by a series of calls to
3352 <function>PQputline</function> has the same format as that returned by
3353 <function>PQgetlineAsync</function>, except that applications are not
3354 obliged to send exactly one data row per <function>PQputline</function>
3355 call; it is okay to send a partial line or multiple lines per call.
3360 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
3361 for the application to explicitly send the two characters
3362 <literal>\.</literal> as a final line to indicate to the server that it had
3363 finished sending <command>COPY</> data. While this still works, it is deprecated and the
3364 special meaning of <literal>\.</literal> can be expected to be removed in a
3365 future release. It is sufficient to call <function>PQendcopy</function> after
3366 having sent the actual data.
3373 <term><function>PQputnbytes</function><indexterm><primary>PQputnbytes</></></term>
3376 Sends a non-null-terminated string to the server.
3377 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
3379 int PQputnbytes(PGconn *conn,
3386 This is exactly like <function>PQputline</function>, except that the data
3387 buffer need not be null-terminated since the number of bytes to send is
3388 specified directly. Use this procedure when sending binary data.
3394 <term><function>PQendcopy</function><indexterm><primary>PQendcopy</></></term>
3397 Synchronizes with the server.
3399 int PQendcopy(PGconn *conn);
3401 This function waits until
3402 the server has finished the copying. It should
3403 either be issued when the last string has been
3404 sent to the server using <function>PQputline</function> or when the
3405 last string has been received from the server
3406 using <function>PGgetline</function>. It must be issued or the server
3407 will get <quote>out of sync</quote> with the client. Upon
3408 return from this function, the server is ready to
3409 receive the next SQL command.
3410 The return value is 0 on successful completion,
3411 nonzero otherwise. (Use <function>PQerrorMessage</function> to retrieve
3412 details if the return value is nonzero.)
3416 When using <function>PQgetResult</function>, the application should respond to
3417 a <literal>PGRES_COPY_OUT</literal> result by executing <function>PQgetline</function>
3418 repeatedly, followed by <function>PQendcopy</function> after the terminator line is seen.
3419 It should then return to the <function>PQgetResult</function> loop until
3420 <function>PQgetResult</function> returns a null pointer. Similarly a <literal>PGRES_COPY_IN</literal>
3421 result is processed by a series of <function>PQputline</function> calls followed by
3422 <function>PQendcopy</function>, then return to the <function>PQgetResult</function> loop.
3423 This arrangement will ensure that
3424 a <command>COPY</command> command embedded in a series of <acronym>SQL</acronym> commands
3425 will be executed correctly.
3429 Older applications are likely to submit a <command>COPY</command>
3430 via <function>PQexec</function> and assume that the transaction is done after
3431 <function>PQendcopy</function>.
3432 This will work correctly only if the <command>COPY</command> is the only
3433 <acronym>SQL</acronym> command in the command string.
3443 <sect1 id="libpq-control">
3444 <title>Control Functions</title>
3447 These functions control miscellaneous details of
3448 <application>libpq</>'s behavior.
3453 <term><function>PQsetErrorVerbosity</function><indexterm><primary>PQsetErrorVerbosity</></></term>
3456 Determines the verbosity of messages returned by
3457 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
3465 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
3467 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
3468 the connection's previous setting. In <firstterm>TERSE</> mode,
3469 returned messages include severity, primary text, and position only;
3470 this will normally fit on a single line. The default mode produces
3471 messages that include the above plus any detail, hint, or context
3472 fields (these may span multiple lines). The <firstterm>VERBOSE</>
3473 mode includes all available fields. Changing the verbosity does not
3474 affect the messages available from already-existing
3475 <structname>PGresult</> objects, only subsequently-created ones.
3481 <term><function>PQtrace</function><indexterm><primary>PQtrace</></></term>
3484 Enables tracing of the client/server communication to a debugging file stream.
3486 void PQtrace(PGconn *conn, FILE *stream);
3493 <term><function>PQuntrace</function><indexterm><primary>PQuntrace</></></term>
3496 Disables tracing started by <function>PQtrace</function>.
3498 void PQuntrace(PGconn *conn);
3507 <sect1 id="libpq-notice-processing">
3508 <title>Notice Processing</title>
3510 <indexterm zone="libpq-notice-processing">
3511 <primary>notice processing</primary>
3512 <secondary>in libpq</secondary>
3516 Notice and warning messages generated by the server are not returned by the
3517 query execution functions, since they do not imply failure of the query.
3518 Instead they are passed to a notice handling function, and execution continues
3519 normally after the handler returns. The default notice handling function
3520 prints the message on <filename>stderr</filename>, but the application can
3521 override this behavior by supplying its own handling function.
3525 For historical reasons, there are two levels of notice handling, called the
3526 notice receiver and notice processor. The default behavior is for the notice
3527 receiver to format the notice and pass a string to the notice processor
3528 for printing. However, an application that chooses to provide its own notice
3529 receiver will typically ignore the notice processor layer and just do all the
3530 work in the notice receiver.
3534 The function <function>PQsetNoticeReceiver</function>
3535 <indexterm><primary>notice receiver</></><indexterm><primary>PQsetNoticeReceiver</></>
3536 sets or examines the current notice receiver for a connection object.
3537 Similarly, <function>PQsetNoticeProcessor</function>
3538 <indexterm><primary>notice processor</></><indexterm><primary>PQsetNoticeProcessor</></>
3539 sets or examines the current notice processor.
3542 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
3545 PQsetNoticeReceiver(PGconn *conn,
3546 PQnoticeReceiver proc,
3549 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
3552 PQsetNoticeProcessor(PGconn *conn,
3553 PQnoticeProcessor proc,
3557 Each of these functions returns the previous notice receiver or processor
3558 function pointer, and sets the new value.
3559 If you supply a null function pointer, no action is taken,
3560 but the current pointer is returned.
3564 When a notice or warning message is received from the server, or generated
3565 internally by <application>libpq</application>, the notice receiver function
3566 is called. It is passed the message in the form of a
3567 <symbol>PGRES_NONFATAL_ERROR</symbol> <structname>PGresult</structname>.
3568 (This allows the receiver to extract individual fields using
3569 <function>PQresultErrorField</>, or the complete preformatted message using
3570 <function>PQresultErrorMessage</>.)
3571 The same void pointer passed to
3572 <function>PQsetNoticeReceiver</function> is also passed.
3573 (This pointer can be used to access application-specific state if needed.)
3577 The default notice receiver simply extracts the message (using
3578 <function>PQresultErrorMessage</>) and passes it to the notice processor.
3582 The notice processor is responsible for handling a notice or warning message
3583 given in text form. It is passed the string text of the message
3584 (including a trailing newline), plus
3585 a void pointer that is the same one passed to
3586 <function>PQsetNoticeProcessor</function>.
3587 (This pointer can be used to access application-specific state if needed.)
3591 The default notice processor is simply
3594 defaultNoticeProcessor(void *arg, const char *message)
3596 fprintf(stderr, "%s", message);
3602 Once you have set a notice receiver or processor, you should expect that that
3603 function could be called as long as either the <structname>PGconn</> object or
3604 <structname>PGresult</> objects made from it exist. At creation of a
3605 <structname>PGresult</>, the <structname>PGconn</>'s current notice handling
3606 pointers are copied into the <structname>PGresult</> for possible use by
3607 functions like <function>PQgetvalue</function>.
3612 <sect1 id="libpq-envars">
3613 <title>Environment Variables</title>
3615 <indexterm zone="libpq-envars">
3616 <primary>environment variable</primary>
3620 The following environment variables can be used to select default
3621 connection parameter values, which will be used by
3622 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
3623 <function>PQsetdb</> if no value is directly specified by the calling
3624 code. These are useful to avoid hard-coding database connection
3625 information into simple client applications, for example.
3631 <primary><envar>PGHOST</envar></primary>
3633 <envar>PGHOST</envar> sets the database server name.
3634 If this begins with a slash, it specifies Unix-domain communication
3635 rather than TCP/IP communication; the value is then the name of the
3636 directory in which the socket file is stored (in a default installation
3637 setup this would be <filename>/tmp</filename>).
3643 <primary><envar>PGHOSTADDR</envar></primary>
3645 <envar>PGHOSTADDR</envar> specifies the numeric IP address of the database
3646 server. This can be set instead of or in addition to <envar>PGHOST</envar>
3647 to avoid DNS lookup overhead. See the documentation of
3648 these parameters, under <function>PQconnectdb</function> above, for details
3649 on their interaction.
3652 When neither <envar>PGHOST</envar> nor <envar>PGHOSTADDR</envar> is set,
3653 the default behavior is to connect using a local Unix-domain socket; or on
3654 machines without Unix-domain sockets, <application>libpq</application> will
3655 attempt to connect to <literal>localhost</>.
3661 <primary><envar>PGPORT</envar></primary>
3663 <envar>PGPORT</envar> sets the TCP port number or Unix-domain
3664 socket file extension for communicating with the
3665 <productname>PostgreSQL</productname> server.
3671 <primary><envar>PGDATABASE</envar></primary>
3673 <envar>PGDATABASE</envar> sets the
3674 <productname>PostgreSQL</productname> database name.
3680 <primary><envar>PGUSER</envar></primary>
3682 <envar>PGUSER</envar>
3683 sets the user name used to connect to the database.
3689 <primary><envar>PGPASSWORD</envar></primary>
3691 <envar>PGPASSWORD</envar>
3692 sets the password used if the server demands password
3693 authentication. This environment variable is deprecated for security
3694 reasons; instead consider using the <filename>~/.pgpass</>
3695 file (see <xref linkend="libpq-pgpass">).
3701 <primary><envar>PGSERVICE</envar></primary>
3703 <envar>PGSERVICE</envar>
3704 sets the service name to be looked up in <filename>pg_service.conf</filename>.
3705 This offers a shorthand way of setting all the parameters.
3711 <primary><envar>PGREALM</envar></primary>
3713 <envar>PGREALM</envar> sets the Kerberos realm to use with
3714 <productname>PostgreSQL</productname>, if it is different from the local realm.
3715 If <envar>PGREALM</envar> is set, <application>libpq</application>
3716 applications will attempt authentication with servers for this realm and use
3717 separate ticket files to avoid conflicts with local
3718 ticket files. This environment variable is only
3719 used if Kerberos authentication is selected by the server.
3725 <primary><envar>PGOPTIONS</envar></primary>
3727 <envar>PGOPTIONS</envar> sets additional run-time options for
3728 the <productname>PostgreSQL</productname> server.
3734 <primary><envar>PGSSLMODE</envar></primary>
3736 <envar>PGSSLMODE</envar> determines whether and with what priority an
3737 <acronym>SSL</> connection will be negotiated with the server. There are
3738 four modes: <literal>disable</> will attempt only an unencrypted
3739 <acronym>SSL</> connection; <literal>allow</> will negotiate,
3740 trying first a non-<acronym>SSL</> connection, then if that fails,
3741 trying an <acronym>SSL</> connection; <literal>prefer</>
3742 (the default) will negotiate, trying first an <acronym>SSL</>
3743 connection, then if that fails, trying a regular non-<acronym>SSL</>
3744 connection; <literal>require</> will try only an <acronym>SSL</>
3745 connection. If <productname>PostgreSQL</> is compiled without SSL support,
3746 using option <literal>require</> will cause an error, while options
3747 <literal>allow</> and <literal>prefer</> will be accepted but
3748 <application>libpq</> will not in fact attempt an <acronym>SSL</>
3755 <primary><envar>PGREQUIRESSL</envar></primary>
3757 <envar>PGREQUIRESSL</envar> sets whether or not the connection must be
3758 made over <acronym>SSL</acronym>. If set to
3759 <quote>1</quote>, <application>libpq</>
3760 will refuse to connect if the server does not accept
3761 an <acronym>SSL</acronym> connection (equivalent to <literal>sslmode</>
3762 <literal>prefer</>).
3763 This option is deprecated in favor of the <literal>sslmode</>
3764 setting, and is only available if
3765 <productname>PostgreSQL</> is compiled with SSL support.
3771 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
3773 <envar>PGCONNECT_TIMEOUT</envar> sets the maximum number of seconds
3774 that <application>libpq</application> will wait when attempting to
3775 connect to the <productname>PostgreSQL</productname> server. If unset
3776 or set to zero, <application>libpq</application> will wait indefinitely.
3777 It is not recommended to set the timeout to less than 2 seconds.
3784 The following environment variables can be used to specify default
3785 behavior for each <productname>PostgreSQL</productname> session.
3787 <xref linkend="sql-alteruser" endterm="sql-alteruser-title"> and
3788 <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">
3789 commands for ways to set default behavior on a per-user or per-database
3796 <primary><envar>PGDATESTYLE</envar></primary>
3798 <envar>PGDATESTYLE</envar>
3799 sets the default style of date/time representation.
3800 (Equivalent to <literal>SET datestyle TO ...</literal>.)
3806 <primary><envar>PGTZ</envar></primary>
3809 sets the default time zone.
3810 (Equivalent to <literal>SET timezone TO ...</literal>.)
3816 <primary><envar>PGCLIENTENCODING</envar></primary>
3818 <envar>PGCLIENTENCODING</envar>
3819 sets the default client character set encoding.
3820 (Equivalent to <literal>SET client_encoding TO ...</literal>.)
3826 <primary><envar>PGGEQO</envar></primary>
3828 <envar>PGGEQO</envar>
3829 sets the default mode for the genetic query optimizer.
3830 (Equivalent to <literal>SET geqo TO ...</literal>.)
3835 Refer to the <acronym>SQL</acronym> command
3836 <xref linkend="sql-set" endterm="sql-set-title">
3837 for information on correct values for these environment variables.
3841 The following environment variables determine internal behavior of
3842 <application>libpq</application>; they override compiled-in defaults.
3848 <primary><envar>PGSYSCONFDIR</envar></primary>
3850 <envar>PGSYSCONFDIR</envar>
3851 sets the directory containing the <filename>pg_service.conf</> file.
3857 <primary><envar>PGLOCALEDIR</envar></primary>
3859 <envar>PGLOCALEDIR</envar>
3860 sets the directory containing the <literal>locale</> files for message
3861 internationalization.
3870 <sect1 id="libpq-pgpass">
3871 <title>The Password File</title>
3873 <indexterm zone="libpq-pgpass">
3874 <primary>password file</primary>
3876 <indexterm zone="libpq-pgpass">
3877 <primary>.pgpass</primary>
3881 The file <filename>.pgpass</filename> in a user's home directory is a file
3882 that can contain passwords to be used if the connection requires a
3883 password (and no password has been specified otherwise).
3884 On Microsoft Windows the file is named
3885 <filename>%APPDATA%\postgresql\pgpass.txt</> (where <filename>%APPDATA%</>
3886 refers to the Application Data subdirectory in the user's profile).
3890 This file should contain lines of the following format:
3892 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
3894 Each of the first four fields may be a literal value, or <literal>*</literal>,
3896 matches anything. The password field from the first line that matches the
3897 current connection parameters will be used. (Therefore, put more-specific
3898 entries first when you are using wildcards.)
3899 If an entry needs to contain <literal>:</literal> or
3900 <literal>\</literal>, escape this character with <literal>\</literal>.
3904 The permissions on <filename>.pgpass</filename> must disallow any
3905 access to world or group; achieve this by the command
3906 <command>chmod 0600 ~/.pgpass</command>.
3907 If the permissions are less strict than this, the file will be ignored.
3908 (The file permissions are not currently checked on Microsoft Windows,
3914 <sect1 id="libpq-ssl">
3915 <title>SSL Support</title>
3917 <indexterm zone="libpq-ssl">
3918 <primary>SSL</primary>
3922 <productname>PostgreSQL</> has native support for using
3923 <acronym>SSL</> connections to encrypt client/server communications
3924 for increased security. See <xref linkend="ssl-tcp"> for details
3925 about the server-side <acronym>SSL</> functionality.
3929 If the server demands a client certificate,
3930 <application>libpq</application>
3931 will send the certificate stored in file
3932 <filename>~/.postgresql/postgresql.crt</> within the user's home directory.
3933 A matching private key file <filename>~/.postgresql/postgresql.key</>
3934 must also be present, and must not be world-readable.
3935 (On Microsoft Windows these files are named
3936 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
3937 <filename>%APPDATA%\postgresql\postgresql.key</filename>.)
3941 If the file <filename>~/.postgresql/root.crt</> is present in the user's
3943 <application>libpq</application> will use the certificate list stored
3944 therein to verify the server's certificate.
3945 (On Microsoft Windows the file is named
3946 <filename>%APPDATA%\postgresql\root.crt</filename>.)
3947 The SSL connection will
3948 fail if the server does not present a certificate; therefore, to
3949 use this feature the server must also have a <filename>root.crt</> file.
3954 <sect1 id="libpq-threading">
3955 <title>Behavior in Threaded Programs</title>
3957 <indexterm zone="libpq-threading">
3958 <primary>threads</primary>
3959 <secondary>with libpq</secondary>
3963 <application>libpq</application> is reentrant and thread-safe if the
3964 <filename>configure</filename> command-line option
3965 <literal>--enable-thread-safety</> was used when the
3966 <productname>PostgreSQL</productname> distribution was built. In
3967 addition, you might need to use additional compiler command-line
3968 options when you compile your application code. Refer to your
3969 system's documentation for information about how to build
3970 thread-enabled applications, or look in
3971 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
3972 and <literal>PTHREAD_LIBS</>.
3976 One restriction is that no two threads attempt to manipulate the same
3977 <structname>PGconn</> object at the same time. In particular, you cannot
3978 issue concurrent commands from different threads through the same
3979 connection object. (If you need to run concurrent commands, use
3980 multiple connections.)
3984 <structname>PGresult</> objects are read-only after creation, and so can be
3985 passed around freely between threads.
3989 The deprecated functions
3990 <function>PQrequestCancel</function>,
3991 <function>PQoidStatus</function> and
3992 <function>fe_setauthsvc</function>
3993 are not thread-safe and should not be used in multithread programs.
3994 <function>PQrequestCancel</function> can be replaced by
3995 <function>PQcancel</function>.
3996 <function>PQoidStatus</function> can be replaced by
3997 <function>PQoidValue</function>.
3998 There is no good reason to call <function>fe_setauthsvc</function> at all.
4002 <application>libpq</application> applications that use the
4003 <literal>crypt</literal> authentication method rely on the
4004 <literal>crypt()</literal> operating system function, which is often
4005 not thread-safe.<indexterm><primary>crypt</><secondary>thread
4006 safety</></> It is better to use the <literal>md5</literal> method,
4007 which is thread-safe on all platforms.
4011 If you experience problems with threaded applications, run
4012 the program in <filename>src/tools/thread</> to see if your
4013 platform has thread-unsafe functions. This program is run
4014 by <filename>configure</filename>, but for binary distributions
4015 your library might not match the library used to build the binaries.
4020 <sect1 id="libpq-build">
4021 <title>Building <application>libpq</application> Programs</title>
4023 <indexterm zone="libpq-build">
4024 <primary>compiling</primary>
4025 <secondary>libpq applications</secondary>
4029 To build (i.e., compile and link) a program using
4030 <application>libpq</application> you need to
4031 do all of the following things:
4036 Include the <filename>libpq-fe.h</filename> header file:
4038 #include <libpq-fe.h>
4040 If you failed to do that then you will normally get error
4041 messages from your compiler similar to
4043 foo.c: In function `main':
4044 foo.c:34: `PGconn' undeclared (first use in this function)
4045 foo.c:35: `PGresult' undeclared (first use in this function)
4046 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
4047 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
4048 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
4055 Point your compiler to the directory where the <productname>PostgreSQL</> header
4056 files were installed, by supplying the
4057 <literal>-I<replaceable>directory</replaceable></literal> option
4058 to your compiler. (In some cases the compiler will look into
4059 the directory in question by default, so you can omit this
4060 option.) For instance, your compile command line could look
4063 cc -c -I/usr/local/pgsql/include testprog.c
4065 If you are using makefiles then add the option to the
4066 <varname>CPPFLAGS</varname> variable:
4068 CPPFLAGS += -I/usr/local/pgsql/include
4073 If there is any chance that your program might be compiled by
4074 other users then you should not hardcode the directory location
4075 like that. Instead, you can run the utility
4076 <command>pg_config</command><indexterm><primary>pg_config</><secondary
4077 sortas="libpq">with libpq</></> to find out where the header
4078 files are on the local system:
4080 <prompt>$</prompt> pg_config --includedir
4081 <computeroutput>/usr/local/include</computeroutput>
4086 Failure to specify the correct option to the compiler will
4087 result in an error message such as
4089 testlibpq.c:8:22: libpq-fe.h: No such file or directory
4096 When linking the final program, specify the option
4097 <literal>-lpq</literal> so that the <application>libpq</application> library gets pulled
4098 in, as well as the option
4099 <literal>-L<replaceable>directory</replaceable></literal> to
4100 point the compiler to the directory where the <application>libpq</application> library resides. (Again, the
4101 compiler will search some directories by default.) For maximum
4102 portability, put the <option>-L</option> option before the
4103 <option>-lpq</option> option. For example:
4105 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
4110 You can find out the library directory using
4111 <command>pg_config</command> as well:
4113 <prompt>$</prompt> pg_config --libdir
4114 <computeroutput>/usr/local/pgsql/lib</computeroutput>
4119 Error messages that point to problems in this area could look
4122 testlibpq.o: In function `main':
4123 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
4124 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
4125 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
4127 This means you forgot <option>-lpq</option>.
4129 /usr/bin/ld: cannot find -lpq
4131 This means you forgot the <option>-L</option> option or did not specify
4132 the right directory.
4139 <indexterm><primary>libpq-int.h</></>
4140 If your codes references the header file
4141 <filename>libpq-int.h</filename> and you refuse to fix your code to
4142 not use it, starting in <productname>PostgreSQL</> 7.2, this file will be found in
4143 <filename><replaceable>includedir</replaceable>/postgresql/internal/libpq-int.h</filename>,
4144 so you need to add the appropriate <option>-I</option> option to
4145 your compiler command line.
4151 <sect1 id="libpq-example">
4152 <title>Example Programs</title>
4155 These examples and others can be found in the
4156 directory <filename>src/test/examples</filename> in the source code
4160 <example id="libpq-example-1">
4161 <title><application>libpq</application> Example Program 1</title>
4167 * Test the C version of LIBPQ, the POSTGRES frontend library.
4169 #include <stdio.h>
4170 #include <stdlib.h>
4171 #include "libpq-fe.h"
4174 exit_nicely(PGconn *conn)
4181 main(int argc, char **argv)
4183 const char *conninfo;
4191 * If the user supplies a parameter on the command line, use it as
4192 * the conninfo string; otherwise default to setting dbname=template1
4193 * and using environment variables or defaults for all other connection
4199 conninfo = "dbname = template1";
4201 /* Make a connection to the database */
4202 conn = PQconnectdb(conninfo);
4204 /* Check to see that the backend connection was successfully made */
4205 if (PQstatus(conn) != CONNECTION_OK)
4207 fprintf(stderr, "Connection to database failed: %s",
4208 PQerrorMessage(conn));
4213 * Our test case here involves using a cursor, for which we must be
4214 * inside a transaction block. We could do the whole thing with a
4215 * single PQexec() of "select * from pg_database", but that's too
4216 * trivial to make a good example.
4219 /* Start a transaction block */
4220 res = PQexec(conn, "BEGIN");
4221 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4223 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
4229 * Should PQclear PGresult whenever it is no longer needed to avoid
4235 * Fetch rows from pg_database, the system catalog of databases
4237 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
4238 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4240 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
4246 res = PQexec(conn, "FETCH ALL in myportal");
4247 if (PQresultStatus(res) != PGRES_TUPLES_OK)
4249 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
4254 /* first, print out the attribute names */
4255 nFields = PQnfields(res);
4256 for (i = 0; i < nFields; i++)
4257 printf("%-15s", PQfname(res, i));
4260 /* next, print out the rows */
4261 for (i = 0; i < PQntuples(res); i++)
4263 for (j = 0; j < nFields; j++)
4264 printf("%-15s", PQgetvalue(res, i, j));
4270 /* close the portal ... we don't bother to check for errors ... */
4271 res = PQexec(conn, "CLOSE myportal");
4274 /* end the transaction */
4275 res = PQexec(conn, "END");
4278 /* close the connection to the database and cleanup */
4286 <example id="libpq-example-2">
4287 <title><application>libpq</application> Example Program 2</title>
4292 * Test of the asynchronous notification interface
4294 * Start this program, then from psql in another window do
4296 * Repeat four times to get this program to exit.
4298 * Or, if you want to get fancy, try this:
4299 * populate a database with the following commands
4300 * (provided in src/test/examples/testlibpq2.sql):
4302 * CREATE TABLE TBL1 (i int4);
4304 * CREATE TABLE TBL2 (i int4);
4306 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
4307 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
4309 * and do this four times:
4311 * INSERT INTO TBL1 VALUES (10);
4313 #include <stdio.h>
4314 #include <stdlib.h>
4315 #include <string.h>
4316 #include <errno.h>
4317 #include <sys/time.h>
4318 #include "libpq-fe.h"
4321 exit_nicely(PGconn *conn)
4328 main(int argc, char **argv)
4330 const char *conninfo;
4337 * If the user supplies a parameter on the command line, use it as
4338 * the conninfo string; otherwise default to setting dbname=template1
4339 * and using environment variables or defaults for all other connection
4345 conninfo = "dbname = template1";
4347 /* Make a connection to the database */
4348 conn = PQconnectdb(conninfo);
4350 /* Check to see that the backend connection was successfully made */
4351 if (PQstatus(conn) != CONNECTION_OK)
4353 fprintf(stderr, "Connection to database failed: %s",
4354 PQerrorMessage(conn));
4359 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
4361 res = PQexec(conn, "LISTEN TBL2");
4362 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4364 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
4370 * should PQclear PGresult whenever it is no longer needed to avoid
4375 /* Quit after four notifies are received. */
4377 while (nnotifies < 4)
4380 * Sleep until something happens on the connection. We use select(2)
4381 * to wait for input, but you could also use poll() or similar
4387 sock = PQsocket(conn);
4390 break; /* shouldn't happen */
4392 FD_ZERO(&input_mask);
4393 FD_SET(sock, &input_mask);
4395 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
4397 fprintf(stderr, "select() failed: %s\n", strerror(errno));
4401 /* Now check for input */
4402 PQconsumeInput(conn);
4403 while ((notify = PQnotifies(conn)) != NULL)
4406 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
4407 notify->relname, notify->be_pid);
4413 fprintf(stderr, "Done.\n");
4415 /* close the connection to the database and cleanup */
4423 <example id="libpq-example-3">
4424 <title><application>libpq</application> Example Program 3</>
4429 * Test out-of-line parameters and binary I/O.
4431 * Before running this, populate a database with the following commands
4432 * (provided in src/test/examples/testlibpq3.sql):
4434 * CREATE TABLE test1 (i int4, t text, b bytea);
4436 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
4437 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
4439 * The expected output is:
4443 * t = (11 bytes) 'joe's place'
4444 * b = (5 bytes) \000\001\002\003\004
4447 #include <stdio.h>
4448 #include <stdlib.h>
4449 #include <string.h>
4450 #include <sys/types.h>
4451 #include "libpq-fe.h"
4453 /* for ntohl/htonl */
4454 #include <netinet/in.h>
4455 #include <arpa/inet.h>
4459 exit_nicely(PGconn *conn)
4466 main(int argc, char **argv)
4468 const char *conninfo;
4471 const char *paramValues[1];
4479 * If the user supplies a parameter on the command line, use it as
4480 * the conninfo string; otherwise default to setting dbname=template1
4481 * and using environment variables or defaults for all other connection
4487 conninfo = "dbname = template1";
4489 /* Make a connection to the database */
4490 conn = PQconnectdb(conninfo);
4492 /* Check to see that the backend connection was successfully made */
4493 if (PQstatus(conn) != CONNECTION_OK)
4495 fprintf(stderr, "Connection to database failed: %s",
4496 PQerrorMessage(conn));
4501 * The point of this program is to illustrate use of PQexecParams()
4502 * with out-of-line parameters, as well as binary transmission of
4503 * results. By using out-of-line parameters we can avoid a lot of
4504 * tedious mucking about with quoting and escaping. Notice how we
4505 * don't have to do anything special with the quote mark in the
4509 /* Here is our out-of-line parameter value */
4510 paramValues[0] = "joe's place";
4512 res = PQexecParams(conn,
4513 "SELECT * FROM test1 WHERE t = $1",
4515 NULL, /* let the backend deduce param type */
4517 NULL, /* don't need param lengths since text */
4518 NULL, /* default to all text params */
4519 1); /* ask for binary results */
4521 if (PQresultStatus(res) != PGRES_TUPLES_OK)
4523 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
4528 /* Use PQfnumber to avoid assumptions about field order in result */
4529 i_fnum = PQfnumber(res, "i");
4530 t_fnum = PQfnumber(res, "t");
4531 b_fnum = PQfnumber(res, "b");
4533 for (i = 0; i < PQntuples(res); i++)
4541 /* Get the field values (we ignore possibility they are null!) */
4542 iptr = PQgetvalue(res, i, i_fnum);
4543 tptr = PQgetvalue(res, i, t_fnum);
4544 bptr = PQgetvalue(res, i, b_fnum);
4547 * The binary representation of INT4 is in network byte order,
4548 * which we'd better coerce to the local byte order.
4550 ival = ntohl(*((uint32_t *) iptr));
4553 * The binary representation of TEXT is, well, text, and since
4554 * libpq was nice enough to append a zero byte to it, it'll work
4555 * just fine as a C string.
4557 * The binary representation of BYTEA is a bunch of bytes, which
4558 * could include embedded nulls so we have to pay attention to
4561 blen = PQgetlength(res, i, b_fnum);
4563 printf("tuple %d: got\n", i);
4564 printf(" i = (%d bytes) %d\n",
4565 PQgetlength(res, i, i_fnum), ival);
4566 printf(" t = (%d bytes) '%s'\n",
4567 PQgetlength(res, i, t_fnum), tptr);
4568 printf(" b = (%d bytes) ", blen);
4569 for (j = 0; j < blen; j++)
4570 printf("\\%03o", bptr[j]);
4576 /* close the connection to the database and cleanup */
4587 <!-- Keep this comment at the end of the file
4592 sgml-minimize-attributes:nil
4593 sgml-always-quote-attributes:t
4596 sgml-parent-document:nil
4597 sgml-default-dtd-file:"./reference.ced"
4598 sgml-exposed-tags:nil
4599 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4600 sgml-local-ecat-files:nil
projects / postgresql / blob