Movatterモバイル変換

SPI_execute

SPI_execute — execute a command

Synopsis

int SPI_execute(const char *command, boolread_only, longcount)

Description

SPI_execute executes the specified SQL command forcount rows. Ifread_only istrue, the command must be read-only, and execution overhead is somewhat reduced.

This function can only be called from a connected C function.

Ifcount is zero then the command is executed for all rows that it applies to. Ifcount is greater than zero, then no more thancount rows will be retrieved; execution stops when the count is reached, much like adding aLIMIT clause to the query. For example,

SPI_execute("SELECT * FROM foo", true, 5);

will retrieve at most 5 rows from the table. Note that such a limit is only effective when the command actually returns rows. For example,

SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);

inserts all rows frombar, ignoring thecount parameter. However, with

SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);

at most 5 rows would be inserted, since execution would stop after the fifthRETURNING result row is retrieved.

You can pass multiple commands in one string;SPI_execute returns the result for the command executed last. Thecount limit applies to each command separately (even though only the last result will actually be returned). The limit is not applied to any hidden commands generated by rules.

Whenread_only isfalse,SPI_execute increments the command counter and computes a newsnapshot before executing each command in the string. The snapshot does not actually change if the current transaction isolation level isSERIALIZABLE orREPEATABLE READ, but inREAD COMMITTED mode the snapshot update allows each command to see the results of newly committed transactions from other sessions. This is essential for consistent behavior when the commands are modifying the database.

Whenread_only istrue,SPI_execute does not update either the snapshot or the command counter, and it allows only plainSELECT commands to appear in the command string. The commands are executed using the snapshot previously established for the surrounding query. This execution mode is somewhat faster than the read/write mode due to eliminating per-command overhead. It also allows genuinelystable functions to be built: since successive executions will all use the same snapshot, there will be no change in the results.

It is generally unwise to mix read-only and read-write commands within a single function using SPI; that could result in very confusing behavior, since the read-only queries would not see the results of any database updates done by the read-write queries.

The actual number of rows for which the (last) command was executed is returned in the global variableSPI_processed. If the return value of the function isSPI_OK_SELECT,SPI_OK_INSERT_RETURNING,SPI_OK_DELETE_RETURNING,SPI_OK_UPDATE_RETURNING, orSPI_OK_MERGE_RETURNING, then you can use the global pointerSPITupleTable *SPI_tuptable to access the result rows. Some utility commands (such asEXPLAIN) also return row sets, andSPI_tuptable will contain the result in these cases too. Some utility commands (COPY,CREATE TABLE AS) don't return a row set, soSPI_tuptable is NULL, but they still return the number of rows processed inSPI_processed.

The structureSPITupleTable is defined thus:

typedef struct SPITupleTable{    /* Public members */    TupleDesc   tupdesc;        /* tuple descriptor */    HeapTuple  *vals;           /* array of tuples */    uint64      numvals;        /* number of valid tuples */    /* Private members, not intended for external callers */    uint64      alloced;        /* allocated length of vals array */    MemoryContext tuptabcxt;    /* memory context of result table */    slist_node  next;           /* link for internal bookkeeping */    SubTransactionId subid;     /* subxact in which tuptable was created */} SPITupleTable;

The fieldstupdesc,vals, andnumvals can be used by SPI callers; the remaining fields are internal.vals is an array of pointers to rows. The number of rows is given bynumvals (for somewhat historical reasons, this count is also returned inSPI_processed).tupdesc is a row descriptor which you can pass to SPI functions dealing with rows.

SPI_finish frees allSPITupleTables allocated during the current C function. You can free a particular result table earlier, if you are done with it, by callingSPI_freetuptable.

Arguments

Return Value

If the execution of the command was successful then one of the following (nonnegative) values will be returned:

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

Notes

All SPI query-execution functions set bothSPI_processed andSPI_tuptable (just the pointer, not the contents of the structure). Save these two global variables into local C function variables if you need to access the result table ofSPI_execute or another query-execution function across later calls.