Supported Versions: Current (16) / 15 / 14 / 13 / 12
Development Versions: devel
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
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.

Chapter 22. Server Programming Interface

Table of Contents
22.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 --  Connects your procedure to the SPI manager.
SPI_saveplan --  Saves a passed plan
SPI_execp -- Executes a plan from SPI_saveplan
22.2. Interface Support Functions
SPI_copytuple -- Makes copy of tuple in upper Executor context
SPI_modifytuple -- Modifies tuple of relation
SPI_fnumber -- Finds the attribute number for specified attribute
SPI_fname -- Finds the attribute name for the specified attribute
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
SPI_palloc -- Allocates memory in upper Executor context
SPI_repalloc -- Re-allocates memory in upper Executor context
SPI_pfree -- Frees memory from upper Executor context
22.3. Memory Management
22.4. Visibility of Data Changes
22.5. Examples

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 alternate 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'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 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 SPI_result global variable, as described below). On error, a negative or NULL result will be returned.

22.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 --  Connects your procedure to the SPI manager.
SPI_saveplan --  Saves a passed plan
SPI_execp -- Executes a plan from SPI_saveplan