spi.sgml

PostgreSQL7.4.6 for Linux

   <function>SPI_copytupleintoslot</function> makes a copy of a row in
   the upper executor context, returning it in the form of a filled-in
   <type>TupleTableSlot</type> structure.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>HeapTuple <parameter>row</parameter></literal></term>
    <listitem>
     <para>
      row to be copied
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
    <listitem>
     <para>
      row descriptor to be copied
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   <type>TupleTableSlot</type> containing the copied row and
   descriptor; <symbol>NULL</symbol> only if
   <parameter>row</parameter> or <parameter>rowdesc</parameter> are
   <symbol>NULL</symbol>
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-modifytuple">
 <refmeta>
  <refentrytitle>SPI_modifytuple</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_modifytuple</refname>
  <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_modifytuple</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
HeapTuple SPI_modifytuple(Relation <parameter>rel</parameter>, HeapTuple <parameter>row</parameter>, <parameter>ncols</parameter>, <parameter>colnum</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_modifytuple</function> creates a new row by
   substituting new values for selected columns, copying the original
   row's columns at other positions.  The input row is not modified.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>Relation <parameter>rel</parameter></literal></term>
    <listitem>
     <para>
      Used only as the source of the row descriptor for the row.
      (Passing a relation rather than a row descriptor is a
      misfeature.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>HeapTuple <parameter>row</parameter></literal></term>
    <listitem>
     <para>
      row to be modified
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>int <parameter>ncols</parameter></literal></term>
    <listitem>
     <para>
      number of column numbers in the array
      <parameter>colnum</parameter>
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>int * <parameter>colnum</parameter></literal></term>
    <listitem>
     <para>
      array of the numbers of the columns that are to be changed
      (count starts at 1)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>Datum * <parameter>values</parameter></literal></term>
    <listitem>
     <para>
      new values for the specified columns
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>const char * <parameter>Nulls</parameter></literal></term>
    <listitem>
     <para>
      which new values are null, if any (see <function>SPI_execp</function> for the format)
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   new row with modifications, allocated in the upper executor
   context; <symbol>NULL</symbol> only if <parameter>row</parameter>
   is <symbol>NULL</symbol>
  </para>

  <para>
   On error, <varname>SPI_result</varname> is set as follows:
   <variablelist>
    <varlistentry>
     <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
     <listitem>
      <para>
       if <parameter>rel</> is <symbol>NULL</>, or if
       <parameter>row</> is <symbol>NULL</>, or if <parameter>ncols</>
       is less than or equal to 0, or if <parameter>colnum</> is
       <symbol>NULL</>, or if <parameter>values</> is <symbol>NULL</>.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
     <listitem>
      <para>
       if <parameter>colnum</> contains an invalid column number (less
       than or equal to 0 or greater than the number of column in
       <parameter>row</>)
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-freetuple">
 <refmeta>
  <refentrytitle>SPI_freetuple</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_freetuple</refname>
  <refpurpose>frees a row allocated in the upper executor context</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_freetuple</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void SPI_freetuple(HeapTuple <parameter>row</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_freetuple</function> frees a row previously allocated
   in the upper executor context.
  </para>

  <para>
   This function is no longer different from plain
   <function>heap_freetuple</function>.  It's kept just for backward
   compatibility of existing code.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>HeapTuple <parameter>row</parameter></literal></term>
    <listitem>
     <para>
      row to free
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-freetupletable">
 <refmeta>
  <refentrytitle>SPI_freetuptable</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_freetuptable</refname>
  <refpurpose>free a row set created by <function>SPI_exec</> or a similar function</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_freetuptable</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_freetuptable</function> frees a row set created by a
   prior SPI command execution function, such as
   <function>SPI_exec</>.  Therefore, this function is usually called
   with the global variable <varname>SPI_tupletable</varname> as
   argument.
  </para>

  <para>
   This function is useful if a SPI procedure needs to execute
   multiple commands and does not want to keep the results of earlier
   commands around until it ends.  Note that any unfreed row sets will
   be freed anyway at <function>SPI_finish</>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
    <listitem>
     <para>
      pointer to row set to free
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-freeplan">
 <refmeta>
  <refentrytitle>SPI_freeplan</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_freeplan</refname>
  <refpurpose>free a previously saved plan</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_freeplan</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
int SPI_freeplan(void *<parameter>plan</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_freeplan</function> releases a command execution plan
   previously returned by <function>SPI_prepare</function> or saved by
   <function>SPI_saveplan</function>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>void * <parameter>plan</parameter></literal></term>
    <listitem>
     <para>
      pointer to plan to free
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
   is <symbol>NULL</symbol>.
  </para>
 </refsect1>
</refentry>

 </sect1>

 <sect1 id="spi-visibility">
  <title>Visibility of Data Changes</title>

  <para>
   The following two rules govern the visibility of data changes in
   functions that use SPI (or any other C function):

   <itemizedlist>
    <listitem>
     <para>
      During the execution of an SQL command, any data changes made by
      the command (or by function called by the command, including
      trigger functions) are invisible to the command.  For
      example, in command
<programlisting>
INSERT INTO a SELECT * FROM a;
</programlisting>
      the inserted rows are invisible to the <command>SELECT</command>
      part.
     </para>
    </listitem>

    <listitem>
     <para>
      Changes made by a command C are visible to all commands that are
      started after C, no matter whether they are started inside C
      (during the execution of C) or after C is done.
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
   The next section contains an example that illustrates the
   application of these rules.
  </para>
 </sect1>

 <sect1 id="spi-examples">
  <title>Examples</title>

  <para>
   This section contains a very simple example of SPI usage. The
   procedure <function>execq</function> takes an SQL command as its
   first argument and a row count as its second, executes the command
   using <function>SPI_exec</function> and returns the number of rows
   that were processed by the command.  You can find more complex
   examples for SPI in the source tree in
   <filename>src/test/regress/regress.c</filename> and in
   <filename>contrib/spi</filename>.
  </para>

<programlisting>
#include "executor/spi.h"

int execq(text *sql, int cnt);

int
execq(text *sql, int cnt)
{
    char *command;
    int ret;
    int proc;

    /* Convert given text object to a C string */
    command = DatumGetCString(DirectFunctionCall1(textout,
                                                  PointerGetDatum(sql)));

    SPI_connect();
    
    ret = SPI_exec(command, cnt);
    
    proc = SPI_processed;
    /*
     * If this is a SELECT and some rows were fetched,
     * then the rows are printed via elog(INFO).
     */
    if (ret == SPI_OK_SELECT && SPI_processed > 0)
    {
        TupleDesc tupdesc = SPI_tuptable->tupdesc;
        SPITupleTable *tuptable = SPI_tuptable;
        char buf[8192];
        int i, j;
        
        for (j = 0; j < proc; j++)
        {
            HeapTuple tuple = tuptable->vals[j];
            
            for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
                snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
                        SPI_getvalue(tuple, tupdesc, i),
                        (i == tupdesc->natts) ? " " : " |");
            elog (INFO, "EXECQ: %s", buf);
        }
    }

    SPI_finish();
    pfree(command);

    return (proc);
}
</programlisting>

  <para>
   (This function uses call convention version 0, to make the example
   easier to understand.  In real applications you should user the new
   version 1 interface.)
  </para>

  <para>
   This is how you declare the function after having compiled it into
   a shared library:

<programlisting>
CREATE FUNCTION execq(text, integer) RETURNS integer
    AS '<replaceable>filename</replaceable>'
    LANGUAGE C;
</programlisting>
  </para>

  <para>
   Here is a sample session:

<programlisting>
=> SELECT execq('CREATE TABLE a (x integer)', 0);
 execq
-------
     0
(1 row)

=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 167631 1
=> SELECT execq('SELECT * FROM a', 0);
INFO:  EXECQ:  0    -- inserted by execq
INFO:  EXECQ:  1    -- returned by execq and inserted by upper INSERT

 execq
-------
     2
(1 row)

=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
 execq
-------
     1
(1 row)

=> SELECT execq('SELECT * FROM a', 10);
INFO:  EXECQ:  0
INFO:  EXECQ:  1
INFO:  EXECQ:  2    -- 0 + 2, only one row inserted - as specified

 execq
-------
     3              -- 10 is the max value only, 3 is the real number of rows
(1 row)

=> DELETE FROM a;
DELETE 3
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INSERT 167712 1
=> SELECT * FROM a;
 x
---
 1                  -- no rows in a (0) + 1
(1 row)

=> INSERT INTO a VALUES (execq('SELECT