spi.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/spi.sgml,v 1.28 2003/10/22 22:28:10 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.  Some of these are based on or
   modelled after 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 during the execution of a procedure the transaction is
  aborted because of an error in a command, then control will not be
  returned to your procedure.  Rather, all work will be rolled back
  and the server will wait for the next command from the client.  A
  related restriction is the inability to execute
  <command>BEGIN</command>, <command>COMMIT</command>, and
  <command>ROLLBACK</command> (transaction control statements) inside
  a procedure.  Both of these restrictions will probably be changed in
  the future.
 </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.
  </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-exec">
 <refmeta>
  <refentrytitle>SPI_exec</refentrytitle>
 </refmeta>

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

 <indexterm><primary>SPI_exec</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
int SPI_exec(const char * <parameter>command</parameter>, int <parameter>count</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_exec</function> executes the specified SQL command
   for <parameter>count</parameter> rows.
  </para>

  <para>
   This function should only be called from a connected procedure.  If
   <parameter>count</parameter> is zero then it executes the command
   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_exec("INSERT INTO tab SELECT * FROM tab", 5);
</programlisting>
   will allow at most 5 rows to be inserted into the table.
  </para>

  <para>
   You may pass multiple commands in one string, and the command may
   be rewritten by rules. <function>SPI_exec</function> returns the
   result for the command executed last.
  </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 the use
   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>int <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>
     <term><symbol>SPI_OK_UTILITY</symbol></term>
     <listitem>
      <para>
       if a utility command (e.g., <command>CREATE TABLE</command>)
       was executed
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>

  <para>
   On error, one of the following negative values is returned:

   <variablelist>
    <varlistentry>
     <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
     <listitem>
      <para>
       if <parameter>command</parameter> is <symbol>NULL</symbol> or
       <parameter>count</parameter> is less than 0
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_ERROR_COPY</symbol></term>
     <listitem>
      <para>
       if <command>COPY TO stdout</> or <command>COPY FROM stdin</>
       was attempted
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_ERROR_CURSOR</symbol></term>
     <listitem>
      <para>
       if <command>DECLARE</>, <command>CLOSE</>, or <command>FETCH</>
       was attempted
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
     <listitem>
      <para>
       if <command>BEGIN</>, <command>COMMIT</>, or
       <command>ROLLBACK</> was attempted
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_ERROR_OPUNKNOWN</symbol></term>
     <listitem>
      <para>
       if the command type is unknown (shouldn't happen)
      </para>
     </listitem>
    </varlistentry>

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

 <refsect1>
  <title>Notes</title>

  <para>
   The functions <function>SPI_exec</function>,
   <function>SPI_execp</function>, and
   <function>SPI_prepare</function> change both
   <varname>SPI_processed</varname> and
   <varname>SPI_tuptable</varname> (just the pointer, not the contents
   of the structure).  Save these two global variables into local
   procedure variables if you need to access the result of
   <function>SPI_exec</function> or <function>SPI_execp</function>
   across later calls.
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-prepare">
 <refmeta>
  <refentrytitle>SPI_prepare</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_prepare</refname>
  <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_prepare</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void * SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_prepare</function> creates and returns an execution
   plan for the specified command but doesn't execute the command.
   This function should only be called from a connected procedure.
  </para>

  <para>
   When the same or a similar command is to be executed repeatedly, it
   may be advantageous to perform the planning only once.
   <function>SPI_prepare</function> converts a command string into an
   execution plan that can be executed repeatedly using
   <function>SPI_execp</function>.
  </para>

  <para>
   A prepared command can be generalized by writing parameters
   (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
   constants in a normal command.  The actual values of the parameters
   are then specified when <function>SPI_execp</function> is called.
   This allows the prepared command to be used over a wider range of
   situations than would be possible without parameters.
  </para>

  <para>
   The plan returned by <function>SPI_prepare</function> can be used
   only in the current invocation of the procedure since
   <function>SPI_finish</function> frees memory allocated for a plan.
   But a plan can be saved for longer using the function