executes the specified SQL command for
true, the command must be read-only, and execution overhead is somewhat reduced.
This function can only be called from a connected procedure.
is zero then the command is executed for all rows that it applies to. If
is greater than zero, then no more than
rows will be retrieved; execution stops when the count is reached, much like adding a
clause to the query. For example,
SPI_execute("SELECT * FROM foo", true, 5);
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,
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
inserts all rows from
bar, ignoring the
parameter. However, with
SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
at most 5 rows would be inserted, since execution would stop after the fifth
result row is retrieved.
You can pass multiple commands in one string;
returns the result for the command executed last. The
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.
increments the command counter and computes a new
before executing each command in the string. The snapshot does not actually change if the current transaction isolation level is
REPEATABLE READ, but in
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.
does not update either the snapshot or the command counter, and it allows only plain
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
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_UPDATE_RETURNING, then you can use the global pointer
to access the result rows. Some utility commands (such as
EXPLAIN) also return row sets, and
will contain the result in these cases too. Some utility commands (COPY,
CREATE TABLE AS) dont return a row set, so
is NULL, but they still return the number of rows processed in
is defined thus:
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 */
is an array of pointers to rows. (The number of valid entries is given by
is a row descriptor which you can pass to SPI functions dealing with rows.
are internal fields not intended for use by SPI callers.
SPITupleTables allocated during the current procedure. You can free a particular result table earlier, if you are done with it, by calling