Server Programming InterfaceSPI
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 FunctionsSPI_connect3SPI_connectconnect a procedure to the SPI managerSPI_connect
int SPI_connect(void)
DescriptionSPI_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 ValueSPI_OK_CONNECT
on success
SPI_ERROR_CONNECT
on error
SPI_finish3SPI_finishdisconnect a procedure from the SPI managerSPI_finish
int SPI_finish(void)
DescriptionSPI_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 ValueSPI_OK_FINISH
if properly disconnected
SPI_ERROR_UNCONNECTED
if called from an unconnected procedure
SPI_push3SPI_pushpush SPI stack to allow recursive SPI usageSPI_push
void SPI_push(void)
DescriptionSPI_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_pop3SPI_poppop SPI stack to return from recursive SPI usageSPI_pop
void SPI_pop(void)
DescriptionSPI_pop pops the previous environment from the
SPI call stack. See SPI_push.
SPI_execute3SPI_executeexecute a commandSPI_execute
int SPI_execute(const char * command, bool read_only, long count)
DescriptionSPI_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.
Argumentsconst char * command
string containing command to execute
bool read_onlytrue> 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
SPI_OK_REWRITTEN
if the command was rewritten into another kind of command (e.g.,
UPDATE became an INSERT) by a rule.
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_exec3SPI_execexecute a read/write commandSPI_exec
int SPI_exec(const char * command, long count)
DescriptionSPI_exec is the same as
SPI_execute, with the latter's
read_only parameter always taken as
false>.
Argumentsconst 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_args3SPI_execute_with_argsexecute a command with out-of-line parametersSPI_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)
DescriptionSPI_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.
Argumentsconst 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_onlytrue> 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_prepare3SPI_prepareprepare a plan for a command, without executing it yetSPI_prepare
SPIPlanPtr SPI_prepare(const char * command, int nargs, Oid * argtypes)
DescriptionSPI_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.
Argumentsconst 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 ValueSPI_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.
NotesSPIPlanPtr> 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_cursor3SPI_prepare_cursorprepare a plan for a command, without executing it yetSPI_prepare_cursor
SPIPlanPtr SPI_prepare_cursor(const char * command, int nargs,
Oid * argtypes, int cursorOptions)
DescriptionSPI_prepare_cursor is identical to
SPI_prepare, except that it also allows specification
of the planner's cursor options> parameter. This is a bit mask
having the values shown in nodes/parsenodes.h
for the options> field of DeclareCursorStmt>.
SPI_prepare always takes the cursor options as zero.
Argumentsconst 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 bit mask of cursor options; zero produces default behavior
Return ValueSPI_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_prepare_params3SPI_prepare_paramsprepare a plan for a command, without executing it yetSPI_prepare_params
SPIPlanPtr SPI_prepare_params(const char * command,
ParserSetupHook parserSetup,
void * parserSetupArg,
int cursorOptions)
DescriptionSPI_prepare_params creates and returns an execution
plan for the specified command, but doesn't execute the command.
This function is equivalent to SPI_prepare_cursor,
with the addition that the caller can specify parser hook functions
to control the parsing of external parameter references.
Argumentsconst char * command
command string
ParserSetupHook parserSetup
Parser hook setup function
void * parserSetupArg
passthrough argument for parserSetupint cursorOptions
integer bit mask of cursor options; zero produces default behavior
Return ValueSPI_prepare_params has the same return conventions as
SPI_prepare.
SPI_getargcount3SPI_getargcountreturn the number of arguments needed by a plan
prepared by SPI_prepareSPI_getargcount
int SPI_getargcount(SPIPlanPtr plan)
DescriptionSPI_getargcount returns the number of arguments needed
to execute a plan prepared by SPI_prepare.
ArgumentsSPIPlanPtr 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_getargtypeid3SPI_getargtypeidreturn the data type OID for an argument of
a plan prepared by SPI_prepareSPI_getargtypeid
Oid SPI_getargtypeid(SPIPlanPtr plan, int argIndex)
DescriptionSPI_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.
ArgumentsSPIPlanPtr 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_plan3SPI_is_cursor_planreturn true if a plan
prepared by SPI_prepare can be used with
SPI_cursor_openSPI_is_cursor_plan
bool SPI_is_cursor_plan(SPIPlanPtr plan)
DescriptionSPI_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.
ArgumentsSPIPlanPtr plan
execution plan (returned by SPI_prepare)
Return Valuetrue 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_plan3SPI_execute_planexecute a plan prepared by SPI_prepareSPI_execute_plan
int SPI_execute_plan(SPIPlanPtr plan, Datum * values, const char * nulls,
bool read_only, long count)
DescriptionSPI_execute_plan executes a plan prepared by
SPI_prepare. read_only and
count have the same interpretation as in
SPI_execute.
ArgumentsSPIPlanPtr 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_onlytrue> 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.
SPI_execute_plan_with_paramlist3SPI_execute_plan_with_paramlistexecute a plan prepared by SPI_prepareSPI_execute_plan_with_paramlist
int SPI_execute_plan_with_paramlist(SPIPlanPtr plan,
ParamListInfo params,
bool read_only,
long count)
DescriptionSPI_execute_plan_with_paramlist executes a plan
prepared by SPI_prepare.
This function is equivalent to SPI_execute_plan
except that information about the parameter values to be passed to the
query is presented differently. The ParamListInfo>
representation can be convenient for passing down values that are
already available in that format. It also supports use of dynamic
parameter sets via hook functions specified in ParamListInfo>.
ArgumentsSPIPlanPtr plan
execution plan (returned by SPI_prepare)
ParamListInfo params
data structure containing parameter types and values; NULL if none
bool read_onlytrue> 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_plan.
SPI_processed and
SPI_tuptable are set as in
SPI_execute_plan if successful.
SPI_execp3SPI_execpexecute a plan in read/write modeSPI_execp
int SPI_execp(SPIPlanPtr plan, Datum * values, const char * nulls, long count)
DescriptionSPI_execp is the same as
SPI_execute_plan, with the latter's
read_only parameter always taken as
false>.
ArgumentsSPIPlanPtr 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_open3SPI_cursor_openset up a cursor using a plan created with SPI_prepareSPI_cursor_open
Portal SPI_cursor_open(const char * name, SPIPlanPtr plan,
Datum * values, const char * nulls,
bool read_only)
DescriptionSPI_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 parameter data will be copied into the cursor's portal, so it
can be freed while the cursor still exists.
Argumentsconst 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_onlytrue> 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_args3SPI_cursor_open_with_argsset up a cursor using a query and parametersSPI_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)
DescriptionSPI_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 parameter data will be copied into the cursor's portal, so it
can be freed while the cursor still exists.
Argumentsconst 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_onlytrue> for read-only execution
int cursorOptions
integer bit mask 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_open_with_paramlist3SPI_cursor_open_with_paramlistset up a cursor using parametersSPI_cursor_open_with_paramlist
Portal SPI_cursor_open_with_paramlist(const char *name,
SPIPlanPtr plan,
ParamListInfo params,
bool read_only)
DescriptionSPI_cursor_open_with_paramlist sets up a cursor
(internally, a portal) that will execute a plan prepared by
SPI_prepare.
This function is equivalent to SPI_cursor_open
except that information about the parameter values to be passed to the
query is presented differently. The ParamListInfo>
representation can be convenient for passing down values that are
already available in that format. It also supports use of dynamic
parameter sets via hook functions specified in ParamListInfo>.
The passed-in parameter data will be copied into the cursor's portal, so it
can be freed while the cursor still exists.
Argumentsconst char * name
name for portal, or NULL to let the system
select a name
SPIPlanPtr plan
execution plan (returned by SPI_prepare)
ParamListInfo params
data structure containing parameter types and values; NULL if none
bool read_onlytrue> 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_find3SPI_cursor_findfind an existing cursor by nameSPI_cursor_find
Portal SPI_cursor_find(const char * name)
DescriptionSPI_cursor_find finds an existing portal by
name. This is primarily useful to resolve a cursor name returned
as text by some other function.
Argumentsconst char * name
name of the portal
Return Value
pointer to the portal with the specified name, or
NULL if none was found
SPI_cursor_fetch3SPI_cursor_fetchfetch some rows from a cursorSPI_cursor_fetch
void SPI_cursor_fetch(Portal portal, bool forward, long count)
DescriptionSPI_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).
ArgumentsPortal portal
portal containing the cursor
bool forward
true for fetch forward, false for fetch backward
long count
maximum number of rows to fetch
Return ValueSPI_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_move3SPI_cursor_movemove a cursorSPI_cursor_move
void SPI_cursor_move(Portal portal, bool forward, long count)
DescriptionSPI_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).
ArgumentsPortal 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_fetch3SPI_scroll_cursor_fetchfetch some rows from a cursorSPI_scroll_cursor_fetch
void SPI_scroll_cursor_fetch(Portal portal, FetchDirection direction,
long count)
DescriptionSPI_scroll_cursor_fetch fetches some rows from a
cursor. This is equivalent to the SQL command FETCH>.
ArgumentsPortal portal
portal containing the cursor
FetchDirection direction
one of FETCH_FORWARD,
FETCH_BACKWARD,
FETCH_ABSOLUTE or
FETCH_RELATIVElong 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_RELATIVEReturn ValueSPI_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_move3SPI_scroll_cursor_movemove a cursorSPI_scroll_cursor_move
void SPI_scroll_cursor_move(Portal portal, FetchDirection direction,
long count)
DescriptionSPI_scroll_cursor_move skips over some number of rows
in a cursor. This is equivalent to the SQL command
MOVE>.
ArgumentsPortal portal
portal containing the cursor
FetchDirection direction
one of FETCH_FORWARD,
FETCH_BACKWARD,
FETCH_ABSOLUTE or
FETCH_RELATIVElong 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_RELATIVEReturn ValueSPI_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_close3SPI_cursor_closeclose a cursorSPI_cursor_close
void SPI_cursor_close(Portal portal)
DescriptionSPI_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.
ArgumentsPortal portal
portal containing the cursor
SPI_saveplan3SPI_saveplansave a planSPI_saveplan
SPIPlanPtr SPI_saveplan(SPIPlanPtr plan)
DescriptionSPI_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.
ArgumentsSPIPlanPtr 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_fname3SPI_fnamedetermine the column name for the specified column numberSPI_fname
char * SPI_fname(TupleDesc rowdesc, int colnumber)
DescriptionSPI_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.)
ArgumentsTupleDesc 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_fnumber3SPI_fnumberdetermine the column number for the specified column nameSPI_fnumber
int SPI_fnumber(TupleDesc rowdesc, const char * colname)
DescriptionSPI_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.
ArgumentsTupleDesc 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_getvalue3SPI_getvaluereturn the string value of the specified columnSPI_getvalue
char * SPI_getvalue(HeapTuple row, TupleDesc rowdesc, int colnumber)
DescriptionSPI_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.)
ArgumentsHeapTuple 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_getbinval3SPI_getbinvalreturn the binary value of the specified columnSPI_getbinval
Datum SPI_getbinval(HeapTuple row, TupleDesc rowdesc, int colnumber,
bool * isnull)
DescriptionSPI_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.
ArgumentsHeapTuple 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_gettype3SPI_gettypereturn the data type name of the specified columnSPI_gettype
char * SPI_gettype(TupleDesc rowdesc, int colnumber)
DescriptionSPI_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.)
ArgumentsTupleDesc 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_gettypeid3SPI_gettypeidreturn the data type OID of the specified columnSPI_gettypeid
Oid SPI_gettypeid(TupleDesc rowdesc, int colnumber)
DescriptionSPI_gettypeid returns the
OID of the data type of the specified column.
ArgumentsTupleDesc 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_getrelname3SPI_getrelnamereturn the name of the specified relationSPI_getrelname
char * SPI_getrelname(Relation rel)
DescriptionSPI_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.)
ArgumentsRelation rel
input relation
Return Value
The name of the specified relation.
SPI_getnspname3SPI_getnspnamereturn the namespace of the specified relationSPI_getnspname
char * SPI_getnspname(Relation rel)
DescriptionSPI_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.
ArgumentsRelation rel
input relation
Return Value
The name of the specified relation's namespace.
Memory ManagementPostgreSQL 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_palloc3SPI_pallocallocate memory in the upper executor contextSPI_palloc
void * SPI_palloc(Size size)
DescriptionSPI_palloc allocates memory in the upper
executor context.
ArgumentsSize size
size in bytes of storage to allocate
Return Value
pointer to new storage space of the specified size
SPI_repalloc3SPI_repallocreallocate memory in the upper executor contextSPI_repalloc
void * SPI_repalloc(void * pointer, Size size)
DescriptionSPI_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.
Argumentsvoid * 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_pfree3SPI_pfreefree memory in the upper executor contextSPI_pfree
void SPI_pfree(void * pointer)
DescriptionSPI_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.
Argumentsvoid * pointer
pointer to existing storage to free
SPI_copytuple3SPI_copytuplemake a copy of a row in the upper executor contextSPI_copytuple
HeapTuple SPI_copytuple(HeapTuple row)
DescriptionSPI_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.
ArgumentsHeapTuple row
row to be copied
Return Value
the copied row; NULL only if
tuple is NULLSPI_returntuple3SPI_returntupleprepare to return a tuple as a DatumSPI_returntuple
HeapTupleHeader SPI_returntuple(HeapTuple row, TupleDesc rowdesc)
DescriptionSPI_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.
ArgumentsHeapTuple row
row to be copied
TupleDesc rowdesc
descriptor for row (pass the same descriptor each time for most
effective caching)
Return ValueHeapTupleHeader pointing to copied row;
NULL only if
row or rowdesc is
NULLSPI_modifytuple3SPI_modifytuplecreate a row by replacing selected fields of a given rowSPI_modifytuple
HeapTuple SPI_modifytuple(Relation rel, HeapTuple row, int ncols,
int * colnum, Datum * values, const char * nulls)
DescriptionSPI_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.
ArgumentsRelation 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
colnumint * 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_freetuple3SPI_freetuplefree a row allocated in the upper executor contextSPI_freetuple
void SPI_freetuple(HeapTuple row)
DescriptionSPI_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.
ArgumentsHeapTuple row
row to free
SPI_freetuptable3SPI_freetuptablefree a row set created by SPI_execute> or a similar
functionSPI_freetuptable
void SPI_freetuptable(SPITupleTable * tuptable)
DescriptionSPI_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>.
ArgumentsSPITupleTable * tuptable
pointer to row set to free
SPI_freeplan3SPI_freeplanfree a previously saved planSPI_freeplan
int SPI_freeplan(SPIPlanPtr plan)
DescriptionSPI_freeplan releases a command execution plan
previously returned by SPI_prepare or saved by
SPI_saveplan.
ArgumentsSPIPlanPtr plan
pointer to plan to free
Return ValueSPI_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 "postgres.h"
#include "executor/spi.h"
#include "utils/builtins.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