The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 478 pages ISBN 9781906966065 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> support free software

13.1.5 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 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.

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 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 (see section 7 The Rule System).

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_TRANSACTION

if a transaction manipulation command 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_execute or a related function across later calls.