<!-- doc/src/sgml/spi.sgml -->

<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 or procedures.
  <acronym>SPI</acronym> is a set of
  interface functions to simplify access to the parser, planner,
  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 functions.  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>
  Note that if a command invoked via SPI fails, then control will not be
  returned to your C function.  Rather, the
  transaction or subtransaction in which your C function executes will be
  rolled back.  (This might 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.
 </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">
  <indexterm><primary>SPI_connect</primary></indexterm>
  <indexterm><primary>SPI_connect_ext</primary></indexterm>

  <refmeta>
   <refentrytitle>SPI_connect</refentrytitle>
   <manvolnum>3</manvolnum>
  </refmeta>

  <refnamediv>
   <refname>SPI_connect</refname>
   <refname>SPI_connect_ext</refname>
   <refpurpose>connect a C function to the SPI manager</refpurpose>
 </refnamediv>

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

 <synopsis>
int SPI_connect_ext(int <parameter>options</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

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

  <para>
   <function>SPI_connect_ext</function> does the same but has an argument that
   allows passing option flags.  Currently, the following option values are
   available:
   <variablelist>
    <varlistentry>
     <term><symbol>SPI_OPT_NONATOMIC</symbol></term>
     <listitem>
      <para>
       Sets the SPI connection to be <firstterm>nonatomic</firstterm>, which
       means that transaction control calls (<function>SPI_commit</function>,
       <function>SPI_rollback</function>) are allowed.  Otherwise,
       calling those functions will result in an immediate error.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>

  <para>
   <literal>SPI_connect()</literal> is equivalent to
   <literal>SPI_connect_ext(0)</literal>.
  </para>
 </refsect1>

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

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

  <para>
   The fact that these functions return <type>int</type>
   not <type>void</type> is historical.  All failure cases are reported
   via <function>ereport</function>