Chapter 11. Server Programming Interface (SPI)
The Server Programming Interface (SPI) gives users the ability to run SQL queries inside user-defined C functions. The available Procedural Languages (PL) give an alternative means to access these capabilities.
In fact, SPI is just a set of native interface functions to simplify access to the Parser, Planner, Optimizer, and Executor. SPI also does some memory management.
To avoid misunderstanding, we will use functions to represent SPI interface functions and procedures for user-defined C-functions using SPI.
Procedures that use SPI are called by the Executor. The SPI calls recursively invoke the Executor in turn to run queries. When the Executor is invoked recursively, it may call procedures that make SPI calls.
If a transaction is aborted during execution of a query from a procedure, control will not be returned to the procedure. Rather, all work will be rolled back and the server will wait for the next command from the client. This will be changed in future versions.
Other restrictions are the inability to execute BEGIN, END, and ABORT (transaction control statements) and cursor operations. This will also be changed in the future.
If successful, SPI functions return a non-negative result (either via a returned integer value or in the SPI_result global variable, as described below). On error, a negative or NULL result is returned.
Connection Management Functions
SPI_connect
SPI_connect connects your procedure to the SPI manager.
int SPI_connect(void) |
Inputs
None
Outputs
- int
SPI_OK_CONNECT if connected. SPI_ERROR_CONNECT if not connected.
Description
SPI_connect opens a connection to the backend server. You should call this function if you will need to execute queries. Note that some utility SPI functions may be called from un-connected procedures.
SPI_connect initializes the SPI internal structures for query execution and memory management.
If your procedure is already connected, SPI_connect will return an SPI_ERROR_CONNECT error. Note that this may happen if a procedure which has called SPI_connect directly calls another procedure which itself calls SPI_connect. While recursive calls to the SPI manager are permitted when an SPI query invokes another function which uses SPI , directly nested calls to SPI_connect and SPI_finish are forbidden.
SPI_finish
SPI_finish disconnects your procedure from the SPI manager.
int SPI_finish(void) |
Inputs
None
Outputs
- int
SPI_OK_FINISH if properly disconnected. SPI_ERROR_UNCONNECTED if called from an un-connected procedure.
Description
SPI_finish closes an existing connection to the backend server. You should call this function after completing operations through the SPI manager.
SPI_finish disconnects your procedure from the SPI manager and frees all memory allocations made by your procedure via palloc since the SPI_connect. These allocations cannot be used any more.
You may get the error SPI_ERROR_UNCONNECTED if SPI_finish is called without having a current valid connection. There is no fundamental problem with this; it means that nothing was done by the SPI manager.
Usage
SPI_finish must be called as a final step by a connected procedure or you may get unpredictable results. Note that you can safely skip the call to SPI_finish if you abort the transaction (via elog(ERROR)).
SPI_exec
SPI_exec creates an execution plan (parser+planner+optimizer) and executes the query for tcount tuples.
int 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_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 should not 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 (for example, 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
Note
SPI_exec disconnects your procedure from the SPI manager and frees all memory allocations made by your procedure via palloc since the SPI_connect. These allocations cannot be used any more.
Usage
This should be called only 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); |
 | 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.
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 should not occur). |