PostgreSQL 7.3.4 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 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 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). `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