Documentation → PostgreSQL 7.2

Unsupported versions: 7.3 / 7.2 / 7.1

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.

PostgreSQL 7.2.8 Documentation
Prev		Next

SPI_exec

Name

SPI_exec -- Creates an execution plan (parser+planner+optimizer) and executes a query.

Synopsis

SPI_exec(query, tcount)

Inputs

char *query: String containing query plan
int tcount: Maximum number of tuples to return

Outputs

int

SPI_ERROR_UNCONNECTED if called from an un-connected procedure

SPI_ERROR_ARGUMENT if query is NULL or tcount < 0.

SPI_ERROR_UNCONNECTED if procedure is unconnected.

SPI_ERROR_COPY if COPY TO/FROM stdin.

SPI_ERROR_CURSOR if DECLARE/CLOSE CURSOR, FETCH.

SPI_ERROR_TRANSACTION if BEGIN/ABORT/END.

SPI_ERROR_OPUNKNOWN if type of query is unknown (this shouldn't occur).

If execution of your query was successful then one of the following (non-negative) values will be returned:

SPI_OK_UTILITY if some utility (e.g. CREATE TABLE ...) was executed

SPI_OK_SELECT if SELECT (but not SELECT ... INTO!) was executed

SPI_OK_SELINTO if SELECT ... INTO was executed

SPI_OK_INSERT if INSERT (or INSERT ... SELECT) was executed

SPI_OK_DELETE if DELETE was executed

SPI_OK_UPDATE if UPDATE was executed

Description

SPI_exec creates an execution plan (parser+planner+optimizer) and executes the query for tcount tuples.

Usage

This should only be called from a connected procedure. If tcount is zero then it executes the query for all tuples returned by the query scan. Using tcount > 0 you may restrict the number of tuples for which the query will be executed (much like a LIMIT clause). For example,

SPI_exec ("INSERT INTO tab SELECT * FROM tab", 5);

will allow at most 5 tuples to be inserted into table. If execution of your query was successful then a non-negative value will be returned.

Note: You may pass multiple queries in one string or query string may be re-written by RULEs. SPI_exec returns the result for the last query executed.

The actual number of tuples for which the (last) query was executed is returned in the global variable SPI_processed (if not SPI_OK_UTILITY). If SPI_OK_SELECT is returned and SPI_processed > 0 then you may use global pointer SPITupleTable *SPI_tuptable to access the result tuples.

SPI_exec may return one of the following (negative) values:

SPI_ERROR_ARGUMENT if query is NULL or tcount < 0.

SPI_ERROR_UNCONNECTED if procedure is unconnected.

SPI_ERROR_COPY if COPY TO/FROM stdin.

SPI_ERROR_CURSOR if DECLARE/CLOSE CURSOR, FETCH.

SPI_ERROR_TRANSACTION if BEGIN/ABORT/END.

SPI_ERROR_OPUNKNOWN if type of query is unknown (this shouldn't occur).

Structures

If SPI_OK_SELECT is returned and SPI_processed > 0 then you may use the global pointer SPITupleTable *SPI_tuptable to access the selected tuples.

Structure SPITupleTable is defined in spi.h:

   typedef struct
   {
       MemoryContext tuptabcxt;    /* memory context of result table */
       uint32      alloced;        /* # of alloced vals */
       uint32      free;           /* # of free vals */
       TupleDesc   tupdesc;        /* tuple descriptor */
       HeapTuple  *vals;           /* tuples */
   } SPITupleTable;

vals is an array of pointers to tuples (the number of useful entries is given by SPI_processed). TupleDesc tupdesc is a tuple descriptor which you may pass to SPI functions dealing with tuples. tuptabcxt, alloced, and free are internal fields not intended for use by SPI callers.

Note: 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 one SPI_exec or SPI_execp across later calls.

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.

Prev	Home	Next
SPI_finish	Up	SPI_prepare