Server Programming Interface
SPI
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,
and executor. SPI also does some
memory management.
The available procedural languages provide various means to
execute SQL commands from procedures. Most of these facilities are
based on 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 a command invoked via SPI fails, then control will not be
returned to your procedure. Rather, the
transaction or subtransaction in which your procedure executes will be
rolled back. (This might seem surprising given that the SPI functions mostly
have documented error-return conventions. Those conventions only apply
for errors detected within the SPI functions themselves, however.)
It is possible to recover control after an error by establishing your own
subtransaction surrounding SPI calls that might fail. This is not currently
documented because the mechanisms required are still in flux.
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.
Interface Functions
SPI_connect
SPI_connect
connect a procedure to the SPI manager
SPI_connect
int SPI_connect(void)
Description
SPI_connect opens a connection from a
procedure invocation to the SPI manager. You must call this
function if you want to execute commands through SPI. Some utility
SPI functions can be called from unconnected procedures.
If your procedure is already connected,
SPI_connect will return the error code
SPI_ERROR_CONNECT. This could happen if
a procedure that has called SPI_connect
directly calls another procedure that calls
SPI_connect. While recursive calls to the
SPI manager are permitted when an SQL command
called through SPI invokes another function that uses
SPI, directly nested calls to
SPI_connect and
SPI_finish are forbidden.
(But see SPI_push and SPI_pop.)
Return Value
SPI_OK_CONNECT
on success
SPI_ERROR_CONNECT
on error
SPI_finish
SPI_finish
disconnect a procedure from the SPI manager
SPI_finish
int SPI_finish(void)
Description
SPI_finish closes an existing connection to
the SPI manager. You must call this function after completing the
SPI operations needed during your procedure's current invocation.
You do not need to worry about making this happen, however, if you
abort the transaction via elog(ERROR). In that
case SPI will clean itself up automatically.
If SPI_finish is called without having a valid
connection, it will return SPI_ERROR_UNCONNECTED.
There is no fundamental problem with this; it means that the SPI
manager has nothing to do.
Return Value
SPI_OK_FINISH
if properly disconnected
SPI_ERROR_UNCONNECTED
if called from an unconnected procedure
SPI_push
SPI_push
push SPI stack to allow recursive SPI usage
SPI_push
void SPI_push(void)
Description
SPI_push should be called before executing another
procedure that might itself wish to use SPI.
After SPI_push, SPI is no longer in a
connected> state, and SPI function calls will be rejected unless
a fresh SPI_connect is done. This ensures a clean
separation between your procedure's SPI state and that of another procedure
you call. After the other procedure returns, call
SPI_pop to restore access to your own SPI state.
Note that SPI_execute and related functions
automatically do the equivalent of SPI_push before
passing control back to the SQL execution engine, so it is not necessary
for you to worry about this when using those functions.
Only when you are directly calling arbitrary code that might contain
SPI_connect calls do you need to issue
SPI_push and SPI_pop.
SPI_pop
SPI_pop
pop SPI stack to return from recursive SPI usage
SPI_pop
void SPI_pop(void)
Description
SPI_pop pops the previous environment from the
SPI call stack. See SPI_push.
SPI_execute
SPI_execute
execute a command
SPI_execute
int SPI_execute(const char * command, bool read_only, long count)
Description
SPI_execute executes the specified SQL command
for count rows. If read_only
is true>, the command must be read-only, and execution overhead
is somewhat reduced.
This function can only be called from a connected procedure.
If count is zero then the command is executed
for all rows that it applies to. If count
is greater than 0, then the number of rows for which the command
will be executed is restricted (much like a
LIMIT clause). For example:
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
will allow at most 5 rows to be inserted into the table.
You can pass multiple commands in one string, but later commands cannot
depend on the creation of objects earlier in the string, because the
whole string will be parsed and planned before execution begins.
SPI_execute returns the
result for the command executed last. The count
limit applies to each command separately, but it is not applied to
hidden commands generated by rules.
When read_only is false>,
SPI_execute increments the command
counter and computes a new snapshot> before executing each
command in the string. The snapshot does not actually change if the
current transaction isolation level is SERIALIZABLE>, but in
READ COMMITTED> mode the snapshot update allows each command to
see the results of newly committed transactions from other sessions.
This is essential for consistent behavior when the commands are modifying
the database.
When read_only is true>,
SPI_execute does not update either the snapshot
or the command counter, and it allows only plain SELECT>
commands to appear in the command string. The commands are executed
using the snapshot previously established for the surrounding query.
This execution mode is somewhat faster than the read/write mode due
to eliminating per-command overhead. It also allows genuinely
stable> functions to be built: since successive executions
will all use the same snapshot, there will be no change in the results.
It is generally unwise to mix read-only and read-write commands within
a single function using SPI; that could result in very confusing behavior,
since the read-only queries would not see the results of any database
updates done by the read-write queries.
The actual number of rows for which the (last) command was executed
is returned in the global variable SPI_processed.
If the return value of the function is SPI_OK_SELECT,
SPI_OK_INSERT_RETURNING,
SPI_OK_DELETE_RETURNING, or
SPI_OK_UPDATE_RETURNING,
then you can use the
global pointer SPITupleTable *SPI_tuptable to
access the result rows. Some utility commands (such as
EXPLAIN>) also return row sets, and SPI_tuptable>
will contain the result in these cases too.
The structure SPITupleTable is defined
thus:
typedef struct
{
MemoryContext tuptabcxt; /* memory context of result table */
uint32 alloced; /* number of alloced vals */
uint32 free; /* number of free vals */
TupleDesc tupdesc; /* row descriptor */
HeapTuple *vals; /* rows */
} SPITupleTable;
vals> is an array of pointers to rows. (The number
of valid entries is given by SPI_processed.)
tupdesc> is a row descriptor which you can pass to
SPI functions dealing with rows. tuptabcxt>,
alloced>, and free> are internal
fields not intended for use by SPI callers.
SPI_finish frees all
SPITupleTable>s allocated during the current
procedure. You can free a particular result table earlier, if you
are done with it, by calling SPI_freetuptable.
Arguments
const char * command
string containing command to execute
bool read_only
true> for read-only execution
long count
maximum number of rows to process or return
Return Value
If the execution of the command was successful then one of the
following (nonnegative) values will be returned:
SPI_OK_SELECT
if a SELECT (but not SELECT
INTO>) was executed
SPI_OK_SELINTO
if a SELECT INTO was executed
SPI_OK_INSERT
if an INSERT was executed
SPI_OK_DELETE
if a DELETE was executed
SPI_OK_UPDATE
if an UPDATE was executed
SPI_OK_INSERT_RETURNING
if an INSERT RETURNING was executed
SPI_OK_DELETE_RETURNING
if a DELETE RETURNING was executed
SPI_OK_UPDATE_RETURNING
if an UPDATE RETURNING was executed
SPI_OK_UTILITY
if a utility command (e.g., CREATE TABLE)
was executed
On error, one of the following negative values is returned:
SPI_ERROR_ARGUMENT
if command is NULL or
count is less than 0
SPI_ERROR_COPY
if COPY TO stdout> or COPY FROM stdin>
was attempted
SPI_ERROR_TRANSACTION
if a transaction manipulation command was attempted
(BEGIN>,
COMMIT>,
ROLLBACK>,
SAVEPOINT>,
PREPARE TRANSACTION>,
COMMIT PREPARED>,
ROLLBACK PREPARED>,
or any variant thereof)
SPI_ERROR_OPUNKNOWN
if the command type is unknown (shouldn't happen)
SPI_ERROR_UNCONNECTED
if called from an unconnected procedure
Notes
The functions SPI_execute,
SPI_exec,
SPI_execute_plan, and
SPI_execp change both
SPI_processed and
SPI_tuptable (just the pointer, not the contents
of the structure). Save these two global variables into local
procedure variables if you need to access the result table of
SPI_execute or a related function
across later calls.
SPI_exec
SPI_exec
execute a read/write command
SPI_exec
int SPI_exec(const char * command, long count)
Description
SPI_exec is the same as
SPI_execute, with the latter's
read_only parameter always taken as
false>.
Arguments
const char * command
string containing command to execute
long count
maximum number of rows to process or return
Return Value
See SPI_execute.
SPI_execute_with_args
SPI_execute_with_args
execute a command with out-of-line parameters
SPI_execute_with_args
int SPI_execute_with_args(const char *command,
int nargs, Oid *argtypes,
Datum *values, const char *nulls,
bool read_only, long count)
Description
SPI_execute_with_args executes a command that might
include references to externally supplied parameters. The command text
refers to a parameter as $n>, and
the call specifies data types and values for each such symbol.
read_only and count have
the same interpretation as in SPI_execute.
The main advantage of this routine compared to
SPI_execute is that data values can be inserted
into the command without tedious quoting/escaping, and thus with much
less risk of SQL-injection attacks.
Similar results can be achieved with SPI_prepare> followed by
SPI_execute_plan; however, when using this function
the query plan is customized to the specific parameter values provided.
For one-time query execution, this function should be preferred.
If the same command is to be executed with many different parameters,
either method might be faster, depending on the cost of re-planning
versus the benefit of custom plans.
Arguments
const char * command
command string
int nargs
number of input parameters ($1>, $2>, etc.)
Oid * argtypes
an array containing the OIDs of
the data types of the parameters
Datum * values
an array of actual parameter values
const char * nulls
an array describing which parameters are null
If nulls is NULL then
SPI_execute_with_args assumes that no parameters are
null.
bool read_only
true> for read-only execution
long count
maximum number of rows to process or return
Return Value
The return value is the same as for SPI_execute.
SPI_processed and
SPI_tuptable are set as in
SPI_execute if successful.
SPI_prepare
SPI_prepare
prepare a plan for a command, without executing it yet
SPI_prepare
SPIPlanPtr SPI_prepare(const char * command, int nargs, Oid * argtypes)
Description
SPI_prepare creates and returns an execution
plan for the specified command but doesn't execute the command.
This function should only be called from a connected procedure.
When the same or a similar command is to be executed repeatedly, it
might be advantageous to perform the planning only once.
SPI_prepare converts a command string into an
execution plan that can be executed repeatedly using
SPI_execute_plan.
A prepared command can be generalized by writing parameters
($1>, $2>, etc.) in place of what would be
constants in a normal command. The actual values of the parameters
are then specified when SPI_execute_plan is called.
This allows the prepared command to be used over a wider range of
situations than would be possible without parameters.
The plan returned by SPI_prepare can be used
only in the current invocation of the procedure, since
SPI_finish frees memory allocated for a plan.
But a plan can be saved for longer using the function
SPI_saveplan.
Arguments
const char * command
command string
int nargs
number of input parameters ($1>, $2>, etc.)
Oid * argtypes
pointer to an array containing the OIDs of
the data types of the parameters
Return Value
SPI_prepare returns a non-null pointer to an
execution plan. On error, NULL will be returned,
and SPI_result will be set to one of the same
error codes used by SPI_execute, except that
it is set to SPI_ERROR_ARGUMENT if
command is NULL, or if
nargs> is less than 0, or if nargs> is
greater than 0 and argtypes> is NULL.
Notes
SPIPlanPtr> is declared as a pointer to an opaque struct type in
spi.h>. It is unwise to try to access its contents
directly, as that makes your code much more likely to break in
future revisions of PostgreSQL.
There is a disadvantage to using parameters: since the planner does
not know the values that will be supplied for the parameters, it
might make worse planning choices than it would make for a normal
command with all constants visible.
SPI_prepare_cursor
SPI_prepare_cursor
prepare a plan for a command, without executing it yet
SPI_prepare_cursor
SPIPlanPtr SPI_prepare_cursor(const char * command, int nargs, Oid * argtypes, int cursorOptions)
Description
SPI_prepare_cursor is identical to
SPI_prepare, except that it also allows specification
of the planner's cursor options> parameter. This is a bitmask
having the values shown in nodes/parsenodes.h
for the options> field of DeclareCursorStmt>.
SPI_prepare always takes these options as zero.
Arguments
const char * command
command string
int nargs
number of input parameters ($1>, $2>, etc.)
Oid * argtypes
pointer to an array containing the OIDs of
the data types of the parameters
int cursorOptions
integer bitmask of cursor options; zero produces default behavior
Return Value
SPI_prepare_cursor has the same return conventions as
SPI_prepare.
Notes
Useful bits to set in cursorOptions> include
CURSOR_OPT_SCROLL,
CURSOR_OPT_NO_SCROLL, and
CURSOR_OPT_FAST_PLAN. Note in particular that
CURSOR_OPT_HOLD is ignored.
SPI_getargcount
SPI_getargcount
return the number of arguments needed by a plan
prepared by SPI_prepare
SPI_getargcount
int SPI_getargcount(SPIPlanPtr plan)
Description
SPI_getargcount returns the number of arguments needed
to execute a plan prepared by SPI_prepare.
Arguments
SPIPlanPtr plan
execution plan (returned by SPI_prepare)
Return Value
The count of expected arguments for the plan.
If the plan is NULL or invalid,
SPI_result is set to SPI_ERROR_ARGUMENT
and -1 is returned.
SPI_getargtypeid
SPI_getargtypeid
return the data type OID for an argument of
a plan prepared by SPI_prepare
SPI_getargtypeid
Oid SPI_getargtypeid(SPIPlanPtr plan, int argIndex)
Description
SPI_getargtypeid returns the OID representing the type
id for the argIndex'th argument of a plan prepared by
SPI_prepare. First argument is at index zero.
Arguments
SPIPlanPtr plan
execution plan (returned by SPI_prepare)
int argIndex
zero based index of the argument
Return Value
The type id of the argument at the given index.
If the plan is NULL or invalid,
or argIndex is less than 0 or
not less than the number of arguments declared for the
plan,
SPI_result is set to SPI_ERROR_ARGUMENT
and InvalidOid is returned.
SPI_is_cursor_plan
SPI_is_cursor_plan
return true if a plan
prepared by SPI_prepare can be used with
SPI_cursor_open
SPI_is_cursor_plan
bool SPI_is_cursor_plan(SPIPlanPtr plan)
Description
SPI_is_cursor_plan returns true
if a plan prepared by SPI_prepare can be passed
as an argument to SPI_cursor_open, or
false if that is not the case. The criteria are that the
plan represents one single command and that this
command returns tuples to the caller; for example, SELECT>
is allowed unless it contains an INTO> clause, and
UPDATE> is allowed only if it contains a RETURNING>
clause.
Arguments
SPIPlanPtr plan
execution plan (returned by SPI_prepare)
Return Value
true or false to indicate if the
plan can produce a cursor or not, with
SPI_result set to zero.
If it is not possible to determine the answer (for example,
if the plan is NULL or invalid,
or if called when not connected to SPI), then
SPI_result is set to a suitable error code
and false is returned.
SPI_execute_plan
SPI_execute_plan
execute a plan prepared by SPI_prepare
SPI_execute_plan
int SPI_execute_plan(SPIPlanPtr plan, Datum * values, const char * nulls,
bool read_only, long count)
Description
SPI_execute_plan executes a plan prepared by
SPI_prepare. read_only and
count have the same interpretation as in
SPI_execute.
Arguments
SPIPlanPtr plan
execution plan (returned by SPI_prepare)
Datum * values
An array of actual parameter values. Must have same length as the
plan's number of arguments.
const char * nulls
An array describing which parameters are null. Must have same length as
the plan's number of arguments.
n indicates a null value (entry in
values> will be ignored); a space indicates a
nonnull value (entry in values> is valid).
If nulls is NULL then
SPI_execute_plan assumes that no parameters are
null.
bool read_only
true> for read-only execution
long count
maximum number of rows to process or return
Return Value
The return value is the same as for SPI_execute,
with the following additional possible error (negative) results:
SPI_ERROR_ARGUMENT
if plan is NULL or invalid,
or count is less than 0
SPI_ERROR_PARAM
if values is NULL and
plan was prepared with some parameters
SPI_processed and
SPI_tuptable are set as in
SPI_execute if successful.
Notes
If one of the objects (a table, function, etc.) referenced by the
prepared plan is dropped during the session then the result of
SPI_execute_plan for this plan will be unpredictable.
SPI_execp
SPI_execp
execute a plan in read/write mode
SPI_execp
int SPI_execp(SPIPlanPtr plan, Datum * values, const char * nulls, long count)
Description
SPI_execp is the same as
SPI_execute_plan, with the latter's
read_only parameter always taken as
false>.
Arguments
SPIPlanPtr plan
execution plan (returned by SPI_prepare)
Datum * values
An array of actual parameter values. Must have same length as the
plan's number of arguments.
const char * nulls
An array describing which parameters are null. Must have same length as
the plan's number of arguments.
n indicates a null value (entry in
values> will be ignored); a space indicates a
nonnull value (entry in values> is valid).
If nulls is NULL then
SPI_execp assumes that no parameters are
null.
long count
maximum number of rows to process or return
Return Value
See SPI_execute_plan.
SPI_processed and
SPI_tuptable are set as in
SPI_execute if successful.
SPI_cursor_open
SPI_cursor_open
set up a cursor using a plan created with SPI_prepare
SPI_cursor_open
Portal SPI_cursor_open(const char * name, SPIPlanPtr plan,
Datum * values, const char * nulls,
bool read_only)
Description
SPI_cursor_open sets up a cursor (internally,
a portal) that will execute a plan prepared by
SPI_prepare. The parameters have the same
meanings as the corresponding parameters to
SPI_execute_plan.
Using a cursor instead of executing the plan directly has two
benefits. First, the result rows can be retrieved a few at a time,
avoiding memory overrun for queries that return many rows. Second,
a portal can outlive the current procedure (it can, in fact, live
to the end of the current transaction). Returning the portal name
to the procedure's caller provides a way of returning a row set as
result.
The passed-in data will be copied into the cursor's portal, so it
can be freed while the cursor still exists.
Arguments
const char * name
name for portal, or NULL to let the system
select a name
SPIPlanPtr plan
execution plan (returned by SPI_prepare)
Datum * values
An array of actual parameter values. Must have same length as the
plan's number of arguments.
const char * nulls
An array describing which parameters are null. Must have same length as
the plan's number of arguments.
n indicates a null value (entry in
values> will be ignored); a space indicates a
nonnull value (entry in values> is valid).
If nulls is NULL then
SPI_cursor_open assumes that no parameters are
null.
bool read_only
true> for read-only execution
Return Value
Pointer to portal containing the cursor. Note there is no error
return convention; any error will be reported via elog>.
SPI_cursor_open_with_args
SPI_cursor_open_with_args
set up a cursor using a query and parameters
SPI_cursor_open_with_args
Portal SPI_cursor_open_with_args(const char *name,
const char *command,
int nargs, Oid *argtypes,
Datum *values, const char *nulls,
bool read_only, int cursorOptions)
Description
SPI_cursor_open_with_args sets up a cursor
(internally, a portal) that will execute the specified query.
Most of the parameters have the same meanings as the corresponding
parameters to SPI_prepare_cursor
and SPI_cursor_open.
For one-time query execution, this function should be preferred
over SPI_prepare_cursor followed by
SPI_cursor_open.
If the same command is to be executed with many different parameters,
either method might be faster, depending on the cost of re-planning
versus the benefit of custom plans.
The passed-in data will be copied into the cursor's portal, so it
can be freed while the cursor still exists.
Arguments
const char * name
name for portal, or NULL to let the system
select a name
const char * command
command string
int nargs
number of input parameters ($1>, $2>, etc.)
Oid * argtypes
an array containing the OIDs of
the data types of the parameters
Datum * values
an array of actual parameter values
const char * nulls
an array describing which parameters are null
If nulls is NULL then
SPI_cursor_open_with_args assumes that no
parameters are null.
bool read_only
true> for read-only execution
int cursorOptions
integer bitmask of cursor options; zero produces default behavior
Return Value
Pointer to portal containing the cursor. Note there is no error
return convention; any error will be reported via elog>.
SPI_cursor_find
SPI_cursor_find
find an existing cursor by name
SPI_cursor_find
Portal SPI_cursor_find(const char * name)
Description
SPI_cursor_find finds an existing portal by
name. This is primarily useful to resolve a cursor name returned
as text by some other function.
Arguments
const char * name
name of the portal
Return Value
pointer to the portal with the specified name, or
NULL if none was found
SPI_cursor_fetch
SPI_cursor_fetch
fetch some rows from a cursor
SPI_cursor_fetch
void SPI_cursor_fetch(Portal portal, bool forward, long count)
Description
SPI_cursor_fetch fetches some rows from a
cursor. This is equivalent to a subset of the SQL command
FETCH> (see SPI_scroll_cursor_fetch
for more functionality).
Arguments
Portal portal
portal containing the cursor
bool forward
true for fetch forward, false for fetch backward
long count
maximum number of rows to fetch
Return Value
SPI_processed and
SPI_tuptable are set as in
SPI_execute if successful.
Notes
Fetching backward may fail if the cursor's plan was not created
with the CURSOR_OPT_SCROLL option.
SPI_cursor_move
SPI_cursor_move
move a cursor
SPI_cursor_move
void SPI_cursor_move(Portal portal, bool forward, long count)
Description
SPI_cursor_move skips over some number of rows
in a cursor. This is equivalent to a subset of the SQL command
MOVE> (see SPI_scroll_cursor_move
for more functionality).
Arguments
Portal portal
portal containing the cursor
bool forward
true for move forward, false for move backward
long count
maximum number of rows to move
Notes
Moving backward may fail if the cursor's plan was not created
with the CURSOR_OPT_SCROLL option.
SPI_scroll_cursor_fetch
SPI_scroll_cursor_fetch
fetch some rows from a cursor
SPI_scroll_cursor_fetch
void SPI_scroll_cursor_fetch(Portal portal, FetchDirection direction, long count)
Description
SPI_scroll_cursor_fetch fetches some rows from a
cursor. This is equivalent to the SQL command FETCH>.
Arguments
Portal portal
portal containing the cursor
FetchDirection direction
one of FETCH_FORWARD,
FETCH_BACKWARD,
FETCH_ABSOLUTE or
FETCH_RELATIVE
long count
number of rows to fetch for
FETCH_FORWARD or
FETCH_BACKWARD; absolute row number to fetch for
FETCH_ABSOLUTE; or relative row number to fetch for
FETCH_RELATIVE
Return Value
SPI_processed and
SPI_tuptable are set as in
SPI_execute if successful.
Notes
See the SQL command
for details of the interpretation of the
direction and
count parameters.
Direction values other than FETCH_FORWARD
may fail if the cursor's plan was not created
with the CURSOR_OPT_SCROLL option.
SPI_scroll_cursor_move
SPI_scroll_cursor_move
move a cursor
SPI_scroll_cursor_move
void SPI_scroll_cursor_move(Portal portal, FetchDirection direction, long count)
Description
SPI_scroll_cursor_move skips over some number of rows
in a cursor. This is equivalent to the SQL command
MOVE>.
Arguments
Portal portal
portal containing the cursor
FetchDirection direction
one of FETCH_FORWARD,
FETCH_BACKWARD,
FETCH_ABSOLUTE or
FETCH_RELATIVE
long count
number of rows to move for
FETCH_FORWARD or
FETCH_BACKWARD; absolute row number to move to for
FETCH_ABSOLUTE; or relative row number to move to for
FETCH_RELATIVE
Return Value
SPI_processed is set as in
SPI_execute if successful.
SPI_tuptable is set to NULL>, since
no rows are returned by this function.
Notes
See the SQL command
for details of the interpretation of the
direction and
count parameters.
Direction values other than FETCH_FORWARD
may fail if the cursor's plan was not created
with the CURSOR_OPT_SCROLL option.
SPI_cursor_close
SPI_cursor_close
close a cursor
SPI_cursor_close
void SPI_cursor_close(Portal portal)
Description
SPI_cursor_close closes a previously created
cursor and releases its portal storage.
All open cursors are closed automatically at the end of a
transaction. SPI_cursor_close need only be
invoked if it is desirable to release resources sooner.
Arguments
Portal portal
portal containing the cursor
SPI_saveplan
SPI_saveplan
save a plan
SPI_saveplan
SPIPlanPtr SPI_saveplan(SPIPlanPtr plan)
Description
SPI_saveplan saves a passed plan (prepared by
SPI_prepare) in memory that will not be freed
by SPI_finish nor by the transaction manager,
and returns a pointer to the saved plan. This gives you the
ability to reuse prepared plans in the subsequent invocations of
your procedure in the current session.
Arguments
SPIPlanPtr plan
the plan to be saved
Return Value
Pointer to the saved plan; NULL if unsuccessful.
On error, SPI_result is set thus:
SPI_ERROR_ARGUMENT
if plan is NULL or invalid
SPI_ERROR_UNCONNECTED
if called from an unconnected procedure
Notes
The passed-in plan is not freed, so you might wish to do
SPI_freeplan on it to avoid leaking memory
until SPI_finish>.
If one of the objects (a table, function, etc.) referenced by the
prepared plan is dropped or redefined, then future executions of
SPI_execute_plan may fail or return different
results than the plan initially indicates.
Interface Support Functions
The functions described here provide an interface for extracting
information from result sets returned by SPI_execute> and
other SPI functions.
All functions described in this section can be used by both
connected and unconnected procedures.
SPI_fname
SPI_fname
determine the column name for the specified column number
SPI_fname
char * SPI_fname(TupleDesc rowdesc, int colnumber)
Description
SPI_fname returns a copy of the column name of the
specified column. (You can use pfree to
release the copy of the name when you don't need it anymore.)
Arguments
TupleDesc rowdesc
input row description
int colnumber
column number (count starts at 1)
Return Value
The column name; NULL if
colnumber is out of range.
SPI_result set to
SPI_ERROR_NOATTRIBUTE on error.
SPI_fnumber
SPI_fnumber
determine the column number for the specified column name
SPI_fnumber
int SPI_fnumber(TupleDesc rowdesc, const char * colname)
Description
SPI_fnumber returns the column number for the
column with the specified name.
If colname refers to a system column (e.g.,
oid>) then the appropriate negative column number will
be returned. The caller should be careful to test the return value
for exact equality to SPI_ERROR_NOATTRIBUTE to
detect an error; testing the result for less than or equal to 0 is
not correct unless system columns should be rejected.
Arguments
TupleDesc rowdesc
input row description
const char * colname
column name
Return Value
Column number (count starts at 1), or
SPI_ERROR_NOATTRIBUTE if the named column was not
found.
SPI_getvalue
SPI_getvalue
return the string value of the specified column
SPI_getvalue
char * SPI_getvalue(HeapTuple row, TupleDesc rowdesc, int colnumber)
Description
SPI_getvalue returns the string representation
of the value of the specified column.
The result is returned in memory allocated using
palloc. (You can use
pfree to release the memory when you don't
need it anymore.)
Arguments
HeapTuple row
input row to be examined
TupleDesc rowdesc
input row description
int colnumber
column number (count starts at 1)
Return Value
Column value, or NULL if the column is null,
colnumber is out of range
(SPI_result is set to
SPI_ERROR_NOATTRIBUTE), or no output function is
available (SPI_result is set to
SPI_ERROR_NOOUTFUNC).
SPI_getbinval
SPI_getbinval
return the binary value of the specified column
SPI_getbinval
Datum SPI_getbinval(HeapTuple row, TupleDesc rowdesc, int colnumber, bool * isnull)
Description
SPI_getbinval returns the value of the
specified column in the internal form (as type Datum).
This function does not allocate new space for the datum. In the
case of a pass-by-reference data type, the return value will be a
pointer into the passed row.
Arguments
HeapTuple row
input row to be examined
TupleDesc rowdesc
input row description
int colnumber
column number (count starts at 1)
bool * isnull
flag for a null value in the column
Return Value
The binary value of the column is returned. The variable pointed
to by isnull is set to true if the column is
null, else to false.
SPI_result is set to
SPI_ERROR_NOATTRIBUTE on error.
SPI_gettype
SPI_gettype
return the data type name of the specified column
SPI_gettype
char * SPI_gettype(TupleDesc rowdesc, int colnumber)
Description
SPI_gettype returns a copy of the data type name of the
specified column. (You can use pfree to
release the copy of the name when you don't need it anymore.)
Arguments
TupleDesc rowdesc
input row description
int colnumber
column number (count starts at 1)
Return Value
The data type name of the specified column, or
NULL on error. SPI_result is
set to SPI_ERROR_NOATTRIBUTE on error.
SPI_gettypeid
SPI_gettypeid
return the data type OID of the specified column
SPI_gettypeid
Oid SPI_gettypeid(TupleDesc rowdesc, int colnumber)
Description
SPI_gettypeid returns the
OID of the data type of the specified column.
Arguments
TupleDesc rowdesc
input row description
int colnumber
column number (count starts at 1)
Return Value
The OID of the data type of the specified column
or InvalidOid on error. On error,
SPI_result is set to
SPI_ERROR_NOATTRIBUTE.
SPI_getrelname
SPI_getrelname
return the name of the specified relation
SPI_getrelname
char * SPI_getrelname(Relation rel)
Description
SPI_getrelname returns a copy of the name of the
specified relation. (You can use pfree to
release the copy of the name when you don't need it anymore.)
Arguments
Relation rel
input relation
Return Value
The name of the specified relation.
SPI_getnspname
SPI_getnspname
return the namespace of the specified relation
SPI_getnspname
char * SPI_getnspname(Relation rel)
Description
SPI_getnspname returns a copy of the name of
the namespace that the specified Relation
belongs to. This is equivalent to the relation's schema. You should
pfree the return value of this function when
you are finished with it.
Arguments
Relation rel
input relation
Return Value
The name of the specified relation's namespace.
Memory Management
PostgreSQL allocates memory within
memory contextsmemory
contextin SPI, which provide a convenient method of
managing allocations made in many different places that need to
live for differing amounts of time. Destroying a context releases
all the memory that was allocated in it. Thus, it is not necessary
to keep track of individual objects to avoid memory leaks; instead
only a relatively small number of contexts have to be managed.
palloc and related functions allocate memory
from the current> context.
SPI_connect creates a new memory context and
makes it current. SPI_finish restores the
previous current memory context and destroys the context created by
SPI_connect. These actions ensure that
transient memory allocations made inside your procedure are
reclaimed at procedure exit, avoiding memory leakage.
However, if your procedure needs to return an object in allocated
memory (such as a value of a pass-by-reference data type), you
cannot allocate that memory using palloc, at
least not while you are connected to SPI. If you try, the object
will be deallocated by SPI_finish, and your
procedure will not work reliably. To solve this problem, use
SPI_palloc to allocate memory for your return
object. SPI_palloc allocates memory in the
upper executor context
, that is, the memory context
that was current when SPI_connect was called,
which is precisely the right context for a value returned from your
procedure.
If SPI_palloc is called while the procedure is
not connected to SPI, then it acts the same as a normal
palloc. Before a procedure connects to the
SPI manager, the current memory context is the upper executor
context, so all allocations made by the procedure via
palloc or by SPI utility functions are made in
this context.
When SPI_connect is called, the private
context of the procedure, which is created by
SPI_connect, is made the current context. All
allocations made by palloc,
repalloc, or SPI utility functions (except for
SPI_copytuple,
SPI_returntuple,
SPI_modifytuple, and
SPI_palloc) are made in this context. When a
procedure disconnects from the SPI manager (via
SPI_finish) the current context is restored to
the upper executor context, and all allocations made in the
procedure memory context are freed and cannot be used any more.
All functions described in this section can be used by both
connected and unconnected procedures. In an unconnected procedure,
they act the same as the underlying ordinary server functions
(palloc>, etc.).
SPI_palloc
SPI_palloc
allocate memory in the upper executor context
SPI_palloc
void * SPI_palloc(Size size)
Description
SPI_palloc allocates memory in the upper
executor context.
Arguments
Size size
size in bytes of storage to allocate
Return Value
pointer to new storage space of the specified size
SPI_repalloc
SPI_repalloc
reallocate memory in the upper executor context
SPI_repalloc
void * SPI_repalloc(void * pointer, Size size)
Description
SPI_repalloc changes the size of a memory
segment previously allocated using SPI_palloc.
This function is no longer different from plain
repalloc. It's kept just for backward
compatibility of existing code.
Arguments
void * pointer
pointer to existing storage to change
Size size
size in bytes of storage to allocate
Return Value
pointer to new storage space of specified size with the contents
copied from the existing area
SPI_pfree
SPI_pfree
free memory in the upper executor context
SPI_pfree
void SPI_pfree(void * pointer)
Description
SPI_pfree frees memory previously allocated
using SPI_palloc or
SPI_repalloc.
This function is no longer different from plain
pfree. It's kept just for backward
compatibility of existing code.
Arguments
void * pointer
pointer to existing storage to free
SPI_copytuple
SPI_copytuple
make a copy of a row in the upper executor context
SPI_copytuple
HeapTuple SPI_copytuple(HeapTuple row)
Description
SPI_copytuple makes a copy of a row in the
upper executor context. This is normally used to return a modified
row from a trigger. In a function declared to return a composite
type, use SPI_returntuple instead.
Arguments
HeapTuple row
row to be copied
Return Value
the copied row; NULL only if
tuple is NULL
SPI_returntuple
SPI_returntuple
prepare to return a tuple as a Datum
SPI_returntuple
HeapTupleHeader SPI_returntuple(HeapTuple row, TupleDesc rowdesc)
Description
SPI_returntuple makes a copy of a row in
the upper executor context, returning it in the form of a row type Datum.
The returned pointer need only be converted to Datum via PointerGetDatum
before returning.
Note that this should be used for functions that are declared to return
composite types. It is not used for triggers; use
SPI_copytuple> for returning a modified row in a trigger.
Arguments
HeapTuple row
row to be copied
TupleDesc rowdesc
descriptor for row (pass the same descriptor each time for most
effective caching)
Return Value
HeapTupleHeader pointing to copied row;
NULL only if
row or rowdesc is
NULL
SPI_modifytuple
SPI_modifytuple
create a row by replacing selected fields of a given row
SPI_modifytuple
HeapTuple SPI_modifytuple(Relation rel, HeapTuple row, ncols, colnum, Datum * values, const char * nulls)
Description
SPI_modifytuple creates a new row by
substituting new values for selected columns, copying the original
row's columns at other positions. The input row is not modified.
Arguments
Relation rel
Used only as the source of the row descriptor for the row.
(Passing a relation rather than a row descriptor is a
misfeature.)
HeapTuple row
row to be modified
int ncols
number of column numbers in the array
colnum
int * colnum
array of the numbers of the columns that are to be changed
(column numbers start at 1)
Datum * values
new values for the specified columns
const char * Nulls
which new values are null, if any (see
SPI_execute_plan for the format)
Return Value
new row with modifications, allocated in the upper executor
context; NULL only if row
is NULL
On error, SPI_result is set as follows:
SPI_ERROR_ARGUMENT
if rel> is NULL>, or if
row> is NULL>, or if ncols>
is less than or equal to 0, or if colnum> is
NULL>, or if values> is NULL>.
SPI_ERROR_NOATTRIBUTE
if colnum> contains an invalid column number (less
than or equal to 0 or greater than the number of column in
row>)
SPI_freetuple
SPI_freetuple
free a row allocated in the upper executor context
SPI_freetuple
void SPI_freetuple(HeapTuple row)
Description
SPI_freetuple frees a row previously allocated
in the upper executor context.
This function is no longer different from plain
heap_freetuple. It's kept just for backward
compatibility of existing code.
Arguments
HeapTuple row
row to free
SPI_freetuptable
SPI_freetuptable
free a row set created by SPI_execute> or a similar
function
SPI_freetuptable
void SPI_freetuptable(SPITupleTable * tuptable)
Description
SPI_freetuptable frees a row set created by a
prior SPI command execution function, such as
SPI_execute>. Therefore, this function is usually called
with the global variable SPI_tupletable as
argument.
This function is useful if a SPI procedure needs to execute
multiple commands and does not want to keep the results of earlier
commands around until it ends. Note that any unfreed row sets will
be freed anyway at SPI_finish>.
Arguments
SPITupleTable * tuptable
pointer to row set to free
SPI_freeplan
SPI_freeplan
free a previously saved plan
SPI_freeplan
int SPI_freeplan(SPIPlanPtr plan)
Description
SPI_freeplan releases a command execution plan
previously returned by SPI_prepare or saved by
SPI_saveplan.
Arguments
SPIPlanPtr plan
pointer to plan to free
Return Value
SPI_ERROR_ARGUMENT if plan
is NULL or invalid
Visibility of Data Changes
The following rules govern the visibility of data changes in
functions that use SPI (or any other C function):
During the execution of an SQL command, any data changes made by
the command are invisible to the command itself. For
example, in:
INSERT INTO a SELECT * FROM a;
the inserted rows are invisible to the SELECT
part.
Changes made by a command C are visible to all commands that are
started after C, no matter whether they are started inside C
(during the execution of C) or after C is done.
Commands executed via SPI inside a function called by an SQL command
(either an ordinary function or a trigger) follow one or the
other of the above rules depending on the read/write flag passed
to SPI. Commands executed in read-only mode follow the first
rule: they cannot see changes of the calling command. Commands executed
in read-write mode follow the second rule: they can see all changes made
so far.
All standard procedural languages set the SPI read-write mode
depending on the volatility attribute of the function. Commands of
STABLE> and IMMUTABLE> functions are done in
read-only mode, while commands of VOLATILE> functions are
done in read-write mode. While authors of C functions are able to
violate this convention, it's unlikely to be a good idea to do so.
The next section contains an example that illustrates the
application of these rules.
Examples
This section contains a very simple example of SPI usage. The
procedure execq takes an SQL command as its
first argument and a row count as its second, executes the command
using SPI_exec and returns the number of rows
that were processed by the command. You can find more complex
examples for SPI in the source tree in
src/test/regress/regress.c and in
contrib/spi.
#include "executor/spi.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
int execq(text *sql, int cnt);
int
execq(text *sql, int cnt)
{
char *command;
int ret;
int proc;
/* Convert given text object to a C string */
command = text_to_cstring(sql);
SPI_connect();
ret = SPI_exec(command, cnt);
proc = SPI_processed;
/*
* If some rows were fetched, print them via elog(INFO).
*/
if (ret > 0 && SPI_tuptable != NULL)
{
TupleDesc tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
char buf[8192];
int i, j;
for (j = 0; j < proc; j++)
{
HeapTuple tuple = tuptable->vals[j];
for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
SPI_getvalue(tuple, tupdesc, i),
(i == tupdesc->natts) ? " " : " |");
elog(INFO, "EXECQ: %s", buf);
}
}
SPI_finish();
pfree(command);
return (proc);
}
(This function uses call convention version 0, to make the example
easier to understand. In real applications you should use the new
version 1 interface.)
This is how you declare the function after having compiled it into
a shared library (details are in .):
CREATE FUNCTION execq(text, integer) RETURNS integer
AS 'filename'
LANGUAGE C;
Here is a sample session:
=> SELECT execq('CREATE TABLE a (x integer)', 0);
execq
-------
0
(1 row)
=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 0 1
=> SELECT execq('SELECT * FROM a', 0);
INFO: EXECQ: 0 -- inserted by execq
INFO: EXECQ: 1 -- returned by execq and inserted by upper INSERT
execq
-------
2
(1 row)
=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
execq
-------
1
(1 row)
=> SELECT execq('SELECT * FROM a', 10);
INFO: EXECQ: 0
INFO: EXECQ: 1
INFO: EXECQ: 2 -- 0 + 2, only one row inserted - as specified
execq
-------
3 -- 10 is the max value only, 3 is the real number of rows
(1 row)
=> DELETE FROM a;
DELETE 3
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INSERT 0 1
=> SELECT * FROM a;
x
---
1 -- no rows in a (0) + 1
(1 row)
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INFO: EXECQ: 1
INSERT 0 1
=> SELECT * FROM a;
x
---
1
2 -- there was one row in a + 1
(2 rows)
-- This demonstrates the data changes visibility rule:
=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
INFO: EXECQ: 1
INFO: EXECQ: 2
INFO: EXECQ: 1
INFO: EXECQ: 2
INFO: EXECQ: 2
INSERT 0 2
=> SELECT * FROM a;
x
---
1
2
2 -- 2 rows * 1 (x in first row)
6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
(4 rows) ^^^^^^
rows visible to execq() in different invocations