1 <!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.259 2008/06/23 21:10:49 momjian Exp $ -->
4 <title><application>libpq</application> - C Library</title>
6 <indexterm zone="libpq">
7 <primary>libpq</primary>
10 <indexterm zone="libpq">
15 <application>libpq</application> is the <acronym>C</acronym>
16 application programmer's interface to <productname>PostgreSQL</>.
17 <application>libpq</> is a set of library functions that allow
18 client programs to pass queries to the <productname>PostgreSQL</>
19 backend server and to receive the results of these queries.
23 <application>libpq</> is also the underlying engine for several
24 other <productname>PostgreSQL</> application interfaces, including
25 those written for C++, Perl, Python, Tcl and <application>ECPG</>.
26 So some aspects of <application>libpq</>'s behavior will be
27 important to you if you use one of those packages. In particular,
28 <xref linkend="libpq-envars">,
29 <xref linkend="libpq-pgpass"> and
30 <xref linkend="libpq-ssl">
31 describe behavior that is visible to the user of any application
32 that uses <application>libpq</>.
36 Some short programs are included at the end of this chapter (<xref linkend="libpq-example">) to show how
37 to write programs that use <application>libpq</application>. There are also several
38 complete examples of <application>libpq</application> applications in the
39 directory <filename>src/test/examples</filename> in the source code distribution.
43 Client programs that use <application>libpq</application> must
44 include the header file
45 <filename>libpq-fe.h</filename><indexterm><primary>libpq-fe.h</></>
46 and must link with the <application>libpq</application> library.
49 <sect1 id="libpq-connect">
50 <title>Database Connection Control Functions</title>
53 The following functions deal with making a connection to a
54 <productname>PostgreSQL</productname> backend server. An
55 application program can have several backend connections open at
56 one time. (One reason to do that is to access more than one
57 database.) Each connection is represented by a
58 <structname>PGconn</><indexterm><primary>PGconn</></> object, which
59 is obtained from the function <function>PQconnectdb</> or
60 <function>PQsetdbLogin</>. Note that these functions will always
61 return a non-null object pointer, unless perhaps there is too
62 little memory even to allocate the <structname>PGconn</> object.
63 The <function>PQstatus</> function should be called to check
64 whether a connection was successfully made before queries are sent
65 via the connection object.
69 <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
72 Makes a new connection to the database server.
75 PGconn *PQconnectdb(const char *conninfo);
80 This function opens a new database connection using the parameters taken
81 from the string <literal>conninfo</literal>. Unlike <function>PQsetdbLogin</> below,
82 the parameter set can be extended without changing the function signature,
83 so use of this function (or its nonblocking analogues <function>PQconnectStart</>
84 and <function>PQconnectPoll</function>) is preferred for new application programming.
89 can be empty to use all default parameters, or it can contain one or more
90 parameter settings separated by whitespace.
91 Each parameter setting is in the form <literal>keyword = value</literal>.
92 Spaces around the equal sign are optional.
93 To write an empty value or a value containing
94 spaces, surround it with single quotes, e.g.,
95 <literal>keyword = 'a value'</literal>.
96 Single quotes and backslashes within the value must be escaped with a
97 backslash, i.e., <literal>\'</literal> and <literal>\\</literal>.
101 The currently recognized parameter key words are:
105 <term><literal>host</literal></term>
108 Name of host to connect to.<indexterm><primary>host name</></>
109 If this begins with a slash, it specifies Unix-domain
110 communication rather than TCP/IP communication; the value is the
111 name of the directory in which the socket file is stored. The
112 default behavior when <literal>host</literal> is not specified
113 is to connect to a Unix-domain
114 socket<indexterm><primary>Unix domain socket</></> in
115 <filename>/tmp</filename> (or whatever socket directory was specified
116 when <productname>PostgreSQL</> was built). On machines without
117 Unix-domain sockets, the default is to connect to <literal>localhost</>.
123 <term><literal>hostaddr</literal></term>
126 Numeric IP address of host to connect to. This should be in the
127 standard IPv4 address format, e.g., <literal>172.28.40.9</>. If
128 your machine supports IPv6, you can also use those addresses.
129 TCP/IP communication is
130 always used when a nonempty string is specified for this parameter.
134 Using <literal>hostaddr</> instead of <literal>host</> allows the
135 application to avoid a host name look-up, which might be important in
136 applications with time constraints. However, Kerberos and GSSAPI authentication
137 requires the host name. The following therefore applies: If
138 <literal>host</> is specified without <literal>hostaddr</>, a host name
139 lookup occurs. If <literal>hostaddr</> is specified without
140 <literal>host</>, the value for <literal>hostaddr</> gives the remote
141 address. When Kerberos is used, a reverse name query occurs to obtain
142 the host name for Kerberos. If both
143 <literal>host</> and <literal>hostaddr</> are specified, the value for
144 <literal>hostaddr</> gives the remote address; the value for
145 <literal>host</> is ignored, unless Kerberos is used, in which case that
146 value is used for Kerberos authentication. (Note that authentication is
147 likely to fail if <application>libpq</application> is passed a host name
148 that is not the name of the machine at <literal>hostaddr</>.) Also,
149 <literal>host</> rather than <literal>hostaddr</> is used to identify
150 the connection in <filename>~/.pgpass</> (see
151 <xref linkend="libpq-pgpass">).
155 Without either a host name or host address,
156 <application>libpq</application> will connect using a
157 local Unix-domain socket; or on machines without Unix-domain
158 sockets, it will attempt to connect to <literal>localhost</>.
164 <term><literal>port</literal></term>
167 Port number to connect to at the server host, or socket file
168 name extension for Unix-domain
169 connections.<indexterm><primary>port</></>
175 <term><literal>dbname</literal></term>
178 The database name. Defaults to be the same as the user name.
184 <term><literal>user</literal></term>
187 <productname>PostgreSQL</productname> user name to connect as.
188 Defaults to be the same as the operating system name of the user
189 running the application.
195 <term><literal>password</literal></term>
198 Password to be used if the server demands password authentication.
204 <term><literal>connect_timeout</literal></term>
207 Maximum wait for connection, in seconds (write as a decimal integer
208 string). Zero or not specified means wait indefinitely. It is not
209 recommended to use a timeout of less than 2 seconds.
215 <term><literal>options</literal></term>
218 Command-line options to be sent to the server.
224 <term><literal>tty</literal></term>
227 Ignored (formerly, this specified where to send server debug output).
233 <term><literal>sslmode</literal></term>
236 This option determines whether or with what priority an
237 <acronym>SSL</> connection will be negotiated with the
238 server. There are four modes: <literal>disable</> will attempt
239 only an unencrypted <acronym>SSL</> connection;
240 <literal>allow</> will negotiate, trying first a
241 non-<acronym>SSL</> connection, then if that fails, trying an
242 <acronym>SSL</> connection; <literal>prefer</> (the default)
243 will negotiate, trying first an <acronym>SSL</> connection,
244 then if that fails, trying a regular non-<acronym>SSL</>
245 connection; <literal>require</> will try only an
246 <acronym>SSL</> connection.
250 If <productname>PostgreSQL</> is compiled without SSL support,
251 using option <literal>require</> will cause an error, while
252 options <literal>allow</> and <literal>prefer</> will be
253 accepted but <application>libpq</> will not in fact attempt
255 connection.<indexterm><primary>SSL</><secondary
256 sortas="libpq">with libpq</></indexterm>
262 <term><literal>requiressl</literal></term>
265 This option is deprecated in favor of the <literal>sslmode</>
270 If set to 1, an <acronym>SSL</acronym> connection to the server
271 is required (this is equivalent to <literal>sslmode</>
272 <literal>require</>). <application>libpq</> will then refuse
273 to connect if the server does not accept an
274 <acronym>SSL</acronym> connection. If set to 0 (default),
275 <application>libpq</> will negotiate the connection type with
276 the server (equivalent to <literal>sslmode</>
277 <literal>prefer</>). This option is only available if
278 <productname>PostgreSQL</> is compiled with SSL support.
284 <term><literal>krbsrvname</literal></term>
287 Kerberos service name to use when authenticating with Kerberos 5
289 This must match the service name specified in the server
290 configuration for Kerberos authentication to succeed. (See also
291 <xref linkend="kerberos-auth"> and <xref linkend="gssapi-auth">.)
297 <term><literal>gsslib</literal></term>
300 GSS library to use for GSSAPI authentication. Only used on Windows.
301 Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
302 library for authentication instead of the default SSPI.
308 <term><literal>service</literal></term>
311 Service name to use for additional parameters. It specifies a service
312 name in <filename>pg_service.conf</filename> that holds additional connection parameters.
313 This allows applications to specify only a service name so connection parameters
314 can be centrally maintained. See <xref linkend="libpq-pgservice">.
320 If any parameter is unspecified, then the corresponding
321 environment variable (see <xref linkend="libpq-envars">)
322 is checked. If the environment variable is not set either,
323 then the indicated built-in defaults are used.
329 <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
332 Makes a new connection to the database server.
334 PGconn *PQsetdbLogin(const char *pghost,
336 const char *pgoptions,
345 This is the predecessor of <function>PQconnectdb</function> with a fixed
346 set of parameters. It has the same functionality except that the
347 missing parameters will always take on default values. Write <symbol>NULL</symbol> or an
348 empty string for any one of the fixed parameters that is to be defaulted.
352 If the <parameter>dbName</parameter> contains an <symbol>=</symbol> sign, it
353 is taken as a <parameter>conninfo</parameter> string in exactly the same way as
354 if it had been passed to <function>PQconnectdb</function>, and the remaining
355 parameters are then applied as above.
361 <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
364 Makes a new connection to the database server.
366 PGconn *PQsetdb(char *pghost,
375 This is a macro that calls <function>PQsetdbLogin</function> with null pointers
376 for the <parameter>login</> and <parameter>pwd</> parameters. It is provided
377 for backward compatibility with very old programs.
383 <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
384 <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
387 <indexterm><primary>nonblocking connection</primary></indexterm>
388 Make a connection to the database server in a nonblocking manner.
391 PGconn *PQconnectStart(const char *conninfo);
395 PostgresPollingStatusType PQconnectPoll(PGconn *conn);
400 These two functions are used to open a connection to a database server such
401 that your application's thread of execution is not blocked on remote I/O
403 The point of this approach is that the waits for I/O to complete can occur
404 in the application's main loop, rather than down inside
405 <function>PQconnectdb</>, and so the application can manage this
406 operation in parallel with other activities.
410 The database connection is made using the parameters taken from the string
411 <literal>conninfo</literal>, passed to <function>PQconnectStart</function>. This string is in
412 the same format as described above for <function>PQconnectdb</function>.
415 Neither <function>PQconnectStart</function> nor <function>PQconnectPoll</function> will block, so long as a number of
416 restrictions are met:
420 The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
421 name and reverse name queries are not made. See the documentation of
422 these parameters under <function>PQconnectdb</function> above for details.
428 If you call <function>PQtrace</function>, ensure that the stream object
429 into which you trace will not block.
435 You ensure that the socket is in the appropriate state
436 before calling <function>PQconnectPoll</function>, as described below.
443 To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
444 If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
445 structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
446 representing a valid connection to the database). On return from
447 <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
448 <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
452 If <function>PQconnectStart</> succeeds, the next stage is to poll
453 <application>libpq</> so that it can proceed with the connection sequence.
454 Use <function>PQsocket(conn)</function> to obtain the descriptor of the
455 socket underlying the database connection.
456 Loop thus: If <function>PQconnectPoll(conn)</function> last returned
457 <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
458 read (as indicated by <function>select()</>, <function>poll()</>, or
459 similar system function).
460 Then call <function>PQconnectPoll(conn)</function> again.
461 Conversely, if <function>PQconnectPoll(conn)</function> last returned
462 <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
463 to write, then call <function>PQconnectPoll(conn)</function> again.
464 If you have yet to call
465 <function>PQconnectPoll</function>, i.e., just after the call to
466 <function>PQconnectStart</function>, behave as if it last returned
467 <symbol>PGRES_POLLING_WRITING</symbol>. Continue this loop until
468 <function>PQconnectPoll(conn)</function> returns
469 <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
470 has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
471 has been successfully made.
475 At any time during connection, the status of the connection can be
476 checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
477 connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
478 connection is ready. Both of these states are equally detectable
479 from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
480 during (and only during) an asynchronous connection procedure. These
481 indicate the current stage of the connection procedure and might be useful
482 to provide feedback to the user for example. These statuses are:
486 <term><symbol>CONNECTION_STARTED</symbol></term>
489 Waiting for connection to be made.
495 <term><symbol>CONNECTION_MADE</symbol></term>
498 Connection OK; waiting to send.
504 <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
507 Waiting for a response from the server.
513 <term><symbol>CONNECTION_AUTH_OK</symbol></term>
516 Received authentication; waiting for backend start-up to finish.
522 <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
525 Negotiating SSL encryption.
531 <term><symbol>CONNECTION_SETENV</symbol></term>
534 Negotiating environment-driven parameter settings.
540 Note that, although these constants will remain (in order to maintain
541 compatibility), an application should never rely upon these occurring in a
542 particular order, or at all, or on the status always being one of these
543 documented values. An application might do something like this:
545 switch(PQstatus(conn))
547 case CONNECTION_STARTED:
548 feedback = "Connecting...";
551 case CONNECTION_MADE:
552 feedback = "Connected to server...";
558 feedback = "Connecting...";
564 The <literal>connect_timeout</literal> connection parameter is ignored
565 when using <function>PQconnectPoll</function>; it is the application's
566 responsibility to decide whether an excessive amount of time has elapsed.
567 Otherwise, <function>PQconnectStart</function> followed by a
568 <function>PQconnectPoll</function> loop is equivalent to
569 <function>PQconnectdb</function>.
573 Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
574 <function>PQfinish</function> when you are finished with it, in order to dispose of
575 the structure and any associated memory blocks. This must be done even if
576 the connection attempt fails or is abandoned.
582 <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
585 Returns the default connection options.
587 PQconninfoOption *PQconndefaults(void);
591 char *keyword; /* The keyword of the option */
592 char *envvar; /* Fallback environment variable name */
593 char *compiled; /* Fallback compiled in default value */
594 char *val; /* Option's current value, or NULL */
595 char *label; /* Label for field in connect dialog */
596 char *dispchar; /* Character to display for this field
597 in a connect dialog. Values are:
598 "" Display entered value as is
599 "*" Password field - hide value
600 "D" Debug option - don't show by default */
601 int dispsize; /* Field size in characters for dialog */
607 Returns a connection options array. This can be used to determine
608 all possible <function>PQconnectdb</function> options and their
609 current default values. The return value points to an array of
610 <structname>PQconninfoOption</structname> structures, which ends
611 with an entry having a null <structfield>keyword</> pointer. The
612 null pointer is returned if memory could not be allocated. Note that
613 the current default values (<structfield>val</structfield> fields)
614 will depend on environment variables and other context. Callers
615 must treat the connection options data as read-only.
619 After processing the options array, free it by passing it to
620 <function>PQconninfoFree</function>. If this is not done, a small amount of memory
621 is leaked for each call to <function>PQconndefaults</function>.
628 <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
631 Closes the connection to the server. Also frees
632 memory used by the <structname>PGconn</structname> object.
634 void PQfinish(PGconn *conn);
639 Note that even if the server connection attempt fails (as
640 indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
641 to free the memory used by the <structname>PGconn</structname> object.
642 The <structname>PGconn</> pointer must not be used again after
643 <function>PQfinish</function> has been called.
649 <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
652 Resets the communication channel to the server.
654 void PQreset(PGconn *conn);
659 This function will close the connection
660 to the server and attempt to reestablish a new
661 connection to the same server, using all the same
662 parameters previously used. This might be useful for
663 error recovery if a working connection is lost.
669 <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
670 <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
673 Reset the communication channel to the server, in a nonblocking manner.
676 int PQresetStart(PGconn *conn);
679 PostgresPollingStatusType PQresetPoll(PGconn *conn);
684 These functions will close the connection to the server and attempt to
685 reestablish a new connection to the same server, using all the same
686 parameters previously used. This can be useful for error recovery if a
687 working connection is lost. They differ from <function>PQreset</function> (above) in that they
688 act in a nonblocking manner. These functions suffer from the same
689 restrictions as <function>PQconnectStart</> and <function>PQconnectPoll</>.
693 To initiate a connection reset, call
694 <function>PQresetStart</function>. If it returns 0, the reset has
695 failed. If it returns 1, poll the reset using
696 <function>PQresetPoll</function> in exactly the same way as you
697 would create the connection using <function>PQconnectPoll</function>.
706 <sect1 id="libpq-status">
707 <title>Connection Status Functions</title>
710 These functions can be used to interrogate the status
711 of an existing database connection object.
716 <indexterm><primary>libpq-fe.h</></>
717 <indexterm><primary>libpq-int.h</></>
718 <application>libpq</application> application programmers should be careful to
719 maintain the <structname>PGconn</structname> abstraction. Use the accessor
720 functions described below to get at the contents of <structname>PGconn</structname>.
721 Reference to internal <structname>PGconn</structname> fields using
722 <filename>libpq-int.h</> is not recommended because they are subject to change
728 The following functions return parameter values established at connection.
729 These values are fixed for the life of the <structname>PGconn</> object.
734 <function>PQdb</function>
736 <primary>PQdb</primary>
742 Returns the database name of the connection.
744 char *PQdb(const PGconn *conn);
752 <function>PQuser</function>
754 <primary>PQuser</primary>
760 Returns the user name of the connection.
762 char *PQuser(const PGconn *conn);
770 <function>PQpass</function>
772 <primary>PQpass</primary>
778 Returns the password of the connection.
780 char *PQpass(const PGconn *conn);
788 <function>PQhost</function>
790 <primary>PQhost</primary>
796 Returns the server host name of the connection.
798 char *PQhost(const PGconn *conn);
806 <function>PQport</function>
808 <primary>PQport</primary>
814 Returns the port of the connection.
817 char *PQport(const PGconn *conn);
825 <function>PQtty</function>
827 <primary>PQtty</primary>
833 Returns the debug <acronym>TTY</acronym> of the connection.
834 (This is obsolete, since the server no longer pays attention
835 to the <acronym>TTY</acronym> setting, but the function remains
836 for backwards compatibility.)
839 char *PQtty(const PGconn *conn);
847 <function>PQoptions</function>
849 <primary>PQoptions</primary>
855 Returns the command-line options passed in the connection request.
857 char *PQoptions(const PGconn *conn);
866 The following functions return status data that can change as operations
867 are executed on the <structname>PGconn</> object.
872 <function>PQstatus</function>
874 <primary>PQstatus</primary>
880 Returns the status of the connection.
882 ConnStatusType PQstatus(const PGconn *conn);
887 The status can be one of a number of values. However, only two of
888 these are seen outside of an asynchronous connection procedure:
889 <literal>CONNECTION_OK</literal> and
890 <literal>CONNECTION_BAD</literal>. A good connection to the database
891 has the status <literal>CONNECTION_OK</literal>. A failed
892 connection attempt is signaled by status
893 <literal>CONNECTION_BAD</literal>. Ordinarily, an OK status will
894 remain so until <function>PQfinish</function>, but a communications
895 failure might result in the status changing to
896 <literal>CONNECTION_BAD</literal> prematurely. In that case the
897 application could try to recover by calling
898 <function>PQreset</function>.
902 See the entry for <function>PQconnectStart</> and <function>PQconnectPoll</> with regards
903 to other status codes
911 <function>PQtransactionStatus</function>
913 <primary>PQtransactionStatus</primary>
919 Returns the current in-transaction status of the server.
922 PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
925 The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
926 <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
927 <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
928 or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
929 <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
930 <literal>PQTRANS_ACTIVE</literal> is reported only when a query
931 has been sent to the server and not yet completed.
936 <function>PQtransactionStatus</> will give incorrect results when using
937 a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
938 set to off. The server-side autocommit feature has been
939 deprecated and does not exist in later server versions.
947 <function>PQparameterStatus</function>
949 <primary>PQparameterStatus</primary>
955 Looks up a current parameter setting of the server.
958 const char *PQparameterStatus(const PGconn *conn, const char *paramName);
961 Certain parameter values are reported by the server automatically at
962 connection startup or whenever their values change.
963 <function>PQparameterStatus</> can be used to interrogate these settings.
964 It returns the current value of a parameter if known, or <symbol>NULL</symbol>
965 if the parameter is not known.
969 Parameters reported as of the current release include
970 <literal>server_version</>,
971 <literal>server_encoding</>,
972 <literal>client_encoding</>,
973 <literal>is_superuser</>,
974 <literal>session_authorization</>,
975 <literal>DateStyle</>,
976 <literal>TimeZone</>,
977 <literal>integer_datetimes</>, and
978 <literal>standard_conforming_strings</>.
979 (<literal>server_encoding</>, <literal>TimeZone</>, and
980 <literal>integer_datetimes</> were not reported by releases before 8.0;
981 <literal>standard_conforming_strings</> was not reported by releases
984 <literal>server_version</>,
985 <literal>server_encoding</> and
986 <literal>integer_datetimes</>
987 cannot change after startup.
991 Pre-3.0-protocol servers do not report parameter settings, but
992 <application>libpq</> includes logic to obtain values for
993 <literal>server_version</> and <literal>client_encoding</> anyway.
994 Applications are encouraged to use <function>PQparameterStatus</>
995 rather than <foreignphrase>ad hoc</> code to determine these values.
996 (Beware however that on a pre-3.0 connection, changing
997 <literal>client_encoding</> via <command>SET</> after connection
998 startup will not be reflected by <function>PQparameterStatus</>.)
999 For <literal>server_version</>, see also
1000 <function>PQserverVersion</>, which returns the information in a
1001 numeric form that is much easier to compare against.
1005 If no value for <literal>standard_conforming_strings</> is reported,
1006 applications can assume it is <literal>off</>, that is, backslashes
1007 are treated as escapes in string literals. Also, the presence of
1008 this parameter can be taken as an indication that the escape string
1009 syntax (<literal>E'...'</>) is accepted.
1013 Although the returned pointer is declared <literal>const</>, it in fact
1014 points to mutable storage associated with the <literal>PGconn</> structure.
1015 It is unwise to assume the pointer will remain valid across queries.
1022 <function>PQprotocolVersion</function>
1024 <primary>PQprotocolVersion</primary>
1030 Interrogates the frontend/backend protocol being used.
1032 int PQprotocolVersion(const PGconn *conn);
1034 Applications might wish to use this to determine whether certain
1035 features are supported. Currently, the possible values are 2 (2.0
1036 protocol), 3 (3.0 protocol), or zero (connection bad). This will
1037 not change after connection startup is complete, but it could
1038 theoretically change during a connection reset. The 3.0 protocol
1039 will normally be used when communicating with
1040 <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
1041 support only protocol 2.0. (Protocol 1.0 is obsolete and not
1042 supported by <application>libpq</application>.)
1049 <function>PQserverVersion</function>
1051 <primary>PQserverVersion</primary>
1057 Returns an integer representing the backend version.
1059 int PQserverVersion(const PGconn *conn);
1061 Applications might use this to determine the version of the database
1062 server they are connected to. The number is formed by converting
1063 the major, minor, and revision numbers into two-decimal-digit
1064 numbers and appending them together. For example, version 8.1.5
1065 will be returned as 80105, and version 8.2 will be returned as
1066 80200 (leading zeroes are not shown). Zero is returned if the
1074 <function>PQerrorMessage</function>
1076 <primary>PQerrorMessage</primary>
1082 <indexterm><primary>error message</></> Returns the error message
1083 most recently generated by an operation on the connection.
1086 char *PQerrorMessage(const PGconn *conn);
1092 Nearly all <application>libpq</> functions will set a message for
1093 <function>PQerrorMessage</function> if they fail. Note that by
1094 <application>libpq</application> convention, a nonempty
1095 <function>PQerrorMessage</function> result will include a trailing
1096 newline. The caller should not free the result directly. It will
1097 be freed when the associated <structname>PGconn</> handle is passed
1098 to <function>PQfinish</function>. The result string should not be
1099 expected to remain the same across operations on the
1100 <literal>PGconn</> structure.
1106 <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1109 Obtains the file descriptor number of the connection socket to
1110 the server. A valid descriptor will be greater than or equal
1111 to 0; a result of -1 indicates that no server connection is
1112 currently open. (This will not change during normal operation,
1113 but could change during connection setup or reset.)
1116 int PQsocket(const PGconn *conn);
1124 <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1127 Returns the process <acronym>ID</acronym>
1128 (PID)<indexterm><primary>PID</><secondary>determining PID of
1129 server process</><tertiary>in libpq</></> of the backend server
1130 process handling this connection.
1133 int PQbackendPID(const PGconn *conn);
1138 The backend <acronym>PID</acronym> is useful for debugging
1139 purposes and for comparison to <command>NOTIFY</command>
1140 messages (which include the <acronym>PID</acronym> of the
1141 notifying backend process). Note that the
1142 <acronym>PID</acronym> belongs to a process executing on the
1143 database server host, not the local host!
1149 <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
1152 Returns true (1) if the connection authentication method
1153 required a password, but none was available.
1154 Returns false (0) if not.
1157 int PQconnectionNeedsPassword(const PGconn *conn);
1163 This function can be applied after a failed connection attempt
1164 to decide whether to prompt the user for a password.
1170 <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
1173 Returns true (1) if the connection authentication method
1174 used a caller-supplied password. Returns false (0) if not.
1177 int PQconnectionUsedPassword(const PGconn *conn);
1183 This function detects whether a password supplied to the connection
1184 function was actually used. Passwords obtained from other
1185 sources (such as the <filename>.pgpass</> file) are not considered
1192 <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1195 <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1196 Returns the SSL structure used in the connection, or null
1197 if SSL is not in use.
1200 SSL *PQgetssl(const PGconn *conn);
1205 This structure can be used to verify encryption levels, check server
1206 certificates, and more. Refer to the <productname>OpenSSL</>
1207 documentation for information about this structure.
1211 You must define <symbol>USE_SSL</symbol> in order to get the
1212 correct prototype for this function. Doing this will also
1213 automatically include <filename>ssl.h</filename> from <productname>OpenSSL</productname>.
1223 <sect1 id="libpq-exec">
1224 <title>Command Execution Functions</title>
1227 Once a connection to a database server has been successfully
1228 established, the functions described here are used to perform
1229 SQL queries and commands.
1232 <sect2 id="libpq-exec-main">
1233 <title>Main Functions</title>
1239 <function>PQexec</function>
1241 <primary>PQexec</primary>
1247 Submits a command to the server and waits for the result.
1250 PGresult *PQexec(PGconn *conn, const char *command);
1255 Returns a <structname>PGresult</structname> pointer or possibly a null
1256 pointer. A non-null pointer will generally be returned except in
1257 out-of-memory conditions or serious errors such as inability to send
1258 the command to the server. If a null pointer is returned, it should
1259 be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
1260 <function>PQerrorMessage</function> to get more information about such
1267 It is allowed to include multiple SQL commands (separated by semicolons)
1268 in the command string. Multiple queries sent in a single
1269 <function>PQexec</> call are processed in a single transaction, unless
1270 there are explicit <command>BEGIN</command>/<command>COMMIT</command>
1271 commands included in the query string to divide it into multiple
1272 transactions. Note however that the returned
1273 <structname>PGresult</structname> structure describes only the result
1274 of the last command executed from the string. Should one of the
1275 commands fail, processing of the string stops with it and the returned
1276 <structname>PGresult</structname> describes the error condition.
1283 <function>PQexecParams</function>
1285 <primary>PQexecParams</primary>
1291 Submits a command to the server and waits for the result,
1292 with the ability to pass parameters separately from the SQL
1296 PGresult *PQexecParams(PGconn *conn,
1297 const char *command,
1299 const Oid *paramTypes,
1300 const char * const *paramValues,
1301 const int *paramLengths,
1302 const int *paramFormats,
1308 <function>PQexecParams</> is like <function>PQexec</>, but offers additional
1309 functionality: parameter values can be specified separately from the command
1310 string proper, and query results can be requested in either text or binary
1311 format. <function>PQexecParams</> is supported only in protocol 3.0 and later
1312 connections; it will fail when using protocol 2.0.
1316 The function arguments are:
1320 <term><parameter>conn</parameter></term>
1324 The connection object to send the command through.
1330 <term><parameter>command</parameter></term>
1333 The SQL command string to be executed. If parameters are used,
1334 they are referred to in the command string as <literal>$1</>,
1335 <literal>$2</>, etc.
1341 <term><parameter>nParams</parameter></term>
1344 The number of parameters supplied; it is the length of the arrays
1345 <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
1346 <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
1347 array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
1354 <term><parameter>paramTypes[]</parameter></term>
1357 Specifies, by OID, the data types to be assigned to the
1358 parameter symbols. If <parameter>paramTypes</> is
1359 <symbol>NULL</symbol>, or any particular element in the array
1360 is zero, the server infers a data type for the parameter symbol
1361 in the same way it would do for an untyped literal string.
1367 <term><parameter>paramValues[]</parameter></term>
1370 Specifies the actual values of the parameters. A null pointer
1371 in this array means the corresponding parameter is null;
1372 otherwise the pointer points to a zero-terminated text string
1373 (for text format) or binary data in the format expected by the
1374 server (for binary format).
1380 <term><parameter>paramLengths[]</parameter></term>
1383 Specifies the actual data lengths of binary-format parameters.
1384 It is ignored for null parameters and text-format parameters.
1385 The array pointer can be null when there are no binary parameters.
1391 <term><parameter>paramFormats[]</parameter></term>
1394 Specifies whether parameters are text (put a zero in the
1395 array entry for the corresponding parameter) or binary (put
1396 a one in the array entry for the corresponding parameter).
1397 If the array pointer is null then all parameters are presumed
1401 Values passed in binary format require knowlege of
1402 the internal representation expected by the backend.
1403 For example, integers must be passed in network byte
1404 order. Passing <type>numeric</> values requires
1405 knowledge of the server storage format, as implemented
1406 in <filename>src/backend/utils/adt/numeric.c</>.
1412 <term><parameter>resultFormat</parameter></term>
1415 Specify zero to obtain results in text format, or one to obtain
1416 results in binary format. (There is not currently a provision
1417 to obtain different result columns in different formats,
1418 although that is possible in the underlying protocol.)
1430 The primary advantage of <function>PQexecParams</> over
1431 <function>PQexec</> is that parameter values can be separated from the
1432 command string, thus avoiding the need for tedious and error-prone
1433 quoting and escaping.
1437 Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
1438 one SQL command in the given string. (There can be semicolons in it,
1439 but not more than one nonempty command.) This is a limitation of the
1440 underlying protocol, but has some usefulness as an extra defense against
1441 SQL-injection attacks.
1446 Specifying parameter types via OIDs is tedious, particularly if you prefer
1447 not to hard-wire particular OID values into your program. However, you can
1448 avoid doing so even in cases where the server by itself cannot determine the
1449 type of the parameter, or chooses a different type than you want. In the
1450 SQL command text, attach an explicit cast to the parameter symbol to show what
1451 data type you will send. For example:
1453 SELECT * FROM mytable WHERE x = $1::bigint;
1455 This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
1456 by default it would be assigned the same type as <literal>x</>. Forcing the
1457 parameter type decision, either this way or by specifying a numeric type OID,
1458 is strongly recommended when sending parameter values in binary format, because
1459 binary format has less redundancy than text format and so there is less chance
1460 that the server will detect a type mismatch mistake for you.
1467 <term><function>PQprepare</function>
1469 <primary>PQprepare</primary>
1475 Submits a request to create a prepared statement with the
1476 given parameters, and waits for completion.
1478 PGresult *PQprepare(PGconn *conn,
1479 const char *stmtName,
1482 const Oid *paramTypes);
1487 <function>PQprepare</> creates a prepared statement for later
1488 execution with <function>PQexecPrepared</>. This feature allows
1489 commands that will be used repeatedly to be parsed and planned just
1490 once, rather than each time they are executed.
1491 <function>PQprepare</> is supported only in protocol 3.0 and later
1492 connections; it will fail when using protocol 2.0.
1496 The function creates a prepared statement named
1497 <parameter>stmtName</> from the <parameter>query</> string, which
1498 must contain a single SQL command. <parameter>stmtName</> can be
1499 <literal>""</> to create an unnamed statement, in which case any
1500 pre-existing unnamed statement is automatically replaced; otherwise
1501 it is an error if the statement name is already defined in the
1502 current session. If any parameters are used, they are referred
1503 to in the query as <literal>$1</>, <literal>$2</>, etc.
1504 <parameter>nParams</> is the number of parameters for which types
1505 are pre-specified in the array <parameter>paramTypes[]</>. (The
1506 array pointer can be <symbol>NULL</symbol> when
1507 <parameter>nParams</> is zero.) <parameter>paramTypes[]</>
1508 specifies, by OID, the data types to be assigned to the parameter
1509 symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1510 or any particular element in the array is zero, the server assigns
1511 a data type to the parameter symbol in the same way it would do
1512 for an untyped literal string. Also, the query can use parameter
1513 symbols with numbers higher than <parameter>nParams</>; data types
1514 will be inferred for these symbols as well. (See
1515 <function>PQdescribePrepared</function> for a means to find out
1516 what data types were inferred.)
1520 As with <function>PQexec</>, the result is normally a
1521 <structname>PGresult</structname> object whose contents indicate
1522 server-side success or failure. A null result indicates
1523 out-of-memory or inability to send the command at all. Use
1524 <function>PQerrorMessage</function> to get more information about
1531 Prepared statements for use with <function>PQexecPrepared</> can also
1532 be created by executing SQL <xref linkend="sql-prepare"
1533 endterm="sql-prepare-title"> statements. (But <function>PQprepare</>
1534 is more flexible since it does not require parameter types to be
1535 pre-specified.) Also, although there is no <application>libpq</>
1536 function for deleting a prepared statement, the SQL <xref
1537 linkend="sql-deallocate" endterm="sql-deallocate-title"> statement
1538 can be used for that purpose.
1545 <function>PQexecPrepared</function>
1547 <primary>PQexecPrepared</primary>
1553 Sends a request to execute a prepared statement with given
1554 parameters, and waits for the result.
1556 PGresult *PQexecPrepared(PGconn *conn,
1557 const char *stmtName,
1559 const char * const *paramValues,
1560 const int *paramLengths,
1561 const int *paramFormats,
1567 <function>PQexecPrepared</> is like <function>PQexecParams</>,
1568 but the command to be executed is specified by naming a
1569 previously-prepared statement, instead of giving a query string.
1570 This feature allows commands that will be used repeatedly to be
1571 parsed and planned just once, rather than each time they are
1572 executed. The statement must have been prepared previously in
1573 the current session. <function>PQexecPrepared</> is supported
1574 only in protocol 3.0 and later connections; it will fail when
1579 The parameters are identical to <function>PQexecParams</>, except that the
1580 name of a prepared statement is given instead of a query string, and the
1581 <parameter>paramTypes[]</> parameter is not present (it is not needed since
1582 the prepared statement's parameter types were determined when it was created).
1589 <function>PQdescribePrepared</function>
1591 <primary>PQdescribePrepared</primary>
1597 Submits a request to obtain information about the specified
1598 prepared statement, and waits for completion.
1600 PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
1605 <function>PQdescribePrepared</> allows an application to obtain
1606 information about a previously prepared statement.
1607 <function>PQdescribePrepared</> is supported only in protocol 3.0
1608 and later connections; it will fail when using protocol 2.0.
1612 <parameter>stmtName</> can be <literal>""</> or NULL to reference
1613 the unnamed statement, otherwise it must be the name of an existing
1614 prepared statement. On success, a <structname>PGresult</> with
1615 status <literal>PGRES_COMMAND_OK</literal> is returned. The
1616 functions <function>PQnparams</function> and
1617 <function>PQparamtype</function> can be applied to this
1618 <structname>PGresult</> to obtain information about the parameters
1619 of the prepared statement, and the functions
1620 <function>PQnfields</function>, <function>PQfname</function>,
1621 <function>PQftype</function>, etc provide information about the
1622 result columns (if any) of the statement.
1629 <function>PQdescribePortal</function>
1631 <primary>PQdescribePortal</primary>
1637 Submits a request to obtain information about the specified
1638 portal, and waits for completion.
1640 PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
1645 <function>PQdescribePortal</> allows an application to obtain
1646 information about a previously created portal.
1647 (<application>libpq</> does not provide any direct access to
1648 portals, but you can use this function to inspect the properties
1649 of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
1650 <function>PQdescribePortal</> is supported only in protocol 3.0
1651 and later connections; it will fail when using protocol 2.0.
1655 <parameter>portalName</> can be <literal>""</> or NULL to reference
1656 the unnamed portal, otherwise it must be the name of an existing
1657 portal. On success, a <structname>PGresult</> with status
1658 <literal>PGRES_COMMAND_OK</literal> is returned. The functions
1659 <function>PQnfields</function>, <function>PQfname</function>,
1660 <function>PQftype</function>, etc can be applied to the
1661 <structname>PGresult</> to obtain information about the result
1662 columns (if any) of the portal.
1670 The <structname>PGresult</structname><indexterm><primary>PGresult</></>
1671 structure encapsulates the result returned by the server.
1672 <application>libpq</application> application programmers should be
1673 careful to maintain the <structname>PGresult</structname> abstraction.
1674 Use the accessor functions below to get at the contents of
1675 <structname>PGresult</structname>. Avoid directly referencing the
1676 fields of the <structname>PGresult</structname> structure because they
1677 are subject to change in the future.
1682 <function>PQresultStatus</function>
1684 <primary>PQresultStatus</primary>
1690 Returns the result status of the command.
1692 ExecStatusType PQresultStatus(const PGresult *res);
1697 <function>PQresultStatus</function> can return one of the following values:
1701 <term><literal>PGRES_EMPTY_QUERY</literal></term>
1704 The string sent to the server was empty.
1710 <term><literal>PGRES_COMMAND_OK</literal></term>
1713 Successful completion of a command returning no data.
1719 <term><literal>PGRES_TUPLES_OK</literal></term>
1722 Successful completion of a command returning data (such as
1723 a <command>SELECT</> or <command>SHOW</>).
1729 <term><literal>PGRES_COPY_OUT</literal></term>
1732 Copy Out (from server) data transfer started.
1738 <term><literal>PGRES_COPY_IN</literal></term>
1741 Copy In (to server) data transfer started.
1747 <term><literal>PGRES_BAD_RESPONSE</literal></term>
1750 The server's response was not understood.
1756 <term><literal>PGRES_NONFATAL_ERROR</literal></term>
1759 A nonfatal error (a notice or warning) occurred.
1765 <term><literal>PGRES_FATAL_ERROR</literal></term>
1768 A fatal error occurred.
1774 If the result status is <literal>PGRES_TUPLES_OK</literal>, then
1775 the functions described below can be used to retrieve the rows
1776 returned by the query. Note that a <command>SELECT</command>
1777 command that happens to retrieve zero rows still shows
1778 <literal>PGRES_TUPLES_OK</literal>.
1779 <literal>PGRES_COMMAND_OK</literal> is for commands that can never
1780 return rows (<command>INSERT</command>, <command>UPDATE</command>,
1781 etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
1782 indicate a bug in the client software.
1786 A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
1787 never be returned directly by <function>PQexec</function> or other
1788 query execution functions; results of this kind are instead passed
1789 to the notice processor (see <xref
1790 linkend="libpq-notice-processing">).
1797 <function>PQresStatus</function>
1799 <primary>PQresStatus</primary>
1805 Converts the enumerated type returned by
1806 <function>PQresultStatus</> into a string constant describing the
1807 status code. The caller should not free the result.
1810 char *PQresStatus(ExecStatusType status);
1818 <function>PQresultErrorMessage</function>
1820 <primary>PQresultErrorMessage</primary>
1826 Returns the error message associated with the command, or an empty string
1827 if there was no error.
1829 char *PQresultErrorMessage(const PGresult *res);
1831 If there was an error, the returned string will include a trailing
1832 newline. The caller should not free the result directly. It will
1833 be freed when the associated <structname>PGresult</> handle is
1834 passed to <function>PQclear</function>.
1838 Immediately following a <function>PQexec</function> or
1839 <function>PQgetResult</function> call,
1840 <function>PQerrorMessage</function> (on the connection) will return
1841 the same string as <function>PQresultErrorMessage</function> (on
1842 the result). However, a <structname>PGresult</structname> will
1843 retain its error message until destroyed, whereas the connection's
1844 error message will change when subsequent operations are done.
1845 Use <function>PQresultErrorMessage</function> when you want to
1846 know the status associated with a particular
1847 <structname>PGresult</structname>; use
1848 <function>PQerrorMessage</function> when you want to know the
1849 status from the latest operation on the connection.
1855 <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
1858 Returns an individual field of an error report.
1860 char *PQresultErrorField(const PGresult *res, int fieldcode);
1862 <parameter>fieldcode</> is an error field identifier; see the symbols
1863 listed below. <symbol>NULL</symbol> is returned if the
1864 <structname>PGresult</structname> is not an error or warning result,
1865 or does not include the specified field. Field values will normally
1866 not include a trailing newline. The caller should not free the
1867 result directly. It will be freed when the
1868 associated <structname>PGresult</> handle is passed to
1869 <function>PQclear</function>.
1873 The following field codes are available:
1876 <term><symbol>PG_DIAG_SEVERITY</></term>
1879 The severity; the field contents are <literal>ERROR</>,
1880 <literal>FATAL</>, or <literal>PANIC</> (in an error message),
1881 or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
1882 <literal>INFO</>, or <literal>LOG</> (in a notice message), or
1883 a localized translation of one of these. Always present.
1890 <primary>error codes</primary>
1891 <secondary>libpq</secondary>
1893 <term><symbol>PG_DIAG_SQLSTATE</></term>
1896 The SQLSTATE code for the error. The SQLSTATE code identifies
1897 the type of error that has occurred; it can be used by
1898 front-end applications to perform specific operations (such
1899 as error handling) in response to a particular database error.
1900 For a list of the possible SQLSTATE codes, see <xref
1901 linkend="errcodes-appendix">. This field is not localizable,
1902 and is always present.
1908 <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
1911 The primary human-readable error message (typically one line).
1918 <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
1921 Detail: an optional secondary error message carrying more
1922 detail about the problem. Might run to multiple lines.
1928 <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
1931 Hint: an optional suggestion what to do about the problem.
1932 This is intended to differ from detail in that it offers advice
1933 (potentially inappropriate) rather than hard facts. Might
1934 run to multiple lines.
1940 <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
1943 A string containing a decimal integer indicating an error cursor
1944 position as an index into the original statement string. The
1945 first character has index 1, and positions are measured in
1946 characters not bytes.
1952 <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
1955 This is defined the same as the
1956 <symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
1957 when the cursor position refers to an internally generated
1958 command rather than the one submitted by the client. The
1959 <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
1966 <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
1969 The text of a failed internally-generated command. This could
1970 be, for example, a SQL query issued by a PL/pgSQL function.
1976 <term><symbol>PG_DIAG_CONTEXT</></term>
1979 An indication of the context in which the error occurred.
1980 Presently this includes a call stack traceback of active
1981 procedural language functions and internally-generated queries.
1982 The trace is one entry per line, most recent first.
1988 <term><symbol>PG_DIAG_SOURCE_FILE</></term>
1991 The file name of the source-code location where the error was
1998 <term><symbol>PG_DIAG_SOURCE_LINE</></term>
2001 The line number of the source-code location where the error
2008 <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
2011 The name of the source-code function reporting the error.
2019 The client is responsible for formatting displayed information to meet
2020 its needs; in particular it should break long lines as needed.
2021 Newline characters appearing in the error message fields should be
2022 treated as paragraph breaks, not line breaks.
2026 Errors generated internally by <application>libpq</application> will
2027 have severity and primary message, but typically no other fields.
2028 Errors returned by a pre-3.0-protocol server will include severity and
2029 primary message, and sometimes a detail message, but no other fields.
2033 Note that error fields are only available from
2034 <structname>PGresult</structname> objects, not
2035 <structname>PGconn</structname> objects; there is no
2036 <function>PQerrorField</function> function.
2042 <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
2045 Frees the storage associated with a
2046 <structname>PGresult</structname>. Every command result should be
2047 freed via <function>PQclear</function> when it is no longer
2051 void PQclear(PGresult *res);
2056 You can keep a <structname>PGresult</structname> object around for
2057 as long as you need it; it does not go away when you issue a new
2058 command, nor even if you close the connection. To get rid of it,
2059 you must call <function>PQclear</function>. Failure to do this
2060 will result in memory leaks in your application.
2067 <function>PQmakeEmptyPGresult</function>
2069 <primary>PQmakeEmptyPGresult</primary>
2075 Constructs an empty <structname>PGresult</structname> object with the given status.
2077 PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
2082 This is <application>libpq</>'s internal function to allocate and
2083 initialize an empty <structname>PGresult</structname> object. This
2084 function returns NULL if memory could not be allocated. It is
2085 exported because some applications find it useful to generate result
2086 objects (particularly objects with error status) themselves. If
2087 <parameter>conn</parameter> is not null and <parameter>status</>
2088 indicates an error, the current error message of the specified
2089 connection is copied into the <structname>PGresult</structname>.
2090 Note that <function>PQclear</function> should eventually be called
2091 on the object, just as with a <structname>PGresult</structname>
2092 returned by <application>libpq</application> itself.
2100 <sect2 id="libpq-exec-select-info">
2101 <title>Retrieving Query Result Information</title>
2104 These functions are used to extract information from a
2105 <structname>PGresult</structname> object that represents a successful
2106 query result (that is, one that has status
2107 <literal>PGRES_TUPLES_OK</literal>). They can also be used to extract
2108 information from a successful Describe operation: a Describe's result
2109 has all the same column information that actual execution of the query
2110 would provide, but it has zero rows. For objects with other status values,
2111 these functions will act as though the result has zero rows and zero columns.
2117 <function>PQntuples</function>
2119 <primary>PQntuples</primary>
2125 Returns the number of rows (tuples) in the query result. Because
2126 it returns an integer result, large result sets might overflow the
2127 return value on 32-bit operating systems.
2130 int PQntuples(const PGresult *res);
2139 <function>PQnfields</function>
2141 <primary>PQnfields</primary>
2147 Returns the number of columns (fields) in each row of the query
2151 int PQnfields(const PGresult *res);
2159 <function>PQfname</function>
2161 <primary>PQfname</primary>
2167 Returns the column name associated with the given column number.
2168 Column numbers start at 0. The caller should not free the result
2169 directly. It will be freed when the associated
2170 <structname>PGresult</> handle is passed to
2171 <function>PQclear</function>.
2173 char *PQfname(const PGresult *res,
2179 <symbol>NULL</symbol> is returned if the column number is out of range.
2186 <function>PQfnumber</function>
2188 <primary>PQfnumber</primary>
2194 Returns the column number associated with the given column name.
2196 int PQfnumber(const PGresult *res,
2197 const char *column_name);
2202 -1 is returned if the given name does not match any column.
2206 The given name is treated like an identifier in an SQL command,
2207 that is, it is downcased unless double-quoted. For example, given
2208 a query result generated from the SQL command:
2210 SELECT 1 AS FOO, 2 AS "BAR";
2212 we would have the results:
2214 PQfname(res, 0) <lineannotation>foo</lineannotation>
2215 PQfname(res, 1) <lineannotation>BAR</lineannotation>
2216 PQfnumber(res, "FOO") <lineannotation>0</lineannotation>
2217 PQfnumber(res, "foo") <lineannotation>0</lineannotation>
2218 PQfnumber(res, "BAR") <lineannotation>-1</lineannotation>
2219 PQfnumber(res, "\"BAR\"") <lineannotation>1</lineannotation>
2227 <function>PQftable</function>
2229 <primary>PQftable</primary>
2235 Returns the OID of the table from which the given column was
2236 fetched. Column numbers start at 0.
2238 Oid PQftable(const PGresult *res,
2244 <literal>InvalidOid</> is returned if the column number is out of range,
2245 or if the specified column is not a simple reference to a table column,
2246 or when using pre-3.0 protocol.
2247 You can query the system table <literal>pg_class</literal> to determine
2248 exactly which table is referenced.
2252 The type <type>Oid</type> and the constant
2253 <literal>InvalidOid</literal> will be defined when you include
2254 the <application>libpq</application> header file. They will both
2255 be some integer type.
2262 <function>PQftablecol</function>
2264 <primary>PQftablecol</primary>
2270 Returns the column number (within its table) of the column making
2271 up the specified query result column. Query-result column numbers
2272 start at 0, but table columns have nonzero numbers.
2274 int PQftablecol(const PGresult *res,
2280 Zero is returned if the column number is out of range, or if the
2281 specified column is not a simple reference to a table column, or
2282 when using pre-3.0 protocol.
2289 <function>PQfformat</function>
2291 <primary>PQfformat</primary>
2297 Returns the format code indicating the format of the given
2298 column. Column numbers start at 0.
2300 int PQfformat(const PGresult *res,
2306 Format code zero indicates textual data representation, while format
2307 code one indicates binary representation. (Other codes are reserved
2308 for future definition.)
2315 <function>PQftype</function>
2317 <primary>PQftype</primary>
2323 Returns the data type associated with the given column number.
2324 The integer returned is the internal OID number of the type.
2325 Column numbers start at 0.
2327 Oid PQftype(const PGresult *res,
2333 You can query the system table <literal>pg_type</literal> to
2334 obtain the names and properties of the various data types. The
2335 <acronym>OID</acronym>s of the built-in data types are defined
2336 in the file <filename>src/include/catalog/pg_type.h</filename>
2344 <function>PQfmod</function>
2346 <primary>PQfmod</primary>
2352 Returns the type modifier of the column associated with the
2353 given column number. Column numbers start at 0.
2355 int PQfmod(const PGresult *res,
2361 The interpretation of modifier values is type-specific; they
2362 typically indicate precision or size limits. The value -1 is
2363 used to indicate <quote>no information available</>. Most data
2364 types do not use modifiers, in which case the value is always
2372 <function>PQfsize</function>
2374 <primary>PQfsize</primary>
2380 Returns the size in bytes of the column associated with the
2381 given column number. Column numbers start at 0.
2383 int PQfsize(const PGresult *res,
2389 <function>PQfsize</> returns the space allocated for this column
2390 in a database row, in other words the size of the server's
2391 internal representation of the data type. (Accordingly, it is
2392 not really very useful to clients.) A negative value indicates
2393 the data type is variable-length.
2400 <function>PQbinaryTuples</function>
2402 <primary>PQbinaryTuples</primary>
2408 Returns 1 if the <structname>PGresult</> contains binary data
2409 and 0 if it contains text data.
2411 int PQbinaryTuples(const PGresult *res);
2416 This function is deprecated (except for its use in connection with
2417 <command>COPY</>), because it is possible for a single
2418 <structname>PGresult</> to contain text data in some columns and
2419 binary data in others. <function>PQfformat</> is preferred.
2420 <function>PQbinaryTuples</> returns 1 only if all columns of the
2421 result are binary (format 1).
2428 <function>PQgetvalue</function>
2430 <primary>PQgetvalue</primary>
2436 Returns a single field value of one row of a
2437 <structname>PGresult</structname>. Row and column numbers start
2438 at 0. The caller should not free the result directly. It will
2439 be freed when the associated <structname>PGresult</> handle is
2440 passed to <function>PQclear</function>.
2442 char *PQgetvalue(const PGresult *res,
2449 For data in text format, the value returned by
2450 <function>PQgetvalue</function> is a null-terminated character
2451 string representation of the field value. For data in binary
2452 format, the value is in the binary representation determined by
2453 the data type's <function>typsend</> and <function>typreceive</>
2454 functions. (The value is actually followed by a zero byte in
2455 this case too, but that is not ordinarily useful, since the
2456 value is likely to contain embedded nulls.)
2460 An empty string is returned if the field value is null. See
2461 <function>PQgetisnull</> to distinguish null values from
2462 empty-string values.
2466 The pointer returned by <function>PQgetvalue</function> points
2467 to storage that is part of the <structname>PGresult</structname>
2468 structure. One should not modify the data it points to, and one
2469 must explicitly copy the data into other storage if it is to be
2470 used past the lifetime of the <structname>PGresult</structname>
2478 <function>PQgetisnull</function>
2480 <primary>PQgetisnull</primary>
2483 <primary>null value</primary>
2484 <secondary sortas="libpq">in libpq</secondary>
2490 Tests a field for a null value. Row and column numbers start
2493 int PQgetisnull(const PGresult *res,
2500 This function returns 1 if the field is null and 0 if it
2501 contains a non-null value. (Note that
2502 <function>PQgetvalue</function> will return an empty string,
2503 not a null pointer, for a null field.)
2510 <function>PQgetlength</function>
2512 <primary>PQgetlength</primary>
2517 Returns the actual length of a field value in bytes. Row and
2518 column numbers start at 0.
2520 int PQgetlength(const PGresult *res,
2527 This is the actual data length for the particular data value,
2528 that is, the size of the object pointed to by
2529 <function>PQgetvalue</function>. For text data format this is
2530 the same as <function>strlen()</>. For binary format this is
2531 essential information. Note that one should <emphasis>not</>
2532 rely on <function>PQfsize</function> to obtain the actual data
2540 <function>PQnparams</function>
2542 <primary>PQnparams</primary>
2548 Returns the number of parameters of a prepared statement.
2550 int PQnparams(const PGresult *res);
2555 This function is only useful when inspecting the result of
2556 <function>PQdescribePrepared</>. For other types of queries it
2564 <function>PQparamtype</function>
2566 <primary>PQparamtype</primary>
2572 Returns the data type of the indicated statement parameter.
2573 Parameter numbers start at 0.
2575 Oid PQparamtype(const PGresult *res, int param_number);
2580 This function is only useful when inspecting the result of
2581 <function>PQdescribePrepared</>. For other types of queries it
2589 <function>PQprint</function>
2591 <primary>PQprint</primary>
2597 Prints out all the rows and, optionally, the column names to
2598 the specified output stream.
2600 void PQprint(FILE *fout, /* output stream */
2601 const PGresult *res,
2602 const PQprintOpt *po);
2604 pqbool header; /* print output field headings and row count */
2605 pqbool align; /* fill align the fields */
2606 pqbool standard; /* old brain dead format */
2607 pqbool html3; /* output HTML tables */
2608 pqbool expanded; /* expand tables */
2609 pqbool pager; /* use pager for output if needed */
2610 char *fieldSep; /* field separator */
2611 char *tableOpt; /* attributes for HTML table element */
2612 char *caption; /* HTML table caption */
2613 char **fieldName; /* null-terminated array of replacement field names */
2619 This function was formerly used by <application>psql</application>
2620 to print query results, but this is no longer the case. Note
2621 that it assumes all the data is in text format.
2628 <sect2 id="libpq-exec-nonselect">
2629 <title>Retrieving Result Information for Other Commands</title>
2632 These functions are used to extract information from
2633 <structname>PGresult</structname> objects that are not
2634 <command>SELECT</> results.
2640 <function>PQcmdStatus</function>
2642 <primary>PQcmdStatus</primary>
2648 Returns the command status tag from the SQL command that generated
2649 the <structname>PGresult</structname>.
2651 char *PQcmdStatus(PGresult *res);
2656 Commonly this is just the name of the command, but it might include
2657 additional data such as the number of rows processed. The caller
2658 should not free the result directly. It will be freed when the
2659 associated <structname>PGresult</> handle is passed to
2660 <function>PQclear</function>.
2667 <function>PQcmdTuples</function>
2669 <primary>PQcmdTuples</primary>
2675 Returns the number of rows affected by the SQL command.
2677 char *PQcmdTuples(PGresult *res);
2682 This function returns a string containing the number of rows
2683 affected by the <acronym>SQL</> statement that generated the
2684 <structname>PGresult</>. This function can only be used following
2685 the execution of an <command>INSERT</>, <command>UPDATE</>,
2686 <command>DELETE</>, <command>MOVE</>, <command>FETCH</>, or
2687 <command>COPY</> statement, or an <command>EXECUTE</> of a
2688 prepared query that contains an <command>INSERT</>,
2689 <command>UPDATE</>, or <command>DELETE</> statement. If the
2690 command that generated the <structname>PGresult</> was anything
2691 else, <function>PQcmdTuples</> returns an empty string. The caller
2692 should not free the return value directly. It will be freed when
2693 the associated <structname>PGresult</> handle is passed to
2694 <function>PQclear</function>.
2701 <function>PQoidValue</function>
2703 <primary>PQoidValue</primary>
2709 Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
2710 of the inserted row, if the <acronym>SQL</> command was an
2711 <command>INSERT</> that inserted exactly one row into a table that
2712 has OIDs, or a <command>EXECUTE</> of a prepared query containing
2713 a suitable <command>INSERT</> statement. Otherwise, this function
2714 returns <literal>InvalidOid</literal>. This function will also
2715 return <literal>InvalidOid</literal> if the table affected by the
2716 <command>INSERT</> statement does not contain OIDs.
2718 Oid PQoidValue(const PGresult *res);
2726 <function>PQoidStatus</function>
2728 <primary>PQoidStatus</primary>
2734 Returns a string with the OID of the inserted row, if the
2735 <acronym>SQL</acronym> command was an <command>INSERT</command>
2736 that inserted exactly one row, or a <command>EXECUTE</command> of
2737 a prepared statement consisting of a suitable
2738 <command>INSERT</command>. (The string will be <literal>0</> if
2739 the <command>INSERT</command> did not insert exactly one row, or
2740 if the target table does not have OIDs.) If the command was not
2741 an <command>INSERT</command>, returns an empty string.
2743 char *PQoidStatus(const PGresult *res);
2748 This function is deprecated in favor of
2749 <function>PQoidValue</function>. It is not thread-safe.
2757 <sect2 id="libpq-exec-escape-string">
2758 <title>Escaping Strings for Inclusion in SQL Commands</title>
2760 <indexterm zone="libpq-exec-escape-string">
2761 <primary>PQescapeStringConn</primary>
2763 <indexterm zone="libpq-exec-escape-string">
2764 <primary>PQescapeString</primary>
2766 <indexterm zone="libpq-exec-escape-string">
2767 <primary>escaping strings</primary>
2768 <secondary>in libpq</secondary>
2772 <function>PQescapeStringConn</function> escapes a string for use within an SQL
2773 command. This is useful when inserting data values as literal constants
2774 in SQL commands. Certain characters (such as quotes and backslashes) must
2775 be escaped to prevent them from being interpreted specially by the SQL parser.
2776 <function>PQescapeStringConn</> performs this operation.
2781 It is especially important to do proper escaping when handling strings that
2782 were received from an untrustworthy source. Otherwise there is a security
2783 risk: you are vulnerable to <quote>SQL injection</> attacks wherein unwanted
2784 SQL commands are fed to your database.
2789 Note that it is not necessary nor correct to do escaping when a data
2790 value is passed as a separate parameter in <function>PQexecParams</> or
2791 its sibling routines.
2794 size_t PQescapeStringConn (PGconn *conn,
2795 char *to, const char *from, size_t length,
2801 <function>PQescapeStringConn</> writes an escaped version of the
2802 <parameter>from</> string to the <parameter>to</> buffer, escaping
2803 special characters so that they cannot cause any harm, and adding a
2804 terminating zero byte. The single quotes that must surround
2805 <productname>PostgreSQL</> string literals are not included in the
2806 result string; they should be provided in the SQL command that the
2807 result is inserted into. The parameter <parameter>from</> points to
2808 the first character of the string that is to be escaped, and the
2809 <parameter>length</> parameter gives the number of bytes in this
2810 string. A terminating zero byte is not required, and should not be
2811 counted in <parameter>length</>. (If a terminating zero byte is found
2812 before <parameter>length</> bytes are processed,
2813 <function>PQescapeStringConn</> stops at the zero; the behavior is
2814 thus rather like <function>strncpy</>.) <parameter>to</> shall point
2815 to a buffer that is able to hold at least one more byte than twice
2816 the value of <parameter>length</>, otherwise the behavior is undefined.
2817 Behavior is likewise undefined if the <parameter>to</> and
2818 <parameter>from</> strings overlap.
2822 If the <parameter>error</> parameter is not NULL, then
2823 <literal>*error</> is set to zero on success, nonzero on error.
2824 Presently the only possible error conditions involve invalid multibyte
2825 encoding in the source string. The output string is still generated
2826 on error, but it can be expected that the server will reject it as
2827 malformed. On error, a suitable message is stored in the
2828 <parameter>conn</> object, whether or not <parameter>error</> is NULL.
2832 <function>PQescapeStringConn</> returns the number of bytes written
2833 to <parameter>to</>, not including the terminating zero byte.
2838 size_t PQescapeString (char *to, const char *from, size_t length);
2843 <function>PQescapeString</> is an older, deprecated version of
2844 <function>PQescapeStringConn</>; the difference is that it does
2845 not take <parameter>conn</> or <parameter>error</> parameters.
2846 Because of this, it cannot adjust its behavior depending on the
2847 connection properties (such as character encoding) and therefore
2848 <emphasis>it might give the wrong results</>. Also, it has no way
2849 to report error conditions.
2853 <function>PQescapeString</> can be used safely in single-threaded
2854 client programs that work with only one <productname>PostgreSQL</>
2855 connection at a time (in this case it can find out what it needs to
2856 know <quote>behind the scenes</>). In other contexts it is a security
2857 hazard and should be avoided in favor of
2858 <function>PQescapeStringConn</>.
2863 <sect2 id="libpq-exec-escape-bytea">
2864 <title>Escaping Binary Strings for Inclusion in SQL Commands</title>
2866 <indexterm zone="libpq-exec-escape-bytea">
2867 <primary>bytea</primary>
2868 <secondary sortas="libpq">in libpq</secondary>
2874 <function>PQescapeByteaConn</function>
2876 <primary>PQescapeByteaConn</primary>
2882 Escapes binary data for use within an SQL command with the type
2883 <type>bytea</type>. As with <function>PQescapeStringConn</function>,
2884 this is only used when inserting data directly into an SQL command string.
2886 unsigned char *PQescapeByteaConn(PGconn *conn,
2887 const unsigned char *from,
2894 Certain byte values <emphasis>must</emphasis> be escaped (but all
2895 byte values <emphasis>can</emphasis> be escaped) when used as part
2896 of a <type>bytea</type> literal in an <acronym>SQL</acronym>
2897 statement. In general, to escape a byte, it is converted into the
2898 three digit octal number equal to the octet value, and preceded by
2899 usually two backslashes. The single quote (<literal>'</>) and backslash
2900 (<literal>\</>) characters have special alternative escape
2901 sequences. See <xref linkend="datatype-binary"> for more
2902 information. <function>PQescapeByteaConn</function> performs this
2903 operation, escaping only the minimally required bytes.
2907 The <parameter>from</parameter> parameter points to the first
2908 byte of the string that is to be escaped, and the
2909 <parameter>from_length</parameter> parameter gives the number of
2910 bytes in this binary string. (A terminating zero byte is
2911 neither necessary nor counted.) The <parameter>to_length</parameter>
2912 parameter points to a variable that will hold the resultant
2913 escaped string length. This result string length includes the terminating
2914 zero byte of the result.
2918 <function>PQescapeByteaConn</> returns an escaped version of the
2919 <parameter>from</parameter> parameter binary string in memory
2920 allocated with <function>malloc()</>. This memory must be freed using
2921 <function>PQfreemem()</> when the result is no longer needed. The
2922 return string has all special characters replaced so that they can
2923 be properly processed by the <productname>PostgreSQL</productname>
2924 string literal parser, and the <type>bytea</type> input function. A
2925 terminating zero byte is also added. The single quotes that must
2926 surround <productname>PostgreSQL</productname> string literals are
2927 not part of the result string.
2931 On error, a NULL pointer is returned, and a suitable error message
2932 is stored in the <parameter>conn</> object. Currently, the only
2933 possible error is insufficient memory for the result string.
2940 <function>PQescapeBytea</function>
2942 <primary>PQescapeBytea</primary>
2948 <function>PQescapeBytea</> is an older, deprecated version of
2949 <function>PQescapeByteaConn</>.
2951 unsigned char *PQescapeBytea(const unsigned char *from,
2958 The only difference from <function>PQescapeByteaConn</> is that
2959 <function>PQescapeBytea</> does not take a <structname>PGconn</>
2960 parameter. Because of this, it cannot adjust its behavior
2961 depending on the connection properties (in particular, whether
2962 standard-conforming strings are enabled) and therefore
2963 <emphasis>it might give the wrong results</>. Also, it has no
2964 way to return an error message on failure.
2968 <function>PQescapeBytea</> can be used safely in single-threaded
2969 client programs that work with only one <productname>PostgreSQL</>
2970 connection at a time (in this case it can find out what it needs
2971 to know <quote>behind the scenes</>). In other contexts it is
2972 a security hazard and should be avoided in favor of
2973 <function>PQescapeByteaConn</>.
2980 <function>PQunescapeBytea</function>
2982 <primary>PQunescapeBytea</primary>
2988 Converts a string representation of binary data into binary data
2989 — the reverse of <function>PQescapeBytea</function>. This
2990 is needed when retrieving <type>bytea</type> data in text format,
2991 but not when retrieving it in binary format.
2994 unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
2999 The <parameter>from</parameter> parameter points to a string
3000 such as might be returned by <function>PQgetvalue</function> when applied
3001 to a <type>bytea</type> column. <function>PQunescapeBytea</function>
3002 converts this string representation into its binary representation.
3003 It returns a pointer to a buffer allocated with
3004 <function>malloc()</function>, or null on error, and puts the size of
3005 the buffer in <parameter>to_length</parameter>. The result must be
3006 freed using <function>PQfreemem</> when it is no longer needed.
3010 This conversion is not exactly the inverse of
3011 <function>PQescapeBytea</function>, because the string is not expected
3012 to be <quote>escaped</> when received from <function>PQgetvalue</function>.
3013 In particular this means there is no need for string quoting considerations,
3014 and so no need for a <structname>PGconn</> parameter.
3021 <function>PQfreemem</function>
3023 <primary>PQfreemem</primary>
3029 Frees memory allocated by <application>libpq</>.
3031 void PQfreemem(void *ptr);
3036 Frees memory allocated by <application>libpq</>, particularly
3037 <function>PQescapeByteaConn</function>,
3038 <function>PQescapeBytea</function>,
3039 <function>PQunescapeBytea</function>,
3040 and <function>PQnotifies</function>.
3041 It is particularly important that this function, rather than
3042 <function>free()</>, be used on Microsoft Windows. This is because
3043 allocating memory in a DLL and releasing it in the application works
3044 only if multithreaded/single-threaded, release/debug, and static/dynamic
3045 flags are the same for the DLL and the application. On non-Microsoft
3046 Windows platforms, this function is the same as the standard library
3047 function <function>free()</>.
3057 <sect1 id="libpq-async">
3058 <title>Asynchronous Command Processing</title>
3060 <indexterm zone="libpq-async">
3061 <primary>nonblocking connection</primary>
3065 The <function>PQexec</function> function is adequate for submitting
3066 commands in normal, synchronous applications. It has a couple of
3067 deficiencies, however, that can be of importance to some users:
3072 <function>PQexec</function> waits for the command to be completed.
3073 The application might have other work to do (such as maintaining a
3074 user interface), in which case it won't want to block waiting for
3081 Since the execution of the client application is suspended while it
3082 waits for the result, it is hard for the application to decide that
3083 it would like to try to cancel the ongoing command. (It can be done
3084 from a signal handler, but not otherwise.)
3090 <function>PQexec</function> can return only one
3091 <structname>PGresult</structname> structure. If the submitted command
3092 string contains multiple <acronym>SQL</acronym> commands, all but
3093 the last <structname>PGresult</structname> are discarded by
3094 <function>PQexec</function>.
3101 Applications that do not like these limitations can instead use the
3102 underlying functions that <function>PQexec</function> is built from:
3103 <function>PQsendQuery</function> and <function>PQgetResult</function>.
3105 <function>PQsendQueryParams</function>,
3106 <function>PQsendPrepare</function>,
3107 <function>PQsendQueryPrepared</function>,
3108 <function>PQsendDescribePrepared</function>, and
3109 <function>PQsendDescribePortal</function>,
3110 which can be used with <function>PQgetResult</function> to duplicate
3111 the functionality of
3112 <function>PQexecParams</function>,
3113 <function>PQprepare</function>,
3114 <function>PQexecPrepared</function>,
3115 <function>PQdescribePrepared</function>, and
3116 <function>PQdescribePortal</function>
3122 <function>PQsendQuery</function>
3124 <primary>PQsendQuery</primary>
3130 Submits a command to the server without waiting for the result(s).
3131 1 is returned if the command was successfully dispatched and 0 if
3132 not (in which case, use <function>PQerrorMessage</> to get more
3133 information about the failure).
3135 int PQsendQuery(PGconn *conn, const char *command);
3138 After successfully calling <function>PQsendQuery</function>, call
3139 <function>PQgetResult</function> one or more times to obtain the
3140 results. <function>PQsendQuery</function> cannot be called again
3141 (on the same connection) until <function>PQgetResult</function>
3142 has returned a null pointer, indicating that the command is done.
3149 <function>PQsendQueryParams</function>
3151 <primary>PQsendQueryParams</primary>
3157 Submits a command and separate parameters to the server without
3158 waiting for the result(s).
3160 int PQsendQueryParams(PGconn *conn,
3161 const char *command,
3163 const Oid *paramTypes,
3164 const char * const *paramValues,
3165 const int *paramLengths,
3166 const int *paramFormats,
3170 This is equivalent to <function>PQsendQuery</function> except that
3171 query parameters can be specified separately from the query string.
3172 The function's parameters are handled identically to
3173 <function>PQexecParams</function>. Like
3174 <function>PQexecParams</function>, it will not work on 2.0-protocol
3175 connections, and it allows only one command in the query string.
3182 <function>PQsendPrepare</>
3184 <primary>PQsendPrepare</primary>
3190 Sends a request to create a prepared statement with the given
3191 parameters, without waiting for completion.
3193 int PQsendPrepare(PGconn *conn,
3194 const char *stmtName,
3197 const Oid *paramTypes);
3200 This is an asynchronous version of <function>PQprepare</>: it
3201 returns 1 if it was able to dispatch the request, and 0 if not.
3202 After a successful call, call <function>PQgetResult</function> to
3203 determine whether the server successfully created the prepared
3204 statement. The function's parameters are handled identically to
3205 <function>PQprepare</function>. Like
3206 <function>PQprepare</function>, it will not work on 2.0-protocol
3214 <function>PQsendQueryPrepared</function>
3216 <primary>PQsendQueryPrepared</primary>
3222 Sends a request to execute a prepared statement with given
3223 parameters, without waiting for the result(s).
3225 int PQsendQueryPrepared(PGconn *conn,
3226 const char *stmtName,
3228 const char * const *paramValues,
3229 const int *paramLengths,
3230 const int *paramFormats,
3234 This is similar to <function>PQsendQueryParams</function>, but
3235 the command to be executed is specified by naming a
3236 previously-prepared statement, instead of giving a query string.
3237 The function's parameters are handled identically to
3238 <function>PQexecPrepared</function>. Like
3239 <function>PQexecPrepared</function>, it will not work on
3240 2.0-protocol connections.
3247 <function>PQsendDescribePrepared</>
3249 <primary>PQsendDescribePrepared</primary>
3255 Submits a request to obtain information about the specified
3256 prepared statement, without waiting for completion.
3258 int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
3261 This is an asynchronous version of <function>PQdescribePrepared</>:
3262 it returns 1 if it was able to dispatch the request, and 0 if not.
3263 After a successful call, call <function>PQgetResult</function> to
3264 obtain the results. The function's parameters are handled
3265 identically to <function>PQdescribePrepared</function>. Like
3266 <function>PQdescribePrepared</function>, it will not work on
3267 2.0-protocol connections.
3274 <function>PQsendDescribePortal</>
3276 <primary>PQsendDescribePortal</primary>
3282 Submits a request to obtain information about the specified
3283 portal, without waiting for completion.
3285 int PQsendDescribePortal(PGconn *conn, const char *portalName);
3288 This is an asynchronous version of <function>PQdescribePortal</>:
3289 it returns 1 if it was able to dispatch the request, and 0 if not.
3290 After a successful call, call <function>PQgetResult</function> to
3291 obtain the results. The function's parameters are handled
3292 identically to <function>PQdescribePortal</function>. Like
3293 <function>PQdescribePortal</function>, it will not work on
3294 2.0-protocol connections.
3301 <function>PQgetResult</function>
3303 <primary>PQgetResult</primary>
3309 Waits for the next result from a prior
3310 <function>PQsendQuery</function>,
3311 <function>PQsendQueryParams</function>,
3312 <function>PQsendPrepare</function>, or
3313 <function>PQsendQueryPrepared</function> call, and returns it.
3314 A null pointer is returned when the command is complete and there
3315 will be no more results.
3317 PGresult *PQgetResult(PGconn *conn);
3322 <function>PQgetResult</function> must be called repeatedly until
3323 it returns a null pointer, indicating that the command is done.
3324 (If called when no command is active,
3325 <function>PQgetResult</function> will just return a null pointer
3326 at once.) Each non-null result from
3327 <function>PQgetResult</function> should be processed using the
3328 same <structname>PGresult</> accessor functions previously
3329 described. Don't forget to free each result object with
3330 <function>PQclear</function> when done with it. Note that
3331 <function>PQgetResult</function> will block only if a command is
3332 active and the necessary response data has not yet been read by
3333 <function>PQconsumeInput</function>.
3341 Using <function>PQsendQuery</function> and
3342 <function>PQgetResult</function> solves one of
3343 <function>PQexec</function>'s problems: If a command string contains
3344 multiple <acronym>SQL</acronym> commands, the results of those commands
3345 can be obtained individually. (This allows a simple form of overlapped
3346 processing, by the way: the client can be handling the results of one
3347 command while the server is still working on later queries in the same
3348 command string.) However, calling <function>PQgetResult</function>
3349 will still cause the client to block until the server completes the
3350 next <acronym>SQL</acronym> command. This can be avoided by proper
3351 use of two more functions:
3356 <function>PQconsumeInput</function>
3358 <primary>PQconsumeInput</primary>
3364 If input is available from the server, consume it.
3366 int PQconsumeInput(PGconn *conn);
3371 <function>PQconsumeInput</function> normally returns 1 indicating
3372 <quote>no error</quote>, but returns 0 if there was some kind of
3373 trouble (in which case <function>PQerrorMessage</function> can be
3374 consulted). Note that the result does not say whether any input
3375 data was actually collected. After calling
3376 <function>PQconsumeInput</function>, the application can check
3377 <function>PQisBusy</function> and/or
3378 <function>PQnotifies</function> to see if their state has changed.
3382 <function>PQconsumeInput</function> can be called even if the
3383 application is not prepared to deal with a result or notification
3384 just yet. The function will read available data and save it in
3385 a buffer, thereby causing a <function>select()</function>
3386 read-ready indication to go away. The application can thus use
3387 <function>PQconsumeInput</function> to clear the
3388 <function>select()</function> condition immediately, and then
3389 examine the results at leisure.
3396 <function>PQisBusy</function>
3398 <primary>PQisBusy</primary>
3404 Returns 1 if a command is busy, that is,
3405 <function>PQgetResult</function> would block waiting for input.
3406 A 0 return indicates that <function>PQgetResult</function> can be
3407 called with assurance of not blocking.
3409 int PQisBusy(PGconn *conn);
3414 <function>PQisBusy</function> will not itself attempt to read data
3415 from the server; therefore <function>PQconsumeInput</function>
3416 must be invoked first, or the busy state will never end.
3424 A typical application using these functions will have a main loop that
3425 uses <function>select()</function> or <function>poll()</> to wait for
3426 all the conditions that it must respond to. One of the conditions
3427 will be input available from the server, which in terms of
3428 <function>select()</function> means readable data on the file
3429 descriptor identified by <function>PQsocket</function>. When the main
3430 loop detects input ready, it should call
3431 <function>PQconsumeInput</function> to read the input. It can then
3432 call <function>PQisBusy</function>, followed by
3433 <function>PQgetResult</function> if <function>PQisBusy</function>
3434 returns false (0). It can also call <function>PQnotifies</function>
3435 to detect <command>NOTIFY</> messages (see <xref
3436 linkend="libpq-notify">).
3441 <function>PQsendQuery</function>/<function>PQgetResult</function>
3442 can also attempt to cancel a command that is still being processed
3443 by the server; see <xref linkend="libpq-cancel">. But regardless of
3444 the return value of <function>PQcancel</function>, the application
3445 must continue with the normal result-reading sequence using
3446 <function>PQgetResult</function>. A successful cancellation will
3447 simply cause the command to terminate sooner than it would have
3452 By using the functions described above, it is possible to avoid
3453 blocking while waiting for input from the database server. However,
3454 it is still possible that the application will block waiting to send
3455 output to the server. This is relatively uncommon but can happen if
3456 very long SQL commands or data values are sent. (It is much more
3457 probable if the application sends data via <command>COPY IN</command>,
3458 however.) To prevent this possibility and achieve completely
3459 nonblocking database operation, the following additional functions
3465 <function>PQsetnonblocking</function>
3467 <primary>PQsetnonblocking</primary>
3473 Sets the nonblocking status of the connection.
3475 int PQsetnonblocking(PGconn *conn, int arg);
3480 Sets the state of the connection to nonblocking if
3481 <parameter>arg</parameter> is 1, or blocking if
3482 <parameter>arg</parameter> is 0. Returns 0 if OK, -1 if error.
3486 In the nonblocking state, calls to
3487 <function>PQsendQuery</function>, <function>PQputline</function>,
3488 <function>PQputnbytes</function>, and
3489 <function>PQendcopy</function> will not block but instead return
3490 an error if they need to be called again.
3494 Note that <function>PQexec</function> does not honor nonblocking
3495 mode; if it is called, it will act in blocking fashion anyway.
3502 <function>PQisnonblocking</function>
3504 <primary>PQisnonblocking</primary>
3510 Returns the blocking status of the database connection.
3512 int PQisnonblocking(const PGconn *conn);
3517 Returns 1 if the connection is set to nonblocking mode and 0 if
3525 <function>PQflush</function>
3527 <primary>PQflush</primary>
3533 Attempts to flush any queued output data to the server. Returns
3534 0 if successful (or if the send queue is empty), -1 if it failed
3535 for some reason, or 1 if it was unable to send all the data in
3536 the send queue yet (this case can only occur if the connection
3539 int PQflush(PGconn *conn);
3548 After sending any command or data on a nonblocking connection, call
3549 <function>PQflush</function>. If it returns 1, wait for the socket
3550 to be write-ready and call it again; repeat until it returns 0. Once
3551 <function>PQflush</function> returns 0, wait for the socket to be
3552 read-ready and then read the response as described above.
3557 <sect1 id="libpq-cancel">
3558 <title>Cancelling Queries in Progress</title>
3560 <indexterm zone="libpq-cancel">
3561 <primary>canceling</primary>
3562 <secondary>SQL command</secondary>
3566 A client application can request cancellation of a command that is
3567 still being processed by the server, using the functions described in
3573 <function>PQgetCancel</function>
3575 <primary>PQgetCancel</primary>
3581 Creates a data structure containing the information needed to cancel
3582 a command issued through a particular database connection.
3584 PGcancel *PQgetCancel(PGconn *conn);
3589 <function>PQgetCancel</function> creates a
3590 <structname>PGcancel</><indexterm><primary>PGcancel</></> object
3591 given a <structname>PGconn</> connection object. It will return
3592 NULL if the given <parameter>conn</> is NULL or an invalid
3593 connection. The <structname>PGcancel</> object is an opaque
3594 structure that is not meant to be accessed directly by the
3595 application; it can only be passed to <function>PQcancel</function>
3596 or <function>PQfreeCancel</function>.
3603 <function>PQfreeCancel</function>
3605 <primary>PQfreeCancel</primary>
3611 Frees a data structure created by <function>PQgetCancel</function>.
3613 void PQfreeCancel(PGcancel *cancel);
3618 <function>PQfreeCancel</function> frees a data object previously created
3619 by <function>PQgetCancel</function>.
3626 <function>PQcancel</function>
3628 <primary>PQcancel</primary>
3634 Requests that the server abandon processing of the current command.
3636 int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
3641 The return value is 1 if the cancel request was successfully
3642 dispatched and 0 if not. If not, <parameter>errbuf</> is filled
3643 with an error message explaining why not. <parameter>errbuf</>
3644 must be a char array of size <parameter>errbufsize</> (the
3645 recommended size is 256 bytes).
3649 Successful dispatch is no guarantee that the request will have
3650 any effect, however. If the cancellation is effective, the current
3651 command will terminate early and return an error result. If the
3652 cancellation fails (say, because the server was already done
3653 processing the command), then there will be no visible result at
3658 <function>PQcancel</function> can safely be invoked from a signal
3659 handler, if the <parameter>errbuf</> is a local variable in the
3660 signal handler. The <structname>PGcancel</> object is read-only
3661 as far as <function>PQcancel</function> is concerned, so it can
3662 also be invoked from a thread that is separate from the one
3663 manipulating the <structname>PGconn</> object.
3672 <function>PQrequestCancel</function>
3674 <primary>PQrequestCancel</primary>
3680 Requests that the server abandon processing of the current
3683 int PQrequestCancel(PGconn *conn);
3688 <function>PQrequestCancel</function> is a deprecated variant of
3689 <function>PQcancel</function>. It operates directly on the
3690 <structname>PGconn</> object, and in case of failure stores the
3691 error message in the <structname>PGconn</> object (whence it can
3692 be retrieved by <function>PQerrorMessage</function>). Although
3693 the functionality is the same, this approach creates hazards for
3694 multiple-thread programs and signal handlers, since it is possible
3695 that overwriting the <structname>PGconn</>'s error message will
3696 mess up the operation currently in progress on the connection.
3705 <sect1 id="libpq-fastpath">
3706 <title>The Fast-Path Interface</title>
3708 <indexterm zone="libpq-fastpath">
3709 <primary>fast path</primary>
3713 <productname>PostgreSQL</productname> provides a fast-path interface
3714 to send simple function calls to the server.
3719 This interface is somewhat obsolete, as one can achieve similar
3720 performance and greater functionality by setting up a prepared
3721 statement to define the function call. Then, executing the statement
3722 with binary transmission of parameters and results substitutes for a
3723 fast-path function call.
3728 The function <function>PQfn</function><indexterm><primary>PQfn</></>
3729 requests execution of a server function via the fast-path interface:
3731 PGresult *PQfn(PGconn *conn,
3736 const PQArgBlock *args,
3751 The <parameter>fnid</> argument is the OID of the function to be
3752 executed. <parameter>args</> and <parameter>nargs</> define the
3753 parameters to be passed to the function; they must match the declared
3754 function argument list. When the <parameter>isint</> field of a
3755 parameter structure is true, the <parameter>u.integer</> value is sent
3756 to the server as an integer of the indicated length (this must be 1,
3757 2, or 4 bytes); proper byte-swapping occurs. When <parameter>isint</>
3758 is false, the indicated number of bytes at <parameter>*u.ptr</> are
3759 sent with no processing; the data must be in the format expected by
3760 the server for binary transmission of the function's argument data
3761 type. <parameter>result_buf</parameter> is the buffer in which to
3762 place the return value. The caller must have allocated sufficient
3763 space to store the return value. (There is no check!) The actual result
3764 length will be returned in the integer pointed to by
3765 <parameter>result_len</parameter>. If a 1, 2, or 4-byte integer result
3766 is expected, set <parameter>result_is_int</parameter> to 1, otherwise
3767 set it to 0. Setting <parameter>result_is_int</parameter> to 1 causes
3768 <application>libpq</> to byte-swap the value if necessary, so that it
3769 is delivered as a proper <type>int</type> value for the client machine.
3770 When <parameter>result_is_int</> is 0, the binary-format byte string
3771 sent by the server is returned unmodified.
3775 <function>PQfn</function> always returns a valid
3776 <structname>PGresult</structname> pointer. The result status should be
3777 checked before the result is used. The caller is responsible for
3778 freeing the <structname>PGresult</structname> with
3779 <function>PQclear</function> when it is no longer needed.
3783 Note that it is not possible to handle null arguments, null results,
3784 nor set-valued results when using this interface.
3789 <sect1 id="libpq-notify">
3790 <title>Asynchronous Notification</title>
3792 <indexterm zone="libpq-notify">
3793 <primary>NOTIFY</primary>
3794 <secondary>in libpq</secondary>
3798 <productname>PostgreSQL</productname> offers asynchronous notification
3799 via the <command>LISTEN</command> and <command>NOTIFY</command>
3800 commands. A client session registers its interest in a particular
3801 notification condition with the <command>LISTEN</command> command (and
3802 can stop listening with the <command>UNLISTEN</command> command). All
3803 sessions listening on a particular condition will be notified
3804 asynchronously when a <command>NOTIFY</command> command with that
3805 condition name is executed by any session. No additional information
3806 is passed from the notifier to the listener. Thus, typically, any
3807 actual data that needs to be communicated is transferred through a
3808 database table. Commonly, the condition name is the same as the
3809 associated table, but it is not necessary for there to be any associated
3814 <application>libpq</application> applications submit
3815 <command>LISTEN</command> and <command>UNLISTEN</command> commands as
3816 ordinary SQL commands. The arrival of <command>NOTIFY</command>
3817 messages can subsequently be detected by calling
3818 <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
3822 The function <function>PQnotifies</function>
3823 returns the next notification from a list of unhandled
3824 notification messages received from the server. It returns a null pointer if
3825 there are no pending notifications. Once a notification is
3826 returned from <function>PQnotifies</>, it is considered handled and will be
3827 removed from the list of notifications.
3829 PGnotify *PQnotifies(PGconn *conn);
3831 typedef struct pgNotify {
3832 char *relname; /* notification condition name */
3833 int be_pid; /* process ID of notifying server process */
3834 char *extra; /* notification parameter */
3837 After processing a <structname>PGnotify</structname> object returned
3838 by <function>PQnotifies</function>, be sure to free it with
3839 <function>PQfreemem</function>. It is sufficient to free the
3840 <structname>PGnotify</structname> pointer; the
3841 <structfield>relname</structfield> and <structfield>extra</structfield>
3842 fields do not represent separate allocations. (At present, the
3843 <structfield>extra</structfield> field is unused and will always point
3844 to an empty string.)
3848 <xref linkend="libpq-example-2"> gives a sample program that illustrates
3849 the use of asynchronous notification.
3853 <function>PQnotifies</function> does not actually read data from the
3854 server; it just returns messages previously absorbed by another
3855 <application>libpq</application> function. In prior releases of
3856 <application>libpq</application>, the only way to ensure timely receipt
3857 of <command>NOTIFY</> messages was to constantly submit commands, even
3858 empty ones, and then check <function>PQnotifies</function> after each
3859 <function>PQexec</function>. While this still works, it is deprecated
3860 as a waste of processing power.
3864 A better way to check for <command>NOTIFY</> messages when you have no
3865 useful commands to execute is to call
3866 <function>PQconsumeInput</function>, then check
3867 <function>PQnotifies</function>. You can use
3868 <function>select()</function> to wait for data to arrive from the
3869 server, thereby using no <acronym>CPU</acronym> power unless there is
3870 something to do. (See <function>PQsocket</function> to obtain the file
3871 descriptor number to use with <function>select()</function>.) Note that
3872 this will work OK whether you submit commands with
3873 <function>PQsendQuery</function>/<function>PQgetResult</function> or
3874 simply use <function>PQexec</function>. You should, however, remember
3875 to check <function>PQnotifies</function> after each
3876 <function>PQgetResult</function> or <function>PQexec</function>, to
3877 see if any notifications came in during the processing of the command.
3882 <sect1 id="libpq-copy">
3883 <title>Functions Associated with the <command>COPY</command> Command</title>
3885 <indexterm zone="libpq-copy">
3886 <primary>COPY</primary>
3887 <secondary>with libpq</secondary>
3891 The <command>COPY</command> command in
3892 <productname>PostgreSQL</productname> has options to read from or write
3893 to the network connection used by <application>libpq</application>.
3894 The functions described in this section allow applications to take
3895 advantage of this capability by supplying or consuming copied data.
3899 The overall process is that the application first issues the SQL
3900 <command>COPY</command> command via <function>PQexec</function> or one
3901 of the equivalent functions. The response to this (if there is no
3902 error in the command) will be a <structname>PGresult</> object bearing
3903 a status code of <literal>PGRES_COPY_OUT</literal> or
3904 <literal>PGRES_COPY_IN</literal> (depending on the specified copy
3905 direction). The application should then use the functions of this
3906 section to receive or transmit data rows. When the data transfer is
3907 complete, another <structname>PGresult</> object is returned to indicate
3908 success or failure of the transfer. Its status will be
3909 <literal>PGRES_COMMAND_OK</literal> for success or
3910 <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
3911 At this point further SQL commands can be issued via
3912 <function>PQexec</function>. (It is not possible to execute other SQL
3913 commands using the same connection while the <command>COPY</command>
3914 operation is in progress.)
3918 If a <command>COPY</command> command is issued via
3919 <function>PQexec</function> in a string that could contain additional
3920 commands, the application must continue fetching results via
3921 <function>PQgetResult</> after completing the <command>COPY</command>
3922 sequence. Only when <function>PQgetResult</> returns
3923 <symbol>NULL</symbol> is it certain that the <function>PQexec</function>
3924 command string is done and it is safe to issue more commands.
3928 The functions of this section should be executed only after obtaining
3929 a result status of <literal>PGRES_COPY_OUT</literal> or
3930 <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
3931 <function>PQgetResult</function>.
3935 A <structname>PGresult</> object bearing one of these status values
3936 carries some additional data about the <command>COPY</command> operation
3937 that is starting. This additional data is available using functions
3938 that are also used in connection with query results:
3943 <function>PQnfields</function>
3945 <primary>PQnfields</primary>
3946 <secondary>with COPY</secondary>
3952 Returns the number of columns (fields) to be copied.
3959 <function>PQbinaryTuples</function>
3961 <primary>PQbinaryTuples</primary>
3962 <secondary>with COPY</secondary>
3968 0 indicates the overall copy format is textual (rows separated by
3969 newlines, columns separated by separator characters, etc). 1
3970 indicates the overall copy format is binary. See <xref
3971 linkend="sql-copy" endterm="sql-copy-title"> for more information.
3978 <function>PQfformat</function>
3980 <primary>PQfformat</primary>
3981 <secondary>with COPY</secondary>
3987 Returns the format code (0 for text, 1 for binary) associated with
3988 each column of the copy operation. The per-column format codes
3989 will always be zero when the overall copy format is textual, but
3990 the binary format can support both text and binary columns.
3991 (However, as of the current implementation of <command>COPY</>,
3992 only binary columns appear in a binary copy; so the per-column
3993 formats always match the overall format at present.)
4002 These additional data values are only available when using protocol
4003 3.0. When using protocol 2.0, all these functions will return 0.
4007 <sect2 id="libpq-copy-send">
4008 <title>Functions for Sending <command>COPY</command> Data</title>
4011 These functions are used to send data during <literal>COPY FROM
4012 STDIN</>. They will fail if called when the connection is not in
4013 <literal>COPY_IN</> state.
4019 <function>PQputCopyData</function>
4021 <primary>PQputCopyData</primary>
4027 Sends data to the server during <literal>COPY_IN</> state.
4029 int PQputCopyData(PGconn *conn,
4036 Transmits the <command>COPY</command> data in the specified
4037 <parameter>buffer</>, of length <parameter>nbytes</>, to the server.
4038 The result is 1 if the data was sent, zero if it was not sent
4039 because the attempt would block (this case is only possible if the
4040 connection is in nonblocking mode), or -1 if an error occurred.
4041 (Use <function>PQerrorMessage</function> to retrieve details if
4042 the return value is -1. If the value is zero, wait for write-ready
4047 The application can divide the <command>COPY</command> data stream
4048 into buffer loads of any convenient size. Buffer-load boundaries
4049 have no semantic significance when sending. The contents of the
4050 data stream must match the data format expected by the
4051 <command>COPY</> command; see <xref linkend="sql-copy"
4052 endterm="sql-copy-title"> for details.
4059 <function>PQputCopyEnd</function>
4061 <primary>PQputCopyEnd</primary>
4067 Sends end-of-data indication to the server during <literal>COPY_IN</> state.
4069 int PQputCopyEnd(PGconn *conn,
4070 const char *errormsg);
4075 Ends the <literal>COPY_IN</> operation successfully if
4076 <parameter>errormsg</> is <symbol>NULL</symbol>. If
4077 <parameter>errormsg</> is not <symbol>NULL</symbol> then the
4078 <command>COPY</> is forced to fail, with the string pointed to by
4079 <parameter>errormsg</> used as the error message. (One should not
4080 assume that this exact error message will come back from the server,
4081 however, as the server might have already failed the
4082 <command>COPY</> for its own reasons. Also note that the option
4083 to force failure does not work when using pre-3.0-protocol
4088 The result is 1 if the termination data was sent, zero if it was
4089 not sent because the attempt would block (this case is only possible
4090 if the connection is in nonblocking mode), or -1 if an error
4091 occurred. (Use <function>PQerrorMessage</function> to retrieve
4092 details if the return value is -1. If the value is zero, wait for
4093 write-ready and try again.)
4097 After successfully calling <function>PQputCopyEnd</>, call
4098 <function>PQgetResult</> to obtain the final result status of the
4099 <command>COPY</> command. One can wait for this result to be
4100 available in the usual way. Then return to normal operation.
4108 <sect2 id="libpq-copy-receive">
4109 <title>Functions for Receiving <command>COPY</command> Data</title>
4112 These functions are used to receive data during <literal>COPY TO
4113 STDOUT</>. They will fail if called when the connection is not in
4114 <literal>COPY_OUT</> state.
4120 <function>PQgetCopyData</function>
4122 <primary>PQgetCopyData</primary>
4128 Receives data from the server during <literal>COPY_OUT</> state.
4130 int PQgetCopyData(PGconn *conn,
4137 Attempts to obtain another row of data from the server during a
4138 <command>COPY</command>. Data is always returned one data row at
4139 a time; if only a partial row is available, it is not returned.
4140 Successful return of a data row involves allocating a chunk of
4141 memory to hold the data. The <parameter>buffer</> parameter must
4142 be non-<symbol>NULL</symbol>. <parameter>*buffer</> is set to
4143 point to the allocated memory, or to <symbol>NULL</symbol> in cases
4144 where no buffer is returned. A non-<symbol>NULL</symbol> result
4145 buffer must be freed using <function>PQfreemem</> when no longer
4150 When a row is successfully returned, the return value is the number
4151 of data bytes in the row (this will always be greater than zero).
4152 The returned string is always null-terminated, though this is
4153 probably only useful for textual <command>COPY</command>. A result
4154 of zero indicates that the <command>COPY</command> is still in
4155 progress, but no row is yet available (this is only possible when
4156 <parameter>async</> is true). A result of -1 indicates that the
4157 <command>COPY</command> is done. A result of -2 indicates that an
4158 error occurred (consult <function>PQerrorMessage</> for the reason).
4162 When <parameter>async</> is true (not zero),
4163 <function>PQgetCopyData</> will not block waiting for input; it
4164 will return zero if the <command>COPY</command> is still in progress
4165 but no complete row is available. (In this case wait for read-ready
4166 and then call <function>PQconsumeInput</> before calling
4167 <function>PQgetCopyData</> again.) When <parameter>async</> is
4168 false (zero), <function>PQgetCopyData</> will block until data is
4169 available or the operation completes.
4173 After <function>PQgetCopyData</> returns -1, call
4174 <function>PQgetResult</> to obtain the final result status of the
4175 <command>COPY</> command. One can wait for this result to be
4176 available in the usual way. Then return to normal operation.
4184 <sect2 id="libpq-copy-deprecated">
4185 <title>Obsolete Functions for <command>COPY</command></title>
4188 These functions represent older methods of handling <command>COPY</>.
4189 Although they still work, they are deprecated due to poor error handling,
4190 inconvenient methods of detecting end-of-data, and lack of support for binary
4191 or nonblocking transfers.
4197 <function>PQgetline</function>
4199 <primary>PQgetline</primary>
4205 Reads a newline-terminated line of characters (transmitted
4206 by the server) into a buffer string of size <parameter>length</>.
4208 int PQgetline(PGconn *conn,
4215 This function copies up to <parameter>length</>-1 characters into
4216 the buffer and converts the terminating newline into a zero byte.
4217 <function>PQgetline</function> returns <symbol>EOF</symbol> at the
4218 end of input, 0 if the entire line has been read, and 1 if the
4219 buffer is full but the terminating newline has not yet been read.
4222 Note that the application must check to see if a new line consists
4223 of the two characters <literal>\.</literal>, which indicates
4224 that the server has finished sending the results of the
4225 <command>COPY</command> command. If the application might receive
4226 lines that are more than <parameter>length</>-1 characters long,
4227 care is needed to be sure it recognizes the <literal>\.</literal>
4228 line correctly (and does not, for example, mistake the end of a
4229 long data line for a terminator line).
4236 <function>PQgetlineAsync</function>
4238 <primary>PQgetlineAsync</primary>
4244 Reads a row of <command>COPY</command> data (transmitted by the
4245 server) into a buffer without blocking.
4247 int PQgetlineAsync(PGconn *conn,
4254 This function is similar to <function>PQgetline</function>, but it can be used
4256 that must read <command>COPY</command> data asynchronously, that is, without blocking.
4257 Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
4259 application should call <function>PQconsumeInput</function> and
4260 <function>PQgetlineAsync</function> until the
4261 end-of-data signal is detected.
4264 Unlike <function>PQgetline</function>, this function takes
4265 responsibility for detecting end-of-data.
4269 On each call, <function>PQgetlineAsync</function> will return data if a
4270 complete data row is available in <application>libpq</>'s input buffer.
4271 Otherwise, no data is returned until the rest of the row arrives.
4272 The function returns -1 if the end-of-copy-data marker has been recognized,
4273 or 0 if no data is available, or a positive number giving the number of
4274 bytes of data returned. If -1 is returned, the caller must next call
4275 <function>PQendcopy</function>, and then return to normal processing.
4279 The data returned will not extend beyond a data-row boundary. If possible
4280 a whole row will be returned at one time. But if the buffer offered by
4281 the caller is too small to hold a row sent by the server, then a partial
4282 data row will be returned. With textual data this can be detected by testing
4283 whether the last returned byte is <literal>\n</literal> or not. (In a binary
4284 <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
4285 equivalent determination.)
4286 The returned string is not null-terminated. (If you want to add a
4287 terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
4288 than the room actually available.)
4295 <function>PQputline</function>
4297 <primary>PQputline</primary>
4303 Sends a null-terminated string to the server. Returns 0 if
4304 OK and <symbol>EOF</symbol> if unable to send the string.
4306 int PQputline(PGconn *conn,
4307 const char *string);
4312 The <command>COPY</command> data stream sent by a series of calls
4313 to <function>PQputline</function> has the same format as that
4314 returned by <function>PQgetlineAsync</function>, except that
4315 applications are not obliged to send exactly one data row per
4316 <function>PQputline</function> call; it is okay to send a partial
4317 line or multiple lines per call.
4322 Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
4323 for the application to explicitly send the two characters
4324 <literal>\.</literal> as a final line to indicate to the server that it had
4325 finished sending <command>COPY</> data. While this still works, it is deprecated and the
4326 special meaning of <literal>\.</literal> can be expected to be removed in a
4327 future release. It is sufficient to call <function>PQendcopy</function> after
4328 having sent the actual data.
4336 <function>PQputnbytes</function>
4338 <primary>PQputnbytes</primary>
4344 Sends a non-null-terminated string to the server. Returns
4345 0 if OK and <symbol>EOF</symbol> if unable to send the string.
4347 int PQputnbytes(PGconn *conn,
4354 This is exactly like <function>PQputline</function>, except that the data
4355 buffer need not be null-terminated since the number of bytes to send is
4356 specified directly. Use this procedure when sending binary data.
4363 <function>PQendcopy</function>
4365 <primary>PQendcopy</primary>
4371 Synchronizes with the server.
4373 int PQendcopy(PGconn *conn);
4375 This function waits until the server has finished the copying.
4376 It should either be issued when the last string has been sent
4377 to the server using <function>PQputline</function> or when the
4378 last string has been received from the server using
4379 <function>PGgetline</function>. It must be issued or the server
4380 will get <quote>out of sync</quote> with the client. Upon return
4381 from this function, the server is ready to receive the next SQL
4382 command. The return value is 0 on successful completion,
4383 nonzero otherwise. (Use <function>PQerrorMessage</function> to
4384 retrieve details if the return value is nonzero.)
4388 When using <function>PQgetResult</function>, the application should
4389 respond to a <literal>PGRES_COPY_OUT</literal> result by executing
4390 <function>PQgetline</function> repeatedly, followed by
4391 <function>PQendcopy</function> after the terminator line is seen.
4392 It should then return to the <function>PQgetResult</function> loop
4393 until <function>PQgetResult</function> returns a null pointer.
4394 Similarly a <literal>PGRES_COPY_IN</literal> result is processed
4395 by a series of <function>PQputline</function> calls followed by
4396 <function>PQendcopy</function>, then return to the
4397 <function>PQgetResult</function> loop. This arrangement will
4398 ensure that a <command>COPY</command> command embedded in a series
4399 of <acronym>SQL</acronym> commands will be executed correctly.
4403 Older applications are likely to submit a <command>COPY</command>
4404 via <function>PQexec</function> and assume that the transaction
4405 is done after <function>PQendcopy</function>. This will work
4406 correctly only if the <command>COPY</command> is the only
4407 <acronym>SQL</acronym> command in the command string.
4417 <sect1 id="libpq-control">
4418 <title>Control Functions</title>
4421 These functions control miscellaneous details of <application>libpq</>'s
4428 <function>PQclientEncoding</function>
4430 <primary>PQclientEncoding</primary>
4436 Returns the client encoding.
4438 int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
4441 Note that it returns the encoding ID, not a symbolic string
4442 such as <literal>EUC_JP</literal>. To convert an encoding ID to an encoding name, you
4446 char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
4454 <function>PQsetClientEncoding</function>
4456 <primary>PQsetClientEncoding</primary>
4462 Sets the client encoding.
4464 int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
4467 <replaceable>conn</replaceable> is a connection to the server,
4468 and <replaceable>encoding</replaceable> is the encoding you want to
4469 use. If the function successfully sets the encoding, it returns 0,
4470 otherwise -1. The current encoding for this connection can be
4471 determined by using <function>PQclientEncoding</>.
4478 <function>PQsetErrorVerbosity</function>
4480 <primary>PQsetErrorVerbosity</primary>
4486 Determines the verbosity of messages returned by
4487 <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
4495 PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
4498 <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
4499 the connection's previous setting. In <firstterm>TERSE</> mode,
4500 returned messages include severity, primary text, and position only;
4501 this will normally fit on a single line. The default mode produces
4502 messages that include the above plus any detail, hint, or context
4503 fields (these might span multiple lines). The <firstterm>VERBOSE</>
4504 mode includes all available fields. Changing the verbosity does not
4505 affect the messages available from already-existing
4506 <structname>PGresult</> objects, only subsequently-created ones.
4513 <function>PQtrace</function>
4515 <primary>PQtrace</primary>
4521 Enables tracing of the client/server communication to a debugging file stream.
4523 void PQtrace(PGconn *conn, FILE *stream);
4529 On Windows, if the <application>libpq</> library and an application are
4530 compiled with different flags, this function call will crash the
4531 application because the internal representation of the <literal>FILE</>
4532 pointers differ. Specifically, multithreaded/single-threaded,
4533 release/debug, and static/dynamic flags should be the same for the
4534 library and all applications using that library.
4543 <function>PQuntrace</function>
4545 <primary>PQuntrace</primary>
4551 Disables tracing started by <function>PQtrace</function>.
4553 void PQuntrace(PGconn *conn);
4562 <sect1 id="libpq-misc">
4563 <title>Miscellaneous Functions</title>
4566 As always, there are some functions that just don't fit anywhere.
4572 <function>PQencryptPassword</function>
4574 <primary>PQencryptPassword</primary>
4580 Prepares the encrypted form of a <productname>PostgreSQL</> password.
4582 char * PQencryptPassword(const char *passwd, const char *user);
4584 This function is intended to be used by client applications that
4585 wish to send commands like <literal>ALTER USER joe PASSWORD
4586 'pwd'</>. It is good practice not to send the original cleartext
4587 password in such a command, because it might be exposed in command
4588 logs, activity displays, and so on. Instead, use this function to
4589 convert the password to encrypted form before it is sent. The
4590 arguments are the cleartext password, and the SQL name of the user
4591 it is for. The return value is a string allocated by
4592 <function>malloc</function>, or <symbol>NULL</symbol> if out of
4593 memory. The caller can assume the string doesn't contain any
4594 special characters that would require escaping. Use
4595 <function>PQfreemem</> to free the result when done with it.
4603 <sect1 id="libpq-notice-processing">
4604 <title>Notice Processing</title>
4606 <indexterm zone="libpq-notice-processing">
4607 <primary>notice processing</primary>
4608 <secondary>in libpq</secondary>
4612 Notice and warning messages generated by the server are not returned
4613 by the query execution functions, since they do not imply failure of
4614 the query. Instead they are passed to a notice handling function, and
4615 execution continues normally after the handler returns. The default
4616 notice handling function prints the message on
4617 <filename>stderr</filename>, but the application can override this
4618 behavior by supplying its own handling function.
4622 For historical reasons, there are two levels of notice handling, called
4623 the notice receiver and notice processor. The default behavior is for
4624 the notice receiver to format the notice and pass a string to the notice
4625 processor for printing. However, an application that chooses to provide
4626 its own notice receiver will typically ignore the notice processor
4627 layer and just do all the work in the notice receiver.
4631 The function <function>PQsetNoticeReceiver</function>
4632 <indexterm><primary>notice
4633 receiver</></><indexterm><primary>PQsetNoticeReceiver</></> sets or
4634 examines the current notice receiver for a connection object.
4635 Similarly, <function>PQsetNoticeProcessor</function>
4636 <indexterm><primary>notice
4637 processor</></><indexterm><primary>PQsetNoticeProcessor</></> sets or
4638 examines the current notice processor.
4641 typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
4644 PQsetNoticeReceiver(PGconn *conn,
4645 PQnoticeReceiver proc,
4648 typedef void (*PQnoticeProcessor) (void *arg, const char *message);
4651 PQsetNoticeProcessor(PGconn *conn,
4652 PQnoticeProcessor proc,
4656 Each of these functions returns the previous notice receiver or
4657 processor function pointer, and sets the new value. If you supply a
4658 null function pointer, no action is taken, but the current pointer is
4663 When a notice or warning message is received from the server, or
4664 generated internally by <application>libpq</application>, the notice
4665 receiver function is called. It is passed the message in the form of
4666 a <symbol>PGRES_NONFATAL_ERROR</symbol>
4667 <structname>PGresult</structname>. (This allows the receiver to extract
4668 individual fields using <function>PQresultErrorField</>, or the complete
4669 preformatted message using <function>PQresultErrorMessage</>.) The same
4670 void pointer passed to <function>PQsetNoticeReceiver</function> is also
4671 passed. (This pointer can be used to access application-specific state
4676 The default notice receiver simply extracts the message (using
4677 <function>PQresultErrorMessage</>) and passes it to the notice
4682 The notice processor is responsible for handling a notice or warning
4683 message given in text form. It is passed the string text of the message
4684 (including a trailing newline), plus a void pointer that is the same
4685 one passed to <function>PQsetNoticeProcessor</function>. (This pointer
4686 can be used to access application-specific state if needed.)
4690 The default notice processor is simply:
4693 defaultNoticeProcessor(void *arg, const char *message)
4695 fprintf(stderr, "%s", message);
4701 Once you have set a notice receiver or processor, you should expect
4702 that that function could be called as long as either the
4703 <structname>PGconn</> object or <structname>PGresult</> objects made
4704 from it exist. At creation of a <structname>PGresult</>, the
4705 <structname>PGconn</>'s current notice handling pointers are copied
4706 into the <structname>PGresult</> for possible use by functions like
4707 <function>PQgetvalue</function>.
4712 <sect1 id="libpq-envars">
4713 <title>Environment Variables</title>
4715 <indexterm zone="libpq-envars">
4716 <primary>environment variable</primary>
4720 The following environment variables can be used to select default
4721 connection parameter values, which will be used by
4722 <function>PQconnectdb</>, <function>PQsetdbLogin</> and
4723 <function>PQsetdb</> if no value is directly specified by the calling
4724 code. These are useful to avoid hard-coding database connection
4725 information into simple client applications, for example.
4731 <primary><envar>PGHOST</envar></primary>
4733 <envar>PGHOST</envar> sets the database server name.
4734 If this begins with a slash, it specifies Unix-domain communication
4735 rather than TCP/IP communication; the value is then the name of the
4736 directory in which the socket file is stored (in a default installation
4737 setup this would be <filename>/tmp</filename>).
4744 <primary><envar>PGHOSTADDR</envar></primary>
4746 <envar>PGHOSTADDR</envar> specifies the numeric IP address of the database
4747 server. This can be set instead of or in addition to <envar>PGHOST</envar>
4748 to avoid DNS lookup overhead. See the documentation of
4749 these parameters, under <function>PQconnectdb</function> above, for details
4750 on their interaction.
4753 When neither <envar>PGHOST</envar> nor <envar>PGHOSTADDR</envar> is set,
4754 the default behavior is to connect using a local Unix-domain socket; or on
4755 machines without Unix-domain sockets, <application>libpq</application> will
4756 attempt to connect to <literal>localhost</>.
4763 <primary><envar>PGPORT</envar></primary>
4765 <envar>PGPORT</envar> sets the TCP port number or Unix-domain socket
4766 file extension for communicating with the
4767 <productname>PostgreSQL</productname> server.
4774 <primary><envar>PGDATABASE</envar></primary>
4776 <envar>PGDATABASE</envar> sets the
4777 <productname>PostgreSQL</productname> database name.
4784 <primary><envar>PGUSER</envar></primary>
4786 <envar>PGUSER</envar> sets the user name used to connect to the
4794 <primary><envar>PGPASSWORD</envar></primary>
4796 <envar>PGPASSWORD</envar> sets the password used if the server
4797 demands password authentication. Use of this environment variable
4798 is not recommended for security reasons (some operating systems
4799 allow non-root users to see process environment variables via
4800 <application>ps</>); instead consider using the
4801 <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
4808 <primary><envar>PGPASSFILE</envar></primary>
4810 <envar>PGPASSFILE</envar> specifies the name of the password file to
4811 use for lookups. If not set, it defaults to <filename>~/.pgpass</>
4812 (see <xref linkend="libpq-pgpass">).
4819 <primary><envar>PGSERVICE</envar></primary>
4821 <envar>PGSERVICE</envar>
4822 sets the service name to be looked up in
4823 <filename>pg_service.conf</filename>. This offers a shorthand way
4824 of setting all the parameters.
4831 <primary><envar>PGREALM</envar></primary>
4833 <envar>PGREALM</envar> sets the Kerberos realm to use with
4834 <productname>PostgreSQL</productname>, if it is different from the
4835 local realm. If <envar>PGREALM</envar> is set,
4836 <application>libpq</application> applications will attempt
4837 authentication with servers for this realm and use separate ticket
4838 files to avoid conflicts with local ticket files. This
4839 environment variable is only used if Kerberos authentication is
4840 selected by the server.
4847 <primary><envar>PGOPTIONS</envar></primary>
4849 <envar>PGOPTIONS</envar> sets additional run-time options for the
4850 <productname>PostgreSQL</productname> server. For example, setting
4851 <envar>PGOPTIONS</envar> to <literal>-c geqo=off</> sets the session's
4852 value of the <varname>geqo</> parameter to <literal>off</>.
4853 For a detailed discussion of the available options consult <xref
4854 linkend="runtime-config">.
4861 <primary><envar>PGSSLMODE</envar></primary>
4863 <envar>PGSSLMODE</envar> determines whether and with what priority
4864 an <acronym>SSL</> connection will be negotiated with the server.
4865 There are four modes: <literal>disable</> will attempt only an
4866 unencrypted <acronym>SSL</> connection; <literal>allow</> will
4867 negotiate, trying first a non-<acronym>SSL</> connection, then if
4868 that fails, trying an <acronym>SSL</> connection; <literal>prefer</>
4869 (the default) will negotiate, trying first an <acronym>SSL</>
4870 connection, then if that fails, trying a regular non-<acronym>SSL</>
4871 connection; <literal>require</> will try only an <acronym>SSL</>
4872 connection. If <productname>PostgreSQL</> is compiled without SSL
4873 support, using option <literal>require</> will cause an error, while
4874 options <literal>allow</> and <literal>prefer</> will be accepted
4875 but <application>libpq</> will not in fact attempt an <acronym>SSL</>
4883 <primary><envar>PGREQUIRESSL</envar></primary>
4885 <envar>PGREQUIRESSL</envar> sets whether or not the connection must
4886 be made over <acronym>SSL</acronym>. If set to <quote>1</quote>,
4887 <application>libpq</> will refuse to connect if the server does not
4888 accept an <acronym>SSL</acronym> connection (equivalent to
4889 <literal>sslmode</> <literal>prefer</>). This option is deprecated
4890 in favor of the <literal>sslmode</> setting, and is only available
4891 if <productname>PostgreSQL</> is compiled with SSL support.
4898 <primary><envar>PGSSLKEY</envar></primary>
4900 <envar>PGSSLKEY</envar> specifies the hardware token that stores the
4901 secret key for the client certificate. The value of this variable
4902 should consist of a colon-separated engine name (engines are
4903 <productname>OpenSSL</> loadable modules) and an engine-specific key
4904 identifier. If this is not set, the secret key must be kept in a
4912 <primary><envar>PGKRBSRVNAME</envar></primary>
4914 <envar>PGKRBSRVNAME</envar> sets the Kerberos service name to use
4915 when authenticating with Kerberos 5 or GSSAPI.
4922 <primary><envar>PGGSSLIB</envar></primary>
4924 <envar>PGGSSLIB</envar> sets the GSS library to use for GSSAPI
4932 <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
4934 <envar>PGCONNECT_TIMEOUT</envar> sets the maximum number of seconds
4935 that <application>libpq</application> will wait when attempting to
4936 connect to the <productname>PostgreSQL</productname> server. If
4937 unset or set to zero, <application>libpq</application> will wait
4938 indefinitely. It is not recommended to set the timeout to less than
4946 The following environment variables can be used to specify default
4947 behavior for each <productname>PostgreSQL</productname> session. (See
4948 also the <xref linkend="sql-alteruser" endterm="sql-alteruser-title">
4949 and <xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">
4950 commands for ways to set default behavior on a per-user or per-database
4957 <primary><envar>PGDATESTYLE</envar></primary>
4959 <envar>PGDATESTYLE</envar> sets the default style of date/time
4960 representation. (Equivalent to <literal>SET datestyle TO
4968 <primary><envar>PGTZ</envar></primary>
4970 <envar>PGTZ</envar> sets the default time zone. (Equivalent to
4971 <literal>SET timezone TO ...</literal>.)
4978 <primary><envar>PGCLIENTENCODING</envar></primary>
4980 <envar>PGCLIENTENCODING</envar> sets the default client character
4981 set encoding. (Equivalent to <literal>SET client_encoding TO
4989 <primary><envar>PGGEQO</envar></primary>
4991 <envar>PGGEQO</envar> sets the default mode for the genetic query
4992 optimizer. (Equivalent to <literal>SET geqo TO ...</literal>.)
4997 Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set"
4998 endterm="sql-set-title"> for information on correct values for these
4999 environment variables.
5003 The following environment variables determine internal behavior of
5004 <application>libpq</application>; they override compiled-in defaults.
5010 <primary><envar>PGSYSCONFDIR</envar></primary>
5012 <envar>PGSYSCONFDIR</envar> sets the directory containing the
5013 <filename>pg_service.conf</> file.
5020 <primary><envar>PGLOCALEDIR</envar></primary>
5022 <envar>PGLOCALEDIR</envar> sets the directory containing the
5023 <literal>locale</> files for message internationalization.
5032 <sect1 id="libpq-pgpass">
5033 <title>The Password File</title>
5035 <indexterm zone="libpq-pgpass">
5036 <primary>password file</primary>
5038 <indexterm zone="libpq-pgpass">
5039 <primary>.pgpass</primary>
5043 The file <filename>.pgpass</filename> in a user's home directory or the
5044 file referenced by <envar>PGPASSFILE</envar> can contain passwords to
5045 be used if the connection requires a password (and no password has been
5046 specified otherwise). On Microsoft Windows the file is named
5047 <filename>%APPDATA%\postgresql\pgpass.conf</> (where
5048 <filename>%APPDATA%</> refers to the Application Data subdirectory in
5049 the user's profile).
5053 This file should contain lines of the following format:
5055 <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
5057 Each of the first four fields can be a literal value, or
5058 <literal>*</literal>, which matches anything. The password field from
5059 the first line that matches the current connection parameters will be
5060 used. (Therefore, put more-specific entries first when you are using
5061 wildcards.) If an entry needs to contain <literal>:</literal> or
5062 <literal>\</literal>, escape this character with <literal>\</literal>.
5063 A host name of <literal>localhost</> matches both TCP (host name
5064 <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
5065 or the default socket directory) connections coming from the local
5070 On Unix systems, the permissions on <filename>.pgpass</filename> must
5071 disallow any access to world or group; achieve this by the command
5072 <command>chmod 0600 ~/.pgpass</command>. If the permissions are less
5073 strict than this, the file will be ignored. On Microsoft Windows, it
5074 is assumed that the file is stored in a directory that is secure, so
5075 no special permissions check is made.
5080 <sect1 id="libpq-pgservice">
5081 <title>The Connection Service File</title>
5083 <indexterm zone="libpq-pgservice">
5084 <primary>connection service file</primary>
5086 <indexterm zone="libpq-pgservice">
5087 <primary>pg_service.conf</primary>
5091 The connection service file allows libpq connection parameters to be
5092 associated with a single service name. That service name can then be
5093 specified by a libpq connection, and the associated settings will be
5094 used. This allows connection parameters to be modified without requiring
5095 a recompile of the libpq application. The service name can also be
5096 specified using the <envar>PGSERVICE</envar> environment variable.
5100 To use this feature, copy
5101 <filename>share/pg_service.conf.sample</filename> to
5102 <filename>etc/pg_service.conf</filename> and edit the file to add
5103 service names and parameters. This file can be used for client-only
5104 installs too. The file's location can also be specified by the
5105 <envar>PGSYSCONFDIR</envar> environment variable.
5110 <sect1 id="libpq-ldap">
5111 <title>LDAP Lookup of Connection Parameters</title>
5113 <indexterm zone="libpq-ldap">
5114 <primary>LDAP connection parameter lookup</primary>
5118 If <application>libpq</application> has been compiled with LDAP support (option
5119 <literal><option>--with-ldap</option></literal> for <command>configure</command>)
5120 it is possible to retrieve connection options like <literal>host</literal>
5121 or <literal>dbname</literal> via LDAP from a central server.
5122 The advantage is that if the connection parameters for a database change,
5123 the connection information doesn't have to be updated on all client machines.
5127 LDAP connection parameter lookup uses the connection service file
5128 <filename>pg_service.conf</filename> (see <xref
5129 linkend="libpq-pgservice">). A line in a
5130 <filename>pg_service.conf</filename> stanza that starts with
5131 <literal>ldap://</literal> will be recognized as an LDAP URL and an
5132 LDAP query will be performed. The result must be a list of
5133 <literal>keyword = value</literal> pairs which will be used to set
5134 connection options. The URL must conform to RFC 1959 and be of the
5137 ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
5139 where <replaceable>hostname</replaceable> defaults to
5140 <literal>localhost</literal> and <replaceable>port</replaceable>
5145 Processing of <filename>pg_service.conf</filename> is terminated after
5146 a successful LDAP lookup, but is continued if the LDAP server cannot
5147 be contacted. This is to provide a fallback with further LDAP URL
5148 lines that point to different LDAP servers, classical <literal>keyword
5149 = value</literal> pairs, or default connection options. If you would
5150 rather get an error message in this case, add a syntactically incorrect
5151 line after the LDAP URL.
5155 A sample LDAP entry that has been created with the LDIF file
5158 dn:cn=mydatabase,dc=mycompany,dc=com
5161 objectclass:groupOfUniqueNames
5163 uniqueMember:host=dbserver.mycompany.com
5164 uniqueMember:port=5439
5165 uniqueMember:dbname=mydb
5166 uniqueMember:user=mydb_user
5167 uniqueMember:sslmode=require
5169 might be queried with the following LDAP URL:
5171 ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)
5176 You can also mix regular service file entries with LDAP lookups.
5177 A complete example for a stanza in <filename>pg_service.conf</filename>
5180 # only host and port are stored in LDAP, specify dbname and user explicitly
5184 ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
5191 <sect1 id="libpq-ssl">
5192 <title>SSL Support</title>
5194 <indexterm zone="libpq-ssl">
5195 <primary>SSL</primary>
5199 <productname>PostgreSQL</> has native support for using <acronym>SSL</>
5200 connections to encrypt client/server communications for increased
5201 security. See <xref linkend="ssl-tcp"> for details about the server-side
5202 <acronym>SSL</> functionality.
5206 <application>libpq</application> reads the system-wide
5207 <productname>OpenSSL</productname> configuration file. By default, this
5208 file is named <filename>openssl.cnf</filename> and is located in the
5209 directory reported by <literal>openssl version -d</>. This default
5210 can be overridden by setting environment variable
5211 <envar>OPENSSL_CONF</envar> to the name of the desired configuration
5216 To verify the server certificate is trustworthy, place certificates of
5217 the certificate authorities (<acronym>CA</acronym>) you trust in the
5218 file <filename>~/.postgresql/root.crt</> in the user's home directory.
5219 (On Microsoft Windows the file is named
5220 <filename>%APPDATA%\postgresql\root.crt</filename>.)
5221 <application>libpq</application> will then verify that the server's
5222 certificate is signed by one of the trusted certificate authorities.
5223 The SSL connection will fail if the server does not present a trusted
5224 certificate. Certificate Revocation List (CRL) entries are also checked
5225 if the file <filename>~/.postgresql/root.crl</filename> exists
5226 (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
5231 If the server requests a trusted client certificate,
5232 <application>libpq</application> will send the certificate stored in
5233 file <filename>~/.postgresql/postgresql.crt</> in the user's home
5234 directory. The certificate must be signed by one of the certificate
5235 authorities (<acronym>CA</acronym>) trusted by the server. A matching
5236 private key file <filename>~/.postgresql/postgresql.key</> must also
5237 be present, unless the secret key for the certificate is stored in a
5238 hardware token, as specified by <envar>PGSSLKEY</envar>. The private
5239 key file must not allow any access to world or group; achieve this by the
5240 command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
5241 On Microsoft Windows these files are named
5242 <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
5243 <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
5244 is no special permissions check since the directory is presumed secure.
5248 If the environment variable <envar>PGSSLKEY</envar> is set, its value
5249 should consist of a colon-separated engine name and key identifier. In
5250 this case, <application>libpq</application> will load the specified
5251 engine, i.e. the <productname>OpenSSL</> module which supports special
5252 hardware, and reference the key with the specified identifier.
5253 Identifiers are engine-specific. Typically, cryptography hardware tokens
5254 do not reveal secret keys to the application. Instead, applications
5255 delegate all cryptography operations which require the secret key to
5260 If you are using <acronym>SSL</> inside your application (in addition
5261 to inside <application>libpq</application>), you can use
5262 <function>PQinitSSL(int)</> to tell <application>libpq</application>
5263 that the <acronym>SSL</> library has already been initialized by your
5265 <!-- If this URL changes replace it with a URL to www.archive.org. -->
5267 url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
5268 for details on the SSL API.
5271 <table id="libpq-ssl-file-usage">
5272 <title>Libpq/Client SSL File Usage</title>
5277 <entry>Contents</entry>
5278 <entry>Effect</entry>
5285 <entry><filename>~/.postgresql/postgresql.crt</></entry>
5286 <entry>client certificate</entry>
5287 <entry>requested by server</entry>
5291 <entry><filename>~/.postgresql/postgresql.key</></entry>
5292 <entry>client private key</entry>
5293 <entry>proves client certificate sent by owner; does not indicate
5294 certificate owner is trustworthy</entry>
5298 <entry><filename>~/.postgresql/root.crt</></entry>
5299 <entry>trusted certificate authorities</entry>
5300 <entry>checks server certificate is signed by a trusted certificate
5305 <entry><filename>~/.postgresql/root.crl</></entry>
5306 <entry>certificates revoked by certificate authorities</entry>
5307 <entry>server certificate must not be on this list</entry>
5317 <sect1 id="libpq-threading">
5318 <title>Behavior in Threaded Programs</title>
5320 <indexterm zone="libpq-threading">
5321 <primary>threads</primary>
5322 <secondary>with libpq</secondary>
5326 <application>libpq</application> is reentrant and thread-safe if the
5327 <filename>configure</filename> command-line option
5328 <literal>--enable-thread-safety</> was used when the
5329 <productname>PostgreSQL</productname> distribution was built. In
5330 addition, you might need to use additional compiler command-line
5331 options when you compile your application code. Refer to your
5332 system's documentation for information about how to build
5333 thread-enabled applications, or look in
5334 <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
5335 and <literal>PTHREAD_LIBS</>. This function allows the querying of
5336 <application>libpq</application>'s thread-safe status:
5342 <function>PQisthreadsafe</function>
5344 <primary>PQisthreadsafe</primary>
5350 Returns the thread safety status of the
5351 <application>libpq</application> library.
5353 int PQisthreadsafe();
5358 Returns 1 if the <application>libpq</application> is thread-safe
5366 One thread restriction is that no two threads attempt to manipulate
5367 the same <structname>PGconn</> object at the same time. In particular,
5368 you cannot issue concurrent commands from different threads through
5369 the same connection object. (If you need to run concurrent commands,
5370 use multiple connections.)
5374 <structname>PGresult</> objects are read-only after creation, and so
5375 can be passed around freely between threads.
5379 The deprecated functions <function>PQrequestCancel</function> and
5380 <function>PQoidStatus</function> are not thread-safe and should not be
5381 used in multithread programs. <function>PQrequestCancel</function>
5382 can be replaced by <function>PQcancel</function>.
5383 <function>PQoidStatus</function> can be replaced by
5384 <function>PQoidValue</function>.
5388 If you are using Kerberos inside your application (in addition to inside
5389 <application>libpq</application>), you will need to do locking around
5390 Kerberos calls because Kerberos functions are not thread-safe. See
5391 function <function>PQregisterThreadLock</> in the
5392 <application>libpq</application> source code for a way to do cooperative
5393 locking between <application>libpq</application> and your application.
5397 If you experience problems with threaded applications, run the program
5398 in <filename>src/tools/thread</> to see if your platform has
5399 thread-unsafe functions. This program is run by
5400 <filename>configure</filename>, but for binary distributions your
5401 library might not match the library used to build the binaries.
5406 <sect1 id="libpq-build">
5407 <title>Building <application>libpq</application> Programs</title>
5409 <indexterm zone="libpq-build">
5410 <primary>compiling</primary>
5411 <secondary>libpq applications</secondary>
5415 To build (i.e., compile and link) a program using
5416 <application>libpq</application> you need to do all of the following
5422 Include the <filename>libpq-fe.h</filename> header file:
5424 #include <libpq-fe.h>
5426 If you failed to do that then you will normally get error messages
5427 from your compiler similar to
5429 foo.c: In function `main':
5430 foo.c:34: `PGconn' undeclared (first use in this function)
5431 foo.c:35: `PGresult' undeclared (first use in this function)
5432 foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
5433 foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
5434 foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
5441 Point your compiler to the directory where the <productname>PostgreSQL</> header
5442 files were installed, by supplying the
5443 <literal>-I<replaceable>directory</replaceable></literal> option
5444 to your compiler. (In some cases the compiler will look into
5445 the directory in question by default, so you can omit this
5446 option.) For instance, your compile command line could look
5449 cc -c -I/usr/local/pgsql/include testprog.c
5451 If you are using makefiles then add the option to the
5452 <varname>CPPFLAGS</varname> variable:
5454 CPPFLAGS += -I/usr/local/pgsql/include
5459 If there is any chance that your program might be compiled by
5460 other users then you should not hardcode the directory location
5461 like that. Instead, you can run the utility
5462 <command>pg_config</command><indexterm><primary>pg_config</><secondary
5463 sortas="libpq">with libpq</></> to find out where the header
5464 files are on the local system:
5466 <prompt>$</prompt> pg_config --includedir
5467 <computeroutput>/usr/local/include</computeroutput>
5472 Failure to specify the correct option to the compiler will
5473 result in an error message such as
5475 testlibpq.c:8:22: libpq-fe.h: No such file or directory
5482 When linking the final program, specify the option
5483 <literal>-lpq</literal> so that the <application>libpq</application>
5484 library gets pulled in, as well as the option
5485 <literal>-L<replaceable>directory</replaceable></literal> to point
5486 the compiler to the directory where the
5487 <application>libpq</application> library resides. (Again, the
5488 compiler will search some directories by default.) For maximum
5489 portability, put the <option>-L</option> option before the
5490 <option>-lpq</option> option. For example:
5492 cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
5497 You can find out the library directory using
5498 <command>pg_config</command> as well:
5500 <prompt>$</prompt> pg_config --libdir
5501 <computeroutput>/usr/local/pgsql/lib</computeroutput>
5506 Error messages that point to problems in this area could look like
5509 testlibpq.o: In function `main':
5510 testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
5511 testlibpq.o(.text+0x71): undefined reference to `PQstatus'
5512 testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
5514 This means you forgot <option>-lpq</option>.
5516 /usr/bin/ld: cannot find -lpq
5518 This means you forgot the <option>-L</option> option or did not
5519 specify the right directory.
5528 <sect1 id="libpq-example">
5529 <title>Example Programs</title>
5532 These examples and others can be found in the
5533 directory <filename>src/test/examples</filename> in the source code
5537 <example id="libpq-example-1">
5538 <title><application>libpq</application> Example Program 1</title>
5544 * Test the C version of libpq, the PostgreSQL frontend library.
5546 #include <stdio.h>
5547 #include <stdlib.h>
5548 #include "libpq-fe.h"
5551 exit_nicely(PGconn *conn)
5558 main(int argc, char **argv)
5560 const char *conninfo;
5568 * If the user supplies a parameter on the command line, use it as the
5569 * conninfo string; otherwise default to setting dbname=postgres and using
5570 * environment variables or defaults for all other connection parameters.
5575 conninfo = "dbname = postgres";
5577 /* Make a connection to the database */
5578 conn = PQconnectdb(conninfo);
5580 /* Check to see that the backend connection was successfully made */
5581 if (PQstatus(conn) != CONNECTION_OK)
5583 fprintf(stderr, "Connection to database failed: %s",
5584 PQerrorMessage(conn));
5589 * Our test case here involves using a cursor, for which we must be inside
5590 * a transaction block. We could do the whole thing with a single
5591 * PQexec() of "select * from pg_database", but that's too trivial to make
5595 /* Start a transaction block */
5596 res = PQexec(conn, "BEGIN");
5597 if (PQresultStatus(res) != PGRES_COMMAND_OK)
5599 fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
5605 * Should PQclear PGresult whenever it is no longer needed to avoid memory
5611 * Fetch rows from pg_database, the system catalog of databases
5613 res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
5614 if (PQresultStatus(res) != PGRES_COMMAND_OK)
5616 fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
5622 res = PQexec(conn, "FETCH ALL in myportal");
5623 if (PQresultStatus(res) != PGRES_TUPLES_OK)
5625 fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
5630 /* first, print out the attribute names */
5631 nFields = PQnfields(res);
5632 for (i = 0; i < nFields; i++)
5633 printf("%-15s", PQfname(res, i));
5636 /* next, print out the rows */
5637 for (i = 0; i < PQntuples(res); i++)
5639 for (j = 0; j < nFields; j++)
5640 printf("%-15s", PQgetvalue(res, i, j));
5646 /* close the portal ... we don't bother to check for errors ... */
5647 res = PQexec(conn, "CLOSE myportal");
5650 /* end the transaction */
5651 res = PQexec(conn, "END");
5654 /* close the connection to the database and cleanup */
5662 <example id="libpq-example-2">
5663 <title><application>libpq</application> Example Program 2</title>
5668 * Test of the asynchronous notification interface
5670 * Start this program, then from psql in another window do
5672 * Repeat four times to get this program to exit.
5674 * Or, if you want to get fancy, try this:
5675 * populate a database with the following commands
5676 * (provided in src/test/examples/testlibpq2.sql):
5678 * CREATE TABLE TBL1 (i int4);
5680 * CREATE TABLE TBL2 (i int4);
5682 * CREATE RULE r1 AS ON INSERT TO TBL1 DO
5683 * (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
5685 * and do this four times:
5687 * INSERT INTO TBL1 VALUES (10);
5689 #include <stdio.h>
5690 #include <stdlib.h>
5691 #include <string.h>
5692 #include <errno.h>
5693 #include <sys/time.h>
5694 #include "libpq-fe.h"
5697 exit_nicely(PGconn *conn)
5704 main(int argc, char **argv)
5706 const char *conninfo;
5713 * If the user supplies a parameter on the command line, use it as the
5714 * conninfo string; otherwise default to setting dbname=postgres and using
5715 * environment variables or defaults for all other connection parameters.
5720 conninfo = "dbname = postgres";
5722 /* Make a connection to the database */
5723 conn = PQconnectdb(conninfo);
5725 /* Check to see that the backend connection was successfully made */
5726 if (PQstatus(conn) != CONNECTION_OK)
5728 fprintf(stderr, "Connection to database failed: %s",
5729 PQerrorMessage(conn));
5734 * Issue LISTEN command to enable notifications from the rule's NOTIFY.
5736 res = PQexec(conn, "LISTEN TBL2");
5737 if (PQresultStatus(res) != PGRES_COMMAND_OK)
5739 fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
5745 * should PQclear PGresult whenever it is no longer needed to avoid memory
5750 /* Quit after four notifies are received. */
5752 while (nnotifies < 4)
5755 * Sleep until something happens on the connection. We use select(2)
5756 * to wait for input, but you could also use poll() or similar
5762 sock = PQsocket(conn);
5765 break; /* shouldn't happen */
5767 FD_ZERO(&input_mask);
5768 FD_SET(sock, &input_mask);
5770 if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
5772 fprintf(stderr, "select() failed: %s\n", strerror(errno));
5776 /* Now check for input */
5777 PQconsumeInput(conn);
5778 while ((notify = PQnotifies(conn)) != NULL)
5781 "ASYNC NOTIFY of '%s' received from backend pid %d\n",
5782 notify->relname, notify->be_pid);
5788 fprintf(stderr, "Done.\n");
5790 /* close the connection to the database and cleanup */
5798 <example id="libpq-example-3">
5799 <title><application>libpq</application> Example Program 3</>
5804 * Test out-of-line parameters and binary I/O.
5806 * Before running this, populate a database with the following commands
5807 * (provided in src/test/examples/testlibpq3.sql):
5809 * CREATE TABLE test1 (i int4, t text, b bytea);
5811 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
5812 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
5814 * The expected output is:
5818 * t = (11 bytes) 'joe's place'
5819 * b = (5 bytes) \000\001\002\003\004
5823 * t = (8 bytes) 'ho there'
5824 * b = (5 bytes) \004\003\002\001\000
5826 #include <stdio.h>
5827 #include <stdlib.h>
5828 #include <string.h>
5829 #include <sys/types.h>
5830 #include "libpq-fe.h"
5832 /* for ntohl/htonl */
5833 #include <netinet/in.h>
5834 #include <arpa/inet.h>
5838 exit_nicely(PGconn *conn)
5845 * This function prints a query result that is a binary-format fetch from
5846 * a table defined as in the comment above. We split it out because the
5847 * main() function uses it twice.
5850 show_binary_results(PGresult *res)
5858 /* Use PQfnumber to avoid assumptions about field order in result */
5859 i_fnum = PQfnumber(res, "i");
5860 t_fnum = PQfnumber(res, "t");
5861 b_fnum = PQfnumber(res, "b");
5863 for (i = 0; i < PQntuples(res); i++)
5871 /* Get the field values (we ignore possibility they are null!) */
5872 iptr = PQgetvalue(res, i, i_fnum);
5873 tptr = PQgetvalue(res, i, t_fnum);
5874 bptr = PQgetvalue(res, i, b_fnum);
5877 * The binary representation of INT4 is in network byte order, which
5878 * we'd better coerce to the local byte order.
5880 ival = ntohl(*((uint32_t *) iptr));
5883 * The binary representation of TEXT is, well, text, and since libpq
5884 * was nice enough to append a zero byte to it, it'll work just fine
5887 * The binary representation of BYTEA is a bunch of bytes, which could
5888 * include embedded nulls so we have to pay attention to field length.
5890 blen = PQgetlength(res, i, b_fnum);
5892 printf("tuple %d: got\n", i);
5893 printf(" i = (%d bytes) %d\n",
5894 PQgetlength(res, i, i_fnum), ival);
5895 printf(" t = (%d bytes) '%s'\n",
5896 PQgetlength(res, i, t_fnum), tptr);
5897 printf(" b = (%d bytes) ", blen);
5898 for (j = 0; j < blen; j++)
5899 printf("\\%03o", bptr[j]);
5905 main(int argc, char **argv)
5907 const char *conninfo;
5910 const char *paramValues[1];
5911 int paramLengths[1];
5912 int paramFormats[1];
5913 uint32_t binaryIntVal;
5916 * If the user supplies a parameter on the command line, use it as the
5917 * conninfo string; otherwise default to setting dbname=postgres and using
5918 * environment variables or defaults for all other connection parameters.
5923 conninfo = "dbname = postgres";
5925 /* Make a connection to the database */
5926 conn = PQconnectdb(conninfo);
5928 /* Check to see that the backend connection was successfully made */
5929 if (PQstatus(conn) != CONNECTION_OK)
5931 fprintf(stderr, "Connection to database failed: %s",
5932 PQerrorMessage(conn));
5937 * The point of this program is to illustrate use of PQexecParams() with
5938 * out-of-line parameters, as well as binary transmission of data.
5940 * This first example transmits the parameters as text, but receives the
5941 * results in binary format. By using out-of-line parameters we can
5942 * avoid a lot of tedious mucking about with quoting and escaping, even
5943 * though the data is text. Notice how we don't have to do anything
5944 * special with the quote mark in the parameter value.
5947 /* Here is our out-of-line parameter value */
5948 paramValues[0] = "joe's place";
5950 res = PQexecParams(conn,
5951 "SELECT * FROM test1 WHERE t = $1",
5953 NULL, /* let the backend deduce param type */
5955 NULL, /* don't need param lengths since text */
5956 NULL, /* default to all text params */
5957 1); /* ask for binary results */
5959 if (PQresultStatus(res) != PGRES_TUPLES_OK)
5961 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
5966 show_binary_results(res);
5971 * In this second example we transmit an integer parameter in binary
5972 * form, and again retrieve the results in binary form.
5974 * Although we tell PQexecParams we are letting the backend deduce
5975 * parameter type, we really force the decision by casting the parameter
5976 * symbol in the query text. This is a good safety measure when sending
5977 * binary parameters.
5980 /* Convert integer value "2" to network byte order */
5981 binaryIntVal = htonl((uint32_t) 2);
5983 /* Set up parameter arrays for PQexecParams */
5984 paramValues[0] = (char *) &binaryIntVal;
5985 paramLengths[0] = sizeof(binaryIntVal);
5986 paramFormats[0] = 1; /* binary */
5988 res = PQexecParams(conn,
5989 "SELECT * FROM test1 WHERE i = $1::int4",
5991 NULL, /* let the backend deduce param type */
5995 1); /* ask for binary results */
5997 if (PQresultStatus(res) != PGRES_TUPLES_OK)
5999 fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
6004 show_binary_results(res);
6008 /* close the connection to the database and cleanup */
projects / postgresql / blob