spi.sgml

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

<!--
$PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.43.2.1 2005/12/07 15:39:32 tgl Exp $
-->

<chapter id="spi">
 <title>Server Programming Interface</title>

 <indexterm zone="spi">
  <primary>SPI</primary>
 </indexterm>

 <para>
  The <firstterm>Server Programming Interface</firstterm>
  (<acronym>SPI</acronym>) gives writers of user-defined
  <acronym>C</acronym> functions the ability to run
  <acronym>SQL</acronym> commands inside their functions.
  <acronym>SPI</acronym> is a set of
  interface functions to simplify access to the parser, planner,
  optimizer, and executor. <acronym>SPI</acronym> also does some
  memory management.
 </para>

 <note>
  <para>
   The available procedural languages provide various means to
   execute SQL commands from procedures.  Most of these facilities are
   based on SPI, so this documentation might be of use for users
   of those languages as well.
  </para>
 </note>

 <para>
  To avoid misunderstanding we'll use the term <quote>function</quote>
  when we speak of <acronym>SPI</acronym> interface functions and
  <quote>procedure</quote> for a user-defined C-function that is
  using <acronym>SPI</acronym>.
 </para>

 <para>
  Note that if a command invoked via SPI fails, then control will not be
  returned to your procedure.  Rather, the
  transaction or subtransaction in which your procedure executes will be
  rolled back.  (This may seem surprising given that the SPI functions mostly
  have documented error-return conventions.  Those conventions only apply
  for errors detected within the SPI functions themselves, however.)
  It is possible to recover control after an error by establishing your own
  subtransaction surrounding SPI calls that might fail.  This is not currently
  documented because the mechanisms required are still in flux.
 </para>

 <para>
  <acronym>SPI</acronym> functions return a nonnegative result on
  success (either via a returned integer value or in the global
  variable <varname>SPI_result</varname>, as described below).  On
  error, a negative result or <symbol>NULL</symbol> will be returned.
 </para>

 <para>
  Source code files that use SPI must include the header file
  <filename>executor/spi.h</filename>.
 </para>


<sect1 id="spi-interface">
 <title>Interface Functions</title>

 <refentry id="spi-spi-connect">
  <refmeta>
   <refentrytitle>SPI_connect</refentrytitle>
  </refmeta>

  <refnamediv>
   <refname>SPI_connect</refname>
   <refpurpose>connect a procedure to the SPI manager</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_connect</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
int SPI_connect(void)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_connect</function> opens a connection from a
   procedure invocation to the SPI manager.  You must call this
   function if you want to execute commands through SPI.  Some utility
   SPI functions may be called from unconnected procedures.
  </para>

  <para>
   If your procedure is already connected,
   <function>SPI_connect</function> will return the error code
   <returnvalue>SPI_ERROR_CONNECT</returnvalue>.  This could happen if
   a procedure that has called <function>SPI_connect</function>
   directly calls another procedure that calls
   <function>SPI_connect</function>.  While recursive calls to the
   <acronym>SPI</acronym> manager are permitted when an SQL command
   called through SPI invokes another function that uses
   <acronym>SPI</acronym>, directly nested calls to
   <function>SPI_connect</function> and
   <function>SPI_finish</function> are forbidden.
   (But see <function>SPI_push</function> and <function>SPI_pop</function>.)
  </para>
 </refsect1>

 <refsect1>
  <title>Return Value</title>

  <variablelist>
   <varlistentry>
    <term><symbol>SPI_OK_CONNECT</symbol></term>
    <listitem>
     <para>
      on success
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><symbol>SPI_ERROR_CONNECT</symbol></term>
    <listitem>
     <para>
      on error
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>
</refentry>

<!-- *********************************************** -->

<refentry id="spi-spi-finish">
 <refmeta>
  <refentrytitle>SPI_finish</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_finish</refname>
  <refpurpose>disconnect a procedure from the SPI manager</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_finish</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
int SPI_finish(void)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_finish</function> closes an existing connection to
   the SPI manager.  You must call this function after completing the
   SPI operations needed during your procedure's current invocation.
   You do not need to worry about making this happen, however, if you
   abort the transaction via <literal>elog(ERROR)</literal>.  In that
   case SPI will clean itself up automatically.
  </para>

  <para>
   If <function>SPI_finish</function> is called without having a valid
   connection, it will return <symbol>SPI_ERROR_UNCONNECTED</symbol>.
   There is no fundamental problem with this; it means that the SPI
   manager has nothing to do.
  </para>
 </refsect1>

 <refsect1>
  <title>Return Value</title>

  <variablelist>
   <varlistentry>
    <term><symbol>SPI_OK_FINISH</symbol></term>
    <listitem>
     <para>
      if properly disconnected
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
    <listitem>
     <para>
      if called from an unconnected procedure
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>
</refentry>

<!-- *********************************************** -->

<refentry id="spi-spi-push">
 <refmeta>
  <refentrytitle>SPI_push</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_push</refname>
  <refpurpose>push SPI stack to allow recursive SPI usage</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_push</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void SPI_push(void)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_push</function> should be called before executing another
   procedure that might itself wish to use SPI.
   After <function>SPI_push</function>, SPI is no longer in a
   <quote>connected</> state, and SPI function calls will be rejected unless
   a fresh <function>SPI_connect</function> is done.  This ensures a clean
   separation between your procedure's SPI state and that of another procedure
   you call.  After the other procedure returns, call
   <function>SPI_pop</function> to restore access to your own SPI state.
  </para>

  <para>
   Note that <function>SPI_execute</function> and related functions
   automatically do the equivalent of <function>SPI_push</function> before
   passing control back to the SQL execution engine, so it is not necessary
   for you to worry about this when using those functions.
   Only when you are directly calling arbitrary code that might contain
   <function>SPI_connect</function> calls do you need to issue
   <function>SPI_push</function> and <function>SPI_pop</function>.
  </para>
 </refsect1>

</refentry>

<!-- *********************************************** -->

<refentry id="spi-spi-pop">
 <refmeta>
  <refentrytitle>SPI_pop</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_pop</refname>
  <refpurpose>pop SPI stack to return from recursive SPI usage</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_pop</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void SPI_pop(void)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_pop</function> pops the previous environment from the 
   SPI call stack.  See <function>SPI_push</function>.
  </para>
 </refsect1>

</refentry>

<!-- *********************************************** -->

<refentry id="spi-spi-execute">
 <refmeta>
  <refentrytitle>SPI_execute</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_execute</refname>
  <refpurpose>execute a command</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_execute</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
int SPI_execute(const char * <parameter>command</parameter>, bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_execute</function> executes the specified SQL command
   for <parameter>count</parameter> rows.  If <parameter>read_only</parameter>
   is <literal>true</>, the command must be read-only, and execution overhead
   is somewhat reduced.
  </para>

  <para>
   This function may only be called from a connected procedure.
  </para>

  <para>
   If <parameter>count</parameter> is zero then the command is executed
   for all rows that it applies to.  If <parameter>count</parameter>
   is greater than 0, then the number of rows for which the command
   will be executed is restricted (much like a
   <literal>LIMIT</literal> clause). For example,
<programlisting>
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
</programlisting>
   will allow at most 5 rows to be inserted into the table.
  </para>

  <para>
   You may pass multiple commands in one string.
   <function>SPI_execute</function> returns the
   result for the command executed last.  The <parameter>count</parameter>
   limit applies to each command separately, but it is not applied to
   hidden commands generated by rules.
  </para>

  <para>
   When <parameter>read_only</parameter> is <literal>false</>,
   <function>SPI_execute</function> increments the command
   counter and computes a new <firstterm>snapshot</> before executing each
   command in the string.  The snapshot does not actually change if the
   current transaction isolation level is <literal>SERIALIZABLE</>, but in
   <literal>READ COMMITTED</> mode the snapshot update allows each command to
   see the results of newly committed transactions from other sessions.
   This is essential for consistent behavior when the commands are modifying
   the database.
  </para>

  <para>
   When <parameter>read_only</parameter> is <literal>true</>,
   <function>SPI_execute</function> does not update either the snapshot
   or the command counter, and it allows only plain <command>SELECT</>
   commands to appear in the command string.  The commands are executed
   using the snapshot previously established for the surrounding query.
   This execution mode is somewhat faster than the read/write mode due
   to eliminating per-command overhead.  It also allows genuinely
   <firstterm>stable</> functions to be built: since successive executions
   will all use the same snapshot, there will be no change in the results.
  </para>

  <para>
   It is generally unwise to mix read-only and read-write commands within
   a single function using SPI; that could result in very confusing behavior,
   since the read-only queries would not see the results of any database
   updates done by the read-write queries.
  </para>

  <para>
   The actual number of rows for which the (last) command was executed
   is returned in the global variable <varname>SPI_processed</varname>
   (unless the return value of the function is
   <symbol>SPI_OK_UTILITY</symbol>).  If the return value of the
   function is <symbol>SPI_OK_SELECT</symbol> then you may use the
   global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
   access the result rows.
  </para>

  <para>
   The structure <structname>SPITupleTable</structname> is defined
   thus:
<programlisting>
typedef struct
{
    MemoryContext tuptabcxt;    /* memory context of result table */
    uint32      alloced;        /* number of alloced vals */
    uint32      free;           /* number of free vals */
    TupleDesc   tupdesc;        /* row descriptor */
    HeapTuple  *vals;           /* rows */
} SPITupleTable;
</programlisting>
   <structfield>vals</> is an array of pointers to rows.  (The number
   of valid entries is given by <varname>SPI_processed</varname>.)
   <structfield>tupdesc</> is a row descriptor which you may pass to
   SPI functions dealing with rows.  <structfield>tuptabcxt</>,
   <structfield>alloced</>, and <structfield>free</> are internal
   fields not intended for use by SPI callers.
  </para>

  <para>
   <function>SPI_finish</function> frees all
   <structname>SPITupleTable</>s allocated during the current
   procedure.  You can free a particular result table earlier, if you
   are done with it, by calling <function>SPI_freetuptable</function>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>const char * <parameter>command</parameter></literal></term>
    <listitem>
     <para>
      string containing command to execute
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>bool <parameter>read_only</parameter></literal></term>
    <listitem>
     <para>
      <literal>true</> for read-only execution
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>long <parameter>count</parameter></literal></term>
    <listitem>
     <para>
      maximum number of rows to process or return
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Return Value</title>

  <para>
   If the execution of the command was successful then one of the
   following (nonnegative) values will be returned:

   <variablelist>
    <varlistentry>
     <term><symbol>SPI_OK_SELECT</symbol></term>
     <listitem>
      <para>
       if a <command>SELECT</command> (but not <command>SELECT
       INTO</>) was executed
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_OK_SELINTO</symbol></term>
     <listitem>
      <para>
       if a <command>SELECT INTO</command> was executed
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_OK_DELETE</symbol></term>
     <listitem>
      <para>
       if a <command>DELETE</command> was executed
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_OK_INSERT</symbol></term>
     <listitem>
      <para>
       if an <command>INSERT</command> was executed
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_OK_UPDATE</symbol></term>
     <listitem>
      <para>
       if an <command>UPDATE</command> was executed
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>