SPI_execute

Name

SPI_execute -- execute a command

Synopsis

int SPI_execute(const char *command, boolread_only, longcount)

Description

SPI_executeexecutes the specified SQL command forcountrows. Ifread_onlyistrue, the command must be read-only, and execution overhead is somewhat reduced.

This function can only be called from a connected procedure.

Ifcountis zero then the command is executed for all rows that it applies to. Ifcountis greater than 0, then the number of rows for which the command will be executed is restricted (much like aLIMITclause). 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_executereturns the result for the command executed last. Thecountlimit applies to each command separately, but it is not applied to hidden commands generated by rules.

Whenread_onlyisfalse,SPI_executeincrements the command counter and computes a newsnapshotbefore executing each command in the string. The snapshot does not actually change if the current transaction isolation level isSERIALIZABLE, but inREAD COMMITTEDmode 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.

Whenread_onlyistrue,SPI_executedoes not update either the snapshot or the command counter, and it allows only plainSELECTcommands 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 genuinelystablefunctions 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 variableSPI_processed. If the return value of the function isSPI_OK_SELECT,SPI_OK_INSERT_RETURNING,SPI_OK_DELETE_RETURNING, orSPI_OK_UPDATE_RETURNING, then you can use the global pointerSPITupleTable *SPI_tuptableto access the result rows. Some utility commands (such asEXPLAIN) also return row sets, andSPI_tuptablewill contain the result in these cases too.

The structureSPITupleTableis 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;

valsis an array of pointers to rows. (The number of valid entries is given bySPI_processed.)tupdescis a row descriptor which you can pass to SPI functions dealing with rows.tuptabcxt,alloced, andfreeare internal fields not intended for use by SPI callers.

SPI_finishfrees allSPITupleTables allocated during the current procedure. You can free a particular result table earlier, if you are done with it, by callingSPI_freetuptable.

Arguments

const char *command: string containing command to execute
boolread_only: truefor read-only execution
longcount: 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 aSELECT(but notSELECT INTO) was executed
SPI_OK_SELINTO: if aSELECT INTOwas executed
SPI_OK_INSERT: if anINSERTwas executed
SPI_OK_DELETE: if aDELETEwas executed
SPI_OK_UPDATE: if anUPDATEwas executed
SPI_OK_INSERT_RETURNING: if anINSERT RETURNINGwas executed
SPI_OK_DELETE_RETURNING: if aDELETE RETURNINGwas executed
SPI_OK_UPDATE_RETURNING: if anUPDATE RETURNINGwas 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.,UPDATEbecame anINSERT) by a rule.

On error, one of the following negative values is returned:

SPI_ERROR_ARGUMENT: ifcommandisNULLorcountis less than 0
SPI_ERROR_COPY: ifCOPY TO stdoutorCOPY FROM stdinwas 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 functionsSPI_execute,SPI_exec,SPI_execute_plan, andSPI_execpchange bothSPI_processedandSPI_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 ofSPI_executeor a related function across later calls.

Previous article： Next article：