libpq.sgml

PostgreSQL7.4.6 for Linux

</varlistentry>
</variablelist>
</para>

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

<variablelist>
<varlistentry>
<term><function>PQstatus</function><indexterm><primary>PQstatus</></></term>
<listitem>
<para>
         Returns the status of the connection. 
<synopsis>
ConnStatusType PQstatus(const PGconn *conn);
</synopsis>
</para>

      <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>

      <para>
       See the entry for <function>PQconnectStart</> and <function>PQconnectPoll</> with regards
       to other status codes
       that might be seen.
      </para>
     </listitem>
    </varlistentry>

<varlistentry>
<term><function>PQtransactionStatus</function><indexterm><primary>PQtransactionStatus</></></term>
<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>
<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>
</listitem>
</varlistentry>

<varlistentry>
<term><function>PQparameterStatus</function><indexterm><primary>PQparameterStatus</></></term>
<listitem>
<para>
         Looks up a current parameter setting of the server.
<synopsis>
const char *PQparameterStatus(const PGconn *conn, const char *paramName);
</synopsis>

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>

<para>
Parameters reported as of the current release include
<literal>server_version</> (cannot change after startup);
<literal>client_encoding</>,
<literal>is_superuser</>,
<literal>session_authorization</literal>, and
<literal>DateStyle</>.
</para>

<para>
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</>.
Applications are encouraged to use <function>PQparameterStatus</>
rather than 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</>.)
</para>
</listitem>
</varlistentry>

<varlistentry>
<term><function>PQprotocolVersion</function><indexterm><primary>PQprotocolVersion</></></term>
<listitem>
<para>
         Interrogates the frontend/backend protocol being used.
<synopsis>
int PQprotocolVersion(const PGconn *conn);
</synopsis>
Applications may 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 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>

    <varlistentry>
     <term><function>PQerrorMessage</function><indexterm><primary>PQerrorMessage</></></term>
     <listitem>
      <para>
       <indexterm><primary>error message</></>
       Returns the error message most recently generated by
       an operation on the connection.
<synopsis>
char *PQerrorMessage(const PGconn* conn);
</synopsis>
      </para>

      <para>
       Nearly all <application>libpq</> functions will set a message for
       <function>PQerrorMessage</function> if they fail.
       Note that by <application>libpq</application> convention, a nonempty
       <function>PQerrorMessage</function> result will
       include a trailing newline.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><function>PQsocket</function><indexterm><primary>PQsocket</></></term>
     <listitem>
      <para>
       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
       currently open.  (This will not change during normal operation,
       but could change during connection setup or reset.)
<synopsis>
int PQsocket(const PGconn *conn);
</synopsis>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><function>PQbackendPID</function><indexterm><primary>PQbackendPID</></></term>
     <listitem>
      <para>
       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.
<synopsis>
int PQbackendPID(const PGconn *conn);
</synopsis>
</para>

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

    <varlistentry>
     <term><function>PQgetssl</function><indexterm><primary>PQgetssl</></></term>
     <listitem>
      <para>
       <indexterm><primary>SSL</><secondary sortas="libpq">in libpq</secondary></indexterm>
       Returns the SSL structure used in the connection, or null
       if SSL is not in use. 
<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.
      </para>
      <para>
       You must define <symbol>USE_SSL</symbol> in order to get the
       prototype for this function. Doing this will also 
       automatically include <filename>ssl.h</filename> from <productname>OpenSSL</productname>.
      </para>
     </listitem>
    </varlistentry>

</variablelist>
</para>

</sect1>

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

<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>

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

<para>
<variablelist>
<varlistentry>
<term><function>PQexec</function><indexterm><primary>PQexec</></></term>
<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 the error.
</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</></></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.
<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>
</para>

<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>

<para>
If parameters are used, they are referred to in the command string
as <literal>$1</>, <literal>$2</>, etc.
<parameter>nParams</> is the number of parameters supplied; it is the length
of the arrays <parameter>paramTypes[]</>, <parameter>paramValues[]</>,
<parameter>paramLengths[]</>, and <parameter>paramFormats[]</>.  (The
array pointers may 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.
<parameter>paramValues[]</> 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).
<parameter>paramLengths[]</> specifies the actual data lengths of
binary-format parameters.  It is ignored for null parameters and text-format
parameters.  The array pointer may be null when there are no binary
parameters.
<parameter>paramFormats[]</> specifies whether parameters are text (put a zero
in the array) or binary (put a one in the array).  If the array pointer is
null then all parameters are presumed to be text.
<parameter>resultFormat</> is 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>

The primary advantage of <function>PQexecParams</> over <function>PQexec</>
is that parameter values may be separated from the command string, thus
avoiding the need for tedious and error-prone quoting and escaping.

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>

<para>
<variablelist>
<varlistentry>
<term><function>PQexecPrepared</function><indexterm><primary>PQexecPrepared</></></term>
<listitem>
<para>
          Sends a request to execute a prepared statement with given
          parameters, and waits for the result.
<synopsis>
PGresult *PQexecPrepared(PGconn *conn,
                         const char *stmtName,
                         int nParams,
                         const char * const *paramValues,
                         const int *paramLengths,
                         const int *paramFormats,
                         int resultFormat);
</synopsis>
</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.
<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>
</variablelist>

Presently, prepared statements for use with <function>PQexecPrepared</>