libpq.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

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>

<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,
<programlisting>
select * from mytable where x = $1::bigint;
</programlisting>
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</></></term>
<listitem>
<para>
          Submits a request to create a prepared statement with the
          given parameters, and waits for completion.
<synopsis>
PGresult *PQprepare(PGconn *conn,
                    const char *stmtName,
                    const char *query,
                    int nParams,
                    const Oid *paramTypes);
</synopsis>
</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>

<para>
The function creates a prepared statement named <parameter>stmtName</>
from the <parameter>query</> string, which must contain a single SQL command.
<parameter>stmtName</> may 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
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.  Also, the query may use parameter symbols with numbers higher than
<parameter>nParams</>; data types will be inferred for these symbols as
well.
</para>

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

<para>
At present, there is no way to determine the actual data type inferred for
any parameters whose types are not specified in <parameter>paramTypes[]</>.
This is a <application>libpq</> omission that will probably be rectified
in a future release.
</para>
</listitem>
</varlistentry>
</variablelist>

Prepared statements for use with <function>PQexecPrepared</> can also
be created by executing SQL <xref linkend="sql-prepare"
endterm="sql-prepare-title"> statements.  (But <function>PQprepare</>
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
linkend="sql-deallocate" endterm="sql-deallocate-title"> statement can
be used for that purpose.
</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.
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>
</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</></></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> may 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</></></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</></></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