SPI_execute

Name

Synopsis

Description

SPI_execute executes the
specified SQL command for count rows.
If read_only is true, the command must be read-only, and execution
overhead is somewhat reduced.

This function can only be called from a connected
procedure.

If count is zero then the command
is executed for all rows that it applies to. If count is greater than 0, then the number of rows
for which the command will be executed is restricted (much like a
LIMIT clause). For example:

SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);

will allow at most 5 rows to be inserted into the table.

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. SPI_execute returns the result for the command
executed last. The count limit applies
to each command separately, but it is not applied to hidden
commands generated by rules.

When read_only is false, SPI_execute
increments the command counter and computes a new snapshot before executing each command in the
string. The snapshot does not actually change if the current
transaction isolation level is SERIALIZABLE, but in READ
COMMITTED 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.

When read_only is true, SPI_execute
does not update either the snapshot or the command counter, and
it allows only plain SELECT 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
stable functions to be built: since
successive executions will all use the same snapshot, there will
be no change in the results.

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.

The actual number of rows for which the (last) command was
executed is returned in the global variable SPI_processed. If the return value of the function
is SPI_OK_SELECT, SPI_OK_INSERT_RETURNING, SPI_OK_DELETE_RETURNING, or SPI_OK_UPDATE_RETURNING, then you can use the
global pointer SPITupleTable
*SPI_tuptable to access the result rows. Some utility
commands (such as EXPLAIN) also return
row sets, and SPI_tuptable will contain
the result in these cases too.

vals is an array of pointers to
rows. (The number of valid entries is given by SPI_processed.) tupdesc is a row descriptor which you can pass
to SPI functions dealing with rows. tuptabcxt, alloced, and free
are internal fields not intended for use by SPI callers.

SPI_finish frees all SPITupleTables allocated during the current
procedure. You can free a particular result table earlier, if you
are done with it, by calling SPI_freetuptable.

Arguments

const char * command

string containing command to execute

bool read_only

true for read-only
execution

long count

maximum number of rows to process or return

Return Value

If the execution of the command was successful then one of the
following (nonnegative) values will be returned:

SPI_OK_SELECT

if a SELECT (but not SELECT INTO) was executed

SPI_OK_SELINTO

if a SELECT INTO was
executed

SPI_OK_INSERT

if an INSERT was executed

SPI_OK_DELETE

if a DELETE was executed

SPI_OK_UPDATE

if an UPDATE was executed

SPI_OK_INSERT_RETURNING

if an INSERT RETURNING was
executed

SPI_OK_DELETE_RETURNING

if a DELETE RETURNING was
executed

SPI_OK_UPDATE_RETURNING

if an UPDATE RETURNING was
executed

SPI_OK_UTILITY

if a utility command (e.g., CREATE
TABLE) was executed

SPI_OK_REWRITTEN

if the command was rewritten into another kind of
command (e.g., UPDATE became an
INSERT) by a rule.

Notes

The functions SPI_execute,
SPI_exec, SPI_execute_plan, and SPI_execp change both SPI_processed and SPI_tuptable (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
SPI_execute or a related function
across later calls.

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.