<!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 9.3.9 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="2015-06-13T20:07:22"></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"
><A
HREF="index.html"
>PostgreSQL 9.3.9 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="SPI_pop"
HREF="spi-spi-pop.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="spi-interface.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="SPI_exec"
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="AEN61756"
></A
><H2
>Name</H2
>SPI_execute -- execute a command</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN61761"
></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="AEN61766"
></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 zero, then no more than <TT
CLASS="PARAMETER"
>count</TT
> rows
will be retrieved; execution stops when the count is reached, much like
adding a <TT
CLASS="LITERAL"
>LIMIT</TT
> clause to the query. For example,
</P><PRE
CLASS="PROGRAMLISTING"
>SPI_execute("SELECT * FROM foo", true, 5);</PRE
><P>
will retrieve at most 5 rows from the table. Note that such a limit
is only effective when the command actually returns rows. For example,
</P><PRE
CLASS="PROGRAMLISTING"
>SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);</PRE
><P>
inserts all rows from <TT
CLASS="STRUCTNAME"
>bar</TT
>, ignoring the
<TT
CLASS="PARAMETER"
>count</TT
> parameter. However, with
</P><PRE
CLASS="PROGRAMLISTING"
>SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);</PRE
><P>
at most 5 rows would be inserted, since execution would stop after the
fifth <TT
CLASS="LITERAL"
>RETURNING</TT
> result row is retrieved.
</P
><P
> You can pass multiple commands in one string;
<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 (even though only the last
result will actually be returned). The limit is not applied to any
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
> or <TT
CLASS="LITERAL"
>REPEATABLE READ</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. Some utility commands
(<TT
CLASS="COMMAND"
>COPY</TT
>, <TT
CLASS="COMMAND"
>CREATE TABLE AS</TT
>) don't return a row set, so
<TT
CLASS="LITERAL"
>SPI_tuptable</TT
> is NULL, but they still return the number of
rows processed in <TT
CLASS="VARNAME"
>SPI_processed</TT
>.
</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="AEN61829"
></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 return,
or <TT
CLASS="LITERAL"
>0</TT
> for no limit
</P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN61852"
></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="AEN61958"
></A
><H2
>Notes</H2
><P
> All SPI query-execution functions set 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 another query-execution 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
>
