2 $Header: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v 1.127 2003/06/27 19:08:37 tgl Exp $
6 <title><application>libpq</application> - C Library</title>
8 <indexterm zone="libpq">
9 <primary>libpq</primary>
13 <application>libpq</application> is the <acronym>C</acronym>
14 application programmer's interface to
15 <productname>PostgreSQL</productname>. <application>libpq</application> is a set
16 of library functions that allow client programs to pass queries to the
17 <productname>PostgreSQL</productname> backend server and to receive the
18 results of these queries. <application>libpq</application> is also the
19 underlying engine for several other <productname>PostgreSQL</productname>
20 application interfaces, including <application>libpq++</application> (C++),
21 <application>libpgtcl</application> (Tcl), <productname>Perl</productname>, and
22 <application>ECPG</application>. So some aspects of <application>libpq</>'s behavior will be
23 important to you if you use one of those packages.
27 Some short programs are included at the end of this chapter (<xref linkend="libpq-example">) to show how
28 to write programs that use <application>libpq</application>. There are also several
29 complete examples of <application>libpq</application> applications in the
30 directory <filename>src/test/examples</filename> in the source code distribution.
34 Client programs that use <application>libpq</application> must include the
35 header file <filename>libpq-fe.h</filename> and must link with the
36 <application>libpq</application> library.
39 <sect1 id="libpq-connect">
40 <title>Database Connection Control Functions</title>
43 The following functions deal with making a connection to a
44 <productname>PostgreSQL</productname> backend server. An
45 application program can have several backend connections open at
46 one time. (One reason to do that is to access more than one
47 database.) Each connection is represented by a
48 <structname>PGconn</> object which is obtained from the function
49 <function>PQconnectdb</> or <function>PQsetdbLogin</>. Note that
50 these functions will always return a non-null object pointer,
51 unless perhaps there is too little memory even to allocate the
52 <structname>PGconn</> object. The <function>PQstatus</> function
53 should be called to check whether a connection was successfully
54 made before queries are sent via the connection object.
58 <term><function>PQconnectdb</function></term>
61 Makes a new connection to the database server.
63 PGconn *PQconnectdb(const char *conninfo);
68 This function opens a new database connection using the parameters taken
69 from the string <literal>conninfo</literal>. Unlike <function>PQsetdbLogin</> below,
70 the parameter set can be extended without changing the function signature,
71 so use of this function (or its nonblocking analogues <function>PQconnectStart</>
72 and <function>PQconnectPoll</function>) is preferred for new application programming.
77 can be empty to use all default parameters, or it can contain one or more
78 parameter settings separated by whitespace.
79 Each parameter setting is in the form <literal>keyword = value</literal>.
80 (To write an empty value or a value containing
81 spaces, surround it with single quotes, e.g.,
82 <literal>keyword = 'a value'</literal>.
83 Single quotes and backslashes within the value must be escaped with a
84 backslash, i.e., <literal>\'</literal> and <literal>\\</literal>.)
85 Spaces around the equal sign are optional.
89 The currently recognized parameter keywords are:
93 <term><literal>host</literal></term>
96 Name of host to connect to.
97 If this begins with a slash, it specifies Unix-domain communication
98 rather than TCP/IP communication; the value is the name of the
99 directory in which the socket file is stored.
100 The default is to connect to a Unix-domain socket in
101 <filename>/tmp</filename>.
107 <term><literal>hostaddr</literal></term>
110 Numeric IP address of host to connect to. This should be in the
111 standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
112 your machine supports IPv6, you can also use those addresses.
113 TCP/IP communication is
114 always used when a nonempty string is specified for this parameter.
117 Using <literal>hostaddr</> instead of <literal>host</> allows the
118 application to avoid a host name look-up, which may be important in
119 applications with time constraints. However, Kerberos authentication
120 requires the host name. The following therefore applies: If
121 <literal>host</> is specified without <literal>hostaddr</>, a host name
122 lookup occurs. If <literal>hostaddr</> is specified without
123 <literal>host</>, the value for <literal>hostaddr</> gives the remote
124 address. When Kerberos is used, a reverse name query occurs to obtain
125 the host name for Kerberos. If both
126 <literal>host</> and <literal>hostaddr</> are specified, the value for
127 <literal>hostaddr</> gives the remote address; the value for
128 <literal>host</> is ignored, unless Kerberos is used, in which case that
129 value is used for Kerberos authentication. (Note that authentication is
130 likely to fail if <application>libpq</application> is passed a host name
131 that is not the name of the machine at <literal>hostaddr</>.) Also,
132 <literal>host</> rather than <literal>hostaddr</> is used to identify
133 the connection in <filename>$HOME/.pgpass</>.
136 Without either a host name or host address,
137 <application>libpq</application> will connect using a
138 local Unix domain socket.
144 <term><literal>port</literal></term>
147 Port number to connect to at the server host,
148 or socket file name extension for Unix-domain connections.
154 <term><literal>dbname</literal></term>
157 The database name. Defaults to be the same as the user name.
163 <term><literal>user</literal></term>
166 <productname>PostgreSQL</productname> user name to connect as.
172 <term><literal>password</literal></term>
175 Password to be used if the server demands password authentication.
181 <term><literal>connect_timeout</literal></term>
184 Maximum wait for connection, in seconds (write as a decimal integer
185 string). Zero or not specified means wait indefinitely. It is not
186 recommended to use a timeout of less than 2 seconds.
192 <term><literal>options</literal></term>
195 Command-line options to be sent to the server.
201 <term><literal>tty</literal></term>
204 Ignored (formerly, this specified where to send server debug output).
210 <term><literal>requiressl</literal></term>
213 If set to 1, an <acronym>SSL</acronym> connection to the server is required.
214 <application>libpq</> will then refuse to connect if the server does not
215 accept an <acronym>SSL</acronym> connection.
216 If set to 0 (default), <application>libpq</> will negotiate the connection type with server.
217 This option is only available if
218 <productname>PostgreSQL</> is compiled with SSL support.
224 <term><literal>service</literal></term>
227 Service name to use for additional parameters. It specifies a service
228 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
229 This allows applications to specify only a service name so connection parameters
230 can be centrally maintained. See
231 <filename><replaceable>PREFIX</>/share/pg_service.conf.sample</> for
232 information on how to set up the file.
238 If any parameter is unspecified, then the corresponding
239 environment variable (see <xref linkend="libpq-envars">)
240 is checked. If the environment variable is not set either,
241 then built-in defaults are used.
247 <term><function>PQsetdbLogin</function></term>
250 Makes a new connection to the database server.
252 PGconn *PQsetdbLogin(const char *pghost,
254 const char *pgoptions,
263 This is the predecessor of <function>PQconnectdb</function> with a fixed
264 set of parameters. It has the same functionality except that the
265 missing parameters will always take on default values. Write NULL or an
266 empty string for any one of the fixed parameters that is to be defaulted.
272 <term><function>PQsetdb</function></term>
275 Makes a new connection to the database server.
277 PGconn *PQsetdb(char *pghost,
286 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
287 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
288 for backward compatibility with very old programs.
294 <term><function>PQconnectStart</function></term>
295 <term><function>PQconnectPoll</function></term>
298 <indexterm><primary>nonblocking connection</primary></indexterm>
299 Make a connection to the database server in a nonblocking manner.
301 PGconn *PQconnectStart(const char *conninfo);
304 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
308 These two functions are used to open a connection to a database server such
309 that your application's thread of execution is not blocked on remote I/O
311 The point of this approach is that the waits for I/O to complete can occur
312 in the application's main loop, rather than down inside
313 <function>PQconnectdb()</>, and so the application can manage this
314 operation in parallel with other activities.
317 The database connection is made using the parameters taken from the string
318 <literal>conninfo</literal>, passed to <function>PQconnectStart</function>. This string is in
319 the same format as described above for <function>PQconnectdb</function>.
322 Neither <function>PQconnectStart</function> nor <function>PQconnectPoll</function> will block, so long as a number of
323 restrictions are met:
327 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
328 name and reverse name queries are not made. See the documentation of
329 these parameters under <function>PQconnectdb</function> above for details.
335 If you call <function>PQtrace</function>, ensure that the stream object
336 into which you trace will not block.
342 You ensure that the socket is in the appropriate state
343 before calling <function>PQconnectPoll</function>, as described below.
350 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
351 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
352 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
353 representing a valid connection to the database). On return from
354 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
355 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
358 If <function>PQconnectStart</> succeeds, the next stage is to poll
359 <application>libpq</> so that it may proceed with the connection sequence.
360 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
361 socket underlying the database connection.
362 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
363 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
364 read (as indicated by <function>select()</>, <function>poll()</>, or
365 similar system function).
366 Then call <function>PQconnectPoll(conn)</function> again.
367 Conversely, if <function>PQconnectPoll(conn)</function> last returned
368 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
369 to write, then call <function>PQconnectPoll(conn)</function> again.
370 If you have yet to call
371 <function>PQconnectPoll</function>, i.e., just after the call to
372 <function>PQconnectStart</function>, behave as if it last returned
373 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
374 <function>PQconnectPoll(conn)</function> returns
375 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
376 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
377 has been successfully made.
381 At any time during connection, the status of the connection may be
382 checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
383 connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
384 connection is ready. Both of these states are equally detectable
385 from the return value of <function>PQconnectPoll</>, described above. Other states may also occur
386 during (and only during) an asynchronous connection procedure. These
387 indicate the current stage of the connection procedure and may be useful
388 to provide feedback to the user for example. These statuses are:
392 <term><symbol>CONNECTION_STARTED</symbol></term>
395 Waiting for connection to be made.
401 <term><symbol>CONNECTION_MADE</symbol></term>
404 Connection OK; waiting to send.
410 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
413 Waiting for a response from the server.
419 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
422 Received authentication; waiting for backend start-up to finish.
428 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
431 Negotiating SSL encryption.
437 <term><symbol>CONNECTION_SETENV</symbol></term>
440 Negotiating environment-driven parameter settings.
446 Note that, although these constants will remain (in order to maintain
447 compatibility), an application should never rely upon these appearing in a
448 particular order, or at all, or on the status always being one of these
449 documented values. An application might do something like this:
451 switch(PQstatus(conn))
453 case CONNECTION_STARTED:
454 feedback = "Connecting...";
457 case CONNECTION_MADE:
458 feedback = "Connected to server...";
464 feedback = "Connecting...";
470 The <literal>connect_timeout</literal> connection parameter is ignored
471 when using <function>PQconnectPoll</function>; it is the application's
472 responsibility to decide whether an excessive amount of time has elapsed.
473 Otherwise, <function>PQconnectStart</function> followed by a
474 <function>PQconnectPoll</function> loop is equivalent to
475 <function>PQconnectdb</function>.
479 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
480 <function>PQfinish</function> when you are finished with it, in order to dispose of
481 the structure and any associated memory blocks. This must be done even if
482 the connection attempt fails or is abandoned.
488 <term><function>PQconndefaults</function></term>
491 Returns the default connection options.
493 PQconninfoOption *PQconndefaults(void);
497 char *keyword; /* The keyword of the option */
498 char *envvar; /* Fallback environment variable name */
499 char *compiled; /* Fallback compiled in default value */
500 char *val; /* Option's current value, or NULL */
501 char *label; /* Label for field in connect dialog */
502 char *dispchar; /* Character to display for this field
503 in a connect dialog. Values are:
504 "" Display entered value as is
505 "*" Password field - hide value
506 "D" Debug option - don't show by default */
507 int dispsize; /* Field size in characters for dialog */
513 Returns a connection options array. This may
514 be used to determine all possible <function>PQconnectdb</function> options and their
515 current default values. The return value points to an array of
516 <structname>PQconninfoOption</structname> structures, which ends with an entry having a null
517 <structfield>keyword</> pointer. Note that the current default values
518 (<structfield>val</structfield> fields)
519 will depend on environment variables and other context.
520 Callers must treat the connection options data as read-only.
524 After processing the options array, free it by passing it to
525 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
526 is leaked for each call to <function>PQconndefaults</function>.
533 <term><function>PQfinish</function></term>
536 Closes the connection to the server. Also frees
537 memory used by the <structname>PGconn</structname> object.
539 void PQfinish(PGconn *conn);
544 Note that even if the server connection attempt fails (as
545 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
546 to free the memory used by the <structname>PGconn</structname> object.
547 The <structname>PGconn</> pointer must not be used again after
548 <function>PQfinish</function> has been called.
554 <term><function>PQreset</function></term>
557 Resets the communication channel to the server.
559 void PQreset(PGconn *conn);
564 This function will close the connection
565 to the server and attempt to reestablish a new
566 connection to the same server, using all the same
567 parameters previously used. This may be useful for
568 error recovery if a working connection is lost.
574 <term><function>PQresetStart</function></term>
575 <term><function>PQresetPoll</function></term>
578 Reset the communication channel to the server, in a nonblocking manner.
580 int PQresetStart(PGconn *conn);
583 PostgresPollingStatusType PQresetPoll(PGconn *conn);
588 These functions will close the connection to the server and attempt to
589 reestablish a new connection to the same server, using all the same
590 parameters previously used. This may be useful for error recovery if a
591 working connection is lost. They differ from <function>PQreset</function> (above) in that they
592 act in a nonblocking manner. These functions suffer from the same
593 restrictions as <function>PQconnectStart</> and <function>PQconnectPoll</>.
596 To initiate a connection reset, call <function>PQresetStart</function>. If it returns 0, the reset has failed. If it returns 1,
597 poll the reset using <function>PQresetPoll</function> in exactly the same way as you would
598 create the connection using <function>PQconnectPoll</function>.
607 <sect1 id="libpq-status">
608 <title>Connection Status Functions</title>
611 These functions may be used to interrogate the status
612 of an existing database connection object.
617 <indexterm><primary>libpq-fe.h</></>
618 <indexterm><primary>libpq-int.h</></>
619 <application>libpq</application> application programmers should be careful to
620 maintain the <structname>PGconn</structname> abstraction. Use the accessor
621 functions described below to get
622 at the contents of <structname>PGconn</structname>. Avoid directly referencing the fields of the
623 <structname>PGconn</> structure because they are subject to change in the future.
624 (Beginning in <productname>PostgreSQL</productname> release 6.4, the
625 definition of the <type>struct</type> behind <structname>PGconn</> is not even provided in <filename>libpq-fe.h</filename>.
626 If you have old code that accesses <structname>PGconn</structname> fields directly, you can keep using it
627 by including <filename>libpq-int.h</filename> too, but you are encouraged to fix the code
633 The following functions return parameter values established at connection.
634 These values are fixed for the life of the <structname>PGconn</> object.
638 <term><function>PQdb</function></term>
641 Returns the database name of the connection.
643 char *PQdb(const PGconn *conn);
650 <term><function>PQuser</function></term>
653 Returns the user name of the connection.
655 char *PQuser(const PGconn *conn);
662 <term><function>PQpass</function></term>
665 Returns the password of the connection.
667 char *PQpass(const PGconn *conn);
674 <term><function>PQhost</function></term>
677 Returns the server host name of the connection.
679 char *PQhost(const PGconn *conn);
686 <term><function>PQport</function></term>
689 Returns the port of the connection.
691 char *PQport(const PGconn *conn);
698 <term><function>PQtty</function></term>
701 Returns the debug <acronym>TTY</acronym> of the connection.
702 (This is obsolete, since the server no longer pays attention
703 to the <acronym>TTY</acronym> setting, but the function remains
704 for backwards compatibility.)
706 char *PQtty(const PGconn *conn);
713 <term><function>PQoptions</function></term>
716 Returns the command-line options passed in the connection request.
718 char *PQoptions(const PGconn *conn);
727 The following functions return status data that can change as operations
728 are executed on the <structname>PGconn</> object.
732 <term><function>PQstatus</function></term>
735 Returns the status of the connection.
737 ConnStatusType PQstatus(const PGconn *conn);
742 The status can be one of a number of values.
743 However, only two of these are
744 seen outside of an asynchronous connection procedure:
745 <literal>CONNECTION_OK</literal> and
746 <literal>CONNECTION_BAD</literal>. A good
747 connection to the database has the status <literal>CONNECTION_OK</literal>.
749 attempt is signaled by status
750 <literal>CONNECTION_BAD</literal>.
751 Ordinarily, an OK status will remain so until
752 <function>PQfinish</function>, but a
753 communications failure might result in the status changing to
754 <literal>CONNECTION_BAD</literal> prematurely.
755 In that case the application
756 could try to recover by calling <function>PQreset</function>.
760 See the entry for <function>PQconnectStart</> and <function>PQconnectPoll</> with regards
761 to other status codes
768 <term><function>PQtransactionStatus</function></term>
771 Returns the current in-transaction status of the server.
773 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
776 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
777 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
778 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
779 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
780 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
781 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
782 has been sent to the server and not yet completed.
786 <function>PQtransactionStatus</> will give incorrect results when using
787 a <productname>PostgreSQL</> 7.3 server that has <literal>AUTOCOMMIT</>
788 set to <literal>OFF</>. The server-side autocommit feature has been
789 deprecated and does not exist in later server versions.
796 <term><function>PQparameterStatus</function></term>
799 Looks up a current parameter setting of the server.
801 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
804 Certain parameter values are reported by the server automatically at
805 connection startup or whenever their values change.
806 <function>PQparameterStatus</> can be used to interrogate these settings.
807 It returns the current value of a parameter if known, or NULL if the parameter
812 Parameters reported as of the current release include
813 <literal>server_version</> (cannot change after startup);
814 <literal>server_encoding</> (also not presently changeable after start);
815 <literal>client_encoding</>,
816 <literal>is_superuser</>, and
817 <literal>DateStyle</>.
821 Pre-3.0-protocol servers do not report parameter settings,
822 but <application>libpq</> includes logic to obtain values for
823 <literal>server_version</>, <literal>server_encoding</>, and
824 <literal>client_encoding</>. Applications are encouraged to use
825 <function>PQparameterStatus</> rather than ad-hoc code to determine these
826 values. (Beware however that on a pre-3.0 connection, changing
827 <literal>client_encoding</> via <command>SET</> after connection startup
828 will not be reflected by <function>PQparameterStatus</>.)
834 <term><function>PQprotocolVersion</function></term>
837 Interrogates the frontend/backend protocol being used.
839 int PQprotocolVersion(const PGconn *conn);
841 Applications may wish to use this to determine whether certain features
843 Currently, the possible values are 2 (2.0 protocol), 3 (3.0 protocol),
844 or zero (connection bad). This will not change after connection
845 startup is complete, but it could theoretically change during a reset.
846 The 3.0 protocol will normally be used when communicating with
847 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers support
848 only protocol 2.0. (Protocol 1.0 is obsolete and not supported by libpq.)
854 <term><function>PQerrorMessage</function></term>
857 <indexterm><primary>error message</></>
858 Returns the error message most recently generated by
859 an operation on the connection.
861 char *PQerrorMessage(const PGconn* conn);
866 Nearly all <application>libpq</> functions will set a message for
867 <function>PQerrorMessage</function> if they fail.
868 Note that by <application>libpq</application> convention, a nonempty
869 <function>PQerrorMessage</function> result will
870 include a trailing newline.
876 <term><function>PQsocket</function></term>
879 Obtains the file descriptor number of the connection socket to
880 the server. A valid descriptor will be greater than or equal
881 to 0; a result of -1 indicates that no server connection is
882 currently open. (This will not change during normal operation,
883 but could change during connection setup or reset.)
885 int PQsocket(const PGconn *conn);
892 <term><function>PQbackendPID</function></term>
895 Returns the process <acronym>ID</acronym> of the backend server process
896 handling this connection.
898 int PQbackendPID(const PGconn *conn);
903 The backend <acronym>PID</acronym> is useful for debugging
904 purposes and for comparison to <command>NOTIFY</command>
905 messages (which include the <acronym>PID</acronym> of the
906 notifying backend process). Note that the
907 <acronym>PID</acronym> belongs to a process executing on the
908 database server host, not the local host!
914 <term><function>PQgetssl</function></term>
917 <indexterm><primary>SSL</></>
918 Returns the SSL structure used in the connection, or null
919 if SSL is not in use.
921 SSL *PQgetssl(const PGconn *conn);
926 This structure can be used to verify encryption levels, check
927 server certificates, and more. Refer to the <productname>OpenSSL</> documentation
928 for information about this structure.
931 You must define <symbol>USE_SSL</symbol> in order to get the
932 prototype for this function. Doing this will also
933 automatically include <filename>ssl.h</filename> from <productname>OpenSSL</productname>.
943 <sect1 id="libpq-exec">
944 <title>Command Execution Functions</title>
947 Once a connection to a database server has been successfully
948 established, the functions described here are used to perform
949 SQL queries and commands.
952 <sect2 id="libpq-exec-main">
953 <title>Main Functions</title>
958 <term><function>PQexec</function></term>
961 Submits a command to the server
962 and waits for the result.
964 PGresult *PQexec(PGconn *conn, const char *command);
969 Returns a <structname>PGresult</structname> pointer or possibly a null pointer.
970 A non-null pointer will generally be returned except in
971 out-of-memory conditions or serious errors such as inability
972 to send the command to the server.
973 If a null pointer is returned, it
974 should be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
975 <function>PQerrorMessage</function> to get more information about the error.
981 It is allowed to include multiple SQL commands (separated by semicolons) in
982 the command string. Multiple queries sent in a single <function>PQexec</>
983 call are processed in a single transaction, unless there are explicit
984 BEGIN/COMMIT commands included in the query string to divide it into multiple
985 transactions. Note however that the returned <structname>PGresult</structname>
986 structure describes only the result of the last command executed from the
987 string. Should one of the commands fail, processing of the string stops with
988 it and the returned <structname>PGresult</structname> describes the error
995 <term><function>PQexecParams</function></term>
998 Submits a command to the server and waits for the result,
999 with the ability to pass parameters separately from the SQL
1002 PGresult *PQexecParams(PGconn *conn,
1003 const char *command,
1005 const Oid *paramTypes,
1006 const char * const *paramValues,
1007 const int *paramLengths,
1008 const int *paramFormats,
1014 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1015 functionality: parameter values can be specified separately from the command
1016 string proper, and query results can be requested in either text or binary
1017 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1018 connections; it will fail when using protocol 2.0.
1022 If parameters are used, they are referred to in the command string
1023 as <literal>$1</>, <literal>$2</>, etc.
1024 <parameter>nParams</> is the number of parameters supplied; it is the length
1025 of the arrays <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1026 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1027 array pointers may be NULL when <parameter>nParams</> is zero.)
1028 <parameter>paramTypes[]</> specifies, by OID, the datatypes to be assigned to
1029 the parameter symbols. If <parameter>paramTypes</> is NULL, or any particular
1030 element in the array is zero, the backend assigns a datatype to the parameter
1031 symbol in the same way it would do for an untyped literal string.
1032 <parameter>paramValues[]</> specifies the actual values of the parameters.
1033 A NULL pointer in this array means the corresponding parameter is NULL;
1034 otherwise the pointer points to a zero-terminated text string (for text
1035 format) or binary data in the format expected by the backend (for binary
1037 <parameter>paramLengths[]</> specifies the actual data lengths of
1038 binary-format parameters. It is ignored for NULL parameters and text-format
1039 parameters. The array pointer may be NULL when there are no binary
1041 <parameter>paramFormats[]</> specifies whether parameters are text (put a zero
1042 in the array) or binary (put a one in the array). If the array pointer is
1043 NULL then all parameters are presumed to be text.
1044 <parameter>resultFormat</> is zero to obtain results in text format, or one to
1045 obtain results in binary format. (There is not currently a provision to
1046 obtain different result columns in different formats, although that is
1047 possible in the underlying protocol.)
1053 The primary advantage of <function>PQexecParams</> over <function>PQexec</>
1054 is that parameter values may be separated from the command string, thus
1055 avoiding the need for tedious and error-prone quoting and escaping.
1057 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most one SQL
1058 command in the given string. (There can be semicolons in it, but not more
1059 than one nonempty command.) This is a limitation of the underlying protocol,
1060 but has some usefulness as an extra defense against SQL-injection attacks.
1064 The <structname>PGresult</structname> structure encapsulates the result
1065 returned by the server.
1066 <application>libpq</application> application programmers should be careful to
1067 maintain the <structname>PGresult</structname> abstraction. Use the accessor functions below to get
1068 at the contents of <structname>PGresult</structname>. Avoid directly referencing the fields of the
1069 <structname>PGresult</structname> structure because they are subject to change in the future.
1073 <term><function>PQresultStatus</function></term>
1076 Returns the result status of the command.
1078 ExecStatusType PQresultStatus(const PGresult *res);
1083 <function>PQresultStatus</function> can return one of the following values:
1087 <term><literal>PGRES_EMPTY_QUERY</literal></term>
1089 <para>The string sent to the server was empty.</para>
1094 <term><literal>PGRES_COMMAND_OK</literal></term>
1096 <para>Successful completion of a command returning no data.</para>
1101 <term><literal>PGRES_TUPLES_OK</literal></term>
1103 <para>Successful completion of a command returning data (such as
1104 a <command>SELECT</> or <command>SHOW</>).</para>
1109 <term><literal>PGRES_COPY_OUT</literal></term>
1111 <para>Copy Out (from server) data transfer started.</para>
1116 <term><literal>PGRES_COPY_IN</literal></term>
1118 <para>Copy In (to server) data transfer started.</para>
1123 <term><literal>PGRES_BAD_RESPONSE</literal></term>
1125 <para>The server's response was not understood.</para>
1130 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
1132 <para>A nonfatal error (a notice or warning) occurred.</para>
1137 <term><literal>PGRES_FATAL_ERROR</literal></term>
1139 <para>A fatal error occurred.</para>
1144 If the result status is <literal>PGRES_TUPLES_OK</literal>, then the
1145 functions described below can be used to retrieve the rows returned by
1146 the query. Note that a <command>SELECT</command> command that happens
1147 to retrieve zero rows still shows <literal>PGRES_TUPLES_OK</literal>.
1148 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
1149 return rows (<command>INSERT</command>, <command>UPDATE</command>,
1150 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> may indicate
1151 a bug in the client software.
1155 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will never be
1156 returned directly by <function>PQexec</function> or other query
1157 execution functions; results of this kind are instead passed to the notice
1158 processor (see <xref linkend="libpq-notice-processing">).
1164 <term><function>PQresStatus</function></term>
1167 Converts the enumerated type returned by <function>PQresultStatus</> into
1168 a string constant describing the status code.
1170 char *PQresStatus(ExecStatusType status);
1177 <term><function>PQresultErrorMessage</function></term>
1180 Returns the error message associated with the command, or an empty string
1181 if there was no error.
1183 char *PQresultErrorMessage(const PGresult *res);
1185 If there was an error, the returned string will include a trailing newline.
1189 Immediately following a <function>PQexec</function> or <function>PQgetResult</function>
1190 call, <function>PQerrorMessage</function> (on the connection) will return the same
1191 string as <function>PQresultErrorMessage</function> (on the result). However, a
1192 <structname>PGresult</structname> will retain its error message
1193 until destroyed, whereas the connection's error message will change when
1194 subsequent operations are done. Use <function>PQresultErrorMessage</function> when you want to
1195 know the status associated with a particular <structname>PGresult</structname>; use <function>PQerrorMessage</function>
1196 when you want to know the status from the latest operation on the connection.
1202 <term><function>PQresultErrorField</function></term>
1205 Returns an individual field of an error report.
1207 char *PQresultErrorField(const PGresult *res, int fieldcode);
1209 <parameter>fieldcode</> is an error field identifier defined by the
1210 <productname>PostgreSQL</> protocol (see <xref
1211 linkend="protocol-error-fields">), for example <literal>'C'</> for
1212 the SQLSTATE error code. NULL is returned if the
1213 <structname>PGresult</structname> is not an error or warning result,
1214 or does not include the specified field. Field values will normally
1215 not include a trailing newline.
1219 Errors generated internally by libpq will have severity and primary message,
1220 but typically no other fields. Errors returned by a pre-3.0-protocol server
1221 will include severity and primary message, and sometimes a detail message,
1222 but no other fields.
1226 Note that error fields are only available from
1227 <structname>PGresult</structname> objects, not
1228 <structname>PGconn</structname> objects; there is no
1229 <function>PQerrorField</function> function.
1235 <term><function>PQclear</function></term>
1238 Frees the storage associated with a <structname>PGresult</structname>.
1239 Every command result should be freed via <function>PQclear</function> when
1240 it is no longer needed.
1242 void PQclear(PQresult *res);
1247 You can keep a <structname>PGresult</structname> object around for as long as you
1248 need it; it does not go away when you issue a new command,
1249 nor even if you close the connection. To get rid of it,
1250 you must call <function>PQclear</function>. Failure to do this will
1251 result in memory leaks in your application.
1257 <term><function>PQmakeEmptyPGresult</function></term>
1260 Constructs an empty <structname>PGresult</structname> object with the given status.
1262 PGresult* PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
1267 This is <application>libpq</>'s internal function to allocate and initialize an empty
1268 <structname>PGresult</structname> object. It is exported because some applications find it
1269 useful to generate result objects (particularly objects with error
1270 status) themselves. If <parameter>conn</parameter> is not null and <parameter>status</> indicates an error,
1271 the current error message of the specified connection is copied into the <structname>PGresult</structname>.
1272 Note that <function>PQclear</function> should eventually be called on the object, just
1273 as with a <structname>PGresult</structname> returned by <application>libpq</application> itself.
1281 <sect2 id="libpq-exec-select-info">
1282 <title>Retrieving Query Result Information</title>
1285 These functions are used to extract information from a
1286 <structname>PGresult</structname> object that represents a successful
1287 query result (that is, one that has status
1288 <literal>PGRES_TUPLES_OK</literal>). For objects with other status
1289 values they will act as though the result has zero rows and zero columns.
1294 <term><function>PQntuples</function></term>
1297 Returns the number of rows (tuples)
1298 in the query result.
1300 int PQntuples(const PGresult *res);
1307 <term><function>PQnfields</function></term>
1310 Returns the number of columns (fields)
1311 in each row of the query result.
1313 int PQnfields(const PGresult *res);
1320 <term><function>PQfname</function></term>
1323 Returns the column name associated with the given column number.
1324 Column numbers start at 0.
1326 char *PQfname(const PGresult *res,
1332 NULL is returned if the column number is out of range.
1338 <term><function>PQfnumber</function></term>
1341 Returns the column number
1342 associated with the given column name.
1344 int PQfnumber(const PGresult *res,
1345 const char *column_name);
1350 -1 is returned if the given name does not match any column.
1356 <term><function>PQftable</function></term>
1359 Returns the OID of the table from which the given column was fetched.
1360 Column numbers start at 0.
1362 Oid PQftable(const PGresult *res,
1368 <literal>InvalidOid</> is returned if the column number is out of range,
1369 or if the specified column is not a simple reference to a table column,
1370 or when using pre-3.0 protocol.
1371 You can query the system table <literal>pg_class</literal> to determine
1372 exactly which table is referenced.
1376 The type <type>Oid</type> and the constant
1377 <literal>InvalidOid</literal> will be defined when you include
1378 the <application>libpq</application> header file. They will
1379 both be some integer type.
1385 <term><function>PQftablecol</function></term>
1388 Returns the column number (within its table) of the column making up
1389 the specified query result column.
1390 Result column numbers start at 0.
1392 int PQftablecol(const PGresult *res,
1398 Zero is returned if the column number is out of range,
1399 or if the specified column is not a simple reference to a table column,
1400 or when using pre-3.0 protocol.
1406 <term><function>PQfformat</function></term>
1409 Returns the format code indicating the format of the given column.
1410 Column numbers start at 0.
1412 int PQfformat(const PGresult *res,
1418 Format code zero indicates textual data representation, while format
1419 code one indicates binary representation. (Other codes are reserved
1420 for future definition.)
1426 <term><function>PQftype</function></term>
1429 Returns the data type associated with the
1430 given column number. The integer returned is the
1431 internal OID number of the type. Column numbers start
1434 Oid PQftype(const PGresult *res,
1440 You can query the system table <literal>pg_type</literal> to obtain
1441 the names and properties of the various data types. The <acronym>OID</acronym>s
1442 of the built-in data types are defined in the file <filename>src/include/catalog/pg_type.h</filename>
1449 <term><function>PQfmod</function></term>
1452 Returns the type modifier of the column
1453 associated with the given column number.
1454 Column numbers start at 0.
1456 int PQfmod(const PGresult *res,
1462 The interpretation of modifier values is type-specific; they typically
1463 indicate precision or size limits. The value -1 is used to indicate
1464 <quote>no information available</>. Most data types do not use modifiers,
1465 in which case the value is always -1.
1471 <term><function>PQfsize</function></term>
1474 Returns the size in bytes of the column
1475 associated with the given column number.
1476 Column numbers start at 0.
1478 int PQfsize(const PGresult *res,
1484 <function>PQfsize</> returns the space allocated for this column in a database
1485 row, in other words the size of the server's internal representation
1486 of the data type. (Accordingly, it is not really very useful to clients.)
1487 A negative value indicates the data type is variable-length.
1493 <term><function>PQbinaryTuples</function></term>
1496 Returns 1 if the <structname>PGresult</> contains binary data
1497 and 0 if it contains text data.
1499 int PQbinaryTuples(const PGresult *res);
1504 This function is deprecated (except for its use in connection with
1505 <command>COPY</>), because it is possible for a single
1506 <structname>PGresult</>
1507 to contain text data in some columns and binary data in others.
1508 <function>PQfformat()</> is preferred. <function>PQbinaryTuples</>
1509 returns 1 only if all columns of the result are binary (format 1).
1515 <term><function>PQgetvalue</function></term>
1518 Returns a single field value of one row
1519 of a <structname>PGresult</structname>.
1520 Row and column numbers start at 0.
1522 char* PQgetvalue(const PGresult *res,
1529 For data in text format, the value returned by <function>PQgetvalue</function>
1530 is a null-terminated character string representation
1531 of the field value. For data in binary format, the value is in the binary
1532 representation determined by the datatype's <function>typsend</> and
1533 <function>typreceive</> functions. (The value is actually followed by
1534 a zero byte in this case too, but that is not ordinarily useful, since
1535 the value is likely to contain embedded nulls.)
1539 An empty string is returned if the field value is NULL. See
1540 <function>PQgetisnull</> to distinguish NULLs from empty-string values.
1545 returned by <function>PQgetvalue</function> points to storage that is
1546 part of the <structname>PGresult</structname> structure. One should not modify the data it points to,
1547 and one must explicitly
1548 copy the data into other storage if it is to
1549 be used past the lifetime of the <structname>PGresult</structname> structure itself.
1555 <term><function>PQgetisnull</function></term>
1558 Tests a field for a null value.
1559 Row and column numbers start at 0.
1561 int PQgetisnull(const PGresult *res,
1568 This function returns 1 if the field is null and 0 if
1569 it contains a non-null value. (Note that <function>PQgetvalue</function>
1570 will return an empty string, not a null pointer, for a null field.)
1576 <term><function>PQgetlength</function></term>
1579 Returns the actual length of a field value in bytes.
1580 Row and column numbers start at 0.
1582 int PQgetlength(const PGresult *res,
1589 This is the actual data length for the particular data value, that is, the
1590 size of the object pointed to by <function>PQgetvalue</function>. For text
1591 data format this is the same as <function>strlen()</>. For binary format
1592 this is essential information. Note that one should <emphasis>not</> rely
1593 on <function>PQfsize</function> to obtain the actual data length.
1599 <term><function>PQprint</function></term>
1602 Prints out all the rows and, optionally, the
1603 column names to the specified output stream.
1605 void PQprint(FILE* fout, /* output stream */
1606 const PGresult *res,
1607 const PQprintOpt *po);
1610 pqbool header; /* print output field headings and row count */
1611 pqbool align; /* fill align the fields */
1612 pqbool standard; /* old brain dead format */
1613 pqbool html3; /* output HTML tables */
1614 pqbool expanded; /* expand tables */
1615 pqbool pager; /* use pager for output if needed */
1616 char *fieldSep; /* field separator */
1617 char *tableOpt; /* attributes for HTML table element */
1618 char *caption; /* HTML table caption */
1619 char **fieldName; /* null-terminated array of replacement field names */
1625 This function was formerly used by <application>psql</application>
1626 to print query results, but this is no longer the case. Note that it
1627 assumes all the data is in text format.
1634 <sect2 id="libpq-exec-nonselect">
1635 <title>Retrieving Result Information for Other Commands</title>
1638 These functions are used to extract information from
1639 <structname>PGresult</structname> objects that are not <command>SELECT</>
1645 <term><function>PQcmdStatus</function></term>
1648 Returns the command status tag from the SQL command that
1649 generated the <structname>PGresult</structname>.
1651 char * PQcmdStatus(PGresult *res);
1655 Commonly this is just the name of the command, but it may include additional
1656 data such as the number of rows processed.
1662 <term><function>PQcmdTuples</function></term>
1665 Returns the number of rows affected by the SQL command.
1667 char * PQcmdTuples(PGresult *res);
1672 If the <acronym>SQL</acronym> command that generated the
1673 <structname>PGresult</structname> was <command>INSERT</>,
1674 <command>UPDATE</>, <command>DELETE</command>, <command>MOVE</>,
1675 or <command>FETCH</>, this returns a
1676 string containing the number of rows affected. If the
1677 command was anything else, it returns the empty string.
1683 <term><function>PQoidValue</function></term>
1686 Returns the OID of the inserted row, if the
1687 <acronym>SQL</acronym> command was an <command>INSERT</command>
1688 that inserted exactly one row into a table that has OIDs.
1689 Otherwise, returns <literal>InvalidOid</literal>.
1691 Oid PQoidValue(const PGresult *res);
1698 <term><function>PQoidStatus</function></term>
1701 Returns a string with the OID of the inserted row, if the
1702 <acronym>SQL</acronym> command was an
1703 <command>INSERT</command>. (The string will be
1704 <literal>0</> if the <command>INSERT</command> did not
1705 insert exactly one row, or if the target table does not have
1706 OIDs.) If the command was not an <command>INSERT</command>,
1707 returns an empty string.
1709 char * PQoidStatus(const PGresult *res);
1714 This function is deprecated in favor of <function>PQoidValue</function>.
1715 It is not thread-safe.
1723 <sect2 id="libpq-exec-escape-string">
1724 <title>Escaping Strings for Inclusion in SQL Commands</title>
1726 <indexterm zone="libpq-exec-escape-string"><primary>escaping strings</></>
1729 <function>PQescapeString</function> escapes a string for use within an SQL
1730 commmand. This is useful when inserting data values as literal constants
1731 in SQL commands. Certain characters (such as quotes and backslashes) must
1732 be escaped to prevent them from being interpreted specially by the SQL parser.
1733 <function>PQescapeString</> performs this operation.
1738 It is especially important to do proper escaping when handling strings that
1739 were received from an untrustworthy source. Otherwise there is a security
1740 risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
1741 SQL commands are fed to your database.
1746 Note that it is not necessary nor correct to do escaping when a data
1747 value is passed as a separate parameter in <function>PQexecParams</> or
1748 <function>PQsendQueryParams</>.
1751 size_t PQescapeString (char *to, const char *from, size_t length);
1756 The parameter <parameter>from</> points to the first character of the string
1758 is to be escaped, and the <parameter>length</> parameter gives the
1759 number of characters in this string. (A terminating zero byte is
1760 neither necessary nor counted.) <parameter>to</> shall point to a
1761 buffer that is able to hold at least one more character than twice
1762 the value of <parameter>length</>, otherwise the behavior is
1763 undefined. A call to <function>PQescapeString</> writes an escaped
1764 version of the <parameter>from</> string to the <parameter>to</>
1765 buffer, replacing special characters so that they cannot cause any
1766 harm, and adding a terminating zero byte. The single quotes that
1767 must surround <productname>PostgreSQL</> string literals are not
1768 included in the result string; they should be provided in the SQL
1769 command that the result is inserted into.
1772 <function>PQescapeString</> returns the number of characters written
1773 to <parameter>to</>, not including the terminating zero byte.
1776 Behavior is undefined if the <parameter>to</> and <parameter>from</>
1782 <sect2 id="libpq-exec-escape-bytea">
1783 <title>Escaping Binary Strings for Inclusion in SQL Commands</title>
1784 <indexterm zone="libpq-exec-escape-bytea">
1785 <primary>escaping binary strings</primary>
1790 <term><function>PQescapeBytea</function></term>
1793 Escapes binary data for use within an SQL command with the type
1794 <type>bytea</type>. As with <function>PQescapeString</function>,
1795 this is only used when inserting data directly into an SQL command string.
1797 unsigned char *PQescapeBytea(const unsigned char *from,
1804 Certain byte values <emphasis>must</emphasis> be escaped (but all
1805 byte values <emphasis>may</emphasis> be escaped) when used as part
1806 of a <type>bytea</type> literal in an <acronym>SQL</acronym>
1807 statement. In general, to escape a byte, it is converted into the
1808 three digit octal number equal to the octet value, and preceded by
1809 two backslashes. The single quote (<literal>'</>) and backslash
1810 (<literal>\</>) characters have special alternative escape
1811 sequences. See <xref linkend="datatype-binary"> for more
1812 information. <function>PQescapeBytea</function> performs this
1813 operation, escaping only the minimally required bytes.
1817 The <parameter>from</parameter> parameter points to the first
1818 byte of the string that is to be escaped, and the
1819 <parameter>from_length</parameter> parameter gives the number of
1820 bytes in this binary string. (A terminating zero byte is
1821 neither necessary nor counted.) The <parameter>to_length</parameter>
1822 parameter points to a variable that will hold the resultant
1823 escaped string length. The result string length includes the terminating
1824 zero byte of the result.
1828 <function>PQescapeBytea</> returns an escaped version of the
1829 <parameter>from</parameter> parameter binary string in memory
1830 allocated with <function>malloc()</>. This memory must be freed
1831 using <function>PQfreemem()</> when the result is no longer needed.
1832 The return string has all special characters replaced
1833 so that they can be properly processed by the PostgreSQL string literal
1834 parser, and the <type>bytea</type> input function. A terminating zero
1835 byte is also added. The single quotes that must surround
1836 PostgreSQL string literals are not part of the result string.
1842 <term><function>PQunescapeBytea</function></term>
1845 Converts an escaped string representation of binary data into binary
1846 data --- the reverse of <function>PQescapeBytea</function>.
1847 This is needed when retrieving <type>bytea</type> data in text format,
1848 but not when retrieving it in binary format.
1851 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
1856 The <parameter>from</parameter> parameter points to an escaped string
1857 such as might be returned by <function>PQgetvalue</function> when applied to a
1858 <type>bytea</type> column. <function>PQunescapeBytea</function> converts
1859 this string representation into its binary representation.
1860 It returns a pointer to a buffer allocated with
1861 <function>malloc()</function>, or null on error, and puts the size of
1862 the buffer in <parameter>to_length</parameter>. The result must be
1863 freed using <function>PQfreemem()</> when it is no longer needed.
1869 <term><function>PQfreemem</function></term>
1872 Frees memory allocated by <application>libpq</>.
1874 void PQfreemem(void *ptr);
1879 Frees memory allocated by <application>libpq</>, particularly
1880 <function>PQescapeBytea</function>,
1881 <function>PQunescapeBytea</function>,
1882 and <function>PQnotifies</function>.
1883 It is needed by Win32, which can not free memory across
1884 DLL's, unless multithreaded DLL's (/MD in VC6) are used.
1885 On other platforms it is the same as <function>free()</>.
1894 <sect1 id="libpq-async">
1895 <title>Asynchronous Command Processing</title>
1897 <indexterm zone="libpq-async"><primary>nonblocking connection</></>
1900 The <function>PQexec</function> function is adequate for submitting commands in
1902 applications. It has a couple of deficiencies, however, that can be of importance to some users:
1907 <function>PQexec</function> waits for the command to be completed. The application may have other
1908 work to do (such as maintaining a user interface), in which case it won't
1909 want to block waiting for the response.
1914 Since the execution of the client application is suspended while it
1915 waits for the result, it is hard for the application to decide that it
1916 would like to try to cancel the ongoing command. (It can be done from
1917 a signal handler, but not otherwise.)
1922 <function>PQexec</function> can return only one <structname>PGresult</structname> structure. If the submitted command
1923 string contains multiple <acronym>SQL</acronym> commands, all but the last <structname>PGresult</structname> are
1924 discarded by <function>PQexec</function>.
1931 Applications that do not like these limitations can instead use the
1932 underlying functions that <function>PQexec</function> is built from:
1933 <function>PQsendQuery</function> and <function>PQgetResult</function>.
1934 There is also <function>PQsendQueryParams</function>, which can be
1935 used with <function>PQgetResult</function> to duplicate the functionality
1936 of <function>PQexecParams</function>.
1940 <term><function>PQsendQuery</function></term>
1943 Submits a command to the server without
1944 waiting for the result(s). 1 is returned if the command was
1945 successfully dispatched and 0 if not (in which case, use
1946 <function>PQerrorMessage</> to get more information about the failure).
1948 int PQsendQuery(PGconn *conn, const char *command);
1951 After successfully calling <function>PQsendQuery</function>, call
1952 <function>PQgetResult</function> one or more
1953 times to obtain the results. <function>PQsendQuery</function> may not be called
1954 again (on the same connection) until <function>PQgetResult</function> has returned a null pointer,
1955 indicating that the command is done.
1961 <term><function>PQsendQueryParams</function></term>
1964 Submits a command and separate parameters to the server without
1965 waiting for the result(s).
1967 int PQsendQueryParams(PGconn *conn,
1968 const char *command,
1970 const Oid *paramTypes,
1971 const char * const *paramValues,
1972 const int *paramLengths,
1973 const int *paramFormats,
1977 This is equivalent to <function>PQsendQuery</function> except that
1978 query parameters can be specified separately from the query string.
1979 The function's parameters are handled identically to
1980 <function>PQexecParams</function>. Like
1981 <function>PQexecParams</function>, it will not work on 2.0-protocol
1982 connections, and it allows only one command in the query string.
1988 <term><function>PQgetResult</function></term>
1991 Waits for the next result from a prior
1992 <function>PQsendQuery</function> or
1993 <function>PQsendQueryParams</function>,
1994 and returns it. A null pointer is returned when the command is complete
1995 and there will be no more results.
1997 PGresult *PQgetResult(PGconn *conn);
2002 <function>PQgetResult</function> must be called repeatedly until it returns a null pointer,
2003 indicating that the command is done. (If called when no command is
2004 active, <function>PQgetResult</function> will just return a null pointer at once.)
2005 Each non-null result from <function>PQgetResult</function> should be processed using
2006 the same <structname>PGresult</> accessor functions previously described.
2007 Don't forget to free each result object with <function>PQclear</function> when done with it.
2008 Note that <function>PQgetResult</function> will block only if a command is active and the
2009 necessary response data has not yet been read by <function>PQconsumeInput</function>.
2017 Using <function>PQsendQuery</function> and <function>PQgetResult</function>
2018 solves one of <function>PQexec</function>'s problems:
2019 If a command string contains multiple <acronym>SQL</acronym> commands, the results of those
2020 commands can be obtained individually. (This allows a simple form of
2021 overlapped processing, by the way: the client can be handling the
2022 results of one command while the server is still working on later
2023 queries in the same command string.) However, calling <function>PQgetResult</function> will
2024 still cause the client to block until the server completes the
2025 next <acronym>SQL</acronym> command. This can be avoided by proper use of two
2030 <term><function>PQconsumeInput</function></term>
2033 If input is available from the server, consume it.
2035 int PQconsumeInput(PGconn *conn);
2040 <function>PQconsumeInput</function> normally returns 1 indicating <quote>no error</quote>,
2041 but returns 0 if there was some kind of trouble (in which case
2042 <function>PQerrorMessage</function> can be consulted). Note that the result
2044 whether any input data was actually collected. After calling
2045 <function>PQconsumeInput</function>, the application may check
2046 <function>PQisBusy</function> and/or <function>PQnotifies</function> to see if
2047 their state has changed.
2050 <function>PQconsumeInput</function> may be called even if the application is not
2051 prepared to deal with a result or notification just yet. The
2052 function will read available data and save it in a buffer, thereby
2053 causing a <function>select()</function> read-ready indication to go away. The
2054 application can thus use <function>PQconsumeInput</function> to clear the
2055 <function>select()</function> condition immediately, and then examine the results at leisure.
2061 <term><function>PQisBusy</function></term>
2064 Returns 1 if a command is busy, that is, <function>PQgetResult</function> would block
2065 waiting for input. A 0 return indicates that <function>PQgetResult</function> can
2066 be called with assurance of not blocking.
2068 int PQisBusy(PGconn *conn);
2073 <function>PQisBusy</function> will not itself attempt to read data from the server;
2074 therefore <function>PQconsumeInput</function> must be invoked first, or the busy
2075 state will never end.
2083 A typical application using these functions will have a main loop that uses
2084 <function>select()</function> or <function>poll()</> to wait for all the
2085 conditions that it must
2086 respond to. One of the conditions will be input available from the server,
2087 which in terms of <function>select()</function> means readable data on the file
2088 descriptor identified by <function>PQsocket</function>.
2089 When the main loop detects input ready, it should call
2090 <function>PQconsumeInput</function> to read the input. It can then call
2091 <function>PQisBusy</function>, followed by <function>PQgetResult</function>
2092 if <function>PQisBusy</function> returns false (0). It can also call
2093 <function>PQnotifies</function> to detect <command>NOTIFY</> messages (see <xref linkend="libpq-notify">).
2097 A client that uses <function>PQsendQuery</function>/<function>PQgetResult</function>
2098 can also attempt to cancel a command that is still being processed by the server.
2102 <term><function>PQrequestCancel</function></term>
2105 Requests that the server abandon
2106 processing of the current command.
2108 int PQrequestCancel(PGconn *conn);
2113 The return value is 1 if the cancel request was successfully
2114 dispatched and 0 if not. (If not, <function>PQerrorMessage</function> tells why not.)
2115 Successful dispatch is no guarantee that the request will have any
2116 effect, however. Regardless of the return value of <function>PQrequestCancel</function>,
2117 the application must continue with the normal result-reading
2118 sequence using <function>PQgetResult</function>. If the cancellation
2119 is effective, the current command will terminate early and return
2120 an error result. If the cancellation fails (say, because the
2121 server was already done processing the command), then there will
2122 be no visible result at all.
2126 Note that if the current command is part of a transaction block, cancellation
2127 will abort the whole transaction.
2131 <function>PQrequestCancel</function> can safely be invoked from a signal handler.
2132 So, it is also possible to use it in conjunction with plain
2133 <function>PQexec</function>, if the decision to cancel can be made in a signal
2134 handler. For example, <application>psql</application> invokes
2135 <function>PQrequestCancel</function> from a <symbol>SIGINT</> signal handler, thus allowing
2136 interactive cancellation of commands that it issues through <function>PQexec</function>.
2144 By using the functions described above, it is possible to avoid blocking
2145 while waiting for input from the database server. However, it is still
2146 possible that the application will block waiting to send output to the
2147 server. This is relatively uncommon but can happen if very long SQL commands
2148 or data values are sent. (It is much more probable if the application
2149 sends data via COPY IN, however.) To prevent this possibility and achieve
2150 completely nonblocking database operation, the following additional
2151 functions may be used.
2155 <term><function>PQsetnonblocking</function></term>
2158 Sets the nonblocking status of the connection.
2160 int PQsetnonblocking(PGconn *conn, int arg);
2165 Sets the state of the connection to nonblocking if
2166 <parameter>arg</parameter> is 1, or
2167 blocking if <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
2170 In the nonblocking state, calls to
2171 <function>PQsendQuery</function>,
2172 <function>PQputline</function>, <function>PQputnbytes</function>,
2173 and <function>PQendcopy</function>
2174 will not block but instead return an error if they need to be called
2178 Note that <function>PQexec</function> does not honor nonblocking mode;
2179 if it is called, it will act in blocking fashion anyway.
2185 <term><function>PQisnonblocking</function></term>
2188 Returns the blocking status of the database connection.
2190 int PQisnonblocking(const PGconn *conn);
2195 Returns 1 if the connection is set to nonblocking mode and
2202 <term><function>PQflush</function></term>
2205 Attempts to flush any queued output data to the server.
2206 Returns 0 if successful (or if the send queue is empty), -1 if it failed for
2207 some reason, or 1 if it was unable to send all the data in the send queue yet
2208 (this case can only occur if the connection is nonblocking).
2210 int PQflush(PGconn *conn);
2219 After sending any command or data on a nonblocking connection, call
2220 <function>PQflush</function>. If it returns 1, wait for the socket to be
2221 write-ready and call it again; repeat until it returns 0. Once
2222 <function>PQflush</function> returns 0, wait for the socket to be read-ready
2223 and then read the response as described above.
2228 <sect1 id="libpq-fastpath">
2229 <title>The Fast-Path Interface</title>
2232 <productname>PostgreSQL</productname> provides a fast-path interface to send
2233 simple function calls to the server.
2238 This interface is somewhat obsolete, as one may achieve similar performance
2239 and greater functionality by setting up a prepared statement to define the
2240 function call. Then, executing the statement with binary transmission of
2241 parameters and results substitutes for a fast-path function call.
2246 The function <function>PQfn</function> requests execution of a server
2247 function via the fast-path interface:
2249 PGresult* PQfn(PGconn* conn,
2254 const PQArgBlock *args,
2269 The <parameter>fnid</> argument is the OID of the function to be
2270 executed. <parameter>args</> and <parameter>nargs</> define the
2271 parameters to be passed to the function; they must match the declared
2272 function argument list. When the <parameter>isint</> field of a
2275 the <parameter>u.integer</> value is sent to the server as an integer
2276 of the indicated length (this must be 1, 2, or 4 bytes); proper
2277 byte-swapping occurs. When <parameter>isint</> is false, the
2278 indicated number of bytes at <parameter>*u.ptr</> are sent with no
2279 processing; the data must be in the format expected by the server for
2280 binary transmission of the function's argument datatype.
2281 <parameter>result_buf</parameter> is the buffer in which
2282 to place the return value. The caller must have allocated
2283 sufficient space to store the return value. (There is no check!)
2284 The actual result length will be returned in the integer pointed
2285 to by <parameter>result_len</parameter>.
2286 If a 1, 2, or 4-byte integer result is expected, set
2287 <parameter>result_is_int</parameter> to 1, otherwise set it to 0.
2288 Setting <parameter>result_is_int</parameter> to 1
2289 causes <application>libpq</> to byte-swap the value if necessary, so that
2291 delivered as a proper <type>int</type> value for the client machine. When
2292 <parameter>result_is_int</> is 0, the binary-format byte string sent by
2293 the server is returned unmodified.
2297 <function>PQfn</function> always returns a valid <structname>PGresult</structname> pointer. The result status
2298 should be checked before the result is used. The
2299 caller is responsible for freeing the <structname>PGresult</structname> with
2300 <function>PQclear</function> when it is no longer needed.
2304 Note that it is not possible to handle NULL arguments, NULL results, nor
2305 set-valued results when using this interface.
2310 <sect1 id="libpq-notify">
2311 <title>Asynchronous Notification</title>
2313 <indexterm zone="libpq-notify"><primary>NOTIFY</primary></indexterm>
2316 <productname>PostgreSQL</productname> offers asynchronous notification via the
2317 <command>LISTEN</command> and <command>NOTIFY</command> commands. A client session registers its interest in a particular
2318 notification condition with the <command>LISTEN</command> command (and can stop listening
2319 with the <command>UNLISTEN</command> command). All sessions listening on a
2320 particular condition will be notified asynchronously when a <command>NOTIFY</command> command with that
2321 condition name is executed by any session. No additional information is
2322 passed from the notifier to the listener. Thus, typically, any actual data
2323 that needs to be communicated is transferred through a database table.
2324 Commonly, the condition name is the same as the associated table, but it is
2325 not necessary for there to be any associated table.
2329 <application>libpq</application> applications submit <command>LISTEN</command> and <command>UNLISTEN</command>
2330 commands as ordinary SQL commands. The arrival of <command>NOTIFY</command>
2331 messages can subsequently be detected by calling <function>PQnotifies</function>.
2335 The function <function>PQnotifies</function>
2336 returns the next notification from a list of unhandled
2337 notification messages received from the server. It returns a null pointer if
2338 there are no pending notifications. Once a notification is
2339 returned from <function>PQnotifies</>, it is considered handled and will be
2340 removed from the list of notifications.
2342 PGnotify* PQnotifies(PGconn *conn);
2344 typedef struct pgNotify {
2345 char *relname; /* notification condition name */
2346 int be_pid; /* process ID of server process */
2347 char *extra; /* notification parameter */
2350 After processing a <structname>PGnotify</structname> object returned by
2351 <function>PQnotifies</function>, be sure to free it with
2352 <function>PQfreemem</function>. It is sufficient to free the
2353 <structname>PGnotify</structname> pointer; the
2354 <structfield>relname</structfield> and <structfield>extra</structfield> fields
2355 do not represent separate allocations.
2360 At present the <structfield>extra</structfield> field is unused and will
2361 always point to an empty string.
2367 In <productname>PostgreSQL</productname> 6.4 and later,
2368 the <structfield>be_pid</structfield> is that of the notifying backend process,
2369 whereas in earlier versions it was always the <acronym>PID</acronym> of your own backend process.
2374 <xref linkend="libpq-example-2"> gives a sample program that illustrates the use
2375 of asynchronous notification.
2379 <function>PQnotifies()</function> does not actually read data from the server; it just
2380 returns messages previously absorbed by another <application>libpq</application>
2381 function. In prior releases of <application>libpq</application>, the only way
2382 to ensure timely receipt of <command>NOTIFY</> messages was to constantly submit commands,
2383 even empty ones, and then check <function>PQnotifies()</function> after each
2384 <function>PQexec()</function>. While this still works, it is
2385 deprecated as a waste of processing power.
2389 A better way to check for <command>NOTIFY</>
2390 messages when you have no useful commands to execute is to call
2391 <function>PQconsumeInput()</function>, then check
2392 <function>PQnotifies()</function>.
2393 You can use <function>select()</function> to wait for data to
2394 arrive from the server, thereby using no <acronym>CPU</acronym> power unless there is something
2395 to do. (See <function>PQsocket()</function> to obtain the file descriptor
2396 number to use with <function>select()</function>.)
2397 Note that this will work OK whether you submit commands with
2398 <function>PQsendQuery</function>/<function>PQgetResult</function> or simply
2399 use <function>PQexec</function>. You should, however, remember to
2400 check <function>PQnotifies()</function> after each
2401 <function>PQgetResult</function> or <function>PQexec</function>, to see
2402 if any notifications came in during the processing of the command.
2407 <sect1 id="libpq-copy">
2408 <title>Functions Associated with the <command>COPY</command> Command</title>
2410 <indexterm zone="libpq-copy">
2411 <primary>COPY</primary>
2412 <secondary>with libpq</secondary>
2416 The <command>COPY</command> command in <productname>PostgreSQL</productname>
2417 has options to read from or write to the network connection used by
2418 <application>libpq</application>. The functions described in this section
2419 allow applications to take advantage of this capability by supplying or
2420 consuming copied data.
2424 The overall process is that the application first issues the SQL
2425 <command>COPY</command> command via <function>PQexec</function> or one
2426 of the equivalent functions. The response to this (if there is no error
2427 in the command) will be a <structname>PGresult</> object bearing a status
2428 code of <literal>PGRES_COPY_OUT</literal> or <literal>PGRES_COPY_IN</literal>
2429 (depending on the specified copy direction). The application should then
2430 use the functions of this section to receive or transmit data rows.
2431 When the data transfer is complete, another <structname>PGresult</> object
2432 is returned to indicate success or failure of the transfer. Its status
2433 will be <literal>PGRES_COMMAND_OK</literal> for success or
2434 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
2435 At this point further SQL commands may be issued via
2436 <function>PQexec</function>. (It is not possible to execute other SQL
2437 commands using the same connection while the <command>COPY</command>
2438 operation is in progress.)
2442 If a <command>COPY</command> command is issued via
2443 <function>PQexec</function> in a string that could contain additional
2444 commands, the application must continue fetching results via
2445 <function>PQgetResult</> after completing the <command>COPY</command>
2446 sequence. Only when <function>PQgetResult</> returns NULL is it certain
2447 that the <function>PQexec</function> command string is done and it is
2448 safe to issue more commands.
2452 The functions of this section should be executed only after obtaining a
2453 result status of <literal>PGRES_COPY_OUT</literal> or
2454 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
2455 <function>PQgetResult</function>.
2459 A <structname>PGresult</> object bearing one of these status values
2460 carries some additional data about the <command>COPY</command> operation that
2461 is starting. This additional data is available using functions that are
2462 also used in connection with query results:
2466 <term><function>PQnfields</function></term>
2469 Returns the number of columns (fields) to be copied.
2475 <term><function>PQbinaryTuples</function></term>
2478 0 indicates the overall copy format is textual (rows
2479 separated by newlines, columns separated by separator
2481 1 indicates the overall copy format is binary.
2482 See <xref linkend="sql-copy" endterm="sql-copy-title">
2483 for more information.
2489 <term><function>PQfformat</function></term>
2492 Returns the format code (0 for text, 1 for binary) associated
2493 with each column of the copy operation. The per-column format
2494 codes will always be zero when the overall copy format is textual,
2495 but the binary format can support both text and binary columns.
2496 (However, as of the current implementation of <command>COPY</>,
2497 only binary columns appear in a binary copy; so the per-column
2498 formats always match the overall format at present.)
2507 These additional data values are only available when using protocol 3.0.
2508 When using protocol 2.0, all these functions will return 0.
2512 <sect2 id="libpq-copy-send">
2513 <title>Functions for Sending <command>COPY</command> Data</title>
2516 These functions are used to send data during <literal>COPY FROM STDIN</>.
2517 They will fail if called when the connection is not in <literal>COPY_IN</>
2523 <term><function>PQputCopyData</function></term>
2526 Sends data to the server during <literal>COPY_IN</> state.
2528 int PQputCopyData(PGconn *conn,
2535 Transmits the COPY data in the specified <parameter>buffer</>, of length
2536 <parameter>nbytes</>, to the server. The result is 1 if the data was sent,
2537 zero if it was not sent because the attempt would block (this case is only
2538 possible if the connection is in nonblock mode), or -1 if an error occurred.
2539 (Use <function>PQerrorMessage</function> to retrieve details if the return
2540 value is -1. If the value is zero, wait for write-ready and try again.)
2544 The application may divide the COPY datastream into bufferloads of any
2545 convenient size. Bufferload boundaries have no semantic significance when
2546 sending. The contents of the datastream must match the data format expected
2547 by the <command>COPY</> command; see
2548 <xref linkend="sql-copy" endterm="sql-copy-title"> for details.
2554 <term><function>PQputCopyEnd</function></term>
2557 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
2559 int PQputCopyEnd(PGconn *conn,
2560 const char *errormsg);
2565 Ends the <literal>COPY_IN</> operation successfully if <parameter>errormsg</>
2566 is NULL. If <parameter>errormsg</> is not NULL then the <command>COPY</>
2567 is forced to fail, with the string pointed to by <parameter>errormsg</>
2568 used as the error message. (One should not assume that this exact error
2569 message will come back from the server, however, as the server might have
2570 already failed the <command>COPY</> for its own reasons. Also note that the
2571 option to force failure does not work when using pre-3.0-protocol connections.)
2575 The result is 1 if the termination data was sent,
2576 zero if it was not sent because the attempt would block (this case is only
2577 possible if the connection is in nonblock mode), or -1 if an error occurred.
2578 (Use <function>PQerrorMessage</function> to retrieve details if the return
2579 value is -1. If the value is zero, wait for write-ready and try again.)
2583 After successfully calling <function>PQputCopyEnd</>, call
2584 <function>PQgetResult</> to obtain the final result status of the
2585 <command>COPY</> command. One may wait for
2586 this result to be available in the usual way. Then return to normal
2595 <sect2 id="libpq-copy-receive">
2596 <title>Functions for Receiving <command>COPY</command> Data</title>
2599 These functions are used to receive data during <literal>COPY TO STDOUT</>.
2600 They will fail if called when the connection is not in <literal>COPY_OUT</>
2606 <term><function>PQgetCopyData</function></term>
2609 Receives data from the server during <literal>COPY_OUT</> state.
2611 int PQgetCopyData(PGconn *conn,
2618 Attempts to obtain another row of data from the server during a COPY.
2619 Data is always returned one data row at a time; if only a partial row
2620 is available, it is not returned. Successful return of a data row
2621 involves allocating a chunk of memory to hold the data. The
2622 <parameter>buffer</> parameter must be non-NULL. <parameter>*buffer</>
2623 is set to point to the allocated memory, or to NULL in cases where no
2624 buffer is returned. A non-NULL result buffer must be freed using
2625 <function>PQfreemem</> when no longer needed.
2629 When a row is successfully returned, the return value is the number of
2630 data bytes in the row (this will always be greater than zero). The
2631 returned string is always null-terminated, though this is probably only
2632 useful for textual COPY. A result of zero indicates that the COPY is
2633 still in progress, but no row is yet available (this is only possible
2634 when <parameter>async</> is true). A
2635 result of -1 indicates that the COPY is done.
2636 A result of -2 indicates that an error occurred (consult
2637 <function>PQerrorMessage</> for the reason).
2641 When <parameter>async</> is true (not zero), <function>PQgetCopyData</>
2642 will not block waiting for input; it will return zero if the COPY is still
2643 in progress but no complete row is available. (In this case wait for
2644 read-ready before trying again; it does not matter whether you call
2645 <function>PQconsumeInput</>.) When <parameter>async</> is
2646 false (zero), <function>PQgetCopyData</> will block until data is available
2647 or the operation completes.
2651 After <function>PQgetCopyData</> returns -1, call
2652 <function>PQgetResult</> to obtain the final result status of the
2653 <command>COPY</> command. One may wait for
2654 this result to be available in the usual way. Then return to normal
2663 <sect2 id="libpq-copy-deprecated">
2664 <title>Obsolete Functions for <command>COPY</command></title>
2667 These functions represent older methods of handling <command>COPY</>.
2668 Although they still work, they are deprecated due to poor error handling,
2669 inconvenient methods of detecting end-of-data, and lack of support for binary
2670 or nonblocking transfers.
2675 <term><function>PQgetline</function></term>
2678 Reads a newline-terminated line of characters
2679 (transmitted by the server) into a buffer
2680 string of size <parameter>length</>.
2682 int PQgetline(PGconn *conn,
2689 This function copies up to <parameter>length</>-1 characters
2690 into the buffer and converts
2691 the terminating newline into a zero byte.
2692 <function>PQgetline</function> returns <symbol>EOF</symbol> at the end of input, 0 if the
2693 entire line has been read, and 1 if the buffer is full but the
2694 terminating newline has not yet been read.
2697 Note that the application must check to see if a
2698 new line consists of the two characters <literal>\.</literal>,
2699 which indicates that the server has finished sending
2700 the results of the <command>COPY</command> command.
2701 If the application might
2702 receive lines that are more than <parameter>length</>-1 characters long,
2703 care is needed to be sure it recognizes the <literal>\.</literal> line correctly
2704 (and does not, for example, mistake the end of a long data line
2705 for a terminator line).
2711 <term><function>PQgetlineAsync</function></term>
2714 Reads a row of COPY data
2715 (transmitted by the server) into a buffer
2718 int PQgetlineAsync(PGconn *conn,
2725 This function is similar to <function>PQgetline</function>, but it can be used
2727 that must read <command>COPY</command> data asynchronously, that is, without blocking.
2728 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
2730 application should call <function>PQconsumeInput</function> and
2731 <function>PQgetlineAsync</function> until the
2732 end-of-data signal is detected.
2735 Unlike <function>PQgetline</function>, this function takes
2736 responsibility for detecting end-of-data.
2739 On each call, <function>PQgetlineAsync</function> will return data if a
2740 complete data row is available in <application>libpq</>'s input buffer.
2741 Otherwise, no data is returned until the rest of the row arrives.
2742 The function returns -1 if the end-of-copy-data marker has been recognized,
2743 or 0 if no data is available, or a positive number giving the number of
2744 bytes of data returned. If -1 is returned, the caller must next call
2745 <function>PQendcopy</function>, and then return to normal processing.
2748 The data returned will not extend beyond a data-row boundary. If possible
2749 a whole row will be returned at one time. But if the buffer offered by
2750 the caller is too small to hold a row sent by the server, then a partial
2751 data row will be returned. With textual data this can be detected by testing
2752 whether the last returned byte is <literal>\n</literal> or not. (In a binary
2753 COPY, actual parsing of the COPY data format will be needed to make the
2754 equivalent determination.)
2755 The returned string is not null-terminated. (If you want to add a
2756 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
2757 than the room actually available.)
2763 <term><function>PQputline</function></term>
2766 Sends a null-terminated string to the server.
2767 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
2769 int PQputline(PGconn *conn,
2770 const char *string);
2775 The COPY datastream sent by a series of calls to
2776 <function>PQputline</function> has the same format as that returned by
2777 <function>PQgetlineAsync</function>, except that applications are not
2778 obliged to send exactly one data row per <function>PQputline</function>
2779 call; it is okay to send a partial line or multiple lines per call.
2784 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
2785 for the application to explicitly send the two characters
2786 <literal>\.</literal> as a final line to indicate to the server that it had
2787 finished sending COPY data. While this still works, it is deprecated and the
2788 special meaning of <literal>\.</literal> can be expected to be removed in a
2789 future release. It is sufficient to call <function>PQendcopy</function> after
2790 having sent the actual data.
2797 <term><function>PQputnbytes</function></term>
2800 Sends a non-null-terminated string to the server.
2801 Returns 0 if OK and <symbol>EOF</symbol> if unable to send the string.
2803 int PQputnbytes(PGconn *conn,
2810 This is exactly like <function>PQputline</function>, except that the data
2811 buffer need not be null-terminated since the number of bytes to send is
2812 specified directly. Use this procedure when sending binary data.
2818 <term><function>PQendcopy</function></term>
2821 Synchronizes with the server.
2823 int PQendcopy(PGconn *conn);
2825 This function waits until
2826 the server has finished the copying. It should
2827 either be issued when the last string has been
2828 sent to the server using <function>PQputline</function> or when the
2829 last string has been received from the server
2830 using <function>PGgetline</function>. It must be issued or the server
2831 will get <quote>out of sync</quote> with the client. Upon
2832 return from this function, the server is ready to
2833 receive the next SQL command.
2834 The return value is 0 on successful completion,
2835 nonzero otherwise. (Use <function>PQerrorMessage</function> to retrieve
2836 details if the return value is nonzero.)
2840 When using <function>PQgetResult</function>, the application should respond to
2841 a <literal>PGRES_COPY_OUT</literal> result by executing <function>PQgetline</function>
2842 repeatedly, followed by <function>PQendcopy</function> after the terminator line is seen.
2843 It should then return to the <function>PQgetResult</function> loop until
2844 <function>PQgetResult</function> returns a null pointer. Similarly a <literal>PGRES_COPY_IN</literal>
2845 result is processed by a series of <function>PQputline</function> calls followed by
2846 <function>PQendcopy</function>, then return to the <function>PQgetResult</function> loop.
2847 This arrangement will ensure that
2848 a <command>COPY</command> command embedded in a series of <acronym>SQL</acronym> commands
2849 will be executed correctly.
2853 Older applications are likely to submit a <command>COPY</command>
2854 via <function>PQexec</function> and assume that the transaction is done after
2855 <function>PQendcopy</function>.
2856 This will work correctly only if the <command>COPY</command> is the only
2857 <acronym>SQL</acronym> command in the command string.
2867 <sect1 id="libpq-control">
2868 <title>Control Functions</title>
2871 These functions control miscellaneous details of
2872 <application>libpq</>'s behavior.
2877 <term><function>PQsetErrorVerbosity</function></term>
2880 Determines the verbosity of messages returned by
2881 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
2884 PQERRORS_TERSE, PQERRORS_DEFAULT, PQERRORS_VERBOSE
2887 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
2889 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning the
2890 connection's previous setting.
2891 In TERSE mode, returned messages include severity, primary text, and position
2892 only; this will normally fit on a single line. The DEFAULT mode produces
2893 messages that include the above plus any detail, hint, or context fields
2894 (these may span multiple lines). The VERBOSE mode includes all available
2895 fields. Changing the verbosity does not affect the messages available from
2896 already-existing <structname>PGresult</> objects, only subsequently-created
2903 <term><function>PQtrace</function></term>
2906 Enables tracing of the client/server communication to a debugging file stream.
2908 void PQtrace(PGconn *conn, FILE *stream);
2915 <term><function>PQuntrace</function></term>
2918 Disables tracing started by <function>PQtrace</function>.
2920 void PQuntrace(PGconn *conn);
2929 <sect1 id="libpq-notice-processing">
2930 <title>Notice Processing</title>
2933 Notice and warning messages generated by the server are not returned by the
2934 query execution functions, since they do not imply failure of the query.
2935 Instead they are passed to a notice handling function, and execution continues
2936 normally after the handler returns. The default notice handling function
2937 prints the message on <filename>stderr</filename>, but the application can
2938 override this behavior by supplying its own handling function.
2942 For historical reasons, there are two levels of notice handling, called the
2943 notice receiver and notice processor. The default behavior is for the notice
2944 receiver to format the notice and pass a string to the notice processor
2945 for printing. However, an application that chooses to provide its own notice
2946 receiver will typically ignore the notice processor layer and just do all the
2947 work in the notice receiver.
2951 The function <function>PQsetNoticeReceiver</function>
2952 <indexterm><primary>notice receiver</></>
2953 sets or examines the current notice receiver for a connection object.
2954 Similarly, <function>PQsetNoticeProcessor</function>
2955 <indexterm><primary>notice processor</></>
2956 sets or examines the current notice processor.
2959 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
2962 PQsetNoticeReceiver(PGconn *conn,
2963 PQnoticeReceiver proc,
2966 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
2969 PQsetNoticeProcessor(PGconn *conn,
2970 PQnoticeProcessor proc,
2974 Each of these functions returns the previous notice receiver or processor
2975 function pointer, and sets the new value.
2976 If you supply a null function pointer, no action is taken,
2977 but the current pointer is returned.
2981 When a notice or warning message is received from the server, or generated
2982 internally by <application>libpq</application>, the notice receiver function
2983 is called. It is passed the message in the form of a
2984 <symbol>PGRES_NONFATAL_ERROR</symbol> <structname>PGresult</structname>.
2985 (This allows the receiver to extract individual fields using
2986 <function>PQresultErrorField</>, or the complete preformatted message using
2987 <function>PQresultErrorMessage</>.)
2988 The same void pointer passed to
2989 <function>PQsetNoticeReceiver</function> is also passed.
2990 (This pointer can be used to access application-specific state if needed.)
2994 The default notice receiver simply extracts the message (using
2995 <function>PQresultErrorMessage</>) and passes it to the notice processor.
2999 The notice processor is responsible for handling a notice or warning message
3000 given in text form. It is passed the string text of the message
3001 (including a trailing newline), plus
3002 a void pointer that is the same one passed to
3003 <function>PQsetNoticeProcessor</function>.
3004 (This pointer can be used to access application-specific state if needed.)
3008 The default notice processor is simply
3011 defaultNoticeProcessor(void * arg, const char * message)
3013 fprintf(stderr, "%s", message);
3019 Once you have set a notice receiver or processor, you should expect that that
3020 function could be called as long as either the <structname>PGconn</> object or
3021 <structname>PGresult</> objects made from it exist. At creation of a
3022 <structname>PGresult</>, the <structname>PGconn</>'s current notice handling
3023 pointers are copied into the <structname>PGresult</> for possible use by
3024 functions like <function>PQgetvalue</function>.
3029 <sect1 id="libpq-envars">
3030 <title>Environment Variables</title>
3032 <indexterm zone="libpq-envars">
3033 <primary>environment variables</primary>
3037 The following environment variables can be used to select default
3038 connection parameter values, which will be used by
3039 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
3040 <function>PQsetdb</> if no value is directly specified by the calling
3041 code. These are useful to avoid hard-coding database connection
3042 information into simple client applications, for example.
3048 <primary><envar>PGHOST</envar></primary>
3050 <envar>PGHOST</envar> sets the database server name.
3051 If this begins with a slash, it specifies Unix-domain communication
3052 rather than TCP/IP communication; the value is the name of the
3053 directory in which the socket file is stored (default <filename>/tmp</filename>).
3059 <primary><envar>PGHOSTADDR</envar></primary>
3061 <envar>PGHOSTADDR</envar> specifies the numeric IP address of the database
3062 server. This can be set instead of <envar>PGHOST</envar> to avoid DNS
3063 lookup overhead. See the documentation of
3064 these parameters, under <function>PQconnectdb</function> above, for details
3065 on their interaction.
3071 <primary><envar>PGPORT</envar></primary>
3073 <envar>PGPORT</envar> sets the TCP port number or Unix-domain
3074 socket file extension for communicating with the
3075 <productname>PostgreSQL</productname> server.
3081 <primary><envar>PGDATABASE</envar></primary>
3083 <envar>PGDATABASE</envar> sets the
3084 <productname>PostgreSQL</productname> database name.
3090 <primary><envar>PGUSER</envar></primary>
3092 <envar>PGUSER</envar>
3093 sets the user name used to connect to the database.
3099 <primary><envar>PGPASSWORD</envar></primary>
3101 <envar>PGPASSWORD</envar>
3102 sets the password used if the server demands password
3103 authentication. This environment variable is deprecated for security
3104 reasons; consider migrating to use the <filename>$HOME/.pgpass</>
3105 file (see <xref linkend="libpq-pgpass">).
3111 <primary><envar>PGSERVICE</envar></primary>
3113 <envar>PGSERVICE</envar>
3114 sets the service name to be looked up in <filename>pg_service.conf</filename>.
3115 This offers a shorthand way of setting all the parameters.
3121 <primary><envar>PGREALM</envar></primary>
3123 <envar>PGREALM</envar> sets the Kerberos realm to use with
3124 <productname>PostgreSQL</productname>, if it is different from the local realm.
3125 If <envar>PGREALM</envar> is set, <application>libpq</application>
3126 applications will attempt authentication with servers for this realm and use
3127 separate ticket files to avoid conflicts with local
3128 ticket files. This environment variable is only
3129 used if Kerberos authentication is selected by the server.
3135 <primary><envar>PGOPTIONS</envar></primary>
3137 <envar>PGOPTIONS</envar> sets additional run-time options for
3138 the <productname>PostgreSQL</productname> server.
3144 <primary><envar>PGREQUIRESSL</envar></primary>
3146 <envar>PGREQUIRESSL</envar> sets whether or not the connection must be
3147 made over <acronym>SSL</acronym>. If set to
3148 <quote>1</quote>, <application>libpq</>
3149 will refuse to connect if the server does not accept
3150 an <acronym>SSL</acronym> connection.
3151 This option is only available if
3152 <productname>PostgreSQL</> is compiled with SSL support.
3158 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
3160 <envar>PGCONNECT_TIMEOUT</envar> sets the maximum number of seconds
3161 that <application>libpq</application> will wait when attempting to
3162 connect to the <productname>PostgreSQL</productname> server. If unset
3163 or set to zero, <application>libpq</application> will wait indefinitely.
3164 It is not recommended to set the timeout to less than 2 seconds.
3171 The following environment variables can be used to specify default
3172 behavior for each <productname>PostgreSQL</productname> session.
3173 (See also the <command>ALTER USER</> and <command>ALTER DATABASE</>
3174 commands for ways to set default behavior on a per-user or per-database
3181 <primary><envar>PGDATESTYLE</envar></primary>
3183 <envar>PGDATESTYLE</envar>
3184 sets the default style of date/time representation.
3185 (Equivalent to <literal>SET datestyle TO ...</literal>.)
3191 <primary><envar>PGTZ</envar></primary>
3194 sets the default time zone.
3195 (Equivalent to <literal>SET timezone TO ...</literal>.)
3201 <primary><envar>PGCLIENTENCODING</envar></primary>
3203 <envar>PGCLIENTENCODING</envar>
3204 sets the default client character set encoding.
3205 (Equivalent to <literal>SET client_encoding TO ...</literal>.)
3211 <primary><envar>PGGEQO</envar></primary>
3213 <envar>PGGEQO</envar>
3214 sets the default mode for the genetic query optimizer.
3215 (Equivalent to <literal>SET geqo TO ...</literal>.)
3220 Refer to the <acronym>SQL</acronym> command <command>SET</command>
3221 for information on correct values for these environment variables.
3227 <sect1 id="libpq-pgpass">
3228 <title>The Password File</title>
3230 <indexterm zone="libpq-pgpass">
3231 <primary>password file</primary>
3233 <indexterm zone="libpq-pgpass">
3234 <primary>.pgpass</primary>
3238 The file <filename>.pgpass</filename> in a user's home directory is a file
3239 that can contain passwords to be used if the connection requires a
3240 password (and no password has been specified otherwise).
3241 This file should have lines of the following format:
3243 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
3245 Each of the first four fields may be a literal value, or <literal>*</literal>,
3247 matches anything. The password field from the first line that matches the
3248 current connection parameters will be used. (Therefore, put more-specific
3249 entries first when you are using wildcards.)
3250 If an entry needs to contain <literal>:</literal> or
3251 <literal>\</literal>, escape this character with <literal>\</literal>.
3254 The permissions on <filename>.pgpass</filename> must disallow any
3255 access to world or group; achieve this by the command
3256 <command>chmod 0600 ~/.pgpass</command>.
3257 If the permissions are less strict than this, the file will be ignored.
3261 <sect1 id="libpq-threading">
3262 <title>Threading Behavior</title>
3264 <indexterm zone="libpq-threading">
3265 <primary>threads</primary>
3266 <secondary>with libpq</secondary>
3270 <application>libpq</application> is thread-safe if the library is
3271 compiled using <filename>configure</filename>'s <literal>--with-threads</>
3272 command-line option. (In addition, you might need to
3273 use other threading command-line options to compile your client code.)
3277 One restriction is that no two threads attempt to manipulate the same
3278 <structname>PGconn</> object at the same time. In particular, you cannot
3279 issue concurrent commands from different threads through the same
3280 connection object. (If you need to run concurrent commands, start up
3281 multiple connections.)
3285 <structname>PGresult</> objects are read-only after creation, and so can be
3286 passed around freely between threads.
3290 The deprecated functions <function>PQoidStatus</function> and
3291 <function>fe_setauthsvc</function> are not thread-safe and should not be
3292 used in multithread programs. <function>PQoidStatus</function> can be
3293 replaced by <function>PQoidValue</function>. There is no good reason to
3294 call <function>fe_setauthsvc</function> at all.
3298 <application>libpq</application> applications that use the <literal>crypt</literal>
3299 authentication method rely on the <literal>crypt()</literal> operating
3300 system function, which is often not thread-safe. It is better to use the
3301 <literal>md5</literal> method, which is thread-safe on all
3307 <sect1 id="libpq-build">
3308 <title>Building <application>libpq</application> Programs</title>
3311 To build (i.e., compile and link) your <application>libpq</application> programs you need to
3312 do all of the following things:
3317 Include the <filename>libpq-fe.h</filename> header file:
3319 #include <libpq-fe.h>
3321 If you failed to do that then you will normally get error
3322 messages from your compiler similar to
3324 foo.c: In function `main':
3325 foo.c:34: `PGconn' undeclared (first use in this function)
3326 foo.c:35: `PGresult' undeclared (first use in this function)
3327 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
3328 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
3329 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
3336 Point your compiler to the directory where the <productname>PostgreSQL</> header
3337 files were installed, by supplying the
3338 <literal>-I<replaceable>directory</replaceable></literal> option
3339 to your compiler. (In some cases the compiler will look into
3340 the directory in question by default, so you can omit this
3341 option.) For instance, your compile command line could look
3344 cc -c -I/usr/local/pgsql/include testprog.c
3346 If you are using makefiles then add the option to the
3347 <varname>CPPFLAGS</varname> variable:
3349 CPPFLAGS += -I/usr/local/pgsql/include
3354 <indexterm><primary>pg_config</></>
3355 If there is any chance that your program might be compiled by
3356 other users then you should not hardcode the directory location
3357 like that. Instead, you can run the utility
3358 <command>pg_config</command> to find out where the header files
3359 are on the local system:
3361 <prompt>$</prompt> pg_config --includedir
3362 <computeroutput>/usr/local/include</computeroutput>
3367 Failure to specify the correct option to the compiler will
3368 result in an error message such as
3370 testlibpq.c:8:22: libpq-fe.h: No such file or directory
3377 When linking the final program, specify the option
3378 <literal>-lpq</literal> so that the <application>libpq</application> library gets pulled
3379 in, as well as the option
3380 <literal>-L<replaceable>directory</replaceable></literal> to
3381 point the compiler to the directory where the <application>libpq</application> library resides. (Again, the
3382 compiler will search some directories by default.) For maximum
3383 portability, put the <option>-L</option> option before the
3384 <option>-lpq</option> option. For example:
3386 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
3391 You can find out the library directory using
3392 <command>pg_config</command> as well:
3394 <prompt>$</prompt> pg_config --libdir
3395 <computeroutput>/usr/local/pgsql/lib</computeroutput>
3400 Error messages that point to problems in this area could look
3403 testlibpq.o: In function `main':
3404 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
3405 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
3406 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
3408 This means you forgot <option>-lpq</option>.
3410 /usr/bin/ld: cannot find -lpq
3412 This means you forgot the <option>-L</option> option or did not specify
3413 the right directory.
3420 <indexterm><primary>libpq-int.h</></>
3421 If your codes references the header file
3422 <filename>libpq-int.h</filename> and you refuse to fix your code to
3423 not use it, starting in <productname>PostgreSQL</> 7.2, this file will be found in
3424 <filename><replaceable>includedir</replaceable>/postgresql/internal/libpq-int.h</filename>,
3425 so you need to add the appropriate <option>-I</option> option to
3426 your compiler command line.
3432 <sect1 id="libpq-example">
3433 <title>Example Programs</title>
3436 These examples and others can be found in the
3437 directory <filename>src/test/examples</filename> in the source code
3441 <example id="libpq-example-1">
3442 <title><application>libpq</application> Example Program 1</title>
3448 * Test the C version of LIBPQ, the POSTGRES frontend library.
3450 #include <stdio.h>
3451 #include <stdlib.h>
3452 #include "libpq-fe.h"
3455 exit_nicely(PGconn *conn)
3462 main(int argc, char **argv)
3464 const char *conninfo;
3472 * If the user supplies a parameter on the command line, use it as
3473 * the conninfo string; otherwise default to setting dbname=template1
3474 * and using environment variables or defaults for all other connection
3480 conninfo = "dbname = template1";
3482 /* Make a connection to the database */
3483 conn = PQconnectdb(conninfo);
3485 /* Check to see that the backend connection was successfully made */
3486 if (PQstatus(conn) != CONNECTION_OK)
3488 fprintf(stderr, "Connection to database '%s' failed.\n", PQdb(conn));
3489 fprintf(stderr, "%s", PQerrorMessage(conn));
3494 * Our test case here involves using a cursor, for which we must be
3495 * inside a transaction block. We could do the whole thing with a
3496 * single PQexec() of "select * from pg_database", but that's too
3497 * trivial to make a good example.
3500 /* Start a transaction block */
3501 res = PQexec(conn, "BEGIN");
3502 if (PQresultStatus(res) != PGRES_COMMAND_OK)
3504 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
3510 * Should PQclear PGresult whenever it is no longer needed to avoid
3516 * Fetch rows from pg_database, the system catalog of databases
3518 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
3519 if (PQresultStatus(res) != PGRES_COMMAND_OK)
3521 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
3527 res = PQexec(conn, "FETCH ALL in myportal");
3528 if (PQresultStatus(res) != PGRES_TUPLES_OK)
3530 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
3535 /* first, print out the attribute names */
3536 nFields = PQnfields(res);
3537 for (i = 0; i < nFields; i++)
3538 printf("%-15s", PQfname(res, i));
3541 /* next, print out the rows */
3542 for (i = 0; i < PQntuples(res); i++)
3544 for (j = 0; j < nFields; j++)
3545 printf("%-15s", PQgetvalue(res, i, j));
3551 /* close the portal ... we don't bother to check for errors ... */
3552 res = PQexec(conn, "CLOSE myportal");
3555 /* end the transaction */
3556 res = PQexec(conn, "END");
3559 /* close the connection to the database and cleanup */
3567 <example id="libpq-example-2">
3568 <title><application>libpq</application> Example Program 2</title>
3573 * Test of the asynchronous notification interface
3575 * Start this program, then from psql in another window do
3577 * Repeat four times to get this program to exit.
3579 * Or, if you want to get fancy, try this:
3580 * populate a database with the following commands
3581 * (provided in src/test/examples/testlibpq2.sql):
3583 * CREATE TABLE TBL1 (i int4);
3585 * CREATE TABLE TBL2 (i int4);
3587 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
3588 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
3590 * and do this four times:
3592 * INSERT INTO TBL1 VALUES (10);
3594 #include <stdio.h>
3595 #include <stdlib.h>
3596 #include <string.h>
3597 #include <errno.h>
3598 #include <sys/time.h>
3599 #include "libpq-fe.h"
3602 exit_nicely(PGconn *conn)
3609 main(int argc, char **argv)
3611 const char *conninfo;
3618 * If the user supplies a parameter on the command line, use it as
3619 * the conninfo string; otherwise default to setting dbname=template1
3620 * and using environment variables or defaults for all other connection
3626 conninfo = "dbname = template1";
3628 /* Make a connection to the database */
3629 conn = PQconnectdb(conninfo);
3631 /* Check to see that the backend connection was successfully made */
3632 if (PQstatus(conn) != CONNECTION_OK)
3634 fprintf(stderr, "Connection to database '%s' failed.\n", PQdb(conn));
3635 fprintf(stderr, "%s", PQerrorMessage(conn));
3640 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
3642 res = PQexec(conn, "LISTEN TBL2");
3643 if (PQresultStatus(res) != PGRES_COMMAND_OK)
3645 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
3651 * should PQclear PGresult whenever it is no longer needed to avoid
3656 /* Quit after four notifies are received. */
3658 while (nnotifies < 4)
3661 * Sleep until something happens on the connection. We use select(2)
3662 * to wait for input, but you could also use poll() or similar
3668 sock = PQsocket(conn);
3671 break; /* shouldn't happen */
3673 FD_ZERO(&input_mask);
3674 FD_SET(sock, &input_mask);
3676 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
3678 fprintf(stderr, "select() failed: %s\n", strerror(errno));
3682 /* Now check for input */
3683 PQconsumeInput(conn);
3684 while ((notify = PQnotifies(conn)) != NULL)
3687 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
3688 notify->relname, notify->be_pid);
3694 fprintf(stderr, "Done.\n");
3696 /* close the connection to the database and cleanup */
3704 <example id="libpq-example-3">
3705 <title><application>libpq</application> Example Program 3</>
3710 * Test out-of-line parameters and binary I/O.
3712 * Before running this, populate a database with the following commands
3713 * (provided in src/test/examples/testlibpq3.sql):
3715 * CREATE TABLE test1 (i int4, t text, b bytea);
3717 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
3718 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
3720 * The expected output is:
3724 * t = (11 bytes) 'joe's place'
3725 * b = (5 bytes) \000\001\002\003\004
3728 #include <stdio.h>
3729 #include <stdlib.h>
3730 #include <string.h>
3731 #include <sys/types.h>
3732 #include "libpq-fe.h"
3734 /* for ntohl/htonl */
3735 #include <netinet/in.h>
3736 #include <arpa/inet.h>
3740 exit_nicely(PGconn *conn)
3747 main(int argc, char **argv)
3749 const char *conninfo;
3752 const char *paramValues[1];
3760 * If the user supplies a parameter on the command line, use it as
3761 * the conninfo string; otherwise default to setting dbname=template1
3762 * and using environment variables or defaults for all other connection
3768 conninfo = "dbname = template1";
3770 /* Make a connection to the database */
3771 conn = PQconnectdb(conninfo);
3773 /* Check to see that the backend connection was successfully made */
3774 if (PQstatus(conn) != CONNECTION_OK)
3776 fprintf(stderr, "Connection to database '%s' failed.\n", PQdb(conn));
3777 fprintf(stderr, "%s", PQerrorMessage(conn));
3782 * The point of this program is to illustrate use of PQexecParams()
3783 * with out-of-line parameters, as well as binary transmission of
3784 * results. By using out-of-line parameters we can avoid a lot of
3785 * tedious mucking about with quoting and escaping. Notice how we
3786 * don't have to do anything special with the quote mark in the
3790 /* Here is our out-of-line parameter value */
3791 paramValues[0] = "joe's place";
3793 res = PQexecParams(conn,
3794 "SELECT * FROM test1 WHERE t = $1",
3796 NULL, /* let the backend deduce param type */
3798 NULL, /* don't need param lengths since text */
3799 NULL, /* default to all text params */
3800 1); /* ask for binary results */
3802 if (PQresultStatus(res) != PGRES_TUPLES_OK)
3804 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
3809 /* Use PQfnumber to avoid assumptions about field order in result */
3810 i_fnum = PQfnumber(res, "i");
3811 t_fnum = PQfnumber(res, "t");
3812 b_fnum = PQfnumber(res, "b");
3814 for (i = 0; i < PQntuples(res); i++)
3822 /* Get the field values (we ignore possibility they are null!) */
3823 iptr = PQgetvalue(res, i, i_fnum);
3824 tptr = PQgetvalue(res, i, t_fnum);
3825 bptr = PQgetvalue(res, i, b_fnum);
3828 * The binary representation of INT4 is in network byte order,
3829 * which we'd better coerce to the local byte order.
3831 ival = ntohl(*((uint32_t *) iptr));
3834 * The binary representation of TEXT is, well, text, and since
3835 * libpq was nice enough to append a zero byte to it, it'll work
3836 * just fine as a C string.
3838 * The binary representation of BYTEA is a bunch of bytes, which
3839 * could include embedded nulls so we have to pay attention to
3842 blen = PQgetlength(res, i, b_fnum);
3844 printf("tuple %d: got\n", i);
3845 printf(" i = (%d bytes) %d\n",
3846 PQgetlength(res, i, i_fnum), ival);
3847 printf(" t = (%d bytes) '%s'\n",
3848 PQgetlength(res, i, t_fnum), tptr);
3849 printf(" b = (%d bytes) ", blen);
3850 for (j = 0; j < blen; j++)
3851 printf("\\%03o", bptr[j]);
3857 /* close the connection to the database and cleanup */
3868 <!-- Keep this comment at the end of the file
3873 sgml-minimize-attributes:nil
3874 sgml-always-quote-attributes:t
3877 sgml-parent-document:nil
3878 sgml-default-dtd-file:"./reference.ced"
3879 sgml-exposed-tags:nil
3880 sgml-local-catalogs:("/usr/lib/sgml/catalog")
3881 sgml-local-ecat-files:nil
projects / postgresql / blob