Server Programming Interface
Chapter 17. Server Programming Interface
- Table of Contents
- 17.1. Interface Functions
- SPI_connect -- Connects your procedure to the SPI manager.
- SPI_finish -- Disconnects your procedure from the SPI manager.
- SPI_exec -- Creates an execution plan (parser+planner+optimizer) and executes a query.
- SPI_prepare -- Prepares a plan for a query, without executing it yet
- SPI_execp -- Executes a plan from
SPI_prepare - SPI_cursor_open -- Sets up a cursor using a plan created with
SPI_prepare - SPI_cursor_find -- Finds an existing cursor (Portal) by name
- SPI_cursor_fetch -- Fetches some rows from a cursor
- SPI_cursor_move -- Moves a cursor
- SPI_cursor_close -- Closes a cursor
- SPI_saveplan -- Saves a passed plan
- 17.2. Interface Support Functions
- SPI_fnumber -- Finds the attribute number for specified attribute name
- SPI_fname -- Finds the attribute name for the specified attribute number
- SPI_getvalue -- Returns the string value of the specified attribute
- SPI_getbinval -- Returns the binary value of the specified attribute
- SPI_gettype -- Returns the type name of the specified attribute
- SPI_gettypeid -- Returns the type OID of the specified attribute
- SPI_getrelname -- Returns the name of the specified relation
- 17.3. Memory Management
- SPI_copytuple -- Makes copy of tuple in upper Executor context
- SPI_copytupledesc -- Makes copy of tuple descriptor in upper Executor context
- SPI_copytupleintoslot -- Makes copy of tuple and descriptor in upper Executor context
- SPI_modifytuple -- Creates a tuple by replacing selected fields of a given tuple
- SPI_palloc -- Allocates memory in upper Executor context
- SPI_repalloc -- Re-allocates memory in upper Executor context
- SPI_pfree -- Frees memory in upper Executor context
- SPI_freetuple -- Frees a tuple allocated in upper Executor context
- SPI_freetuptable -- Frees a tuple set created by
SPI_execor similar function - SPI_freeplan -- Releases a previously saved plan
- 17.4. Visibility of Data Changes
- 17.5. Examples
The Server Programming Interface (SPI) gives users the ability to run SQL queries inside user-defined C functions.
Note: The available Procedural Languages (PL) give an alternate means to build functions that can execute queries.
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'll use function to mean SPI interface functions and procedure for user-defined C-functions using SPI.
Procedures which 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 itself call procedures which may make SPI calls.
Note that if during execution of a query from a procedure the transaction is aborted, then control will not be returned to your procedure. Rather, all work will be rolled back and the server will wait for the next command from the client. This will probably be changed in future versions.
A related restriction is the inability to execute BEGIN, END and ABORT (transaction control statements). 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 SPI_result global variable, as described below). On error, a negative or NULL result will be returned.
17.1. Interface Functions
- Table of Contents
- SPI_connect -- Connects your procedure to the SPI manager.
- SPI_finish -- Disconnects your procedure from the SPI manager.
- SPI_exec -- Creates an execution plan (parser+planner+optimizer) and executes a query.
- SPI_prepare -- Prepares a plan for a query, without executing it yet
- SPI_execp -- Executes a plan from
SPI_prepare - SPI_cursor_open -- Sets up a cursor using a plan created with
SPI_prepare - SPI_cursor_find -- Finds an existing cursor (Portal) by name
- SPI_cursor_fetch -- Fetches some rows from a cursor
- SPI_cursor_move -- Moves a cursor
- SPI_cursor_close -- Closes a cursor
- SPI_saveplan -- Saves a passed plan