Sophie: postgresql8.4-docs-8.4.12-0.1mdv2010.2 i586

postgresql8.4-docs-8.4.12-0.1mdv2010.2.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>SPI_execute</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.4.12 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Interface Functions"
HREF="spi-interface.html"><LINK
REL="PREVIOUS"
TITLE="SPI_pop"
HREF="spi-spi-pop.html"><LINK
REL="NEXT"
TITLE="SPI_exec"
HREF="spi-spi-exec.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2012-05-31T23:30:11"></HEAD
><BODY
CLASS="REFENTRY"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.4.12 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="spi-spi-pop.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="spi-spi-pop.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="spi-spi-exec.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="spi-spi-exec.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="SPI-SPI-EXECUTE"
></A
>SPI_execute</H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN50162"
></A
><H2
>Name</H2
>SPI_execute&nbsp;--&nbsp;execute a command</DIV
><A
NAME="AEN50165"
></A
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN50167"
></A
><H2
>Synopsis</H2
><PRE
CLASS="SYNOPSIS"
>int SPI_execute(const char * <TT
CLASS="PARAMETER"
>command</TT
>, bool <TT
CLASS="PARAMETER"
>read_only</TT
>, long <TT
CLASS="PARAMETER"
>count</TT
>)</PRE
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN50172"
></A
><H2
>Description</H2
><P
>   <CODE
CLASS="FUNCTION"
>SPI_execute</CODE
> executes the specified SQL command
   for <TT
CLASS="PARAMETER"
>count</TT
> rows.  If <TT
CLASS="PARAMETER"
>read_only</TT
>
   is <TT
CLASS="LITERAL"
>true</TT
>, the command must be read-only, and execution overhead
   is somewhat reduced.
  </P
><P
>   This function can only be called from a connected procedure.
  </P
><P
>   If <TT
CLASS="PARAMETER"
>count</TT
> is zero then the command is executed
   for all rows that it applies to.  If <TT
CLASS="PARAMETER"
>count</TT
>
   is greater than 0, then the number of rows for which the command
   will be executed is restricted (much like a
   <TT
CLASS="LITERAL"
>LIMIT</TT
> clause). For example:
</P><PRE
CLASS="PROGRAMLISTING"
>SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);</PRE
><P>
   will allow at most 5 rows to be inserted into the table.
  </P
><P
>   You can pass multiple commands in one string, but later commands cannot
   depend on the creation of objects earlier in the string, because the
   whole string will be parsed and planned before execution begins.
   <CODE
CLASS="FUNCTION"
>SPI_execute</CODE
> returns the
   result for the command executed last.  The <TT
CLASS="PARAMETER"
>count</TT
>
   limit applies to each command separately, but it is not applied to
   hidden commands generated by rules.
  </P
><P
>   When <TT
CLASS="PARAMETER"
>read_only</TT
> is <TT
CLASS="LITERAL"
>false</TT
>,
   <CODE
CLASS="FUNCTION"
>SPI_execute</CODE
> increments the command
   counter and computes a new <I
CLASS="FIRSTTERM"
>snapshot</I
> before executing each
   command in the string.  The snapshot does not actually change if the
   current transaction isolation level is <TT
CLASS="LITERAL"
>SERIALIZABLE</TT
>, but in
   <TT
CLASS="LITERAL"
>READ COMMITTED</TT
> 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.
  </P
><P
>   When <TT
CLASS="PARAMETER"
>read_only</TT
> is <TT
CLASS="LITERAL"
>true</TT
>,
   <CODE
CLASS="FUNCTION"
>SPI_execute</CODE
> does not update either the snapshot
   or the command counter, and it allows only plain <TT
CLASS="COMMAND"
>SELECT</TT
>
   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
   <I
CLASS="FIRSTTERM"
>stable</I
> functions to be built: since successive executions
   will all use the same snapshot, there will be no change in the results.
  </P
><P
>   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.
  </P
><P
>   The actual number of rows for which the (last) command was executed
   is returned in the global variable <TT
CLASS="VARNAME"
>SPI_processed</TT
>.
   If the return value of the function is <TT
CLASS="SYMBOL"
>SPI_OK_SELECT</TT
>,
   <TT
CLASS="SYMBOL"
>SPI_OK_INSERT_RETURNING</TT
>,
   <TT
CLASS="SYMBOL"
>SPI_OK_DELETE_RETURNING</TT
>, or
   <TT
CLASS="SYMBOL"
>SPI_OK_UPDATE_RETURNING</TT
>,
   then you can use the
   global pointer <TT
CLASS="LITERAL"
>SPITupleTable *SPI_tuptable</TT
> to
   access the result rows.  Some utility commands (such as
   <TT
CLASS="COMMAND"
>EXPLAIN</TT
>) also return row sets, and <TT
CLASS="LITERAL"
>SPI_tuptable</TT
>
   will contain the result in these cases too.
  </P
><P
>   The structure <TT
CLASS="STRUCTNAME"
>SPITupleTable</TT
> is defined
   thus:
</P><PRE
CLASS="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;</PRE
><P>
   <TT
CLASS="STRUCTFIELD"
>vals</TT
> is an array of pointers to rows.  (The number
   of valid entries is given by <TT
CLASS="VARNAME"
>SPI_processed</TT
>.)
   <TT
CLASS="STRUCTFIELD"
>tupdesc</TT
> is a row descriptor which you can pass to
   SPI functions dealing with rows.  <TT
CLASS="STRUCTFIELD"
>tuptabcxt</TT
>,
   <TT
CLASS="STRUCTFIELD"
>alloced</TT
>, and <TT
CLASS="STRUCTFIELD"
>free</TT
> are internal
   fields not intended for use by SPI callers.
  </P
><P
>   <CODE
CLASS="FUNCTION"
>SPI_finish</CODE
> frees all
   <TT
CLASS="STRUCTNAME"
>SPITupleTable</TT
>s allocated during the current
   procedure.  You can free a particular result table earlier, if you
   are done with it, by calling <CODE
CLASS="FUNCTION"
>SPI_freetuptable</CODE
>.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN50224"
></A
><H2
>Arguments</H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>const char * <TT
CLASS="PARAMETER"
>command</TT
></TT
></DT
><DD
><P
>      string containing command to execute
     </P
></DD
><DT
><TT
CLASS="LITERAL"
>bool <TT
CLASS="PARAMETER"
>read_only</TT
></TT
></DT
><DD
><P
>      <TT
CLASS="LITERAL"
>true</TT
> for read-only execution
     </P
></DD
><DT
><TT
CLASS="LITERAL"
>long <TT
CLASS="PARAMETER"
>count</TT
></TT
></DT
><DD
><P
>      maximum number of rows to process or return
     </P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN50246"
></A
><H2
>Return Value</H2
><P
>   If the execution of the command was successful then one of the
   following (nonnegative) values will be returned:

   <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_SELECT</TT
></DT
><DD
><P
>       if a <TT
CLASS="COMMAND"
>SELECT</TT
> (but not <TT
CLASS="COMMAND"
>SELECT
       INTO</TT
>) was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_SELINTO</TT
></DT
><DD
><P
>       if a <TT
CLASS="COMMAND"
>SELECT INTO</TT
> was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_INSERT</TT
></DT
><DD
><P
>       if an <TT
CLASS="COMMAND"
>INSERT</TT
> was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_DELETE</TT
></DT
><DD
><P
>       if a <TT
CLASS="COMMAND"
>DELETE</TT
> was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_UPDATE</TT
></DT
><DD
><P
>       if an <TT
CLASS="COMMAND"
>UPDATE</TT
> was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_INSERT_RETURNING</TT
></DT
><DD
><P
>       if an <TT
CLASS="COMMAND"
>INSERT RETURNING</TT
> was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_DELETE_RETURNING</TT
></DT
><DD
><P
>       if a <TT
CLASS="COMMAND"
>DELETE RETURNING</TT
> was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_UPDATE_RETURNING</TT
></DT
><DD
><P
>       if an <TT
CLASS="COMMAND"
>UPDATE RETURNING</TT
> was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_UTILITY</TT
></DT
><DD
><P
>       if a utility command (e.g., <TT
CLASS="COMMAND"
>CREATE TABLE</TT
>)
       was executed
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_OK_REWRITTEN</TT
></DT
><DD
><P
>       if the command was rewritten into another kind of command (e.g.,
       <TT
CLASS="COMMAND"
>UPDATE</TT
> became an <TT
CLASS="COMMAND"
>INSERT</TT
>) by a <A
HREF="rules.html"
>rule</A
>.
      </P
></DD
></DL
></DIV
><P>
  </P
><P
>   On error, one of the following negative values is returned:

   <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="SYMBOL"
>SPI_ERROR_ARGUMENT</TT
></DT
><DD
><P
>       if <TT
CLASS="PARAMETER"
>command</TT
> is <TT
CLASS="SYMBOL"
>NULL</TT
> or
       <TT
CLASS="PARAMETER"
>count</TT
> is less than 0
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_ERROR_COPY</TT
></DT
><DD
><P
>       if <TT
CLASS="COMMAND"
>COPY TO stdout</TT
> or <TT
CLASS="COMMAND"
>COPY FROM stdin</TT
>
       was attempted
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_ERROR_TRANSACTION</TT
></DT
><DD
><P
>       if a transaction manipulation command was attempted
       (<TT
CLASS="COMMAND"
>BEGIN</TT
>,
       <TT
CLASS="COMMAND"
>COMMIT</TT
>,
       <TT
CLASS="COMMAND"
>ROLLBACK</TT
>,
       <TT
CLASS="COMMAND"
>SAVEPOINT</TT
>,
       <TT
CLASS="COMMAND"
>PREPARE TRANSACTION</TT
>,
       <TT
CLASS="COMMAND"
>COMMIT PREPARED</TT
>,
       <TT
CLASS="COMMAND"
>ROLLBACK PREPARED</TT
>,
       or any variant thereof)
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_ERROR_OPUNKNOWN</TT
></DT
><DD
><P
>       if the command type is unknown (shouldn't happen)
      </P
></DD
><DT
><TT
CLASS="SYMBOL"
>SPI_ERROR_UNCONNECTED</TT
></DT
><DD
><P
>       if called from an unconnected procedure
      </P
></DD
></DL
></DIV
><P>
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN50352"
></A
><H2
>Notes</H2
><P
>   The functions <CODE
CLASS="FUNCTION"
>SPI_execute</CODE
>,
   <CODE
CLASS="FUNCTION"
>SPI_exec</CODE
>,
   <CODE
CLASS="FUNCTION"
>SPI_execute_plan</CODE
>, and
   <CODE
CLASS="FUNCTION"
>SPI_execp</CODE
> change both
   <TT
CLASS="VARNAME"
>SPI_processed</TT
> and
   <TT
CLASS="VARNAME"
>SPI_tuptable</TT
> (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 table of
   <CODE
CLASS="FUNCTION"
>SPI_execute</CODE
> or a related function
   across later calls.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="spi-spi-pop.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="spi-spi-exec.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>SPI_pop</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="spi-interface.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>SPI_exec</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>