PostgreSQL 7.4.8 Documentation
PrevFast BackwardFast ForwardNext

Chapter 41. Server Programming Interface

Table of Contents
41.1. Interface Functions
SPI_connect -- connect a procedure to the SPI manager
SPI_finish -- disconnect a procedure from the SPI manager
SPI_exec -- execute a command
SPI_prepare -- prepare a plan for a command, without executing it yet
SPI_execp -- executes a plan prepared by SPI_prepare
SPI_cursor_open -- set up a cursor using a plan created with SPI_prepare
SPI_cursor_find -- find an existing cursor by name
SPI_cursor_fetch -- fetch some rows from a cursor
SPI_cursor_move -- move a cursor
SPI_cursor_close -- close a cursor
SPI_saveplan -- save a plan
41.2. Interface Support Functions
SPI_fname -- determine the column name for the specified column number
SPI_fnumber -- determine the column number for the specified column name
SPI_getvalue -- return the string value of the specified column
SPI_getbinval -- return the binary value of the specified column
SPI_gettype -- return the data type name of the specified column
SPI_gettypeid -- return the data type OID of the specified column
SPI_getrelname -- return the name of the specified relation
41.3. Memory Management
SPI_palloc -- allocate memory in the upper executor context
SPI_repalloc -- reallocate memory in the upper executor context
SPI_pfree -- free memory in the upper executor context
SPI_copytuple -- make a copy of a row in the upper executor context
SPI_copytupledesc -- make a copy of a row descriptor in the upper executor context
SPI_copytupleintoslot -- make a copy of a row and descriptor in the upper executor context
SPI_modifytuple -- create a row by replacing selected fields of a given row
SPI_freetuple -- frees a row allocated in the upper executor context
SPI_freetuptable -- free a row set created by SPI_exec or a similar function
SPI_freeplan -- free a previously saved plan
41.4. Visibility of Data Changes
41.5. Examples

The Server Programming Interface (SPI) gives writers of user-defined C functions the ability to run SQL commands inside their functions. SPI is a set of interface functions to simplify access to the parser, planner, optimizer, and executor. SPI also does some memory management.

Note: The available procedural languages provide various means to execute SQL commands from procedures. Some of these are based on or modelled after SPI, so this documentation might be of use for users of those languages as well.

To avoid misunderstanding we'll use the term "function" when we speak of SPI interface functions and "procedure" for a user-defined C-function that is using SPI.

Note that if during the execution of a procedure the transaction is aborted because of an error in a command, 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. A related restriction is the inability to execute BEGIN, COMMIT, and ROLLBACK (transaction control statements) inside a procedure. Both of these restrictions will probably be changed in the future.

SPI functions return a nonnegative result on success (either via a returned integer value or in the global variable SPI_result, as described below). On error, a negative result or NULL will be returned.

Source code files that use SPI must include the header file executor/spi.h.

41.1. Interface Functions

Table of Contents
SPI_connect -- connect a procedure to the SPI manager
SPI_finish -- disconnect a procedure from the SPI manager
SPI_exec -- execute a command
SPI_prepare -- prepare a plan for a command, without executing it yet
SPI_execp -- executes a plan prepared by SPI_prepare
SPI_cursor_open -- set up a cursor using a plan created with SPI_prepare
SPI_cursor_find -- find an existing cursor by name
SPI_cursor_fetch -- fetch some rows from a cursor
SPI_cursor_move -- move a cursor
SPI_cursor_close -- close a cursor
SPI_saveplan -- save a plan
PrevHomeNext
Database AccessUpSPI_connect