Documentation → PostgreSQL 6.3

Unsupported versions: 6.3

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

PostgreSQL
Prev		Next

SPI_exec

Name

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

SPI_exec(query, tcount)

Inputs

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

Outputs

int

SPI_OK_EXEC if properly disconnected

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. For example,

SPI_exec ("insert into table select * from table", 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 many 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 returned and SPI_processed > 0 then you may use global pointer SPITupleTable *SPI_tuptable to access the selected tuples: Also NOTE, that SPI_finish frees and makes all SPITupleTables unusable! (See Memory management).

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