PostgreSQL 7.4.3 Documentation
PrevFast BackwardFast ForwardNext

SPI_exec

Name

SPI_exec -- execute a command

Synopsis

int SPI_exec(const char * command, int count)

Description

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.

Arguments

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:

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 BEGIN, COMMIT, or ROLLBACK was attempted

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

PrevHomeNext
SPI_finishUpSPI_prepare