The Server Programming Interface (SPI) is an attempt to give users the ability to run SQL queries inside user-defined C functions. Given the lack of a proper Procedural Language (PL) in the current version of Postgres, SPI is the only way to write server-stored procedures and triggers. In the future SPI will be used as the "workhorse" for a PL.
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.
SPI procedures are always called by some (upper) Executor and the SPI manager uses the Executor to run your queries. Other procedures may be called by the Executor running queries from your procedure.
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.