libpq.sgml 258 KB
Newer Older
1
<!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.307 2010/06/11 10:13:08 heikki Exp $ -->
2

3 4
<chapter id="libpq">
 <title><application>libpq</application> - C Library</title>
5

6 7 8
 <indexterm zone="libpq">
  <primary>libpq</primary>
 </indexterm>
9

10 11 12
 <indexterm zone="libpq">
  <primary>C</primary>
 </indexterm>
13

14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
 <para>
  <application>libpq</application> is the <acronym>C</acronym>
  application programmer's interface to <productname>PostgreSQL</>.
  <application>libpq</> is a set of library functions that allow
  client programs to pass queries to the <productname>PostgreSQL</>
  backend server and to receive the results of these queries.
 </para>

 <para>
  <application>libpq</> is also the underlying engine for several
  other <productname>PostgreSQL</> application interfaces, including
  those written for C++, Perl, Python, Tcl and <application>ECPG</>.
  So some aspects of <application>libpq</>'s behavior will be
  important to you if you use one of those packages.  In particular,
  <xref linkend="libpq-envars">,
  <xref linkend="libpq-pgpass"> and
  <xref linkend="libpq-ssl">
  describe behavior that is visible to the user of any application
  that uses <application>libpq</>.
 </para>

 <para>
  Some short programs are included at the end of this chapter (<xref linkend="libpq-example">) to show how
  to write programs that use <application>libpq</application>.  There are also several
  complete examples of <application>libpq</application> applications in the
  directory <filename>src/test/examples</filename> in the source code distribution.
 </para>

 <para>
  Client programs that use <application>libpq</application> must
  include the header file
  <filename>libpq-fe.h</filename><indexterm><primary>libpq-fe.h</></>
  and must link with the <application>libpq</application> library.
 </para>
48

49
 <sect1 id="libpq-connect">
50
  <title>Database Connection Control Functions</title>
51

52
  <para>
53 54
   The following functions deal with making a connection to a
   <productname>PostgreSQL</productname> backend server.  An
55 56 57
   application program can have several backend connections open at
   one time.  (One reason to do that is to access more than one
   database.)  Each connection is represented by a
58
   <structname>PGconn</><indexterm><primary>PGconn</></> object, which
59 60
   is obtained from the function <function>PQconnectdb</>,
   <function>PQconnectdbParams</>, or
Peter Eisentraut's avatar
Peter Eisentraut committed
61 62 63 64 65 66
   <function>PQsetdbLogin</>.  Note that these functions will always
   return a non-null object pointer, unless perhaps there is too
   little memory even to allocate the <structname>PGconn</> object.
   The <function>PQstatus</> function should be called to check
   whether a connection was successfully made before queries are sent
   via the connection object.
67

68 69 70 71 72 73 74 75 76 77
   <warning>
    <para>
     On Unix, forking a process with open libpq connections can lead to
     unpredictable results because the parent and child processes share
     the same sockets and operating system resources.  For this reason,
     such usage is not recommended, though doing an <function>exec</> from
     the child process to load a new executable is safe.
    </para>
   </warning>

78 79 80
   <note>
    <para>
     On Windows, there is a way to improve performance if a single
Bruce Momjian's avatar
Bruce Momjian committed
81
     database connection is repeatedly started and shutdown.  Internally,
82 83 84 85 86 87 88 89 90 91
     libpq calls WSAStartup() and WSACleanup() for connection startup
     and shutdown, respectively.  WSAStartup() increments an internal
     Windows library reference count which is decremented by WSACleanup().
     When the reference count is just one, calling WSACleanup() frees
     all resources and all DLLs are unloaded.  This is an expensive
     operation.  To avoid this, an application can manually call
     WSAStartup() so resources will not be freed when the last database
     connection is closed.
    </para>
   </note>
92

93 94
   <variablelist>
    <varlistentry>
95
     <term><function>PQconnectdbParams</function><indexterm><primary>PQconnectdbParams</></></term>
96 97 98
     <listitem>
      <para>
       Makes a new connection to the database server.
99 100

       <synopsis>
101
        PGconn *PQconnectdbParams(const char **keywords, const char **values, int expand_dbname);
102 103 104
       </synopsis>
      </para>

105 106
      <para>
       This function opens a new database connection using the parameters taken
107 108 109 110 111 112 113 114
       from two <symbol>NULL</symbol>-terminated arrays. The first,
       <literal>keywords</literal>, is defined as an array of strings, each one
       being a key word. The second, <literal>values</literal>, gives the value
       for each key word. Unlike <function>PQsetdbLogin</> below, the parameter
       set can be extended without changing the function signature, so use of
       this function (or its nonblocking analogs <function>PQconnectStartParams</>
       and <function>PQconnectPoll</function>) is preferred for new application
       programming.
115 116
      </para>

117 118 119 120 121 122
      <para>
       When <literal>expand_dbname</literal> is non-zero, the
       <parameter>dbname</parameter> key word value is allowed to be recognized
       as a <parameter>conninfo</parameter> string. See below for details.
      </para>

123
      <para>
124 125 126 127
       The passed arrays can be empty to use all default parameters, or can
       contain one or more parameter settings. They should be matched in length.
       Processing will stop with the last non-<symbol>NULL</symbol> element
       of the <literal>keywords</literal> array.
128 129 130 131 132 133
      </para>

      <para>
       The currently recognized parameter key words are:

       <variablelist>
134
        <varlistentry id="libpq-connect-host" xreflabel="host">
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151
         <term><literal>host</literal></term>
         <listitem>
          <para>
           Name of host to connect to.<indexterm><primary>host name</></>
           If this begins with a slash, it specifies Unix-domain
           communication rather than TCP/IP communication; the value is the
           name of the directory in which the socket file is stored.  The
           default behavior when <literal>host</literal> is not specified
           is to connect to a Unix-domain
           socket<indexterm><primary>Unix domain socket</></> in
           <filename>/tmp</filename> (or whatever socket directory was specified
           when <productname>PostgreSQL</> was built). On machines without
           Unix-domain sockets, the default is to connect to <literal>localhost</>.
          </para>
         </listitem>
        </varlistentry>

152
        <varlistentry id="libpq-connect-hostaddr" xreflabel="hostaddr">
153 154 155 156 157 158 159 160 161
         <term><literal>hostaddr</literal></term>
         <listitem>
          <para>
           Numeric IP address of host to connect to.  This should be in the
           standard IPv4 address format, e.g., <literal>172.28.40.9</>.  If
           your machine supports IPv6, you can also use those addresses.
           TCP/IP communication is
           always used when a nonempty string is specified for this parameter.
          </para>
Bruce Momjian's avatar
Bruce Momjian committed
162

163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182
          <para>
           Using <literal>hostaddr</> instead of <literal>host</> allows the
           application to avoid a host name look-up, which might be important in
           applications with time constraints. However, Kerberos and GSSAPI authentication
           requires the host name. The following therefore applies: If
           <literal>host</> is specified without <literal>hostaddr</>, a host name
           lookup occurs. If <literal>hostaddr</> is specified without
           <literal>host</>, the value for <literal>hostaddr</> gives the remote
           address. When Kerberos is used, a reverse name query occurs to obtain
           the host name for Kerberos. If both
           <literal>host</> and <literal>hostaddr</> are specified, the value for
           <literal>hostaddr</> gives the remote address; the value for
           <literal>host</> is ignored, unless Kerberos is used, in which case that
           value is used for Kerberos authentication. (Note that authentication is
           likely to fail if <application>libpq</application> is passed a host name
           that is not the name of the machine at <literal>hostaddr</>.)  Also,
           <literal>host</> rather than <literal>hostaddr</> is used to identify
           the connection in <filename>~/.pgpass</> (see
           <xref linkend="libpq-pgpass">).
          </para>
Bruce Momjian's avatar
Bruce Momjian committed
183

184 185 186 187 188 189 190 191
          <para>
           Without either a host name or host address,
           <application>libpq</application> will connect using a
           local Unix-domain socket; or on machines without Unix-domain
           sockets, it will attempt to connect to <literal>localhost</>.
          </para>
          </listitem>
         </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
192

193
         <varlistentry id="libpq-connect-port" xreflabel="port">
194 195 196 197 198 199 200 201 202 203
          <term><literal>port</literal></term>
          <listitem>
          <para>
           Port number to connect to at the server host, or socket file
           name extension for Unix-domain
           connections.<indexterm><primary>port</></>
          </para>
         </listitem>
        </varlistentry>

204
        <varlistentry id="libpq-connect-dbname" xreflabel="dbname">
205 206
         <term><literal>dbname</literal></term>
         <listitem>
207
         <para>
208
          The database name.  Defaults to be the same as the user name.
209
         </para>
210 211
         </listitem>
        </varlistentry>
212

213
        <varlistentry id="libpq-connect-user" xreflabel="user">
214 215
         <term><literal>user</literal></term>
         <listitem>
216
         <para>
217 218 219
          <productname>PostgreSQL</productname> user name to connect as.
          Defaults to be the same as the operating system name of the user
          running the application.
220
         </para>
221 222
         </listitem>
        </varlistentry>
223

224
        <varlistentry id="libpq-connect-password" xreflabel="password">
225 226
         <term><literal>password</literal></term>
         <listitem>
227
         <para>
228
          Password to be used if the server demands password authentication.
229
         </para>
230 231
         </listitem>
        </varlistentry>
232

233
        <varlistentry id="libpq-connect-connect-timeout" xreflabel="connect_timeout">
234 235
         <term><literal>connect_timeout</literal></term>
         <listitem>
236
         <para>
237 238 239
          Maximum wait for connection, in seconds (write as a decimal integer
          string). Zero or not specified means wait indefinitely.  It is not
          recommended to use a timeout of less than 2 seconds.
240
         </para>
241 242 243
         </listitem>
        </varlistentry>

244
        <varlistentry id="libpq-connect-options" xreflabel="options">
245 246 247
         <term><literal>options</literal></term>
         <listitem>
          <para>
248 249 250 251 252
           Adds command-line options to send to the server at run-time.
           For example, setting this to <literal>-c geqo=off</> sets the
           session's value of the <varname>geqo</> parameter to
           <literal>off</>.  For a detailed discussion of the available
           options, consult <xref linkend="runtime-config">.
253 254 255 256
          </para>
         </listitem>
        </varlistentry>

257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282
        <varlistentry id="libpq-connect-application-name" xreflabel="application_name">
         <term><literal>application_name</literal></term>
         <listitem>
          <para>
           Specifies a value for the <xref linkend="guc-application-name">
           configuration parameter.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry id="libpq-connect-fallback-application-name" xreflabel="fallback_application_name">
         <term><literal>fallback_application_name</literal></term>
         <listitem>
          <para>
           Specifies a fallback value for the <xref
           linkend="guc-application-name"> configuration parameter.
           This value will be used if no value has been given for
           <literal>application_name</> via a connection parameter or the
           <envar>PGAPPNAME</envar> environment variable.  Specifying
           a fallback name is useful in generic utility programs that
           wish to set a default application name but allow it to be
           overridden by the user.
          </para>
         </listitem>
        </varlistentry>

283
        <varlistentry id="libpq-connect-tty" xreflabel="tty">
284 285
         <term><literal>tty</literal></term>
         <listitem>
286
         <para>
287
          Ignored (formerly, this specified where to send server debug output).
288
         </para>
289 290 291
         </listitem>
        </varlistentry>

292
        <varlistentry id="libpq-connect-sslmode" xreflabel="sslmode">
293 294 295
         <term><literal>sslmode</literal></term>
         <listitem>
          <para>
296
           This option determines whether or with what priority a secure
297
           <acronym>SSL</> TCP/IP connection will be negotiated with the
298
           server. There are six modes:
299
          </para>
300

301 302 303 304 305 306 307 308 309
          <table id="libpq-connect-sslmode-options">
           <title><literal>sslmode</literal> options</title>
           <tgroup cols="2">
            <thead>
             <row>
              <entry>Option</entry>
              <entry>Description</entry>
             </row>
            </thead>
310

311
            <tbody>
312

313 314 315 316
             <row>
              <entry><literal>disable</></entry>
              <entry>only try a non-<acronym>SSL</> connection</entry>
             </row>
317

318 319 320 321
             <row>
              <entry><literal>allow</></entry>
              <entry>first try a non-<acronym>SSL</>
               connection;  if that fails, try an <acronym>SSL</>
322
               connection</entry>
323
             </row>
324

325 326 327 328 329 330
             <row>
              <entry><literal>prefer</> (default)</entry>
              <entry>first try an <acronym>SSL</> connection;  if
              that fails, try a non-<acronym>SSL</>
              connection</entry>
             </row>
331

332 333 334 335
             <row>
              <entry><literal>require</></entry>
              <entry>only try an <acronym>SSL</> connection</entry>
             </row>
336 337 338 339

             <row>
              <entry><literal>verify-ca</></entry>
              <entry>only try an <acronym>SSL</> connection, and verify that
340
              the server certificate is issued by a trusted <acronym>CA</>
341 342 343 344 345 346 347
              </entry>
             </row>

             <row>
              <entry><literal>verify-full</></entry>
              <entry>only try an <acronym>SSL</> connection, verify that
              the server certificate is issued by a trusted <acronym>CA</> and
348
              that the server hostname matches that in the certificate</entry>
349 350
             </row>

351 352 353
            </tbody>
           </tgroup>
          </table>
354

355 356 357 358 359
          <para>
           See <xref linkend="libpq-ssl"> for a detailed description of how
           these options work.
          </para>

360
          <para>
361 362
           <literal>sslmode</> is ignored for Unix domain socket
           communication.
363
           If <productname>PostgreSQL</> is compiled without SSL support,
364 365
           using options <literal>require</>, <literal>verify-ca</>, or
           <literal>verify-full</> will cause an error, while
366
           options <literal>allow</> and <literal>prefer</> will be
367
           accepted but <application>libpq</> will not actually attempt
368 369 370 371 372 373 374
           an <acronym>SSL</>
           connection.<indexterm><primary>SSL</><secondary
           sortas="libpq">with libpq</></indexterm>
          </para>
         </listitem>
        </varlistentry>

375
        <varlistentry id="libpq-connect-requiressl" xreflabel="requiressl">
376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396
         <term><literal>requiressl</literal></term>
         <listitem>
          <para>
           This option is deprecated in favor of the <literal>sslmode</>
           setting.
          </para>

          <para>
           If set to 1, an <acronym>SSL</acronym> connection to the server
           is required (this is equivalent to <literal>sslmode</>
           <literal>require</>).  <application>libpq</> will then refuse
           to connect if the server does not accept an
           <acronym>SSL</acronym> connection.  If set to 0 (default),
           <application>libpq</> will negotiate the connection type with
           the server (equivalent to <literal>sslmode</>
           <literal>prefer</>).  This option is only available if
           <productname>PostgreSQL</> is compiled with SSL support.
          </para>
         </listitem>
        </varlistentry>

397
        <varlistentry id="libpq-connect-sslcert" xreflabel="sslcert">
398 399 400 401
         <term><literal>sslcert</literal></term>
         <listitem>
          <para>
           This parameter specifies the file name of the client SSL
402 403 404
           certificate, replacing the default
           <filename>~/.postgresql/postgresql.crt</>.
           This parameter is ignored if an SSL connection is not made. 
405 406 407 408
          </para>
         </listitem>
        </varlistentry>

409
        <varlistentry id="libpq-connect-sslkey" xreflabel="sslkey">
410 411 412
         <term><literal>sslkey</literal></term>
         <listitem>
          <para>
413 414 415 416 417 418 419 420 421
           This parameter specifies the location for the secret key used for
           the client certificate. It can either specify a filename that will
           be used instead of the default
           <filename>~/.postgresql/postgresql.key</>, or it can specify a key
           obtained from an external <quote>engine</> (engines are
           <productname>OpenSSL</> loadable modules).  An external engine
           specification should consist of a colon-separated engine name and
           an engine-specific key identifier.  This parameter is ignored if an
           SSL connection is not made.
422 423 424 425
          </para>
         </listitem>
        </varlistentry>

426
        <varlistentry id="libpq-connect-sslrootcert" xreflabel="sslrootcert">
427 428 429
         <term><literal>sslrootcert</literal></term>
         <listitem>
          <para>
430 431 432 433 434
           This parameter specifies the name of a file containing SSL
           certificate authority (<acronym>CA</>) certificate(s).
           If the file exists, the server's certificate will be verified
           to be signed by one of these authorities.  The default is
           <filename>~/.postgresql/root.crt</>.
435 436 437 438
          </para>
         </listitem>
        </varlistentry>

439
        <varlistentry id="libpq-connect-sslcrl" xreflabel="sslcrl">
440 441 442 443
         <term><literal>sslcrl</literal></term>
         <listitem>
          <para>
           This parameter specifies the file name of the SSL certificate
444 445 446 447
           revocation list (CRL).  Certificates listed in this file, if it
           exists, will be rejected while attempting to authenticate the
           server's certificate.  The default is
           <filename>~/.postgresql/root.crl</>.
448 449 450 451
          </para>
         </listitem>
        </varlistentry>

452
        <varlistentry id="libpq-connect-krbsrvname" xreflabel="krbsrvname">
453 454 455 456 457 458 459 460 461 462 463 464
         <term><literal>krbsrvname</literal></term>
         <listitem>
          <para>
           Kerberos service name to use when authenticating with Kerberos 5
           or GSSAPI.
           This must match the service name specified in the server
           configuration for Kerberos authentication to succeed. (See also
           <xref linkend="kerberos-auth"> and <xref linkend="gssapi-auth">.)
          </para>
         </listitem>
        </varlistentry>

465
        <varlistentry id="libpq-connect-gsslib" xreflabel="gsslib">
466 467 468 469 470 471 472 473 474 475
         <term><literal>gsslib</literal></term>
         <listitem>
          <para>
           GSS library to use for GSSAPI authentication. Only used on Windows.
           Set to <literal>gssapi</literal> to force libpq to use the GSSAPI
           library for authentication instead of the default SSPI.
          </para>
         </listitem>
        </varlistentry>

476
        <varlistentry id="libpq-connect-service" xreflabel="service">
477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492
         <term><literal>service</literal></term>
         <listitem>
          <para>
           Service name to use for additional parameters.  It specifies a service
           name in <filename>pg_service.conf</filename> that holds additional connection parameters.
           This allows applications to specify only a service name so connection parameters
           can be centrally maintained. See <xref linkend="libpq-pgservice">.
          </para>
         </listitem>
        </varlistentry>
       </variablelist>

       If  any  parameter is unspecified, then the corresponding
       environment variable (see <xref linkend="libpq-envars">)
       is checked. If the  environment  variable is not set either,
       then the indicated built-in defaults are used.
493
      </para>
494 495

      <para>
496
        If <literal>expand_dbname</literal> is non-zero and
497 498 499 500 501 502 503 504 505 506 507 508 509 510 511
        <parameter>dbname</parameter> contains an <symbol>=</symbol> sign, it
        is taken as a <parameter>conninfo</parameter> string in exactly the same way as
        if it had been passed to <function>PQconnectdb</function>(see below). Previously
        processed key words will be overridden by key words in the
        <parameter>conninfo</parameter> string.
      </para>

      <para>
        In general key words are processed from the beginning of these arrays in index
        order. The effect of this is that when key words are repeated, the last processed
        value is retained. Therefore, through careful placement of the
        <parameter>dbname</parameter> key word, it is possible to determine what may
        be overridden by a <parameter>conninfo</parameter> string, and what may not.
      </para>

512 513 514
     </listitem>
    </varlistentry>

515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547
    <varlistentry>
     <term><function>PQconnectdb</function><indexterm><primary>PQconnectdb</></></term>
     <listitem>
      <para>
       Makes a new connection to the database server.

       <synopsis>
        PGconn *PQconnectdb(const char *conninfo);
       </synopsis>
      </para>

      <para>
       This function opens a new database connection using the parameters taken
       from the string <literal>conninfo</literal>.
      </para>

      <para>
       The passed string can be empty to use all default parameters, or it can
       contain one or more parameter settings separated by whitespace.
       Each parameter setting is in the form <literal>keyword = value</literal>.
       Spaces around the equal sign are optional. To write an empty value,
       or a value containing spaces, surround it with single quotes, e.g.,
       <literal>keyword = 'a value'</literal>. Single quotes and backslashes
       within the value must be escaped with a backslash, i.e.,
       <literal>\'</literal> and <literal>\\</literal>.
      </para>

      <para>
       The currently recognized parameter key words are the same as above.
      </para>
     </listitem>
    </varlistentry>

548 549 550 551 552
    <varlistentry>
     <term><function>PQsetdbLogin</function><indexterm><primary>PQsetdbLogin</></></term>
     <listitem>
      <para>
       Makes a new connection to the database server.
553
<synopsis>
554 555 556 557 558 559 560
PGconn *PQsetdbLogin(const char *pghost,
                     const char *pgport,
                     const char *pgoptions,
                     const char *pgtty,
                     const char *dbName,
                     const char *login,
                     const char *pwd);
561
</synopsis>
562
       </para>
563

564 565 566 567 568 569
       <para>
        This is the predecessor of <function>PQconnectdb</function> with a fixed
        set of parameters.  It has the same functionality except that the
        missing parameters will always take on default values.  Write <symbol>NULL</symbol> or an
        empty string for any one of the fixed parameters that is to be defaulted.
      </para>
570

571 572 573 574 575 576 577 578
      <para>
        If the <parameter>dbName</parameter> contains an <symbol>=</symbol> sign, it
        is taken as a <parameter>conninfo</parameter> string in exactly the same way as
        if it had been passed to <function>PQconnectdb</function>, and the remaining
        parameters are then applied as above.
      </para>
     </listitem>
    </varlistentry>
579

580
    <varlistentry>
581
     <term><function>PQsetdb</function><indexterm><primary>PQsetdb</></></term>
582
     <listitem>
583 584 585 586 587 588 589 590 591 592 593
      <para>
   Makes a new connection to the database server.
<synopsis>
PGconn *PQsetdb(char *pghost,
                char *pgport,
                char *pgoptions,
                char *pgtty,
                char *dbName);
</synopsis>
     </para>

594
     <para>
595 596 597
      This is a macro that calls <function>PQsetdbLogin</function> with null pointers
      for the <parameter>login</> and <parameter>pwd</> parameters.  It is provided
      for backward compatibility with very old programs.
598 599 600
     </para>
     </listitem>
    </varlistentry>
601

602
    <varlistentry>
603
     <term><function>PQconnectStartParams</function><indexterm><primary>PQconnectStartParams</></></term>
604 605
     <term><function>PQconnectStart</function><indexterm><primary>PQconnectStart</></></term>
     <term><function>PQconnectPoll</function><indexterm><primary>PQconnectPoll</></></term>
606
     <listitem>
607 608 609
      <para>
       <indexterm><primary>nonblocking connection</primary></indexterm>
       Make a connection to the database server in a nonblocking manner.
Bruce Momjian's avatar
Bruce Momjian committed
610

611
       <synopsis>
612
        PGconn *PQconnectStartParams(const char **keywords, const char **values, int expand_dbname);
613 614
       </synopsis>

615 616 617
       <synopsis>
        PGconn *PQconnectStart(const char *conninfo);
       </synopsis>
Bruce Momjian's avatar
Bruce Momjian committed
618

619 620 621 622
       <synopsis>
        PostgresPollingStatusType PQconnectPoll(PGconn *conn);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
623

624
      <para>
625
       These three functions are used to open a connection to a database server such
626
       that your application's thread of execution is not blocked on remote I/O
627 628 629 630
       whilst doing so. The point of this approach is that the waits for I/O to
       complete can occur in the application's main loop, rather than down inside
       <function>PQconnectdbParams</> or <function>PQconnectdb</>, and so the
       application can manage this operation in parallel with other activities.
631
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
632

633
      <para>
634 635
       With <function>PQconnectStartParams</function>, the database connection is made
       using the parameters taken from the <literal>keywords</literal> and
636 637
       <literal>values</literal> arrays, and controlled by <literal>expand_dbname</literal>,
       as described above for <function>PQconnectdbParams</function>.
638
      </para>
639

640
      <para>
641 642 643 644 645 646 647 648
       With <function>PQconnectStart</function>, the database connection is made
       using the parameters taken from the string <literal>conninfo</literal> as
       described above for <function>PQconnectdb</function>.
      </para>

      <para>
       Neither <function>PQconnectStartParams</function> nor <function>PQconnectStart</function>
       nor <function>PQconnectPoll</function> will block, so long as a number of
649 650
       restrictions are met:
       <itemizedlist>
651 652
        <listitem>
         <para>
653 654
          The <literal>hostaddr</> and <literal>host</> parameters are used appropriately to ensure that
          name and reverse name queries are not made. See the documentation of
655
          these parameters under <function>PQconnectdbParams</function> above for details.
656 657
         </para>
        </listitem>
Bruce Momjian's avatar
Bruce Momjian committed
658

659 660
        <listitem>
         <para>
661 662
          If you call <function>PQtrace</function>, ensure that the stream object
          into which you trace will not block.
663 664
         </para>
        </listitem>
Bruce Momjian's avatar
Bruce Momjian committed
665

666 667
        <listitem>
         <para>
668 669
          You ensure that the socket is in the appropriate state
          before calling <function>PQconnectPoll</function>, as described below.
670 671
         </para>
        </listitem>
672 673
       </itemizedlist>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
674

675 676 677 678 679
      <para>
       Note: use of <function>PQconnectStartParams</> is analogous to
       <function>PQconnectStart</> shown below.
      </para>

680 681 682 683 684 685 686 687
      <para>
       To begin a nonblocking connection request, call <literal>conn = PQconnectStart("<replaceable>connection_info_string</>")</literal>.
       If <varname>conn</varname> is null, then <application>libpq</> has been unable to allocate a new <structname>PGconn</>
       structure. Otherwise, a valid <structname>PGconn</> pointer is returned (though not yet
       representing a valid connection to the database). On return from
       <function>PQconnectStart</function>, call <literal>status = PQstatus(conn)</literal>. If <varname>status</varname> equals
       <symbol>CONNECTION_BAD</symbol>, <function>PQconnectStart</function> has failed.
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
688

689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710
      <para>
       If <function>PQconnectStart</> succeeds, the next stage is to poll
       <application>libpq</> so that it can proceed with the connection sequence.
       Use <function>PQsocket(conn)</function> to obtain the descriptor of the
       socket underlying the database connection.
       Loop thus: If <function>PQconnectPoll(conn)</function> last returned
       <symbol>PGRES_POLLING_READING</symbol>, wait until the socket is ready to
       read (as indicated by <function>select()</>, <function>poll()</>, or
       similar system function).
       Then call <function>PQconnectPoll(conn)</function> again.
       Conversely, if <function>PQconnectPoll(conn)</function> last returned
       <symbol>PGRES_POLLING_WRITING</symbol>, wait until the socket is ready
       to write, then call <function>PQconnectPoll(conn)</function> again.
       If you have yet to call
       <function>PQconnectPoll</function>, i.e., just after the call to
       <function>PQconnectStart</function>, behave as if it last returned
       <symbol>PGRES_POLLING_WRITING</symbol>.  Continue this loop until
       <function>PQconnectPoll(conn)</function> returns
       <symbol>PGRES_POLLING_FAILED</symbol>, indicating the connection procedure
       has failed, or <symbol>PGRES_POLLING_OK</symbol>, indicating the connection
       has been successfully made.
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
711

712 713 714 715 716 717 718 719 720
      <para>
       At any time during connection, the status of the connection can be
       checked by calling <function>PQstatus</>. If this gives <symbol>CONNECTION_BAD</>, then the
       connection procedure has failed; if it gives <function>CONNECTION_OK</>, then the
       connection is ready.  Both of these states are equally detectable
       from the return value of <function>PQconnectPoll</>, described above. Other states might also occur
       during (and only during) an asynchronous connection procedure. These
       indicate the current stage of the connection procedure and might be useful
       to provide feedback to the user for example. These statuses are:
Bruce Momjian's avatar
Bruce Momjian committed
721

722 723 724 725 726 727 728 729 730
       <variablelist>
        <varlistentry>
         <term><symbol>CONNECTION_STARTED</symbol></term>
         <listitem>
          <para>
           Waiting for connection to be made.
          </para>
         </listitem>
        </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
731

732 733 734 735 736 737 738 739
        <varlistentry>
         <term><symbol>CONNECTION_MADE</symbol></term>
         <listitem>
          <para>
           Connection OK; waiting to send.
          </para>
         </listitem>
        </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
740

741 742 743 744 745 746 747 748
        <varlistentry>
         <term><symbol>CONNECTION_AWAITING_RESPONSE</symbol></term>
         <listitem>
          <para>
           Waiting for a response from the server.
          </para>
         </listitem>
        </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
749

750 751 752 753 754 755 756 757
        <varlistentry>
         <term><symbol>CONNECTION_AUTH_OK</symbol></term>
         <listitem>
          <para>
           Received authentication; waiting for backend start-up to finish.
          </para>
         </listitem>
        </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
758

759 760 761 762 763 764 765 766
        <varlistentry>
         <term><symbol>CONNECTION_SSL_STARTUP</symbol></term>
         <listitem>
          <para>
           Negotiating SSL encryption.
          </para>
         </listitem>
        </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
767

768 769 770 771 772 773 774 775 776
        <varlistentry>
         <term><symbol>CONNECTION_SETENV</symbol></term>
         <listitem>
          <para>
           Negotiating environment-driven parameter settings.
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
Bruce Momjian's avatar
Bruce Momjian committed
777

778 779 780 781
       Note that, although these constants will remain (in order to maintain
       compatibility), an application should never rely upon these occurring in a
       particular order, or at all, or on the status always being one of these
       documented values. An application might do something like this:
782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798
<programlisting>
switch(PQstatus(conn))
{
        case CONNECTION_STARTED:
            feedback = "Connecting...";
            break;

        case CONNECTION_MADE:
            feedback = "Connected to server...";
            break;
.
.
.
        default:
            feedback = "Connecting...";
}
</programlisting>
799
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
800

801 802 803 804 805 806 807 808
      <para>
       The <literal>connect_timeout</literal> connection parameter is ignored
       when using <function>PQconnectPoll</function>; it is the application's
       responsibility to decide whether an excessive amount of time has elapsed.
       Otherwise, <function>PQconnectStart</function> followed by a
       <function>PQconnectPoll</function> loop is equivalent to
       <function>PQconnectdb</function>.
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
809

810 811 812 813 814 815 816 817
      <para>
       Note that if <function>PQconnectStart</function> returns a non-null pointer, you must call
       <function>PQfinish</function> when you are finished with it, in order to dispose of
       the structure and any associated memory blocks. This must be done even if
       the connection attempt fails or is abandoned.
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
818

819 820 821 822 823
    <varlistentry>
     <term><function>PQconndefaults</function><indexterm><primary>PQconndefaults</></></term>
     <listitem>
      <para>
       Returns the default connection options.
824 825 826 827 828 829 830 831 832 833
<synopsis>
PQconninfoOption *PQconndefaults(void);

typedef struct
{
    char   *keyword;   /* The keyword of the option */
    char   *envvar;    /* Fallback environment variable name */
    char   *compiled;  /* Fallback compiled in default value */
    char   *val;       /* Option's current value, or NULL */
    char   *label;     /* Label for field in connect dialog */
834
    char   *dispchar;  /* Indicates how to display this field
835 836 837 838 839 840 841
                          in a connect dialog. Values are:
                          ""        Display entered value as is
                          "*"       Password field - hide value
                          "D"       Debug option - don't show by default */
    int     dispsize;  /* Field size in characters for dialog */
} PQconninfoOption;
</synopsis>
842
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
843

844 845 846 847 848 849 850 851 852 853 854
      <para>
       Returns a connection options array.  This can be used to determine
       all possible <function>PQconnectdb</function> options and their
       current default values.  The return value points to an array of
       <structname>PQconninfoOption</structname> structures, which ends
       with an entry having a null <structfield>keyword</> pointer.  The
       null pointer is returned if memory could not be allocated. Note that
       the current default values (<structfield>val</structfield> fields)
       will depend on environment variables and other context.  Callers
       must treat the connection options data as read-only.
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
855

856 857 858 859 860
      <para>
       After processing the options array, free it by passing it to
       <function>PQconninfoFree</function>.  If this is not done, a small amount of memory
       is leaked for each call to <function>PQconndefaults</function>.
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
861

862 863
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
864

865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909
    <varlistentry>
     <term><function>PQconninfoParse</function><indexterm><primary>PQconninfoParse</></></term>
     <listitem>
      <para>
       Returns parsed connection options from the provided connection string.

<synopsis>
PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg);
</synopsis>
      </para>

      <para>
       Parses a connection string and returns the resulting options as an
       array; or returns NULL if there is a problem with the connection
       string.  This can be used to determine
       the <function>PQconnectdb</function> options in the provided
       connection string.  The return value points to an array of
       <structname>PQconninfoOption</structname> structures, which ends
       with an entry having a null <structfield>keyword</> pointer.
      </para>

      <para>
       Note that only options explicitly specified in the string will have
       values set in the result array; no defaults are inserted.
      </para>

      <para>
       If <literal>errmsg</> is not NULL, then <literal>*errmsg</> is set
       to NULL on success, else to a malloc'd error string explaining
       the problem.  (It is also possible for <literal>*errmsg</> to be
       set to NULL even when NULL is returned; this indicates an out-of-memory
       situation.)
      </para>

      <para>
       After processing the options array, free it by passing it to
       <function>PQconninfoFree</function>.  If this is not done, some memory
       is leaked for each call to <function>PQconninfoParse</function>.
       Conversely, if an error occurs and <literal>errmsg</> is not NULL,
       be sure to free the error string using <function>PQfreemem</>.
      </para>

   </listitem>
    </varlistentry>

910 911 912 913 914 915 916 917 918 919
    <varlistentry>
     <term><function>PQfinish</function><indexterm><primary>PQfinish</></></term>
     <listitem>
      <para>
       Closes  the  connection to the server.  Also frees
       memory used by the <structname>PGconn</structname> object.
       <synopsis>
        void PQfinish(PGconn *conn);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
920

921 922 923 924 925 926 927 928 929
      <para>
       Note that even if the server connection attempt fails (as
       indicated by <function>PQstatus</function>), the application should call <function>PQfinish</function>
       to free the memory used by the <structname>PGconn</structname> object.
       The <structname>PGconn</> pointer must not be used again after
       <function>PQfinish</function> has been called.
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
930

931 932 933 934 935 936 937 938 939
    <varlistentry>
     <term><function>PQreset</function><indexterm><primary>PQreset</></></term>
     <listitem>
      <para>
       Resets the communication channel to the server.
       <synopsis>
        void PQreset(PGconn *conn);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
940

941 942 943 944 945 946
      <para>
       This function will close the connection
       to the server and attempt to  reestablish  a  new
       connection to the same server, using all the same
       parameters previously used.  This might be useful for
       error recovery if a working connection is lost.
Peter Eisentraut's avatar
Peter Eisentraut committed
947
      </para>
948 949 950
     </listitem>
    </varlistentry>

951
    <varlistentry>
952 953
     <term><function>PQresetStart</function><indexterm><primary>PQresetStart</></></term>
     <term><function>PQresetPoll</function><indexterm><primary>PQresetPoll</></></term>
954
     <listitem>
Peter Eisentraut's avatar
Peter Eisentraut committed
955
      <para>
956 957 958 959 960 961 962 963
       Reset the communication channel to the server, in a nonblocking manner.

       <synopsis>
        int PQresetStart(PGconn *conn);
       </synopsis>
       <synopsis>
        PostgresPollingStatusType PQresetPoll(PGconn *conn);
       </synopsis>
Peter Eisentraut's avatar
Peter Eisentraut committed
964 965 966
      </para>

      <para>
967 968 969 970 971
       These functions will close the connection to the server and attempt to
       reestablish a new connection to the same server, using all the same
       parameters previously used. This can be useful for error recovery if a
       working connection is lost. They differ from <function>PQreset</function> (above) in that they
       act in a nonblocking manner. These functions suffer from the same
972 973
       restrictions as <function>PQconnectStartParams</>, <function>PQconnectStart</>
       and <function>PQconnectPoll</>.
974 975
      </para>

976
      <para>
977 978 979 980 981
       To initiate a connection reset, call
       <function>PQresetStart</function>. If it returns 0, the reset has
       failed. If it returns 1, poll the reset using
       <function>PQresetPoll</function> in exactly the same way as you
       would create the connection using <function>PQconnectPoll</function>.
982 983 984 985
      </para>
     </listitem>
    </varlistentry>

986
   </variablelist>
987 988
  </para>
 </sect1>
989

990 991
 <sect1 id="libpq-status">
  <title>Connection Status Functions</title>
992

993 994 995 996
  <para>
   These functions can be used to interrogate the status
   of an existing database connection object.
  </para>
Tom Lane's avatar
Tom Lane committed
997

998
  <tip>
999
   <para>
1000 1001 1002 1003 1004
    <indexterm><primary>libpq-fe.h</></>
    <indexterm><primary>libpq-int.h</></>
    <application>libpq</application> application programmers should be careful to
    maintain the <structname>PGconn</structname> abstraction.  Use the accessor
    functions described below to get at the contents of <structname>PGconn</structname>.
1005
    Reference to internal <structname>PGconn</structname> fields using
1006 1007
    <filename>libpq-int.h</> is not recommended because they are subject to change
    in the future.
1008
   </para>
1009
  </tip>
1010

1011 1012 1013
  <para>
   The following functions return parameter values established at connection.
   These values are fixed for the life of the <structname>PGconn</> object.
1014

1015 1016 1017 1018 1019 1020 1021 1022
   <variablelist>
    <varlistentry>
     <term>
      <function>PQdb</function>
      <indexterm>
       <primary>PQdb</primary>
      </indexterm>
     </term>
1023

1024 1025 1026 1027 1028 1029 1030 1031 1032
     <listitem>
      <para>
       Returns the database name of the connection.
       <synopsis>
        char *PQdb(const PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
1033

1034 1035 1036 1037 1038 1039 1040
    <varlistentry>
     <term>
      <function>PQuser</function>
      <indexterm>
       <primary>PQuser</primary>
      </indexterm>
     </term>
1041

1042 1043 1044 1045 1046 1047 1048 1049 1050
     <listitem>
      <para>
       Returns the user name of the connection.
       <synopsis>
        char *PQuser(const PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
1051

1052 1053 1054 1055 1056 1057 1058
    <varlistentry>
     <term>
      <function>PQpass</function>
      <indexterm>
       <primary>PQpass</primary>
      </indexterm>
     </term>
1059

1060 1061 1062 1063 1064 1065 1066 1067 1068
     <listitem>
      <para>
       Returns the password of the connection.
       <synopsis>
        char *PQpass(const PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
1069

1070 1071 1072 1073 1074 1075 1076
    <varlistentry>
     <term>
      <function>PQhost</function>
      <indexterm>
       <primary>PQhost</primary>
      </indexterm>
     </term>
1077

1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088
     <listitem>
      <para>
       Returns the server host name of the connection.
       <synopsis>
        char *PQhost(const PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
1089 1090 1091 1092 1093 1094
     <term>
      <function>PQport</function>
      <indexterm>
       <primary>PQport</primary>
      </indexterm>
     </term>
1095

1096 1097 1098
     <listitem>
      <para>
       Returns the port of the connection.
1099

1100 1101 1102 1103 1104 1105
       <synopsis>
        char *PQport(const PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
1106

1107 1108 1109 1110 1111 1112 1113
    <varlistentry>
     <term>
      <function>PQtty</function>
      <indexterm>
       <primary>PQtty</primary>
      </indexterm>
     </term>
1114

1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146
     <listitem>
      <para>
       Returns the debug <acronym>TTY</acronym> of the connection.
       (This is obsolete, since the server no longer pays attention
       to the <acronym>TTY</acronym> setting, but the function remains
       for backwards compatibility.)

       <synopsis>
        char *PQtty(const PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQoptions</function>
      <indexterm>
       <primary>PQoptions</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns the command-line options passed in the connection request.
       <synopsis>
        char *PQoptions(const PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
1147
  </para>
1148

1149
  <para>
1150 1151
   The following functions return status data that can change as operations
   are executed on the <structname>PGconn</> object.
1152

1153 1154 1155 1156 1157 1158 1159 1160
   <variablelist>
    <varlistentry>
     <term>
      <function>PQstatus</function>
      <indexterm>
       <primary>PQstatus</primary>
      </indexterm>
     </term>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1161

1162 1163
     <listitem>
      <para>
1164
       Returns the status of the connection.
1165 1166 1167 1168
       <synopsis>
        ConnStatusType PQstatus(const PGconn *conn);
       </synopsis>
      </para>
1169

1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183
      <para>
       The status can be one of a number of values.  However, only two of
       these are seen outside of an asynchronous connection procedure:
       <literal>CONNECTION_OK</literal> and
       <literal>CONNECTION_BAD</literal>. A good connection to the database
       has the status <literal>CONNECTION_OK</literal>.  A failed
       connection attempt is signaled by status
       <literal>CONNECTION_BAD</literal>.  Ordinarily, an OK status will
       remain so until <function>PQfinish</function>, but a communications
       failure might result in the status changing to
       <literal>CONNECTION_BAD</literal> prematurely.  In that case the
       application could try to recover by calling
       <function>PQreset</function>.
      </para>
1184

1185
      <para>
1186 1187 1188
       See the entry for <function>PQconnectStartParams</>, <function>PQconnectStart</>
       and <function>PQconnectPoll</> with regards to other status codes that
       might be seen.
1189 1190 1191
      </para>
     </listitem>
    </varlistentry>
1192

1193 1194 1195 1196 1197 1198 1199
    <varlistentry>
     <term>
      <function>PQtransactionStatus</function>
      <indexterm>
       <primary>PQtransactionStatus</primary>
      </indexterm>
     </term>
1200

1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216
     <listitem>
      <para>
       Returns the current in-transaction status of the server.

       <synopsis>
        PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
       </synopsis>

       The status can be <literal>PQTRANS_IDLE</literal> (currently idle),
       <literal>PQTRANS_ACTIVE</literal> (a command is in progress),
       <literal>PQTRANS_INTRANS</literal> (idle, in a valid transaction block),
       or <literal>PQTRANS_INERROR</literal> (idle, in a failed transaction block).
       <literal>PQTRANS_UNKNOWN</literal> is reported if the connection is bad.
       <literal>PQTRANS_ACTIVE</literal> is reported only when a query
       has been sent to the server and not yet completed.
      </para>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
1217

1218 1219 1220 1221 1222 1223 1224 1225
      <caution>
       <para>
        <function>PQtransactionStatus</> will give incorrect results when using
        a <productname>PostgreSQL</> 7.3 server that has the parameter <literal>autocommit</>
        set to off.  The server-side autocommit feature has been
        deprecated and does not exist in later server versions.
       </para>
      </caution>
1226 1227
     </listitem>
    </varlistentry>
1228

1229 1230 1231 1232 1233 1234 1235
    <varlistentry>
     <term>
      <function>PQparameterStatus</function>
      <indexterm>
       <primary>PQparameterStatus</primary>
      </indexterm>
     </term>
1236

1237 1238 1239
     <listitem>
      <para>
       Looks up a current parameter setting of the server.
Tom Lane's avatar
Tom Lane committed
1240

1241 1242 1243
       <synopsis>
        const char *PQparameterStatus(const PGconn *conn, const char *paramName);
       </synopsis>
1244

1245 1246 1247 1248 1249 1250
       Certain parameter values are reported by the server automatically at
       connection startup or whenever their values change.
       <function>PQparameterStatus</> can be used to interrogate these settings.
       It returns the current value of a parameter if known, or <symbol>NULL</symbol>
       if the parameter is not known.
      </para>
1251 1252

      <para>
1253 1254 1255 1256
       Parameters reported as of the current release include
       <literal>server_version</>,
       <literal>server_encoding</>,
       <literal>client_encoding</>,
1257
       <literal>application_name</>,
1258 1259 1260
       <literal>is_superuser</>,
       <literal>session_authorization</>,
       <literal>DateStyle</>,
1261
       <literal>IntervalStyle</>,
1262 1263 1264 1265 1266 1267
       <literal>TimeZone</>,
       <literal>integer_datetimes</>, and
       <literal>standard_conforming_strings</>.
       (<literal>server_encoding</>, <literal>TimeZone</>, and
       <literal>integer_datetimes</> were not reported by releases before 8.0;
       <literal>standard_conforming_strings</> was not reported by releases
1268 1269
       before 8.1;
       <literal>IntervalStyle</> was not reported by releases before 8.4;
1270
       <literal>application_name</> was not reported by releases before 9.0.)
1271 1272 1273 1274 1275
       Note that
       <literal>server_version</>,
       <literal>server_encoding</> and
       <literal>integer_datetimes</>
       cannot change after startup.
1276 1277 1278
      </para>

      <para>
1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303
       Pre-3.0-protocol servers do not report parameter settings, but
       <application>libpq</> includes logic to obtain values for
       <literal>server_version</> and <literal>client_encoding</> anyway.
       Applications are encouraged to use <function>PQparameterStatus</>
       rather than <foreignphrase>ad hoc</> code to determine these values.
       (Beware however that on a pre-3.0 connection, changing
       <literal>client_encoding</> via <command>SET</> after connection
       startup will not be reflected by <function>PQparameterStatus</>.)
       For <literal>server_version</>, see also
       <function>PQserverVersion</>, which returns the information in a
       numeric form that is much easier to compare against.
      </para>

      <para>
       If no value for <literal>standard_conforming_strings</> is reported,
       applications can assume it is <literal>off</>, that is, backslashes
       are treated as escapes in string literals.  Also, the presence of
       this parameter can be taken as an indication that the escape string
       syntax (<literal>E'...'</>) is accepted.
      </para>

      <para>
       Although the returned pointer is declared <literal>const</>, it in fact
       points to mutable storage associated with the <literal>PGconn</> structure.
       It is unwise to assume the pointer will remain valid across queries.
1304 1305
      </para>
     </listitem>
1306
    </varlistentry>
1307

1308 1309 1310 1311 1312 1313 1314
    <varlistentry>
     <term>
      <function>PQprotocolVersion</function>
      <indexterm>
       <primary>PQprotocolVersion</primary>
      </indexterm>
     </term>
1315

1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333
     <listitem>
      <para>
       Interrogates the frontend/backend protocol being used.
       <synopsis>
        int PQprotocolVersion(const PGconn *conn);
       </synopsis>
       Applications might wish to use this to determine whether certain
       features are supported.  Currently, the possible values are 2 (2.0
       protocol), 3 (3.0 protocol), or zero (connection bad).  This will
       not change after connection startup is complete, but it could
       theoretically change during a connection reset.  The 3.0 protocol
       will normally be used when communicating with
       <productname>PostgreSQL</> 7.4 or later servers; pre-7.4 servers
       support only protocol 2.0.  (Protocol 1.0 is obsolete and not
       supported by <application>libpq</application>.)
      </para>
     </listitem>
    </varlistentry>
1334

1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358
    <varlistentry>
     <term>
      <function>PQserverVersion</function>
      <indexterm>
       <primary>PQserverVersion</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns an integer representing the backend version.
       <synopsis>
        int PQserverVersion(const PGconn *conn);
       </synopsis>
       Applications might use this to determine the version of the database
       server they are connected to. The number is formed by converting
       the major, minor, and revision numbers into two-decimal-digit
       numbers and appending them together. For example, version 8.1.5
       will be returned as 80105, and version 8.2 will be returned as
       80200 (leading zeroes are not shown).  Zero is returned if the
       connection is bad.
      </para>
     </listitem>
    </varlistentry>
1359

1360
    <varlistentry>
1361 1362 1363 1364 1365 1366 1367
     <term>
      <function>PQerrorMessage</function>
      <indexterm>
       <primary>PQerrorMessage</primary>
      </indexterm>
     </term>

1368 1369
     <listitem>
      <para>
1370 1371 1372 1373 1374 1375 1376
       <indexterm><primary>error message</></> Returns the error message
       most recently generated by an operation on the connection.

       <synopsis>
        char *PQerrorMessage(const PGconn *conn);
       </synopsis>

1377 1378 1379
      </para>

      <para>
1380
       Nearly all <application>libpq</> functions will set a message for
1381 1382
       <function>PQerrorMessage</function> if they fail.  Note that by
       <application>libpq</application> convention, a nonempty
1383 1384 1385 1386 1387
       <function>PQerrorMessage</function> result can be multiple lines,
       and will include a trailing newline. The caller should not free
       the result directly. It will be freed when the associated
       <structname>PGconn</> handle is passed to
       <function>PQfinish</function>.  The result string should not be
1388
       expected to remain the same across operations on the
1389
       <literal>PGconn</> structure.
1390 1391
      </para>
     </listitem>
1392
    </varlistentry>
1393

1394
    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
1395
     <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
1396 1397
     <listitem>
      <para>
1398 1399 1400
       Obtains the file descriptor number of the connection socket to
       the server.  A valid descriptor will be greater than or equal
       to 0; a result of -1 indicates that no server connection is
1401 1402
       currently open.  (This will not change during normal operation,
       but could change during connection setup or reset.)
1403 1404 1405 1406 1407

       <synopsis>
        int PQsocket(const PGconn *conn);
       </synopsis>

1408 1409 1410 1411 1412
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
1413
     <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
1414 1415
     <listitem>
      <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1416 1417 1418 1419
       Returns the process <acronym>ID</acronym>
       (PID)<indexterm><primary>PID</><secondary>determining PID of
       server process</><tertiary>in libpq</></> of the backend server
       process handling this connection.
1420

1421 1422 1423 1424 1425 1426
       <synopsis>
        int PQbackendPID(const PGconn *conn);
       </synopsis>
      </para>

      <para>
1427
       The backend <acronym>PID</acronym> is useful for debugging
1428 1429
       purposes and for comparison to <command>NOTIFY</command>
       messages (which include the <acronym>PID</acronym> of the
1430 1431 1432
       notifying backend process).  Note that the
       <acronym>PID</acronym> belongs to a process executing on the
       database server host, not the local host!
1433 1434
      </para>
     </listitem>
1435
    </varlistentry>
1436

1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456
    <varlistentry>
     <term><function>PQconnectionNeedsPassword</function><indexterm><primary>PQconnectionNeedsPassword</></></term>
     <listitem>
      <para>
       Returns true (1) if the connection authentication method
       required a password, but none was available.
       Returns false (0) if not.

       <synopsis>
        int PQconnectionNeedsPassword(const PGconn *conn);
       </synopsis>
      </para>

      <para>
       This function can be applied after a failed connection attempt
       to decide whether to prompt the user for a password.
      </para>
     </listitem>
    </varlistentry>

1457 1458 1459 1460 1461
    <varlistentry>
     <term><function>PQconnectionUsedPassword</function><indexterm><primary>PQconnectionUsedPassword</></></term>
     <listitem>
      <para>
       Returns true (1) if the connection authentication method
1462
       used a password. Returns false (0) if not.
1463 1464 1465 1466

       <synopsis>
        int PQconnectionUsedPassword(const PGconn *conn);
       </synopsis>
1467 1468 1469
      </para>

      <para>
1470 1471
       This function can be applied after either a failed or successful
       connection attempt to detect whether the server demanded a password.
1472 1473 1474 1475
      </para>
     </listitem>
    </varlistentry>

1476
    <varlistentry>
Peter Eisentraut's avatar
Peter Eisentraut committed
1477
     <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
1478 1479
     <listitem>
      <para>
Peter Eisentraut's avatar
Peter Eisentraut committed
1480
       <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
1481
       Returns the SSL structure used in the connection, or null
1482
       if SSL is not in use.
1483

1484 1485 1486 1487 1488 1489 1490 1491 1492
       <synopsis>
        SSL *PQgetssl(const PGconn *conn);
       </synopsis>
      </para>

      <para>
       This structure can be used to verify encryption levels, check server
       certificates, and more. Refer to the <productname>OpenSSL</>
       documentation for information about this structure.
1493
      </para>
1494

1495
      <para>
1496
       You must define <symbol>USE_SSL</symbol> in order to get the
1497 1498 1499
       correct prototype for this function. Doing so will also
       automatically include <filename>ssl.h</filename> from
       <productname>OpenSSL</productname>.
1500 1501
      </para>
     </listitem>
1502
    </varlistentry>
1503

1504 1505 1506 1507 1508 1509 1510
   </variablelist>
  </para>

 </sect1>

 <sect1 id="libpq-exec">
  <title>Command Execution Functions</title>
1511

1512 1513 1514 1515 1516
  <para>
   Once a connection to a database server has been successfully
   established, the functions described here are used to perform
   SQL queries and commands.
  </para>
1517

1518 1519
  <sect2 id="libpq-exec-main">
   <title>Main Functions</title>
1520

1521 1522 1523 1524 1525 1526 1527 1528 1529
   <para>
    <variablelist>
     <varlistentry>
      <term>
       <function>PQexec</function>
       <indexterm>
        <primary>PQexec</primary>
       </indexterm>
      </term>
Peter Eisentraut's avatar
Peter Eisentraut committed
1530

1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579
      <listitem>
       <para>
        Submits a command to the server and waits for the result.

        <synopsis>
         PGresult *PQexec(PGconn *conn, const char *command);
        </synopsis>
       </para>

       <para>
        Returns a <structname>PGresult</structname> pointer or possibly a null
        pointer.  A non-null pointer will generally be returned except in
        out-of-memory conditions or serious errors such as inability to send
        the command to the server.  If a null pointer is returned, it should
        be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result.  Use
        <function>PQerrorMessage</function> to get more information about such
        errors.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    It is allowed to include multiple SQL commands (separated by semicolons)
    in the command string.  Multiple queries sent in a single
    <function>PQexec</> call are processed in a single transaction, unless
    there are explicit <command>BEGIN</command>/<command>COMMIT</command>
    commands included in the query string to divide it into multiple
    transactions.  Note however that the returned
    <structname>PGresult</structname> structure describes only the result
    of the last command executed from the string.  Should one of the
    commands fail, processing of the string stops with it and the returned
    <structname>PGresult</structname> describes the error condition.
   </para>

   <para>
    <variablelist>
     <varlistentry>
      <term>
       <function>PQexecParams</function>
       <indexterm>
        <primary>PQexecParams</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Submits a command to the server and waits for the result,
        with the ability to pass parameters separately from the SQL
        command text.
1580

1581 1582 1583 1584 1585 1586 1587 1588 1589 1590
<synopsis>
PGresult *PQexecParams(PGconn *conn,
                       const char *command,
                       int nParams,
                       const Oid *paramTypes,
                       const char * const *paramValues,
                       const int *paramLengths,
                       const int *paramFormats,
                       int resultFormat);
</synopsis>
1591
       </para>
1592

1593 1594 1595 1596 1597 1598 1599
       <para>
        <function>PQexecParams</> is like <function>PQexec</>, but offers additional
        functionality: parameter values can be specified separately from the command
        string proper, and query results can be requested in either text or binary
        format.  <function>PQexecParams</> is supported only in protocol 3.0 and later
        connections; it will fail when using protocol 2.0.
       </para>
1600

1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685
       <para>
        The function arguments are:

        <variablelist>
         <varlistentry>
          <term><parameter>conn</parameter></term>

          <listitem>
           <para>
            The connection object to send the command through.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><parameter>command</parameter></term>
          <listitem>
           <para>
            The SQL command string to be executed. If parameters are used,
            they are referred to in the command string as <literal>$1</>,
            <literal>$2</>, etc.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><parameter>nParams</parameter></term>
          <listitem>
           <para>
            The number of parameters supplied; it is the length of the arrays
            <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
            <parameter>paramLengths[]</>, and <parameter>paramFormats[]</>. (The
            array pointers can be <symbol>NULL</symbol> when <parameter>nParams</>
            is zero.)
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><parameter>paramTypes[]</parameter></term>
          <listitem>
           <para>
            Specifies, by OID, the data types to be assigned to the
            parameter symbols.  If <parameter>paramTypes</> is
            <symbol>NULL</symbol>, or any particular element in the array
            is zero, the server infers a data type for the parameter symbol
            in the same way it would do for an untyped literal string.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><parameter>paramValues[]</parameter></term>
          <listitem>
           <para>
            Specifies the actual values of the parameters.  A null pointer
            in this array means the corresponding parameter is null;
            otherwise the pointer points to a zero-terminated text string
            (for text format) or binary data in the format expected by the
            server (for binary format).
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><parameter>paramLengths[]</parameter></term>
          <listitem>
           <para>
            Specifies the actual data lengths of binary-format parameters.
            It is ignored for null parameters and text-format parameters.
            The array pointer can be null when there are no binary parameters.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><parameter>paramFormats[]</parameter></term>
          <listitem>
           <para>
            Specifies whether parameters are text (put a zero in the
            array entry for the corresponding parameter) or binary (put
            a one in the array entry for the corresponding parameter).
            If the array pointer is null then all parameters are presumed
            to be text strings.
           </para>
1686 1687 1688 1689 1690 1691
           <para>
            Values passed in binary format require knowlege of
            the internal representation expected by the backend.
            For example, integers must be passed in network byte
            order.  Passing <type>numeric</> values requires
            knowledge of the server storage format, as implemented
1692 1693 1694
            in
            <filename>src/backend/utils/adt/numeric.c::numeric_send()</> and
            <filename>src/backend/utils/adt/numeric.c::numeric_recv()</>.
1695
           </para>
1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><parameter>resultFormat</parameter></term>
          <listitem>
           <para>
            Specify zero to obtain results in text format, or one to obtain
            results in binary format.  (There is not currently a provision
            to obtain different result columns in different formats,
            although that is possible in the underlying protocol.)
           </para>
          </listitem>
         </varlistentry>
        </variablelist>
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
1716

1717 1718 1719 1720 1721 1722
   <para>
    The primary advantage of <function>PQexecParams</> over
    <function>PQexec</> is that parameter values can be separated from the
    command string, thus avoiding the need for tedious and error-prone
    quoting and escaping.
   </para>
1723

1724 1725 1726 1727 1728 1729 1730
   <para>
    Unlike <function>PQexec</>, <function>PQexecParams</> allows at most
    one SQL command in the given string.  (There can be semicolons in it,
    but not more than one nonempty command.)  This is a limitation of the
    underlying protocol, but has some usefulness as an extra defense against
    SQL-injection attacks.
   </para>
1731

1732 1733 1734 1735 1736 1737 1738 1739
   <tip>
    <para>
     Specifying parameter types via OIDs is tedious, particularly if you prefer
     not to hard-wire particular OID values into your program.  However, you can
     avoid doing so even in cases where the server by itself cannot determine the
     type of the parameter, or chooses a different type than you want.  In the
     SQL command text, attach an explicit cast to the parameter symbol to show what
     data type you will send.  For example:
1740
<programlisting>
1741
SELECT * FROM mytable WHERE x = $1::bigint;
1742
</programlisting>
1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764
     This forces parameter <literal>$1</> to be treated as <type>bigint</>, whereas
     by default it would be assigned the same type as <literal>x</>.  Forcing the
     parameter type decision, either this way or by specifying a numeric type OID,
     is strongly recommended when sending parameter values in binary format, because
     binary format has less redundancy than text format and so there is less chance
     that the server will detect a type mismatch mistake for you.
    </para>
   </tip>

   <para>
    <variablelist>
     <varlistentry>
      <term><function>PQprepare</function>
       <indexterm>
        <primary>PQprepare</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Submits a request to create a prepared statement with the
        given parameters, and waits for completion.
1765 1766 1767 1768 1769 1770 1771
<synopsis>
PGresult *PQprepare(PGconn *conn,
                    const char *stmtName,
                    const char *query,
                    int nParams,
                    const Oid *paramTypes);
</synopsis>
1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782
       </para>

       <para>
        <function>PQprepare</> creates a prepared statement for later
        execution with <function>PQexecPrepared</>.  This feature allows
        commands that will be used repeatedly to be parsed and planned just
        once, rather than each time they are executed.
        <function>PQprepare</> is supported only in protocol 3.0 and later
        connections; it will fail when using protocol 2.0.
       </para>

1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805
       <para>
        The function creates a prepared statement named
        <parameter>stmtName</> from the <parameter>query</> string, which
        must contain a single SQL command.  <parameter>stmtName</> can be
        <literal>""</> to create an unnamed statement, in which case any
        pre-existing unnamed statement is automatically replaced; otherwise
        it is an error if the statement name is already defined in the
        current session.  If any parameters are used, they are referred
        to in the query as <literal>$1</>, <literal>$2</>, etc.
        <parameter>nParams</> is the number of parameters for which types
        are pre-specified in the array <parameter>paramTypes[]</>.  (The
        array pointer can be <symbol>NULL</symbol> when
        <parameter>nParams</> is zero.) <parameter>paramTypes[]</>
        specifies, by OID, the data types to be assigned to the parameter
        symbols.  If <parameter>paramTypes</> is <symbol>NULL</symbol>,
        or any particular element in the array is zero, the server assigns
        a data type to the parameter symbol in the same way it would do
        for an untyped literal string.  Also, the query can use parameter
        symbols with numbers higher than <parameter>nParams</>; data types
        will be inferred for these symbols as well.  (See
        <function>PQdescribePrepared</function> for a means to find out
        what data types were inferred.)
       </para>
1806

1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819
       <para>
        As with <function>PQexec</>, the result is normally a
        <structname>PGresult</structname> object whose contents indicate
        server-side success or failure.  A null result indicates
        out-of-memory or inability to send the command at all.  Use
        <function>PQerrorMessage</function> to get more information about
        such errors.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    Prepared statements for use with <function>PQexecPrepared</> can also
1820 1821
    be created by executing SQL <xref linkend="sql-prepare">
    statements.  (But <function>PQprepare</>
1822 1823 1824
    is more flexible since it does not require parameter types to be
    pre-specified.)  Also, although there is no <application>libpq</>
    function for deleting a prepared statement, the SQL <xref
1825
    linkend="sql-deallocate"> statement
1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842
    can be used for that purpose.
   </para>

   <para>
    <variablelist>
     <varlistentry>
      <term>
       <function>PQexecPrepared</function>
       <indexterm>
        <primary>PQexecPrepared</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Sends a request to execute a prepared statement with given
        parameters, and waits for the result.
1843 1844 1845 1846 1847 1848 1849 1850 1851
<synopsis>
PGresult *PQexecPrepared(PGconn *conn,
                         const char *stmtName,
                         int nParams,
                         const char * const *paramValues,
                         const int *paramLengths,
                         const int *paramFormats,
                         int resultFormat);
</synopsis>
1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886
       </para>

       <para>
        <function>PQexecPrepared</> is like <function>PQexecParams</>,
        but the command to be executed is specified by naming a
        previously-prepared statement, instead of giving a query string.
        This feature allows commands that will be used repeatedly to be
        parsed and planned just once, rather than each time they are
        executed.  The statement must have been prepared previously in
        the current session.  <function>PQexecPrepared</> is supported
        only in protocol 3.0 and later connections; it will fail when
        using protocol 2.0.
       </para>

       <para>
        The parameters are identical to <function>PQexecParams</>, except that the
        name of a prepared statement is given instead of a query string, and the
        <parameter>paramTypes[]</> parameter is not present (it is not needed since
        the prepared statement's parameter types were determined when it was created).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       <function>PQdescribePrepared</function>
       <indexterm>
        <primary>PQdescribePrepared</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Submits a request to obtain information about the specified
        prepared statement, and waits for completion.
1887 1888 1889
<synopsis>
PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName);
</synopsis>
1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926
       </para>

       <para>
        <function>PQdescribePrepared</> allows an application to obtain
        information about a previously prepared statement.
        <function>PQdescribePrepared</> is supported only in protocol 3.0
        and later connections; it will fail when using protocol 2.0.
       </para>

       <para>
        <parameter>stmtName</> can be <literal>""</> or NULL to reference
        the unnamed statement, otherwise it must be the name of an existing
        prepared statement.  On success, a <structname>PGresult</> with
        status <literal>PGRES_COMMAND_OK</literal> is returned.  The
        functions <function>PQnparams</function> and
        <function>PQparamtype</function> can be applied to this
        <structname>PGresult</> to obtain information about the parameters
        of the prepared statement, and the functions
        <function>PQnfields</function>, <function>PQfname</function>,
        <function>PQftype</function>, etc provide information about the
        result columns (if any) of the statement.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       <function>PQdescribePortal</function>
       <indexterm>
        <primary>PQdescribePortal</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Submits a request to obtain information about the specified
        portal, and waits for completion.
1927 1928 1929
<synopsis>
PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
</synopsis>
1930
       </para>
1931

1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229
       <para>
        <function>PQdescribePortal</> allows an application to obtain
        information about a previously created portal.
        (<application>libpq</> does not provide any direct access to
        portals, but you can use this function to inspect the properties
        of a cursor created with a <command>DECLARE CURSOR</> SQL command.)
        <function>PQdescribePortal</> is supported only in protocol 3.0
        and later connections; it will fail when using protocol 2.0.
       </para>

       <para>
        <parameter>portalName</> can be <literal>""</> or NULL to reference
        the unnamed portal, otherwise it must be the name of an existing
        portal.  On success, a <structname>PGresult</> with status
        <literal>PGRES_COMMAND_OK</literal> is returned.  The functions
        <function>PQnfields</function>, <function>PQfname</function>,
        <function>PQftype</function>, etc can be applied to the
        <structname>PGresult</> to obtain information about the result
        columns (if any) of the portal.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    The <structname>PGresult</structname><indexterm><primary>PGresult</></>
    structure encapsulates the result returned by the server.
    <application>libpq</application> application programmers should be
    careful to maintain the <structname>PGresult</structname> abstraction.
    Use the accessor functions below to get at the contents of
    <structname>PGresult</structname>.  Avoid directly referencing the
    fields of the <structname>PGresult</structname> structure because they
    are subject to change in the future.

    <variablelist>
     <varlistentry>
      <term>
       <function>PQresultStatus</function>
       <indexterm>
        <primary>PQresultStatus</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Returns the result status of the command.
        <synopsis>
         ExecStatusType PQresultStatus(const PGresult *res);
        </synopsis>
       </para>

       <para>
        <function>PQresultStatus</function> can return one of the following values:

        <variablelist>
         <varlistentry>
          <term><literal>PGRES_EMPTY_QUERY</literal></term>
          <listitem>
           <para>
            The string sent to the server was empty.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PGRES_COMMAND_OK</literal></term>
          <listitem>
           <para>
            Successful completion of a command returning no data.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PGRES_TUPLES_OK</literal></term>
          <listitem>
           <para>
            Successful completion of a command returning data (such as
            a <command>SELECT</> or <command>SHOW</>).
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PGRES_COPY_OUT</literal></term>
          <listitem>
           <para>
            Copy Out (from server) data transfer started.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PGRES_COPY_IN</literal></term>
          <listitem>
           <para>
            Copy In (to server) data transfer started.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PGRES_BAD_RESPONSE</literal></term>
          <listitem>
           <para>
            The server's response was not understood.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PGRES_NONFATAL_ERROR</literal></term>
          <listitem>
           <para>
            A nonfatal error (a notice or warning) occurred.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><literal>PGRES_FATAL_ERROR</literal></term>
          <listitem>
           <para>
            A fatal error occurred.
           </para>
          </listitem>
         </varlistentry>
        </variablelist>

        If the result status is <literal>PGRES_TUPLES_OK</literal>, then
        the functions described below can be used to retrieve the rows
        returned by the query.  Note that a <command>SELECT</command>
        command that happens to retrieve zero rows still shows
        <literal>PGRES_TUPLES_OK</literal>.
        <literal>PGRES_COMMAND_OK</literal> is for commands that can never
        return rows (<command>INSERT</command>, <command>UPDATE</command>,
        etc.). A response of <literal>PGRES_EMPTY_QUERY</literal> might
        indicate a bug in the client software.
       </para>

       <para>
        A result of status <symbol>PGRES_NONFATAL_ERROR</symbol> will
        never be returned directly by <function>PQexec</function> or other
        query execution functions; results of this kind are instead passed
        to the notice processor (see <xref
        linkend="libpq-notice-processing">).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       <function>PQresStatus</function>
       <indexterm>
        <primary>PQresStatus</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Converts the enumerated type returned by
        <function>PQresultStatus</> into a string constant describing the
        status code. The caller should not free the result.

        <synopsis>
         char *PQresStatus(ExecStatusType status);
        </synopsis>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       <function>PQresultErrorMessage</function>
       <indexterm>
        <primary>PQresultErrorMessage</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
        Returns the error message associated with the command, or an empty string
        if there was no error.
        <synopsis>
         char *PQresultErrorMessage(const PGresult *res);
        </synopsis>
        If there was an error, the returned string will include a trailing
        newline.  The caller should not free the result directly. It will
        be freed when the associated <structname>PGresult</> handle is
        passed to <function>PQclear</function>.
       </para>

       <para>
        Immediately following a <function>PQexec</function> or
        <function>PQgetResult</function> call,
        <function>PQerrorMessage</function> (on the connection) will return
        the same string as <function>PQresultErrorMessage</function> (on
        the result).  However, a <structname>PGresult</structname> will
        retain its error message until destroyed, whereas the connection's
        error message will change when subsequent operations are done.
        Use <function>PQresultErrorMessage</function> when you want to
        know the status associated with a particular
        <structname>PGresult</structname>; use
        <function>PQerrorMessage</function> when you want to know the
        status from the latest operation on the connection.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><function>PQresultErrorField</function><indexterm><primary>PQresultErrorField</></></term>
      <listitem>
       <para>
        Returns an individual field of an error report.
        <synopsis>
         char *PQresultErrorField(const PGresult *res, int fieldcode);
        </synopsis>
        <parameter>fieldcode</> is an error field identifier; see the symbols
        listed below.  <symbol>NULL</symbol> is returned if the
        <structname>PGresult</structname> is not an error or warning result,
        or does not include the specified field.  Field values will normally
        not include a trailing newline. The caller should not free the
        result directly. It will be freed when the
        associated <structname>PGresult</> handle is passed to
        <function>PQclear</function>.
       </para>

       <para>
        The following field codes are available:
        <variablelist>
         <varlistentry>
          <term><symbol>PG_DIAG_SEVERITY</></term>
          <listitem>
           <para>
            The severity; the field contents are <literal>ERROR</>,
            <literal>FATAL</>, or <literal>PANIC</> (in an error message),
            or <literal>WARNING</>, <literal>NOTICE</>, <literal>DEBUG</>,
            <literal>INFO</>, or <literal>LOG</> (in a notice message), or
            a localized translation of one of these.  Always present.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <indexterm>
           <primary>error codes</primary>
           <secondary>libpq</secondary>
          </indexterm>
          <term><symbol>PG_DIAG_SQLSTATE</></term>
          <listitem>
           <para>
            The SQLSTATE code for the error. The SQLSTATE code identifies
            the type of error that has occurred; it can be used by
            front-end applications to perform specific operations (such
            as error handling) in response to a particular database error.
            For a list of the possible SQLSTATE codes, see <xref
            linkend="errcodes-appendix">. This field is not localizable,
            and is always present.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><symbol>PG_DIAG_MESSAGE_PRIMARY</></term>
          <listitem>
           <para>
            The primary human-readable error message (typically one line).
            Always present.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><symbol>PG_DIAG_MESSAGE_DETAIL</></term>
          <listitem>
           <para>
            Detail: an optional secondary error message carrying more
            detail about the problem.  Might run to multiple lines.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><symbol>PG_DIAG_MESSAGE_HINT</></term>
          <listitem>
           <para>
            Hint: an optional suggestion what to do about the problem.
            This is intended to differ from detail in that it offers advice
            (potentially inappropriate) rather than hard facts.  Might
            run to multiple lines.
           </para>
          </listitem>
         </varlistentry>

         <varlistentry>
          <term><symbol>PG_DIAG_STATEMENT_POSITION</></term>
          <listitem>
2230 2231 2232 2233 2234 2235 2236 2237
           <para>
            A string containing a decimal integer indicating an error cursor
            position as an index into the original statement string.  The
            first character has index 1, and positions are measured in
            characters not bytes.
           </para>
          </listitem>
         </varlistentry>
2238

2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251
         <varlistentry>
          <term><symbol>PG_DIAG_INTERNAL_POSITION</></term>
          <listitem>
           <para>
            This is defined the same as the
            <symbol>PG_DIAG_STATEMENT_POSITION</> field, but it is used
            when the cursor position refers to an internally generated
            command rather than the one submitted by the client.  The
            <symbol>PG_DIAG_INTERNAL_QUERY</> field will always appear when
            this field appears.
           </para>
          </listitem>
         </varlistentry>
2252

2253 2254 2255 2256 2257 2258 2259 2260 2261
         <varlistentry>
          <term><symbol>PG_DIAG_INTERNAL_QUERY</></term>
          <listitem>
           <para>
            The text of a failed internally-generated command.  This could
            be, for example, a SQL query issued by a PL/pgSQL function.
           </para>
          </listitem>
         </varlistentry>
2262

2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273
         <varlistentry>
          <term><symbol>PG_DIAG_CONTEXT</></term>
          <listitem>
           <para>
            An indication of the context in which the error occurred.
            Presently this includes a call stack traceback of active
            procedural language functions and internally-generated queries.
            The trace is one entry per line, most recent first.
           </para>
          </listitem>
         </varlistentry>
2274

2275 2276 2277 2278 2279 2280 2281 2282 2283
         <varlistentry>
          <term><symbol>PG_DIAG_SOURCE_FILE</></term>
          <listitem>
           <para>
            The file name of the source-code location where the error was
            reported.
           </para>
          </listitem>
         </varlistentry>
2284

2285 2286 2287 2288 2289 2290 2291 2292 2293
         <varlistentry>
          <term><symbol>PG_DIAG_SOURCE_LINE</></term>
          <listitem>
           <para>
            The line number of the source-code location where the error
            was reported.
           </para>
          </listitem>
         </varlistentry>
2294

2295 2296 2297 2298 2299 2300 2301 2302 2303 2304
         <varlistentry>
          <term><symbol>PG_DIAG_SOURCE_FUNCTION</></term>
          <listitem>
           <para>
            The name of the source-code function reporting the error.
           </para>
          </listitem>
         </varlistentry>
        </variablelist>
       </para>
2305

2306 2307 2308 2309 2310 2311
       <para>
        The client is responsible for formatting displayed information to meet
        its needs; in particular it should break long lines as needed.
        Newline characters appearing in the error message fields should be
        treated as paragraph breaks, not line breaks.
       </para>
2312

2313 2314 2315 2316 2317 2318
       <para>
        Errors generated internally by <application>libpq</application> will
        have severity and primary message, but typically no other fields.
        Errors returned by a pre-3.0-protocol server will include severity and
        primary message, and sometimes a detail message, but no other fields.
       </para>
2319

2320 2321 2322 2323 2324 2325 2326 2327
       <para>
        Note that error fields are only available from
        <structname>PGresult</structname> objects, not
        <structname>PGconn</structname> objects; there is no
        <function>PQerrorField</function> function.
       </para>
      </listitem>
     </varlistentry>
2328

2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341
     <varlistentry>
      <term><function>PQclear</function><indexterm><primary>PQclear</></></term>
      <listitem>
       <para>
        Frees  the  storage  associated with a
        <structname>PGresult</structname>.  Every command result should be
        freed via <function>PQclear</function> when it  is  no  longer
        needed.

        <synopsis>
         void PQclear(PGresult *res);
        </synopsis>
       </para>
2342

2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354
       <para>
        You can keep a <structname>PGresult</structname> object around for
        as long as you need it; it does not go away when you issue a new
        command, nor even if you close the connection.  To get rid of it,
        you must call <function>PQclear</function>.  Failure to do this
        will result in memory leaks in your application.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </sect2>
2355

2356 2357
  <sect2 id="libpq-exec-select-info">
   <title>Retrieving Query Result Information</title>
2358

2359 2360 2361 2362 2363 2364 2365 2366 2367 2368
   <para>
    These functions are used to extract information from a
    <structname>PGresult</structname> object that represents a successful
    query result (that is, one that has status
    <literal>PGRES_TUPLES_OK</literal>).  They can also be used to extract
    information from a successful Describe operation: a Describe's result
    has all the same column information that actual execution of the query
    would provide, but it has zero rows.  For objects with other status values,
    these functions will act as though the result has zero rows and zero columns.
   </para>
2369

2370 2371 2372 2373 2374 2375 2376 2377
   <variablelist>
    <varlistentry>
     <term>
      <function>PQntuples</function>
      <indexterm>
       <primary>PQntuples</primary>
      </indexterm>
     </term>
2378

2379 2380
     <listitem>
      <para>
2381 2382 2383
       Returns the number of rows (tuples) in the query result.  Because
       it returns an integer result, large result sets might overflow the
       return value on 32-bit operating systems.
2384

2385 2386 2387
       <synopsis>
        int PQntuples(const PGresult *res);
       </synopsis>
2388

2389 2390 2391
      </para>
     </listitem>
    </varlistentry>
2392

2393 2394 2395 2396 2397 2398 2399
    <varlistentry>
     <term>
      <function>PQnfields</function>
      <indexterm>
       <primary>PQnfields</primary>
      </indexterm>
     </term>
2400

2401 2402 2403 2404
     <listitem>
      <para>
       Returns the number of columns (fields) in each row of the query
       result.
2405

2406 2407 2408 2409 2410 2411
       <synopsis>
        int PQnfields(const PGresult *res);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
2412

2413 2414 2415 2416 2417 2418 2419
    <varlistentry>
     <term>
      <function>PQfname</function>
      <indexterm>
       <primary>PQfname</primary>
      </indexterm>
     </term>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
2420

2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432
     <listitem>
      <para>
       Returns the column name associated with the given column number.
       Column numbers start at 0. The caller should not free the result
       directly. It will be freed when the associated
       <structname>PGresult</> handle is passed to
       <function>PQclear</function>.
       <synopsis>
        char *PQfname(const PGresult *res,
                      int column_number);
       </synopsis>
      </para>
2433

2434 2435 2436 2437 2438
      <para>
       <symbol>NULL</symbol> is returned if the column number is out of range.
      </para>
     </listitem>
    </varlistentry>
2439

2440 2441 2442 2443 2444 2445 2446
    <varlistentry>
     <term>
      <function>PQfnumber</function>
      <indexterm>
       <primary>PQfnumber</primary>
      </indexterm>
     </term>
2447

2448 2449 2450 2451 2452 2453 2454 2455
     <listitem>
      <para>
       Returns the column number associated with the given column name.
       <synopsis>
        int PQfnumber(const PGresult *res,
                      const char *column_name);
       </synopsis>
      </para>
2456

2457 2458 2459
      <para>
       -1 is returned if the given name does not match any column.
      </para>
2460

2461 2462 2463 2464
      <para>
       The given name is treated like an identifier in an SQL command,
       that is, it is downcased unless double-quoted.  For example, given
       a query result generated from the SQL command:
2465
<programlisting>
2466
SELECT 1 AS FOO, 2 AS "BAR";
2467
</programlisting>
2468
       we would have the results:
2469 2470 2471 2472 2473 2474 2475 2476
<programlisting>
PQfname(res, 0)              <lineannotation>foo</lineannotation>
PQfname(res, 1)              <lineannotation>BAR</lineannotation>
PQfnumber(res, "FOO")        <lineannotation>0</lineannotation>
PQfnumber(res, "foo")        <lineannotation>0</lineannotation>
PQfnumber(res, "BAR")        <lineannotation>-1</lineannotation>
PQfnumber(res, "\"BAR\"")    <lineannotation>1</lineannotation>
</programlisting>
2477 2478 2479
      </para>
     </listitem>
    </varlistentry>
2480

2481 2482 2483 2484 2485 2486 2487
    <varlistentry>
     <term>
      <function>PQftable</function>
      <indexterm>
       <primary>PQftable</primary>
      </indexterm>
     </term>
2488

2489 2490 2491 2492 2493 2494 2495 2496 2497
     <listitem>
      <para>
       Returns the OID of the table from which the given column was
       fetched.  Column numbers start at 0.
       <synopsis>
        Oid PQftable(const PGresult *res,
                     int column_number);
       </synopsis>
      </para>
2498

2499 2500 2501 2502 2503 2504 2505
      <para>
       <literal>InvalidOid</> is returned if the column number is out of range,
       or if the specified column is not a simple reference to a table column,
       or when using pre-3.0 protocol.
       You can query the system table <literal>pg_class</literal> to determine
       exactly which table is referenced.
      </para>
Tom Lane's avatar
Tom Lane committed
2506

2507 2508 2509 2510 2511 2512 2513 2514
      <para>
       The type <type>Oid</type> and the constant
       <literal>InvalidOid</literal> will be defined when you include
       the <application>libpq</application> header file. They will both
       be some integer type.
      </para>
     </listitem>
    </varlistentry>
2515

2516 2517 2518 2519 2520 2521 2522
    <varlistentry>
     <term>
      <function>PQftablecol</function>
      <indexterm>
       <primary>PQftablecol</primary>
      </indexterm>
     </term>
2523

2524 2525 2526 2527 2528 2529 2530 2531 2532 2533
     <listitem>
      <para>
       Returns the column number (within its table) of the column making
       up the specified query result column.  Query-result column numbers
       start at 0, but table columns have nonzero numbers.
       <synopsis>
       int PQftablecol(const PGresult *res,
                       int column_number);
       </synopsis>
      </para>
2534

2535 2536 2537 2538 2539 2540 2541
      <para>
       Zero is returned if the column number is out of range, or if the
       specified column is not a simple reference to a table column, or
       when using pre-3.0 protocol.
      </para>
     </listitem>
    </varlistentry>
2542

2543 2544 2545 2546 2547 2548 2549
    <varlistentry>
     <term>
      <function>PQfformat</function>
      <indexterm>
       <primary>PQfformat</primary>
      </indexterm>
     </term>
2550

2551 2552 2553 2554 2555 2556 2557 2558 2559
     <listitem>
      <para>
       Returns the format code indicating the format of the given
       column.  Column numbers start at 0.
       <synopsis>
        int PQfformat(const PGresult *res,
                      int column_number);
       </synopsis>
      </para>
2560

2561 2562 2563 2564 2565 2566 2567
      <para>
       Format code zero indicates textual data representation, while format
       code one indicates binary representation.  (Other codes are reserved
       for future definition.)
      </para>
     </listitem>
    </varlistentry>
2568

2569 2570 2571 2572 2573 2574 2575
    <varlistentry>
     <term>
      <function>PQftype</function>
      <indexterm>
       <primary>PQftype</primary>
      </indexterm>
     </term>
2576

2577 2578 2579 2580 2581 2582 2583 2584 2585 2586
     <listitem>
      <para>
       Returns the data type associated with the given  column number.
       The  integer  returned is the internal OID number of the type.
       Column numbers start at 0.
       <synopsis>
        Oid PQftype(const PGresult *res,
                    int column_number);
       </synopsis>
      </para>
2587

2588 2589 2590 2591 2592 2593 2594 2595 2596
      <para>
       You can query the system table <literal>pg_type</literal> to
       obtain the names and properties of the various data types. The
       <acronym>OID</acronym>s of the built-in data types are defined
       in the file <filename>src/include/catalog/pg_type.h</filename>
       in the source tree.
      </para>
     </listitem>
    </varlistentry>
2597

2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614
    <varlistentry>
     <term>
      <function>PQfmod</function>
      <indexterm>
       <primary>PQfmod</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns  the type modifier of the column associated with the
       given column number.  Column numbers start at 0.
       <synopsis>
        int PQfmod(const PGresult *res,
                   int column_number);
       </synopsis>
      </para>
2615

2616 2617 2618 2619 2620 2621 2622 2623 2624
      <para>
       The interpretation of modifier values is type-specific; they
       typically indicate precision or size limits.  The value -1 is
       used to indicate <quote>no information available</>.  Most data
       types do not use modifiers, in which case the value is always
       -1.
      </para>
     </listitem>
    </varlistentry>
2625

2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642
    <varlistentry>
     <term>
      <function>PQfsize</function>
      <indexterm>
       <primary>PQfsize</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns  the  size  in bytes of the column associated with the
       given column number.  Column numbers start at 0.
       <synopsis>
        int PQfsize(const PGresult *res,
                    int column_number);
       </synopsis>
      </para>
2643

2644 2645 2646 2647 2648 2649 2650 2651 2652
      <para>
       <function>PQfsize</> returns the space allocated for this column
       in a database row, in other words the size of the server's
       internal representation of the data type.  (Accordingly, it is
       not really very useful to clients.) A negative value indicates
       the data type is variable-length.
      </para>
     </listitem>
    </varlistentry>
2653

2654 2655 2656 2657 2658 2659 2660 2661 2662
    <varlistentry>
     <term>
      <function>PQbinaryTuples</function>
      <indexterm>
       <primary>PQbinaryTuples</primary>
      </indexterm>
     </term>

     <listitem>
2663 2664 2665 2666 2667 2668 2669
      <para>
       Returns 1 if the <structname>PGresult</> contains binary data
       and 0 if it contains text data.
       <synopsis>
        int PQbinaryTuples(const PGresult *res);
       </synopsis>
      </para>
2670

2671 2672 2673 2674 2675 2676 2677 2678 2679 2680
      <para>
       This function is deprecated (except for its use in connection with
       <command>COPY</>), because it is possible for a single
       <structname>PGresult</> to contain text data in some columns and
       binary data in others.  <function>PQfformat</> is preferred.
       <function>PQbinaryTuples</> returns 1 only if all columns of the
       result are binary (format 1).
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
2681

2682 2683 2684 2685 2686 2687 2688
    <varlistentry>
     <term>
      <function>PQgetvalue</function>
       <indexterm>
        <primary>PQgetvalue</primary>
       </indexterm>
     </term>
Bruce Momjian's avatar
Bruce Momjian committed
2689

2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702
     <listitem>
      <para>
       Returns a single field value of one row of a
       <structname>PGresult</structname>.  Row and column numbers start
       at 0.  The caller should not free the result directly.  It will
       be freed when the associated <structname>PGresult</> handle is
       passed to <function>PQclear</function>.
       <synopsis>
        char *PQgetvalue(const PGresult *res,
                         int row_number,
                         int column_number);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
2703

2704 2705 2706 2707 2708 2709 2710 2711 2712 2713
      <para>
       For data in text format, the value returned by
       <function>PQgetvalue</function> is a null-terminated character
       string  representation of the field value.  For data in binary
       format, the value is in the binary representation determined by
       the data type's <function>typsend</> and <function>typreceive</>
       functions.  (The value is actually followed by a zero byte in
       this case too, but that is not ordinarily useful, since the
       value is likely to contain embedded nulls.)
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
2714

2715 2716 2717 2718 2719
      <para>
       An empty string is returned if the field value is null.  See
       <function>PQgetisnull</> to distinguish null values from
       empty-string values.
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
2720

2721 2722 2723 2724 2725 2726 2727 2728 2729 2730
      <para>
       The pointer returned  by  <function>PQgetvalue</function> points
       to storage that is part of the <structname>PGresult</structname>
       structure.  One should not modify the data it points to, and one
       must explicitly copy the data into other storage if it is to be
       used past the lifetime of the  <structname>PGresult</structname>
       structure itself.
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
2731

2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742
    <varlistentry>
     <term>
      <function>PQgetisnull</function>
      <indexterm>
       <primary>PQgetisnull</primary>
      </indexterm>
      <indexterm>
       <primary>null value</primary>
       <secondary sortas="libpq">in libpq</secondary>
      </indexterm>
     </term>
Bruce Momjian's avatar
Bruce Momjian committed
2743

2744 2745 2746 2747 2748 2749 2750 2751 2752 2753
     <listitem>
      <para>
       Tests a field for a null value.  Row and column numbers start
       at 0.
       <synopsis>
        int PQgetisnull(const PGresult *res,
                        int row_number,
                        int column_number);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
2754

2755 2756 2757 2758 2759 2760 2761 2762
      <para>
       This function returns  1 if the field is null and 0 if it
       contains a non-null value.  (Note that
       <function>PQgetvalue</function> will return an empty string,
       not a null pointer, for a null field.)
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
2763

2764 2765 2766 2767 2768 2769
    <varlistentry>
     <term>
     <function>PQgetlength</function>
     <indexterm>
      <primary>PQgetlength</primary>
     </indexterm></term>
Bruce Momjian's avatar
Bruce Momjian committed
2770

2771 2772 2773 2774 2775 2776 2777 2778 2779 2780
     <listitem>
      <para>
       Returns the actual length of a field value in bytes.  Row and
       column numbers start at 0.
       <synopsis>
        int PQgetlength(const PGresult *res,
                        int row_number,
                        int column_number);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
2781

2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792
      <para>
       This is the actual data length for the particular data value,
       that is, the size of the object pointed to by
       <function>PQgetvalue</function>.  For text data format this is
       the same as <function>strlen()</>.  For binary format this is
       essential information.  Note that one should <emphasis>not</>
       rely on <function>PQfsize</function> to obtain the actual data
       length.
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
2793

2794 2795 2796 2797 2798 2799 2800
    <varlistentry>
     <term>
      <function>PQnparams</function>
      <indexterm>
       <primary>PQnparams</primary>
      </indexterm>
     </term>
Bruce Momjian's avatar
Bruce Momjian committed
2801

2802 2803 2804 2805 2806 2807 2808
     <listitem>
      <para>
       Returns the number of parameters of a prepared statement.
       <synopsis>
        int PQnparams(const PGresult *res);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
2809

2810 2811 2812 2813 2814 2815 2816
      <para>
       This function is only useful when inspecting the result of
       <function>PQdescribePrepared</>.  For other types of queries it
       will return zero.
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
2817

2818 2819 2820 2821 2822 2823 2824
    <varlistentry>
     <term>
      <function>PQparamtype</function>
      <indexterm>
       <primary>PQparamtype</primary>
      </indexterm>
     </term>
Bruce Momjian's avatar
Bruce Momjian committed
2825

2826 2827 2828 2829 2830 2831 2832 2833
     <listitem>
      <para>
       Returns the data type of the indicated statement parameter.
       Parameter numbers start at 0.
       <synopsis>
        Oid PQparamtype(const PGresult *res, int param_number);
       </synopsis>
      </para>
Bruce Momjian's avatar
Bruce Momjian committed
2834

2835 2836 2837 2838 2839 2840 2841
      <para>
       This function is only useful when inspecting the result of
       <function>PQdescribePrepared</>.  For other types of queries it
       will return zero.
      </para>
     </listitem>
    </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
2842

2843 2844 2845 2846 2847 2848 2849
    <varlistentry>
     <term>
      <function>PQprint</function>
      <indexterm>
       <primary>PQprint</primary>
      </indexterm>
     </term>
Bruce Momjian's avatar
Bruce Momjian committed
2850

2851 2852 2853 2854 2855
     <listitem>
      <para>
       Prints out all the rows and,  optionally,  the column names  to
       the specified output stream.
       <synopsis>
2856 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872
void PQprint(FILE *fout,      /* output stream */
             const PGresult *res,
             const PQprintOpt *po);
typedef struct {
  pqbool  header;      /* print output field headings and row count */
  pqbool  align;       /* fill align the fields */
  pqbool  standard;    /* old brain dead format */
  pqbool  html3;       /* output HTML tables */
  pqbool  expanded;    /* expand tables */
  pqbool  pager;       /* use pager for output if needed */
  char    *fieldSep;   /* field separator */
  char    *tableOpt;   /* attributes for HTML table element */
  char    *caption;    /* HTML table caption */
  char    **fieldName; /* null-terminated array of replacement field names */
} PQprintOpt;
       </synopsis>
      </para>
2873

2874 2875 2876 2877 2878 2879 2880 2881 2882
      <para>
       This function was formerly used by <application>psql</application>
       to print query results, but this is no longer the case.  Note
       that it assumes all the data is in text format.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </sect2>
2883

2884
  <sect2 id="libpq-exec-nonselect">
2885
   <title>Retrieving Other Result Information</title>
2886

2887
   <para>
2888 2889
    These functions are used to extract other information from
    <structname>PGresult</structname> objects.
2890
   </para>
2891

2892 2893 2894 2895 2896 2897 2898 2899 2900 2901 2902 2903 2904 2905 2906 2907 2908 2909 2910 2911 2912 2913 2914 2915 2916 2917 2918
   <variablelist>
    <varlistentry>
     <term>
      <function>PQcmdStatus</function>
      <indexterm>
       <primary>PQcmdStatus</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns the command status tag from the SQL command that generated
       the <structname>PGresult</structname>.
       <synopsis>
        char *PQcmdStatus(PGresult *res);
       </synopsis>
      </para>

      <para>
       Commonly this is just the name of the command, but it might include
       additional data such as the number of rows processed. The caller
       should not free the result directly. It will be freed when the
       associated <structname>PGresult</> handle is passed to
       <function>PQclear</function>.
      </para>
     </listitem>
    </varlistentry>
2919

2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934
    <varlistentry>
     <term>
      <function>PQcmdTuples</function>
      <indexterm>
       <primary>PQcmdTuples</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns the number of rows affected by the SQL command.
       <synopsis>
        char *PQcmdTuples(PGresult *res);
       </synopsis>
      </para>
2935

2936 2937 2938 2939
      <para>
       This function returns a string containing the number of rows
       affected by the <acronym>SQL</> statement that generated the
       <structname>PGresult</>. This function can only be used following
2940 2941 2942 2943 2944 2945
       the execution of a <command>SELECT</>, <command>CREATE TABLE AS</>,
       <command>INSERT</>, <command>UPDATE</>, <command>DELETE</>,
       <command>MOVE</>, <command>FETCH</>, or <command>COPY</> statement,
       or an <command>EXECUTE</> of a prepared query that contains an
       <command>INSERT</>, <command>UPDATE</>, or <command>DELETE</> statement.
       If the command that generated the <structname>PGresult</> was anything
2946 2947 2948 2949 2950 2951 2952
       else, <function>PQcmdTuples</> returns an empty string. The caller
       should not free the return value directly. It will be freed when
       the associated <structname>PGresult</> handle is passed to
       <function>PQclear</function>.
      </para>
     </listitem>
    </varlistentry>
2953

2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977
    <varlistentry>
     <term>
      <function>PQoidValue</function>
      <indexterm>
       <primary>PQoidValue</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns the OID<indexterm><primary>OID</><secondary>in libpq</></>
       of the inserted row, if the <acronym>SQL</> command was an
       <command>INSERT</> that inserted exactly one row into a table that
       has OIDs, or a <command>EXECUTE</> of a prepared query containing
       a suitable <command>INSERT</> statement.  Otherwise, this function
       returns <literal>InvalidOid</literal>. This function will also
       return <literal>InvalidOid</literal> if the table affected by the
       <command>INSERT</> statement does not contain OIDs.
       <synopsis>
        Oid PQoidValue(const PGresult *res);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
2978

2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000
    <varlistentry>
     <term>
      <function>PQoidStatus</function>
      <indexterm>
       <primary>PQoidStatus</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns a string with the OID of the inserted row, if the
       <acronym>SQL</acronym> command was an <command>INSERT</command>
       that inserted exactly one row, or a <command>EXECUTE</command> of
       a prepared statement consisting of a suitable
       <command>INSERT</command>.  (The string will be <literal>0</> if
       the <command>INSERT</command> did not insert exactly one row, or
       if the target table does not have OIDs.)  If the command was not
       an <command>INSERT</command>, returns an empty string.
       <synopsis>
        char *PQoidStatus(const PGresult *res);
       </synopsis>
      </para>
3001

3002 3003 3004 3005 3006 3007 3008
      <para>
       This function is deprecated in favor of
       <function>PQoidValue</function>.  It is not thread-safe.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
3009

3010
  </sect2>
3011

3012 3013
  <sect2 id="libpq-exec-escape-string">
   <title>Escaping Strings for Inclusion in SQL Commands</title>
3014

3015 3016 3017 3018
   <indexterm zone="libpq-exec-escape-string">
    <primary>escaping strings</primary>
    <secondary>in libpq</secondary>
   </indexterm>
3019

3020 3021 3022
   <variablelist>
    <varlistentry>
     <term>
3023
      <function>PQescapeLiteral</function>
3024
      <indexterm>
3025
       <primary>PQescapeLiteral</primary>
3026 3027
      </indexterm>
     </term>
3028

3029 3030
     <listitem>
     <para>
3031 3032 3033 3034 3035 3036 3037
      <synopsis>
       size_t PQescapeLiteral(PGconn *conn, char *str, size_t len)
      </synopsis>
     </para>

     <para>
      <function>PQescapeLiteral</function> escapes a string for
3038 3039 3040 3041
      use within an SQL command.  This is useful when inserting data
      values as literal constants in SQL commands.  Certain characters
      (such as quotes and backslashes) must be escaped to prevent them
      from being interpreted specially by the SQL parser.
3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064
      <function>PQescapeLiteral</> performs this operation.
     </para>

     <para>
      <function>PQescapeLiteral</> returns an escaped version of the
      <parameter>str</parameter> parameter in memory allocated with
      <function>malloc()</>.  This memory should be freed using
      <function>PQfreemem()</> when the result is no longer needed.
      A terminating zero byte is not required, and should not be
      counted in <parameter>length</>.  (If a terminating zero byte is found
      before <parameter>length</> bytes are processed,
      <function>PQescapeLiteral</> stops at the zero; the behavior is
      thus rather like <function>strncpy</>.) The
      return string has all special characters replaced so that they can
      be properly processed by the <productname>PostgreSQL</productname>
      string literal parser.  A terminating zero byte is also added.  The
      single quotes that must surround <productname>PostgreSQL</productname>
      string literals are included in the result string.
     </para>

     <para>
      On error, <function>PQescapeLiteral</> returns NULL and a suitable
      message is stored in the <parameter>conn</> object.
3065
     </para>
3066

3067 3068 3069 3070 3071 3072 3073 3074 3075
     <tip>
      <para>
       It is especially important to do proper escaping when handling
       strings that were received from an untrustworthy source.
       Otherwise there is a security risk: you are vulnerable to
       <quote>SQL injection</> attacks wherein unwanted SQL commands are
       fed to your database.
      </para>
     </tip>
3076

3077 3078 3079 3080
     <para>
      Note that it is not necessary nor correct to do escaping when a data
      value is passed as a separate parameter in <function>PQexecParams</> or
      its sibling routines.
3081 3082 3083 3084 3085 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 3099 3100 3101 3102 3103 3104 3105 3106 3107
     </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQescapeIdentifier</function>
      <indexterm>
       <primary>PQescapeIdentifier</primary>
      </indexterm>
     </term>

     <listitem>
     <para>
      <synopsis>
       size_t PQescapeIdentifier(PGconn *conn, char *str, size_t len)
      </synopsis>
     </para>

     <para>
      <function>PQescapeIndentifier</function> escapes a string for
      use as an SQL identifier, such as a table, column, or function name.
      This is useful when a user-supplied identifier might contain
      special characters that would otherwise not be interpreted as part
      of the identifier by the SQL parser, or when the identifier might
      contain uppercase characters whose case should be preserved.
     </para>
3108

3109 3110 3111 3112 3113 3114 3115 3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149
     <para>
      <function>PQescapeIdentifier</> returns a version of the
      <parameter>str</parameter> parameter escaped as an SQL identifier
      in memory allocated with <function>malloc()</>.  This memory must be
      freed using <function>PQfreemem()</> when the result is no longer
      needed.  A terminating zero byte is not required, and should not be
      counted in <parameter>length</>.  (If a terminating zero byte is found
      before <parameter>length</> bytes are processed,
      <function>PQescapeIdentifier</> stops at the zero; the behavior is
      thus rather like <function>strncpy</>.) The
      return string has all special characters replaced so that it
      will be properly processed as an SQL identifier.  A terminating zero byte
      is also added.  The return string will also be surrounded by double
      quotes.
     </para>

     <para>
      On error, <function>PQescapeIdentifier</> returns NULL and a suitable
      message is stored in the <parameter>conn</> object.
     </para>

     <tip>
      <para>
       As with string literals, to prevent SQL injection attacks,
       SQL identifiers must be escaped when they are received from an
       untrustworthy source.
      </para>
     </tip>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQescapeStringConn</function>
      <indexterm>
       <primary>PQescapeStringConn</primary>
      </indexterm>
     </term>

     <listitem>
     <para>
3150 3151 3152 3153 3154 3155
      <synopsis>
       size_t PQescapeStringConn (PGconn *conn,
                                  char *to, const char *from, size_t length,
                                  int *error);
      </synopsis>
     </para>
3156

3157
     <para>
3158 3159 3160 3161 3162 3163
      <function>PQescapeStringConn</> escapes string literals, much like
      <function>PQescapeLiteral</>.  Unlike <function>PQescapeLiteral</>,
      the caller is responsible for providing an appropriately sized buffer.
      Furthermore, <function>PQescapeStringConn</> does not generate the
      single quotes that must surround <productname>PostgreSQL</> string
      literals; they should be provided in the SQL command that the
3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176
      result is inserted into.  The parameter <parameter>from</> points to
      the first character of the string that is to be escaped, and the
      <parameter>length</> parameter gives the number of bytes in this
      string.  A terminating zero byte is not required, and should not be
      counted in <parameter>length</>.  (If a terminating zero byte is found
      before <parameter>length</> bytes are processed,
      <function>PQescapeStringConn</> stops at the zero; the behavior is
      thus rather like <function>strncpy</>.) <parameter>to</> shall point
      to a buffer that is able to hold at least one more byte than twice
      the value of <parameter>length</>, otherwise the behavior is undefined.
      Behavior is likewise undefined if the <parameter>to</> and
      <parameter>from</> strings overlap.
     </para>
3177

3178 3179 3180 3181 3182 3183 3184 3185 3186
     <para>
      If the <parameter>error</> parameter is not NULL, then
      <literal>*error</> is set to zero on success, nonzero on error.
      Presently the only possible error conditions involve invalid multibyte
      encoding in the source string.  The output string is still generated
      on error, but it can be expected that the server will reject it as
      malformed.  On error, a suitable message is stored in the
      <parameter>conn</> object, whether or not <parameter>error</> is NULL.
     </para>
3187

3188 3189 3190 3191 3192 3193
     <para>
      <function>PQescapeStringConn</> returns the number of bytes written
      to <parameter>to</>, not including the terminating zero byte.
     </para>
     </listitem>
    </varlistentry>
3194

3195 3196 3197 3198 3199 3200 3201
    <varlistentry>
     <term>
      <function>PQescapeString</function>
      <indexterm>
       <primary>PQescapeString</primary>
      </indexterm>
     </term>
3202

3203 3204 3205 3206 3207 3208
     <listitem>
     <para>
      <synopsis>
       size_t PQescapeString (char *to, const char *from, size_t length);
      </synopsis>
     </para>
3209

3210 3211 3212 3213 3214 3215 3216 3217 3218
     <para>
      <function>PQescapeString</> is an older, deprecated version of
      <function>PQescapeStringConn</>; the difference is that it does
      not take <parameter>conn</> or <parameter>error</> parameters.
      Because of this, it cannot adjust its behavior depending on the
      connection properties (such as character encoding) and therefore
      <emphasis>it might give the wrong results</>.  Also, it has no way
      to report error conditions.
     </para>
3219

3220 3221 3222 3223 3224 3225 3226 3227 3228 3229
     <para>
      <function>PQescapeString</> can be used safely in single-threaded
      client programs that work with only one <productname>PostgreSQL</>
      connection at a time (in this case it can find out what it needs to
      know <quote>behind the scenes</>).  In other contexts it is a security
      hazard and should be avoided in favor of
      <function>PQescapeStringConn</>.
     </para>
     </listitem>
    </varlistentry>
3230

3231 3232 3233 3234 3235 3236 3237 3238 3239 3240 3241 3242 3243 3244 3245 3246 3247 3248 3249 3250
    <varlistentry>
     <term>
      <function>PQescapeByteaConn</function>
      <indexterm>
       <primary>PQescapeByteaConn</primary>
      </indexterm>
     </term>

     <listitem>
     <para>
       Escapes binary data for use within an SQL command with the type
       <type>bytea</type>.  As with <function>PQescapeStringConn</function>,
       this is only used when inserting data directly into an SQL command string.
       <synopsis>
        unsigned char *PQescapeByteaConn(PGconn *conn,
                                         const unsigned char *from,
                                         size_t from_length,
                                         size_t *to_length);
       </synopsis>
      </para>
3251

3252 3253 3254 3255 3256 3257 3258 3259 3260 3261 3262 3263
      <para>
       Certain byte values <emphasis>must</emphasis> be escaped (but all
       byte values <emphasis>can</emphasis> be escaped) when used as part
       of a <type>bytea</type> literal in an <acronym>SQL</acronym>
       statement. In general, to escape a byte, it is converted into the
       three digit octal number equal to the octet value, and preceded by
       usually two backslashes. The single quote (<literal>'</>) and backslash
       (<literal>\</>) characters have special alternative escape
       sequences. See <xref linkend="datatype-binary"> for more
       information. <function>PQescapeByteaConn</function> performs this
       operation, escaping only the minimally required bytes.
      </para>
3264

3265 3266 3267 3268 3269 3270 3271 3272 3273 3274
      <para>
       The <parameter>from</parameter> parameter points to the first
       byte of the string that is to be escaped, and the
       <parameter>from_length</parameter> parameter gives the number of
       bytes in this binary string.  (A terminating zero byte is
       neither necessary nor counted.)  The <parameter>to_length</parameter>
       parameter points to a variable that will hold the resultant
       escaped string length. This result string length includes the terminating
       zero byte of the result.
      </para>
3275

3276 3277 3278
      <para>
       <function>PQescapeByteaConn</> returns an escaped version of the
       <parameter>from</parameter> parameter binary string in memory
3279
       allocated with <function>malloc()</>.  This memory should be freed using
3280 3281 3282 3283 3284 3285 3286 3287
       <function>PQfreemem()</> when the result is no longer needed.  The
       return string has all special characters replaced so that they can
       be properly processed by the <productname>PostgreSQL</productname>
       string literal parser, and the <type>bytea</type> input function. A
       terminating zero byte is also added.  The single quotes that must
       surround <productname>PostgreSQL</productname> string literals are
       not part of the result string.
      </para>
3288

3289 3290 3291 3292 3293 3294 3295
      <para>
       On error, a NULL pointer is returned, and a suitable error message
       is stored in the <parameter>conn</> object.  Currently, the only
       possible error is insufficient memory for the result string.
      </para>
     </listitem>
    </varlistentry>
3296

3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 3311 3312 3313 3314
    <varlistentry>
     <term>
      <function>PQescapeBytea</function>
      <indexterm>
       <primary>PQescapeBytea</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       <function>PQescapeBytea</> is an older, deprecated version of
       <function>PQescapeByteaConn</>.
       <synopsis>
        unsigned char *PQescapeBytea(const unsigned char *from,
                                     size_t from_length,
                                     size_t *to_length);
       </synopsis>
      </para>
3315

3316 3317 3318 3319 3320 3321 3322 3323 3324
      <para>
       The only difference from <function>PQescapeByteaConn</> is that
       <function>PQescapeBytea</> does not take a <structname>PGconn</>
       parameter.  Because of this, it cannot adjust its behavior
       depending on the connection properties (in particular, whether
       standard-conforming strings are enabled) and therefore
       <emphasis>it might give the wrong results</>.  Also, it has no
       way to return an error message on failure.
      </para>
3325

3326 3327 3328 3329 3330 3331 3332 3333 3334 3335
      <para>
       <function>PQescapeBytea</> can be used safely in single-threaded
       client programs that work with only one <productname>PostgreSQL</>
       connection at a time (in this case it can find out what it needs
       to know <quote>behind the scenes</>).  In other contexts it is
       a security hazard and should be avoided in favor of
       <function>PQescapeByteaConn</>.
      </para>
     </listitem>
    </varlistentry>
3336

3337 3338 3339 3340 3341 3342 3343 3344 3345 3346 3347 3348 3349 3350
    <varlistentry>
     <term>
      <function>PQunescapeBytea</function>
      <indexterm>
       <primary>PQunescapeBytea</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Converts a string representation of binary data into binary data
       &mdash; the reverse of <function>PQescapeBytea</function>.  This
       is needed when retrieving <type>bytea</type> data in text format,
       but not when retrieving it in binary format.
3351

3352 3353 3354 3355
       <synopsis>
        unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length);
       </synopsis>
      </para>
3356

3357 3358 3359 3360 3361 3362 3363 3364 3365 3366
      <para>
       The <parameter>from</parameter> parameter points to a string
       such as might be returned by <function>PQgetvalue</function> when applied
       to a <type>bytea</type> column. <function>PQunescapeBytea</function>
       converts this string representation into its binary representation.
       It returns a pointer to a buffer allocated with
       <function>malloc()</function>, or null on error, and puts the size of
       the buffer in <parameter>to_length</parameter>. The result must be
       freed using <function>PQfreemem</> when it is no longer needed.
      </para>
3367

3368 3369 3370 3371 3372 3373 3374 3375 3376 3377
      <para>
       This conversion is not exactly the inverse of
       <function>PQescapeBytea</function>, because the string is not expected
       to be <quote>escaped</> when received from <function>PQgetvalue</function>.
       In particular this means there is no need for string quoting considerations,
       and so no need for a <structname>PGconn</> parameter.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
3378

3379 3380 3381
  </sect2>

 </sect1>
3382

3383 3384
 <sect1 id="libpq-async">
  <title>Asynchronous Command Processing</title>
3385

3386 3387 3388
  <indexterm zone="libpq-async">
   <primary>nonblocking connection</primary>
  </indexterm>
3389

3390 3391 3392 3393
  <para>
   The <function>PQexec</function> function is adequate for submitting
   commands in normal, synchronous applications.  It has a couple of
   deficiencies, however, that can be of importance to some users:
3394

3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412
   <itemizedlist>
    <listitem>
     <para>
      <function>PQexec</function> waits for the command to be completed.
      The application might have other work to do (such as maintaining a
      user interface), in which case it won't want to block waiting for
      the response.
     </para>
    </listitem>

    <listitem>
     <para>
      Since the execution of the client application is suspended while it
      waits for the result, it is hard for the application to decide that
      it would like to try to cancel the ongoing command.  (It can be done
      from a signal handler, but not otherwise.)
     </para>
    </listitem>
3413

3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424
    <listitem>
     <para>
      <function>PQexec</function> can return only one
      <structname>PGresult</structname> structure.  If the submitted command
      string contains multiple <acronym>SQL</acronym> commands, all but
      the last <structname>PGresult</structname> are discarded by
      <function>PQexec</function>.
     </para>
    </listitem>
   </itemizedlist>
  </para>
3425

3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443
  <para>
   Applications that do not like these limitations can instead use the
   underlying functions that <function>PQexec</function> is built from:
   <function>PQsendQuery</function> and <function>PQgetResult</function>.
   There are also
   <function>PQsendQueryParams</function>,
   <function>PQsendPrepare</function>,
   <function>PQsendQueryPrepared</function>,
   <function>PQsendDescribePrepared</function>, and
   <function>PQsendDescribePortal</function>,
   which can be used with <function>PQgetResult</function> to duplicate
   the functionality of
   <function>PQexecParams</function>,
   <function>PQprepare</function>,
   <function>PQexecPrepared</function>,
   <function>PQdescribePrepared</function>, and
   <function>PQdescribePortal</function>
   respectively.
3444

3445 3446 3447 3448 3449 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462
   <variablelist>
    <varlistentry>
     <term>
      <function>PQsendQuery</function>
      <indexterm>
       <primary>PQsendQuery</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Submits a command to the server without waiting for the result(s).
       1 is returned if the command was successfully dispatched and 0 if
       not (in which case, use <function>PQerrorMessage</> to get more
       information about the failure).
       <synopsis>
        int PQsendQuery(PGconn *conn, const char *command);
       </synopsis>
3463

3464 3465 3466 3467 3468 3469 3470 3471
       After successfully calling <function>PQsendQuery</function>, call
       <function>PQgetResult</function> one or more times to obtain the
       results.  <function>PQsendQuery</function> cannot be called again
       (on the same connection) until <function>PQgetResult</function>
       has returned a null pointer, indicating that the command is done.
      </para>
     </listitem>
    </varlistentry>
3472

3473 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494
    <varlistentry>
     <term>
      <function>PQsendQueryParams</function>
      <indexterm>
       <primary>PQsendQueryParams</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Submits a command and separate parameters to the server without
       waiting for the result(s).
       <synopsis>
        int PQsendQueryParams(PGconn *conn,
                              const char *command,
                              int nParams,
                              const Oid *paramTypes,
                              const char * const *paramValues,
                              const int *paramLengths,
                              const int *paramFormats,
                              int resultFormat);
       </synopsis>
3495

3496 3497 3498 3499 3500 3501 3502 3503 3504
       This is equivalent to <function>PQsendQuery</function> except that
       query parameters can be specified separately from the query string.
       The function's parameters are handled identically to
       <function>PQexecParams</function>.  Like
       <function>PQexecParams</function>, it will not work on 2.0-protocol
       connections, and it allows only one command in the query string.
      </para>
     </listitem>
    </varlistentry>
3505

3506 3507 3508 3509 3510 3511 3512 3513 3514 3515 3516 3517 3518 3519 3520 3521 3522 3523 3524
    <varlistentry>
     <term>
      <function>PQsendPrepare</>
      <indexterm>
       <primary>PQsendPrepare</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Sends a request to create a prepared statement with the given
       parameters, without waiting for completion.
       <synopsis>
        int PQsendPrepare(PGconn *conn,
                          const char *stmtName,
                          const char *query,
                          int nParams,
                          const Oid *paramTypes);
       </synopsis>
3525

3526 3527 3528 3529 3530 3531 3532 3533 3534 3535 3536
       This is an asynchronous version of <function>PQprepare</>: it
       returns 1 if it was able to dispatch the request, and 0 if not.
       After a successful call, call <function>PQgetResult</function> to
       determine whether the server successfully created the prepared
       statement.  The function's parameters are handled identically to
       <function>PQprepare</function>.  Like
       <function>PQprepare</function>, it will not work on 2.0-protocol
       connections.
      </para>
     </listitem>
    </varlistentry>
3537

3538 3539 3540 3541 3542 3543 3544 3545 3546 3547 3548 3549 3550 3551 3552 3553 3554 3555 3556 3557 3558
    <varlistentry>
     <term>
      <function>PQsendQueryPrepared</function>
      <indexterm>
       <primary>PQsendQueryPrepared</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Sends a request to execute a prepared statement with given
       parameters, without waiting for the result(s).
       <synopsis>
        int PQsendQueryPrepared(PGconn *conn,
                                const char *stmtName,
                                int nParams,
                                const char * const *paramValues,
                                const int *paramLengths,
                                const int *paramFormats,
                                int resultFormat);
       </synopsis>
3559

3560 3561 3562 3563 3564 3565 3566 3567 3568 3569
       This is similar to <function>PQsendQueryParams</function>, but
       the command to be executed is specified by naming a
       previously-prepared statement, instead of giving a query string.
       The function's parameters are handled identically to
       <function>PQexecPrepared</function>.  Like
       <function>PQexecPrepared</function>, it will not work on
       2.0-protocol connections.
      </para>
     </listitem>
    </varlistentry>
3570

3571 3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585
    <varlistentry>
     <term>
      <function>PQsendDescribePrepared</>
      <indexterm>
       <primary>PQsendDescribePrepared</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Submits a request to obtain information about the specified
       prepared statement, without waiting for completion.
       <synopsis>
        int PQsendDescribePrepared(PGconn *conn, const char *stmtName);
       </synopsis>
3586

3587 3588 3589 3590 3591 3592 3593 3594 3595 3596
       This is an asynchronous version of <function>PQdescribePrepared</>:
       it returns 1 if it was able to dispatch the request, and 0 if not.
       After a successful call, call <function>PQgetResult</function> to
       obtain the results.  The function's parameters are handled
       identically to <function>PQdescribePrepared</function>.  Like
       <function>PQdescribePrepared</function>, it will not work on
       2.0-protocol connections.
      </para>
     </listitem>
    </varlistentry>
3597

3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 3608 3609 3610 3611 3612
    <varlistentry>
     <term>
      <function>PQsendDescribePortal</>
      <indexterm>
       <primary>PQsendDescribePortal</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Submits a request to obtain information about the specified
       portal, without waiting for completion.
       <synopsis>
        int PQsendDescribePortal(PGconn *conn, const char *portalName);
       </synopsis>
3613

3614 3615 3616 3617 3618 3619 3620 3621 3622 3623
       This is an asynchronous version of <function>PQdescribePortal</>:
       it returns 1 if it was able to dispatch the request, and 0 if not.
       After a successful call, call <function>PQgetResult</function> to
       obtain the results.  The function's parameters are handled
       identically to <function>PQdescribePortal</function>.  Like
       <function>PQdescribePortal</function>, it will not work on
       2.0-protocol connections.
      </para>
     </listitem>
    </varlistentry>
3624

3625 3626 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645
    <varlistentry>
     <term>
      <function>PQgetResult</function>
      <indexterm>
       <primary>PQgetResult</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Waits for the next result from a prior
       <function>PQsendQuery</function>,
       <function>PQsendQueryParams</function>,
       <function>PQsendPrepare</function>, or
       <function>PQsendQueryPrepared</function> call, and returns it.
       A null pointer is returned when the command is complete and there
       will be no more results.
       <synopsis>
        PGresult *PQgetResult(PGconn *conn);
       </synopsis>
      </para>
3646

3647 3648 3649 3650 3651 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 3664
      <para>
       <function>PQgetResult</function> must be called repeatedly until
       it returns a null pointer, indicating that the command is done.
       (If called when no command is active,
       <function>PQgetResult</function> will just return a null pointer
       at once.) Each non-null result from
       <function>PQgetResult</function> should be processed using the
       same <structname>PGresult</> accessor functions previously
       described.  Don't forget to free each result object with
       <function>PQclear</function> when done with it.  Note that
       <function>PQgetResult</function> will block only if a command is
       active and the necessary response data has not yet been read by
       <function>PQconsumeInput</function>.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
3665

3666 3667 3668 3669 3670 3671 3672 3673 3674 3675 3676 3677
  <para>
   Using <function>PQsendQuery</function> and
   <function>PQgetResult</function> solves one of
   <function>PQexec</function>'s problems:  If a command string contains
   multiple <acronym>SQL</acronym> commands, the results of those commands
   can be obtained individually.  (This allows a simple form of overlapped
   processing, by the way: the client can be handling the results of one
   command while the server is still working on later queries in the same
   command string.)  However, calling <function>PQgetResult</function>
   will still cause the client to block until the server completes the
   next <acronym>SQL</acronym> command.  This can be avoided by proper
   use of two more functions:
3678

3679 3680 3681 3682 3683 3684 3685 3686 3687 3688 3689 3690 3691 3692 3693 3694
   <variablelist>
    <varlistentry>
     <term>
      <function>PQconsumeInput</function>
      <indexterm>
       <primary>PQconsumeInput</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       If input is available from the server, consume it.
       <synopsis>
        int PQconsumeInput(PGconn *conn);
       </synopsis>
      </para>
3695

3696 3697 3698 3699 3700 3701 3702 3703 3704 3705 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718
      <para>
       <function>PQconsumeInput</function> normally returns 1 indicating
       <quote>no error</quote>, but returns 0 if there was some kind of
       trouble (in which case <function>PQerrorMessage</function> can be
       consulted).  Note that the result does not say whether any input
       data was actually collected. After calling
       <function>PQconsumeInput</function>, the application can check
       <function>PQisBusy</function> and/or
       <function>PQnotifies</function> to see if their state has changed.
      </para>

      <para>
       <function>PQconsumeInput</function> can be called even if the
       application is not prepared to deal with a result or notification
       just yet.  The function will read available data and save it in
       a buffer, thereby causing a <function>select()</function>
       read-ready indication to go away.  The application can thus use
       <function>PQconsumeInput</function> to clear the
       <function>select()</function> condition immediately, and then
       examine the results at leisure.
      </para>
     </listitem>
    </varlistentry>
3719

3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 3732 3733 3734 3735 3736 3737
    <varlistentry>
     <term>
      <function>PQisBusy</function>
      <indexterm>
       <primary>PQisBusy</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns 1 if a command is busy, that is,
       <function>PQgetResult</function> would block waiting for input.
       A 0 return indicates that <function>PQgetResult</function> can be
       called with assurance of not blocking.
       <synopsis>
        int PQisBusy(PGconn *conn);
       </synopsis>
      </para>
3738

3739 3740 3741 3742 3743 3744 3745 3746 3747
      <para>
       <function>PQisBusy</function> will not itself attempt to read data
       from the server; therefore <function>PQconsumeInput</function>
       must be invoked first, or the busy state will never end.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
3748

3749 3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763
  <para>
   A typical application using these functions will have a main loop that
   uses <function>select()</function> or <function>poll()</> to wait for
   all the conditions that it must respond to.  One of the conditions
   will be input available from the server, which in terms of
   <function>select()</function> means readable data on the file
   descriptor identified by <function>PQsocket</function>.  When the main
   loop detects input ready, it should call
   <function>PQconsumeInput</function> to read the input.  It can then
   call <function>PQisBusy</function>, followed by
   <function>PQgetResult</function> if <function>PQisBusy</function>
   returns false (0).  It can also call <function>PQnotifies</function>
   to detect <command>NOTIFY</> messages (see <xref
   linkend="libpq-notify">).
  </para>
3764

3765 3766 3767 3768 3769 3770 3771 3772 3773 3774 3775
  <para>
   A client that uses
   <function>PQsendQuery</function>/<function>PQgetResult</function>
   can also attempt to cancel a command that is still being processed
   by the server; see <xref linkend="libpq-cancel">.  But regardless of
   the return value of <function>PQcancel</function>, the application
   must continue with the normal result-reading sequence using
   <function>PQgetResult</function>.  A successful cancellation will
   simply cause the command to terminate sooner than it would have
   otherwise.
  </para>
3776

3777 3778 3779 3780 3781 3782 3783 3784 3785 3786
  <para>
   By using the functions described above, it is possible to avoid
   blocking while waiting for input from the database server.  However,
   it is still possible that the application will block waiting to send
   output to the server.  This is relatively uncommon but can happen if
   very long SQL commands or data values are sent.  (It is much more
   probable if the application sends data via <command>COPY IN</command>,
   however.)  To prevent this possibility and achieve completely
   nonblocking database operation, the following additional functions
   can be used.
3787

3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803
   <variablelist>
    <varlistentry>
     <term>
      <function>PQsetnonblocking</function>
      <indexterm>
       <primary>PQsetnonblocking</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Sets the nonblocking status of the connection.
       <synopsis>
        int PQsetnonblocking(PGconn *conn, int arg);
       </synopsis>
      </para>
3804

3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 3815 3816 3817 3818 3819 3820 3821 3822 3823 3824
      <para>
       Sets the state of the connection to nonblocking if
       <parameter>arg</parameter> is 1, or blocking if
       <parameter>arg</parameter> is 0.  Returns 0 if OK, -1 if error.
      </para>

      <para>
       In the nonblocking state, calls to
       <function>PQsendQuery</function>, <function>PQputline</function>,
       <function>PQputnbytes</function>, and
       <function>PQendcopy</function> will not block but instead return
       an error if they need to be called again.
      </para>

      <para>
       Note that <function>PQexec</function> does not honor nonblocking
       mode; if it is called, it will act in blocking fashion anyway.
      </para>
     </listitem>
    </varlistentry>
3825

3826 3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840
    <varlistentry>
     <term>
      <function>PQisnonblocking</function>
      <indexterm>
       <primary>PQisnonblocking</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns the blocking status of the database connection.
       <synopsis>
        int PQisnonblocking(const PGconn *conn);
       </synopsis>
      </para>
3841

3842 3843 3844 3845 3846 3847
      <para>
       Returns 1 if the connection is set to nonblocking mode and 0 if
       blocking.
      </para>
     </listitem>
    </varlistentry>
3848

3849 3850 3851 3852 3853 3854 3855 3856 3857 3858 3859 3860 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871
    <varlistentry>
     <term>
      <function>PQflush</function>
       <indexterm>
        <primary>PQflush</primary>
       </indexterm>
      </term>

      <listitem>
       <para>
       Attempts to flush any queued output data to the server.  Returns
       0 if successful (or if the send queue is empty), -1 if it failed
       for some reason, or 1 if it was unable to send all the data in
       the send queue yet (this case can only occur if the connection
       is nonblocking).
       <synopsis>
        int PQflush(PGconn *conn);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
3872

3873 3874 3875 3876 3877 3878 3879
  <para>
   After sending any command or data on a nonblocking connection, call
   <function>PQflush</function>.  If it returns 1, wait for the socket
   to be write-ready and call it again; repeat until it returns 0.  Once
   <function>PQflush</function> returns 0, wait for the socket to be
   read-ready and then read the response as described above.
  </para>
3880

3881
 </sect1>
3882

3883 3884
 <sect1 id="libpq-cancel">
  <title>Cancelling Queries in Progress</title>
3885

3886 3887 3888
  <indexterm zone="libpq-cancel">
   <primary>canceling</primary>
   <secondary>SQL command</secondary>
3889
  </indexterm>
3890

3891 3892 3893 3894
  <para>
   A client application can request cancellation of a command that is
   still being processed by the server, using the functions described in
   this section.
3895

3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912
   <variablelist>
    <varlistentry>
     <term>
      <function>PQgetCancel</function>
      <indexterm>
       <primary>PQgetCancel</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Creates a data structure containing the information needed to cancel
       a command issued through a particular database connection.
       <synopsis>
        PGcancel *PQgetCancel(PGconn *conn);
       </synopsis>
      </para>
3913

3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 3932 3933 3934 3935 3936 3937 3938 3939 3940 3941
      <para>
       <function>PQgetCancel</function> creates a
       <structname>PGcancel</><indexterm><primary>PGcancel</></> object
       given a <structname>PGconn</> connection object.  It will return
       NULL if the given <parameter>conn</> is NULL or an invalid
       connection.  The <structname>PGcancel</> object is an opaque
       structure that is not meant to be accessed directly by the
       application; it can only be passed to <function>PQcancel</function>
       or <function>PQfreeCancel</function>.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQfreeCancel</function>
      <indexterm>
       <primary>PQfreeCancel</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Frees a data structure created by <function>PQgetCancel</function>.
       <synopsis>
        void PQfreeCancel(PGcancel *cancel);
       </synopsis>
      </para>
3942

3943 3944 3945 3946 3947 3948
      <para>
       <function>PQfreeCancel</function> frees a data object previously created
       by <function>PQgetCancel</function>.
      </para>
     </listitem>
    </varlistentry>
3949

3950 3951 3952 3953 3954 3955 3956 3957 3958 3959 3960 3961 3962 3963 3964
    <varlistentry>
     <term>
      <function>PQcancel</function>
      <indexterm>
       <primary>PQcancel</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Requests that the server abandon processing of the current command.
       <synopsis>
        int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize);
       </synopsis>
      </para>
3965

3966 3967 3968 3969 3970 3971 3972
      <para>
       The return value is 1 if the cancel request was successfully
       dispatched and 0 if not.  If not, <parameter>errbuf</> is filled
       with an error message explaining why not.  <parameter>errbuf</>
       must be a char array of size <parameter>errbufsize</> (the
       recommended size is 256 bytes).
      </para>
3973

3974 3975 3976 3977 3978 3979 3980 3981
      <para>
       Successful dispatch is no guarantee that the request will have
       any effect, however.  If the cancellation is effective, the current
       command will terminate early and return an error result.  If the
       cancellation fails (say, because the server was already done
       processing the command), then there will be no visible result at
       all.
      </para>
3982

3983 3984 3985 3986 3987 3988 3989 3990 3991 3992 3993
      <para>
       <function>PQcancel</function> can safely be invoked from a signal
       handler, if the <parameter>errbuf</> is a local variable in the
       signal handler.  The <structname>PGcancel</> object is read-only
       as far as <function>PQcancel</function> is concerned, so it can
       also be invoked from a thread that is separate from the one
       manipulating the <structname>PGconn</> object.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
3994

3995 3996 3997 3998 3999 4000 4001 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011
   <variablelist>
    <varlistentry>
     <term>
      <function>PQrequestCancel</function>
      <indexterm>
       <primary>PQrequestCancel</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Requests that the server abandon processing of the current
       command.
       <synopsis>
        int PQrequestCancel(PGconn *conn);
       </synopsis>
      </para>
4012

4013 4014 4015 4016 4017 4018 4019 4020 4021 4022 4023 4024 4025 4026 4027
      <para>
       <function>PQrequestCancel</function> is a deprecated variant of
       <function>PQcancel</function>.  It operates directly on the
       <structname>PGconn</> object, and in case of failure stores the
       error message in the <structname>PGconn</> object (whence it can
       be retrieved by <function>PQerrorMessage</function>).  Although
       the functionality is the same, this approach creates hazards for
       multiple-thread programs and signal handlers, since it is possible
       that overwriting the <structname>PGconn</>'s error message will
       mess up the operation currently in progress on the connection.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
4028

4029
 </sect1>
4030

4031 4032
 <sect1 id="libpq-fastpath">
  <title>The Fast-Path Interface</title>
4033

4034 4035 4036
  <indexterm zone="libpq-fastpath">
   <primary>fast path</primary>
  </indexterm>
4037

4038 4039 4040 4041
  <para>
   <productname>PostgreSQL</productname> provides a fast-path interface
   to send simple function calls to the server.
  </para>
4042

4043 4044 4045 4046 4047 4048 4049 4050 4051
  <tip>
   <para>
    This interface is somewhat obsolete, as one can achieve similar
    performance and greater functionality by setting up a prepared
    statement to define the function call.  Then, executing the statement
    with binary transmission of parameters and results substitutes for a
    fast-path function call.
   </para>
  </tip>
4052

4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063
  <para>
   The function <function>PQfn</function><indexterm><primary>PQfn</></>
   requests execution of a server function via the fast-path interface:
   <synopsis>
    PGresult *PQfn(PGconn *conn,
                   int fnid,
                   int *result_buf,
                   int *result_len,
                   int result_is_int,
                   const PQArgBlock *args,
                   int nargs);
4064

4065 4066 4067 4068 4069 4070 4071 4072 4073 4074
    typedef struct {
        int len;
        int isint;
        union {
            int *ptr;
            int integer;
        } u;
    } PQArgBlock;
   </synopsis>
  </para>
4075

4076 4077 4078 4079 4080 4081 4082 4083 4084 4085 4086 4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098
  <para>
   The <parameter>fnid</> argument is the OID of the function to be
   executed.  <parameter>args</> and <parameter>nargs</> define the
   parameters to be passed to the function; they must match the declared
   function argument list.  When the <parameter>isint</> field of a
   parameter structure is true, the <parameter>u.integer</> value is sent
   to the server as an integer of the indicated length (this must be 1,
   2, or 4 bytes); proper byte-swapping occurs.  When <parameter>isint</>
   is false, the indicated number of bytes at <parameter>*u.ptr</> are
   sent with no processing; the data must be in the format expected by
   the server for binary transmission of the function's argument data
   type.  <parameter>result_buf</parameter> is the buffer in which to
   place the return value.  The caller must  have  allocated sufficient
   space to store the return value.  (There is no check!) The actual result
   length will be returned in the integer pointed to  by
   <parameter>result_len</parameter>.  If a 1, 2, or 4-byte integer result
   is expected, set <parameter>result_is_int</parameter> to 1, otherwise
   set it to 0.  Setting <parameter>result_is_int</parameter> to 1 causes
   <application>libpq</> to byte-swap the value if necessary, so that it
   is delivered as a proper <type>int</type> value for the client machine.
   When <parameter>result_is_int</> is 0, the binary-format byte string
   sent by the server is returned unmodified.
  </para>
4099

4100 4101 4102 4103 4104 4105 4106
  <para>
   <function>PQfn</function> always returns a valid
   <structname>PGresult</structname> pointer. The result status should be
   checked before the result is used.   The caller is responsible for
   freeing  the  <structname>PGresult</structname>  with
   <function>PQclear</function> when it is no longer needed.
  </para>
4107

4108 4109 4110 4111
  <para>
   Note that it is not possible to handle null arguments, null results,
   nor set-valued results when using this interface.
  </para>
4112

4113
 </sect1>
4114

4115 4116
 <sect1 id="libpq-notify">
  <title>Asynchronous Notification</title>
4117

4118 4119 4120 4121
  <indexterm zone="libpq-notify">
   <primary>NOTIFY</primary>
   <secondary>in libpq</secondary>
  </indexterm>
4122

4123 4124 4125 4126
  <para>
   <productname>PostgreSQL</productname> offers asynchronous notification
   via the <command>LISTEN</command> and <command>NOTIFY</command>
   commands.  A client session registers its interest in a particular
4127
   notification channel with the <command>LISTEN</command> command (and
4128
   can stop listening with the <command>UNLISTEN</command> command).  All
4129
   sessions listening on a particular channel will be notified
4130
   asynchronously when a <command>NOTIFY</command> command with that
4131 4132
   channel name is executed by any session. A <quote>payload</> string can
   be passed to communicate additional data to the listeners.
4133
  </para>
4134

4135 4136
  <para>
   <application>libpq</application> applications submit
4137 4138
   <command>LISTEN</command>, <command>UNLISTEN</command>,
   and <command>NOTIFY</command> commands as
4139 4140 4141 4142
   ordinary SQL commands.  The arrival of <command>NOTIFY</command>
   messages can subsequently be detected by calling
   <function>PQnotifies</function>.<indexterm><primary>PQnotifies</></>
  </para>
4143

4144
  <para>
4145 4146 4147 4148 4149 4150
   The function <function>PQnotifies</function> returns the next notification
   from a list of unhandled notification messages received from the server.
   It returns a null pointer if there are no pending notifications.  Once a
   notification is returned from <function>PQnotifies</>, it is considered
   handled and will be removed from the list of notifications.

4151 4152
   <synopsis>
   PGnotify *PQnotifies(PGconn *conn);
4153

4154
   typedef struct pgNotify {
4155
       char *relname;              /* notification channel name */
4156
       int  be_pid;                /* process ID of notifying server process */
4157
       char *extra;                /* notification payload string */
4158 4159
   } PGnotify;
   </synopsis>
4160

4161 4162 4163 4164 4165
   After processing a <structname>PGnotify</structname> object returned
   by <function>PQnotifies</function>, be sure to free it with
   <function>PQfreemem</function>.  It is sufficient to free the
   <structname>PGnotify</structname> pointer; the
   <structfield>relname</structfield> and <structfield>extra</structfield>
4166 4167 4168
   fields do not represent separate allocations.  (The names of these fields
   are historical; in particular, channel names need not have anything to
   do with relation names.)
4169
  </para>
4170

4171 4172 4173 4174
  <para>
   <xref linkend="libpq-example-2"> gives a sample program that illustrates
   the use of asynchronous notification.
  </para>
4175

4176 4177 4178 4179 4180 4181 4182 4183 4184 4185
  <para>
   <function>PQnotifies</function> does not actually read data from the
   server; it just returns messages previously absorbed by another
   <application>libpq</application> function.  In prior releases of
   <application>libpq</application>, the only way to ensure timely receipt
   of <command>NOTIFY</> messages was to constantly submit commands, even
   empty ones, and then check <function>PQnotifies</function> after each
   <function>PQexec</function>.  While this still works, it is deprecated
   as a waste of processing power.
  </para>
4186

4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202
  <para>
   A better way to check for <command>NOTIFY</> messages when you have no
   useful commands to execute is to call
   <function>PQconsumeInput</function>, then check
   <function>PQnotifies</function>.  You can use
   <function>select()</function> to wait for data to arrive from the
   server, thereby using no <acronym>CPU</acronym> power unless there is
   something to do.  (See <function>PQsocket</function> to obtain the file
   descriptor number to use with <function>select()</function>.) Note that
   this will work OK whether you submit commands with
   <function>PQsendQuery</function>/<function>PQgetResult</function> or
   simply use <function>PQexec</function>.  You should, however, remember
   to check <function>PQnotifies</function> after each
   <function>PQgetResult</function> or <function>PQexec</function>, to
   see if any notifications came in during the processing of the command.
  </para>
4203

4204
 </sect1>
4205

4206 4207
 <sect1 id="libpq-copy">
  <title>Functions Associated with the <command>COPY</command> Command</title>
4208

4209 4210 4211 4212
  <indexterm zone="libpq-copy">
   <primary>COPY</primary>
   <secondary>with libpq</secondary>
  </indexterm>
4213

4214 4215 4216 4217 4218 4219 4220
  <para>
   The <command>COPY</command> command in
   <productname>PostgreSQL</productname> has options to read from or write
   to the network connection used by <application>libpq</application>.
   The functions described in this section allow applications to take
   advantage of this capability by supplying or consuming copied data.
  </para>
4221

4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239
  <para>
   The overall process is that the application first issues the SQL
   <command>COPY</command> command via <function>PQexec</function> or one
   of the equivalent functions.  The response to this (if there is no
   error in the command) will be a <structname>PGresult</> object bearing
   a status code of <literal>PGRES_COPY_OUT</literal> or
   <literal>PGRES_COPY_IN</literal> (depending on the specified copy
   direction).  The application should then use the functions of this
   section to receive or transmit data rows.  When the data transfer is
   complete, another <structname>PGresult</> object is returned to indicate
   success or failure of the transfer.  Its status will be
   <literal>PGRES_COMMAND_OK</literal> for success or
   <literal>PGRES_FATAL_ERROR</literal> if some problem was encountered.
   At this point further SQL commands can be issued via
   <function>PQexec</function>.  (It is not possible to execute other SQL
   commands using the same connection while the <command>COPY</command>
   operation is in progress.)
  </para>
4240

4241 4242 4243 4244 4245 4246 4247 4248 4249
  <para>
   If a <command>COPY</command> command is issued via
   <function>PQexec</function> in a string that could contain additional
   commands, the application must continue fetching results via
   <function>PQgetResult</> after completing the <command>COPY</command>
   sequence.  Only when <function>PQgetResult</> returns
   <symbol>NULL</symbol> is it certain that the <function>PQexec</function>
   command string is done and it is safe to issue more commands.
  </para>
4250

4251 4252 4253 4254 4255 4256
  <para>
   The functions of this section should be executed only after obtaining
   a result status of <literal>PGRES_COPY_OUT</literal> or
   <literal>PGRES_COPY_IN</literal> from <function>PQexec</function> or
   <function>PQgetResult</function>.
  </para>
4257

4258 4259 4260 4261 4262
  <para>
   A <structname>PGresult</> object bearing one of these status values
   carries some additional data about the <command>COPY</command> operation
   that is starting.  This additional data is available using functions
   that are also used in connection with query results:
4263

4264 4265 4266 4267 4268 4269 4270 4271 4272 4273 4274 4275 4276 4277 4278 4279
   <variablelist>
    <varlistentry>
     <term>
      <function>PQnfields</function>
      <indexterm>
       <primary>PQnfields</primary>
       <secondary>with COPY</secondary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns the number of columns (fields) to be copied.
      </para>
     </listitem>
    </varlistentry>
4280

4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294
    <varlistentry>
     <term>
      <function>PQbinaryTuples</function>
      <indexterm>
       <primary>PQbinaryTuples</primary>
       <secondary>with COPY</secondary>
      </indexterm>
     </term>

     <listitem>
      <para>
       0 indicates the overall copy format is textual (rows separated by
       newlines, columns separated by separator characters, etc).  1
       indicates the overall copy format is binary.  See <xref
4295
       linkend="sql-copy"> for more information.
4296 4297 4298
      </para>
     </listitem>
    </varlistentry>
4299

4300 4301 4302 4303 4304 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322
    <varlistentry>
     <term>
      <function>PQfformat</function>
      <indexterm>
       <primary>PQfformat</primary>
       <secondary>with COPY</secondary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Returns the format code (0 for text, 1 for binary) associated with
       each column of the copy operation.  The per-column format codes
       will always be zero when the overall copy format is textual, but
       the binary format can support both text and binary columns.
       (However, as of the current implementation of <command>COPY</>,
       only binary columns appear in a binary copy; so the per-column
       formats always match the overall format at present.)
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
4323

4324 4325 4326 4327 4328 4329
  <note>
   <para>
    These additional data values are only available when using protocol
    3.0.  When using protocol 2.0, all these functions will return 0.
   </para>
  </note>
4330

4331 4332
  <sect2 id="libpq-copy-send">
   <title>Functions for Sending <command>COPY</command> Data</title>
4333

4334 4335 4336 4337 4338
   <para>
    These functions are used to send data during <literal>COPY FROM
    STDIN</>.  They will fail if called when the connection is not in
    <literal>COPY_IN</> state.
   </para>
4339

4340 4341 4342 4343 4344 4345 4346 4347 4348 4349 4350 4351 4352 4353 4354 4355 4356 4357
   <variablelist>
    <varlistentry>
     <term>
      <function>PQputCopyData</function>
      <indexterm>
       <primary>PQputCopyData</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Sends data to the server during <literal>COPY_IN</> state.
       <synopsis>
        int PQputCopyData(PGconn *conn,
                          const char *buffer,
                          int nbytes);
       </synopsis>
      </para>
4358

4359 4360 4361 4362 4363 4364 4365 4366 4367 4368
      <para>
       Transmits the <command>COPY</command> data in the specified
       <parameter>buffer</>, of length <parameter>nbytes</>, to the server.
       The result is 1 if the data was sent, zero if it was not sent
       because the attempt would block (this case is only possible if the
       connection is in nonblocking mode), or -1 if an error occurred.
       (Use <function>PQerrorMessage</function> to retrieve details if
       the return value is -1.  If the value is zero, wait for write-ready
       and try again.)
      </para>
4369

4370 4371 4372 4373 4374
      <para>
       The application can divide the <command>COPY</command> data stream
       into buffer loads of any convenient size.  Buffer-load boundaries
       have no semantic significance when sending.  The contents of the
       data stream must match the data format expected by the
4375
       <command>COPY</> command; see <xref linkend="sql-copy"> for details.
4376 4377 4378
      </para>
     </listitem>
    </varlistentry>
4379

4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 4391 4392 4393 4394 4395
    <varlistentry>
     <term>
      <function>PQputCopyEnd</function>
      <indexterm>
       <primary>PQputCopyEnd</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Sends end-of-data indication to the server during <literal>COPY_IN</> state.
       <synopsis>
        int PQputCopyEnd(PGconn *conn,
                         const char *errormsg);
       </synopsis>
      </para>
4396

4397 4398 4399 4400 4401 4402 4403 4404 4405 4406 4407 4408
      <para>
       Ends the <literal>COPY_IN</> operation successfully if
       <parameter>errormsg</> is <symbol>NULL</symbol>.  If
       <parameter>errormsg</> is not <symbol>NULL</symbol> then the
       <command>COPY</> is forced to fail, with the string pointed to by
       <parameter>errormsg</> used as the error message.  (One should not
       assume that this exact error message will come back from the server,
       however, as the server might have already failed the
       <command>COPY</> for its own reasons.  Also note that the option
       to force failure does not work when using pre-3.0-protocol
       connections.)
      </para>
4409

4410 4411 4412 4413 4414 4415 4416 4417
      <para>
       The result is 1 if the termination data was sent, zero if it was
       not sent because the attempt would block (this case is only possible
       if the connection is in nonblocking mode), or -1 if an error
       occurred.  (Use <function>PQerrorMessage</function> to retrieve
       details if the return value is -1.  If the value is zero, wait for
       write-ready and try again.)
      </para>
4418

4419 4420 4421 4422 4423 4424 4425 4426 4427
      <para>
       After successfully calling <function>PQputCopyEnd</>, call
       <function>PQgetResult</> to obtain the final result status of the
       <command>COPY</> command.  One can wait for this result to be
       available in the usual way.  Then return to normal operation.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
4428

4429
  </sect2>
4430

4431 4432
  <sect2 id="libpq-copy-receive">
   <title>Functions for Receiving <command>COPY</command> Data</title>
4433

4434 4435 4436 4437 4438
   <para>
    These functions are used to receive data during <literal>COPY TO
    STDOUT</>.  They will fail if called when the connection is not in
    <literal>COPY_OUT</> state.
   </para>
4439

4440 4441 4442 4443 4444 4445 4446 4447 4448 4449 4450 4451 4452 4453 4454 4455 4456 4457
   <variablelist>
    <varlistentry>
     <term>
      <function>PQgetCopyData</function>
      <indexterm>
       <primary>PQgetCopyData</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Receives data from the server during <literal>COPY_OUT</> state.
       <synopsis>
        int PQgetCopyData(PGconn *conn,
                          char **buffer,
                          int async);
       </synopsis>
      </para>
4458

4459 4460 4461 4462 4463 4464 4465 4466 4467
      <para>
       Attempts to obtain another row of data from the server during a
       <command>COPY</command>.  Data is always returned one data row at
       a time; if only a partial row is available, it is not returned.
       Successful return of a data row involves allocating a chunk of
       memory to hold the data.  The <parameter>buffer</> parameter must
       be non-<symbol>NULL</symbol>.  <parameter>*buffer</> is set to
       point to the allocated memory, or to <symbol>NULL</symbol> in cases
       where no buffer is returned.  A non-<symbol>NULL</symbol> result
4468
       buffer should be freed using <function>PQfreemem</> when no longer
4469 4470
       needed.
      </para>
4471

4472 4473 4474 4475 4476 4477 4478 4479 4480 4481 4482
      <para>
       When a row is successfully returned, the return value is the number
       of data bytes in the row (this will always be greater than zero).
       The returned string is always null-terminated, though this is
       probably only useful for textual <command>COPY</command>.  A result
       of zero indicates that the <command>COPY</command> is still in
       progress, but no row is yet available (this is only possible when
       <parameter>async</> is true).  A result of -1 indicates that the
       <command>COPY</command> is done.  A result of -2 indicates that an
       error occurred (consult <function>PQerrorMessage</> for the reason).
      </para>
4483

4484 4485 4486 4487 4488 4489 4490 4491 4492 4493
      <para>
       When <parameter>async</> is true (not zero),
       <function>PQgetCopyData</> will not block waiting for input; it
       will return zero if the <command>COPY</command> is still in progress
       but no complete row is available.  (In this case wait for read-ready
       and then call <function>PQconsumeInput</> before calling
       <function>PQgetCopyData</> again.)  When <parameter>async</> is
       false (zero), <function>PQgetCopyData</> will block until data is
       available or the operation completes.
      </para>
4494

4495 4496 4497 4498 4499 4500 4501 4502 4503
      <para>
       After <function>PQgetCopyData</> returns -1, call
       <function>PQgetResult</> to obtain the final result status of the
       <command>COPY</> command.  One can wait for this result to be
       available in the usual way.  Then return to normal operation.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
4504

4505
  </sect2>
4506

4507 4508
  <sect2 id="libpq-copy-deprecated">
   <title>Obsolete Functions for <command>COPY</command></title>
4509

4510 4511 4512 4513 4514 4515
   <para>
    These functions represent older methods of handling <command>COPY</>.
    Although they still work, they are deprecated due to poor error handling,
    inconvenient methods of detecting end-of-data, and lack of support for binary
    or nonblocking transfers.
   </para>
4516

4517 4518 4519 4520 4521 4522 4523 4524 4525 4526 4527 4528 4529 4530 4531 4532 4533 4534 4535
   <variablelist>
    <varlistentry>
     <term>
      <function>PQgetline</function>
      <indexterm>
       <primary>PQgetline</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Reads  a  newline-terminated  line  of  characters (transmitted
       by the server) into a buffer string of size <parameter>length</>.
       <synopsis>
        int PQgetline(PGconn *conn,
                      char *buffer,
                      int length);
       </synopsis>
      </para>
4536

4537 4538 4539 4540 4541 4542 4543 4544 4545 4546 4547 4548 4549 4550 4551 4552 4553 4554 4555
      <para>
       This function copies up to <parameter>length</>-1 characters into
       the buffer and converts the terminating newline into a zero byte.
       <function>PQgetline</function> returns <symbol>EOF</symbol> at the
       end of input, 0 if the entire line has been read, and 1 if the
       buffer is full but the terminating newline has not yet been read.
       </para>
       <para>
       Note that the application must check to see if a new line consists
       of  the  two characters  <literal>\.</literal>, which  indicates
       that the server has finished sending the results  of  the
       <command>COPY</command> command.  If  the  application might receive
       lines that are more than <parameter>length</>-1  characters  long,
       care is needed to be sure it recognizes the <literal>\.</literal>
       line correctly (and does not, for example, mistake the end of a
       long data line for a terminator line).
      </para>
     </listitem>
    </varlistentry>
4556

4557 4558 4559 4560 4561 4562 4563 4564 4565 4566 4567 4568 4569 4570 4571 4572 4573 4574
    <varlistentry>
     <term>
      <function>PQgetlineAsync</function>
      <indexterm>
       <primary>PQgetlineAsync</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Reads a row of <command>COPY</command> data (transmitted  by the
       server) into a buffer without blocking.
       <synopsis>
        int PQgetlineAsync(PGconn *conn,
                           char *buffer,
                           int bufsize);
       </synopsis>
      </para>
4575

4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 4603 4604 4605 4606 4607 4608 4609 4610 4611 4612 4613 4614
      <para>
       This function is similar to <function>PQgetline</function>, but it can be used
       by applications
       that must read <command>COPY</command> data asynchronously, that is, without blocking.
       Having issued the <command>COPY</command> command and gotten a <literal>PGRES_COPY_OUT</literal>
       response, the
       application should call <function>PQconsumeInput</function> and
       <function>PQgetlineAsync</function> until the
       end-of-data signal is detected.
       </para>
       <para>
       Unlike <function>PQgetline</function>, this function takes
       responsibility for detecting end-of-data.
      </para>

      <para>
       On each call, <function>PQgetlineAsync</function> will return data if a
       complete data row is available in <application>libpq</>'s input buffer.
       Otherwise, no data is returned until the rest of the row arrives.
       The function returns -1 if the end-of-copy-data marker has been recognized,
       or 0 if no data is available, or a positive number giving the number of
       bytes of data returned.  If -1 is returned, the caller must next call
       <function>PQendcopy</function>, and then return to normal processing.
      </para>

      <para>
       The data returned will not extend beyond a data-row boundary.  If possible
       a whole row will be returned at one time.  But if the buffer offered by
       the caller is too small to hold a row sent by the server, then a partial
       data row will be returned.  With textual data this can be detected by testing
       whether the last returned byte is <literal>\n</literal> or not.  (In a binary
       <command>COPY</>, actual parsing of the <command>COPY</> data format will be needed to make the
       equivalent determination.)
       The returned string is not null-terminated.  (If you want to add a
       terminating null, be sure to pass a <parameter>bufsize</parameter> one smaller
       than the room actually available.)
      </para>
     </listitem>
    </varlistentry>
4615

4616 4617 4618 4619 4620 4621 4622 4623 4624 4625 4626 4627 4628 4629 4630 4631 4632
    <varlistentry>
     <term>
      <function>PQputline</function>
      <indexterm>
       <primary>PQputline</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Sends  a  null-terminated  string  to  the server.  Returns 0 if
       OK and <symbol>EOF</symbol> if unable to send the string.
       <synopsis>
        int PQputline(PGconn *conn,
                      const char *string);
       </synopsis>
      </para>
4633

4634 4635 4636 4637 4638 4639 4640 4641
      <para>
       The <command>COPY</command> data stream sent by a series of calls
       to <function>PQputline</function> has the same format as that
       returned by <function>PQgetlineAsync</function>, except that
       applications are not obliged to send exactly one data row per
       <function>PQputline</function> call; it is okay to send a partial
       line or multiple lines per call.
      </para>
4642

4643 4644 4645 4646 4647 4648 4649 4650 4651 4652 4653 4654 4655
      <note>
       <para>
        Before <productname>PostgreSQL</productname> protocol 3.0, it was necessary
        for the application to explicitly send the two characters
        <literal>\.</literal> as a final line to indicate to the server that it had
        finished sending <command>COPY</> data.  While this still works, it is deprecated and the
        special meaning of <literal>\.</literal> can be expected to be removed in a
        future release.  It is sufficient to call <function>PQendcopy</function> after
        having sent the actual data.
       </para>
      </note>
     </listitem>
    </varlistentry>
4656

4657 4658 4659 4660 4661 4662 4663
    <varlistentry>
     <term>
      <function>PQputnbytes</function>
      <indexterm>
       <primary>PQputnbytes</primary>
      </indexterm>
     </term>
4664

4665 4666 4667 4668 4669 4670 4671 4672 4673 4674
     <listitem>
      <para>
       Sends  a  non-null-terminated  string  to  the server.  Returns
       0 if OK and <symbol>EOF</symbol> if unable to send the string.
       <synopsis>
        int PQputnbytes(PGconn *conn,
                        const char *buffer,
                        int nbytes);
       </synopsis>
      </para>
4675

4676 4677 4678 4679 4680 4681 4682
      <para>
       This is exactly like <function>PQputline</function>, except that the data
       buffer need not be null-terminated since the number of bytes to send is
       specified directly.  Use this procedure when sending binary data.
      </para>
     </listitem>
    </varlistentry>
4683

4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 4707 4708
    <varlistentry>
     <term>
      <function>PQendcopy</function>
      <indexterm>
       <primary>PQendcopy</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Synchronizes with the server.
       <synopsis>
        int PQendcopy(PGconn *conn);
       </synopsis>
       This function waits until the  server  has  finished  the copying.
       It should either be issued when the  last  string  has  been sent
       to  the  server using <function>PQputline</function> or when the
       last string has been  received  from  the  server using
       <function>PGgetline</function>.  It must be issued or the server
       will get <quote>out of sync</quote> with  the client.   Upon return
       from this function, the server is ready to receive the next SQL
       command.  The return value is 0  on  successful  completion,
       nonzero otherwise.  (Use <function>PQerrorMessage</function> to
       retrieve details if the return value is nonzero.)
      </para>
4709

4710 4711 4712 4713 4714 4715 4716 4717 4718 4719 4720 4721 4722 4723
      <para>
       When using <function>PQgetResult</function>, the application should
       respond to a <literal>PGRES_COPY_OUT</literal> result by executing
       <function>PQgetline</function> repeatedly, followed by
       <function>PQendcopy</function> after the terminator line is seen.
       It should then return to the <function>PQgetResult</function> loop
       until <function>PQgetResult</function> returns a null pointer.
       Similarly a <literal>PGRES_COPY_IN</literal> result is processed
       by a series of <function>PQputline</function> calls followed by
       <function>PQendcopy</function>, then return to the
       <function>PQgetResult</function> loop.  This arrangement will
       ensure that a <command>COPY</command> command embedded in a series
       of <acronym>SQL</acronym> commands will be executed correctly.
      </para>
4724

4725 4726 4727 4728 4729 4730 4731 4732 4733 4734
      <para>
       Older applications are likely to submit a <command>COPY</command>
       via <function>PQexec</function> and assume that the transaction
       is done after <function>PQendcopy</function>.  This will work
       correctly only if the <command>COPY</command> is the only
       <acronym>SQL</acronym> command in the command string.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
4735

4736
  </sect2>
4737

4738
 </sect1>
4739

4740 4741
 <sect1 id="libpq-control">
  <title>Control Functions</title>
4742

4743 4744 4745 4746
  <para>
   These functions control miscellaneous details of <application>libpq</>'s
   behavior.
  </para>
4747

4748
  <variablelist>
4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770 4771 4772 4773 4774 4775 4776 4777 4778 4779 4780 4781 4782 4783 4784 4785 4786 4787 4788 4789 4790 4791 4792 4793 4794 4795 4796 4797 4798
   <varlistentry>
    <term>
     <function>PQclientEncoding</function>
     <indexterm>
      <primary>PQclientEncoding</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Returns the client encoding.
      <synopsis>
      int PQclientEncoding(const PGconn *<replaceable>conn</replaceable>);
      </synopsis>

      Note that it returns the encoding ID, not a symbolic string
      such as <literal>EUC_JP</literal>. To convert an encoding ID to an encoding name, you
      can use:

<synopsis>
char *pg_encoding_to_char(int <replaceable>encoding_id</replaceable>);
</synopsis>
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>PQsetClientEncoding</function>
     <indexterm>
      <primary>PQsetClientEncoding</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Sets the client encoding.
      <synopsis>
      int PQsetClientEncoding(PGconn *<replaceable>conn</replaceable>, const char *<replaceable>encoding</replaceable>);
      </synopsis>

      <replaceable>conn</replaceable> is a connection to the server,
      and <replaceable>encoding</replaceable> is the encoding you want to
      use. If the function successfully sets the encoding, it returns 0,
      otherwise -1. The current encoding for this connection can be
      determined by using <function>PQclientEncoding</>.
     </para>
    </listitem>
   </varlistentry>

4799 4800 4801 4802 4803 4804 4805 4806
   <varlistentry>
    <term>
     <function>PQsetErrorVerbosity</function>
     <indexterm>
      <primary>PQsetErrorVerbosity</primary>
     </indexterm>
    </term>

4807 4808 4809 4810 4811
    <listitem>
     <para>
      Determines the verbosity of messages returned by
      <function>PQerrorMessage</> and <function>PQresultErrorMessage</>.
      <synopsis>
4812 4813 4814 4815 4816
      typedef enum {
          PQERRORS_TERSE,
          PQERRORS_DEFAULT,
          PQERRORS_VERBOSE
      } PGVerbosity;
4817

4818
      PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity);
4819
      </synopsis>
4820

4821 4822 4823 4824 4825 4826 4827 4828 4829 4830 4831 4832
      <function>PQsetErrorVerbosity</> sets the verbosity mode, returning
      the connection's previous setting.  In <firstterm>TERSE</> mode,
      returned messages include severity, primary text, and position only;
      this will normally fit on a single line.  The default mode produces
      messages that include the above plus any detail, hint, or context
      fields (these might span multiple lines).  The <firstterm>VERBOSE</>
      mode includes all available fields.  Changing the verbosity does not
      affect the messages available from already-existing
      <structname>PGresult</> objects, only subsequently-created ones.
     </para>
    </listitem>
   </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
4833

4834 4835 4836 4837 4838 4839 4840
   <varlistentry>
    <term>
     <function>PQtrace</function>
     <indexterm>
      <primary>PQtrace</primary>
     </indexterm>
    </term>
Bruce Momjian's avatar
Bruce Momjian committed
4841

4842
    <listitem>
4843
     <para>
4844 4845 4846 4847
      Enables  tracing of the client/server communication to a debugging file stream.
      <synopsis>
       void PQtrace(PGconn *conn, FILE *stream);
      </synopsis>
4848
     </para>
Bruce Momjian's avatar
Bruce Momjian committed
4849

4850 4851 4852 4853 4854 4855 4856 4857 4858 4859
     <note>
      <para>
       On Windows, if the <application>libpq</> library and an application are
       compiled with different flags, this function call will crash the
       application because the internal representation of the <literal>FILE</>
       pointers differ.  Specifically, multithreaded/single-threaded,
       release/debug, and static/dynamic flags should be the same for the
       library and all applications using that library.
      </para>
     </note>
Bruce Momjian's avatar
Bruce Momjian committed
4860

4861 4862
    </listitem>
   </varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
4863

4864 4865 4866 4867 4868 4869 4870
   <varlistentry>
    <term>
     <function>PQuntrace</function>
     <indexterm>
      <primary>PQuntrace</primary>
     </indexterm>
    </term>
Bruce Momjian's avatar
Bruce Momjian committed
4871

4872 4873 4874 4875 4876 4877 4878 4879 4880 4881
    <listitem>
     <para>
      Disables tracing started by <function>PQtrace</function>.
      <synopsis>
       void PQuntrace(PGconn *conn);
      </synopsis>
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
Bruce Momjian's avatar
Bruce Momjian committed
4882

4883
 </sect1>
Bruce Momjian's avatar
Bruce Momjian committed
4884

4885 4886
 <sect1 id="libpq-misc">
  <title>Miscellaneous Functions</title>
4887

4888 4889 4890
  <para>
   As always, there are some functions that just don't fit anywhere.
  </para>
4891

4892
  <variablelist>
4893 4894 4895 4896 4897 4898 4899 4900 4901 4902 4903 4904 4905 4906 4907 4908 4909 4910 4911 4912 4913 4914 4915 4916 4917 4918 4919 4920 4921 4922 4923 4924 4925 4926 4927 4928 4929 4930 4931 4932 4933 4934 4935 4936 4937 4938 4939 4940 4941 4942 4943 4944 4945 4946 4947 4948 4949
   <varlistentry>
    <term>
     <function>PQfreemem</function>
     <indexterm>
      <primary>PQfreemem</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Frees memory allocated by <application>libpq</>.
      <synopsis>
       void PQfreemem(void *ptr);
      </synopsis>
     </para>

     <para>
      Frees memory allocated by <application>libpq</>, particularly
      <function>PQescapeByteaConn</function>,
      <function>PQescapeBytea</function>,
      <function>PQunescapeBytea</function>,
      and <function>PQnotifies</function>.
      It is particularly important that this function, rather than
      <function>free()</>, be used on Microsoft Windows.  This is because
      allocating memory in a DLL and releasing it in the application works
      only if multithreaded/single-threaded, release/debug, and static/dynamic
      flags are the same for the DLL and the application.  On non-Microsoft
      Windows platforms, this function is the same as the standard library
      function <function>free()</>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>PQconninfoFree</function>
     <indexterm>
      <primary>PQconninfoFree</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Frees the data structures allocated by
      <function>PQconndefaults</> or <function>PQconninfoParse</>.
      <synopsis>
       void PQconninfoFree(PQconninfoOption *connOptions);
      </synopsis>
     </para>

     <para>
      A simple <function>PQfreemem</function> will not do for this, since
      the array contains references to subsidiary strings.
     </para>
    </listitem>
   </varlistentry>

4950 4951 4952 4953 4954 4955 4956 4957 4958 4959 4960 4961 4962 4963 4964 4965 4966 4967 4968 4969 4970 4971 4972 4973 4974 4975 4976 4977 4978
   <varlistentry>
    <term>
     <function>PQencryptPassword</function>
     <indexterm>
      <primary>PQencryptPassword</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Prepares the encrypted form of a <productname>PostgreSQL</> password.
      <synopsis>
       char * PQencryptPassword(const char *passwd, const char *user);
      </synopsis>
      This function is intended to be used by client applications that
      wish to send commands like <literal>ALTER USER joe PASSWORD
      'pwd'</>.  It is good practice not to send the original cleartext
      password in such a command, because it might be exposed in command
      logs, activity displays, and so on.  Instead, use this function to
      convert the password to encrypted form before it is sent.  The
      arguments are the cleartext password, and the SQL name of the user
      it is for.  The return value is a string allocated by
      <function>malloc</function>, or <symbol>NULL</symbol> if out of
      memory.  The caller can assume the string doesn't contain any
      special characters that would require escaping.  Use
      <function>PQfreemem</> to free the result when done with it.
     </para>
    </listitem>
   </varlistentry>
4979 4980 4981 4982 4983 4984 4985 4986 4987 4988 4989 4990 4991 4992 4993 4994 4995 4996 4997 4998 4999 5000 5001 5002 5003 5004

   <varlistentry>
    <term>
     <function>PQmakeEmptyPGresult</function>
     <indexterm>
      <primary>PQmakeEmptyPGresult</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Constructs an empty <structname>PGresult</structname> object with the given status.
      <synopsis>
       PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status);
      </synopsis>
     </para>

     <para>
      This is <application>libpq</>'s internal function to allocate and
      initialize an empty <structname>PGresult</structname> object.  This
      function returns NULL if memory could not be allocated. It is
      exported because some applications find it useful to generate result
      objects (particularly objects with error status) themselves.  If
      <parameter>conn</parameter> is not null and <parameter>status</>
      indicates an error, the current error message of the specified
      connection is copied into the <structname>PGresult</structname>.
5005
      Also, if <parameter>conn</parameter> is not null, any event procedures
5006
      registered in the connection are copied into the
5007 5008 5009
      <structname>PGresult</structname>.  (They do not get
      <literal>PGEVT_RESULTCREATE</> calls, but see
      <function>PQfireResultCreateEvents</function>.)
5010 5011 5012 5013 5014 5015 5016
      Note that <function>PQclear</function> should eventually be called
      on the object, just as with a <structname>PGresult</structname>
      returned by <application>libpq</application> itself.
     </para>
    </listitem>
   </varlistentry>

5017 5018 5019 5020 5021 5022 5023 5024 5025 5026 5027 5028 5029 5030 5031 5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 5044 5045 5046 5047 5048 5049 5050 5051 5052 5053 5054 5055 5056
   <varlistentry>
    <term>
     <function>PQfireResultCreateEvents</function>
     <indexterm>
      <primary>PQfireResultCreateEvents</primary>
     </indexterm>
    </term>
    <listitem>
     <para>
      Fires a <literal>PGEVT_RESULTCREATE</literal> event (see <xref
      linkend="libpq-events">) for each event procedure registered in the
      <structname>PGresult</structname> object.  Returns non-zero for success,
      zero if any event procedure fails.

      <synopsis>
       int PQfireResultCreateEvents(PGconn *conn, PGresult *res);
      </synopsis>
     </para>

     <para>
      The <literal>conn</> argument is passed through to event procedures
      but not used directly.  It can be <literal>NULL</> if the event
      procedures won't use it.
     </para>

     <para>
      Event procedures that have already received a
      <literal>PGEVT_RESULTCREATE</> or <literal>PGEVT_RESULTCOPY</> event
      for this object are not fired again.
     </para>

     <para>
      The main reason that this function is separate from
      <function>PQmakeEmptyPGResult</function> is that it is often appropriate
      to create a <structname>PGresult</structname> and fill it with data
      before invoking the event procedures.
     </para>
    </listitem>
   </varlistentry>

5057 5058 5059 5060 5061 5062 5063 5064 5065 5066 5067 5068 5069 5070 5071 5072 5073 5074 5075 5076 5077 5078 5079 5080 5081 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 5105 5106 5107 5108 5109 5110 5111 5112 5113 5114 5115 5116 5117 5118 5119 5120 5121 5122 5123 5124 5125 5126 5127 5128 5129 5130 5131 5132 5133 5134 5135 5136 5137 5138 5139 5140 5141 5142 5143 5144 5145 5146 5147 5148 5149 5150 5151 5152 5153 5154 5155 5156 5157 5158 5159 5160 5161 5162 5163 5164 5165 5166 5167 5168 5169 5170 5171 5172 5173 5174 5175 5176 5177 5178 5179 5180 5181 5182 5183
   <varlistentry>
    <term>
     <function>PQcopyResult</function>
     <indexterm>
      <primary>PQcopyResult</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Makes a copy of a <structname>PGresult</structname> object.  The copy is
      not linked to the source result in any way and
      <function>PQclear</function> must be called when the copy is no longer
      needed.  If the function fails, NULL is returned.

      <synopsis>
       PGresult *PQcopyResult(const PGresult *src, int flags);
      </synopsis>
     </para>

     <para>
      This is not intended to make an exact copy.  The returned result is
      always put into <literal>PGRES_TUPLES_OK</literal> status, and does not
      copy any error message in the source.  (It does copy the command status
      string, however.)  The <parameter>flags</parameter> argument determines
      what else is copied.  It is a bitwise OR of several flags.
      <literal>PG_COPYRES_ATTRS</literal> specifies copying the source
      result's attributes (column definitions).
      <literal>PG_COPYRES_TUPLES</literal> specifies copying the source
      result's tuples.  (This implies copying the attributes, too.)
      <literal>PG_COPYRES_NOTICEHOOKS</literal> specifies
      copying the source result's notify hooks.
      <literal>PG_COPYRES_EVENTS</literal> specifies copying the source
      result's events.  (But any instance data associated with the source
      is not copied.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>PQsetResultAttrs</function>
     <indexterm>
      <primary>PQsetResultAttrs</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Sets the attributes of a <structname>PGresult</structname> object.
      <synopsis>
       int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs);
      </synopsis>
     </para>

     <para>
      The provided <parameter>attDescs</parameter> are copied into the result.
      If the <parameter>attDescs</parameter> pointer is NULL or
      <parameter>numAttributes</parameter> is less than one, the request is
      ignored and the function succeeds.  If <parameter>res</parameter>
      already contains attributes, the function will fail.  If the function
      fails, the return value is zero.  If the function succeeds, the return
      value is non-zero.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>PQsetvalue</function>
     <indexterm>
      <primary>PQsetvalue</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Sets a tuple field value of a <structname>PGresult</structname> object.
      <synopsis>
       int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len);
      </synopsis>
     </para>

     <para>
      The function will automatically grow the result's internal tuples array
      as needed.  However, the <parameter>tup_num</parameter> argument must be
      less than or equal to <function>PQntuples</function>, meaning this
      function can only grow the tuples array one tuple at a time.  But any
      field of any existing tuple can be modified in any order.  If a value at
      <parameter>field_num</parameter> already exists, it will be overwritten.
      If <parameter>len</parameter> is <literal>-1</literal> or
      <parameter>value</parameter> is <literal>NULL</literal>, the field value
      will be set to an SQL <literal>NULL</literal>.  The
      <parameter>value</parameter> is copied into the result's private storage,
      thus is no longer needed after the function
      returns.  If the function fails, the return value is zero.  If the
      function succeeds, the return value is non-zero.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term>
     <function>PQresultAlloc</function>
     <indexterm>
      <primary>PQresultAlloc</primary>
     </indexterm>
    </term>

    <listitem>
     <para>
      Allocate subsidiary storage for a <structname>PGresult</structname> object.
      <synopsis>
       void *PQresultAlloc(PGresult *res, size_t nBytes);
      </synopsis>
     </para>

     <para>
      Any memory allocated with this function will be freed when
      <parameter>res</parameter> is cleared.  If the function fails,
      the return value is <literal>NULL</literal>.  The result is
      guaranteed to be adequately aligned for any type of data,
      just as for <function>malloc</>.
     </para>
    </listitem>
   </varlistentry>

5184
  </variablelist>
5185

5186
 </sect1>
5187

5188 5189
 <sect1 id="libpq-notice-processing">
  <title>Notice Processing</title>
5190

5191 5192 5193 5194
  <indexterm zone="libpq-notice-processing">
   <primary>notice processing</primary>
   <secondary>in libpq</secondary>
  </indexterm>
5195

5196 5197 5198 5199 5200 5201 5202 5203 5204
  <para>
   Notice and warning messages generated by the server are not returned
   by the query execution functions, since they do not imply failure of
   the query.  Instead they are passed to a notice handling function, and
   execution continues normally after the handler returns.  The default
   notice handling function prints the message on
   <filename>stderr</filename>, but the application can override this
   behavior by supplying its own handling function.
  </para>
5205

5206 5207 5208 5209 5210 5211 5212 5213
  <para>
   For historical reasons, there are two levels of notice handling, called
   the notice receiver and notice processor.  The default behavior is for
   the notice receiver to format the notice and pass a string to the notice
   processor for printing.  However, an application that chooses to provide
   its own notice receiver will typically ignore the notice processor
   layer and just do all the work in the notice receiver.
  </para>
5214

5215 5216 5217 5218 5219 5220 5221 5222 5223
  <para>
   The function <function>PQsetNoticeReceiver</function>
   <indexterm><primary>notice
   receiver</></><indexterm><primary>PQsetNoticeReceiver</></> sets or
   examines the current notice receiver for a connection object.
   Similarly, <function>PQsetNoticeProcessor</function>
   <indexterm><primary>notice
   processor</></><indexterm><primary>PQsetNoticeProcessor</></> sets or
   examines the current notice processor.
5224

5225 5226
   <synopsis>
    typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
5227

5228 5229 5230 5231
    PQnoticeReceiver
    PQsetNoticeReceiver(PGconn *conn,
                        PQnoticeReceiver proc,
                        void *arg);
5232

5233
    typedef void (*PQnoticeProcessor) (void *arg, const char *message);
5234

5235 5236 5237 5238 5239
    PQnoticeProcessor
    PQsetNoticeProcessor(PGconn *conn,
                         PQnoticeProcessor proc,
                         void *arg);
   </synopsis>
5240

5241 5242 5243 5244 5245
   Each of these functions returns the previous notice receiver or
   processor function pointer, and sets the new value.  If you supply a
   null function pointer, no action is taken, but the current pointer is
   returned.
  </para>
5246

5247 5248 5249 5250 5251 5252 5253 5254 5255 5256 5257 5258
  <para>
   When a notice or warning message is received from the server, or
   generated internally by <application>libpq</application>, the notice
   receiver function is called.  It is passed the message in the form of
   a <symbol>PGRES_NONFATAL_ERROR</symbol>
   <structname>PGresult</structname>.  (This allows the receiver to extract
   individual fields using <function>PQresultErrorField</>, or the complete
   preformatted message using <function>PQresultErrorMessage</>.) The same
   void pointer passed to <function>PQsetNoticeReceiver</function> is also
   passed.  (This pointer can be used to access application-specific state
   if needed.)
  </para>
5259

5260 5261 5262 5263 5264
  <para>
   The default notice receiver simply extracts the message (using
   <function>PQresultErrorMessage</>) and passes it to the notice
   processor.
  </para>
5265

5266 5267 5268 5269 5270 5271 5272
  <para>
   The notice processor is responsible for handling a notice or warning
   message given in text form.  It is passed the string text of the message
   (including a trailing newline), plus a void pointer that is the same
   one passed to <function>PQsetNoticeProcessor</function>.  (This pointer
   can be used to access application-specific state if needed.)
  </para>
5273

5274 5275 5276 5277 5278 5279 5280 5281 5282 5283
  <para>
   The default notice processor is simply:
   <programlisting>
static void
defaultNoticeProcessor(void *arg, const char *message)
{
    fprintf(stderr, "%s", message);
}
</programlisting>
  </para>
5284

5285 5286 5287 5288 5289 5290 5291 5292 5293
  <para>
   Once you have set a notice receiver or processor, you should expect
   that that function could be called as long as either the
   <structname>PGconn</> object or <structname>PGresult</> objects made
   from it exist.  At creation of a <structname>PGresult</>, the
   <structname>PGconn</>'s current notice handling pointers are copied
   into the <structname>PGresult</> for possible use by functions like
   <function>PQgetvalue</function>.
  </para>
5294

5295
 </sect1>
5296

5297 5298 5299 5300 5301 5302 5303 5304 5305 5306 5307 5308 5309 5310 5311 5312 5313 5314 5315 5316 5317 5318 5319 5320 5321 5322 5323 5324 5325 5326 5327 5328 5329 5330 5331 5332 5333 5334 5335 5336 5337 5338 5339 5340 5341 5342 5343 5344 5345 5346 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 5357
 <sect1 id="libpq-events">
  <title>Event System</title>

  <para>
   <application>libpq</application>'s event system is designed to notify
   registered event handlers about interesting
   <application>libpq</application> events, such as the creation or
   destruction of <structname>PGconn</structname> and
   <structname>PGresult</structname> objects.  A principal use case is that
   this allows applications to associate their own data with a
   <structname>PGconn</structname> or <structname>PGresult</structname>
   and ensure that that data is freed at an appropriate time.
  </para>

  <para>
   Each registered event handler is associated with two pieces of data,
   known to <application>libpq</application> only as opaque <literal>void *</>
   pointers.  There is a <firstterm>passthrough</> pointer that is provided
   by the application when the event handler is registered with a
   <structname>PGconn</>.  The passthrough pointer never changes for the
   life of the <structname>PGconn</> and all <structname>PGresult</>s
   generated from it; so if used, it must point to long-lived data.
   In addition there is an <firstterm>instance data</> pointer, which starts
   out NULL in every <structname>PGconn</> and <structname>PGresult</>.
   This pointer can be manipulated using the
   <function>PQinstanceData</function>,
   <function>PQsetInstanceData</function>,
   <function>PQresultInstanceData</function> and
   <function>PQsetResultInstanceData</function> functions.  Note that
   unlike the passthrough pointer, instance data of a <structname>PGconn</>
   is not automatically inherited by <structname>PGresult</>s created from
   it.  <application>libpq</application> does not know what passthrough
   and instance data pointers point to (if anything) and will never attempt
   to free them &mdash; that is the responsibility of the event handler.
  </para>

  <sect2 id="libpq-events-types">
   <title>Event Types</title>

   <para>
    The enum <literal>PGEventId</> names the types of events handled by
    the event system.  All its values have names beginning with
    <literal>PGEVT</literal>.  For each event type, there is a corresponding
    event info structure that carries the parameters passed to the event
    handlers.  The event types are:
   </para>

   <variablelist>
    <varlistentry>
     <term><literal>PGEVT_REGISTER</literal></term>
     <listitem>
      <para>
       The register event occurs when <function>PQregisterEventProc</function>
       is called.  It is the ideal time to initialize any
       <literal>instanceData</literal> an event procedure may need.  Only one
       register event will be fired per event handler per connection.  If the
       event procedure fails, the registration is aborted.

      <synopsis>
typedef struct
{
5358
    PGconn *conn;
5359 5360 5361 5362 5363 5364 5365 5366 5367
} PGEventRegister;
      </synopsis>

       When a <literal>PGEVT_REGISTER</literal> event is received, the
       <parameter>evtInfo</parameter> pointer should be cast to a
       <structname>PGEventRegister *</structname>.  This structure contains a
       <structname>PGconn</structname> that should be in the
       <literal>CONNECTION_OK</literal> status; guaranteed if one calls
       <function>PQregisterEventProc</function> right after obtaining a good
5368 5369 5370
       <structname>PGconn</structname>.  When returning a failure code, all
       cleanup must be performed as no <literal>PGEVT_CONNDESTROY</literal>
       event will be sent.
5371 5372 5373 5374 5375 5376 5377 5378 5379 5380 5381 5382 5383 5384 5385 5386 5387 5388 5389 5390
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>PGEVT_CONNRESET</literal></term>
     <listitem>
      <para>
       The connection reset event is fired on completion of
       <function>PQreset</function> or <function>PQresetPoll</function>.  In
       both cases, the event is only fired if the reset was successful.  If
       the event procedure fails, the entire connection reset will fail; the
       <structname>PGconn</structname> is put into
       <literal>CONNECTION_BAD</literal> status and
       <function>PQresetPoll</function> will return
       <literal>PGRES_POLLING_FAILED</literal>.

      <synopsis>
typedef struct
{
5391
    PGconn *conn;
5392 5393 5394 5395 5396 5397 5398 5399
} PGEventConnReset;
      </synopsis>

       When a <literal>PGEVT_CONNRESET</literal> event is received, the
       <parameter>evtInfo</parameter> pointer should be cast to a
       <structname>PGEventConnReset *</structname>.  Although the contained
       <structname>PGconn</structname> was just reset, all event data remains
       unchanged.  This event should be used to reset/reload/requery any
5400 5401 5402 5403
       associated <literal>instanceData</literal>.  Note that even if the
       event procedure fails to process <literal>PGEVT_CONNRESET</>, it will
       still receive a <literal>PGEVT_CONNDESTROY</> event when the connection
       is closed.
5404 5405 5406 5407 5408 5409 5410 5411 5412 5413 5414 5415 5416 5417 5418 5419 5420
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>PGEVT_CONNDESTROY</literal></term>
     <listitem>
      <para>
       The connection destroy event is fired in response to
       <function>PQfinish</function>.  It is the event procedure's
       responsibility to properly clean up its event data as libpq has no
       ability to manage this memory.  Failure to clean up will lead
       to memory leaks.

      <synopsis>
typedef struct
{
5421
    PGconn *conn;
5422 5423 5424 5425 5426 5427 5428 5429 5430 5431 5432 5433 5434 5435 5436 5437 5438 5439 5440 5441 5442 5443 5444 5445 5446 5447 5448
} PGEventConnDestroy;
      </synopsis>

       When a <literal>PGEVT_CONNDESTROY</literal> event is received, the
       <parameter>evtInfo</parameter> pointer should be cast to a
       <structname>PGEventConnDestroy *</structname>.  This event is fired
       prior to <function>PQfinish</function> performing any other cleanup.
       The return value of the event procedure is ignored since there is no
       way of indicating a failure from <function>PQfinish</function>.  Also,
       an event procedure failure should not abort the process of cleaning up
       unwanted memory.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>PGEVT_RESULTCREATE</literal></term>
     <listitem>
      <para>
       The result creation event is fired in response to any query execution
       function that generates a result, including
       <function>PQgetResult</function>.  This event will only be fired after
       the result has been created successfully.

      <synopsis>
typedef struct
{
5449
    PGconn *conn;
5450 5451 5452 5453 5454 5455 5456 5457 5458 5459 5460 5461
    PGresult *result;
} PGEventResultCreate;
      </synopsis>

       When a <literal>PGEVT_RESULTCREATE</literal> event is received, the
       <parameter>evtInfo</parameter> pointer should be cast to a
       <structname>PGEventResultCreate *</structname>.  The
       <parameter>conn</parameter> is the connection used to generate the
       result.  This is the ideal place to initialize any
       <literal>instanceData</literal> that needs to be associated with the
       result.  If the event procedure fails, the result will be cleared and
       the failure will be propagated.  The event procedure must not try to
5462 5463 5464
       <function>PQclear</> the result object for itself.  When returning a
       failure code, all cleanup must be performed as no
       <literal>PGEVT_RESULTDESTROY</literal> event will be sent.
5465 5466 5467 5468 5469 5470 5471 5472 5473 5474
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>PGEVT_RESULTCOPY</literal></term>
     <listitem>
      <para>
       The result copy event is fired in response to
       <function>PQcopyResult</function>.  This event will only be fired after
5475 5476 5477 5478
       the copy is complete.  Only event procedures that have
       successfully handled the <literal>PGEVT_RESULTCREATE</literal>
       or <literal>PGEVT_RESULTCOPY</literal> event for the source result
       will receive <literal>PGEVT_RESULTCOPY</literal> events.
5479 5480 5481 5482 5483 5484 5485 5486 5487 5488 5489 5490 5491 5492 5493 5494 5495

      <synopsis>
typedef struct
{
    const PGresult *src;
    PGresult *dest;
} PGEventResultCopy;
      </synopsis>

       When a <literal>PGEVT_RESULTCOPY</literal> event is received, the
       <parameter>evtInfo</parameter> pointer should be cast to a
       <structname>PGEventResultCopy *</structname>.  The
       <parameter>src</parameter> result is what was copied while the
       <parameter>dest</parameter> result is the copy destination.  This event
       can be used to provide a deep copy of <literal>instanceData</literal>,
       since <literal>PQcopyResult</literal> cannot do that.  If the event
       procedure fails, the entire copy operation will fail and the
5496 5497 5498 5499
       <parameter>dest</parameter> result will be cleared.   When returning a
       failure code, all cleanup must be performed as no
       <literal>PGEVT_RESULTDESTROY</literal> event will be sent for the
       destination result.
5500 5501 5502 5503 5504 5505 5506 5507 5508 5509 5510 5511 5512 5513 5514 5515 5516
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>PGEVT_RESULTDESTROY</literal></term>
     <listitem>
      <para>
       The result destroy event is fired in response to a
       <function>PQclear</function>.  It is the event procedure's
       responsibility to properly clean up its event data as libpq has no
       ability to manage this memory.  Failure to clean up will lead
       to memory leaks.

      <synopsis>
typedef struct
{
5517
    PGresult *result;
5518 5519 5520 5521 5522 5523 5524 5525 5526 5527 5528 5529 5530 5531 5532 5533 5534 5535 5536 5537 5538 5539 5540 5541 5542 5543 5544 5545 5546 5547 5548 5549 5550 5551 5552 5553 5554 5555 5556 5557 5558 5559 5560 5561 5562 5563 5564 5565 5566 5567 5568 5569 5570 5571
} PGEventResultDestroy;
      </synopsis>

       When a <literal>PGEVT_RESULTDESTROY</literal> event is received, the
       <parameter>evtInfo</parameter> pointer should be cast to a
       <structname>PGEventResultDestroy *</structname>.  This event is fired
       prior to <function>PQclear</function> performing any other cleanup.
       The return value of the event procedure is ignored since there is no
       way of indicating a failure from <function>PQclear</function>.  Also,
       an event procedure failure should not abort the process of cleaning up
       unwanted memory.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </sect2>

  <sect2 id="libpq-events-proc">
   <title>Event Callback Procedure</title>

   <variablelist>
    <varlistentry>
     <term>
      <literal>PGEventProc</literal>
      <indexterm>
       <primary>PGEventProc</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       <literal>PGEventProc</literal> is a typedef for a pointer to an
       event procedure, that is, the user callback function that receives
       events from libpq.  The signature of an event procedure must be

      <synopsis>
int eventproc(PGEventId evtId, void *evtInfo, void *passThrough)
      </synopsis>

       The <parameter>evtId</parameter> parameter indicates which
       <literal>PGEVT</literal> event occurred.  The
       <parameter>evtInfo</parameter> pointer must be cast to the appropriate
       structure type to obtain further information about the event.
       The <parameter>passThrough</parameter> parameter is the pointer
       provided to <function>PQregisterEventProc</function> when the event
       procedure was registered.  The function should return a non-zero value
       if it succeeds and zero if it fails.
      </para>

      <para>
       A particular event procedure can be registered only once in any
       <structname>PGconn</>.  This is because the address of the procedure
       is used as a lookup key to identify the associated instance data.
      </para>
5572 5573 5574 5575 5576 5577 5578 5579 5580 5581 5582 5583 5584

      <caution>
       <para>
        On Windows, functions can have two different addresses: one visible
        from outside a DLL and another visible from inside the DLL.  One
        should be careful that only one of these addresses is used with
        <application>libpq</>'s event-procedure functions, else confusion will
        result.  The simplest rule for writing code that will work is to
        ensure that event procedures are declared <literal>static</>.  If the
        procedure's address must be available outside its own source file,
        expose a separate function to return the address.
       </para>
      </caution>
5585 5586 5587 5588 5589 5590 5591 5592 5593 5594 5595 5596 5597 5598 5599 5600 5601 5602 5603 5604 5605 5606 5607 5608 5609 5610 5611 5612 5613 5614 5615 5616 5617 5618 5619 5620 5621 5622 5623 5624 5625 5626 5627 5628 5629 5630 5631 5632 5633 5634 5635 5636 5637 5638 5639 5640 5641 5642 5643 5644 5645 5646 5647 5648 5649 5650 5651 5652 5653 5654 5655 5656 5657 5658 5659 5660 5661 5662 5663 5664 5665 5666 5667 5668 5669 5670 5671 5672 5673 5674 5675 5676 5677 5678 5679 5680 5681 5682 5683 5684 5685 5686 5687 5688 5689 5690 5691 5692 5693 5694 5695 5696 5697 5698 5699 5700 5701 5702 5703 5704 5705 5706 5707 5708 5709 5710 5711 5712 5713 5714 5715 5716 5717 5718 5719 5720 5721 5722
     </listitem>
    </varlistentry>
   </variablelist>
  </sect2>

  <sect2 id="libpq-events-funcs">
   <title>Event Support Functions</title>

    <variablelist>
    <varlistentry>
     <term>
      <function>PQregisterEventProc</function>
      <indexterm>
       <primary>PQregisterEventProc</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Registers an event callback procedure with libpq.

       <synopsis>
        int PQregisterEventProc(PGconn *conn, PGEventProc proc,
                                const char *name, void *passThrough);
       </synopsis>
      </para>

      <para>
       An event procedure must be registered once on each
       <structname>PGconn</> you want to receive events about.  There is no
       limit, other than memory, on the number of event procedures that
       can be registered with a connection.  The function returns a non-zero
       value if it succeeds and zero if it fails.
      </para>

      <para>
       The <parameter>proc</parameter> argument will be called when a libpq
       event is fired.  Its memory address is also used to lookup
       <literal>instanceData</literal>.  The <parameter>name</parameter>
       argument is used to refer to the event procedure in error messages.
       This value cannot be NULL or a zero-length string.  The name string is
       copied into the <structname>PGconn</>, so what is passed need not be
       long-lived.  The <parameter>passThrough</parameter> pointer is passed
       to the <parameter>proc</parameter> whenever an event occurs. This
       argument can be NULL.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQsetInstanceData</function>
      <indexterm>
       <primary>PQsetInstanceData</primary>
      </indexterm>
     </term>
     <listitem>
      <para>
       Sets the conn's instanceData for proc to data.  This returns non-zero
       for success and zero for failure.  (Failure is only possible if
       the proc has not been properly registered in the conn.)

       <synopsis>
        int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQinstanceData</function>
      <indexterm>
       <primary>PQinstanceData</primary>
      </indexterm>
     </term>
     <listitem>
      <para>
       Returns the conn's instanceData associated with proc, or NULL
       if there is none.

       <synopsis>
        void *PQinstanceData(const PGconn *conn, PGEventProc proc);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQresultSetInstanceData</function>
      <indexterm>
       <primary>PQresultSetInstanceData</primary>
      </indexterm>
     </term>
     <listitem>
      <para>
       Sets the result's instanceData for proc to data.  This returns non-zero
       for success and zero for failure.  (Failure is only possible if the
       proc has not been properly registered in the result.)

       <synopsis>
        int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQresultInstanceData</function>
      <indexterm>
       <primary>PQresultInstanceData</primary>
      </indexterm>
     </term>
     <listitem>
      <para>
       Returns the result's instanceData associated with proc, or NULL
       if there is none.

       <synopsis>
        void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
       </synopsis>
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </sect2>

  <sect2 id="libpq-events-example">
   <title>Event Example</title>

   <para>
    Here is a skeleton example of managing private data associated with
    libpq connections and results.
   </para>

   <programlisting>
5723
<![CDATA[
5724
/* required header for libpq events (note: includes libpq-fe.h) */
5725
#include <libpq-events.h>
5726 5727 5728 5729 5730 5731 5732 5733 5734 5735 5736 5737 5738 5739 5740 5741 5742 5743 5744 5745 5746 5747 5748 5749 5750 5751 5752 5753 5754 5755 5756 5757 5758 5759 5760 5761 5762 5763 5764 5765 5766 5767 5768 5769 5770 5771 5772 5773 5774 5775 5776 5777 5778 5779 5780 5781 5782 5783 5784 5785 5786 5787 5788 5789 5790 5791 5792 5793 5794 5795 5796

/* The instanceData */
typedef struct
{
    int n;
    char *str;
} mydata;

/* PGEventProc */
static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough);

int
main(void)
{
    mydata *data;
    PGresult *res;
    PGconn *conn = PQconnectdb("dbname = postgres");

    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    /* called once on any connection that should receive events.
     * Sends a PGEVT_REGISTER to myEventProc.
     */
    if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL))
    {
        fprintf(stderr, "Cannot register PGEventProc\n");
        PQfinish(conn);
        return 1;
    }

    /* conn instanceData is available */
    data = PQinstanceData(conn, myEventProc);

    /* Sends a PGEVT_RESULTCREATE to myEventProc */
    res = PQexec(conn, "SELECT 1 + 1");

    /* result instanceData is available */
    data = PQresultInstanceData(res, myEventProc);

    /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */
    res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS);

    /* result instanceData is available if PG_COPYRES_EVENTS was
     * used during the PQcopyResult call.
     */
    data = PQresultInstanceData(res_copy, myEventProc);

    /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */
    PQclear(res);
    PQclear(res_copy);

    /* Sends a PGEVT_CONNDESTROY to myEventProc */
    PQfinish(conn);

    return 0;
}

static int
myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
{
    switch (evtId)
    {
        case PGEVT_REGISTER:
        {
            PGEventRegister *e = (PGEventRegister *)evtInfo;
5797
            mydata *data = get_mydata(e->conn);
5798 5799

            /* associate app specific data with connection */
5800
            PQsetInstanceData(e->conn, myEventProc, data);
5801 5802 5803 5804 5805 5806
            break;
        }

        case PGEVT_CONNRESET:
        {
            PGEventConnReset *e = (PGEventConnReset *)evtInfo;
5807
            mydata *data = PQinstanceData(e->conn, myEventProc);
5808 5809 5810 5811 5812 5813 5814 5815 5816

            if (data)
              memset(data, 0, sizeof(mydata));
            break;
        }

        case PGEVT_CONNDESTROY:
        {
            PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo;
5817
            mydata *data = PQinstanceData(e->conn, myEventProc);
5818 5819 5820 5821 5822 5823 5824 5825 5826 5827

            /* free instance data because the conn is being destroyed */
            if (data)
              free_mydata(data);
            break;
        }

        case PGEVT_RESULTCREATE:
        {
            PGEventResultCreate *e = (PGEventResultCreate *)evtInfo;
5828
            mydata *conn_data = PQinstanceData(e->conn, myEventProc);
5829 5830 5831
            mydata *res_data = dup_mydata(conn_data);

            /* associate app specific data with result (copy it from conn) */
5832
            PQsetResultInstanceData(e->result, myEventProc, res_data);
5833 5834 5835 5836 5837 5838
            break;
        }

        case PGEVT_RESULTCOPY:
        {
            PGEventResultCopy *e = (PGEventResultCopy *)evtInfo;
5839
            mydata *src_data = PQresultInstanceData(e->src, myEventProc);
5840 5841 5842
            mydata *dest_data = dup_mydata(src_data);

            /* associate app specific data with result (copy it from a result) */
5843
            PQsetResultInstanceData(e->dest, myEventProc, dest_data);
5844 5845 5846 5847 5848 5849
            break;
        }

        case PGEVT_RESULTDESTROY:
        {
            PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo;
5850
            mydata *data = PQresultInstanceData(e->result, myEventProc);
5851 5852 5853 5854 5855 5856 5857 5858 5859 5860 5861 5862 5863 5864

            /* free instance data because the result is being destroyed */
            if (data)
              free_mydata(data);
            break;
        }

        /* unknown event id, just return TRUE. */
        default:
            break;
    }

    return TRUE; /* event processing succeeded */
}
5865
]]>
5866 5867 5868 5869
</programlisting>
  </sect2>
 </sect1>

5870 5871
 <sect1 id="libpq-envars">
  <title>Environment Variables</title>
5872

5873 5874 5875
  <indexterm zone="libpq-envars">
   <primary>environment variable</primary>
  </indexterm>
5876

5877 5878 5879 5880 5881 5882 5883
  <para>
   The following environment variables can be used to select default
   connection parameter values, which will be used by
   <function>PQconnectdb</>, <function>PQsetdbLogin</> and
   <function>PQsetdb</> if no value is directly specified by the calling
   code.  These are useful to avoid hard-coding database connection
   information into simple client applications, for example.
5884

5885 5886 5887 5888 5889 5890
   <itemizedlist>
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGHOST</envar></primary>
      </indexterm>
5891
      <envar>PGHOST</envar> behaves the same as the <xref
5892
      linkend="libpq-connect-host"> connection parameter.
5893 5894 5895 5896 5897 5898 5899 5900
     </para>
    </listitem>

    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGHOSTADDR</envar></primary>
      </indexterm>
5901
      <envar>PGHOSTADDR</envar> behaves the same as the <xref
5902 5903 5904
      linkend="libpq-connect-hostaddr"> connection parameter.
      This can be set instead of or in addition to <envar>PGHOST</envar>
      to avoid DNS lookup overhead.
5905 5906 5907 5908 5909 5910 5911 5912
     </para>
    </listitem>

    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGPORT</envar></primary>
      </indexterm>
5913
      <envar>PGPORT</envar> behaves the same as the <xref
5914
      linkend="libpq-connect-port"> connection parameter.
5915 5916 5917 5918 5919
     </para>
    </listitem>

    <listitem>
     <para>
5920
      <indexterm>
5921 5922
       <primary><envar>PGDATABASE</envar></primary>
      </indexterm>
5923
      <envar>PGDATABASE</envar> behaves the same as the <xref
5924 5925
      linkend="libpq-connect-dbname"> connection parameter.
      </para>
5926 5927 5928 5929 5930 5931 5932
    </listitem>

    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGUSER</envar></primary>
      </indexterm>
5933
      <envar>PGUSER</envar> behaves the same as the <xref
5934
      linkend="libpq-connect-user"> connection parameter.
5935 5936 5937 5938
     </para>
    </listitem>

    <listitem>
5939
     <para>
5940 5941 5942
      <indexterm>
       <primary><envar>PGPASSWORD</envar></primary>
      </indexterm>
5943
      <envar>PGPASSWORD</envar> behaves the same as the <xref
5944 5945
      linkend="libpq-connect-password"> connection parameter.
      Use of this environment variable
5946
      is not recommended for security reasons, as some operating systems
5947
      allow non-root users to see process environment variables via
5948
      <application>ps</>; instead consider using the
5949 5950 5951 5952 5953 5954 5955 5956 5957 5958 5959 5960 5961 5962
      <filename>~/.pgpass</> file (see <xref linkend="libpq-pgpass">).
     </para>
    </listitem>

    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGPASSFILE</envar></primary>
      </indexterm>
      <envar>PGPASSFILE</envar> specifies the name of the password file to
      use for lookups.  If not set, it defaults to <filename>~/.pgpass</>
      (see <xref linkend="libpq-pgpass">).
     </para>
    </listitem>
5963

5964 5965 5966 5967 5968
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSERVICE</envar></primary>
      </indexterm>
5969
      <envar>PGSERVICE</envar> behaves the same as the <xref
5970
      linkend="libpq-connect-service"> connection parameter.
5971 5972
     </para>
    </listitem>
5973

5974 5975 5976 5977 5978 5979 5980 5981 5982 5983 5984 5985
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSERVICEFILE</envar></primary>
      </indexterm>
      <envar>PGSERVICEFILE</envar> specifies the name of the per-user
      connection service file.  If not set, it defaults
      to <filename>~/.pg_service.conf</>
      (see <xref linkend="libpq-pgservice">).
     </para>
    </listitem>

5986 5987 5988 5989 5990
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGREALM</envar></primary>
      </indexterm>
5991
      <envar>PGREALM</envar> sets the Kerberos realm to use with
5992 5993
      <productname>PostgreSQL</productname>, if  it is different from the
      local realm.  If <envar>PGREALM</envar> is set,
5994
      <application>libpq</application> applications will attempt
5995
      authentication  with  servers for this realm and use separate ticket
5996
      files to avoid conflicts with local ticket files.   This
5997 5998 5999 6000
      environment  variable is only used if Kerberos authentication is
      selected by the server.
     </para>
    </listitem>
6001

6002 6003 6004 6005 6006
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGOPTIONS</envar></primary>
      </indexterm>
6007
      <envar>PGOPTIONS</envar> behaves the same as the <xref
6008
      linkend="libpq-connect-options"> connection parameter.
6009 6010
     </para>
    </listitem>
6011

6012 6013 6014 6015 6016 6017 6018 6019 6020 6021
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGAPPNAME</envar></primary>
      </indexterm>
      <envar>PGAPPNAME</envar> behaves the same as the <xref
      linkend="libpq-connect-application-name"> connection parameter.
     </para>
    </listitem>

6022 6023 6024 6025 6026
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSSLMODE</envar></primary>
      </indexterm>
6027
      <envar>PGSSLMODE</envar> behaves the same as the <xref
6028
      linkend="libpq-connect-sslmode"> connection parameter.
6029 6030
     </para>
    </listitem>
6031

6032 6033 6034 6035 6036
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGREQUIRESSL</envar></primary>
      </indexterm>
6037
      <envar>PGREQUIRESSL</envar> behaves the same as the <xref
6038
      linkend="libpq-connect-requiressl"> connection parameter.
6039 6040
     </para>
    </listitem>
6041

6042 6043 6044 6045 6046
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSSLCERT</envar></primary>
      </indexterm>
6047
      <envar>PGSSLCERT</envar> behaves the same as the <xref
6048
      linkend="libpq-connect-sslcert"> connection parameter.
6049 6050 6051
     </para>
    </listitem>

6052 6053 6054 6055 6056
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSSLKEY</envar></primary>
      </indexterm>
6057
      <envar>PGSSLKEY</envar> behaves the same as the <xref
6058
      linkend="libpq-connect-sslkey"> connection parameter.
6059 6060 6061 6062 6063 6064 6065 6066
     </para>
    </listitem>

    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSSLROOTCERT</envar></primary>
      </indexterm>
6067
      <envar>PGSSLROOTCERT</envar>  behaves the same as the <xref
6068
      linkend="libpq-connect-sslrootcert"> connection parameter.
6069 6070 6071 6072 6073 6074 6075 6076
     </para>
    </listitem>

    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSSLCRL</envar></primary>
      </indexterm>
6077
      <envar>PGSSLCRL</envar>  behaves the same as the <xref
6078
      linkend="libpq-connect-sslcrl"> connection parameter.
6079 6080
     </para>
    </listitem>
6081

6082 6083 6084 6085 6086
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGKRBSRVNAME</envar></primary>
      </indexterm>
6087
      <envar>PGKRBSRVNAME</envar>  behaves the same as the <xref
6088
      linkend="libpq-connect-krbsrvname"> connection parameter.
6089 6090
     </para>
    </listitem>
6091

6092 6093 6094 6095 6096
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGGSSLIB</envar></primary>
      </indexterm>
6097
      <envar>PGGSSLIB</envar> behaves the same as the <xref
6098
      linkend="libpq-connect-gsslib"> connection parameter.
6099 6100
     </para>
    </listitem>
6101

6102 6103 6104 6105 6106
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGCONNECT_TIMEOUT</envar></primary>
      </indexterm>
6107
      <envar>PGCONNECT_TIMEOUT</envar>  behaves the same as the <xref
6108
      linkend="libpq-connect-connect-timeout"> connection parameter.
6109 6110 6111 6112
     </para>
    </listitem>
   </itemizedlist>
  </para>
6113

6114
  <para>
6115 6116
   The following environment variables can be used to specify default
   behavior for each <productname>PostgreSQL</productname> session.  (See
6117 6118
   also the <xref linkend="sql-alteruser">
   and <xref linkend="sql-alterdatabase">
6119 6120
   commands for ways to set default behavior on a per-user or per-database
   basis.)
6121

6122 6123 6124 6125 6126 6127 6128 6129 6130 6131 6132
   <itemizedlist>
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGDATESTYLE</envar></primary>
      </indexterm>
      <envar>PGDATESTYLE</envar> sets the default style of date/time
      representation.  (Equivalent to <literal>SET datestyle TO
      ...</literal>.)
     </para>
    </listitem>
6133

6134 6135 6136 6137 6138 6139 6140 6141 6142
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGTZ</envar></primary>
      </indexterm>
      <envar>PGTZ</envar> sets the default time zone.  (Equivalent to
      <literal>SET timezone TO ...</literal>.)
     </para>
    </listitem>
6143

6144 6145 6146 6147 6148 6149 6150 6151 6152 6153
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGCLIENTENCODING</envar></primary>
      </indexterm>
      <envar>PGCLIENTENCODING</envar> sets the default client character
      set encoding.  (Equivalent to <literal>SET client_encoding TO
      ...</literal>.)
     </para>
    </listitem>
6154

6155 6156 6157 6158 6159 6160 6161 6162 6163 6164
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGGEQO</envar></primary>
      </indexterm>
      <envar>PGGEQO</envar> sets the default mode for the genetic query
      optimizer.  (Equivalent to <literal>SET geqo TO ...</literal>.)
     </para>
    </listitem>
   </itemizedlist>
6165

6166 6167
   Refer to the <acronym>SQL</acronym> command <xref linkend="sql-set">
   for information on correct values for these
6168
   environment variables.
6169
  </para>
6170

6171
  <para>
6172 6173
   The following environment variables determine internal behavior of
   <application>libpq</application>; they override compiled-in defaults.
6174

6175 6176 6177 6178 6179 6180 6181
   <itemizedlist>
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGSYSCONFDIR</envar></primary>
      </indexterm>
      <envar>PGSYSCONFDIR</envar> sets the directory containing the
6182 6183
      <filename>pg_service.conf</> file and in a future version
      possibly other system-wide configuration files.
6184 6185
     </para>
    </listitem>
6186

6187 6188 6189 6190 6191 6192 6193 6194 6195 6196
    <listitem>
     <para>
      <indexterm>
       <primary><envar>PGLOCALEDIR</envar></primary>
      </indexterm>
      <envar>PGLOCALEDIR</envar> sets the directory containing the
      <literal>locale</> files for message internationalization.
     </para>
    </listitem>
   </itemizedlist>
6197
  </para>
6198

6199
 </sect1>
6200 6201


6202 6203
 <sect1 id="libpq-pgpass">
  <title>The Password File</title>
6204

6205 6206 6207 6208 6209 6210
  <indexterm zone="libpq-pgpass">
   <primary>password file</primary>
  </indexterm>
  <indexterm zone="libpq-pgpass">
   <primary>.pgpass</primary>
  </indexterm>
6211

6212
  <para>
6213 6214 6215 6216 6217 6218 6219
   The file <filename>.pgpass</filename> in a user's home directory or the
   file referenced by <envar>PGPASSFILE</envar> can contain passwords to
   be used if the connection requires a password (and no password has been
   specified  otherwise). On Microsoft Windows the file is named
   <filename>%APPDATA%\postgresql\pgpass.conf</> (where
   <filename>%APPDATA%</> refers to the Application Data subdirectory in
   the user's profile).
6220
  </para>
6221

6222
  <para>
6223 6224 6225 6226 6227 6228 6229 6230 6231 6232
   This file should contain lines of the following format:
   <synopsis>
    <replaceable>hostname</replaceable>:<replaceable>port</replaceable>:<replaceable>database</replaceable>:<replaceable>username</replaceable>:<replaceable>password</replaceable>
   </synopsis>
   Each of the first four fields can be a literal value, or
   <literal>*</literal>, which matches anything.  The password field from
   the first line that matches the current connection parameters will be
   used.  (Therefore, put more-specific entries first when you are using
   wildcards.) If an entry needs to contain <literal>:</literal> or
   <literal>\</literal>, escape this character with <literal>\</literal>.
Peter Eisentraut's avatar
Peter Eisentraut committed
6233
   A host name of <literal>localhost</> matches both TCP (host name
6234 6235
   <literal>localhost</>) and Unix domain socket (<literal>pghost</> empty
   or the default socket directory) connections coming from the local
6236 6237
   machine. In a standby server, a database name of <literal>replication</>
   matches streaming replication connections made to the master server.
6238
  </para>
6239

6240
  <para>
6241 6242 6243 6244 6245 6246
   On Unix systems, the permissions on <filename>.pgpass</filename> must
   disallow any access to world or group; achieve this by the command
   <command>chmod 0600 ~/.pgpass</command>.  If the permissions are less
   strict than this, the file will be ignored.  On Microsoft Windows, it
   is assumed that the file is stored in a directory that is secure, so
   no special permissions check is made.
6247
  </para>
6248
 </sect1>
6249 6250


6251 6252
 <sect1 id="libpq-pgservice">
  <title>The Connection Service File</title>
6253

6254 6255 6256 6257 6258 6259
  <indexterm zone="libpq-pgservice">
   <primary>connection service file</primary>
  </indexterm>
  <indexterm zone="libpq-pgservice">
   <primary>pg_service.conf</primary>
  </indexterm>
6260 6261 6262
  <indexterm zone="libpq-pgservice">
   <primary>.pg_service.conf</primary>
  </indexterm>
6263

6264
  <para>
6265 6266 6267 6268 6269 6270
   The connection service file allows libpq connection parameters to be
   associated with a single service name. That service name can then be
   specified by a libpq connection, and the associated settings will be
   used. This allows connection parameters to be modified without requiring
   a recompile of the libpq application. The service name can also be
   specified using the <envar>PGSERVICE</envar> environment variable.
6271
  </para>
6272 6273

  <para>
6274 6275 6276 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 6287 6288 6289 6290 6291 6292 6293 6294 6295 6296 6297 6298
   The connection service file can be a per-user service file
   at <filename>~/.pg_service.conf</filename> or the location
   specified by the environment variable <envar>PGSERVICEFILE</envar>,
   or it can be a system-wide file
   at <filename>etc/pg_service.conf</filename> or in the directory
   specified by the environment variable
   <envar>PGSYSCONFDIR</envar>.  If service definitions with the same
   name exist in the user and the system file, the user file takes
   precedence.
  </para>

  <para>
   The file uses an <quote>INI file</quote> format where the section
   name is the service name and the parameters are connection
   parameters; see <xref linkend="libpq-connect"> for a list.  For
   example:
<programlisting>
# comment
[mydb]
host=somehost
port=5433
user=admin
</programlisting>
   An example file is provided at
   <filename>share/pg_service.conf.sample</filename>.
6299 6300
  </para>
 </sect1>
6301 6302


6303 6304
 <sect1 id="libpq-ldap">
  <title>LDAP Lookup of Connection Parameters</title>
6305

6306 6307 6308
  <indexterm zone="libpq-ldap">
   <primary>LDAP connection parameter lookup</primary>
  </indexterm>
6309

6310
  <para>
6311 6312 6313 6314 6315 6316
   If <application>libpq</application> has been compiled with LDAP support (option
   <literal><option>--with-ldap</option></literal> for <command>configure</command>)
   it is possible to retrieve connection options like <literal>host</literal>
   or <literal>dbname</literal> via LDAP from a central server.
   The advantage is that if the connection parameters for a database change,
   the connection information doesn't have to be updated on all client machines.
6317
  </para>
6318

6319
  <para>
6320 6321 6322 6323 6324 6325 6326 6327 6328 6329 6330 6331 6332 6333 6334
   LDAP connection parameter lookup uses the connection service file
   <filename>pg_service.conf</filename> (see <xref
   linkend="libpq-pgservice">).  A line in a
   <filename>pg_service.conf</filename> stanza that starts with
   <literal>ldap://</literal> will be recognized as an LDAP URL and an
   LDAP query will be performed. The result must be a list of
   <literal>keyword = value</literal> pairs which will be used to set
   connection options.  The URL must conform to RFC 1959 and be of the
   form
   <synopsis>
    ldap://[<replaceable>hostname</replaceable>[:<replaceable>port</replaceable>]]/<replaceable>search_base</replaceable>?<replaceable>attribute</replaceable>?<replaceable>search_scope</replaceable>?<replaceable>filter</replaceable>
   </synopsis>
   where <replaceable>hostname</replaceable> defaults to
   <literal>localhost</literal> and <replaceable>port</replaceable>
   defaults to 389.
6335
  </para>
6336

6337
  <para>
6338 6339 6340 6341 6342 6343 6344
   Processing of <filename>pg_service.conf</filename> is terminated after
   a successful LDAP lookup, but is continued if the LDAP server cannot
   be contacted.  This is to provide a fallback with further LDAP URL
   lines that point to different LDAP servers, classical <literal>keyword
   = value</literal> pairs, or default connection options.  If you would
   rather get an error message in this case, add a syntactically incorrect
   line after the LDAP URL.
6345
  </para>
6346

6347 6348 6349 6350 6351 6352 6353 6354 6355 6356 6357 6358 6359 6360 6361 6362 6363 6364 6365 6366
  <para>
   A sample LDAP entry that has been created with the LDIF file
   <synopsis>
    version:1
    dn:cn=mydatabase,dc=mycompany,dc=com
    changetype:add
    objectclass:top
    objectclass:groupOfUniqueNames
    cn:mydatabase
    uniqueMember:host=dbserver.mycompany.com
    uniqueMember:port=5439
    uniqueMember:dbname=mydb
    uniqueMember:user=mydb_user
    uniqueMember:sslmode=require
   </synopsis>
   might be queried with the following LDAP URL:
   <synopsis>
    ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase)
   </synopsis>
  </para>
Bruce Momjian's avatar
Bruce Momjian committed
6367 6368 6369 6370 6371 6372 6373 6374 6375 6376 6377 6378

  <para>
   You can also mix regular service file entries with LDAP lookups.
   A complete example for a stanza in <filename>pg_service.conf</filename>
   would be:
   <synopsis>
    # only host and port are stored in LDAP, specify dbname and user explicitly
    [customerdb]
    dbname=customer
    user=appuser
    ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
   </synopsis>
6379 6380
  </para>

6381
 </sect1>
6382 6383


6384 6385
 <sect1 id="libpq-ssl">
  <title>SSL Support</title>
6386

6387 6388
  <indexterm zone="libpq-ssl">
   <primary>SSL</primary>
Peter Eisentraut's avatar
Peter Eisentraut committed
6389
  </indexterm>
6390

6391
  <para>
6392 6393 6394 6395
   <productname>PostgreSQL</> has native support for using <acronym>SSL</>
   connections to encrypt client/server communications for increased
   security. See <xref linkend="ssl-tcp"> for details about the server-side
   <acronym>SSL</> functionality.
6396
  </para>
6397

Bruce Momjian's avatar
Bruce Momjian committed
6398
  <para>
6399 6400 6401
   <application>libpq</application> reads the system-wide
   <productname>OpenSSL</productname> configuration file. By default, this
   file is named <filename>openssl.cnf</filename> and is located in the
6402 6403
   directory reported by <literal>openssl version -d</>.  This default
   can be overridden by setting environment variable
6404 6405
   <envar>OPENSSL_CONF</envar> to the name of the desired configuration
   file.
Bruce Momjian's avatar
Bruce Momjian committed
6406
  </para>
6407

6408 6409 6410 6411
 <sect2 id="libq-ssl-certificates">
  <title>Certificate verification</title>

  <para>
6412
   By default, <productname>PostgreSQL</> will not perform any verification of
6413 6414
   the server certificate. This means that it is possible to spoof the server
   identity (for example by modifying a DNS record or by taking over the server
6415
   IP address) without the client knowing. In order to prevent spoofing,
6416
   <acronym>SSL</> certificate verification must be used.
6417 6418 6419
  </para>

  <para>
6420 6421 6422 6423 6424 6425 6426 6427
   If the parameter <literal>sslmode</> is set to <literal>verify-ca</>,
   libpq will verify that the server is trustworthy by checking the
   certificate chain up to a trusted certificate authority
   (<acronym>CA</>). If <literal>sslmode</> is set to <literal>verify-full</>,
   libpq will <emphasis>also</> verify that the server hostname matches its
   certificate. The SSL connection will fail if the server certificate cannot
   be verified. <literal>verify-full</> is recommended in most
   security-sensitive environments.
6428 6429
  </para>

6430
  <para>
6431 6432
   In <literal>verify-full</> mode, the <literal>cn</> (Common Name) attribute
   of the certificate is matched against the hostname. If the <literal>cn</>
6433 6434 6435 6436 6437 6438 6439 6440
   attribute starts with an asterisk (<literal>*</>), it will be treated as
   a wildcard, and will match all characters <emphasis>except</> a dot
   (<literal>.</>). This means the certificate will not match subdomains.
   If the connection is made using an IP address instead of a hostname, the
   IP address will be matched (without doing any DNS lookups).
  </para>

  <para>
6441 6442
   To allow server certificate verification, the certificate(s) of one or more
   trusted <acronym>CA</>s must be
6443 6444
   placed in the file <filename>~/.postgresql/root.crt</> in the user's home
   directory. (On Microsoft Windows the file is named
6445
   <filename>%APPDATA%\postgresql\root.crt</filename>.)
6446 6447 6448
  </para>

  <para>
6449
   Certificate Revocation List (CRL) entries are also checked
6450 6451 6452
   if the file <filename>~/.postgresql/root.crl</filename> exists
   (<filename>%APPDATA%\postgresql\root.crl</filename> on Microsoft
   Windows).
6453 6454 6455
  </para>

  <para>
6456 6457 6458
   The location of the root certificate file and the CRL can be changed by
   setting
   the connection parameters <literal>sslrootcert</> and <literal>sslcrl</>
6459
   or the environment variables <envar>PGSSLROOTCERT</> and <envar>PGSSLCRL</>.
6460
  </para>
6461 6462 6463 6464
 </sect2>

 <sect2 id="libpq-ssl-clientcert">
  <title>Client certificates</title>
6465 6466 6467

  <para>
   If the server requests a trusted client certificate,
6468
   <application>libpq</application> will send the certificate stored in
6469 6470 6471 6472
   file <filename>~/.postgresql/postgresql.crt</> in the user's home
   directory.  The certificate must be signed by one of the certificate
   authorities (<acronym>CA</acronym>) trusted by the server.  A matching
   private key file <filename>~/.postgresql/postgresql.key</> must also
6473
   be present. The private
6474 6475 6476
   key file must not allow any access to world or group; achieve this by the
   command <command>chmod 0600 ~/.postgresql/postgresql.key</command>.
   On Microsoft Windows these files are named
6477
   <filename>%APPDATA%\postgresql\postgresql.crt</filename> and
6478 6479
   <filename>%APPDATA%\postgresql\postgresql.key</filename>, and there
   is no special permissions check since the directory is presumed secure.
6480 6481 6482
   The location of the certificate and key files can be overridden by the
   connection parameters <literal>sslcert</> and <literal>sslkey</> or the
   environment variables <envar>PGSSLCERT</> and <envar>PGSSLKEY</>.
6483 6484
  </para>

6485 6486 6487 6488 6489 6490 6491 6492 6493 6494 6495 6496 6497 6498 6499 6500 6501 6502
  <para>
   In some cases, the client certificate might be signed by an
   <quote>intermediate</> certificate authority, rather than one that is
   directly trusted by the server.  To use such a certificate, append the
   certificate of the signing authority to the <filename>postgresql.crt</>
   file, then its parent authority's certificate, and so on up to a
   <quote>root</> authority that is trusted by the server.  The root
   certificate should be included in every case where
   <filename>postgresql.crt</> contains more than one certificate.
  </para>

  <para>
   Note that <filename>root.crt</filename> lists the top-level CAs that are
   considered trusted for signing server certificates.  In principle it need
   not list the CA that signed the client's certificate, though in most cases
   that CA would also be trusted for server certificates.
  </para>

6503
 </sect2>
6504 6505 6506 6507 6508 6509

 <sect2 id="libpq-ssl-protection">
  <title>Protection provided in different modes</title>

  <para>
   The different values for the <literal>sslmode</> parameter provide different
6510 6511
   levels of protection. SSL can provide
   protection against three types of attacks:
6512 6513 6514 6515 6516 6517 6518 6519 6520 6521 6522 6523 6524 6525
  </para>
  <table id="libpq-ssl-protect-attacks">
   <title>SSL attacks</title>
   <tgroup cols="2">
    <thead>
     <row>
      <entry>Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry>Eavesdropping</entry>
6526
      <entry>If a third party can examine the network traffic between the
6527 6528 6529 6530 6531 6532 6533 6534 6535 6536 6537 6538 6539 6540 6541 6542 6543 6544 6545 6546 6547 6548 6549 6550 6551 6552 6553 6554 6555 6556 6557 6558 6559 6560
       client and the server, it can read both connection information (including
       the username and password) and the data that is passed. <acronym>SSL</>
       uses encryption to prevent this.
      </entry>
     </row>

     <row>
      <entry>Man in the middle (<acronym>MITM</>)</entry>
      <entry>If a third party can modify the data while passing between the
       client and server, it can pretend to be the server and therefore see and
       modify data <emphasis>even if it is encrypted</>. The third party can then
       forward the connection information and data to the original server,
       making it impossible to detect this attack. Common vectors to do this
       include DNS poisoning and address hijacking, whereby the client is directed
       to a different server than intended. There are also several other
       attack methods that can accomplish this. <acronym>SSL</> uses certificate
       verification to prevent this, by authenticating the server to the client.
      </entry>
     </row>

     <row>
      <entry>Impersonation</entry>
      <entry>If a third party can pretend to be an authorized client, it can
       simply access data it should not have access to. Typically this can
       happen through insecure password management. <acronym>SSL</> uses
       client certificates to prevent this, by making sure that only holders
       of valid certificates can access the server.
      </entry>
     </row>
    </tbody>
   </tgroup>
  </table>

  <para>
6561 6562
   For a connection to be known secure, SSL usage must be configured
   on <emphasis>both the client and the server</> before the connection
6563 6564
   is made. If it is only configured on the server, the client may end up
   sending sensitive information (e.g. passwords) before
6565 6566
   it knows that the server requires high security. In libpq, secure
   connections can be ensured
6567 6568
   by setting the <literal>sslmode</> parameter to <literal>verify-full</> or
   <literal>verify-ca</>, and providing the system with a root certificate to
6569
   verify against. This is analogous to using an <literal>https</>
6570 6571 6572 6573 6574 6575
   <acronym>URL</> for encrypted web browsing.
  </para>

  <para>
   Once the server has been authenticated, the client can pass sensitive data.
   This means that up until this point, the client does not need to know if
6576
   certificates will be used for authentication, making it safe to specify that
6577 6578 6579 6580 6581
   only in the server configuration.
  </para>

  <para>
   All <acronym>SSL</> options carry overhead in the form of encryption and
6582
   key-exchange, so there is a tradeoff that has to be made between performance
6583 6584 6585 6586 6587 6588 6589 6590 6591 6592 6593 6594 6595 6596 6597 6598 6599 6600 6601 6602 6603 6604 6605 6606 6607 6608 6609 6610 6611 6612 6613 6614 6615 6616 6617 6618 6619 6620 6621 6622 6623 6624 6625 6626 6627 6628 6629 6630 6631 6632 6633 6634 6635 6636 6637 6638 6639 6640 6641 6642 6643 6644 6645 6646 6647 6648 6649 6650 6651 6652 6653 6654 6655 6656 6657 6658 6659 6660 6661 6662 6663
   and security. The following table illustrates the risks the different
   <literal>sslmode</> values protect against, and what statement they make
   about security and overhead:
  </para>

  <table id="libpq-ssl-sslmode-statements">
   <title>SSL mode descriptions</title>
   <tgroup cols="4">
    <thead>
     <row>
      <entry><literal>sslmode</></entry>
      <entry>Eavesdropping protection</entry>
      <entry><acronym>MITM</> protection</entry>
      <entry>Statement</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry><literal>disabled</></entry>
      <entry>No</entry>
      <entry>No</entry>
      <entry>I don't care about security, and I don't want to pay the overhead
       of encryption.
      </entry>
     </row>

     <row>
      <entry><literal>allow</></entry>
      <entry>Maybe</entry>
      <entry>No</entry>
      <entry>I don't care about security, but I will pay the overhead of
       encryption if the server insists on it.
      </entry>
     </row>

     <row>
      <entry><literal>prefer</></entry>
      <entry>Maybe</entry>
      <entry>No</entry>
      <entry>I don't care about encryption, but I wish to pay the overhead of
       encryption if the server supports it.
      </entry>
     </row>

     <row>
      <entry><literal>require</></entry>
      <entry>Yes</entry>
      <entry>No</entry>
      <entry>I want my data to be encrypted, and I accept the overhead. I trust
       that the network will make sure I always connect to the server I want.
      </entry>
     </row>

     <row>
      <entry><literal>verify-ca</></entry>
      <entry>Yes</entry>
      <entry><literal>Depends on CA</>-policy</entry>
      <entry>I want my data encrypted, and I accept the overhead. I want to be
       sure that I connect to a server that I trust.
      </entry>
     </row>

     <row>
      <entry><literal>verify-full</></entry>
       <entry>Yes</entry>
       <entry>Yes</entry>
       <entry>I want my data encrypted, and I accept the overhead. I want to be
        sure that I connect to a server I trust, and that it's the one I
        specify.
       </entry>
      </row>

    </tbody>
   </tgroup>
  </table>

  <para>
   The difference between <literal>verify-ca</> and <literal>verify-full</>
   depends on the policy of the root <acronym>CA</>. If a public
   <acronym>CA</> is used, <literal>verify-ca</> allows connections to a server
6664 6665
   that <emphasis>somebody else</> may have registered with the <acronym>CA</>.
   In this case, <literal>verify-full</> should always be used. If
6666 6667 6668 6669 6670 6671 6672 6673
   a local <acronym>CA</> is used, or even a self-signed certificate, using
   <literal>verify-ca</> often provides enough protection.
  </para>

  <para>
   The default value for <literal>sslmode</> is <literal>prefer</>. As is shown
   in the table, this makes no sense from a security point of view, and it only
   promises performance overhead if possible. It is only provided as the default
6674
   for backwards compatibility, and is not recommended in secure deployments.
6675 6676 6677 6678
  </para>

 </sect2>

6679 6680
 <sect2 id="libpq-ssl-fileusage">
  <title>SSL File Usage</title>
6681
  <table id="libpq-ssl-file-usage">
6682
   <title>Libpq/Client SSL File Usage</title>
6683 6684 6685 6686 6687 6688 6689 6690 6691 6692 6693 6694 6695 6696 6697 6698 6699 6700 6701 6702
   <tgroup cols="3">
    <thead>
     <row>
      <entry>File</entry>
      <entry>Contents</entry>
      <entry>Effect</entry>
     </row>
    </thead>

    <tbody>

     <row>
      <entry><filename>~/.postgresql/postgresql.crt</></entry>
      <entry>client certificate</entry>
      <entry>requested by server</entry>
     </row>

     <row>
      <entry><filename>~/.postgresql/postgresql.key</></entry>
      <entry>client private key</entry>
6703 6704
      <entry>proves client certificate sent by owner; does not indicate
      certificate owner is trustworthy</entry>
6705 6706 6707 6708 6709
     </row>

     <row>
      <entry><filename>~/.postgresql/root.crt</></entry>
      <entry>trusted certificate authorities</entry>
6710
      <entry>checks that server certificate is signed by a trusted certificate
6711
      authority</entry>
6712 6713 6714 6715 6716 6717 6718 6719 6720 6721 6722
     </row>

     <row>
      <entry><filename>~/.postgresql/root.crl</></entry>
      <entry>certificates revoked by certificate authorities</entry>
      <entry>server certificate must not be on this list</entry>
     </row>

    </tbody>
   </tgroup>
  </table>
6723 6724 6725 6726
 </sect2>

 <sect2 id="libpq-ssl-initialize">
  <title>SSL library initialization</title>
6727

6728 6729 6730 6731 6732 6733 6734 6735 6736 6737 6738 6739 6740 6741 6742 6743 6744 6745 6746 6747 6748 6749 6750 6751 6752 6753 6754 6755 6756 6757 6758 6759 6760 6761 6762 6763 6764 6765 6766 6767 6768 6769 6770 6771 6772 6773 6774 6775 6776 6777 6778 6779 6780 6781 6782 6783 6784 6785 6786 6787 6788 6789 6790 6791 6792 6793 6794 6795 6796 6797 6798 6799 6800 6801 6802 6803 6804 6805 6806 6807 6808 6809 6810 6811 6812 6813
  <para>
   If your application initializes <literal>libssl</> and/or
   <literal>libcrypto</> libraries and <application>libpq</application>
   is built with <acronym>SSL</> support, you should call
   <function>PQinitOpenSSL</> to tell <application>libpq</application>
   that the <literal>libssl</> and/or <literal>libcrypto</> libraries
   have been initialized by your application, so that
   <application>libpq</application> will not also initialize those libraries.
   <!-- If this URL changes replace it with a URL to www.archive.org. -->
   See <ulink
   url="http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html"></ulink>
   for details on the SSL API.
  </para>

  <para>
   <variablelist>
    <varlistentry>
     <term>
      <function>PQinitOpenSSL</function>
      <indexterm>
       <primary>PQinitOpenSSL</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Allows applications to select which security libraries to initialize.
       <synopsis>
        void PQinitOpenSSL(int do_ssl, init do_crypto);
       </synopsis>
      </para>

      <para>
       When <parameter>do_ssl</> is non-zero, <application>libpq</application>
       will initialize the <application>OpenSSL</> library before first
       opening a database connection.  When <parameter>do_crypto</> is
       non-zero, the <literal>libcrypto</> library will be initialized.  By
       default (if <function>PQinitOpenSSL</> is not called), both libraries
       are initialized.  When SSL support is not compiled in, this function is
       present but does nothing.
      </para>

      <para>
       If your application uses and initializes either <application>OpenSSL</>
       or its underlying <literal>libcrypto</> library, you <emphasis>must</>
       call this function with zeroes for the appropriate parameter(s)
       before first opening a database connection.  Also be sure that you
       have done that initialization before opening a database connection.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <function>PQinitSSL</function>
      <indexterm>
       <primary>PQinitSSL</primary>
      </indexterm>
     </term>

     <listitem>
      <para>
       Allows applications to select which security libraries to initialize.
       <synopsis>
        void PQinitSSL(int do_ssl);
       </synopsis>
      </para>

      <para>
       This function is equivalent to
       <literal>PQinitOpenSSL(do_ssl, do_ssl)</>.
       It is sufficient for applications that initialize both or neither
       of <application>OpenSSL</> and <literal>libcrypto</>.
      </para>

      <para>
       <function>PQinitSSL</> has been present since
       <productname>PostgreSQL</> 8.0, while <function>PQinitOpenSSL</>
       was added in <productname>PostgreSQL</> 8.4, so <function>PQinitSSL</>
       might be preferable for applications that need to work with older
       versions of <application>libpq</application>.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
6814
 </sect2>
6815

6816
 </sect1>
6817 6818


6819 6820
 <sect1 id="libpq-threading">
  <title>Behavior in Threaded Programs</title>
6821

6822 6823 6824 6825
  <indexterm zone="libpq-threading">
   <primary>threads</primary>
   <secondary>with libpq</secondary>
  </indexterm>
6826

6827
  <para>
Bruce Momjian's avatar
Bruce Momjian committed
6828 6829
   <application>libpq</application> is reentrant and thread-safe by default.
   You might need to use special compiler command-line
6830 6831
   options when you compile your application code.  Refer to your
   system's documentation for information about how to build
6832
   thread-enabled applications, or look in
6833 6834 6835 6836
   <filename>src/Makefile.global</filename> for <literal>PTHREAD_CFLAGS</>
   and <literal>PTHREAD_LIBS</>.  This function allows the querying of
   <application>libpq</application>'s thread-safe status:
  </para>
6837

6838 6839 6840 6841 6842 6843 6844 6845
  <variablelist>
   <varlistentry>
    <term>
     <function>PQisthreadsafe</function>
     <indexterm>
      <primary>PQisthreadsafe</primary>
     </indexterm>
    </term>
6846

6847 6848 6849 6850 6851 6852 6853 6854
    <listitem>
     <para>
      Returns the thread safety status of the
      <application>libpq</application> library.
      <synopsis>
       int PQisthreadsafe();
      </synopsis>
     </para>
6855

6856 6857 6858 6859 6860 6861 6862
     <para>
      Returns 1 if the <application>libpq</application> is thread-safe
      and 0 if it is not.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
6863

6864 6865 6866 6867 6868 6869 6870
  <para>
   One thread restriction is that no two threads attempt to manipulate
   the same <structname>PGconn</> object at the same time. In particular,
   you cannot issue concurrent commands from different threads through
   the same connection object. (If you need to run concurrent commands,
   use multiple connections.)
  </para>
6871

6872 6873 6874 6875
  <para>
   <structname>PGresult</> objects are read-only after creation, and so
   can be passed around freely between threads.
  </para>
6876

6877 6878 6879 6880 6881 6882 6883 6884
  <para>
   The deprecated functions <function>PQrequestCancel</function> and
   <function>PQoidStatus</function> are not thread-safe and should not be
   used in multithread programs.  <function>PQrequestCancel</function>
   can be replaced by <function>PQcancel</function>.
   <function>PQoidStatus</function> can be replaced by
   <function>PQoidValue</function>.
  </para>
6885

6886 6887 6888 6889 6890 6891 6892 6893
  <para>
   If you are using Kerberos inside your application (in addition to inside
   <application>libpq</application>), you will need to do locking around
   Kerberos calls because Kerberos functions are not thread-safe.  See
   function <function>PQregisterThreadLock</> in the
   <application>libpq</application> source code for a way to do cooperative
   locking between <application>libpq</application> and your application.
  </para>
6894

6895 6896 6897 6898 6899 6900 6901 6902
  <para>
   If you experience problems with threaded applications, run the program
   in <filename>src/tools/thread</> to see if your platform has
   thread-unsafe functions.  This program is run by
   <filename>configure</filename>, but for binary distributions your
   library might not match the library used to build the binaries.
  </para>
 </sect1>
6903 6904


6905
 <sect1 id="libpq-build">
6906
  <title>Building <application>libpq</application> Programs</title>
6907

Peter Eisentraut's avatar
Peter Eisentraut committed
6908 6909 6910 6911
  <indexterm zone="libpq-build">
   <primary>compiling</primary>
   <secondary>libpq applications</secondary>
  </indexterm>
6912

6913
  <para>
6914
   To build (i.e., compile and link) a program using
6915 6916
   <application>libpq</application> you need to do all of the following
   things:
6917

6918 6919 6920 6921 6922
   <itemizedlist>
    <listitem>
     <para>
      Include the <filename>libpq-fe.h</filename> header file:
<programlisting>
Peter Eisentraut's avatar
Peter Eisentraut committed
6923
#include &lt;libpq-fe.h&gt;
6924
</programlisting>
6925
      If you failed to do that then you will normally get error messages
6926
      from your compiler similar to:
6927 6928 6929 6930 6931 6932 6933 6934 6935 6936
<screen>
foo.c: In function `main':
foo.c:34: `PGconn' undeclared (first use in this function)
foo.c:35: `PGresult' undeclared (first use in this function)
foo.c:54: `CONNECTION_BAD' undeclared (first use in this function)
foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function)
foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function)
</screen>
     </para>
    </listitem>
6937

6938 6939
    <listitem>
     <para>
6940
      Point your compiler to the directory where the <productname>PostgreSQL</> header
6941 6942 6943 6944 6945 6946 6947 6948 6949 6950 6951 6952 6953 6954 6955
      files were installed, by supplying the
      <literal>-I<replaceable>directory</replaceable></literal> option
      to your compiler.  (In some cases the compiler will look into
      the directory in question by default, so you can omit this
      option.)  For instance, your compile command line could look
      like:
<programlisting>
cc -c -I/usr/local/pgsql/include testprog.c
</programlisting>
      If you are using makefiles then add the option to the
      <varname>CPPFLAGS</varname> variable:
<programlisting>
CPPFLAGS += -I/usr/local/pgsql/include
</programlisting>
     </para>
6956

6957 6958 6959 6960
     <para>
      If there is any chance that your program might be compiled by
      other users then you should not hardcode the directory location
      like that.  Instead, you can run the utility
Peter Eisentraut's avatar
Peter Eisentraut committed
6961 6962 6963
      <command>pg_config</command><indexterm><primary>pg_config</><secondary
      sortas="libpq">with libpq</></> to find out where the header
      files are on the local system:
6964 6965 6966 6967 6968
<screen>
<prompt>$</prompt> pg_config --includedir
<computeroutput>/usr/local/include</computeroutput>
</screen>
     </para>
6969

6970 6971
     <para>
      Failure to specify the correct option to the compiler will
6972
      result in an error message such as:
6973 6974 6975 6976 6977
<screen>
testlibpq.c:8:22: libpq-fe.h: No such file or directory
</screen>
     </para>
    </listitem>
6978

6979 6980 6981
    <listitem>
     <para>
      When linking the final program, specify the option
6982 6983 6984 6985 6986
      <literal>-lpq</literal> so that the <application>libpq</application>
      library gets pulled in, as well as the option
      <literal>-L<replaceable>directory</replaceable></literal> to point
      the compiler to the directory where the
      <application>libpq</application> library resides.  (Again, the
6987 6988 6989 6990 6991 6992 6993
      compiler will search some directories by default.)  For maximum
      portability, put the <option>-L</option> option before the
      <option>-lpq</option> option.  For example:
<programlisting>
cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq
</programlisting>
     </para>
6994

6995 6996 6997 6998 6999 7000 7001 7002
     <para>
      You can find out the library directory using
      <command>pg_config</command> as well:
<screen>
<prompt>$</prompt> pg_config --libdir
<computeroutput>/usr/local/pgsql/lib</computeroutput>
</screen>
     </para>
7003

7004
     <para>
7005
      Error messages that point to problems in this area could look like
7006
      the following:
7007 7008 7009 7010 7011 7012 7013 7014 7015 7016
<screen>
testlibpq.o: In function `main':
testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin'
testlibpq.o(.text+0x71): undefined reference to `PQstatus'
testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage'
</screen>
      This means you forgot <option>-lpq</option>.
<screen>
/usr/bin/ld: cannot find -lpq
</screen>
7017 7018
      This means you forgot the <option>-L</option> option or did not
      specify the right directory.
7019 7020 7021 7022
     </para>
    </listitem>
   </itemizedlist>
  </para>
7023

7024
 </sect1>
7025 7026


7027 7028
 <sect1 id="libpq-example">
  <title>Example Programs</title>
7029

7030 7031 7032 7033 7034 7035
  <para>
   These examples and others can be found in the
   directory <filename>src/test/examples</filename> in the source code
   distribution.
  </para>

7036
  <example id="libpq-example-1">
7037
   <title><application>libpq</application> Example Program 1</title>
7038

7039
<programlisting>
7040
<![CDATA[
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7041
/*
7042
 * testlibpq.c
7043
 *
7044
 *      Test the C version of libpq, the PostgreSQL frontend library.
7045
 */
7046 7047
#include <stdio.h>
#include <stdlib.h>
7048
#include <libpq-fe.h>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7049

7050
static void
7051
exit_nicely(PGconn *conn)
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7052
{
7053 7054
    PQfinish(conn);
    exit(1);
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7055 7056
}

7057 7058
int
main(int argc, char **argv)
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7059
{
7060 7061 7062 7063 7064 7065 7066 7067 7068 7069 7070 7071
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    int         nFields;
    int         i,
                j;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
7072
    if (argc > 1)
7073 7074 7075 7076 7077 7078 7079 7080 7081 7082 7083 7084 7085 7086 7087 7088 7089 7090 7091 7092 7093 7094 7095 7096 7097 7098 7099
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /*
     * Our test case here involves using a cursor, for which we must be inside
     * a transaction block.  We could do the whole thing with a single
     * PQexec() of "select * from pg_database", but that's too trivial to make
     * a good example.
     */

    /* Start a transaction block */
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
Peter Eisentraut's avatar
Peter Eisentraut committed
7100
        PQclear(res);
7101 7102 7103 7104 7105 7106 7107 7108 7109 7110 7111 7112 7113 7114 7115 7116
        exit_nicely(conn);
    }

    /*
     * Should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    /*
     * Fetch rows from pg_database, the system catalog of databases
     */
    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
Peter Eisentraut's avatar
Peter Eisentraut committed
7117
        PQclear(res);
7118 7119 7120 7121 7122 7123 7124 7125 7126 7127 7128
        exit_nicely(conn);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in myportal");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
Peter Eisentraut's avatar
Peter Eisentraut committed
7129

7130 7131
    /* first, print out the attribute names */
    nFields = PQnfields(res);
7132
    for (i = 0; i < nFields; i++)
7133 7134
        printf("%-15s", PQfname(res, i));
    printf("\n\n");
Peter Eisentraut's avatar
Peter Eisentraut committed
7135

7136
    /* next, print out the rows */
7137
    for (i = 0; i < PQntuples(res); i++)
7138
    {
7139
        for (j = 0; j < nFields; j++)
7140 7141 7142
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }
Peter Eisentraut's avatar
Peter Eisentraut committed
7143

7144
    PQclear(res);
Peter Eisentraut's avatar
Peter Eisentraut committed
7145

7146 7147 7148
    /* close the portal ... we don't bother to check for errors ... */
    res = PQexec(conn, "CLOSE myportal");
    PQclear(res);
Peter Eisentraut's avatar
Peter Eisentraut committed
7149

7150 7151 7152
    /* end the transaction */
    res = PQexec(conn, "END");
    PQclear(res);
Peter Eisentraut's avatar
Peter Eisentraut committed
7153

7154 7155
    /* close the connection to the database and cleanup */
    PQfinish(conn);
Peter Eisentraut's avatar
Peter Eisentraut committed
7156

7157
    return 0;
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7158
}
7159
]]>
7160
</programlisting>
7161
  </example>
7162

7163
  <example id="libpq-example-2">
7164
   <title><application>libpq</application> Example Program 2</title>
7165

7166
<programlisting>
7167
<![CDATA[
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7168
/*
7169
 * testlibpq2.c
7170
 *      Test of the asynchronous notification interface
7171
 *
7172 7173
 * Start this program, then from psql in another window do
 *   NOTIFY TBL2;
7174
 * Repeat four times to get this program to exit.
7175
 *
7176
 * Or, if you want to get fancy, try this:
7177 7178
 * populate a database with the following commands
 * (provided in src/test/examples/testlibpq2.sql):
7179
 *
7180
 *   CREATE TABLE TBL1 (i int4);
7181
 *
7182
 *   CREATE TABLE TBL2 (i int4);
7183
 *
7184
 *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
7185
 *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
7186
 *
7187
 * and do this four times:
7188
 *
7189
 *   INSERT INTO TBL1 VALUES (10);
7190
 */
7191 7192 7193 7194 7195
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.h>
7196
#include <libpq-fe.h>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7197

7198
static void
7199
exit_nicely(PGconn *conn)
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7200
{
7201 7202
    PQfinish(conn);
    exit(1);
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7203 7204
}

7205 7206
int
main(int argc, char **argv)
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7207
{
7208 7209 7210 7211 7212 7213 7214 7215 7216 7217 7218
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    PGnotify   *notify;
    int         nnotifies;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
7219
    if (argc > 1)
7220 7221 7222 7223 7224 7225 7226 7227 7228 7229 7230 7231 7232 7233 7234 7235 7236 7237 7238 7239 7240 7241 7242 7243 7244 7245 7246 7247 7248 7249 7250 7251 7252 7253
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /*
     * Issue LISTEN command to enable notifications from the rule's NOTIFY.
     */
    res = PQexec(conn, "LISTEN TBL2");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    /*
     * should PQclear PGresult whenever it is no longer needed to avoid memory
     * leaks
     */
    PQclear(res);

    /* Quit after four notifies are received. */
    nnotifies = 0;
7254
    while (nnotifies < 4)
7255
    {
Peter Eisentraut's avatar
Peter Eisentraut committed
7256
        /*
7257 7258 7259
         * Sleep until something happens on the connection.  We use select(2)
         * to wait for input, but you could also use poll() or similar
         * facilities.
Peter Eisentraut's avatar
Peter Eisentraut committed
7260
         */
7261 7262
        int         sock;
        fd_set      input_mask;
Peter Eisentraut's avatar
Peter Eisentraut committed
7263

7264
        sock = PQsocket(conn);
Peter Eisentraut's avatar
Peter Eisentraut committed
7265

7266
        if (sock < 0)
7267
            break;              /* shouldn't happen */
Peter Eisentraut's avatar
Peter Eisentraut committed
7268

7269 7270
        FD_ZERO(&input_mask);
        FD_SET(sock, &input_mask);
7271

7272
        if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
Peter Eisentraut's avatar
Peter Eisentraut committed
7273
        {
7274 7275
            fprintf(stderr, "select() failed: %s\n", strerror(errno));
            exit_nicely(conn);
Peter Eisentraut's avatar
Peter Eisentraut committed
7276 7277
        }

7278 7279 7280
        /* Now check for input */
        PQconsumeInput(conn);
        while ((notify = PQnotifies(conn)) != NULL)
Peter Eisentraut's avatar
Peter Eisentraut committed
7281
        {
7282 7283
            fprintf(stderr,
                    "ASYNC NOTIFY of '%s' received from backend pid %d\n",
7284
                    notify->relname, notify->be_pid);
7285 7286
            PQfreemem(notify);
            nnotifies++;
Peter Eisentraut's avatar
Peter Eisentraut committed
7287
        }
7288
    }
Peter Eisentraut's avatar
Peter Eisentraut committed
7289

7290
    fprintf(stderr, "Done.\n");
Peter Eisentraut's avatar
Peter Eisentraut committed
7291

7292 7293
    /* close the connection to the database and cleanup */
    PQfinish(conn);
Peter Eisentraut's avatar
Peter Eisentraut committed
7294

7295
    return 0;
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7296
}
7297
]]>
7298
</programlisting>
7299
  </example>
7300

7301
  <example id="libpq-example-3">
7302
   <title><application>libpq</application> Example Program 3</>
7303

7304
<programlisting>
7305
<![CDATA[
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7306
/*
7307
 * testlibpq3.c
7308
 *      Test out-of-line parameters and binary I/O.
7309
 *
7310 7311
 * Before running this, populate a database with the following commands
 * (provided in src/test/examples/testlibpq3.sql):
7312
 *
7313
 * CREATE TABLE test1 (i int4, t text, b bytea);
7314
 *
7315 7316
 * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
 * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
7317
 *
7318
 * The expected output is:
7319
 *
7320 7321 7322 7323
 * tuple 0: got
 *  i = (4 bytes) 1
 *  t = (11 bytes) 'joe's place'
 *  b = (5 bytes) \000\001\002\003\004
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7324
 *
7325 7326 7327 7328
 * tuple 0: got
 *  i = (4 bytes) 2
 *  t = (8 bytes) 'ho there'
 *  b = (5 bytes) \004\003\002\001\000
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7329
 */
7330 7331 7332 7333
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
7334
#include <libpq-fe.h>
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7335

7336
/* for ntohl/htonl */
7337 7338
#include <netinet/in.h>
#include <arpa/inet.h>
7339 7340 7341


static void
7342
exit_nicely(PGconn *conn)
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7343
{
7344 7345
    PQfinish(conn);
    exit(1);
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7346 7347
}

7348 7349 7350 7351 7352 7353 7354
/*
 * This function prints a query result that is a binary-format fetch from
 * a table defined as in the comment above.  We split it out because the
 * main() function uses it twice.
 */
static void
show_binary_results(PGresult *res)
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7355
{
7356 7357 7358 7359 7360 7361 7362 7363 7364 7365 7366
    int         i,
                j;
    int         i_fnum,
                t_fnum,
                b_fnum;

    /* Use PQfnumber to avoid assumptions about field order in result */
    i_fnum = PQfnumber(res, "i");
    t_fnum = PQfnumber(res, "t");
    b_fnum = PQfnumber(res, "b");

7367
    for (i = 0; i < PQntuples(res); i++)
7368 7369 7370 7371 7372 7373 7374 7375 7376 7377 7378
    {
        char       *iptr;
        char       *tptr;
        char       *bptr;
        int         blen;
        int         ival;

        /* Get the field values (we ignore possibility they are null!) */
        iptr = PQgetvalue(res, i, i_fnum);
        tptr = PQgetvalue(res, i, t_fnum);
        bptr = PQgetvalue(res, i, b_fnum);
Peter Eisentraut's avatar
Peter Eisentraut committed
7379 7380

        /*
7381 7382
         * The binary representation of INT4 is in network byte order, which
         * we'd better coerce to the local byte order.
Peter Eisentraut's avatar
Peter Eisentraut committed
7383
         */
7384
        ival = ntohl(*((uint32_t *) iptr));
Peter Eisentraut's avatar
Peter Eisentraut committed
7385 7386

        /*
7387 7388 7389 7390 7391 7392
         * The binary representation of TEXT is, well, text, and since libpq
         * was nice enough to append a zero byte to it, it'll work just fine
         * as a C string.
         *
         * The binary representation of BYTEA is a bunch of bytes, which could
         * include embedded nulls so we have to pay attention to field length.
Peter Eisentraut's avatar
Peter Eisentraut committed
7393
         */
7394 7395 7396 7397 7398 7399 7400 7401
        blen = PQgetlength(res, i, b_fnum);

        printf("tuple %d: got\n", i);
        printf(" i = (%d bytes) %d\n",
               PQgetlength(res, i, i_fnum), ival);
        printf(" t = (%d bytes) '%s'\n",
               PQgetlength(res, i, t_fnum), tptr);
        printf(" b = (%d bytes) ", blen);
7402
        for (j = 0; j < blen; j++)
7403 7404 7405 7406
            printf("\\%03o", bptr[j]);
        printf("\n\n");
    }
}
Peter Eisentraut's avatar
Peter Eisentraut committed
7407

7408 7409 7410 7411 7412 7413 7414 7415 7416 7417 7418 7419 7420 7421 7422 7423
int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    const char *paramValues[1];
    int         paramLengths[1];
    int         paramFormats[1];
    uint32_t    binaryIntVal;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
7424
    if (argc > 1)
7425 7426 7427 7428 7429 7430 7431 7432 7433 7434 7435 7436 7437 7438 7439 7440 7441 7442 7443 7444 7445 7446 7447 7448 7449 7450 7451 7452 7453 7454 7455 7456 7457 7458 7459 7460 7461 7462 7463 7464 7465 7466 7467 7468 7469 7470 7471 7472 7473 7474 7475 7476 7477 7478 7479 7480 7481 7482 7483 7484 7485 7486 7487
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /*
     * The point of this program is to illustrate use of PQexecParams() with
     * out-of-line parameters, as well as binary transmission of data.
     *
     * This first example transmits the parameters as text, but receives the
     * results in binary format.  By using out-of-line parameters we can
     * avoid a lot of tedious mucking about with quoting and escaping, even
     * though the data is text.  Notice how we don't have to do anything
     * special with the quote mark in the parameter value.
     */

    /* Here is our out-of-line parameter value */
    paramValues[0] = "joe's place";

    res = PQexecParams(conn,
                       "SELECT * FROM test1 WHERE t = $1",
                       1,       /* one param */
                       NULL,    /* let the backend deduce param type */
                       paramValues,
                       NULL,    /* don't need param lengths since text */
                       NULL,    /* default to all text params */
                       1);      /* ask for binary results */

    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    show_binary_results(res);

    PQclear(res);

    /*
     * In this second example we transmit an integer parameter in binary
     * form, and again retrieve the results in binary form.
     *
     * Although we tell PQexecParams we are letting the backend deduce
     * parameter type, we really force the decision by casting the parameter
     * symbol in the query text.  This is a good safety measure when sending
     * binary parameters.
     */

    /* Convert integer value "2" to network byte order */
    binaryIntVal = htonl((uint32_t) 2);

    /* Set up parameter arrays for PQexecParams */
7488
    paramValues[0] = (char *) &binaryIntVal;
7489 7490 7491 7492 7493 7494 7495 7496 7497 7498 7499 7500 7501 7502 7503 7504 7505 7506
    paramLengths[0] = sizeof(binaryIntVal);
    paramFormats[0] = 1;        /* binary */

    res = PQexecParams(conn,
                       "SELECT * FROM test1 WHERE i = $1::int4",
                       1,       /* one param */
                       NULL,    /* let the backend deduce param type */
                       paramValues,
                       paramLengths,
                       paramFormats,
                       1);      /* ask for binary results */

    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
Peter Eisentraut's avatar
Peter Eisentraut committed
7507

7508
    show_binary_results(res);
Peter Eisentraut's avatar
Peter Eisentraut committed
7509

7510
    PQclear(res);
Peter Eisentraut's avatar
Peter Eisentraut committed
7511

7512 7513
    /* close the connection to the database and cleanup */
    PQfinish(conn);
Peter Eisentraut's avatar
Peter Eisentraut committed
7514

7515
    return 0;
Thomas G. Lockhart's avatar
Thomas G. Lockhart committed
7516
}
7517
]]>
7518
</programlisting>
7519
  </example>
7520

7521
 </sect1>
7522
</chapter>