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. 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. 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) 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, and SPI_start_transaction are allowed. Otherwise, calling these functions will result in an immediate error. SPI_connect() is equivalent to SPI_connect_ext(0). 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) 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. 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) 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. 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 If the execution of the command was successful then one of the following (nonnegative) values will be returned: SPI_OK_SELECT if a SELECT (but not SELECT INTO) was executed SPI_OK_SELINTO if a SELECT INTO was executed SPI_OK_INSERT if an INSERT was executed SPI_OK_DELETE if a DELETE was executed SPI_OK_UPDATE if an UPDATE was executed SPI_OK_INSERT_RETURNING if an INSERT RETURNING was executed SPI_OK_DELETE_RETURNING if a DELETE RETURNING was executed SPI_OK_UPDATE_RETURNING if an UPDATE RETURNING was executed SPI_OK_UTILITY if a utility command (e.g., CREATE TABLE) was executed SPI_OK_REWRITTEN if the command was rewritten into another kind of command (e.g., UPDATE became an INSERT) by a rule. On error, one of the following negative values is returned: SPI_ERROR_ARGUMENT if command is NULL or count is less than 0 SPI_ERROR_COPY if COPY TO stdout or COPY FROM stdin was attempted SPI_ERROR_TRANSACTION if a transaction manipulation command was attempted (BEGIN, COMMIT, ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED, or any variant thereof) SPI_ERROR_OPUNKNOWN if the command type is unknown (shouldn't happen) SPI_ERROR_UNCONNECTED if called from an unconnected C function 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) SPI_exec is the same as SPI_execute, with the latter's read_only parameter always taken as false. const char * command string containing command to execute long count maximum number of rows to return, or 0 for no limit See SPI_execute. 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) 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. 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 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) 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. 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 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. 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 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 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) 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. 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 SPI_prepare_cursor has the same return conventions as SPI_prepare. 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_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) 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. 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 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) SPI_getargcount returns the number of arguments needed to execute a statement prepared by SPI_prepare. SPIPlanPtr plan prepared statement (returned by SPI_prepare) 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) 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. SPIPlanPtr plan prepared statement (returned by SPI_prepare) int argIndex zero based index of the argument 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) 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. SPIPlanPtr plan prepared statement (returned by SPI_prepare) 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) 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. 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 The return value is the same as for SPI_execute, with the following additional possible error (negative) results: SPI_ERROR_ARGUMENT if plan is NULL or invalid, or count is less than 0 SPI_ERROR_PARAM if values is NULL and plan was prepared with some parameters SPI_processed and SPI_tuptable are set as in SPI_execute if successful. SPI_execute_plan_with_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) 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. 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 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) SPI_execp is the same as SPI_execute_plan, with the latter's read_only parameter always taken as false. 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 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) 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. 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 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) 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. 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 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) 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. 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 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) 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. const char * name name of the portal 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) 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). Portal portal portal containing the cursor bool forward true for fetch forward, false for fetch backward long count maximum number of rows to fetch SPI_processed and SPI_tuptable are set as in SPI_execute if successful. 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) 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). Portal portal portal containing the cursor bool forward true for move forward, false for move backward long count maximum number of rows to move 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) SPI_scroll_cursor_fetch fetches some rows from a cursor. This is equivalent to the SQL command FETCH. 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 SPI_processed and SPI_tuptable are set as in SPI_execute if successful. See the SQL command for details of the interpretation of the direction and count parameters. Direction values other than FETCH_FORWARD may fail if the cursor's plan was not created with the CURSOR_OPT_SCROLL option. SPI_scroll_cursor_move SPI_scroll_cursor_move 3 SPI_scroll_cursor_move move a cursor void SPI_scroll_cursor_move(Portal portal, FetchDirection direction, long count) SPI_scroll_cursor_move skips over some number of rows in a cursor. This is equivalent to the SQL command MOVE. 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 SPI_processed is set as in SPI_execute if successful. SPI_tuptable is set to NULL, since no rows are returned by this function. See the SQL command for details of the interpretation of the direction and count parameters. Direction values other than FETCH_FORWARD may fail if the cursor's plan was not created with the CURSOR_OPT_SCROLL option. SPI_cursor_close SPI_cursor_close 3 SPI_cursor_close close a cursor void SPI_cursor_close(Portal portal) 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. Portal portal portal containing the cursor SPI_keepplan SPI_keepplan 3 SPI_keepplan save a prepared statement int SPI_keepplan(SPIPlanPtr plan) 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. SPIPlanPtr plan the prepared statement to be saved 0 on success; SPI_ERROR_ARGUMENT if plan is NULL or invalid 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) 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. SPIPlanPtr plan the prepared statement to be saved 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 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) SPI_register_relation makes an ephemeral named relation, with associated information, available to queries planned and executed through the current SPI connection. EphemeralNamedRelation enr the ephemeral named relation registry entry 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) SPI_unregister_relation removes an ephemeral named relation from the registry for the current connection. const char * name the relation registry entry name 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) 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. TriggerData *tdata the TriggerData object passed to a trigger handler function as fcinfo->context 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 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) 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.) TupleDesc rowdesc input row description int colnumber column number (count starts at 1) 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) 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. TupleDesc rowdesc input row description const char * colname column name 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) 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.) HeapTuple row input row to be examined TupleDesc rowdesc input row description int colnumber column number (count starts at 1) 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) 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. 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 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) 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.) TupleDesc rowdesc input row description int colnumber column number (count starts at 1) 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) SPI_gettypeid returns the OID of the data type of the specified column. TupleDesc rowdesc input row description int colnumber column number (count starts at 1) 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) 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.) Relation rel input relation 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) 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. Relation rel input relation 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); SPI_result_code_string returns a string representation of the result code returned by various SPI functions or stored in SPI_result. int code result code A string representation of the result code. 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) SPI_palloc allocates memory in the upper executor context. This function can only be used while connected to SPI. Otherwise, it throws an error. Size size size in bytes of storage to allocate 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) 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. void * pointer pointer to existing storage to change Size size size in bytes of storage to allocate 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) 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. 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) 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. HeapTuple row row to be copied 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) 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. HeapTuple row row to be copied TupleDesc rowdesc descriptor for row (pass the same descriptor each time for most effective caching) 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) 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. 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. 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) 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. 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) 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. 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) SPI_freeplan releases a prepared statement previously returned by SPI_prepare or saved by SPI_keepplan or SPI_saveplan. SPIPlanPtr plan pointer to statement to free 0 on success; SPI_ERROR_ARGUMENT if plan is NULL or invalid 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) SPI_commit commits the current transaction. It is approximately equivalent to running the SQL command COMMIT. After a transaction is committed, a new transaction has to be started using SPI_start_transaction before further database actions can be executed. SPI_commit_and_chain is the same, but a new transaction is immediately 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) SPI_rollback rolls back the current transaction. It is approximately equivalent to running the SQL command ROLLBACK. After a transaction is rolled back, a new transaction has to be started using SPI_start_transaction before further database actions can be executed. SPI_rollback_and_chain is the same, but a new transaction is immediately 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 start a new transaction void SPI_start_transaction(void) SPI_start_transaction starts a new transaction. It can only be called after SPI_commit or SPI_rollback, as there is no transaction active at that point. Normally, when an SPI-using procedure is called, there is already a transaction active, so attempting to start another one before closing out the current one will result in an error. This function can only be executed if the SPI connection has been set as nonatomic in the call to SPI_connect_ext. 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. 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 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 .): 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