14th September 2023: PostgreSQL 16 Released!
Supported Versions: Current (16) / 15 / 14 / 13 / 12 / 11
Development Versions: devel
Unsupported versions: 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.



SPI_exec -- execute a command


int SPI_exec(const char * command, int count)


SPI_exec executes the specified SQL command for count rows.

This function should only be called from a connected procedure. If count is zero then it executes the command 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_exec("INSERT INTO tab SELECT * FROM tab", 5);

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

You may pass multiple commands in one string, and the command may be rewritten by rules. SPI_exec returns the result for the command executed last.

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 the use 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 procedure. You can free a particular result table earlier, if you are done with it, by calling SPI_freetuptable.


const char * command

string containing command to execute

int 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:


if a SELECT (but not SELECT ... INTO) was executed


if a SELECT ... INTO was executed


if a DELETE was executed


if an INSERT was executed


if an UPDATE was executed


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

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


if command is NULL or count is less than 0


if COPY TO stdout or COPY FROM stdin was attempted


if DECLARE, CLOSE, or FETCH was attempted


if BEGIN, COMMIT, or ROLLBACK was attempted


if the command type is unknown (shouldn't happen)


if called from an unconnected procedure


The functions SPI_exec, SPI_execp, and SPI_prepare 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 of SPI_exec or SPI_execp across later calls.