SPI_execute() PostgreSQL 8.1.19 Documentation SPI_execute()NAME
SPI_execute - execute a command
SYNOPSIS
int SPI_execute(const char * command, bool read_only, long count)
DESCRIPTION
SPI_execute executes the specified SQL command for count rows. If
read_only is true, the command must be read-only, and execution over‐
head is somewhat reduced.
This function may 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 may pass multiple commands in one string. 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 isola‐
tion level is SERIALIZABLE, but in READ COMMITTED mode the snapshot
update allows each command to see the results of newly committed trans‐
actions 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 snap‐
shot 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 behav‐
ior, since the read-only queries would not see the results of any data‐
base 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 (unless the return value
of the function is SPI_OK_UTILITY). If the return value of the function
is SPI_OK_SELECT then you may use the global pointer SPITupleTable
*SPI_tuptable to access the result rows.
The structure SPITupleTable is defined thus:
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;
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 may
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 proce‐
dure. 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 follow‐
ing (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_DELETE
if a DELETE was executed
SPI_OK_INSERT
if an INSERT was executed
SPI_OK_UPDATE
if an UPDATE was executed
SPI_OK_UTILITY
if a utility command (e.g., CREATE TABLE) was executed
On error, one of the following negative values is returned:
SPI_ERROR_ARGUMENT
if command is NULL or count is less than 0
SPI_ERROR_COPY
if COPY TO stdout or COPY FROM stdin was attempted
SPI_ERROR_CURSOR
if DECLARE, CLOSE, or FETCH was attempted
SPI_ERROR_TRANSACTION
if any command involving transaction manipulation was attempted
(BEGIN, COMMIT, ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT
PREPARED, ROLLBACK PREPARED, or any variant thereof)
SPI_ERROR_OPUNKNOWN
if the command type is unknown (shouldn't happen)
SPI_ERROR_UNCONNECTED
if called from an unconnected procedure
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_exe‐
cute or a related function across later calls.
2009-12-09 SPI_execute()
