2 $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.188 2005/06/27 02:04:24 neilc 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>krbsrvname</literal></term>
286 Kerberos service name to use when authenticating with Kerberos 5.
287 This must match the service name specified in the server
288 configuration for Kerberos authentication to succeed. (See also
289 <xref linkend="kerberos-auth">.)
295 <term><literal>service</literal></term>
298 Service name to use for additional parameters. It specifies a service
299 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
300 This allows applications to specify only a service name so connection parameters
301 can be centrally maintained. See
302 <filename>share/pg_service.conf.sample</> in the installation
303 directory for information on how to set up the file.
309 If any parameter is unspecified, then the corresponding
310 environment variable (see <xref linkend="libpq-envars">)
311 is checked. If the environment variable is not set either,
312 then the indicated built-in defaults are used.
318 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
321 Makes a new connection to the database server.
323 PGconn *PQsetdbLogin(const char *pghost,
325 const char *pgoptions,
334 This is the predecessor of <function>PQconnectdb</function> with a fixed
335 set of parameters. It has the same functionality except that the
336 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
337 empty string for any one of the fixed parameters that is to be defaulted.
343 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
346 Makes a new connection to the database server.
348 PGconn *PQsetdb(char *pghost,
357 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
358 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
359 for backward compatibility with very old programs.
365 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
366 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
369 <indexterm><primary>nonblocking connection</primary></indexterm>
370 Make a connection to the database server in a nonblocking manner.
372 PGconn *PQconnectStart(const char *conninfo);
375 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
379 These two functions are used to open a connection to a database server such
380 that your application's thread of execution is not blocked on remote I/O
382 The point of this approach is that the waits for I/O to complete can occur
383 in the application's main loop, rather than down inside
384 <function>PQconnectdb</>, and so the application can manage this
385 operation in parallel with other activities.
388 The database connection is made using the parameters taken from the string
389 <literal>conninfo</literal>, passed to <function>PQconnectStart</function>. This string is in
390 the same format as described above for <function>PQconnectdb</function>.
393 Neither <function>PQconnectStart</function> nor <function>PQconnectPoll</function> will block, so long as a number of
394 restrictions are met:
398 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
399 name and reverse name queries are not made. See the documentation of
400 these parameters under <function>PQconnectdb</function> above for details.
406 If you call <function>PQtrace</function>, ensure that the stream object
407 into which you trace will not block.
413 You ensure that the socket is in the appropriate state
414 before calling <function>PQconnectPoll</function>, as described below.
421 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
422 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
423 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
424 representing a valid connection to the database). On return from
425 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
426 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
429 If <function>PQconnectStart</> succeeds, the next stage is to poll
430 <application>libpq</> so that it may proceed with the connection sequence.
431 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
432 socket underlying the database connection.
433 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
434 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
435 read (as indicated by <function>select()</>, <function>poll()</>, or
436 similar system function).
437 Then call <function>PQconnectPoll(conn)</function> again.
438 Conversely, if <function>PQconnectPoll(conn)</function> last returned
439 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
440 to write, then call <function>PQconnectPoll(conn)</function> again.
441 If you have yet to call
442 <function>PQconnectPoll</function>, i.e., just after the call to
443 <function>PQconnectStart</function>, behave as if it last returned
444 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
445 <function>PQconnectPoll(conn)</function> returns
446 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
447 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
448 has been successfully made.
452 At any time during connection, the status of the connection may be
453 checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
454 connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
455 connection is ready. Both of these states are equally detectable
456 from the return value of <function>PQconnectPoll</>, described above. Other states may also occur
457 during (and only during) an asynchronous connection procedure. These
458 indicate the current stage of the connection procedure and may be useful
459 to provide feedback to the user for example. These statuses are:
463 <term><symbol>CONNECTION_STARTED</symbol></term>
466 Waiting for connection to be made.
472 <term><symbol>CONNECTION_MADE</symbol></term>
475 Connection OK; waiting to send.
481 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
484 Waiting for a response from the server.
490 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
493 Received authentication; waiting for backend start-up to finish.
499 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
502 Negotiating SSL encryption.
508 <term><symbol>CONNECTION_SETENV</symbol></term>
511 Negotiating environment-driven parameter settings.
517 Note that, although these constants will remain (in order to maintain
518 compatibility), an application should never rely upon these occurring in a
519 particular order, or at all, or on the status always being one of these
520 documented values. An application might do something like this:
522 switch(PQstatus(conn))
524 case CONNECTION_STARTED:
525 feedback = "Connecting...";
528 case CONNECTION_MADE:
529 feedback = "Connected to server...";
535 feedback = "Connecting...";
541 The <literal>connect_timeout</literal> connection parameter is ignored
542 when using <function>PQconnectPoll</function>; it is the application's
543 responsibility to decide whether an excessive amount of time has elapsed.
544 Otherwise, <function>PQconnectStart</function> followed by a
545 <function>PQconnectPoll</function> loop is equivalent to
546 <function>PQconnectdb</function>.
550 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
551 <function>PQfinish</function> when you are finished with it, in order to dispose of
552 the structure and any associated memory blocks. This must be done even if
553 the connection attempt fails or is abandoned.
559 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
562 Returns the default connection options.
564 PQconninfoOption *PQconndefaults(void);
568 char *keyword; /* The keyword of the option */
569 char *envvar; /* Fallback environment variable name */
570 char *compiled; /* Fallback compiled in default value */
571 char *val; /* Option's current value, or NULL */
572 char *label; /* Label for field in connect dialog */
573 char *dispchar; /* Character to display for this field
574 in a connect dialog. Values are:
575 "" Display entered value as is
576 "*" Password field - hide value
577 "D" Debug option - don't show by default */
578 int dispsize; /* Field size in characters for dialog */
584 Returns a connection options array. This may be used to determine
585 all possible <function>PQconnectdb</function> options and their
586 current default values. The return value points to an array of
587 <structname>PQconninfoOption</structname> structures, which ends
588 with an entry having a null <structfield>keyword</> pointer. The
589 null pointer is returned if memory could not be allocated. Note that
590 the current default values (<structfield>val</structfield> fields)
591 will depend on environment variables and other context. Callers
592 must treat the connection options data as read-only.
596 After processing the options array, free it by passing it to
597 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
598 is leaked for each call to <function>PQconndefaults</function>.
605 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
608 Closes the connection to the server. Also frees
609 memory used by the <structname>PGconn</structname> object.
611 void PQfinish(PGconn *conn);
616 Note that even if the server connection attempt fails (as
617 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
618 to free the memory used by the <structname>PGconn</structname> object.
619 The <structname>PGconn</> pointer must not be used again after
620 <function>PQfinish</function> has been called.
626 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
629 Resets the communication channel to the server.
631 void PQreset(PGconn *conn);
636 This function will close the connection
637 to the server and attempt to reestablish a new
638 connection to the same server, using all the same
639 parameters previously used. This may be useful for
640 error recovery if a working connection is lost.
646 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
647 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
650 Reset the communication channel to the server, in a nonblocking manner.
652 int PQresetStart(PGconn *conn);
655 PostgresPollingStatusType PQresetPoll(PGconn *conn);
660 These functions will close the connection to the server and attempt to
661 reestablish a new connection to the same server, using all the same
662 parameters previously used. This may be useful for error recovery if a
663 working connection is lost. They differ from <function>PQreset</function> (above) in that they
664 act in a nonblocking manner. These functions suffer from the same
665 restrictions as <function>PQconnectStart</> and <function>PQconnectPoll</>.
668 To initiate a connection reset, call <function>PQresetStart</function>. If it returns 0, the reset has failed. If it returns 1,
669 poll the reset using <function>PQresetPoll</function> in exactly the same way as you would
670 create the connection using <function>PQconnectPoll</function>.
679 <sect1 id="libpq-status">
680 <title>Connection Status Functions</title>
683 These functions may be used to interrogate the status
684 of an existing database connection object.
689 <indexterm><primary>libpq-fe.h</></>
690 <indexterm><primary>libpq-int.h</></>
691 <application>libpq</application> application programmers should be careful to
692 maintain the <structname>PGconn</structname> abstraction. Use the accessor
693 functions described below to get
694 at the contents of <structname>PGconn</structname>. Avoid directly referencing the fields of the
695 <structname>PGconn</> structure because they are subject to change in the future.
696 (Beginning in <productname>PostgreSQL</productname> release 6.4, the
697 definition of the <type>struct</type> behind <structname>PGconn</> is not even provided in <filename>libpq-fe.h</filename>.
698 If you have old code that accesses <structname>PGconn</structname> fields directly, you can keep using it
699 by including <filename>libpq-int.h</filename> too, but you are encouraged to fix the code
705 The following functions return parameter values established at connection.
706 These values are fixed for the life of the <structname>PGconn</> object.
710 <term><function>PQdb</function><indexterm><primary>PQdb</></></term>
713 Returns the database name of the connection.
715 char *PQdb(const PGconn *conn);
722 <term><function>PQuser</function><indexterm><primary>PQuser</></></term>
725 Returns the user name of the connection.
727 char *PQuser(const PGconn *conn);
734 <term><function>PQpass</function><indexterm><primary>PQpass</></></term>
737 Returns the password of the connection.
739 char *PQpass(const PGconn *conn);
746 <term><function>PQhost</function><indexterm><primary>PQhost</></></term>
749 Returns the server host name of the connection.
751 char *PQhost(const PGconn *conn);
758 <term><function>PQport</function><indexterm><primary>PQport</></></term>
761 Returns the port of the connection.
763 char *PQport(const PGconn *conn);
770 <term><function>PQtty</function><indexterm><primary>PQtty</></></term>
773 Returns the debug <acronym>TTY</acronym> of the connection.
774 (This is obsolete, since the server no longer pays attention
775 to the <acronym>TTY</acronym> setting, but the function remains
776 for backwards compatibility.)
778 char *PQtty(const PGconn *conn);
785 <term><function>PQoptions</function><indexterm><primary>PQoptions</></></term>
788 Returns the command-line options passed in the connection request.
790 char *PQoptions(const PGconn *conn);
799 The following functions return status data that can change as operations
800 are executed on the <structname>PGconn</> object.
804 <term><function>PQstatus</function><indexterm><primary>PQstatus</></></term>
807 Returns the status of the connection.
809 ConnStatusType PQstatus(const PGconn *conn);
814 The status can be one of a number of values.
815 However, only two of these are
816 seen outside of an asynchronous connection procedure:
817 <literal>CONNECTION_OK</literal> and
818 <literal>CONNECTION_BAD</literal>. A good
819 connection to the database has the status <literal>CONNECTION_OK</literal>.
821 attempt is signaled by status
822 <literal>CONNECTION_BAD</literal>.
823 Ordinarily, an OK status will remain so until
824 <function>PQfinish</function>, but a
825 communications failure might result in the status changing to
826 <literal>CONNECTION_BAD</literal> prematurely.
827 In that case the application
828 could try to recover by calling <function>PQreset</function>.
832 See the entry for <function>PQconnectStart</> and <function>PQconnectPoll</> with regards
833 to other status codes
840 <term><function>PQtransactionStatus</function><indexterm><primary>PQtransactionStatus</></></term>
843 Returns the current in-transaction status of the server.
845 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
848 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
849 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
850 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
851 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
852 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
853 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
854 has been sent to the server and not yet completed.
858 <function>PQtransactionStatus</> will give incorrect results when using
859 a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
860 set to off. The server-side autocommit feature has been
861 deprecated and does not exist in later server versions.
868 <term><function>PQparameterStatus</function><indexterm><primary>PQparameterStatus</></></term>
871 Looks up a current parameter setting of the server.
873 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
876 Certain parameter values are reported by the server automatically at
877 connection startup or whenever their values change.
878 <function>PQparameterStatus</> can be used to interrogate these settings.
879 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
880 if the parameter is not known.
884 Parameters reported as of the current release include
885 <literal>server_version</>,
886 <literal>server_encoding</>,
887 <literal>client_encoding</>,
888 <literal>is_superuser</>,
889 <literal>session_authorization</>,
890 <literal>DateStyle</>,
891 <literal>TimeZone</>,
892 <literal>integer_datetimes</>, and
893 <literal>standard_compliant_strings</>.
894 (<literal>server_encoding</>, <literal>TimeZone</>, and
895 <literal>integer_datetimes</> were not reported by releases before 8.0;
896 <literal>standard_compliant_strings</> was not reported by releases
899 <literal>server_version</>,
900 <literal>server_encoding</> and
901 <literal>integer_datetimes</>
902 cannot change after startup.
906 Pre-3.0-protocol servers do not report parameter settings, but
907 <application>libpq</> includes logic to obtain values for
908 <literal>server_version</> and <literal>client_encoding</> anyway.
909 Applications are encouraged to use <function>PQparameterStatus</>
910 rather than <foreignphrase>ad hoc</> code to determine these values.
912 that on a pre-3.0 connection, changing <literal>client_encoding</> via
913 <command>SET</> after connection startup will not be reflected by
914 <function>PQparameterStatus</>.) For <literal>server_version</>,
915 see also <function>PQserverVersion</>, which returns the information
916 in a numeric form that is much easier to compare against.
920 If no value for <literal>standard_compliant_strings</> is reported,
921 applications may assume it is <literal>false</>, that is, backslashes
922 are treated as escapes in string literals. Also, the presence of this
923 parameter may be taken as an indication that the escape string syntax
924 (<literal>E'...'</>) is accepted.
928 Although the returned pointer is declared <literal>const</>, it in fact
929 points to mutable storage associated with the <literal>PGconn</> structure.
930 It is unwise to assume the pointer will remain valid across queries.
936 <term><function>PQprotocolVersion</function><indexterm><primary>PQprotocolVersion</></></term>
939 Interrogates the frontend/backend protocol being used.
941 int PQprotocolVersion(const PGconn *conn);
943 Applications may wish to use this to determine whether certain features
945 Currently, the possible values are 2 (2.0 protocol), 3 (3.0 protocol),
946 or zero (connection bad). This will not change after connection
947 startup is complete, but it could theoretically change during a connection
948 reset. The 3.0 protocol will normally be used when communicating with
949 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers support
950 only protocol 2.0. (Protocol 1.0 is obsolete and not supported by <application>libpq</application>.)
956 <term><function>PQserverVersion</function><indexterm><primary>PQserverVersion</></></term>
959 Returns an integer representing the backend version.
961 int PQserverVersion(const PGconn *conn);
963 Applications may use this to determine the version of the database server they
964 are connected to. The number is formed by converting the major, minor, and
965 revision numbers into two-decimal-digit numbers and appending them
966 together. For example, version 7.4.2 will be returned as 70402, and version
967 8.1 will be returned as 80100 (leading zeroes are not shown). Zero is
968 returned if the connection is bad.
974 <term><function>PQerrorMessage</function><indexterm><primary>PQerrorMessage</></></term>
977 <indexterm><primary>error message</></>
978 Returns the error message most recently generated by
979 an operation on the connection.
981 char *PQerrorMessage(const PGconn *conn);
986 Nearly all <application>libpq</> functions will set a message for
987 <function>PQerrorMessage</function> if they fail.
988 Note that by <application>libpq</application> convention, a nonempty
989 <function>PQerrorMessage</function> result will
990 include a trailing newline. The caller should not free the result
991 directly. It will be freed when the associated <structname>PGconn</>
992 handle is passed to <function>PQfinish</function>. The result string
993 should not be expected to remain the same across operations on the
994 <literal>PGconn</> structure.
1000 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1003 Obtains the file descriptor number of the connection socket to
1004 the server. A valid descriptor will be greater than or equal
1005 to 0; a result of -1 indicates that no server connection is
1006 currently open. (This will not change during normal operation,
1007 but could change during connection setup or reset.)
1009 int PQsocket(const PGconn *conn);
1016 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1019 Returns the process <acronym>ID</acronym>
1020 (PID)<indexterm><primary>PID</><secondary>determining PID of
1021 server process</><tertiary>in libpq</></> of the backend server
1022 process handling this connection.
1024 int PQbackendPID(const PGconn *conn);
1029 The backend <acronym>PID</acronym> is useful for debugging
1030 purposes and for comparison to <command>NOTIFY</command>
1031 messages (which include the <acronym>PID</acronym> of the
1032 notifying backend process). Note that the
1033 <acronym>PID</acronym> belongs to a process executing on the
1034 database server host, not the local host!
1040 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1043 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1044 Returns the SSL structure used in the connection, or null
1045 if SSL is not in use.
1047 SSL *PQgetssl(const PGconn *conn);
1052 This structure can be used to verify encryption levels, check
1053 server certificates, and more. Refer to the <productname>OpenSSL</> documentation
1054 for information about this structure.
1057 You must define <symbol>USE_SSL</symbol> in order to get the
1058 correct prototype for this function. Doing this will also
1059 automatically include <filename>ssl.h</filename> from <productname>OpenSSL</productname>.
1069 <sect1 id="libpq-exec">
1070 <title>Command Execution Functions</title>
1073 Once a connection to a database server has been successfully
1074 established, the functions described here are used to perform
1075 SQL queries and commands.
1078 <sect2 id="libpq-exec-main">
1079 <title>Main Functions</title>
1084 <term><function>PQexec</function><indexterm><primary>PQexec</></></term>
1087 Submits a command to the server
1088 and waits for the result.
1090 PGresult *PQexec(PGconn *conn, const char *command);
1095 Returns a <structname>PGresult</structname> pointer or possibly a null pointer.
1096 A non-null pointer will generally be returned except in
1097 out-of-memory conditions or serious errors such as inability
1098 to send the command to the server.
1099 If a null pointer is returned, it
1100 should be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result.
1101 Use <function>PQerrorMessage</function> to get more information
1108 It is allowed to include multiple SQL commands (separated by semicolons) in
1109 the command string. Multiple queries sent in a single <function>PQexec</>
1110 call are processed in a single transaction, unless there are explicit
1111 <command>BEGIN</command>/<command>COMMIT</command> commands included in the query string to divide it into multiple
1112 transactions. Note however that the returned <structname>PGresult</structname>
1113 structure describes only the result of the last command executed from the
1114 string. Should one of the commands fail, processing of the string stops with
1115 it and the returned <structname>PGresult</structname> describes the error
1122 <term><function>PQexecParams</function><indexterm><primary>PQexecParams</></></term>
1125 Submits a command to the server and waits for the result,
1126 with the ability to pass parameters separately from the SQL
1129 PGresult *PQexecParams(PGconn *conn,
1130 const char *command,
1132 const Oid *paramTypes,
1133 const char * const *paramValues,
1134 const int *paramLengths,
1135 const int *paramFormats,
1141 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1142 functionality: parameter values can be specified separately from the command
1143 string proper, and query results can be requested in either text or binary
1144 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1145 connections; it will fail when using protocol 2.0.
1149 If parameters are used, they are referred to in the command string
1150 as <literal>$1</>, <literal>$2</>, etc.
1151 <parameter>nParams</> is the number of parameters supplied; it is the length
1152 of the arrays <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1153 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1154 array pointers may be <symbol>NULL</symbol> when <parameter>nParams</> is zero.)
1155 <parameter>paramTypes[]</> specifies, by OID, the data types to be assigned to
1156 the parameter symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>, or any particular
1157 element in the array is zero, the server assigns a data type to the parameter
1158 symbol in the same way it would do for an untyped literal string.
1159 <parameter>paramValues[]</> specifies the actual values of the parameters.
1160 A null pointer in this array means the corresponding parameter is null;
1161 otherwise the pointer points to a zero-terminated text string (for text
1162 format) or binary data in the format expected by the server (for binary
1164 <parameter>paramLengths[]</> specifies the actual data lengths of
1165 binary-format parameters. It is ignored for null parameters and text-format
1166 parameters. The array pointer may be null when there are no binary
1168 <parameter>paramFormats[]</> specifies whether parameters are text (put a zero
1169 in the array) or binary (put a one in the array). If the array pointer is
1170 null then all parameters are presumed to be text.
1171 <parameter>resultFormat</> is zero to obtain results in text format, or one to
1172 obtain results in binary format. (There is not currently a provision to
1173 obtain different result columns in different formats, although that is
1174 possible in the underlying protocol.)
1180 The primary advantage of <function>PQexecParams</> over <function>PQexec</>
1181 is that parameter values may be separated from the command string, thus
1182 avoiding the need for tedious and error-prone quoting and escaping.
1184 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most one SQL
1185 command in the given string. (There can be semicolons in it, but not more
1186 than one nonempty command.) This is a limitation of the underlying protocol,
1187 but has some usefulness as an extra defense against SQL-injection attacks.
1193 <term><function>PQprepare</function><indexterm><primary>PQprepare</></></term>
1196 Submits a request to create a prepared statement with the
1197 given parameters, and waits for completion.
1199 PGresult *PQprepare(PGconn *conn,
1200 const char *stmtName,
1203 const Oid *paramTypes);
1208 <function>PQprepare</> creates a prepared statement for later execution with
1209 <function>PQexecPrepared</>.
1210 This feature allows commands
1211 that will be used repeatedly to be parsed and planned just once, rather
1212 than each time they are executed.
1213 <function>PQprepare</> is supported only in protocol 3.0 and later
1214 connections; it will fail when using protocol 2.0.
1218 The function creates a prepared statement named <parameter>stmtName</>
1219 from the <parameter>query</> string, which must contain a single SQL command.
1220 <parameter>stmtName</> may be <literal>""</> to create an unnamed statement,
1221 in which case any pre-existing unnamed statement is automatically replaced;
1222 otherwise it is an error if the statement name is already defined in the
1224 If any parameters are used, they are referred
1225 to in the query as <literal>$1</>, <literal>$2</>, etc.
1226 <parameter>nParams</> is the number of parameters for which types are
1227 pre-specified in the array <parameter>paramTypes[]</>. (The array pointer
1228 may be <symbol>NULL</symbol> when <parameter>nParams</> is zero.)
1229 <parameter>paramTypes[]</> specifies, by OID, the data types to be assigned to
1230 the parameter symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1231 or any particular element in the array is zero, the server assigns a data type
1232 to the parameter symbol in the same way it would do for an untyped literal
1233 string. Also, the query may use parameter symbols with numbers higher than
1234 <parameter>nParams</>; data types will be inferred for these symbols as
1239 As with <function>PQexec</>, the result is normally a
1240 <structname>PGresult</structname> object whose contents indicate server-side
1241 success or failure. A null result indicates out-of-memory or inability to
1242 send the command at all.
1243 Use <function>PQerrorMessage</function> to get more information
1248 At present, there is no way to determine the actual data type inferred for
1249 any parameters whose types are not specified in <parameter>paramTypes[]</>.
1250 This is a <application>libpq</> omission that will probably be rectified
1251 in a future release.
1257 Prepared statements for use with <function>PQexecPrepared</> can also be
1258 created by executing SQL <command>PREPARE</> statements. (But
1259 <function>PQprepare</> is more flexible since it does not require
1260 parameter types to be pre-specified.) Also, although there is no
1261 <application>libpq</> function for deleting a prepared statement,
1262 the SQL <command>DEALLOCATE</> statement can be used for that purpose.
1268 <term><function>PQexecPrepared</function><indexterm><primary>PQexecPrepared</></></term>
1271 Sends a request to execute a prepared statement with given
1272 parameters, and waits for the result.
1274 PGresult *PQexecPrepared(PGconn *conn,
1275 const char *stmtName,
1277 const char * const *paramValues,
1278 const int *paramLengths,
1279 const int *paramFormats,
1285 <function>PQexecPrepared</> is like <function>PQexecParams</>, but the
1286 command to be executed is specified by naming a previously-prepared
1287 statement, instead of giving a query string.
1288 This feature allows commands
1289 that will be used repeatedly to be parsed and planned just once, rather
1290 than each time they are executed.
1291 The statement must have been prepared previously in the current session.
1292 <function>PQexecPrepared</> is supported only in protocol 3.0 and later
1293 connections; it will fail when using protocol 2.0.
1297 The parameters are identical to <function>PQexecParams</>, except that the
1298 name of a prepared statement is given instead of a query string, and the
1299 <parameter>paramTypes[]</> parameter is not present (it is not needed since
1300 the prepared statement's parameter types were determined when it was created).
1309 <structname>PGresult</structname><indexterm><primary>PGresult</></>
1310 structure encapsulates the result returned by the server.
1311 <application>libpq</application> application programmers should be
1312 careful to maintain the <structname>PGresult</structname> abstraction.
1313 Use the accessor functions below to get at the contents of
1314 <structname>PGresult</structname>. Avoid directly referencing the
1315 fields of the <structname>PGresult</structname> structure because they
1316 are subject to change in the future.
1320 <term><function>PQresultStatus</function><indexterm><primary>PQresultStatus</></></term>
1323 Returns the result status of the command.
1325 ExecStatusType PQresultStatus(const PGresult *res);
1330 <function>PQresultStatus</function> can return one of the following values:
1334 <term><literal>PGRES_EMPTY_QUERY</literal></term>
1336 <para>The string sent to the server was empty.</para>
1341 <term><literal>PGRES_COMMAND_OK</literal></term>
1343 <para>Successful completion of a command returning no data.</para>
1348 <term><literal>PGRES_TUPLES_OK</literal></term>
1350 <para>Successful completion of a command returning data (such as
1351 a <command>SELECT</> or <command>SHOW</>).</para>
1356 <term><literal>PGRES_COPY_OUT</literal></term>
1358 <para>Copy Out (from server) data transfer started.</para>
1363 <term><literal>PGRES_COPY_IN</literal></term>
1365 <para>Copy In (to server) data transfer started.</para>
1370 <term><literal>PGRES_BAD_RESPONSE</literal></term>
1372 <para>The server's response was not understood.</para>
1377 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
1379 <para>A nonfatal error (a notice or warning) occurred.</para>
1384 <term><literal>PGRES_FATAL_ERROR</literal></term>
1386 <para>A fatal error occurred.</para>
1391 If the result status is <literal>PGRES_TUPLES_OK</literal>, then the
1392 functions described below can be used to retrieve the rows returned by
1393 the query. Note that a <command>SELECT</command> command that happens
1394 to retrieve zero rows still shows <literal>PGRES_TUPLES_OK</literal>.
1395 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
1396 return rows (<command>INSERT</command>, <command>UPDATE</command>,
1397 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> may indicate
1398 a bug in the client software.
1402 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will never be
1403 returned directly by <function>PQexec</function> or other query
1404 execution functions; results of this kind are instead passed to the notice
1405 processor (see <xref linkend="libpq-notice-processing">).
1411 <term><function>PQresStatus</function><indexterm><primary>PQresStatus</></></term>
1414 Converts the enumerated type returned by <function>PQresultStatus</> into
1415 a string constant describing the status code. The caller should not
1418 char *PQresStatus(ExecStatusType status);
1425 <term><function>PQresultErrorMessage</function><indexterm><primary>PQresultErrorMessage</></></term>
1428 Returns the error message associated with the command, or an empty string
1429 if there was no error.
1431 char *PQresultErrorMessage(const PGresult *res);
1433 If there was an error, the returned string will include a trailing newline.
1434 The caller should not free the result directly. It will be freed when the
1435 associated <structname>PGresult</> handle is passed to
1436 <function>PQclear</function>.
1440 Immediately following a <function>PQexec</function> or <function>PQgetResult</function>
1441 call, <function>PQerrorMessage</function> (on the connection) will return the same
1442 string as <function>PQresultErrorMessage</function> (on the result). However, a
1443 <structname>PGresult</structname> will retain its error message
1444 until destroyed, whereas the connection's error message will change when
1445 subsequent operations are done. Use <function>PQresultErrorMessage</function> when you want to
1446 know the status associated with a particular <structname>PGresult</structname>; use <function>PQerrorMessage</function>
1447 when you want to know the status from the latest operation on the connection.
1453 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
1456 Returns an individual field of an error report.
1458 char *PQresultErrorField(const PGresult *res, int fieldcode);
1460 <parameter>fieldcode</> is an error field identifier; see the symbols
1461 listed below. <symbol>NULL</symbol> is returned if the
1462 <structname>PGresult</structname> is not an error or warning result,
1463 or does not include the specified field. Field values will normally
1464 not include a trailing newline. The caller should not free the
1465 result directly. It will be freed when the
1466 associated <structname>PGresult</> handle is passed to
1467 <function>PQclear</function>.
1471 The following field codes are available:
1475 <term><symbol>PG_DIAG_SEVERITY</></term>
1478 The severity; the field contents are <literal>ERROR</>,
1479 <literal>FATAL</>, or <literal>PANIC</> (in an error message), or
1480 <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
1481 <literal>INFO</>, or <literal>LOG</> (in a notice message), or a
1482 localized translation of one of these. Always present.
1489 <primary>error codes</primary>
1490 <secondary>libpq</secondary>
1492 <term><symbol>PG_DIAG_SQLSTATE</>
1496 The SQLSTATE code for the error. The SQLSTATE code identifies the type
1497 of error that has occurred; it can be used by front-end applications
1498 to perform specific operations (such as error handling) in response to
1499 a particular database error. For a list of the possible SQLSTATE
1500 codes, see <xref linkend="errcodes-appendix">. This field is not
1501 localizable, and is always present.
1507 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
1510 The primary human-readable error message (typically one line). Always
1517 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
1520 Detail: an optional secondary error message carrying more detail about
1521 the problem. May run to multiple lines.
1527 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
1530 Hint: an optional suggestion what to do about the problem. This is
1531 intended to differ from detail in that it offers advice (potentially
1532 inappropriate) rather than hard facts. May run to multiple lines.
1538 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
1541 A string containing a decimal integer indicating an error cursor
1542 position as an index into the original statement string. The first
1543 character has index 1, and positions are measured in characters not
1550 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
1553 This is defined the same as the <symbol>PG_DIAG_STATEMENT_POSITION</>
1554 field, but it is used when the cursor position refers to an internally
1555 generated command rather than the one submitted by the client.
1556 The <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when this field
1563 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
1566 The text of a failed internally-generated command.
1567 This could be, for example, a SQL query issued by a PL/pgSQL function.
1573 <term><symbol>PG_DIAG_CONTEXT</></term>
1576 An indication of the context in which the error occurred.
1577 Presently this includes a call stack traceback of active
1578 procedural language functions and internally-generated queries.
1579 The trace is one entry per line, most recent first.
1585 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
1588 The file name of the source-code location where the error was
1595 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
1598 The line number of the source-code location where the error was
1605 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
1608 The name of the source-code function reporting the error.
1616 The client is responsible for formatting displayed information to meet
1617 its needs; in particular it should break long lines as needed.
1618 Newline characters appearing in the error message fields should be
1619 treated as paragraph breaks, not line breaks.
1623 Errors generated internally by <application>libpq</application> will
1624 have severity and primary message, but typically no other fields.
1625 Errors returned by a pre-3.0-protocol server will include severity and
1626 primary message, and sometimes a detail message, but no other fields.
1630 Note that error fields are only available from
1631 <structname>PGresult</structname> objects, not
1632 <structname>PGconn</structname> objects; there is no
1633 <function>PQerrorField</function> function.
1639 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
1642 Frees the storage associated with a <structname>PGresult</structname>.
1643 Every command result should be freed via <function>PQclear</function> when
1644 it is no longer needed.
1646 void PQclear(PGresult *res);
1651 You can keep a <structname>PGresult</structname> object around for as long as you
1652 need it; it does not go away when you issue a new command,
1653 nor even if you close the connection. To get rid of it,
1654 you must call <function>PQclear</function>. Failure to do this will
1655 result in memory leaks in your application.
1661 <term><function>PQmakeEmptyPGresult</function><indexterm><primary>PQmakeEmptyPGresult</></></term>
1664 Constructs an empty <structname>PGresult</structname> object with the given status.
1666 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
1671 This is <application>libpq</>'s internal function to allocate and
1672 initialize an empty <structname>PGresult</structname> object. This
1673 function returns NULL if memory could not be allocated. It is exported
1674 because some applications find it useful to generate result objects
1675 (particularly objects with error status) themselves. If
1676 <parameter>conn</parameter> is not null and <parameter>status</>
1677 indicates an error, the current error message of the specified
1678 connection is copied into the <structname>PGresult</structname>. Note
1679 that <function>PQclear</function> should eventually be called on the
1680 object, just as with a <structname>PGresult</structname> returned by
1681 <application>libpq</application> itself.
1689 <sect2 id="libpq-exec-select-info">
1690 <title>Retrieving Query Result Information</title>
1693 These functions are used to extract information from a
1694 <structname>PGresult</structname> object that represents a successful
1695 query result (that is, one that has status
1696 <literal>PGRES_TUPLES_OK</literal>). For objects with other status
1697 values they will act as though the result has zero rows and zero columns.
1702 <term><function>PQntuples</function><indexterm><primary>PQntuples</></></term>
1705 Returns the number of rows (tuples)
1706 in the query result.
1708 int PQntuples(const PGresult *res);
1715 <term><function>PQnfields</function><indexterm><primary>PQnfields</></></term>
1718 Returns the number of columns (fields)
1719 in each row of the query result.
1721 int PQnfields(const PGresult *res);
1728 <term><function>PQfname</function><indexterm><primary>PQfname</></></term>
1731 Returns the column name associated with the given column number.
1732 Column numbers start at 0. The caller should not free the result
1733 directly. It will be freed when the associated <structname>PGresult</>
1734 handle is passed to <function>PQclear</function>.
1736 char *PQfname(const PGresult *res,
1742 <symbol>NULL</symbol> is returned if the column number is out of range.
1748 <term><function>PQfnumber</function><indexterm><primary>PQfnumber</></></term>
1751 Returns the column number associated with the given column name.
1753 int PQfnumber(const PGresult *res,
1754 const char *column_name);
1759 -1 is returned if the given name does not match any column.
1763 The given name is treated like an identifier in an SQL command,
1764 that is, it is downcased unless double-quoted. For example,
1765 given a query result generated from the SQL command
1767 select 1 as FOO, 2 as "BAR";
1769 we would have the results:
1771 PQfname(res, 0) <lineannotation>foo</lineannotation>
1772 PQfname(res, 1) <lineannotation>BAR</lineannotation>
1773 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
1774 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
1775 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
1776 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
1783 <term><function>PQftable</function><indexterm><primary>PQftable</></></term>
1786 Returns the OID of the table from which the given column was fetched.
1787 Column numbers start at 0.
1789 Oid PQftable(const PGresult *res,
1795 <literal>InvalidOid</> is returned if the column number is out of range,
1796 or if the specified column is not a simple reference to a table column,
1797 or when using pre-3.0 protocol.
1798 You can query the system table <literal>pg_class</literal> to determine
1799 exactly which table is referenced.
1803 The type <type>Oid</type> and the constant
1804 <literal>InvalidOid</literal> will be defined when you include
1805 the <application>libpq</application> header file. They will
1806 both be some integer type.
1812 <term><function>PQftablecol</function><indexterm><primary>PQftablecol</></></term>
1815 Returns the column number (within its table) of the column making up
1816 the specified query result column.
1817 Query-result column numbers start at 0, but table columns have nonzero
1820 int PQftablecol(const PGresult *res,
1826 Zero is returned if the column number is out of range,
1827 or if the specified column is not a simple reference to a table column,
1828 or when using pre-3.0 protocol.
1834 <term><function>PQfformat</function><indexterm><primary>PQfformat</></></term>
1837 Returns the format code indicating the format of the given column.
1838 Column numbers start at 0.
1840 int PQfformat(const PGresult *res,
1846 Format code zero indicates textual data representation, while format
1847 code one indicates binary representation. (Other codes are reserved
1848 for future definition.)
1854 <term><function>PQftype</function><indexterm><primary>PQftype</></></term>
1857 Returns the data type associated with the
1858 given column number. The integer returned is the
1859 internal OID number of the type. Column numbers start
1862 Oid PQftype(const PGresult *res,
1868 You can query the system table <literal>pg_type</literal> to obtain
1869 the names and properties of the various data types. The <acronym>OID</acronym>s
1870 of the built-in data types are defined in the file <filename>src/include/catalog/pg_type.h</filename>
1877 <term><function>PQfmod</function><indexterm><primary>PQfmod</></></term>
1880 Returns the type modifier of the column
1881 associated with the given column number.
1882 Column numbers start at 0.
1884 int PQfmod(const PGresult *res,
1890 The interpretation of modifier values is type-specific; they typically
1891 indicate precision or size limits. The value -1 is used to indicate
1892 <quote>no information available</>. Most data types do not use modifiers,
1893 in which case the value is always -1.
1899 <term><function>PQfsize</function><indexterm><primary>PQfsize</></></term>
1902 Returns the size in bytes of the column
1903 associated with the given column number.
1904 Column numbers start at 0.
1906 int PQfsize(const PGresult *res,
1912 <function>PQfsize</> returns the space allocated for this column in a database
1913 row, in other words the size of the server's internal representation
1914 of the data type. (Accordingly, it is not really very useful to clients.)
1915 A negative value indicates the data type is variable-length.
1921 <term><function>PQbinaryTuples</function><indexterm><primary>PQbinaryTuples</></></term>
1924 Returns 1 if the <structname>PGresult</> contains binary data
1925 and 0 if it contains text data.
1927 int PQbinaryTuples(const PGresult *res);
1932 This function is deprecated (except for its use in connection with
1933 <command>COPY</>), because it is possible for a single
1934 <structname>PGresult</>
1935 to contain text data in some columns and binary data in others.
1936 <function>PQfformat</> is preferred. <function>PQbinaryTuples</>
1937 returns 1 only if all columns of the result are binary (format 1).
1943 <term><function>PQgetvalue</function><indexterm><primary>PQgetvalue</></></term>
1946 Returns a single field value of one row of a
1947 <structname>PGresult</structname>. Row and column numbers
1948 start at 0. The caller should not free the result
1949 directly. It will be freed when the associated
1950 <structname>PGresult</> handle is passed to
1951 <function>PQclear</function>.
1953 char *PQgetvalue(const PGresult *res,
1960 For data in text format, the value returned by <function>PQgetvalue</function>
1961 is a null-terminated character string representation
1962 of the field value. For data in binary format, the value is in the binary
1963 representation determined by the data type's <function>typsend</> and
1964 <function>typreceive</> functions. (The value is actually followed by
1965 a zero byte in this case too, but that is not ordinarily useful, since
1966 the value is likely to contain embedded nulls.)
1970 An empty string is returned if the field value is null. See
1971 <function>PQgetisnull</> to distinguish null values from empty-string values.
1976 returned by <function>PQgetvalue</function> points to storage that is
1977 part of the <structname>PGresult</structname> structure. One should not modify the data it points to,
1978 and one must explicitly
1979 copy the data into other storage if it is to
1980 be used past the lifetime of the <structname>PGresult</structname> structure itself.
1986 <term><function>PQgetisnull</function><indexterm><primary>PQgetisnull</></></term>
1987 <indexterm><primary>null value</><secondary sortas="libpq">in libpq</></indexterm><listitem>
1989 Tests a field for a null value.
1990 Row and column numbers start at 0.
1992 int PQgetisnull(const PGresult *res,
1999 This function returns 1 if the field is null and 0 if
2000 it contains a non-null value. (Note that <function>PQgetvalue</function>
2001 will return an empty string, not a null pointer, for a null field.)
2007 <term><function>PQgetlength</function><indexterm><primary>PQgetlength</></></term>
2010 Returns the actual length of a field value in bytes.
2011 Row and column numbers start at 0.
2013 int PQgetlength(const PGresult *res,
2020 This is the actual data length for the particular data value, that is, the
2021 size of the object pointed to by <function>PQgetvalue</function>. For text
2022 data format this is the same as <function>strlen()</>. For binary format
2023 this is essential information. Note that one should <emphasis>not</> rely
2024 on <function>PQfsize</function> to obtain the actual data length.
2030 <term><function>PQprint</function><indexterm><primary>PQprint</></></term>
2033 Prints out all the rows and, optionally, the
2034 column names to the specified output stream.
2036 void PQprint(FILE *fout, /* output stream */
2037 const PGresult *res,
2038 const PQprintOpt *po);
2041 pqbool header; /* print output field headings and row count */
2042 pqbool align; /* fill align the fields */
2043 pqbool standard; /* old brain dead format */
2044 pqbool html3; /* output HTML tables */
2045 pqbool expanded; /* expand tables */
2046 pqbool pager; /* use pager for output if needed */
2047 char *fieldSep; /* field separator */
2048 char *tableOpt; /* attributes for HTML table element */
2049 char *caption; /* HTML table caption */
2050 char **fieldName; /* null-terminated array of replacement field names */
2056 This function was formerly used by <application>psql</application>
2057 to print query results, but this is no longer the case. Note that it
2058 assumes all the data is in text format.
2065 <sect2 id="libpq-exec-nonselect">
2066 <title>Retrieving Result Information for Other Commands</title>
2069 These functions are used to extract information from
2070 <structname>PGresult</structname> objects that are not <command>SELECT</>
2076 <term><function>PQcmdStatus</function><indexterm><primary>PQcmdStatus</></></term>
2079 Returns the command status tag from the SQL command that
2080 generated the <structname>PGresult</structname>.
2082 char *PQcmdStatus(PGresult *res);
2086 Commonly this is just the name of the command, but it may include additional
2087 data such as the number of rows processed. The caller should
2088 not free the result directly. It will be freed when the
2089 associated <structname>PGresult</> handle is passed to
2090 <function>PQclear</function>.
2096 <term><function>PQcmdTuples</function><indexterm><primary>PQcmdTuples</></></term>
2099 Returns the number of rows affected by the SQL command.
2101 char *PQcmdTuples(PGresult *res);
2106 This function returns a string containing the number of rows
2107 affected by the <acronym>SQL</> statement that generated the
2108 <structname>PGresult</>. This function can only be used
2109 following the execution of an <command>INSERT</>,
2110 <command>UPDATE</>, <command>DELETE</>, <command>MOVE</>, or
2111 <command>FETCH</> statement, or an <command>EXECUTE</> of a
2112 prepared query that contains a <command>INSERT</>,
2113 <command>UPDATE</>, or <command>DELETE</> statement. If the
2114 command that generated the <structname>PGresult</> was
2115 anything else, <function>PQcmdTuples</> returns the empty
2116 string. The caller should not free the return value
2117 directly. It will be freed when the associated
2118 <structname>PGresult</> handle is passed to
2119 <function>PQclear</function>.
2125 <term><function>PQoidValue</function><indexterm><primary>PQoidValue</></></term>
2128 Returns the OID<indexterm><primary>OID</><secondary>in
2129 libpq</></> of the inserted row, if the <acronym>SQL</>
2130 command was an <command>INSERT</> that inserted exactly one
2131 row into a table that has OIDs, or a <command>EXECUTE</> of
2132 a prepared query containing a suitable <command>INSERT</>
2133 statement. Otherwise, this function returns
2134 <literal>InvalidOid</literal>. This function will also
2135 return <literal>InvalidOid</literal> if the table affected
2136 by the <command>INSERT</> statement does not contain OIDs.
2138 Oid PQoidValue(const PGresult *res);
2145 <term><function>PQoidStatus</function><indexterm><primary>PQoidStatus</></></term>
2148 Returns a string with the OID of the inserted row, if the
2149 <acronym>SQL</acronym> command was an
2150 <command>INSERT</command> that inserted exactly one row, or
2151 a <command>EXECUTE</command> of a prepared statement
2152 consisting of a suitable <command>INSERT</command>. (The string will be
2153 <literal>0</> if the <command>INSERT</command> did not
2154 insert exactly one row, or if the target table does not have
2155 OIDs.) If the command was not an <command>INSERT</command>,
2156 returns an empty string.
2158 char *PQoidStatus(const PGresult *res);
2163 This function is deprecated in favor of <function>PQoidValue</function>.
2164 It is not thread-safe.
2172 <sect2 id="libpq-exec-escape-string">
2173 <title>Escaping Strings for Inclusion in SQL Commands</title>
2175 <indexterm zone="libpq-exec-escape-string"><primary>PQescapeString</></>
2176 <indexterm zone="libpq-exec-escape-string"><primary>escaping strings</></>
2179 <function>PQescapeString</function> escapes a string for use within an SQL
2180 command. This is useful when inserting data values as literal constants
2181 in SQL commands. Certain characters (such as quotes and backslashes) must
2182 be escaped to prevent them from being interpreted specially by the SQL parser.
2183 <function>PQescapeString</> performs this operation.
2188 It is especially important to do proper escaping when handling strings that
2189 were received from an untrustworthy source. Otherwise there is a security
2190 risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
2191 SQL commands are fed to your database.
2196 Note that it is not necessary nor correct to do escaping when a data
2197 value is passed as a separate parameter in <function>PQexecParams</> or
2198 its sibling routines.
2201 size_t PQescapeString (char *to, const char *from, size_t length);
2206 The parameter <parameter>from</> points to the first character of the string
2207 that is to be escaped, and the <parameter>length</> parameter gives the
2208 number of characters in this string. A terminating zero byte is not
2209 required, and should not be counted in <parameter>length</>. (If
2210 a terminating zero byte is found before <parameter>length</> bytes are
2211 processed, <function>PQescapeString</> stops at the zero; the behavior
2212 is thus rather like <function>strncpy</>.)
2213 <parameter>to</> shall point to a
2214 buffer that is able to hold at least one more character than twice
2215 the value of <parameter>length</>, otherwise the behavior is
2216 undefined. A call to <function>PQescapeString</> writes an escaped
2217 version of the <parameter>from</> string to the <parameter>to</>
2218 buffer, replacing special characters so that they cannot cause any
2219 harm, and adding a terminating zero byte. The single quotes that
2220 must surround <productname>PostgreSQL</> string literals are not
2221 included in the result string; they should be provided in the SQL
2222 command that the result is inserted into.
2225 <function>PQescapeString</> returns the number of characters written
2226 to <parameter>to</>, not including the terminating zero byte.
2229 Behavior is undefined if the <parameter>to</> and <parameter>from</>
2235 <sect2 id="libpq-exec-escape-bytea">
2236 <title>Escaping Binary Strings for Inclusion in SQL Commands</title>
2238 <indexterm zone="libpq-exec-escape-bytea">
2240 <secondary sortas="libpq">in libpq</>
2245 <term><function>PQescapeBytea</function><indexterm><primary>PQescapeBytea</></></term>
2248 Escapes binary data for use within an SQL command with the type
2249 <type>bytea</type>. As with <function>PQescapeString</function>,
2250 this is only used when inserting data directly into an SQL command string.
2252 unsigned char *PQescapeBytea(const unsigned char *from,
2259 Certain byte values <emphasis>must</emphasis> be escaped (but all
2260 byte values <emphasis>can</emphasis> be escaped) when used as part
2261 of a <type>bytea</type> literal in an <acronym>SQL</acronym>
2262 statement. In general, to escape a byte, it is converted into the
2263 three digit octal number equal to the octet value, and preceded by
2264 two backslashes. The single quote (<literal>'</>) and backslash
2265 (<literal>\</>) characters have special alternative escape
2266 sequences. See <xref linkend="datatype-binary"> for more
2267 information. <function>PQescapeBytea</function> performs this
2268 operation, escaping only the minimally required bytes.
2272 The <parameter>from</parameter> parameter points to the first
2273 byte of the string that is to be escaped, and the
2274 <parameter>from_length</parameter> parameter gives the number of
2275 bytes in this binary string. (A terminating zero byte is
2276 neither necessary nor counted.) The <parameter>to_length</parameter>
2277 parameter points to a variable that will hold the resultant
2278 escaped string length. The result string length includes the terminating
2279 zero byte of the result.
2283 <function>PQescapeBytea</> returns an escaped version of the
2284 <parameter>from</parameter> parameter binary string in memory
2285 allocated with <function>malloc()</> (a null pointer is returned if
2286 memory could not be allocated). This memory must be freed using
2287 <function>PQfreemem</> when the result is no longer needed. The
2288 return string has all special characters replaced so that they can
2289 be properly processed by the <productname>PostgreSQL</productname>
2290 string literal parser, and the <type>bytea</type> input function. A
2291 terminating zero byte is also added. The single quotes that must
2292 surround <productname>PostgreSQL</productname> string literals are
2293 not part of the result string.
2299 <term><function>PQunescapeBytea</function><indexterm><primary>PQunescapeBytea</></></term>
2302 Converts an escaped string representation of binary data into binary
2303 data — the reverse of <function>PQescapeBytea</function>.
2304 This is needed when retrieving <type>bytea</type> data in text format,
2305 but not when retrieving it in binary format.
2308 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
2313 The <parameter>from</parameter> parameter points to an escaped string
2314 such as might be returned by <function>PQgetvalue</function> when applied to a
2315 <type>bytea</type> column. <function>PQunescapeBytea</function> converts
2316 this string representation into its binary representation.
2317 It returns a pointer to a buffer allocated with
2318 <function>malloc()</function>, or null on error, and puts the size of
2319 the buffer in <parameter>to_length</parameter>. The result must be
2320 freed using <function>PQfreemem</> when it is no longer needed.
2326 <term><function>PQfreemem</function><indexterm><primary>PQfreemem</></></term>
2329 Frees memory allocated by <application>libpq</>.
2331 void PQfreemem(void *ptr);
2336 Frees memory allocated by <application>libpq</>, particularly
2337 <function>PQescapeBytea</function>,
2338 <function>PQunescapeBytea</function>,
2339 and <function>PQnotifies</function>.
2340 It is needed by Microsoft Windows, which cannot free memory across
2341 DLLs, unless multithreaded DLLs (<option>/MD</option> in VC6) are used.
2342 On other platforms, this function is the same as the standard library function <function>free()</>.
2351 <sect1 id="libpq-async">
2352 <title>Asynchronous Command Processing</title>
2354 <indexterm zone="libpq-async"><primary>nonblocking connection</></>
2357 The <function>PQexec</function> function is adequate for submitting commands in
2359 applications. It has a couple of deficiencies, however, that can be of importance to some users:
2364 <function>PQexec</function> waits for the command to be completed. The application may have other
2365 work to do (such as maintaining a user interface), in which case it won't
2366 want to block waiting for the response.
2371 Since the execution of the client application is suspended while it
2372 waits for the result, it is hard for the application to decide that it
2373 would like to try to cancel the ongoing command. (It can be done from
2374 a signal handler, but not otherwise.)
2379 <function>PQexec</function> can return only one <structname>PGresult</structname> structure. If the submitted command
2380 string contains multiple <acronym>SQL</acronym> commands, all but the last <structname>PGresult</structname> are
2381 discarded by <function>PQexec</function>.
2388 Applications that do not like these limitations can instead use the
2389 underlying functions that <function>PQexec</function> is built from:
2390 <function>PQsendQuery</function> and <function>PQgetResult</function>.
2392 <function>PQsendQueryParams</function>,
2393 <function>PQsendPrepare</function>, and
2394 <function>PQsendQueryPrepared</function>,
2395 which can be used with <function>PQgetResult</function> to duplicate the
2397 <function>PQexecParams</function>,
2398 <function>PQprepare</function>, and
2399 <function>PQexecPrepared</function>
2404 <term><function>PQsendQuery</function><indexterm><primary>PQsendQuery</></></term>
2407 Submits a command to the server without
2408 waiting for the result(s). 1 is returned if the command was
2409 successfully dispatched and 0 if not (in which case, use
2410 <function>PQerrorMessage</> to get more information about the failure).
2412 int PQsendQuery(PGconn *conn, const char *command);
2415 After successfully calling <function>PQsendQuery</function>, call
2416 <function>PQgetResult</function> one or more
2417 times to obtain the results. <function>PQsendQuery</function> may not be called
2418 again (on the same connection) until <function>PQgetResult</function> has returned a null pointer,
2419 indicating that the command is done.
2425 <term><function>PQsendQueryParams</function><indexterm><primary>PQsendQueryParams</></></term>
2428 Submits a command and separate parameters to the server without
2429 waiting for the result(s).
2431 int PQsendQueryParams(PGconn *conn,
2432 const char *command,
2434 const Oid *paramTypes,
2435 const char * const *paramValues,
2436 const int *paramLengths,
2437 const int *paramFormats,
2441 This is equivalent to <function>PQsendQuery</function> except that
2442 query parameters can be specified separately from the query string.
2443 The function's parameters are handled identically to
2444 <function>PQexecParams</function>. Like
2445 <function>PQexecParams</function>, it will not work on 2.0-protocol
2446 connections, and it allows only one command in the query string.
2452 <term><function>PQsendPrepare</><indexterm><primary>PQsendPrepare</></></term>
2455 Sends a request to create a prepared statement with the given
2456 parameters, without waiting for completion.
2458 int PQsendPrepare(PGconn *conn,
2459 const char *stmtName,
2462 const Oid *paramTypes);
2465 This is an asynchronous version of <function>PQprepare</>: it
2466 returns 1 if it was able to dispatch the request, and 0 if not.
2467 After a successful call, call <function>PQgetResult</function>
2468 to determine whether the server successfully created the prepared
2470 The function's parameters are handled identically to
2471 <function>PQprepare</function>. Like
2472 <function>PQprepare</function>, it will not work on 2.0-protocol
2479 <term><function>PQsendQueryPrepared</function><indexterm><primary>PQsendQueryPrepared</></></term>
2482 Sends a request to execute a prepared statement with given
2483 parameters, without waiting for the result(s).
2485 int PQsendQueryPrepared(PGconn *conn,
2486 const char *stmtName,
2488 const char * const *paramValues,
2489 const int *paramLengths,
2490 const int *paramFormats,
2494 This is similar to <function>PQsendQueryParams</function>, but the
2495 command to be executed is specified by naming a previously-prepared
2496 statement, instead of giving a query string.
2497 The function's parameters are handled identically to
2498 <function>PQexecPrepared</function>. Like
2499 <function>PQexecPrepared</function>, it will not work on 2.0-protocol
2506 <term><function>PQgetResult</function><indexterm><primary>PQgetResult</></></term>
2509 Waits for the next result from a prior
2510 <function>PQsendQuery</function>,
2511 <function>PQsendQueryParams</function>,
2512 <function>PQsendPrepare</function>, or
2513 <function>PQsendQueryPrepared</function> call,
2514 and returns it. A null pointer is returned when the command is complete
2515 and there will be no more results.
2517 PGresult *PQgetResult(PGconn *conn);
2522 <function>PQgetResult</function> must be called repeatedly until it returns a null pointer,
2523 indicating that the command is done. (If called when no command is
2524 active, <function>PQgetResult</function> will just return a null pointer at once.)
2525 Each non-null result from <function>PQgetResult</function> should be processed using
2526 the same <structname>PGresult</> accessor functions previously described.
2527 Don't forget to free each result object with <function>PQclear</function> when done with it.
2528 Note that <function>PQgetResult</function> will block only if a command is active and the
2529 necessary response data has not yet been read by <function>PQconsumeInput</function>.
2537 Using <function>PQsendQuery</function> and <function>PQgetResult</function>
2538 solves one of <function>PQexec</function>'s problems:
2539 If a command string contains multiple <acronym>SQL</acronym> commands, the results of those
2540 commands can be obtained individually. (This allows a simple form of
2541 overlapped processing, by the way: the client can be handling the
2542 results of one command while the server is still working on later
2543 queries in the same command string.) However, calling <function>PQgetResult</function> will
2544 still cause the client to block until the server completes the
2545 next <acronym>SQL</acronym> command. This can be avoided by proper use of two
2550 <term><function>PQconsumeInput</function><indexterm><primary>PQconsumeInput</></></term>
2553 If input is available from the server, consume it.
2555 int PQconsumeInput(PGconn *conn);
2560 <function>PQconsumeInput</function> normally returns 1 indicating <quote>no error</quote>,
2561 but returns 0 if there was some kind of trouble (in which case
2562 <function>PQerrorMessage</function> can be consulted). Note that the result
2564 whether any input data was actually collected. After calling
2565 <function>PQconsumeInput</function>, the application may check
2566 <function>PQisBusy</function> and/or <function>PQnotifies</function> to see if
2567 their state has changed.
2570 <function>PQconsumeInput</function> may be called even if the application is not
2571 prepared to deal with a result or notification just yet. The
2572 function will read available data and save it in a buffer, thereby
2573 causing a <function>select()</function> read-ready indication to go away. The
2574 application can thus use <function>PQconsumeInput</function> to clear the
2575 <function>select()</function> condition immediately, and then examine the results at leisure.
2581 <term><function>PQisBusy</function><indexterm><primary>PQisBusy</></></term>
2584 Returns 1 if a command is busy, that is, <function>PQgetResult</function> would block
2585 waiting for input. A 0 return indicates that <function>PQgetResult</function> can
2586 be called with assurance of not blocking.
2588 int PQisBusy(PGconn *conn);
2593 <function>PQisBusy</function> will not itself attempt to read data from the server;
2594 therefore <function>PQconsumeInput</function> must be invoked first, or the busy
2595 state will never end.
2603 A typical application using these functions will have a main loop that uses
2604 <function>select()</function> or <function>poll()</> to wait for all the
2605 conditions that it must
2606 respond to. One of the conditions will be input available from the server,
2607 which in terms of <function>select()</function> means readable data on the file
2608 descriptor identified by <function>PQsocket</function>.
2609 When the main loop detects input ready, it should call
2610 <function>PQconsumeInput</function> to read the input. It can then call
2611 <function>PQisBusy</function>, followed by <function>PQgetResult</function>
2612 if <function>PQisBusy</function> returns false (0). It can also call
2613 <function>PQnotifies</function> to detect <command>NOTIFY</> messages (see <xref linkend="libpq-notify">).
2618 <function>PQsendQuery</function>/<function>PQgetResult</function> can
2619 also attempt to cancel a command that is still being processed by the
2620 server; see <xref linkend="libpq-cancel">. But regardless of the return value
2621 of <function>PQcancel</function>, the application must continue with the
2622 normal result-reading sequence using <function>PQgetResult</function>.
2623 A successful cancellation will simply cause the command to terminate
2624 sooner than it would have otherwise.
2628 By using the functions described above, it is possible to avoid blocking
2629 while waiting for input from the database server. However, it is still
2630 possible that the application will block waiting to send output to the
2631 server. This is relatively uncommon but can happen if very long SQL commands
2632 or data values are sent. (It is much more probable if the application
2633 sends data via <command>COPY IN</command>, however.) To prevent this possibility and achieve
2634 completely nonblocking database operation, the following additional
2635 functions may be used.
2639 <term><function>PQsetnonblocking</function><indexterm><primary>PQsetnonblocking</></></term>
2642 Sets the nonblocking status of the connection.
2644 int PQsetnonblocking(PGconn *conn, int arg);
2649 Sets the state of the connection to nonblocking if
2650 <parameter>arg</parameter> is 1, or
2651 blocking if <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
2654 In the nonblocking state, calls to
2655 <function>PQsendQuery</function>,
2656 <function>PQputline</function>, <function>PQputnbytes</function>,
2657 and <function>PQendcopy</function>
2658 will not block but instead return an error if they need to be called
2662 Note that <function>PQexec</function> does not honor nonblocking mode;
2663 if it is called, it will act in blocking fashion anyway.
2669 <term><function>PQisnonblocking</function><indexterm><primary>PQisnonblocking</></></term>
2672 Returns the blocking status of the database connection.
2674 int PQisnonblocking(const PGconn *conn);
2679 Returns 1 if the connection is set to nonblocking mode and
2686 <term><function>PQflush</function><indexterm><primary>PQflush</></></term>
2689 Attempts to flush any queued output data to the server.
2690 Returns 0 if successful (or if the send queue is empty), -1 if it failed for
2691 some reason, or 1 if it was unable to send all the data in the send queue yet
2692 (this case can only occur if the connection is nonblocking).
2694 int PQflush(PGconn *conn);
2703 After sending any command or data on a nonblocking connection, call
2704 <function>PQflush</function>. If it returns 1, wait for the socket to be
2705 write-ready and call it again; repeat until it returns 0. Once
2706 <function>PQflush</function> returns 0, wait for the socket to be read-ready
2707 and then read the response as described above.
2712 <sect1 id="libpq-cancel">
2713 <title>Cancelling Queries in Progress</title>
2715 <indexterm zone="libpq-cancel"><primary>canceling</><secondary>SQL command</></>
2718 A client application can request cancellation of
2719 a command that is still being processed by the
2720 server, using the functions described in this section.
2724 <term><function>PQgetCancel</function><indexterm><primary>PQgetCancel</></></term>
2727 Creates a data structure containing the information needed to cancel
2728 a command issued through a particular database connection.
2730 PGcancel *PQgetCancel(PGconn *conn);
2735 <function>PQgetCancel</function> creates a
2736 <structname>PGcancel</><indexterm><primary>PGcancel</></> object given
2737 a <structname>PGconn</> connection object. It will return NULL if the
2738 given <parameter>conn</> is NULL or an invalid connection. The
2739 <structname>PGcancel</> object is an opaque structure that is not meant
2740 to be accessed directly by the application; it can only be passed to
2741 <function>PQcancel</function> or <function>PQfreeCancel</function>.
2747 <term><function>PQfreeCancel</function><indexterm><primary>PQfreeCancel</></></term>
2750 Frees a data structure created by <function>PQgetCancel</function>.
2752 void PQfreeCancel(PGcancel *cancel);
2757 <function>PQfreeCancel</function> frees a data object previously created
2758 by <function>PQgetCancel</function>.
2764 <term><function>PQcancel</function><indexterm><primary>PQcancel</></></term>
2767 Requests that the server abandon
2768 processing of the current command.
2770 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
2775 The return value is 1 if the cancel request was successfully
2776 dispatched and 0 if not. If not, <parameter>errbuf</> is filled with an error
2777 message explaining why not. <parameter>errbuf</> must be a char array of size
2778 <parameter>errbufsize</> (the recommended size is 256 bytes).
2782 Successful dispatch is no guarantee that the request will have any effect,
2783 however. If the cancellation is effective, the current command will terminate
2784 early and return an error result. If the cancellation fails (say, because the
2785 server was already done processing the command), then there will be no visible
2790 <function>PQcancel</function> can safely be invoked from a signal handler,
2791 if the <parameter>errbuf</> is a local variable in the signal handler. The
2792 <structname>PGcancel</> object is read-only as far as
2793 <function>PQcancel</function> is concerned, so it can also be invoked from a
2794 thread that is separate from the one manipulating the <structname>PGconn</>
2803 <term><function>PQrequestCancel</function><indexterm><primary>PQrequestCancel</></></term>
2806 Requests that the server abandon
2807 processing of the current command.
2809 int PQrequestCancel(PGconn *conn);
2814 <function>PQrequestCancel</function> is a deprecated variant of
2815 <function>PQcancel</function>. It operates directly on the
2816 <structname>PGconn</> object, and in case of failure stores the
2817 error message in the <structname>PGconn</> object (whence it can be
2818 retrieved by <function>PQerrorMessage</function>). Although the
2819 functionality is the same, this approach creates hazards for multiple-thread
2820 programs and signal handlers, since it is possible that overwriting the
2821 <structname>PGconn</>'s error message will mess up the operation currently
2822 in progress on the connection.
2831 <sect1 id="libpq-fastpath">
2832 <title>The Fast-Path Interface</title>
2834 <indexterm zone="libpq-fastpath"><primary>fast path</></>
2837 <productname>PostgreSQL</productname> provides a fast-path interface to send
2838 simple function calls to the server.
2843 This interface is somewhat obsolete, as one may achieve similar performance
2844 and greater functionality by setting up a prepared statement to define the
2845 function call. Then, executing the statement with binary transmission of
2846 parameters and results substitutes for a fast-path function call.
2851 The function <function>PQfn</function><indexterm><primary>PQfn</></>
2852 requests execution of a server function via the fast-path interface:
2854 PGresult *PQfn(PGconn *conn,
2859 const PQArgBlock *args,
2874 The <parameter>fnid</> argument is the OID of the function to be
2875 executed. <parameter>args</> and <parameter>nargs</> define the
2876 parameters to be passed to the function; they must match the declared
2877 function argument list. When the <parameter>isint</> field of a
2880 the <parameter>u.integer</> value is sent to the server as an integer
2881 of the indicated length (this must be 1, 2, or 4 bytes); proper
2882 byte-swapping occurs. When <parameter>isint</> is false, the
2883 indicated number of bytes at <parameter>*u.ptr</> are sent with no
2884 processing; the data must be in the format expected by the server for
2885 binary transmission of the function's argument data type.
2886 <parameter>result_buf</parameter> is the buffer in which
2887 to place the return value. The caller must have allocated
2888 sufficient space to store the return value. (There is no check!)
2889 The actual result length will be returned in the integer pointed
2890 to by <parameter>result_len</parameter>.
2891 If a 1, 2, or 4-byte integer result is expected, set
2892 <parameter>result_is_int</parameter> to 1, otherwise set it to 0.
2893 Setting <parameter>result_is_int</parameter> to 1
2894 causes <application>libpq</> to byte-swap the value if necessary, so that
2896 delivered as a proper <type>int</type> value for the client machine. When
2897 <parameter>result_is_int</> is 0, the binary-format byte string sent by
2898 the server is returned unmodified.
2902 <function>PQfn</function> always returns a valid <structname>PGresult</structname> pointer. The result status
2903 should be checked before the result is used. The
2904 caller is responsible for freeing the <structname>PGresult</structname> with
2905 <function>PQclear</function> when it is no longer needed.
2909 Note that it is not possible to handle null arguments, null results, nor
2910 set-valued results when using this interface.
2915 <sect1 id="libpq-notify">
2916 <title>Asynchronous Notification</title>
2918 <indexterm zone="libpq-notify">
2919 <primary>NOTIFY</primary>
2920 <secondary>in libpq</secondary>
2924 <productname>PostgreSQL</productname> offers asynchronous notification via the
2925 <command>LISTEN</command> and <command>NOTIFY</command> commands. A client session registers its interest in a particular
2926 notification condition with the <command>LISTEN</command> command (and can stop listening
2927 with the <command>UNLISTEN</command> command). All sessions listening on a
2928 particular condition will be notified asynchronously when a <command>NOTIFY</command> command with that
2929 condition name is executed by any session. No additional information is
2930 passed from the notifier to the listener. Thus, typically, any actual data
2931 that needs to be communicated is transferred through a database table.
2932 Commonly, the condition name is the same as the associated table, but it is
2933 not necessary for there to be any associated table.
2937 <application>libpq</application> applications submit
2938 <command>LISTEN</command> and <command>UNLISTEN</command> commands as
2939 ordinary SQL commands. The arrival of <command>NOTIFY</command>
2940 messages can subsequently be detected by calling
2941 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
2945 The function <function>PQnotifies</function>
2946 returns the next notification from a list of unhandled
2947 notification messages received from the server. It returns a null pointer if
2948 there are no pending notifications. Once a notification is
2949 returned from <function>PQnotifies</>, it is considered handled and will be
2950 removed from the list of notifications.
2952 PGnotify *PQnotifies(PGconn *conn);
2954 typedef struct pgNotify {
2955 char *relname; /* notification condition name */
2956 int be_pid; /* process ID of server process */
2957 char *extra; /* notification parameter */
2960 After processing a <structname>PGnotify</structname> object returned by
2961 <function>PQnotifies</function>, be sure to free it with
2962 <function>PQfreemem</function>. It is sufficient to free the
2963 <structname>PGnotify</structname> pointer; the
2964 <structfield>relname</structfield> and <structfield>extra</structfield> fields
2965 do not represent separate allocations.
2966 (At present, the <structfield>extra</structfield> field is unused and will
2967 always point to an empty string.)
2972 In <productname>PostgreSQL</productname> 6.4 and later,
2973 the <structfield>be_pid</structfield> is that of the notifying server process,
2974 whereas in earlier versions it was always the <acronym>PID</acronym> of your own server process.
2979 <xref linkend="libpq-example-2"> gives a sample program that illustrates the use
2980 of asynchronous notification.
2984 <function>PQnotifies</function> does not actually read data from the server; it just
2985 returns messages previously absorbed by another <application>libpq</application>
2986 function. In prior releases of <application>libpq</application>, the only way
2987 to ensure timely receipt of <command>NOTIFY</> messages was to constantly submit commands,
2988 even empty ones, and then check <function>PQnotifies</function> after each
2989 <function>PQexec</function>. While this still works, it is
2990 deprecated as a waste of processing power.
2994 A better way to check for <command>NOTIFY</>
2995 messages when you have no useful commands to execute is to call
2996 <function>PQconsumeInput</function>, then check
2997 <function>PQnotifies</function>.
2998 You can use <function>select()</function> to wait for data to
2999 arrive from the server, thereby using no <acronym>CPU</acronym> power unless there is something
3000 to do. (See <function>PQsocket</function> to obtain the file descriptor
3001 number to use with <function>select()</function>.)
3002 Note that this will work OK whether you submit commands with
3003 <function>PQsendQuery</function>/<function>PQgetResult</function> or simply
3004 use <function>PQexec</function>. You should, however, remember to
3005 check <function>PQnotifies</function> after each
3006 <function>PQgetResult</function> or <function>PQexec</function>, to see
3007 if any notifications came in during the processing of the command.
3012 <sect1 id="libpq-copy">
3013 <title>Functions Associated with the <command>COPY</command> Command</title>
3015 <indexterm zone="libpq-copy">
3016 <primary>COPY</primary>
3017 <secondary>with libpq</secondary>
3021 The <command>COPY</command> command in <productname>PostgreSQL</productname>
3022 has options to read from or write to the network connection used by
3023 <application>libpq</application>. The functions described in this section
3024 allow applications to take advantage of this capability by supplying or
3025 consuming copied data.
3029 The overall process is that the application first issues the SQL
3030 <command>COPY</command> command via <function>PQexec</function> or one
3031 of the equivalent functions. The response to this (if there is no error
3032 in the command) will be a <structname>PGresult</> object bearing a status
3033 code of <literal>PGRES_COPY_OUT</literal> or <literal>PGRES_COPY_IN</literal>
3034 (depending on the specified copy direction). The application should then
3035 use the functions of this section to receive or transmit data rows.
3036 When the data transfer is complete, another <structname>PGresult</> object
3037 is returned to indicate success or failure of the transfer. Its status
3038 will be <literal>PGRES_COMMAND_OK</literal> for success or
3039 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
3040 At this point further SQL commands may be issued via
3041 <function>PQexec</function>. (It is not possible to execute other SQL
3042 commands using the same connection while the <command>COPY</command>
3043 operation is in progress.)
3047 If a <command>COPY</command> command is issued via
3048 <function>PQexec</function> in a string that could contain additional
3049 commands, the application must continue fetching results via
3050 <function>PQgetResult</> after completing the <command>COPY</command>
3051 sequence. Only when <function>PQgetResult</> returns <symbol>NULL</symbol> is it certain
3052 that the <function>PQexec</function> command string is done and it is
3053 safe to issue more commands.
3057 The functions of this section should be executed only after obtaining a
3058 result status of <literal>PGRES_COPY_OUT</literal> or
3059 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
3060 <function>PQgetResult</function>.
3064 A <structname>PGresult</> object bearing one of these status values
3065 carries some additional data about the <command>COPY</command> operation that
3066 is starting. This additional data is available using functions that are
3067 also used in connection with query results:
3071 <term><function>PQnfields</function><indexterm><primary>PQnfields</><secondary>with COPY</></></term>
3074 Returns the number of columns (fields) to be copied.
3080 <term><function>PQbinaryTuples</function><indexterm><primary>PQbinaryTuples</><secondary>with COPY</></></term>
3083 0 indicates the overall copy format is textual (rows
3084 separated by newlines, columns separated by separator
3086 1 indicates the overall copy format is binary.
3087 See <xref linkend="sql-copy" endterm="sql-copy-title">
3088 for more information.
3094 <term><function>PQfformat</function><indexterm><primary>PQfformat</><secondary>with COPY</></></term>
3097 Returns the format code (0 for text, 1 for binary) associated
3098 with each column of the copy operation. The per-column format
3099 codes will always be zero when the overall copy format is textual,
3100 but the binary format can support both text and binary columns.
3101 (However, as of the current implementation of <command>COPY</>,
3102 only binary columns appear in a binary copy; so the per-column
3103 formats always match the overall format at present.)
3112 These additional data values are only available when using protocol 3.0.
3113 When using protocol 2.0, all these functions will return 0.
3117 <sect2 id="libpq-copy-send">
3118 <title>Functions for Sending <command>COPY</command> Data</title>
3121 These functions are used to send data during <literal>COPY FROM STDIN</>.
3122 They will fail if called when the connection is not in <literal>COPY_IN</>
3128 <term><function>PQputCopyData</function><indexterm><primary>PQputCopyData</></></term>
3131 Sends data to the server during <literal>COPY_IN</> state.
3133 int PQputCopyData(PGconn *conn,
3140 Transmits the <command>COPY</command> data in the specified <parameter>buffer</>, of length
3141 <parameter>nbytes</>, to the server. The result is 1 if the data was sent,
3142 zero if it was not sent because the attempt would block (this case is only
3143 possible if the connection is in nonblocking mode), or -1 if an error occurred.
3144 (Use <function>PQerrorMessage</function> to retrieve details if the return
3145 value is -1. If the value is zero, wait for write-ready and try again.)
3149 The application may divide the <command>COPY</command> data stream into buffer loads of any
3150 convenient size. Buffer-load boundaries have no semantic significance when
3151 sending. The contents of the data stream must match the data format expected
3152 by the <command>COPY</> command; see
3153 <xref linkend="sql-copy" endterm="sql-copy-title"> for details.
3159 <term><function>PQputCopyEnd</function><indexterm><primary>PQputCopyEnd</></></term>
3162 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
3164 int PQputCopyEnd(PGconn *conn,
3165 const char *errormsg);
3170 Ends the <literal>COPY_IN</> operation successfully if <parameter>errormsg</>
3171 is <symbol>NULL</symbol>. If <parameter>errormsg</> is not <symbol>NULL</symbol> then the <command>COPY</>
3172 is forced to fail, with the string pointed to by <parameter>errormsg</>
3173 used as the error message. (One should not assume that this exact error
3174 message will come back from the server, however, as the server might have
3175 already failed the <command>COPY</> for its own reasons. Also note that the
3176 option to force failure does not work when using pre-3.0-protocol connections.)
3180 The result is 1 if the termination data was sent,
3181 zero if it was not sent because the attempt would block (this case is only
3182 possible if the connection is in nonblocking mode), or -1 if an error occurred.
3183 (Use <function>PQerrorMessage</function> to retrieve details if the return
3184 value is -1. If the value is zero, wait for write-ready and try again.)
3188 After successfully calling <function>PQputCopyEnd</>, call
3189 <function>PQgetResult</> to obtain the final result status of the
3190 <command>COPY</> command. One may wait for
3191 this result to be available in the usual way. Then return to normal
3200 <sect2 id="libpq-copy-receive">
3201 <title>Functions for Receiving <command>COPY</command> Data</title>
3204 These functions are used to receive data during <literal>COPY TO STDOUT</>.
3205 They will fail if called when the connection is not in <literal>COPY_OUT</>
3211 <term><function>PQgetCopyData</function><indexterm><primary>PQgetCopyData</></></term>
3214 Receives data from the server during <literal>COPY_OUT</> state.
3216 int PQgetCopyData(PGconn *conn,
3223 Attempts to obtain another row of data from the server during a <command>COPY</command>.
3224 Data is always returned one data row at a time; if only a partial row
3225 is available, it is not returned. Successful return of a data row
3226 involves allocating a chunk of memory to hold the data. The
3227 <parameter>buffer</> parameter must be non-<symbol>NULL</symbol>. <parameter>*buffer</>
3228 is set to point to the allocated memory, or to <symbol>NULL</symbol> in cases where no
3229 buffer is returned. A non-<symbol>NULL</symbol> result buffer must be freed using
3230 <function>PQfreemem</> when no longer needed.
3234 When a row is successfully returned, the return value is the number of
3235 data bytes in the row (this will always be greater than zero). The
3236 returned string is always null-terminated, though this is probably only
3237 useful for textual <command>COPY</command>. A result of zero indicates that the <command>COPY</command> is
3238 still in progress, but no row is yet available (this is only possible
3239 when <parameter>async</> is true). A
3240 result of -1 indicates that the <command>COPY</command> is done.
3241 A result of -2 indicates that an error occurred (consult
3242 <function>PQerrorMessage</> for the reason).
3246 When <parameter>async</> is true (not zero), <function>PQgetCopyData</>
3247 will not block waiting for input; it will return zero if the <command>COPY</command> is still
3248 in progress but no complete row is available. (In this case wait for
3249 read-ready and then call <function>PQconsumeInput</> before calling
3250 <function>PQgetCopyData</> again.) When <parameter>async</> is
3251 false (zero), <function>PQgetCopyData</> will block until data is available
3252 or the operation completes.
3256 After <function>PQgetCopyData</> returns -1, call
3257 <function>PQgetResult</> to obtain the final result status of the
3258 <command>COPY</> command. One may wait for
3259 this result to be available in the usual way. Then return to normal
3268 <sect2 id="libpq-copy-deprecated">
3269 <title>Obsolete Functions for <command>COPY</command></title>
3272 These functions represent older methods of handling <command>COPY</>.
3273 Although they still work, they are deprecated due to poor error handling,
3274 inconvenient methods of detecting end-of-data, and lack of support for binary
3275 or nonblocking transfers.
3280 <term><function>PQgetline</function><indexterm><primary>PQgetline</></></term>
3283 Reads a newline-terminated line of characters
3284 (transmitted by the server) into a buffer
3285 string of size <parameter>length</>.
3287 int PQgetline(PGconn *conn,
3294 This function copies up to <parameter>length</>-1 characters
3295 into the buffer and converts
3296 the terminating newline into a zero byte.
3297 <function>PQgetline</function> returns <symbol>EOF</symbol> at the end of input, 0 if the
3298 entire line has been read, and 1 if the buffer is full but the
3299 terminating newline has not yet been read.
3302 Note that the application must check to see if a
3303 new line consists of the two characters <literal>\.</literal>,
3304 which indicates that the server has finished sending
3305 the results of the <command>COPY</command> command.
3306 If the application might
3307 receive lines that are more than <parameter>length</>-1 characters long,
3308 care is needed to be sure it recognizes the <literal>\.</literal> line correctly
3309 (and does not, for example, mistake the end of a long data line
3310 for a terminator line).
3316 <term><function>PQgetlineAsync</function><indexterm><primary>PQgetlineAsync</></></term>
3319 Reads a row of <command>COPY</command> data
3320 (transmitted by the server) into a buffer
3323 int PQgetlineAsync(PGconn *conn,
3330 This function is similar to <function>PQgetline</function>, but it can be used
3332 that must read <command>COPY</command> data asynchronously, that is, without blocking.
3333 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
3335 application should call <function>PQconsumeInput</function> and
3336 <function>PQgetlineAsync</function> until the
3337 end-of-data signal is detected.
3340 Unlike <function>PQgetline</function>, this function takes
3341 responsibility for detecting end-of-data.
3344 On each call, <function>PQgetlineAsync</function> will return data if a
3345 complete data row is available in <application>libpq</>'s input buffer.
3346 Otherwise, no data is returned until the rest of the row arrives.
3347 The function returns -1 if the end-of-copy-data marker has been recognized,
3348 or 0 if no data is available, or a positive number giving the number of
3349 bytes of data returned. If -1 is returned, the caller must next call
3350 <function>PQendcopy</function>, and then return to normal processing.
3353 The data returned will not extend beyond a data-row boundary. If possible
3354 a whole row will be returned at one time. But if the buffer offered by
3355 the caller is too small to hold a row sent by the server, then a partial
3356 data row will be returned. With textual data this can be detected by testing
3357 whether the last returned byte is <literal>\n</literal> or not. (In a binary
3358 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
3359 equivalent determination.)
3360 The returned string is not null-terminated. (If you want to add a
3361 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
3362 than the room actually available.)
3368 <term><function>PQputline</function><indexterm><primary>PQputline</></></term>
3371 Sends a null-terminated string to the server.
3372 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
3374 int PQputline(PGconn *conn,
3375 const char *string);
3380 The <command>COPY</command> data stream sent by a series of calls to
3381 <function>PQputline</function> has the same format as that returned by
3382 <function>PQgetlineAsync</function>, except that applications are not
3383 obliged to send exactly one data row per <function>PQputline</function>
3384 call; it is okay to send a partial line or multiple lines per call.
3389 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
3390 for the application to explicitly send the two characters
3391 <literal>\.</literal> as a final line to indicate to the server that it had
3392 finished sending <command>COPY</> data. While this still works, it is deprecated and the
3393 special meaning of <literal>\.</literal> can be expected to be removed in a
3394 future release. It is sufficient to call <function>PQendcopy</function> after
3395 having sent the actual data.
3402 <term><function>PQputnbytes</function><indexterm><primary>PQputnbytes</></></term>
3405 Sends a non-null-terminated string to the server.
3406 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
3408 int PQputnbytes(PGconn *conn,
3415 This is exactly like <function>PQputline</function>, except that the data
3416 buffer need not be null-terminated since the number of bytes to send is
3417 specified directly. Use this procedure when sending binary data.
3423 <term><function>PQendcopy</function><indexterm><primary>PQendcopy</></></term>
3426 Synchronizes with the server.
3428 int PQendcopy(PGconn *conn);
3430 This function waits until
3431 the server has finished the copying. It should
3432 either be issued when the last string has been
3433 sent to the server using <function>PQputline</function> or when the
3434 last string has been received from the server
3435 using <function>PGgetline</function>. It must be issued or the server
3436 will get <quote>out of sync</quote> with the client. Upon
3437 return from this function, the server is ready to
3438 receive the next SQL command.
3439 The return value is 0 on successful completion,
3440 nonzero otherwise. (Use <function>PQerrorMessage</function> to retrieve
3441 details if the return value is nonzero.)
3445 When using <function>PQgetResult</function>, the application should respond to
3446 a <literal>PGRES_COPY_OUT</literal> result by executing <function>PQgetline</function>
3447 repeatedly, followed by <function>PQendcopy</function> after the terminator line is seen.
3448 It should then return to the <function>PQgetResult</function> loop until
3449 <function>PQgetResult</function> returns a null pointer. Similarly a <literal>PGRES_COPY_IN</literal>
3450 result is processed by a series of <function>PQputline</function> calls followed by
3451 <function>PQendcopy</function>, then return to the <function>PQgetResult</function> loop.
3452 This arrangement will ensure that
3453 a <command>COPY</command> command embedded in a series of <acronym>SQL</acronym> commands
3454 will be executed correctly.
3458 Older applications are likely to submit a <command>COPY</command>
3459 via <function>PQexec</function> and assume that the transaction is done after
3460 <function>PQendcopy</function>.
3461 This will work correctly only if the <command>COPY</command> is the only
3462 <acronym>SQL</acronym> command in the command string.
3472 <sect1 id="libpq-control">
3473 <title>Control Functions</title>
3476 These functions control miscellaneous details of
3477 <application>libpq</>'s behavior.
3482 <term><function>PQsetErrorVerbosity</function><indexterm><primary>PQsetErrorVerbosity</></></term>
3485 Determines the verbosity of messages returned by
3486 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
3494 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
3496 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
3497 the connection's previous setting. In <firstterm>TERSE</> mode,
3498 returned messages include severity, primary text, and position only;
3499 this will normally fit on a single line. The default mode produces
3500 messages that include the above plus any detail, hint, or context
3501 fields (these may span multiple lines). The <firstterm>VERBOSE</>
3502 mode includes all available fields. Changing the verbosity does not
3503 affect the messages available from already-existing
3504 <structname>PGresult</> objects, only subsequently-created ones.
3510 <term><function>PQtrace</function><indexterm><primary>PQtrace</></></term>
3513 Enables tracing of the client/server communication to a debugging file stream.
3515 void PQtrace(PGconn *conn, FILE *stream);
3522 <term><function>PQuntrace</function><indexterm><primary>PQuntrace</></></term>
3525 Disables tracing started by <function>PQtrace</function>.
3527 void PQuntrace(PGconn *conn);
3536 <sect1 id="libpq-notice-processing">
3537 <title>Notice Processing</title>
3539 <indexterm zone="libpq-notice-processing">
3540 <primary>notice processing</primary>
3541 <secondary>in libpq</secondary>
3545 Notice and warning messages generated by the server are not returned by the
3546 query execution functions, since they do not imply failure of the query.
3547 Instead they are passed to a notice handling function, and execution continues
3548 normally after the handler returns. The default notice handling function
3549 prints the message on <filename>stderr</filename>, but the application can
3550 override this behavior by supplying its own handling function.
3554 For historical reasons, there are two levels of notice handling, called the
3555 notice receiver and notice processor. The default behavior is for the notice
3556 receiver to format the notice and pass a string to the notice processor
3557 for printing. However, an application that chooses to provide its own notice
3558 receiver will typically ignore the notice processor layer and just do all the
3559 work in the notice receiver.
3563 The function <function>PQsetNoticeReceiver</function>
3564 <indexterm><primary>notice receiver</></><indexterm><primary>PQsetNoticeReceiver</></>
3565 sets or examines the current notice receiver for a connection object.
3566 Similarly, <function>PQsetNoticeProcessor</function>
3567 <indexterm><primary>notice processor</></><indexterm><primary>PQsetNoticeProcessor</></>
3568 sets or examines the current notice processor.
3571 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
3574 PQsetNoticeReceiver(PGconn *conn,
3575 PQnoticeReceiver proc,
3578 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
3581 PQsetNoticeProcessor(PGconn *conn,
3582 PQnoticeProcessor proc,
3586 Each of these functions returns the previous notice receiver or processor
3587 function pointer, and sets the new value.
3588 If you supply a null function pointer, no action is taken,
3589 but the current pointer is returned.
3593 When a notice or warning message is received from the server, or generated
3594 internally by <application>libpq</application>, the notice receiver function
3595 is called. It is passed the message in the form of a
3596 <symbol>PGRES_NONFATAL_ERROR</symbol> <structname>PGresult</structname>.
3597 (This allows the receiver to extract individual fields using
3598 <function>PQresultErrorField</>, or the complete preformatted message using
3599 <function>PQresultErrorMessage</>.)
3600 The same void pointer passed to
3601 <function>PQsetNoticeReceiver</function> is also passed.
3602 (This pointer can be used to access application-specific state if needed.)
3606 The default notice receiver simply extracts the message (using
3607 <function>PQresultErrorMessage</>) and passes it to the notice processor.
3611 The notice processor is responsible for handling a notice or warning message
3612 given in text form. It is passed the string text of the message
3613 (including a trailing newline), plus
3614 a void pointer that is the same one passed to
3615 <function>PQsetNoticeProcessor</function>.
3616 (This pointer can be used to access application-specific state if needed.)
3620 The default notice processor is simply
3623 defaultNoticeProcessor(void *arg, const char *message)
3625 fprintf(stderr, "%s", message);
3631 Once you have set a notice receiver or processor, you should expect that that
3632 function could be called as long as either the <structname>PGconn</> object or
3633 <structname>PGresult</> objects made from it exist. At creation of a
3634 <structname>PGresult</>, the <structname>PGconn</>'s current notice handling
3635 pointers are copied into the <structname>PGresult</> for possible use by
3636 functions like <function>PQgetvalue</function>.
3641 <sect1 id="libpq-envars">
3642 <title>Environment Variables</title>
3644 <indexterm zone="libpq-envars">
3645 <primary>environment variable</primary>
3649 The following environment variables can be used to select default
3650 connection parameter values, which will be used by
3651 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
3652 <function>PQsetdb</> if no value is directly specified by the calling
3653 code. These are useful to avoid hard-coding database connection
3654 information into simple client applications, for example.
3660 <primary><envar>PGHOST</envar></primary>
3662 <envar>PGHOST</envar> sets the database server name.
3663 If this begins with a slash, it specifies Unix-domain communication
3664 rather than TCP/IP communication; the value is then the name of the
3665 directory in which the socket file is stored (in a default installation
3666 setup this would be <filename>/tmp</filename>).
3672 <primary><envar>PGHOSTADDR</envar></primary>
3674 <envar>PGHOSTADDR</envar> specifies the numeric IP address of the database
3675 server. This can be set instead of or in addition to <envar>PGHOST</envar>
3676 to avoid DNS lookup overhead. See the documentation of
3677 these parameters, under <function>PQconnectdb</function> above, for details
3678 on their interaction.
3681 When neither <envar>PGHOST</envar> nor <envar>PGHOSTADDR</envar> is set,
3682 the default behavior is to connect using a local Unix-domain socket; or on
3683 machines without Unix-domain sockets, <application>libpq</application> will
3684 attempt to connect to <literal>localhost</>.
3690 <primary><envar>PGPORT</envar></primary>
3692 <envar>PGPORT</envar> sets the TCP port number or Unix-domain
3693 socket file extension for communicating with the
3694 <productname>PostgreSQL</productname> server.
3700 <primary><envar>PGDATABASE</envar></primary>
3702 <envar>PGDATABASE</envar> sets the
3703 <productname>PostgreSQL</productname> database name.
3709 <primary><envar>PGUSER</envar></primary>
3711 <envar>PGUSER</envar>
3712 sets the user name used to connect to the database.
3718 <primary><envar>PGPASSWORD</envar></primary>
3720 <envar>PGPASSWORD</envar>
3721 sets the password used if the server demands password
3722 authentication. Use of this environment variable is not
3723 recommended for security reasons (some operating systems
3724 allow non-root users to see process environment variables via
3725 <application>ps</>); instead consider using the
3726 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
3732 <primary><envar>PGPASSFILE</envar></primary>
3734 <envar>PGPASSFILE</envar>
3735 specifies the name of the password file to use for lookups.
3736 If not set, it defaults to <filename>~/.pgpass</>
3737 (see <xref linkend="libpq-pgpass">).
3743 <primary><envar>PGSERVICE</envar></primary>
3745 <envar>PGSERVICE</envar>
3746 sets the service name to be looked up in <filename>pg_service.conf</filename>.
3747 This offers a shorthand way of setting all the parameters.
3753 <primary><envar>PGREALM</envar></primary>
3755 <envar>PGREALM</envar> sets the Kerberos realm to use with
3756 <productname>PostgreSQL</productname>, if it is different from the local realm.
3757 If <envar>PGREALM</envar> is set, <application>libpq</application>
3758 applications will attempt authentication with servers for this realm and use
3759 separate ticket files to avoid conflicts with local
3760 ticket files. This environment variable is only
3761 used if Kerberos authentication is selected by the server.
3767 <primary><envar>PGOPTIONS</envar></primary>
3769 <envar>PGOPTIONS</envar> sets additional run-time options for
3770 the <productname>PostgreSQL</productname> server.
3776 <primary><envar>PGSSLMODE</envar></primary>
3778 <envar>PGSSLMODE</envar> determines whether and with what priority an
3779 <acronym>SSL</> connection will be negotiated with the server. There are
3780 four modes: <literal>disable</> will attempt only an unencrypted
3781 <acronym>SSL</> connection; <literal>allow</> will negotiate,
3782 trying first a non-<acronym>SSL</> connection, then if that fails,
3783 trying an <acronym>SSL</> connection; <literal>prefer</>
3784 (the default) will negotiate, trying first an <acronym>SSL</>
3785 connection, then if that fails, trying a regular non-<acronym>SSL</>
3786 connection; <literal>require</> will try only an <acronym>SSL</>
3787 connection. If <productname>PostgreSQL</> is compiled without SSL support,
3788 using option <literal>require</> will cause an error, while options
3789 <literal>allow</> and <literal>prefer</> will be accepted but
3790 <application>libpq</> will not in fact attempt an <acronym>SSL</>
3797 <primary><envar>PGREQUIRESSL</envar></primary>
3799 <envar>PGREQUIRESSL</envar> sets whether or not the connection must be
3800 made over <acronym>SSL</acronym>. If set to
3801 <quote>1</quote>, <application>libpq</>
3802 will refuse to connect if the server does not accept
3803 an <acronym>SSL</acronym> connection (equivalent to <literal>sslmode</>
3804 <literal>prefer</>).
3805 This option is deprecated in favor of the <literal>sslmode</>
3806 setting, and is only available if
3807 <productname>PostgreSQL</> is compiled with SSL support.
3813 <primary><envar>PGKRBSRVNAME</envar></primary>
3815 <envar>PGKRBSRVNAME</envar> sets the Kerberos service name to use when
3816 authenticating with Kerberos 5.
3822 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
3824 <envar>PGCONNECT_TIMEOUT</envar> sets the maximum number of seconds
3825 that <application>libpq</application> will wait when attempting to
3826 connect to the <productname>PostgreSQL</productname> server. If unset
3827 or set to zero, <application>libpq</application> will wait indefinitely.
3828 It is not recommended to set the timeout to less than 2 seconds.
3835 The following environment variables can be used to specify default
3836 behavior for each <productname>PostgreSQL</productname> session.
3838 <xref linkend="sql-alteruser" endterm="sql-alteruser-title"> and
3839 <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">
3840 commands for ways to set default behavior on a per-user or per-database
3847 <primary><envar>PGDATESTYLE</envar></primary>
3849 <envar>PGDATESTYLE</envar>
3850 sets the default style of date/time representation.
3851 (Equivalent to <literal>SET datestyle TO ...</literal>.)
3857 <primary><envar>PGTZ</envar></primary>
3860 sets the default time zone.
3861 (Equivalent to <literal>SET timezone TO ...</literal>.)
3867 <primary><envar>PGCLIENTENCODING</envar></primary>
3869 <envar>PGCLIENTENCODING</envar>
3870 sets the default client character set encoding.
3871 (Equivalent to <literal>SET client_encoding TO ...</literal>.)
3877 <primary><envar>PGGEQO</envar></primary>
3879 <envar>PGGEQO</envar>
3880 sets the default mode for the genetic query optimizer.
3881 (Equivalent to <literal>SET geqo TO ...</literal>.)
3886 Refer to the <acronym>SQL</acronym> command
3887 <xref linkend="sql-set" endterm="sql-set-title">
3888 for information on correct values for these environment variables.
3892 The following environment variables determine internal behavior of
3893 <application>libpq</application>; they override compiled-in defaults.
3899 <primary><envar>PGSYSCONFDIR</envar></primary>
3901 <envar>PGSYSCONFDIR</envar>
3902 sets the directory containing the <filename>pg_service.conf</> file.
3908 <primary><envar>PGLOCALEDIR</envar></primary>
3910 <envar>PGLOCALEDIR</envar>
3911 sets the directory containing the <literal>locale</> files for message
3912 internationalization.
3921 <sect1 id="libpq-pgpass">
3922 <title>The Password File</title>
3924 <indexterm zone="libpq-pgpass">
3925 <primary>password file</primary>
3927 <indexterm zone="libpq-pgpass">
3928 <primary>.pgpass</primary>
3932 The file <filename>.pgpass</filename> in a user's home directory or the
3933 file referenced by <envar>PGPASSFILE</envar> can contain passwords to
3934 be used if the connection requires a password (and no password has been
3935 specified otherwise). On Microsoft Windows the file is named
3936 <filename>%APPDATA%\postgresql\pgpass.conf</> (where
3937 <filename>%APPDATA%</> refers to the Application Data subdirectory in
3938 the user's profile).
3942 This file should contain lines of the following format:
3944 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
3946 Each of the first four fields may be a literal value, or <literal>*</literal>,
3948 matches anything. The password field from the first line that matches the
3949 current connection parameters will be used. (Therefore, put more-specific
3950 entries first when you are using wildcards.)
3951 If an entry needs to contain <literal>:</literal> or
3952 <literal>\</literal>, escape this character with <literal>\</literal>.
3956 The permissions on <filename>.pgpass</filename> must disallow any
3957 access to world or group; achieve this by the command
3958 <command>chmod 0600 ~/.pgpass</command>.
3959 If the permissions are less strict than this, the file will be ignored.
3960 (The file permissions are not currently checked on Microsoft Windows,
3966 <sect1 id="libpq-ssl">
3967 <title>SSL Support</title>
3969 <indexterm zone="libpq-ssl">
3970 <primary>SSL</primary>
3974 <productname>PostgreSQL</> has native support for using
3975 <acronym>SSL</> connections to encrypt client/server communications
3976 for increased security. See <xref linkend="ssl-tcp"> for details
3977 about the server-side <acronym>SSL</> functionality.
3981 If the server demands a client certificate,
3982 <application>libpq</application>
3983 will send the certificate stored in file
3984 <filename>~/.postgresql/postgresql.crt</> within the user's home directory.
3985 A matching private key file <filename>~/.postgresql/postgresql.key</>
3986 must also be present, and must not be world-readable.
3987 (On Microsoft Windows these files are named
3988 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
3989 <filename>%APPDATA%\postgresql\postgresql.key</filename>.)
3993 If the file <filename>~/.postgresql/root.crt</> is present in the user's
3995 <application>libpq</application> will use the certificate list stored
3996 therein to verify the server's certificate.
3997 (On Microsoft Windows the file is named
3998 <filename>%APPDATA%\postgresql\root.crt</filename>.)
3999 The SSL connection will
4000 fail if the server does not present a certificate; therefore, to
4001 use this feature the server must also have a <filename>root.crt</> file.
4006 <sect1 id="libpq-threading">
4007 <title>Behavior in Threaded Programs</title>
4009 <indexterm zone="libpq-threading">
4010 <primary>threads</primary>
4011 <secondary>with libpq</secondary>
4015 <application>libpq</application> is reentrant and thread-safe if the
4016 <filename>configure</filename> command-line option
4017 <literal>--enable-thread-safety</> was used when the
4018 <productname>PostgreSQL</productname> distribution was built. In
4019 addition, you might need to use additional compiler command-line
4020 options when you compile your application code. Refer to your
4021 system's documentation for information about how to build
4022 thread-enabled applications, or look in
4023 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
4024 and <literal>PTHREAD_LIBS</>.
4028 One restriction is that no two threads attempt to manipulate the same
4029 <structname>PGconn</> object at the same time. In particular, you cannot
4030 issue concurrent commands from different threads through the same
4031 connection object. (If you need to run concurrent commands, use
4032 multiple connections.)
4036 <structname>PGresult</> objects are read-only after creation, and so can be
4037 passed around freely between threads.
4041 The deprecated functions
4042 <function>PQrequestCancel</function>,
4043 <function>PQoidStatus</function> and
4044 <function>fe_setauthsvc</function>
4045 are not thread-safe and should not be used in multithread programs.
4046 <function>PQrequestCancel</function> can be replaced by
4047 <function>PQcancel</function>.
4048 <function>PQoidStatus</function> can be replaced by
4049 <function>PQoidValue</function>.
4050 There is no good reason to call <function>fe_setauthsvc</function> at all.
4054 <application>libpq</application> applications that use the
4055 <literal>crypt</literal> authentication method rely on the
4056 <literal>crypt()</literal> operating system function, which is often
4057 not thread-safe.<indexterm><primary>crypt</><secondary>thread
4058 safety</></> It is better to use the <literal>md5</literal> method,
4059 which is thread-safe on all platforms.
4063 If you experience problems with threaded applications, run
4064 the program in <filename>src/tools/thread</> to see if your
4065 platform has thread-unsafe functions. This program is run
4066 by <filename>configure</filename>, but for binary distributions
4067 your library might not match the library used to build the binaries.
4072 <sect1 id="libpq-build">
4073 <title>Building <application>libpq</application> Programs</title>
4075 <indexterm zone="libpq-build">
4076 <primary>compiling</primary>
4077 <secondary>libpq applications</secondary>
4081 To build (i.e., compile and link) a program using
4082 <application>libpq</application> you need to
4083 do all of the following things:
4088 Include the <filename>libpq-fe.h</filename> header file:
4090 #include <libpq-fe.h>
4092 If you failed to do that then you will normally get error
4093 messages from your compiler similar to
4095 foo.c: In function `main':
4096 foo.c:34: `PGconn' undeclared (first use in this function)
4097 foo.c:35: `PGresult' undeclared (first use in this function)
4098 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
4099 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
4100 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
4107 Point your compiler to the directory where the <productname>PostgreSQL</> header
4108 files were installed, by supplying the
4109 <literal>-I<replaceable>directory</replaceable></literal> option
4110 to your compiler. (In some cases the compiler will look into
4111 the directory in question by default, so you can omit this
4112 option.) For instance, your compile command line could look
4115 cc -c -I/usr/local/pgsql/include testprog.c
4117 If you are using makefiles then add the option to the
4118 <varname>CPPFLAGS</varname> variable:
4120 CPPFLAGS += -I/usr/local/pgsql/include
4125 If there is any chance that your program might be compiled by
4126 other users then you should not hardcode the directory location
4127 like that. Instead, you can run the utility
4128 <command>pg_config</command><indexterm><primary>pg_config</><secondary
4129 sortas="libpq">with libpq</></> to find out where the header
4130 files are on the local system:
4132 <prompt>$</prompt> pg_config --includedir
4133 <computeroutput>/usr/local/include</computeroutput>
4138 Failure to specify the correct option to the compiler will
4139 result in an error message such as
4141 testlibpq.c:8:22: libpq-fe.h: No such file or directory
4148 When linking the final program, specify the option
4149 <literal>-lpq</literal> so that the <application>libpq</application> library gets pulled
4150 in, as well as the option
4151 <literal>-L<replaceable>directory</replaceable></literal> to
4152 point the compiler to the directory where the <application>libpq</application> library resides. (Again, the
4153 compiler will search some directories by default.) For maximum
4154 portability, put the <option>-L</option> option before the
4155 <option>-lpq</option> option. For example:
4157 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
4162 You can find out the library directory using
4163 <command>pg_config</command> as well:
4165 <prompt>$</prompt> pg_config --libdir
4166 <computeroutput>/usr/local/pgsql/lib</computeroutput>
4171 Error messages that point to problems in this area could look
4174 testlibpq.o: In function `main':
4175 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
4176 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
4177 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
4179 This means you forgot <option>-lpq</option>.
4181 /usr/bin/ld: cannot find -lpq
4183 This means you forgot the <option>-L</option> option or did not specify
4184 the right directory.
4191 <indexterm><primary>libpq-int.h</></>
4192 If your codes references the header file
4193 <filename>libpq-int.h</filename> and you refuse to fix your code to
4194 not use it, starting in <productname>PostgreSQL</> 7.2, this file will be found in
4195 <filename><replaceable>includedir</replaceable>/postgresql/internal/libpq-int.h</filename>,
4196 so you need to add the appropriate <option>-I</option> option to
4197 your compiler command line.
4203 <sect1 id="libpq-example">
4204 <title>Example Programs</title>
4207 These examples and others can be found in the
4208 directory <filename>src/test/examples</filename> in the source code
4212 <example id="libpq-example-1">
4213 <title><application>libpq</application> Example Program 1</title>
4219 * Test the C version of LIBPQ, the POSTGRES frontend library.
4221 #include <stdio.h>
4222 #include <stdlib.h>
4223 #include "libpq-fe.h"
4226 exit_nicely(PGconn *conn)
4233 main(int argc, char **argv)
4235 const char *conninfo;
4243 * If the user supplies a parameter on the command line, use it as
4244 * the conninfo string; otherwise default to setting dbname=postgres
4245 * and using environment variables or defaults for all other connection
4251 conninfo = "dbname = postgres";
4253 /* Make a connection to the database */
4254 conn = PQconnectdb(conninfo);
4256 /* Check to see that the backend connection was successfully made */
4257 if (PQstatus(conn) != CONNECTION_OK)
4259 fprintf(stderr, "Connection to database failed: %s",
4260 PQerrorMessage(conn));
4265 * Our test case here involves using a cursor, for which we must be
4266 * inside a transaction block. We could do the whole thing with a
4267 * single PQexec() of "select * from pg_database", but that's too
4268 * trivial to make a good example.
4271 /* Start a transaction block */
4272 res = PQexec(conn, "BEGIN");
4273 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4275 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
4281 * Should PQclear PGresult whenever it is no longer needed to avoid
4287 * Fetch rows from pg_database, the system catalog of databases
4289 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
4290 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4292 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
4298 res = PQexec(conn, "FETCH ALL in myportal");
4299 if (PQresultStatus(res) != PGRES_TUPLES_OK)
4301 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
4306 /* first, print out the attribute names */
4307 nFields = PQnfields(res);
4308 for (i = 0; i < nFields; i++)
4309 printf("%-15s", PQfname(res, i));
4312 /* next, print out the rows */
4313 for (i = 0; i < PQntuples(res); i++)
4315 for (j = 0; j < nFields; j++)
4316 printf("%-15s", PQgetvalue(res, i, j));
4322 /* close the portal ... we don't bother to check for errors ... */
4323 res = PQexec(conn, "CLOSE myportal");
4326 /* end the transaction */
4327 res = PQexec(conn, "END");
4330 /* close the connection to the database and cleanup */
4338 <example id="libpq-example-2">
4339 <title><application>libpq</application> Example Program 2</title>
4344 * Test of the asynchronous notification interface
4346 * Start this program, then from psql in another window do
4348 * Repeat four times to get this program to exit.
4350 * Or, if you want to get fancy, try this:
4351 * populate a database with the following commands
4352 * (provided in src/test/examples/testlibpq2.sql):
4354 * CREATE TABLE TBL1 (i int4);
4356 * CREATE TABLE TBL2 (i int4);
4358 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
4359 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
4361 * and do this four times:
4363 * INSERT INTO TBL1 VALUES (10);
4365 #include <stdio.h>
4366 #include <stdlib.h>
4367 #include <string.h>
4368 #include <errno.h>
4369 #include <sys/time.h>
4370 #include "libpq-fe.h"
4373 exit_nicely(PGconn *conn)
4380 main(int argc, char **argv)
4382 const char *conninfo;
4389 * If the user supplies a parameter on the command line, use it as
4390 * the conninfo string; otherwise default to setting dbname=postgres
4391 * and using environment variables or defaults for all other connection
4397 conninfo = "dbname = postgres";
4399 /* Make a connection to the database */
4400 conn = PQconnectdb(conninfo);
4402 /* Check to see that the backend connection was successfully made */
4403 if (PQstatus(conn) != CONNECTION_OK)
4405 fprintf(stderr, "Connection to database failed: %s",
4406 PQerrorMessage(conn));
4411 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
4413 res = PQexec(conn, "LISTEN TBL2");
4414 if (PQresultStatus(res) != PGRES_COMMAND_OK)
4416 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
4422 * should PQclear PGresult whenever it is no longer needed to avoid
4427 /* Quit after four notifies are received. */
4429 while (nnotifies < 4)
4432 * Sleep until something happens on the connection. We use select(2)
4433 * to wait for input, but you could also use poll() or similar
4439 sock = PQsocket(conn);
4442 break; /* shouldn't happen */
4444 FD_ZERO(&input_mask);
4445 FD_SET(sock, &input_mask);
4447 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
4449 fprintf(stderr, "select() failed: %s\n", strerror(errno));
4453 /* Now check for input */
4454 PQconsumeInput(conn);
4455 while ((notify = PQnotifies(conn)) != NULL)
4458 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
4459 notify->relname, notify->be_pid);
4465 fprintf(stderr, "Done.\n");
4467 /* close the connection to the database and cleanup */
4475 <example id="libpq-example-3">
4476 <title><application>libpq</application> Example Program 3</>
4481 * Test out-of-line parameters and binary I/O.
4483 * Before running this, populate a database with the following commands
4484 * (provided in src/test/examples/testlibpq3.sql):
4486 * CREATE TABLE test1 (i int4, t text, b bytea);
4488 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
4489 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
4491 * The expected output is:
4495 * t = (11 bytes) 'joe's place'
4496 * b = (5 bytes) \000\001\002\003\004
4499 #include <stdio.h>
4500 #include <stdlib.h>
4501 #include <string.h>
4502 #include <sys/types.h>
4503 #include "libpq-fe.h"
4505 /* for ntohl/htonl */
4506 #include <netinet/in.h>
4507 #include <arpa/inet.h>
4511 exit_nicely(PGconn *conn)
4518 main(int argc, char **argv)
4520 const char *conninfo;
4523 const char *paramValues[1];
4531 * If the user supplies a parameter on the command line, use it as
4532 * the conninfo string; otherwise default to setting dbname=postgres
4533 * and using environment variables or defaults for all other connection
4539 conninfo = "dbname = postgres";
4541 /* Make a connection to the database */
4542 conn = PQconnectdb(conninfo);
4544 /* Check to see that the backend connection was successfully made */
4545 if (PQstatus(conn) != CONNECTION_OK)
4547 fprintf(stderr, "Connection to database failed: %s",
4548 PQerrorMessage(conn));
4553 * The point of this program is to illustrate use of PQexecParams()
4554 * with out-of-line parameters, as well as binary transmission of
4555 * results. By using out-of-line parameters we can avoid a lot of
4556 * tedious mucking about with quoting and escaping. Notice how we
4557 * don't have to do anything special with the quote mark in the
4561 /* Here is our out-of-line parameter value */
4562 paramValues[0] = "joe's place";
4564 res = PQexecParams(conn,
4565 "SELECT * FROM test1 WHERE t = $1",
4567 NULL, /* let the backend deduce param type */
4569 NULL, /* don't need param lengths since text */
4570 NULL, /* default to all text params */
4571 1); /* ask for binary results */
4573 if (PQresultStatus(res) != PGRES_TUPLES_OK)
4575 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
4580 /* Use PQfnumber to avoid assumptions about field order in result */
4581 i_fnum = PQfnumber(res, "i");
4582 t_fnum = PQfnumber(res, "t");
4583 b_fnum = PQfnumber(res, "b");
4585 for (i = 0; i < PQntuples(res); i++)
4593 /* Get the field values (we ignore possibility they are null!) */
4594 iptr = PQgetvalue(res, i, i_fnum);
4595 tptr = PQgetvalue(res, i, t_fnum);
4596 bptr = PQgetvalue(res, i, b_fnum);
4599 * The binary representation of INT4 is in network byte order,
4600 * which we'd better coerce to the local byte order.
4602 ival = ntohl(*((uint32_t *) iptr));
4605 * The binary representation of TEXT is, well, text, and since
4606 * libpq was nice enough to append a zero byte to it, it'll work
4607 * just fine as a C string.
4609 * The binary representation of BYTEA is a bunch of bytes, which
4610 * could include embedded nulls so we have to pay attention to
4613 blen = PQgetlength(res, i, b_fnum);
4615 printf("tuple %d: got\n", i);
4616 printf(" i = (%d bytes) %d\n",
4617 PQgetlength(res, i, i_fnum), ival);
4618 printf(" t = (%d bytes) '%s'\n",
4619 PQgetlength(res, i, t_fnum), tptr);
4620 printf(" b = (%d bytes) ", blen);
4621 for (j = 0; j < blen; j++)
4622 printf("\\%03o", bptr[j]);
4628 /* close the connection to the database and cleanup */
4639 <!-- Keep this comment at the end of the file
4644 sgml-minimize-attributes:nil
4645 sgml-always-quote-attributes:t
4648 sgml-parent-document:nil
4649 sgml-default-dtd-file:"./reference.ced"
4650 sgml-exposed-tags:nil
4651 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4652 sgml-local-ecat-files:nil
projects / postgresql / blob