spi.sgml

PostgreSQL7.4.6 for Linux

   <function>SPI_saveplan</function>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

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

   <varlistentry>
    <term><literal>int <parameter>nargs</parameter></literal></term>
    <listitem>
     <para>
      number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
    <listitem>
     <para>
      pointer to an array containing the <acronym>OID</acronym>s of
      the data types of the parameters
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   <function>SPI_prepare</function> returns non-null pointer to an
   execution plan.  On error, <symbol>NULL</symbol> will be returned.
   In both cases, <varname>SPI_result</varname> will be set analogous
   to the value returned by <function>SPI_exec</function>, except that
   it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
   <parameter>command</parameter> is <symbol>NULL</symbol>, or if
   <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
   greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>.
  </para>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   There is a disadvantage to using parameters: since the planner does
   not know the values that will be supplied for the parameters, it
   may make worse planning choices than it would make for a normal
   command with all constants visible.
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-execp">
 <refmeta>
  <refentrytitle>SPI_execp</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_execp</refname>
  <refpurpose>executes a plan prepared by <function>SPI_prepare</function></refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_execp</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, int <parameter>count</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_execp</function> executes a plan prepared by
   <function>SPI_prepare</function>.  <parameter>tcount</parameter>
   has the same interpretation as in <function>SPI_exec</function>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>void * <parameter>plan</parameter></literal></term>
    <listitem>
     <para>
      execution plan (returned by <function>SPI_prepare</function>)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>Datum *<parameter>values</parameter></literal></term>
    <listitem>
     <para>
      actual parameter values
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>const char * <parameter>nulls</parameter></literal></term>
    <listitem>
     <para>
      An array describing which parameters are null.
      <literal>n</literal> indicates a null value (entry in
      <parameter>values</> will be ignored); a space indicates a
      nonnull value (entry in <parameter>values</> is valid).
     </para>

     <para>
      If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
      <function>SPI_execp</function> assumes that no parameters are
      null.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>int <parameter>count</parameter></literal></term>
    <listitem>
     <para>
      number of row for which plan is to be executed
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   The return value is the same as for <function>SPI_exec</function>
   or one of the following:

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

    <varlistentry>
     <term><symbol>SPI_ERROR_PARAM</symbol></term>
     <listitem>
      <para>
       if <parameter>values</parameter> is <symbol>NULL</symbol> and
       <parameter>plan</parameter> was prepared with some parameters
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>

  <para>
   <varname>SPI_processed</varname> and
   <varname>SPI_tuptable</varname> are set as in
   <function>SPI_exec</function> if successful.
  </para>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   If one of the objects (a table, function, etc.) referenced by the
   prepared plan is dropped during the session then the result of
   <function>SPI_execp</function> for this plan will be unpredictable.
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-cursor-open">
 <refmeta>
  <refentrytitle>SPI_cursor_open</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_cursor_open</refname>
  <refpurpose>set up a cursor using a plan created with <function>SPI_prepare</function></refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_cursor_open</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
Portal SPI_cursor_open(const char * <parameter>name</parameter>, void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_cursor_open</function> sets up a cursor (internally,
   a portal) that will execute a plan prepared by
   <function>SPI_prepare</function>.
  </para>

  <para>
   Using a cursor instead of executing the plan directly has two
   benefits.  First, the result rows can be retrieved a few at a time,
   avoiding memory overrun for queries that return many rows.  Second,
   a portal can outlive the current procedure (it can, in fact, live
   to the end of the current transaction).  Returning the portal name
   to the procedure's caller provides a way of returning a row set as
   result.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>const char * <parameter>name</parameter></literal></term>
    <listitem>
     <para>
      name for portal, or <symbol>NULL</symbol> to let the system
      select a name
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>void * <parameter>plan</parameter></literal></term>
    <listitem>
     <para>
      execution plan (returned by <function>SPI_prepare</function>)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>Datum * <parameter>values</parameter></literal></term>
    <listitem>
     <para>
      actual parameter values
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>const char *<parameter>nulls</parameter></literal></term>
    <listitem>
     <para>
      An array describing which parameters are null values.
      <literal>n</literal> indicates a null value (entry in
      <parameter>values</> will be ignored); a space indicates a
      nonnull value (entry in <parameter>values</> is valid).  If
      <parameter>nulls</parameter> is <symbol>NULL</> then
      <function>SPI_cursor_open</function> assumes that no parameters
      are null.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   pointer to portal containing the cursor, or <symbol>NULL</symbol>
   on error
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-cursor-find">
 <refmeta>
  <refentrytitle>SPI_cursor_find</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_cursor_find</refname>
  <refpurpose>find an existing cursor by name</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_cursor_find</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
Portal SPI_cursor_find(const char * <parameter>name</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_cursor_find</function> finds an existing portal by
   name.  This is primarily useful to resolve a cursor name returned
   as text by some other function.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>const char * <parameter>name</parameter></literal></term>
    <listitem>
     <para>
      name of the portal
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   pointer to the portal with the specified name, or
   <symbol>NULL</symbol> if none was found
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-cursor-fetch">
 <refmeta>
  <refentrytitle>SPI_cursor_fetch</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_cursor_fetch</refname>
  <refpurpose>fetch some rows from a cursor</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_cursor_fetch</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, int <parameter>count</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_cursor_fetch</function> fetches some rows from a
   cursor.  This is equivalent to the SQL command <command>FETCH</>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>Portal <parameter>portal</parameter></literal></term>
    <listitem>
     <para>
      portal containing the cursor
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>bool <parameter>forward</parameter></literal></term>
    <listitem>
     <para>
      true for fetch forward, false for fetch backward
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>int <parameter>count</parameter></literal></term>
    <listitem>
     <para>
      maximum number of rows to fetch
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

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

  <para>
   <varname>SPI_processed</varname> and
   <varname>SPI_tuptable</varname> are set as in
   <function>SPI_exec</function> if successful.
  </para>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-cursor-move">
 <refmeta>
  <refentrytitle>SPI_cursor_move</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_cursor_move</refname>
  <refpurpose>move a cursor</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_cursor_move</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, int <parameter>count</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_cursor_move</function> skips over some number of rows
   in a cursor.  This is equivalent to the SQL command
   <command>MOVE</>.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>Portal <parameter>portal</parameter></literal></term>
    <listitem>
     <para>
      portal containing the cursor
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>bool <parameter>forward</parameter></literal></term>
    <listitem>
     <para>
      true for move forward, false for move backward
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>int <parameter>count</parameter></literal></term>
    <listitem>
     <para>
      maximum number of rows to move
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>
</refentry>

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

<refentry id="spi-spi-cursor-close">
 <refmeta>
  <refentrytitle>SPI_cursor_close</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>SPI_cursor_close</refname>
  <refpurpose>close a cursor</refpurpose>
 </refnamediv>

 <indexterm><primary>SPI_cursor_close</primary></indexterm>

 <refsynopsisdiv>
<synopsis>
void SPI_cursor_close(Portal <parameter>portal</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>