Interface Functions

SPI_connect

connect a C function to the SPI manager

Synopsis

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

The fact that these functions return int not void is historical. All failure cases are reported via ereport or elog. (In versions before PostgreSQL v10, some but not all failures would be reported with a result value of SPI_ERROR_CONNECT.)

SPI_finish

disconnect a C function from the SPI manager

Synopsis

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

execute a command

Synopsis

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

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

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

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, SPI_OK_UPDATE_RETURNING, or SPI_OK_MERGE_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;

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

boolread_only` :true` for read-only execution

longcount` : maximum number of rows to return, or0` 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_MERGE : if a MERGE 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_MERGE_RETURNING : if a MERGE 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 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

execute a read/write command

Synopsis

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

longcount` : maximum number of rows to return, or0` for no limit

Return Value

See SPI_execute.

SPI_execute_extended

execute a command with out-of-line parameters

Synopsis

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:

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

boolread_only` :true` for read-only execution

boolallow_nonatomic` :trueallows non-atomic execution of CALL and DO statements (but this field is ignored unless theSPI_OPT_NONATOMICflag was passed toSPI_connect_ext`)

boolmust_return_tuples` : iftrue`, 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)

uint64tcount` : maximum number of rows to return, or0` for no limit

DestReceiver *dest` :DestReceiverobject that will receive any tuples emitted by the query; if NULL, result tuples are accumulated into aSPI_tuptablestructure, as inSPI_execute`

ResourceOwnerowner` : This field is present for consistency withSPI_execute_plan_extended, but it is ignored, since the plan used bySPI_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

execute a command with out-of-line parameters

Synopsis

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

intnargs` : number of input parameters ($1,$2`, etc.)

Oid *argtypes` : an array of lengthnargs`, containing the OIDs of the data types of the parameters

Datum *values` : an array of lengthnargs`, containing the actual parameter values

const char *nulls` : an array of lengthnargs`, 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.

boolread_only` :true` for read-only execution

longcount` : maximum number of rows to return, or0` 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

prepare a statement, without executing it yet

Synopsis

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

intnargs` : 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 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

prepare a statement, without executing it yet

Synopsis

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

intnargs` : number of input parameters ($1,$2`, etc.)

Oid *argtypes`` : pointer to an array containing the OIDs of the data types of the parameters

intcursorOptions`` : 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

prepare a statement, without executing it yet

Synopsis

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:

ParserSetupHookparserSetup`` : Parser hook setup function

void *parserSetupArg` : pass-through argument forparserSetup`

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

intcursorOptions`` : 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

prepare a statement, without executing it yet

Synopsis

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

ParserSetupHookparserSetup`` : Parser hook setup function

void *parserSetupArg` : pass-through argument forparserSetup`

intcursorOptions`` : 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

return the number of arguments needed by a statement prepared by SPI_prepare

Synopsis

int SPI_getargcount(SPIPlanPtr plan)

Description

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

Arguments

SPIPlanPtrplan` : prepared statement (returned bySPI_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

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

Synopsis

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

SPIPlanPtrplan` : prepared statement (returned bySPI_prepare`)

intargIndex`` : 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

return true if a statement prepared by SPI_prepare can be used with SPI_cursor_open

Synopsis

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

SPIPlanPtrplan` : prepared statement (returned bySPI_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

execute a statement prepared by SPI_prepare

Synopsis

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

SPIPlanPtrplan` : prepared statement (returned bySPI_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.

boolread_only` :true` for read-only execution

longcount` : maximum number of rows to return, or0` 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

execute a statement prepared by SPI_prepare

Synopsis

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

SPIPlanPtrplan` : prepared statement (returned bySPI_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:

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

boolread_only` :true` for read-only execution

boolallow_nonatomic` :trueallows non-atomic execution of CALL and DO statements (but this field is ignored unless theSPI_OPT_NONATOMICflag was passed toSPI_connect_ext`)

boolmust_return_tuples` : iftrue`, 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)

uint64tcount` : maximum number of rows to return, or0` for no limit

DestReceiver *dest` :DestReceiverobject that will receive any tuples emitted by the query; if NULL, result tuples are accumulated into aSPI_tuptablestructure, as inSPI_execute_plan`

ResourceOwnerowner`` : 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

execute a statement prepared by SPI_prepare

Synopsis

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

SPIPlanPtrplan` : prepared statement (returned bySPI_prepare`)

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

boolread_only` :true` for read-only execution

longcount` : maximum number of rows to return, or0` 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

execute a statement in read/write mode

Synopsis

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

SPIPlanPtrplan` : prepared statement (returned bySPI_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.

longcount` : maximum number of rows to return, or0` 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

set up a cursor using a statement created with SPI_prepare

Synopsis

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, orNULL` to let the system select a name

SPIPlanPtrplan` : prepared statement (returned bySPI_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.

boolread_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

set up a cursor using a query and parameters

Synopsis

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, orNULL` to let the system select a name

const char *command`` : command string

intnargs` : number of input parameters ($1,$2`, etc.)

Oid *argtypes` : an array of lengthnargs`, containing the OIDs of the data types of the parameters

Datum *values` : an array of lengthnargs`, containing the actual parameter values

const char *nulls` : an array of lengthnargs`, 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.

boolread_only` :true` for read-only execution

intcursorOptions`` : 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

set up a cursor using parameters

Synopsis

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, orNULL` to let the system select a name

SPIPlanPtrplan` : prepared statement (returned bySPI_prepare`)

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

boolread_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

set up a cursor using a query string and parameters

Synopsis

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, orNULL` 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:

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

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

boolread_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

find an existing cursor by name

Synopsis

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

Notes

Beware that this function can return a Portal object that does not have cursor-like properties; for example it might not return tuples. If you simply pass the Portal pointer to other SPI functions, they can defend themselves against such cases, but caution is appropriate when directly inspecting the Portal.

SPI_cursor_fetch

fetch some rows from a cursor

Synopsis

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

Portalportal`` : portal containing the cursor

boolforward`` : true for fetch forward, false for fetch backward

longcount`` : 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

move a cursor

Synopsis

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

Portalportal`` : portal containing the cursor

boolforward`` : true for move forward, false for move backward

longcount`` : 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

fetch some rows from a cursor

Synopsis

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

Portalportal`` : portal containing the cursor

FetchDirectiondirection` : one ofFETCH_FORWARD,FETCH_BACKWARD,FETCH_ABSOLUTEorFETCH_RELATIVE`

longcount` : number of rows to fetch forFETCH_FORWARDorFETCH_BACKWARD; absolute row number to fetch forFETCH_ABSOLUTE; or relative row number to fetch forFETCH_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

move a cursor

Synopsis

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

Portalportal`` : portal containing the cursor

FetchDirectiondirection` : one ofFETCH_FORWARD,FETCH_BACKWARD,FETCH_ABSOLUTEorFETCH_RELATIVE`

longcount` : number of rows to move forFETCH_FORWARDorFETCH_BACKWARD; absolute row number to move to forFETCH_ABSOLUTE; or relative row number to move to forFETCH_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

close a cursor

Synopsis

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

Portalportal`` : portal containing the cursor

SPI_keepplan

save a prepared statement

Synopsis

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

SPIPlanPtrplan`` : 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

save a prepared statement

Synopsis

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

SPIPlanPtrplan`` : 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

make an ephemeral named relation available by name in SPI queries

Synopsis

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

EphemeralNamedRelationenr`` : 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

remove an ephemeral named relation from the registry

Synopsis

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

make ephemeral trigger data available in SPI queries

Synopsis

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` : theTriggerDataobject passed to a trigger handler function asfcinfo->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