Server Programming Interface {#spi}
============================

SPI

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

> **Note**
>
> The available procedural languages provide various means to execute
> SQL commands from functions. Most of these facilities are based on
> SPI, so this documentation might be of use for users of those
> languages as well.

Note that if a command invoked via SPI fails, then control will not be
returned to your C function. Rather, the transaction or subtransaction
in which your C function 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.

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-interface}
===================

SPI\_connect

SPI\_connect\_ext

SPI\_connect

3

SPI\_connect

SPI\_connect\_ext

connect a C function to the SPI manager

int SPI\_connect(void)

int SPI\_connect\_ext(int

options

)

Description
===========

`SPI_connect` opens a connection from a C function 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 C
functions.

`SPI_connect_ext` does the same but has an argument that allows passing
option flags. Currently, the following option values are available:

`SPI_OPT_NONATOMIC`

:   Sets the SPI connection to be nonatomic, which means that
    transaction control calls (`SPI_commit`, `SPI_rollback`) are
    allowed. Otherwise, calling those functions will result in an
    immediate error.

`SPI_connect()` is equivalent to `SPI_connect_ext(0)`.

Return Value
============

`SPI_OK_CONNECT`

:   on success

`SPI_ERROR_CONNECT`

:   on error

SPI\_finish

SPI\_finish

3

SPI\_finish

disconnect a C function from the SPI manager

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 C function\'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.

Return Value
============

`SPI_OK_FINISH`

:   if properly disconnected

`SPI_ERROR_UNCONNECTED`

:   if called from an unconnected C function

SPI\_execute

SPI\_execute

3

SPI\_execute

execute a command

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 C function.

If `count` is zero then the command is executed for all rows that it
applies to. If `count` is greater than zero, then no more than `count`
rows will be retrieved; execution stops when the count is reached, much
like adding a `LIMIT` clause to the query. For example,

    SPI_execute("SELECT * FROM foo", true, 5);

will retrieve at most 5 rows from the table. Note that such a limit is
only effective when the command actually returns rows. For example,

    SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);

inserts all rows from bar, ignoring the `count` parameter. However, with

    SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);

at most 5 rows would be inserted, since execution would stop after the
fifth `RETURNING` result row is retrieved.

You can pass multiple commands in one string; `SPI_execute` returns the
result for the command executed last. The `count` limit applies to each
command separately (even though only the last result will actually be
returned). The limit is not applied to any 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` or `REPEATABLE READ`, 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.
Some utility commands (`COPY`, `CREATE TABLE AS`) don\'t return a row
set, so `SPI_tuptable` is NULL, but they still return the number of rows
processed in `SPI_processed`.

The structure SPITupleTable is defined thus:

    typedef struct SPITupleTable
    {
        /* Public members */
        TupleDesc   tupdesc;        /* tuple descriptor */
        HeapTuple  *vals;           /* array of tuples */
        uint64      numvals;        /* number of valid tuples */

        /* Private members, not intended for external callers */
        uint64      alloced;        /* allocated length of vals array */
        MemoryContext tuptabcxt;    /* memory context of result table */
        slist_node  next;           /* link for internal bookkeeping */
        SubTransactionId subid;     /* subxact in which tuptable was created */
    } SPITupleTable;

The fields tupdesc, vals, and numvals can be used by SPI callers; the
remaining fields are internal. vals is an array of pointers to rows. The
number of rows is given by numvals (for somewhat historical reasons,
this count is also returned in `SPI_processed`). tupdesc is a row
descriptor which you can pass to SPI functions dealing with rows.

`SPI_finish` frees all SPITupleTables allocated during the current C
function. 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 return, or `0` for no limit

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](#rules).

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 C function

Notes
=====

All SPI query-execution functions set both `SPI_processed` and
`SPI_tuptable` (just the pointer, not the contents of the structure).
Save these two global variables into local C function variables if you
need to access the result table of `SPI_execute` or another
query-execution function across later calls.

SPI\_exec

SPI\_exec

3

SPI\_exec

execute a read/write command

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 return, or `0` for no limit

Return Value
============

See `SPI_execute`.

SPI\_execute\_extended

SPI\_execute\_extended

3

SPI\_execute\_extended

execute a command with out-of-line parameters

int SPI\_execute\_extended(const char \*

command

, const SPIExecuteOptions \*

options

)

Description
===========

`SPI_execute_extended` executes a command that might include references
to externally supplied parameters. The command text refers to a
parameter as `$n`, and the `options->params` object (if supplied)
provides values and type information for each such symbol. Various
execution options can be specified in the `options` struct, too.

The `options->params` object should normally mark each parameter with
the `PARAM_FLAG_CONST` flag, since a one-shot plan is always used for
the query.

If `options->dest` is not NULL, then result tuples are passed to that
object as they are generated by the executor, instead of being
accumulated in `SPI_tuptable`. Using a caller-supplied `DestReceiver`
object is particularly helpful for queries that might generate many
tuples, since the data can be processed on-the-fly instead of being
accumulated in memory.

Arguments
=========

`const char * command`

:   command string

`const SPIExecuteOptions * options`

:   struct containing optional arguments

Callers should always zero out the entire `options` struct, then fill
whichever fields they want to set. This ensures forward compatibility of
code, since any fields that are added to the struct in future will be
defined to behave backwards-compatibly if they are zero. The currently
available `options` fields are:

`ParamListInfo params`

:   data structure containing query parameter types and values; NULL if
    none

`bool read_only`

:   `true` for read-only execution

`bool allow_nonatomic`

:   `true` allows non-atomic execution of CALL and DO statements

`bool must_return_tuples`

:   if `true`, raise error if the query is not of a kind that returns
    tuples (this does not forbid the case where it happens to return
    zero tuples)

`uint64 tcount`

:   maximum number of rows to return, or `0` for no limit

`DestReceiver * dest`

:   `DestReceiver` object that will receive any tuples emitted by the
    query; if NULL, result tuples are accumulated into a `SPI_tuptable`
    structure, as in `SPI_execute`

`ResourceOwner owner`

:   This field is present for consistency with
    `SPI_execute_plan_extended`, but it is ignored, since the plan used
    by `SPI_execute_extended` is never saved.

Return Value
============

The return value is the same as for `SPI_execute`.

When `options->dest` is NULL, `SPI_processed` and `SPI_tuptable` are set
as in `SPI_execute`. When `options->dest` is not NULL, `SPI_processed`
is set to zero and `SPI_tuptable` is set to NULL. If a tuple count is
required, the caller\'s `DestReceiver` object must calculate it.

SPI\_execute\_with\_args

SPI\_execute\_with\_args

3

SPI\_execute\_with\_args

execute a command with out-of-line parameters

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
always 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 of length `nargs`, containing the OIDs of the data types of
    the parameters

`Datum * values`

:   an array of length `nargs`, containing the actual parameter values

`const char * nulls`

:   an array of length `nargs`, describing which parameters are null

    If `nulls` is `NULL` then `SPI_execute_with_args` assumes that no
    parameters are null. Otherwise, each entry of the `nulls` array
    should be `' '` if the corresponding parameter value is non-null, or
    `'n'` if the corresponding parameter value is null. (In the latter
    case, the actual value in the corresponding `values` entry doesn\'t
    matter.) Note that `nulls` is not a text string, just an array: it
    does not need a `'\0'` terminator.

`bool read_only`

:   `true` for read-only execution

`long count`

:   maximum number of rows to return, or `0` for no limit

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

3

SPI\_prepare

prepare a statement, without executing it yet

SPIPlanPtr SPI\_prepare(const char \*

command

, int

nargs

, Oid \*

argtypes

)

Description
===========

`SPI_prepare` creates and returns a prepared statement for the specified
command, but doesn\'t execute the command. The prepared statement can
later be executed repeatedly using `SPI_execute_plan`.

When the same or a similar command is to be executed repeatedly, it is
generally advantageous to perform parse analysis only once, and might
furthermore be advantageous to re-use an execution plan for the command.
`SPI_prepare` converts a command string into a prepared statement that
encapsulates the results of parse analysis. The prepared statement also
provides a place for caching an execution plan if it is found that
generating a custom plan for each execution is not helpful.

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 statement returned by `SPI_prepare` can be used only in the current
invocation of the C function, since `SPI_finish` frees memory allocated
for such a statement. But the statement can be saved for longer using
the functions `SPI_keepplan` or `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 `SPIPlan`, which is an
opaque struct representing a prepared statement. 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
=====

If no parameters are defined, a generic plan will be created at the
first use of `SPI_execute_plan`, and used for all subsequent executions
as well. If there are parameters, the first few uses of
`SPI_execute_plan` will generate custom plans that are specific to the
supplied parameter values. After enough uses of the same prepared
statement, `SPI_execute_plan` will build a generic plan, and if that is
not too much more expensive than the custom plans, it will start using
the generic plan instead of re-planning each time. If this default
behavior is unsuitable, you can alter it by passing the
`CURSOR_OPT_GENERIC_PLAN` or `CURSOR_OPT_CUSTOM_PLAN` flag to
`SPI_prepare_cursor`, to force use of generic or custom plans
respectively.

Although the main point of a prepared statement is to avoid repeated
parse analysis and planning of the statement, PostgreSQL will force
re-analysis and re-planning of the statement before using it whenever
database objects used in the statement have undergone definitional (DDL)
changes since the previous use of the prepared statement. Also, if the
value of [???](#guc-search-path) changes from one use to the next, the
statement will be re-parsed using the new `search_path`. (This latter
behavior is new as of PostgreSQL 9.3.) See [???](#sql-prepare) for more
information about the behavior of prepared statements.

This function should only be called from a connected C function.

`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.

The name `SPIPlanPtr` is somewhat historical, since the data structure
no longer necessarily contains an execution plan.

SPI\_prepare\_cursor

SPI\_prepare\_cursor

3

SPI\_prepare\_cursor

prepare a statement, without executing it yet

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 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.

This function is now deprecated in favor of `SPI_prepare_extended`.

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 bit mask 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`, `CURSOR_OPT_FAST_PLAN`,
`CURSOR_OPT_GENERIC_PLAN`, and `CURSOR_OPT_CUSTOM_PLAN`. Note in
particular that `CURSOR_OPT_HOLD` is ignored.

SPI\_prepare\_extended

SPI\_prepare\_extended

3

SPI\_prepare\_extended

prepare a statement, without executing it yet

SPIPlanPtr SPI\_prepare\_extended(const char \*

command

, const SPIPrepareOptions \*

options

)

Description
===========

`SPI_prepare_extended` creates and returns a prepared statement for the
specified command, but doesn\'t execute the command. This function is
equivalent to `SPI_prepare`, with the addition that the caller can
specify options to control the parsing of external parameter references,
as well as other facets of query parsing and planning.

Arguments
=========

`const char * command`

:   command string

`const SPIPrepareOptions * options`

:   struct containing optional arguments

Callers should always zero out the entire `options` struct, then fill
whichever fields they want to set. This ensures forward compatibility of
code, since any fields that are added to the struct in future will be
defined to behave backwards-compatibly if they are zero. The currently
available `options` fields are:

`ParserSetupHook parserSetup`

:   Parser hook setup function

`void * parserSetupArg`

:   pass-through argument for `parserSetup`

`RawParseMode parseMode`

:   mode for raw parsing; `RAW_PARSE_DEFAULT` (zero) produces default
    behavior

`int cursorOptions`

:   integer bit mask of cursor options; zero produces default behavior

Return Value
============

`SPI_prepare_extended` has the same return conventions as `SPI_prepare`.

SPI\_prepare\_params

SPI\_prepare\_params

3

SPI\_prepare\_params

prepare a statement, without executing it yet

SPIPlanPtr SPI\_prepare\_params(const char \*

command

, ParserSetupHook

parserSetup

, void \*

parserSetupArg

, int

cursorOptions

)

Description
===========

`SPI_prepare_params` creates and returns a prepared statement 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.

This function is now deprecated in favor of `SPI_prepare_extended`.

Arguments
=========

`const char * command`

:   command string

`ParserSetupHook parserSetup`

:   Parser hook setup function

`void * parserSetupArg`

:   pass-through argument for `parserSetup`

`int cursorOptions`

:   integer bit mask of cursor options; zero produces default behavior

Return Value
============

`SPI_prepare_params` has the same return conventions as `SPI_prepare`.

SPI\_getargcount

SPI\_getargcount

3

SPI\_getargcount

return the number of arguments needed by a statement prepared by

SPI\_prepare

int SPI\_getargcount(SPIPlanPtr

plan

)

Description
===========

`SPI_getargcount` returns the number of arguments needed to execute a
statement prepared by `SPI_prepare`.

Arguments
=========

`SPIPlanPtr plan`

:   prepared statement (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

3

SPI\_getargtypeid

return the data type OID for an argument of a statement prepared by

SPI\_prepare

Oid SPI\_getargtypeid(SPIPlanPtr

plan

, int

argIndex

)

Description
===========

`SPI_getargtypeid` returns the OID representing the type for the
`argIndex`\'th argument of a statement prepared by `SPI_prepare`. First
argument is at index zero.

Arguments
=========

`SPIPlanPtr plan`

:   prepared statement (returned by `SPI_prepare`)

`int argIndex`

:   zero based index of the argument

Return Value
============

The type OID 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

3

SPI\_is\_cursor\_plan

return

true

if a statement prepared by

SPI\_prepare

can be used with

SPI\_cursor\_open

bool SPI\_is\_cursor\_plan(SPIPlanPtr

plan

)

Description
===========

`SPI_is_cursor_plan` returns `true` if a statement 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`

:   prepared statement (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

3

SPI\_execute\_plan

execute a statement prepared by

SPI\_prepare

int SPI\_execute\_plan(SPIPlanPtr

plan

, Datum \*

values

, const char \*

nulls

, bool

read\_only

, long

count

)

Description
===========

`SPI_execute_plan` executes a statement prepared by `SPI_prepare` or one
of its siblings. `read_only` and `count` have the same interpretation as
in `SPI_execute`.

Arguments
=========

`SPIPlanPtr plan`

:   prepared statement (returned by `SPI_prepare`)

`Datum * values`

:   An array of actual parameter values. Must have same length as the
    statement\'s number of arguments.

`const char * nulls`

:   An array describing which parameters are null. Must have same length
    as the statement\'s number of arguments.

    If `nulls` is `NULL` then `SPI_execute_plan` assumes that no
    parameters are null. Otherwise, each entry of the `nulls` array
    should be `' '` if the corresponding parameter value is non-null, or
    `'n'` if the corresponding parameter value is null. (In the latter
    case, the actual value in the corresponding `values` entry doesn\'t
    matter.) Note that `nulls` is not a text string, just an array: it
    does not need a `'\0'` terminator.

`bool read_only`

:   `true` for read-only execution

`long count`

:   maximum number of rows to return, or `0` for no limit

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\_extended

SPI\_execute\_plan\_extended

3

SPI\_execute\_plan\_extended

execute a statement prepared by

SPI\_prepare

int SPI\_execute\_plan\_extended(SPIPlanPtr

plan

, const SPIExecuteOptions \*

options

)

Description
===========

`SPI_execute_plan_extended` executes a statement prepared by
`SPI_prepare` or one of its siblings. This function is equivalent to
`SPI_execute_plan`, except that information about the parameter values
to be passed to the query is presented differently, and additional
execution-controlling options can be passed.

Query parameter values are represented by a `ParamListInfo` struct,
which is convenient for passing down values that are already available
in that format. Dynamic parameter sets can also be used, via hook
functions specified in `ParamListInfo`.

Also, instead of always accumulating the result tuples into a
`SPI_tuptable` structure, tuples can be passed to a caller-supplied
`DestReceiver` object as they are generated by the executor. This is
particularly helpful for queries that might generate many tuples, since
the data can be processed on-the-fly instead of being accumulated in
memory.

Arguments
=========

`SPIPlanPtr plan`

:   prepared statement (returned by `SPI_prepare`)

`const SPIExecuteOptions * options`

:   struct containing optional arguments

Callers should always zero out the entire `options` struct, then fill
whichever fields they want to set. This ensures forward compatibility of
code, since any fields that are added to the struct in future will be
defined to behave backwards-compatibly if they are zero. The currently
available `options` fields are:

`ParamListInfo params`

:   data structure containing query parameter types and values; NULL if
    none

`bool read_only`

:   `true` for read-only execution

`bool allow_nonatomic`

:   `true` allows non-atomic execution of CALL and DO statements

`bool must_return_tuples`

:   if `true`, raise error if the query is not of a kind that returns
    tuples (this does not forbid the case where it happens to return
    zero tuples)

`uint64 tcount`

:   maximum number of rows to return, or `0` for no limit

`DestReceiver * dest`

:   `DestReceiver` object that will receive any tuples emitted by the
    query; if NULL, result tuples are accumulated into a `SPI_tuptable`
    structure, as in `SPI_execute_plan`

`ResourceOwner owner`

:   The resource owner that will hold a reference count on the plan
    while it is executed. If NULL, CurrentResourceOwner is used. Ignored
    for non-saved plans, as SPI does not acquire reference counts on
    those.

Return Value
============

The return value is the same as for `SPI_execute_plan`.

When `options->dest` is NULL, `SPI_processed` and `SPI_tuptable` are set
as in `SPI_execute_plan`. When `options->dest` is not NULL,
`SPI_processed` is set to zero and `SPI_tuptable` is set to NULL. If a
tuple count is required, the caller\'s `DestReceiver` object must
calculate it.

SPI\_execute\_plan\_with\_paramlist

SPI\_execute\_plan\_with\_paramlist

3

SPI\_execute\_plan\_with\_paramlist

execute a statement prepared by

SPI\_prepare

int SPI\_execute\_plan\_with\_paramlist(SPIPlanPtr

plan

, ParamListInfo

params

, bool

read\_only

, long

count

)

Description
===========

`SPI_execute_plan_with_paramlist` executes a statement 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`.

This function is now deprecated in favor of `SPI_execute_plan_extended`.

Arguments
=========

`SPIPlanPtr plan`

:   prepared statement (returned by `SPI_prepare`)

`ParamListInfo params`

:   data structure containing parameter types and values; NULL if none

`bool read_only`

:   `true` for read-only execution

`long count`

:   maximum number of rows to return, or `0` for no limit

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\_execp

SPI\_execp

3

SPI\_execp

execute a statement in read/write mode

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`

:   prepared statement (returned by `SPI_prepare`)

`Datum * values`

:   An array of actual parameter values. Must have same length as the
    statement\'s number of arguments.

`const char * nulls`

:   An array describing which parameters are null. Must have same length
    as the statement\'s number of arguments.

    If `nulls` is `NULL` then `SPI_execp` assumes that no parameters are
    null. Otherwise, each entry of the `nulls` array should be `' '` if
    the corresponding parameter value is non-null, or `'n'` if the
    corresponding parameter value is null. (In the latter case, the
    actual value in the corresponding `values` entry doesn\'t matter.)
    Note that `nulls` is not a text string, just an array: it does not
    need a `'\0'` terminator.

`long count`

:   maximum number of rows to return, or `0` for no limit

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

3

SPI\_cursor\_open

set up a cursor using a statement created with

SPI\_prepare

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 statement 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 statement 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 C function (it can, in fact, live to the
end of the current transaction). Returning the portal name to the C
function\'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.

Arguments
=========

`const char * name`

:   name for portal, or `NULL` to let the system select a name

`SPIPlanPtr plan`

:   prepared statement (returned by `SPI_prepare`)

`Datum * values`

:   An array of actual parameter values. Must have same length as the
    statement\'s number of arguments.

`const char * nulls`

:   An array describing which parameters are null. Must have same length
    as the statement\'s number of arguments.

    If `nulls` is `NULL` then `SPI_cursor_open` assumes that no
    parameters are null. Otherwise, each entry of the `nulls` array
    should be `' '` if the corresponding parameter value is non-null, or
    `'n'` if the corresponding parameter value is null. (In the latter
    case, the actual value in the corresponding `values` entry doesn\'t
    matter.) Note that `nulls` is not a text string, just an array: it
    does not need a `'\0'` terminator.

`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

3

SPI\_cursor\_open\_with\_args

set up a cursor using a query and parameters

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 parameter data will be copied into the cursor\'s portal,
so it can be freed while the cursor still exists.

This function is now deprecated in favor of `SPI_cursor_parse_open`,
which provides equivalent functionality using a more modern API for
handling query parameters.

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 of length `nargs`, containing the OIDs of the data types of
    the parameters

`Datum * values`

:   an array of length `nargs`, containing the actual parameter values

`const char * nulls`

:   an array of length `nargs`, describing which parameters are null

    If `nulls` is `NULL` then `SPI_cursor_open_with_args` assumes that
    no parameters are null. Otherwise, each entry of the `nulls` array
    should be `' '` if the corresponding parameter value is non-null, or
    `'n'` if the corresponding parameter value is null. (In the latter
    case, the actual value in the corresponding `values` entry doesn\'t
    matter.) Note that `nulls` is not a text string, just an array: it
    does not need a `'\0'` terminator.

`bool read_only`

:   `true` 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\_paramlist

SPI\_cursor\_open\_with\_paramlist

3

SPI\_cursor\_open\_with\_paramlist

set up a cursor using parameters

Portal SPI\_cursor\_open\_with\_paramlist(const char \*

name

, SPIPlanPtr

plan

, ParamListInfo

params

, bool

read\_only

)

Description
===========

`SPI_cursor_open_with_paramlist` sets up a cursor (internally, a portal)
that will execute a statement 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.

Arguments
=========

`const char * name`

:   name for portal, or `NULL` to let the system select a name

`SPIPlanPtr plan`

:   prepared statement (returned by `SPI_prepare`)

`ParamListInfo params`

:   data structure containing parameter types and values; NULL if none

`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\_parse\_open

SPI\_cursor\_parse\_open

3

SPI\_cursor\_parse\_open

set up a cursor using a query string and parameters

Portal SPI\_cursor\_parse\_open(const char \*

name

, const char \*

command

, const SPIParseOpenOptions \*

options

)

Description
===========

`SPI_cursor_parse_open` sets up a cursor (internally, a portal) that
will execute the specified query string. This is comparable to
`SPI_prepare_cursor` followed by `SPI_cursor_open_with_paramlist`,
except that parameter references within the query string are handled
entirely by supplying a `ParamListInfo` object.

For one-time query execution, this function should be preferred over
`SPI_prepare_cursor` followed by `SPI_cursor_open_with_paramlist`. 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 `options->params` object should normally mark each parameter with
the `PARAM_FLAG_CONST` flag, since a one-shot plan is always used for
the query.

The passed-in parameter 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

`const SPIParseOpenOptions * options`

:   struct containing optional arguments

Callers should always zero out the entire `options` struct, then fill
whichever fields they want to set. This ensures forward compatibility of
code, since any fields that are added to the struct in future will be
defined to behave backwards-compatibly if they are zero. The currently
available `options` fields are:

`ParamListInfo params`

:   data structure containing query parameter types and values; NULL if
    none

`int cursorOptions`

:   integer bit mask of cursor options; zero produces default behavior

`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\_find

SPI\_cursor\_find

3

SPI\_cursor\_find

find an existing cursor by name

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

3

SPI\_cursor\_fetch

fetch some rows from a cursor

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

3

SPI\_cursor\_move

move a cursor

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

3

SPI\_scroll\_cursor\_fetch

fetch some rows from a cursor

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 [???](#sql-fetch) 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

3

SPI\_scroll\_cursor\_move

move a cursor

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 [???](#sql-fetch) 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

3

SPI\_cursor\_close

close a cursor

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\_keepplan

SPI\_keepplan

3

SPI\_keepplan

save a prepared statement

int SPI\_keepplan(SPIPlanPtr

plan

)

Description
===========

`SPI_keepplan` saves a passed statement (prepared by `SPI_prepare`) so
that it will not be freed by `SPI_finish` nor by the transaction
manager. This gives you the ability to reuse prepared statements in the
subsequent invocations of your C function in the current session.

Arguments
=========

`SPIPlanPtr plan`

:   the prepared statement to be saved

Return Value
============

0 on success; `SPI_ERROR_ARGUMENT` if `plan` is `NULL` or invalid

Notes
=====

The passed-in statement is relocated to permanent storage by means of
pointer adjustment (no data copying is required). If you later wish to
delete it, use `SPI_freeplan` on it.

SPI\_saveplan

SPI\_saveplan

3

SPI\_saveplan

save a prepared statement

SPIPlanPtr SPI\_saveplan(SPIPlanPtr

plan

)

Description
===========

`SPI_saveplan` copies a passed statement (prepared by `SPI_prepare`)
into memory that will not be freed by `SPI_finish` nor by the
transaction manager, and returns a pointer to the copied statement. This
gives you the ability to reuse prepared statements in the subsequent
invocations of your C function in the current session.

Arguments
=========

`SPIPlanPtr plan`

:   the prepared statement to be saved

Return Value
============

Pointer to the copied statement; or `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 C function

Notes
=====

The originally passed-in statement is not freed, so you might wish to do
`SPI_freeplan` on it to avoid leaking memory until `SPI_finish`.

In most cases, `SPI_keepplan` is preferred to this function, since it
accomplishes largely the same result without needing to physically copy
the prepared statement\'s data structures.

SPI\_register\_relation

ephemeral named relation

registering with SPI

SPI\_register\_relation

3

SPI\_register\_relation

make an ephemeral named relation available by name in SPI queries

int SPI\_register\_relation(EphemeralNamedRelation

enr

)

Description
===========

`SPI_register_relation` makes an ephemeral named relation, with
associated information, available to queries planned and executed
through the current SPI connection.

Arguments
=========

`EphemeralNamedRelation enr`

:   the ephemeral named relation registry entry

Return Value
============

If the execution of the command was successful then the following
(nonnegative) value will be returned:

`SPI_OK_REL_REGISTER`

:   if the relation has been successfully registered by name

On error, one of the following negative values is returned:

`SPI_ERROR_ARGUMENT`

:   if `enr` is `NULL` or its `name` field is `NULL`

`SPI_ERROR_UNCONNECTED`

:   if called from an unconnected C function

`SPI_ERROR_REL_DUPLICATE`

:   if the name specified in the `name` field of `enr` is already
    registered for this connection

SPI\_unregister\_relation

ephemeral named relation

unregistering from SPI

SPI\_unregister\_relation

3

SPI\_unregister\_relation

remove an ephemeral named relation from the registry

int SPI\_unregister\_relation(const char \*

name

)

Description
===========

`SPI_unregister_relation` removes an ephemeral named relation from the
registry for the current connection.

Arguments
=========

`const char * name`

:   the relation registry entry name

Return Value
============

If the execution of the command was successful then the following
(nonnegative) value will be returned:

`SPI_OK_REL_UNREGISTER`

:   if the tuplestore has been successfully removed from the registry

On error, one of the following negative values is returned:

`SPI_ERROR_ARGUMENT`

:   if `name` is `NULL`

`SPI_ERROR_UNCONNECTED`

:   if called from an unconnected C function

`SPI_ERROR_REL_NOT_FOUND`

:   if `name` is not found in the registry for the current connection

SPI\_register\_trigger\_data

ephemeral named relation

registering with SPI

transition tables

implementation in PLs

SPI\_register\_trigger\_data

3

SPI\_register\_trigger\_data

make ephemeral trigger data available in SPI queries

int SPI\_register\_trigger\_data(TriggerData \*

tdata

)

Description
===========

`SPI_register_trigger_data` makes any ephemeral relations captured by a
trigger available to queries planned and executed through the current
SPI connection. Currently, this means the transition tables captured by
an `AFTER` trigger defined with a `REFERENCING OLD/NEW TABLE AS` \...
clause. This function should be called by a PL trigger handler function
after connecting.

Arguments
=========

`TriggerData *tdata`

:   the TriggerData object passed to a trigger handler function as
    `fcinfo->context`

Return Value
============

If the execution of the command was successful then the following
(nonnegative) value will be returned:

`SPI_OK_TD_REGISTER`

:   if the captured trigger data (if any) has been successfully
    registered

On error, one of the following negative values is returned:

`SPI_ERROR_ARGUMENT`

:   if `tdata` is `NULL`

`SPI_ERROR_UNCONNECTED`

:   if called from an unconnected C function

`SPI_ERROR_REL_DUPLICATE`

:   if the name of any trigger data transient relation is already
    registered for this connection

Interface Support Functions {#spi-interface-support}
===========================

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 C functions.

SPI\_fname

SPI\_fname

3

SPI\_fname

determine the column name for the specified column number

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

3

SPI\_fnumber

determine the column number for the specified column name

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., `ctid`) 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 for user-defined columns), or
`SPI_ERROR_NOATTRIBUTE` if the named column was not found.

SPI\_getvalue

SPI\_getvalue

3

SPI\_getvalue

return the string value of the specified column

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

3

SPI\_getbinval

return the binary value of the specified column

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

3

SPI\_gettype

return the data type name of the specified column

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

3

SPI\_gettypeid

return the data type

OID

of the specified column

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

3

SPI\_getrelname

return the name of the specified relation

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

3

SPI\_getnspname

return the namespace of the specified relation

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.

SPI\_result\_code\_string

SPI\_result\_code\_string

3

SPI\_result\_code\_string

return error code as string

const char \* SPI\_result\_code\_string(int

code

);

Description
===========

`SPI_result_code_string` returns a string representation of the result
code returned by various SPI functions or stored in `SPI_result`.

Arguments
=========

`int code`

:   result code

Return Value
============

A string representation of the result code.

Memory Management {#spi-memory}
=================

memory context in SPI PostgreSQL allocates memory within memory
contexts, 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 C function are reclaimed
at C function exit, avoiding memory leakage.

However, if your C function 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 C function 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 C
function. Several of the other utility functions described in this
section also return objects created in the upper executor context.

When `SPI_connect` is called, the private context of the C function,
which is created by `SPI_connect`, is made the current context. All
allocations made by `palloc`, `repalloc`, or SPI utility functions
(except as described in this section) are made in this context. When a C
function disconnects from the SPI manager (via `SPI_finish`) the current
context is restored to the upper executor context, and all allocations
made in the C function memory context are freed and cannot be used any
more.

SPI\_palloc

SPI\_palloc

3

SPI\_palloc

allocate memory in the upper executor context

void \* SPI\_palloc(Size

size

)

Description
===========

`SPI_palloc` allocates memory in the upper executor context.

This function can only be used while connected to SPI. Otherwise, it
throws an error.

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

3

SPI\_repalloc

reallocate memory in the upper executor context

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

3

SPI\_pfree

free memory in the upper executor context

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

3

SPI\_copytuple

make a copy of a row in the upper executor context

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.

This function can only be used while connected to SPI. Otherwise, it
returns NULL and sets `SPI_result` to `SPI_ERROR_UNCONNECTED`.

Arguments
=========

`HeapTuple row`

:   row to be copied

Return Value
============

the copied row, or `NULL` on error (see `SPI_result` for an error
indication)

SPI\_returntuple

SPI\_returntuple

3

SPI\_returntuple

prepare to return a tuple as a Datum

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.

This function can only be used while connected to SPI. Otherwise, it
returns NULL and sets `SPI_result` to `SPI_ERROR_UNCONNECTED`.

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, or `NULL` on error (see
`SPI_result` for an error indication)

SPI\_modifytuple

SPI\_modifytuple

3

SPI\_modifytuple

create a row by replacing selected fields of a given row

HeapTuple SPI\_modifytuple(Relation

rel

, HeapTuple

row

, int

ncols

, int \*

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. The new row is returned in the
upper executor context.

This function can only be used while connected to SPI. Otherwise, it
returns NULL and sets `SPI_result` to `SPI_ERROR_UNCONNECTED`.

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 columns to be changed

`int * colnum`

:   an array of length `ncols`, containing the numbers of the columns
    that are to be changed (column numbers start at 1)

`Datum * values`

:   an array of length `ncols`, containing the new values for the
    specified columns

`const char * nulls`

:   an array of length `ncols`, describing which new values are null

    If `nulls` is `NULL` then `SPI_modifytuple` assumes that no new
    values are null. Otherwise, each entry of the `nulls` array should
    be `' '` if the corresponding new value is non-null, or `'n'` if the
    corresponding new value is null. (In the latter case, the actual
    value in the corresponding `values` entry doesn\'t matter.) Note
    that `nulls` is not a text string, just an array: it does not need a
    `'\0'` terminator.

Return Value
============

new row with modifications, allocated in the upper executor context, or
`NULL` on error (see `SPI_result` for an error indication)

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 columns in `row`)

`SPI_ERROR_UNCONNECTED`

:   if SPI is not active

SPI\_freetuple

SPI\_freetuple

3

SPI\_freetuple

free a row allocated in the upper executor context

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

3

SPI\_freetuptable

free a row set created by

SPI\_execute

or a similar function

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
often called with the global variable `SPI_tuptable` as argument.

This function is useful if an SPI-using C function 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`. Also, if a subtransaction is started and
then aborted within execution of an SPI-using C function, SPI
automatically frees any row sets created while the subtransaction was
running.

Beginning in PostgreSQL 9.3, `SPI_freetuptable` contains guard logic to
protect against duplicate deletion requests for the same row set. In
previous releases, duplicate deletions would lead to crashes.

Arguments
=========

`SPITupleTable * tuptable`

:   pointer to row set to free, or NULL to do nothing

SPI\_freeplan

SPI\_freeplan

3

SPI\_freeplan

free a previously saved prepared statement

int SPI\_freeplan(SPIPlanPtr

plan

)

Description
===========

`SPI_freeplan` releases a prepared statement previously returned by
`SPI_prepare` or saved by `SPI_keepplan` or `SPI_saveplan`.

Arguments
=========

`SPIPlanPtr plan`

:   pointer to statement to free

Return Value
============

0 on success; `SPI_ERROR_ARGUMENT` if `plan` is `NULL` or invalid

Transaction Management {#spi-transaction}
======================

It is not possible to run transaction control commands such as `COMMIT`
and `ROLLBACK` through SPI functions such as `SPI_execute`. There are,
however, separate interface functions that allow transaction control
through SPI.

It is not generally safe and sensible to start and end transactions in
arbitrary user-defined SQL-callable functions without taking into
account the context in which they are called. For example, a transaction
boundary in the middle of a function that is part of a complex SQL
expression that is part of some SQL command will probably result in
obscure internal errors or crashes. The interface functions presented
here are primarily intended to be used by procedural language
implementations to support transaction management in SQL-level
procedures that are invoked by the `CALL` command, taking the context of
the `CALL` invocation into account. SPI-using procedures implemented in
C can implement the same logic, but the details of that are beyond the
scope of this documentation.

SPI\_commit

SPI\_commit\_and\_chain

SPI\_commit

3

SPI\_commit

SPI\_commit\_and\_chain

commit the current transaction

void SPI\_commit(void)

void SPI\_commit\_and\_chain(void)

Description
===========

`SPI_commit` commits the current transaction. It is approximately
equivalent to running the SQL command `COMMIT`. After the transaction is
committed, a new transaction is automatically started using default
transaction characteristics, so that the caller can continue using SPI
facilities. If there is a failure during commit, the current transaction
is instead rolled back and a new transaction is started, after which the
error is thrown in the usual way.

`SPI_commit_and_chain` is the same, but the new transaction is started
with the same transaction characteristics as the just finished one, like
with the SQL command `COMMIT AND CHAIN`.

These functions can only be executed if the SPI connection has been set
as nonatomic in the call to `SPI_connect_ext`.

SPI\_rollback

SPI\_rollback\_and\_chain

SPI\_rollback

3

SPI\_rollback

SPI\_rollback\_and\_chain

abort the current transaction

void SPI\_rollback(void)

void SPI\_rollback\_and\_chain(void)

Description
===========

`SPI_rollback` rolls back the current transaction. It is approximately
equivalent to running the SQL command `ROLLBACK`. After the transaction
is rolled back, a new transaction is automatically started using default
transaction characteristics, so that the caller can continue using SPI
facilities.

`SPI_rollback_and_chain` is the same, but the new transaction is started
with the same transaction characteristics as the just finished one, like
with the SQL command `ROLLBACK AND CHAIN`.

These functions can only be executed if the SPI connection has been set
as nonatomic in the call to `SPI_connect_ext`.

SPI\_start\_transaction

SPI\_start\_transaction

3

SPI\_start\_transaction

obsolete function

void SPI\_start\_transaction(void)

Description
===========

`SPI_start_transaction` does nothing, and exists only for code
compatibility with earlier PostgreSQL releases. It used to be required
after calling `SPI_commit` or `SPI_rollback`, but now those functions
start a new transaction automatically.

Visibility of Data Changes {#spi-visibility}
==========================

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 {#spi-examples}
========

This section contains a very simple example of SPI usage. The C function
`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 the [???](#contrib-spi) module.

    #include "postgres.h"

    #include "executor/spi.h"
    #include "utils/builtins.h"

    PG_MODULE_MAGIC;

    PG_FUNCTION_INFO_V1(execq);

    Datum
    execq(PG_FUNCTION_ARGS)
    {
        char *command;
        int cnt;
        int ret;
        uint64 proc;

        /* Convert given text object to a C string */
        command = text_to_cstring(PG_GETARG_TEXT_PP(0));
        cnt = PG_GETARG_INT32(1);

        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)
        {
            SPITupleTable *tuptable = SPI_tuptable;
            TupleDesc tupdesc = tuptable->tupdesc;
            char buf[8192];
            uint64 j;

            for (j = 0; j < tuptable->numvals; j++)
            {
                HeapTuple tuple = tuptable->vals[j];
                int i;

                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);

        PG_RETURN_INT64(proc);
    }

This is how you declare the function after having compiled it into a
shared library (details are in [???](#dfunc).):

    CREATE FUNCTION execq(text, integer) RETURNS int8
        AS 'filename'
        LANGUAGE C STRICT;

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
