<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>SPI_execute</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="spi-spi-finish.html" title="SPI_finish" /><link rel="next" href="spi-spi-exec.html" title="SPI_exec" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">SPI_execute</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="spi-spi-finish.html" title="SPI_finish">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="spi-interface.html" title="47.1. Interface Functions">Up</a></td><th width="60%" align="center">47.1. Interface Functions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="spi-spi-exec.html" title="SPI_exec">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SPI-SPI-EXECUTE"><div class="titlepage"></div><a id="id-1.8.12.8.4.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">SPI_execute</span></h2><p>SPI_execute — execute a command</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
int SPI_execute(const char * <em class="parameter"><code>command</code></em>, bool <em class="parameter"><code>read_only</code></em>, long <em class="parameter"><code>count</code></em>)
</pre></div><div class="refsect1" id="id-1.8.12.8.4.5"><h2>Description</h2><p>
<code class="function">SPI_execute</code> executes the specified SQL command
for <em class="parameter"><code>count</code></em> rows. If <em class="parameter"><code>read_only</code></em>
is <code class="literal">true</code>, the command must be read-only, and execution overhead
is somewhat reduced.
</p><p>
This function can only be called from a connected C function.
</p><p>
If <em class="parameter"><code>count</code></em> is zero then the command is executed
for all rows that it applies to. If <em class="parameter"><code>count</code></em>
is greater than zero, then no more than <em class="parameter"><code>count</code></em> rows
will be retrieved; execution stops when the count is reached, much like
adding a <code class="literal">LIMIT</code> 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 <code class="structname">bar</code>, ignoring the
<em class="parameter"><code>count</code></em> 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 <code class="literal">RETURNING</code> 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 <em class="parameter"><code>count</code></em>
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 <em class="parameter"><code>read_only</code></em> is <code class="literal">false</code>,
<code class="function">SPI_execute</code> increments the command
counter and computes a new <em class="firstterm">snapshot</em> before executing each
command in the string. The snapshot does not actually change if the
current transaction isolation level is <code class="literal">SERIALIZABLE</code> or <code class="literal">REPEATABLE READ</code>, but in
<code class="literal">READ COMMITTED</code> 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 <em class="parameter"><code>read_only</code></em> is <code class="literal">true</code>,
<code class="function">SPI_execute</code> does not update either the snapshot
or the command counter, and it allows only plain <code class="command">SELECT</code>
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
<em class="firstterm">stable</em> 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 <code class="varname">SPI_processed</code>.
If the return value of the function is <code class="symbol">SPI_OK_SELECT</code>,
<code class="symbol">SPI_OK_INSERT_RETURNING</code>,
<code class="symbol">SPI_OK_DELETE_RETURNING</code>, or
<code class="symbol">SPI_OK_UPDATE_RETURNING</code>,
then you can use the
global pointer <code class="literal">SPITupleTable *SPI_tuptable</code> to
access the result rows. Some utility commands (such as
<code class="command">EXPLAIN</code>) also return row sets, and <code class="literal">SPI_tuptable</code>
will contain the result in these cases too. Some utility commands
(<code class="command">COPY</code>, <code class="command">CREATE TABLE AS</code>) don't return a row set, so
<code class="literal">SPI_tuptable</code> is NULL, but they still return the number of
rows processed in <code class="varname">SPI_processed</code>.
</p><p>
The structure <code class="structname">SPITupleTable</code> is defined
thus:
</p><pre class="programlisting">
typedef struct
{
MemoryContext tuptabcxt; /* memory context of result table */
uint64 alloced; /* number of alloced vals */
uint64 free; /* number of free vals */
TupleDesc tupdesc; /* row descriptor */
HeapTuple *vals; /* rows */
} SPITupleTable;
</pre><p>
<code class="structfield">vals</code> is an array of pointers to rows. (The number
of valid entries is given by <code class="varname">SPI_processed</code>.)
<code class="structfield">tupdesc</code> is a row descriptor which you can pass to
SPI functions dealing with rows. <code class="structfield">tuptabcxt</code>,
<code class="structfield">alloced</code>, and <code class="structfield">free</code> are internal
fields not intended for use by SPI callers.
</p><p>
<code class="function">SPI_finish</code> frees all
<code class="structname">SPITupleTable</code>s allocated during the current
C function. 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" id="id-1.8.12.8.4.6"><h2>Arguments</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">const char * <em class="parameter"><code>command</code></em></code></span></dt><dd><p>
string containing command to execute
</p></dd><dt><span class="term"><code class="literal">bool <em class="parameter"><code>read_only</code></em></code></span></dt><dd><p><code class="literal">true</code> for read-only execution</p></dd><dt><span class="term"><code class="literal">long <em class="parameter"><code>count</code></em></code></span></dt><dd><p>
maximum number of rows to return,
or <code class="literal">0</code> for no limit
</p></dd></dl></div></div><div class="refsect1" id="id-1.8.12.8.4.7"><h2>Return Value</h2><p>
If the execution of the command was successful then one of the
following (nonnegative) values will be returned:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="symbol">SPI_OK_SELECT</code></span></dt><dd><p>
if a <code class="command">SELECT</code> (but not <code class="command">SELECT
INTO</code>) was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_SELINTO</code></span></dt><dd><p>
if a <code class="command">SELECT INTO</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_INSERT</code></span></dt><dd><p>
if an <code class="command">INSERT</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_DELETE</code></span></dt><dd><p>
if a <code class="command">DELETE</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_UPDATE</code></span></dt><dd><p>
if an <code class="command">UPDATE</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_INSERT_RETURNING</code></span></dt><dd><p>
if an <code class="command">INSERT RETURNING</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_DELETE_RETURNING</code></span></dt><dd><p>
if a <code class="command">DELETE RETURNING</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_UPDATE_RETURNING</code></span></dt><dd><p>
if an <code class="command">UPDATE RETURNING</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_UTILITY</code></span></dt><dd><p>
if a utility command (e.g., <code class="command">CREATE TABLE</code>)
was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_REWRITTEN</code></span></dt><dd><p>
if the command was rewritten into another kind of command (e.g.,
<code class="command">UPDATE</code> became an <code class="command">INSERT</code>) by a <a class="link" href="rules.html" title="Chapter 41. The Rule System">rule</a>.
</p></dd></dl></div><p>
</p><p>
On error, one of the following negative values is returned:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="symbol">SPI_ERROR_ARGUMENT</code></span></dt><dd><p>
if <em class="parameter"><code>command</code></em> is <code class="symbol">NULL</code> or
<em class="parameter"><code>count</code></em> is less than 0
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_COPY</code></span></dt><dd><p>
if <code class="command">COPY TO stdout</code> or <code class="command">COPY FROM stdin</code>
was attempted
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_TRANSACTION</code></span></dt><dd><p>
if a transaction manipulation command was attempted
(<code class="command">BEGIN</code>,
<code class="command">COMMIT</code>,
<code class="command">ROLLBACK</code>,
<code class="command">SAVEPOINT</code>,
<code class="command">PREPARE TRANSACTION</code>,
<code class="command">COMMIT PREPARED</code>,
<code class="command">ROLLBACK PREPARED</code>,
or any variant thereof)
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_OPUNKNOWN</code></span></dt><dd><p>
if the command type is unknown (shouldn't happen)
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_UNCONNECTED</code></span></dt><dd><p>
if called from an unconnected C function
</p></dd></dl></div><p>
</p></div><div class="refsect1" id="id-1.8.12.8.4.8"><h2>Notes</h2><p>
All SPI query-execution functions set both
<code class="varname">SPI_processed</code> and
<code class="varname">SPI_tuptable</code> (just the pointer, not the contents
of the structure). Save these two global variables into local
C function 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><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="spi-spi-finish.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="spi-interface.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="spi-spi-exec.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">SPI_finish </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> SPI_exec</td></tr></table></div></body></html>
