From 293913568e6a7a86fd1479e1cff8e2ecb58d6568 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 15:44:03 +0200 Subject: Adding upstream version 16.2. Signed-off-by: Daniel Baumann --- doc/src/sgml/spi.sgml | 5413 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 5413 insertions(+) create mode 100644 doc/src/sgml/spi.sgml (limited to 'doc/src/sgml/spi.sgml') diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml new file mode 100644 index 0000000..47f4b5b --- /dev/null +++ b/doc/src/sgml/spi.sgml @@ -0,0 +1,5413 @@ + + + + Server Programming Interface + + + SPI + + + + The Server Programming Interface + (SPI) gives writers of user-defined + C functions the ability to run + SQL commands inside their functions 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. + + + + + Interface Functions + + + SPI_connect + SPI_connect_ext + + + SPI_connect + 3 + + + + SPI_connect + SPI_connect_ext + connect a C function to the SPI manager + + + + +int SPI_connect(void) + + + +int SPI_connect_ext(int options) + + + + + Description + + + SPI_connect opens a connection from a + C function invocation to the SPI manager. You must call this + function if you want to execute commands through SPI. Some utility + SPI functions can be called from unconnected C functions. + + + + SPI_connect_ext does the same but has an argument that + allows passing option flags. Currently, the following option values are + available: + + + SPI_OPT_NONATOMIC + + + Sets the SPI connection to be nonatomic, which + means that transaction control calls (SPI_commit, + SPI_rollback) are allowed. Otherwise, + calling those functions will result in an immediate error. + + + + + + + + SPI_connect() is equivalent to + SPI_connect_ext(0). + + + + + Return Value + + + + SPI_OK_CONNECT + + + on success + + + + + + SPI_ERROR_CONNECT + + + on error + + + + + + + + + + + SPI_finish + + + SPI_finish + 3 + + + + SPI_finish + disconnect a C function from the SPI manager + + + + +int SPI_finish(void) + + + + + Description + + + SPI_finish closes an existing connection to + the SPI manager. You must call this function after completing the + SPI operations needed during your C function's current invocation. + You do not need to worry about making this happen, however, if you + abort the transaction via elog(ERROR). In that + case SPI will clean itself up automatically. + + + + + Return Value + + + + SPI_OK_FINISH + + + if properly disconnected + + + + + + SPI_ERROR_UNCONNECTED + + + if called from an unconnected C function + + + + + + + + + + + SPI_execute + + + SPI_execute + 3 + + + + SPI_execute + execute a command + + + + +int SPI_execute(const char * command, bool read_only, long count) + + + + + Description + + + SPI_execute executes the specified SQL command + for count rows. If read_only + is true, the command must be read-only, and execution overhead + is somewhat reduced. + + + + This function can only be called from a connected C function. + + + + If count is zero then the command is executed + for all rows that it applies to. If count + is greater than zero, then no more than count rows + will be retrieved; execution stops when the count is reached, much like + adding a LIMIT clause to the query. For example, + +SPI_execute("SELECT * FROM foo", true, 5); + + will retrieve at most 5 rows from the table. Note that such a limit + is only effective when the command actually returns rows. For example, + +SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5); + + inserts all rows from bar, ignoring the + count parameter. However, with + +SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5); + + at most 5 rows would be inserted, since execution would stop after the + fifth RETURNING result row is retrieved. + + + + You can pass multiple commands in one string; + SPI_execute returns the + result for the command executed last. The count + limit applies to each command separately (even though only the last + result will actually be returned). The limit is not applied to any + hidden commands generated by rules. + + + + When read_only is false, + SPI_execute increments the command + counter and computes a new snapshot before executing each + command in the string. The snapshot does not actually change if the + current transaction isolation level is SERIALIZABLE or REPEATABLE READ, but in + READ COMMITTED mode the snapshot update allows each command to + see the results of newly committed transactions from other sessions. + This is essential for consistent behavior when the commands are modifying + the database. + + + + When read_only is true, + SPI_execute does not update either the snapshot + or the command counter, and it allows only plain SELECT + commands to appear in the command string. The commands are executed + using the snapshot previously established for the surrounding query. + This execution mode is somewhat faster than the read/write mode due + to eliminating per-command overhead. It also allows genuinely + stable functions to be built: since successive executions + will all use the same snapshot, there will be no change in the results. + + + + It is generally unwise to mix read-only and read-write commands within + a single function using SPI; that could result in very confusing behavior, + since the read-only queries would not see the results of any database + updates done by the read-write queries. + + + + The actual number of rows for which the (last) command was executed + is returned in the global variable SPI_processed. + If the return value of the function is SPI_OK_SELECT, + SPI_OK_INSERT_RETURNING, + SPI_OK_DELETE_RETURNING, or + SPI_OK_UPDATE_RETURNING, + then you can use the + global pointer SPITupleTable *SPI_tuptable to + access the result rows. Some utility commands (such as + EXPLAIN) also return row sets, and SPI_tuptable + will contain the result in these cases too. Some utility commands + (COPY, CREATE TABLE AS) don't return a row set, so + SPI_tuptable is NULL, but they still return the number of + rows processed in SPI_processed. + + + + The structure SPITupleTable is defined + thus: + +typedef struct SPITupleTable +{ + /* Public members */ + TupleDesc tupdesc; /* tuple descriptor */ + HeapTuple *vals; /* array of tuples */ + uint64 numvals; /* number of valid tuples */ + + /* Private members, not intended for external callers */ + uint64 alloced; /* allocated length of vals array */ + MemoryContext tuptabcxt; /* memory context of result table */ + slist_node next; /* link for internal bookkeeping */ + SubTransactionId subid; /* subxact in which tuptable was created */ +} SPITupleTable; + + The fields tupdesc, + vals, and + numvals + can be used by SPI callers; the remaining fields are internal. + vals is an array of pointers to rows. + The number of rows is given by numvals + (for somewhat historical reasons, this count is also returned + in SPI_processed). + tupdesc is a row descriptor which you can pass to + SPI functions dealing with rows. + + + + SPI_finish frees all + SPITupleTables allocated during the current + C function. You can free a particular result table earlier, if you + are done with it, by calling SPI_freetuptable. + + + + + Arguments + + + + const char * command + + + string containing command to execute + + + + + + bool read_only + + true for read-only execution + + + + + long count + + + maximum number of rows to return, + or 0 for no limit + + + + + + + + Return Value + + + If the execution of the command was successful then one of the + following (nonnegative) values will be returned: + + + + SPI_OK_SELECT + + + if a SELECT (but not SELECT + INTO) was executed + + + + + + SPI_OK_SELINTO + + + if a SELECT INTO was executed + + + + + + SPI_OK_INSERT + + + if an INSERT was executed + + + + + + SPI_OK_DELETE + + + if a DELETE was executed + + + + + + SPI_OK_UPDATE + + + if an UPDATE was executed + + + + + + SPI_OK_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_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 + + + SPI_exec + 3 + + + + SPI_exec + execute a read/write command + + + + +int SPI_exec(const char * command, long count) + + + + + Description + + + SPI_exec is the same as + SPI_execute, with the latter's + read_only parameter always taken as + false. + + + + + Arguments + + + + const char * command + + + string containing command to execute + + + + + + long count + + + maximum number of rows to return, + or 0 for no limit + + + + + + + + Return Value + + + See SPI_execute. + + + + + + + + SPI_execute_extended + + + SPI_execute_extended + 3 + + + + SPI_execute_extended + execute a command with out-of-line parameters + + + + +int SPI_execute_extended(const char *command, + const SPIExecuteOptions * options) + + + + + Description + + + SPI_execute_extended executes a command that might + include references to externally supplied parameters. The command text + refers to a parameter as $n, + and the options->params object (if supplied) + provides values and type information for each such symbol. + Various execution options can be specified + in the options struct, too. + + + + The options->params object should normally + mark each parameter with the PARAM_FLAG_CONST flag, + since a one-shot plan is always used for the query. + + + + If options->dest is not NULL, then result + tuples are passed to that object as they are generated by the executor, + instead of being accumulated in SPI_tuptable. Using + a caller-supplied DestReceiver object is particularly + helpful for queries that might generate many tuples, since the data can + be processed on-the-fly instead of being accumulated in memory. + + + + + Arguments + + + + const char * command + + + command string + + + + + + const SPIExecuteOptions * options + + + struct containing optional arguments + + + + + + + Callers should always zero out the entire options + struct, then fill whichever fields they want to set. This ensures forward + compatibility of code, since any fields that are added to the struct in + future will be defined to behave backwards-compatibly if they are zero. + The currently available options fields are: + + + + + ParamListInfo params + + + data structure containing query parameter types and values; NULL if none + + + + + + bool read_only + + true for read-only execution + + + + + bool allow_nonatomic + + + true allows non-atomic execution of CALL and DO + statements + + + + + + bool must_return_tuples + + + if true, raise error if the query is not of a kind + that returns tuples (this does not forbid the case where it happens to + return zero tuples) + + + + + + uint64 tcount + + + maximum number of rows to return, + or 0 for no limit + + + + + + DestReceiver * dest + + + DestReceiver object that will receive any tuples + emitted by the query; if NULL, result tuples are accumulated into + a SPI_tuptable structure, as + in SPI_execute + + + + + + ResourceOwner owner + + + This field is present for consistency + with SPI_execute_plan_extended, but it is + ignored, since the plan used + by SPI_execute_extended is never saved. + + + + + + + + Return Value + + + The return value is the same as for SPI_execute. + + + + When options->dest is NULL, + SPI_processed and + SPI_tuptable are set as in + SPI_execute. + When options->dest is not NULL, + SPI_processed is set to zero and + SPI_tuptable is set to NULL. If a tuple count + is required, the caller's DestReceiver object must + calculate it. + + + + + + + + SPI_execute_with_args + + + SPI_execute_with_args + 3 + + + + SPI_execute_with_args + execute a command with out-of-line parameters + + + + +int SPI_execute_with_args(const char *command, + int nargs, Oid *argtypes, + Datum *values, const char *nulls, + bool read_only, long count) + + + + + Description + + + SPI_execute_with_args executes a command that might + include references to externally supplied parameters. The command text + refers to a parameter as $n, and + the call specifies data types and values for each such symbol. + read_only and count have + the same interpretation as in SPI_execute. + + + + The main advantage of this routine compared to + SPI_execute is that data values can be inserted + into the command without tedious quoting/escaping, and thus with much + less risk of SQL-injection attacks. + + + + Similar results can be achieved with SPI_prepare followed by + SPI_execute_plan; however, when using this function + the query plan is always customized to the specific parameter values + provided. + For one-time query execution, this function should be preferred. + If the same command is to be executed with many different parameters, + either method might be faster, depending on the cost of re-planning + versus the benefit of custom plans. + + + + + Arguments + + + + const char * command + + + command string + + + + + + int nargs + + + number of input parameters ($1, $2, etc.) + + + + + + Oid * argtypes + + + an array of length nargs, containing the + OIDs of the data types of the parameters + + + + + + Datum * values + + + an array of length nargs, containing the actual + parameter values + + + + + + const char * nulls + + + an array of length nargs, describing which + parameters are null + + + + If nulls is NULL then + SPI_execute_with_args assumes that no parameters + are null. Otherwise, each entry of the nulls + array should be ' ' if the corresponding parameter + value is non-null, or 'n' if the corresponding parameter + value is null. (In the latter case, the actual value in the + corresponding values entry doesn't matter.) Note + that nulls is not a text string, just an array: + it does not need a '\0' terminator. + + + + + + bool read_only + + true for read-only execution + + + + + long count + + + maximum number of rows to return, + or 0 for no limit + + + + + + + + Return Value + + + The return value is the same as for SPI_execute. + + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + + + + SPI_prepare + + + SPI_prepare + 3 + + + + SPI_prepare + prepare a statement, without executing it yet + + + + +SPIPlanPtr SPI_prepare(const char * command, int nargs, Oid * argtypes) + + + + + Description + + + SPI_prepare creates and returns a prepared + statement for the specified command, but doesn't execute the command. + The prepared statement can later be executed repeatedly using + SPI_execute_plan. + + + + When the same or a similar command is to be executed repeatedly, it + is generally advantageous to perform parse analysis only once, and + might furthermore be advantageous to re-use an execution plan for the + command. + SPI_prepare converts a command string into a + prepared statement that encapsulates the results of parse analysis. + The prepared statement also provides a place for caching an execution plan + if it is found that generating a custom plan for each execution is not + helpful. + + + + A prepared command can be generalized by writing parameters + ($1, $2, etc.) in place of what would be + constants in a normal command. The actual values of the parameters + are then specified when SPI_execute_plan is called. + This allows the prepared command to be used over a wider range of + situations than would be possible without parameters. + + + + The statement returned by SPI_prepare can be used + only in the current invocation of the C function, since + SPI_finish frees memory allocated for such a + statement. But the statement can be saved for longer using the functions + SPI_keepplan or SPI_saveplan. + + + + + Arguments + + + + const char * command + + + command string + + + + + + int nargs + + + number of input parameters ($1, $2, etc.) + + + + + + Oid * argtypes + + + pointer to an array containing the OIDs of + the data types of the parameters + + + + + + + + Return Value + + + SPI_prepare returns a non-null pointer to an + SPIPlan, which is an opaque struct representing a prepared + statement. On error, NULL will be returned, + and SPI_result will be set to one of the same + error codes used by SPI_execute, except that + it is set to SPI_ERROR_ARGUMENT if + command is NULL, or if + nargs is less than 0, or if nargs is + greater than 0 and argtypes is NULL. + + + + + Notes + + + If no parameters are defined, a generic plan will be created at the + first use of SPI_execute_plan, and used for all + subsequent executions as well. If there are parameters, the first few uses + of SPI_execute_plan will generate custom plans + that are specific to the supplied parameter values. After enough uses + of the same prepared statement, SPI_execute_plan will + build a generic plan, and if that is not too much more expensive than the + custom plans, it will start using the generic plan instead of re-planning + each time. If this default behavior is unsuitable, you can alter it by + passing the CURSOR_OPT_GENERIC_PLAN or + CURSOR_OPT_CUSTOM_PLAN flag to + SPI_prepare_cursor, to force use of generic or custom + plans respectively. + + + + Although the main point of a prepared statement is to avoid repeated parse + analysis and planning of the statement, PostgreSQL will + force re-analysis and re-planning of the statement before using it + whenever database objects used in the statement have undergone + definitional (DDL) changes since the previous use of the prepared + statement. Also, if the value of 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) + + + + + Description + + + SPI_prepare_cursor is identical to + SPI_prepare, except that it also allows specification + of the planner's cursor options parameter. This is a bit mask + having the values shown in nodes/parsenodes.h + for the options field of DeclareCursorStmt. + SPI_prepare always takes the cursor options as zero. + + + + This function is now deprecated in favor + of SPI_prepare_extended. + + + + + Arguments + + + + const char * command + + + command string + + + + + + int nargs + + + number of input parameters ($1, $2, etc.) + + + + + + Oid * argtypes + + + pointer to an array containing the OIDs of + the data types of the parameters + + + + + + int cursorOptions + + + integer bit mask of cursor options; zero produces default behavior + + + + + + + + Return Value + + + SPI_prepare_cursor has the same return conventions as + SPI_prepare. + + + + + Notes + + + Useful bits to set in cursorOptions include + CURSOR_OPT_SCROLL, + CURSOR_OPT_NO_SCROLL, + CURSOR_OPT_FAST_PLAN, + CURSOR_OPT_GENERIC_PLAN, and + CURSOR_OPT_CUSTOM_PLAN. Note in particular that + CURSOR_OPT_HOLD is ignored. + + + + + + + + SPI_prepare_extended + + + SPI_prepare_extended + 3 + + + + SPI_prepare_extended + prepare a statement, without executing it yet + + + + +SPIPlanPtr SPI_prepare_extended(const char * command, + const SPIPrepareOptions * options) + + + + + Description + + + SPI_prepare_extended creates and returns a prepared + statement for the specified command, but doesn't execute the command. + This function is equivalent to SPI_prepare, + with the addition that the caller can specify options to control + the parsing of external parameter references, as well as other facets + of query parsing and planning. + + + + + Arguments + + + + const char * command + + + command string + + + + + + const SPIPrepareOptions * options + + + struct containing optional arguments + + + + + + + Callers should always zero out the entire options + struct, then fill whichever fields they want to set. This ensures forward + compatibility of code, since any fields that are added to the struct in + future will be defined to behave backwards-compatibly if they are zero. + The currently available options fields are: + + + + + ParserSetupHook parserSetup + + + Parser hook setup function + + + + + + void * parserSetupArg + + + pass-through argument for parserSetup + + + + + + RawParseMode parseMode + + + mode for raw parsing; RAW_PARSE_DEFAULT (zero) + produces default behavior + + + + + + int cursorOptions + + + integer bit mask of cursor options; zero produces default behavior + + + + + + + + Return Value + + + SPI_prepare_extended has the same return conventions as + SPI_prepare. + + + + + + + + SPI_prepare_params + + + SPI_prepare_params + 3 + + + + SPI_prepare_params + prepare a statement, without executing it yet + + + + +SPIPlanPtr SPI_prepare_params(const char * command, + ParserSetupHook parserSetup, + void * parserSetupArg, + int cursorOptions) + + + + + Description + + + SPI_prepare_params creates and returns a prepared + statement for the specified command, but doesn't execute the command. + This function is equivalent to SPI_prepare_cursor, + with the addition that the caller can specify parser hook functions + to control the parsing of external parameter references. + + + + This function is now deprecated in favor + of SPI_prepare_extended. + + + + + Arguments + + + + const char * command + + + command string + + + + + + ParserSetupHook parserSetup + + + Parser hook setup function + + + + + + void * parserSetupArg + + + pass-through argument for parserSetup + + + + + + int cursorOptions + + + integer bit mask of cursor options; zero produces default behavior + + + + + + + + Return Value + + + SPI_prepare_params has the same return conventions as + SPI_prepare. + + + + + + + + SPI_getargcount + + + SPI_getargcount + 3 + + + + SPI_getargcount + return the number of arguments needed by a statement + prepared by SPI_prepare + + + + +int SPI_getargcount(SPIPlanPtr plan) + + + + + Description + + + SPI_getargcount returns the number of arguments needed + to execute a statement prepared by SPI_prepare. + + + + + Arguments + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + + + Return Value + + The count of expected arguments for the plan. + If the plan is NULL or invalid, + SPI_result is set to SPI_ERROR_ARGUMENT + and -1 is returned. + + + + + + + + SPI_getargtypeid + + + SPI_getargtypeid + 3 + + + + SPI_getargtypeid + return the data type OID for an argument of + a statement prepared by SPI_prepare + + + + +Oid SPI_getargtypeid(SPIPlanPtr plan, int argIndex) + + + + + Description + + + SPI_getargtypeid returns the OID representing the type + for the argIndex'th argument of a statement prepared by + SPI_prepare. First argument is at index zero. + + + + + Arguments + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + int argIndex + + + zero based index of the argument + + + + + + + + Return Value + + The type OID of the argument at the given index. + If the plan is NULL or invalid, + or argIndex is less than 0 or + not less than the number of arguments declared for the + plan, + SPI_result is set to SPI_ERROR_ARGUMENT + and InvalidOid is returned. + + + + + + + + SPI_is_cursor_plan + + + SPI_is_cursor_plan + 3 + + + + SPI_is_cursor_plan + return true if a statement + prepared by SPI_prepare can be used with + SPI_cursor_open + + + + +bool SPI_is_cursor_plan(SPIPlanPtr plan) + + + + + Description + + + SPI_is_cursor_plan returns true + if a statement prepared by SPI_prepare can be passed + as an argument to SPI_cursor_open, or + false if that is not the case. The criteria are that the + plan represents one single command and that this + command returns tuples to the caller; for example, SELECT + is allowed unless it contains an INTO clause, and + UPDATE is allowed only if it contains a RETURNING + clause. + + + + + Arguments + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + + + Return Value + + true or false to indicate if the + plan can produce a cursor or not, with + SPI_result set to zero. + If it is not possible to determine the answer (for example, + if the plan is NULL or invalid, + or if called when not connected to SPI), then + SPI_result is set to a suitable error code + and false is returned. + + + + + + + + SPI_execute_plan + + + SPI_execute_plan + 3 + + + + SPI_execute_plan + execute a statement prepared by SPI_prepare + + + + +int SPI_execute_plan(SPIPlanPtr plan, Datum * values, const char * nulls, + bool read_only, long count) + + + + + Description + + + SPI_execute_plan executes a statement prepared by + SPI_prepare or one of its siblings. + read_only and + count have the same interpretation as in + SPI_execute. + + + + + Arguments + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + Datum * values + + + An array of actual parameter values. Must have same length as the + statement's number of arguments. + + + + + + const char * nulls + + + An array describing which parameters are null. Must have same length as + the statement's number of arguments. + + + + If nulls is NULL then + SPI_execute_plan assumes that no parameters + are null. Otherwise, each entry of the nulls + array should be ' ' if the corresponding parameter + value is non-null, or 'n' if the corresponding parameter + value is null. (In the latter case, the actual value in the + corresponding values entry doesn't matter.) Note + that nulls is not a text string, just an array: + it does not need a '\0' terminator. + + + + + + bool read_only + + true for read-only execution + + + + + long count + + + maximum number of rows to return, + or 0 for no limit + + + + + + + + Return Value + + + The return value is the same as for SPI_execute, + with the following additional possible error (negative) results: + + + + SPI_ERROR_ARGUMENT + + + if plan is NULL or invalid, + or count is less than 0 + + + + + + SPI_ERROR_PARAM + + + if values is NULL and + plan was prepared with some parameters + + + + + + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + + + + SPI_execute_plan_extended + + + SPI_execute_plan_extended + 3 + + + + SPI_execute_plan_extended + execute a statement prepared by SPI_prepare + + + + +int SPI_execute_plan_extended(SPIPlanPtr plan, + const SPIExecuteOptions * options) + + + + + Description + + + SPI_execute_plan_extended executes a statement + prepared by SPI_prepare or one of its siblings. + This function is equivalent to SPI_execute_plan, + except that information about the parameter values to be passed to the + query is presented differently, and additional execution-controlling + options can be passed. + + + + Query parameter values are represented by + a ParamListInfo struct, which is convenient for passing + down values that are already available in that format. Dynamic parameter + sets can also be used, via hook functions specified + in ParamListInfo. + + + + Also, instead of always accumulating the result tuples into a + SPI_tuptable structure, tuples can be passed to a + caller-supplied DestReceiver object as they are + generated by the executor. This is particularly helpful for queries + that might generate many tuples, since the data can be processed + on-the-fly instead of being accumulated in memory. + + + + + Arguments + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + const SPIExecuteOptions * options + + + struct containing optional arguments + + + + + + + Callers should always zero out the entire options + struct, then fill whichever fields they want to set. This ensures forward + compatibility of code, since any fields that are added to the struct in + future will be defined to behave backwards-compatibly if they are zero. + The currently available options fields are: + + + + + ParamListInfo params + + + data structure containing query parameter types and values; NULL if none + + + + + + bool read_only + + true for read-only execution + + + + + bool allow_nonatomic + + + true allows non-atomic execution of CALL and DO + statements + + + + + + bool must_return_tuples + + + if true, raise error if the query is not of a kind + that returns tuples (this does not forbid the case where it happens to + return zero tuples) + + + + + + uint64 tcount + + + maximum number of rows to return, + or 0 for no limit + + + + + + DestReceiver * dest + + + DestReceiver object that will receive any tuples + emitted by the query; if NULL, result tuples are accumulated into + a SPI_tuptable structure, as + in SPI_execute_plan + + + + + + ResourceOwner owner + + + The resource owner that will hold a reference count on the plan while + it is executed. If NULL, CurrentResourceOwner is used. Ignored for + non-saved plans, as SPI does not acquire reference counts on those. + + + + + + + + Return Value + + + The return value is the same as for SPI_execute_plan. + + + + When options->dest is NULL, + SPI_processed and + SPI_tuptable are set as in + SPI_execute_plan. + When options->dest is not NULL, + SPI_processed is set to zero and + SPI_tuptable is set to NULL. If a tuple count + is required, the caller's DestReceiver object must + calculate it. + + + + + + + + SPI_execute_plan_with_paramlist + + + SPI_execute_plan_with_paramlist + 3 + + + + SPI_execute_plan_with_paramlist + execute a statement prepared by SPI_prepare + + + + +int SPI_execute_plan_with_paramlist(SPIPlanPtr plan, + ParamListInfo params, + bool read_only, + long count) + + + + + Description + + + SPI_execute_plan_with_paramlist executes a statement + prepared by SPI_prepare. + This function is equivalent to SPI_execute_plan + except that information about the parameter values to be passed to the + query is presented differently. The ParamListInfo + representation can be convenient for passing down values that are + already available in that format. It also supports use of dynamic + parameter sets via hook functions specified in ParamListInfo. + + + + This function is now deprecated in favor + of SPI_execute_plan_extended. + + + + + Arguments + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + ParamListInfo params + + + data structure containing parameter types and values; NULL if none + + + + + + bool read_only + + true for read-only execution + + + + + long count + + + maximum number of rows to return, + or 0 for no limit + + + + + + + + Return Value + + + The return value is the same as for SPI_execute_plan. + + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute_plan if successful. + + + + + + + + SPI_execp + + + SPI_execp + 3 + + + + SPI_execp + execute a statement in read/write mode + + + + +int SPI_execp(SPIPlanPtr plan, Datum * values, const char * nulls, long count) + + + + + Description + + + SPI_execp is the same as + SPI_execute_plan, with the latter's + read_only parameter always taken as + false. + + + + + Arguments + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + Datum * values + + + An array of actual parameter values. Must have same length as the + statement's number of arguments. + + + + + + const char * nulls + + + An array describing which parameters are null. Must have same length as + the statement's number of arguments. + + + + If nulls is NULL then + SPI_execp assumes that no parameters + are null. Otherwise, each entry of the nulls + array should be ' ' if the corresponding parameter + value is non-null, or 'n' if the corresponding parameter + value is null. (In the latter case, the actual value in the + corresponding values entry doesn't matter.) Note + that nulls is not a text string, just an array: + it does not need a '\0' terminator. + + + + + + long count + + + maximum number of rows to return, + or 0 for no limit + + + + + + + + Return Value + + + See SPI_execute_plan. + + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + + + + SPI_cursor_open + + + SPI_cursor_open + 3 + + + + SPI_cursor_open + set up a cursor using a statement created with SPI_prepare + + + + +Portal SPI_cursor_open(const char * name, SPIPlanPtr plan, + Datum * values, const char * nulls, + bool read_only) + + + + + Description + + + SPI_cursor_open sets up a cursor (internally, + a portal) that will execute a statement prepared by + SPI_prepare. The parameters have the same + meanings as the corresponding parameters to + SPI_execute_plan. + + + + Using a cursor instead of executing the statement directly has two + benefits. First, the result rows can be retrieved a few at a time, + avoiding memory overrun for queries that return many rows. Second, + a portal can outlive the current C function (it can, in fact, live + to the end of the current transaction). Returning the portal name + to the C function's caller provides a way of returning a row set as + result. + + + + The passed-in parameter data will be copied into the cursor's portal, so it + can be freed while the cursor still exists. + + + + + Arguments + + + + const char * name + + + name for portal, or NULL to let the system + select a name + + + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + Datum * values + + + An array of actual parameter values. Must have same length as the + statement's number of arguments. + + + + + + const char * nulls + + + An array describing which parameters are null. Must have same length as + the statement's number of arguments. + + + + If nulls is NULL then + SPI_cursor_open assumes that no parameters + are null. Otherwise, each entry of the nulls + array should be ' ' if the corresponding parameter + value is non-null, or 'n' if the corresponding parameter + value is null. (In the latter case, the actual value in the + corresponding values entry doesn't matter.) Note + that nulls is not a text string, just an array: + it does not need a '\0' terminator. + + + + + + bool read_only + + true for read-only execution + + + + + + + Return Value + + + Pointer to portal containing the cursor. Note there is no error + return convention; any error will be reported via elog. + + + + + + + + SPI_cursor_open_with_args + + + SPI_cursor_open_with_args + 3 + + + + SPI_cursor_open_with_args + set up a cursor using a query and parameters + + + + +Portal SPI_cursor_open_with_args(const char *name, + const char *command, + int nargs, Oid *argtypes, + Datum *values, const char *nulls, + bool read_only, int cursorOptions) + + + + + Description + + + SPI_cursor_open_with_args sets up a cursor + (internally, a portal) that will execute the specified query. + Most of the parameters have the same meanings as the corresponding + parameters to SPI_prepare_cursor + and SPI_cursor_open. + + + + For one-time query execution, this function should be preferred + over SPI_prepare_cursor followed by + SPI_cursor_open. + If the same command is to be executed with many different parameters, + either method might be faster, depending on the cost of re-planning + versus the benefit of custom plans. + + + + The passed-in parameter data will be copied into the cursor's portal, so it + can be freed while the cursor still exists. + + + + This function is now deprecated in favor + of SPI_cursor_parse_open, which provides equivalent + functionality using a more modern API for handling query parameters. + + + + + Arguments + + + + const char * name + + + name for portal, or NULL to let the system + select a name + + + + + + const char * command + + + command string + + + + + + int nargs + + + number of input parameters ($1, $2, etc.) + + + + + + Oid * argtypes + + + an array of length nargs, containing the + OIDs of the data types of the parameters + + + + + + Datum * values + + + an array of length nargs, containing the actual + parameter values + + + + + + const char * nulls + + + an array of length nargs, describing which + parameters are null + + + + If nulls is NULL then + SPI_cursor_open_with_args assumes that no parameters + are null. Otherwise, each entry of the nulls + array should be ' ' if the corresponding parameter + value is non-null, or 'n' if the corresponding parameter + value is null. (In the latter case, the actual value in the + corresponding values entry doesn't matter.) Note + that nulls is not a text string, just an array: + it does not need a '\0' terminator. + + + + + + bool read_only + + true for read-only execution + + + + + int cursorOptions + + + integer bit mask of cursor options; zero produces default behavior + + + + + + + + Return Value + + + Pointer to portal containing the cursor. Note there is no error + return convention; any error will be reported via elog. + + + + + + + + SPI_cursor_open_with_paramlist + + + SPI_cursor_open_with_paramlist + 3 + + + + SPI_cursor_open_with_paramlist + set up a cursor using parameters + + + + +Portal SPI_cursor_open_with_paramlist(const char *name, + SPIPlanPtr plan, + ParamListInfo params, + bool read_only) + + + + + Description + + + SPI_cursor_open_with_paramlist sets up a cursor + (internally, a portal) that will execute a statement prepared by + SPI_prepare. + This function is equivalent to SPI_cursor_open + except that information about the parameter values to be passed to the + query is presented differently. The ParamListInfo + representation can be convenient for passing down values that are + already available in that format. It also supports use of dynamic + parameter sets via hook functions specified in ParamListInfo. + + + + The passed-in parameter data will be copied into the cursor's portal, so it + can be freed while the cursor still exists. + + + + + Arguments + + + + const char * name + + + name for portal, or NULL to let the system + select a name + + + + + + SPIPlanPtr plan + + + prepared statement (returned by SPI_prepare) + + + + + + ParamListInfo params + + + data structure containing parameter types and values; NULL if none + + + + + + bool read_only + + true for read-only execution + + + + + + + Return Value + + + Pointer to portal containing the cursor. Note there is no error + return convention; any error will be reported via elog. + + + + + + + + SPI_cursor_parse_open + + + SPI_cursor_parse_open + 3 + + + + SPI_cursor_parse_open + set up a cursor using a query string and parameters + + + + +Portal SPI_cursor_parse_open(const char *name, + const char *command, + const SPIParseOpenOptions * options) + + + + + Description + + + SPI_cursor_parse_open sets up a cursor + (internally, a portal) that will execute the specified query string. + This is comparable to SPI_prepare_cursor followed + by SPI_cursor_open_with_paramlist, except that + parameter references within the query string are handled entirely by + supplying a ParamListInfo object. + + + + For one-time query execution, this function should be preferred + over SPI_prepare_cursor followed by + SPI_cursor_open_with_paramlist. + If the same command is to be executed with many different parameters, + either method might be faster, depending on the cost of re-planning + versus the benefit of custom plans. + + + + The options->params object should normally + mark each parameter with the PARAM_FLAG_CONST flag, + since a one-shot plan is always used for the query. + + + + The passed-in parameter data will be copied into the cursor's portal, so it + can be freed while the cursor still exists. + + + + + Arguments + + + + const char * name + + + name for portal, or NULL to let the system + select a name + + + + + + const char * command + + + command string + + + + + + const SPIParseOpenOptions * options + + + struct containing optional arguments + + + + + + + Callers should always zero out the entire options + struct, then fill whichever fields they want to set. This ensures forward + compatibility of code, since any fields that are added to the struct in + future will be defined to behave backwards-compatibly if they are zero. + The currently available options fields are: + + + + + ParamListInfo params + + + data structure containing query parameter types and values; NULL if none + + + + + + int cursorOptions + + + integer bit mask of cursor options; zero produces default behavior + + + + + + bool read_only + + true for read-only execution + + + + + + + Return Value + + + Pointer to portal containing the cursor. Note there is no error + return convention; any error will be reported via elog. + + + + + + + + SPI_cursor_find + + + SPI_cursor_find + 3 + + + + SPI_cursor_find + find an existing cursor by name + + + + +Portal SPI_cursor_find(const char * name) + + + + + Description + + + SPI_cursor_find finds an existing portal by + name. This is primarily useful to resolve a cursor name returned + as text by some other function. + + + + + Arguments + + + + const char * name + + + name of the portal + + + + + + + + Return Value + + + pointer to the portal with the specified name, or + NULL if none was found + + + + + 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 + + + SPI_cursor_fetch + 3 + + + + SPI_cursor_fetch + fetch some rows from a cursor + + + + +void SPI_cursor_fetch(Portal portal, bool forward, long count) + + + + + Description + + + SPI_cursor_fetch fetches some rows from a + cursor. This is equivalent to a subset of the SQL command + FETCH (see SPI_scroll_cursor_fetch + for more functionality). + + + + + Arguments + + + + Portal portal + + + portal containing the cursor + + + + + + bool forward + + + true for fetch forward, false for fetch backward + + + + + + long count + + + maximum number of rows to fetch + + + + + + + + Return Value + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + Notes + + + Fetching backward may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + + + + + + + SPI_cursor_move + + + SPI_cursor_move + 3 + + + + SPI_cursor_move + move a cursor + + + + +void SPI_cursor_move(Portal portal, bool forward, long count) + + + + + Description + + + SPI_cursor_move skips over some number of rows + in a cursor. This is equivalent to a subset of the SQL command + MOVE (see SPI_scroll_cursor_move + for more functionality). + + + + + Arguments + + + + Portal portal + + + portal containing the cursor + + + + + + bool forward + + + true for move forward, false for move backward + + + + + + long count + + + maximum number of rows to move + + + + + + + + Notes + + + Moving backward may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + + + + + + + SPI_scroll_cursor_fetch + + + SPI_scroll_cursor_fetch + 3 + + + + SPI_scroll_cursor_fetch + fetch some rows from a cursor + + + + +void SPI_scroll_cursor_fetch(Portal portal, FetchDirection direction, + long count) + + + + + Description + + + SPI_scroll_cursor_fetch fetches some rows from a + cursor. This is equivalent to the SQL command FETCH. + + + + + Arguments + + + + Portal portal + + + portal containing the cursor + + + + + + FetchDirection direction + + + one of FETCH_FORWARD, + FETCH_BACKWARD, + FETCH_ABSOLUTE or + FETCH_RELATIVE + + + + + + long count + + + number of rows to fetch for + FETCH_FORWARD or + FETCH_BACKWARD; absolute row number to fetch for + FETCH_ABSOLUTE; or relative row number to fetch for + FETCH_RELATIVE + + + + + + + + Return Value + + + SPI_processed and + SPI_tuptable are set as in + SPI_execute if successful. + + + + + Notes + + + See the SQL command + for details of the interpretation of the + direction and + count parameters. + + + + Direction values other than FETCH_FORWARD + may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + + + + + + + SPI_scroll_cursor_move + + + SPI_scroll_cursor_move + 3 + + + + SPI_scroll_cursor_move + move a cursor + + + + +void SPI_scroll_cursor_move(Portal portal, FetchDirection direction, + long count) + + + + + Description + + + SPI_scroll_cursor_move skips over some number of rows + in a cursor. This is equivalent to the SQL command + MOVE. + + + + + Arguments + + + + Portal portal + + + portal containing the cursor + + + + + + FetchDirection direction + + + one of FETCH_FORWARD, + FETCH_BACKWARD, + FETCH_ABSOLUTE or + FETCH_RELATIVE + + + + + + long count + + + number of rows to move for + FETCH_FORWARD or + FETCH_BACKWARD; absolute row number to move to for + FETCH_ABSOLUTE; or relative row number to move to for + FETCH_RELATIVE + + + + + + + + Return Value + + + SPI_processed is set as in + SPI_execute if successful. + SPI_tuptable is set to NULL, since + no rows are returned by this function. + + + + + Notes + + + See the SQL command + for details of the interpretation of the + direction and + count parameters. + + + + Direction values other than FETCH_FORWARD + may fail if the cursor's plan was not created + with the CURSOR_OPT_SCROLL option. + + + + + + + + SPI_cursor_close + + + SPI_cursor_close + 3 + + + + SPI_cursor_close + close a cursor + + + + +void SPI_cursor_close(Portal portal) + + + + + Description + + + SPI_cursor_close closes a previously created + cursor and releases its portal storage. + + + + All open cursors are closed automatically at the end of a + transaction. SPI_cursor_close need only be + invoked if it is desirable to release resources sooner. + + + + + Arguments + + + + Portal portal + + + portal containing the cursor + + + + + + + + + + + SPI_keepplan + + + SPI_keepplan + 3 + + + + SPI_keepplan + save a prepared statement + + + + +int SPI_keepplan(SPIPlanPtr plan) + + + + + Description + + + SPI_keepplan saves a passed statement (prepared by + SPI_prepare) so that it will not be freed + by SPI_finish nor by the transaction manager. + This gives you the ability to reuse prepared statements in the subsequent + invocations of your C function in the current session. + + + + + Arguments + + + + SPIPlanPtr plan + + + the prepared statement to be saved + + + + + + + + Return Value + + + 0 on success; + SPI_ERROR_ARGUMENT if plan + is NULL or invalid + + + + + Notes + + + The passed-in statement is relocated to permanent storage by means + of pointer adjustment (no data copying is required). If you later + wish to delete it, use SPI_freeplan on it. + + + + + + + + SPI_saveplan + + + SPI_saveplan + 3 + + + + SPI_saveplan + save a prepared statement + + + + +SPIPlanPtr SPI_saveplan(SPIPlanPtr plan) + + + + + Description + + + SPI_saveplan copies a passed statement (prepared by + SPI_prepare) into memory that will not be freed + by SPI_finish nor by the transaction manager, + and returns a pointer to the copied statement. This gives you the + ability to reuse prepared statements in the subsequent invocations of + your C function in the current session. + + + + + Arguments + + + + SPIPlanPtr plan + + + the prepared statement to be saved + + + + + + + + Return Value + + + Pointer to the copied statement; or NULL if unsuccessful. + On error, SPI_result is set thus: + + + + SPI_ERROR_ARGUMENT + + + if plan is NULL or invalid + + + + + + SPI_ERROR_UNCONNECTED + + + if called from an unconnected C function + + + + + + + + + Notes + + + The originally passed-in statement is not freed, so you might wish to do + SPI_freeplan on it to avoid leaking memory + until SPI_finish. + + + + In most cases, SPI_keepplan is preferred to this + function, since it accomplishes largely the same result without needing + to physically copy the prepared statement's data structures. + + + + + + + + SPI_register_relation + + + ephemeral named relation + registering with SPI + + + + SPI_register_relation + 3 + + + + SPI_register_relation + make an ephemeral named relation available by name in SPI queries + + + + +int SPI_register_relation(EphemeralNamedRelation enr) + + + + + Description + + + SPI_register_relation makes an ephemeral named + relation, with associated information, available to queries planned and + executed through the current SPI connection. + + + + + Arguments + + + + EphemeralNamedRelation enr + + + the ephemeral named relation registry entry + + + + + + + + Return Value + + + If the execution of the command was successful then the following + (nonnegative) value will be returned: + + + + SPI_OK_REL_REGISTER + + + if the relation has been successfully registered by name + + + + + + + + On error, one of the following negative values is returned: + + + + SPI_ERROR_ARGUMENT + + + if enr is NULL or its + name field is NULL + + + + + + SPI_ERROR_UNCONNECTED + + + if called from an unconnected C function + + + + + + SPI_ERROR_REL_DUPLICATE + + + if the name specified in the name field of + enr is already registered for this connection + + + + + + + + + + + + SPI_unregister_relation + + + ephemeral named relation + unregistering from SPI + + + + SPI_unregister_relation + 3 + + + + SPI_unregister_relation + remove an ephemeral named relation from the registry + + + + +int SPI_unregister_relation(const char * name) + + + + + Description + + + SPI_unregister_relation removes an ephemeral named + relation from the registry for the current connection. + + + + + Arguments + + + + const char * name + + + the relation registry entry name + + + + + + + + Return Value + + + If the execution of the command was successful then the following + (nonnegative) value will be returned: + + + + SPI_OK_REL_UNREGISTER + + + if the tuplestore has been successfully removed from the registry + + + + + + + + On error, one of the following negative values is returned: + + + + SPI_ERROR_ARGUMENT + + + if name is NULL + + + + + + SPI_ERROR_UNCONNECTED + + + if called from an unconnected C function + + + + + + SPI_ERROR_REL_NOT_FOUND + + + if name is not found in the registry for the + current connection + + + + + + + + + + + + SPI_register_trigger_data + + + ephemeral named relation + registering with SPI + + + + transition tables + implementation in PLs + + + + SPI_register_trigger_data + 3 + + + + SPI_register_trigger_data + make ephemeral trigger data available in SPI queries + + + + +int SPI_register_trigger_data(TriggerData *tdata) + + + + + Description + + + SPI_register_trigger_data makes any ephemeral + relations captured by a trigger available to queries planned and executed + through the current SPI connection. Currently, this means the transition + tables captured by an AFTER trigger defined with a + REFERENCING OLD/NEW TABLE AS ... clause. This function + should be called by a PL trigger handler function after connecting. + + + + + Arguments + + + + TriggerData *tdata + + + the TriggerData object passed to a trigger + handler function as fcinfo->context + + + + + + + + Return Value + + + If the execution of the command was successful then the following + (nonnegative) value will be returned: + + + + SPI_OK_TD_REGISTER + + + if the captured trigger data (if any) has been successfully registered + + + + + + + + On error, one of the following negative values is returned: + + + + SPI_ERROR_ARGUMENT + + + if tdata is NULL + + + + + + SPI_ERROR_UNCONNECTED + + + if called from an unconnected C function + + + + + + SPI_ERROR_REL_DUPLICATE + + + if the name of any trigger data transient relation is already + registered for this connection + + + + + + + + + + + + + + Interface Support Functions + + + The functions described here provide an interface for extracting + information from result sets returned by SPI_execute and + other SPI functions. + + + + All functions described in this section can be used by both + connected and unconnected C functions. + + + + + + SPI_fname + + + SPI_fname + 3 + + + + SPI_fname + determine the column name for the specified column number + + + + +char * SPI_fname(TupleDesc rowdesc, int colnumber) + + + + + Description + + + SPI_fname returns a copy of the column name of the + specified column. (You can use pfree to + release the copy of the name when you don't need it anymore.) + + + + + Arguments + + + + TupleDesc rowdesc + + + input row description + + + + + + int colnumber + + + column number (count starts at 1) + + + + + + + + Return Value + + + The column name; NULL if + colnumber is out of range. + SPI_result set to + SPI_ERROR_NOATTRIBUTE on error. + + + + + + + + SPI_fnumber + + + SPI_fnumber + 3 + + + + SPI_fnumber + determine the column number for the specified column name + + + + +int SPI_fnumber(TupleDesc rowdesc, const char * colname) + + + + + Description + + + SPI_fnumber returns the column number for the + column with the specified name. + + + + If colname refers to a system column (e.g., + ctid) then the appropriate negative column number will + be returned. The caller should be careful to test the return value + for exact equality to SPI_ERROR_NOATTRIBUTE to + detect an error; testing the result for less than or equal to 0 is + not correct unless system columns should be rejected. + + + + + Arguments + + + + TupleDesc rowdesc + + + input row description + + + + + + const char * colname + + + column name + + + + + + + + Return Value + + + Column number (count starts at 1 for user-defined columns), or + SPI_ERROR_NOATTRIBUTE if the named column was not + found. + + + + + + + + SPI_getvalue + + + SPI_getvalue + 3 + + + + SPI_getvalue + return the string value of the specified column + + + + +char * SPI_getvalue(HeapTuple row, TupleDesc rowdesc, int colnumber) + + + + + Description + + + SPI_getvalue returns the string representation + of the value of the specified column. + + + + The result is returned in memory allocated using + palloc. (You can use + pfree to release the memory when you don't + need it anymore.) + + + + + Arguments + + + + HeapTuple row + + + input row to be examined + + + + + + TupleDesc rowdesc + + + input row description + + + + + + int colnumber + + + column number (count starts at 1) + + + + + + + + Return Value + + + Column value, or NULL if the column is null, + colnumber is out of range + (SPI_result is set to + SPI_ERROR_NOATTRIBUTE), or no output function is + available (SPI_result is set to + SPI_ERROR_NOOUTFUNC). + + + + + + + + SPI_getbinval + + + SPI_getbinval + 3 + + + + SPI_getbinval + return the binary value of the specified column + + + + +Datum SPI_getbinval(HeapTuple row, TupleDesc rowdesc, int colnumber, + bool * isnull) + + + + + Description + + + SPI_getbinval returns the value of the + specified column in the internal form (as type Datum). + + + + This function does not allocate new space for the datum. In the + case of a pass-by-reference data type, the return value will be a + pointer into the passed row. + + + + + Arguments + + + + HeapTuple row + + + input row to be examined + + + + + + TupleDesc rowdesc + + + input row description + + + + + + int colnumber + + + column number (count starts at 1) + + + + + + bool * isnull + + + flag for a null value in the column + + + + + + + + Return Value + + + The binary value of the column is returned. The variable pointed + to by isnull is set to true if the column is + null, else to false. + + + + SPI_result is set to + SPI_ERROR_NOATTRIBUTE on error. + + + + + + + + SPI_gettype + + + SPI_gettype + 3 + + + + SPI_gettype + return the data type name of the specified column + + + + +char * SPI_gettype(TupleDesc rowdesc, int colnumber) + + + + + Description + + + SPI_gettype returns a copy of the data type name of the + specified column. (You can use pfree to + release the copy of the name when you don't need it anymore.) + + + + + Arguments + + + + TupleDesc rowdesc + + + input row description + + + + + + int colnumber + + + column number (count starts at 1) + + + + + + + + Return Value + + + The data type name of the specified column, or + NULL on error. SPI_result is + set to SPI_ERROR_NOATTRIBUTE on error. + + + + + + + + SPI_gettypeid + + + SPI_gettypeid + 3 + + + + SPI_gettypeid + return the data type OID of the specified column + + + + +Oid SPI_gettypeid(TupleDesc rowdesc, int colnumber) + + + + + Description + + + SPI_gettypeid returns the + OID of the data type of the specified column. + + + + + Arguments + + + + TupleDesc rowdesc + + + input row description + + + + + + int colnumber + + + column number (count starts at 1) + + + + + + + + Return Value + + + The OID of the data type of the specified column + or InvalidOid on error. On error, + SPI_result is set to + SPI_ERROR_NOATTRIBUTE. + + + + + + + + SPI_getrelname + + + SPI_getrelname + 3 + + + + SPI_getrelname + return the name of the specified relation + + + + +char * SPI_getrelname(Relation rel) + + + + + Description + + + SPI_getrelname returns a copy of the name of the + specified relation. (You can use pfree to + release the copy of the name when you don't need it anymore.) + + + + + Arguments + + + + Relation rel + + + input relation + + + + + + + + Return Value + + + The name of the specified relation. + + + + + + SPI_getnspname + + + SPI_getnspname + 3 + + + + SPI_getnspname + return the namespace of the specified relation + + + + +char * SPI_getnspname(Relation rel) + + + + + Description + + + SPI_getnspname returns a copy of the name of + the namespace that the specified Relation + belongs to. This is equivalent to the relation's schema. You should + pfree the return value of this function when + you are finished with it. + + + + + Arguments + + + + Relation rel + + + input relation + + + + + + + + Return Value + + + The name of the specified relation's namespace. + + + + + + SPI_result_code_string + + + SPI_result_code_string + 3 + + + + SPI_result_code_string + return error code as string + + + + +const char * SPI_result_code_string(int code); + + + + + Description + + + SPI_result_code_string returns a string representation + of the result code returned by various SPI functions or stored + in SPI_result. + + + + + Arguments + + + + int code + + + result code + + + + + + + + Return Value + + + A string representation of the result code. + + + + + + + + Memory Management + + + + memory context + in SPI + + PostgreSQL allocates memory within + memory contexts, which provide a convenient method of + managing allocations made in many different places that need to + live for differing amounts of time. Destroying a context releases + all the memory that was allocated in it. Thus, it is not necessary + to keep track of individual objects to avoid memory leaks; instead + only a relatively small number of contexts have to be managed. + palloc and related functions allocate memory + from the current context. + + + + SPI_connect creates a new memory context and + makes it current. SPI_finish restores the + previous current memory context and destroys the context created by + SPI_connect. These actions ensure that + transient memory allocations made inside your C function are + reclaimed at C function exit, avoiding memory leakage. + + + + However, if your C function needs to return an object in allocated + memory (such as a value of a pass-by-reference data type), you + cannot allocate that memory using palloc, at + least not while you are connected to SPI. If you try, the object + will be deallocated by SPI_finish, and your + C function will not work reliably. To solve this problem, use + SPI_palloc to allocate memory for your return + object. SPI_palloc allocates memory in the + upper executor context, that is, the memory context + that was current when SPI_connect was called, + which is precisely the right context for a value returned from your + C function. Several of the other utility functions described in + this section also return objects created in the upper executor context. + + + + When SPI_connect is called, the private + context of the C function, which is created by + SPI_connect, is made the current context. All + allocations made by palloc, + repalloc, or SPI utility functions (except as + described in this section) are made in this context. When a + C function disconnects from the SPI manager (via + SPI_finish) the current context is restored to + the upper executor context, and all allocations made in the + C function memory context are freed and cannot be used any more. + + + + + + SPI_palloc + + + SPI_palloc + 3 + + + + SPI_palloc + allocate memory in the upper executor context + + + + +void * SPI_palloc(Size size) + + + + + Description + + + SPI_palloc allocates memory in the upper + executor context. + + + + This function can only be used while connected to SPI. + Otherwise, it throws an error. + + + + + Arguments + + + + Size size + + + size in bytes of storage to allocate + + + + + + + + Return Value + + + pointer to new storage space of the specified size + + + + + + + + SPI_repalloc + + + SPI_repalloc + 3 + + + + SPI_repalloc + reallocate memory in the upper executor context + + + + +void * SPI_repalloc(void * pointer, Size size) + + + + + Description + + + SPI_repalloc changes the size of a memory + segment previously allocated using SPI_palloc. + + + + This function is no longer different from plain + repalloc. It's kept just for backward + compatibility of existing code. + + + + + Arguments + + + + void * pointer + + + pointer to existing storage to change + + + + + + Size size + + + size in bytes of storage to allocate + + + + + + + + Return Value + + + pointer to new storage space of specified size with the contents + copied from the existing area + + + + + + + + SPI_pfree + + + SPI_pfree + 3 + + + + SPI_pfree + free memory in the upper executor context + + + + +void SPI_pfree(void * pointer) + + + + + Description + + + SPI_pfree frees memory previously allocated + using SPI_palloc or + SPI_repalloc. + + + + This function is no longer different from plain + pfree. It's kept just for backward + compatibility of existing code. + + + + + Arguments + + + + void * pointer + + + pointer to existing storage to free + + + + + + + + + + + SPI_copytuple + + + SPI_copytuple + 3 + + + + SPI_copytuple + make a copy of a row in the upper executor context + + + + +HeapTuple SPI_copytuple(HeapTuple row) + + + + + Description + + + SPI_copytuple makes a copy of a row in the + upper executor context. This is normally used to return a modified + row from a trigger. In a function declared to return a composite + type, use SPI_returntuple instead. + + + + This function can only be used while connected to SPI. + Otherwise, it returns NULL and sets SPI_result to + SPI_ERROR_UNCONNECTED. + + + + + Arguments + + + + HeapTuple row + + + row to be copied + + + + + + + + Return Value + + + the copied row, or NULL on error + (see SPI_result for an error indication) + + + + + + + + SPI_returntuple + + + SPI_returntuple + 3 + + + + SPI_returntuple + prepare to return a tuple as a Datum + + + + +HeapTupleHeader SPI_returntuple(HeapTuple row, TupleDesc rowdesc) + + + + + Description + + + SPI_returntuple makes a copy of a row in + the upper executor context, returning it in the form of a row type Datum. + The returned pointer need only be converted to Datum via PointerGetDatum + before returning. + + + + This function can only be used while connected to SPI. + Otherwise, it returns NULL and sets SPI_result to + SPI_ERROR_UNCONNECTED. + + + + Note that this should be used for functions that are declared to return + composite types. It is not used for triggers; use + SPI_copytuple for returning a modified row in a trigger. + + + + + Arguments + + + + HeapTuple row + + + row to be copied + + + + + + TupleDesc rowdesc + + + descriptor for row (pass the same descriptor each time for most + effective caching) + + + + + + + + Return Value + + + HeapTupleHeader pointing to copied row, + or NULL on error + (see SPI_result for an error indication) + + + + + + + + SPI_modifytuple + + + SPI_modifytuple + 3 + + + + SPI_modifytuple + create a row by replacing selected fields of a given row + + + + +HeapTuple SPI_modifytuple(Relation rel, HeapTuple row, int ncols, + int * colnum, Datum * values, const char * nulls) + + + + + Description + + + SPI_modifytuple creates a new row by + substituting new values for selected columns, copying the original + row's columns at other positions. The input row is not modified. + The new row is returned in the upper executor context. + + + + This function can only be used while connected to SPI. + Otherwise, it returns NULL and sets SPI_result to + SPI_ERROR_UNCONNECTED. + + + + + Arguments + + + + Relation rel + + + Used only as the source of the row descriptor for the row. + (Passing a relation rather than a row descriptor is a + misfeature.) + + + + + + HeapTuple row + + + row to be modified + + + + + + int ncols + + + number of columns to be changed + + + + + + int * colnum + + + an array of length ncols, containing the numbers + of the columns that are to be changed (column numbers start at 1) + + + + + + Datum * values + + + an array of length ncols, containing the + new values for the specified columns + + + + + + const char * nulls + + + an array of length ncols, describing which + new values are null + + + + If nulls is NULL then + SPI_modifytuple assumes that no new values + are null. Otherwise, each entry of the nulls + array should be ' ' if the corresponding new value is + non-null, or 'n' if the corresponding new value is + null. (In the latter case, the actual value in the corresponding + values entry doesn't matter.) Note that + nulls is not a text string, just an array: it + does not need a '\0' terminator. + + + + + + + + Return Value + + + new row with modifications, allocated in the upper executor + context, or NULL on error + (see SPI_result for an error indication) + + + + On error, SPI_result is set as follows: + + + SPI_ERROR_ARGUMENT + + + if rel is NULL, or if + row is NULL, or if ncols + is less than or equal to 0, or if colnum is + NULL, or if values is NULL. + + + + + + SPI_ERROR_NOATTRIBUTE + + + if colnum contains an invalid column number (less + than or equal to 0 or greater than the number of columns in + row) + + + + + + SPI_ERROR_UNCONNECTED + + + if SPI is not active + + + + + + + + + + + + SPI_freetuple + + + SPI_freetuple + 3 + + + + SPI_freetuple + free a row allocated in the upper executor context + + + + +void SPI_freetuple(HeapTuple row) + + + + + Description + + + SPI_freetuple frees a row previously allocated + in the upper executor context. + + + + This function is no longer different from plain + heap_freetuple. It's kept just for backward + compatibility of existing code. + + + + + Arguments + + + + HeapTuple row + + + row to free + + + + + + + + + + + SPI_freetuptable + + + SPI_freetuptable + 3 + + + + SPI_freetuptable + free a row set created by SPI_execute or a similar + function + + + + +void SPI_freetuptable(SPITupleTable * tuptable) + + + + + Description + + + SPI_freetuptable frees a row set created by a + prior SPI command execution function, such as + SPI_execute. Therefore, this function is often called + with the global variable SPI_tuptable as + argument. + + + + This function is useful if an SPI-using C function needs to execute + multiple commands and does not want to keep the results of earlier + commands around until it ends. Note that any unfreed row sets will + be freed anyway at SPI_finish. + Also, if a subtransaction is started and then aborted within execution + of an SPI-using C function, SPI automatically frees any row sets created while + the subtransaction was running. + + + + Beginning in PostgreSQL 9.3, + SPI_freetuptable contains guard logic to protect + against duplicate deletion requests for the same row set. In previous + releases, duplicate deletions would lead to crashes. + + + + + Arguments + + + + SPITupleTable * tuptable + + + pointer to row set to free, or NULL to do nothing + + + + + + + + + + + SPI_freeplan + + + SPI_freeplan + 3 + + + + SPI_freeplan + free a previously saved prepared statement + + + + +int SPI_freeplan(SPIPlanPtr plan) + + + + + Description + + + SPI_freeplan releases a prepared statement + previously returned by SPI_prepare or saved by + SPI_keepplan or SPI_saveplan. + + + + + Arguments + + + + SPIPlanPtr plan + + + pointer to statement to free + + + + + + + + Return Value + + + 0 on success; + SPI_ERROR_ARGUMENT if plan + is NULL or invalid + + + + + + + + Transaction Management + + + It is not possible to run transaction control commands such + as COMMIT and ROLLBACK through SPI + functions such as SPI_execute. There are, however, + separate interface functions that allow transaction control through SPI. + + + + It is not generally safe and sensible to start and end transactions in + arbitrary user-defined SQL-callable functions without taking into account + the context in which they are called. For example, a transaction boundary + in the middle of a function that is part of a complex SQL expression that + is part of some SQL command will probably result in obscure internal errors + or crashes. The interface functions presented here are primarily intended + to be used by procedural language implementations to support transaction + management in SQL-level procedures that are invoked by the CALL + command, taking the context of the CALL invocation into + account. SPI-using procedures implemented in C can implement the same logic, but + the details of that are beyond the scope of this documentation. + + + + + + SPI_commit + SPI_commit_and_chain + + + SPI_commit + 3 + + + + SPI_commit + SPI_commit_and_chain + commit the current transaction + + + + +void SPI_commit(void) + + + +void SPI_commit_and_chain(void) + + + + + Description + + + SPI_commit commits the current transaction. It is + approximately equivalent to running the SQL + command COMMIT. After the transaction is committed, a + new transaction is automatically started using default transaction + characteristics, so that the caller can continue using SPI facilities. + If there is a failure during commit, the current transaction is instead + rolled back and a new transaction is started, after which the error is + thrown in the usual way. + + + + SPI_commit_and_chain is the same, but the new + transaction is started with the same transaction + characteristics as the just finished one, like with the SQL command + COMMIT AND CHAIN. + + + + These functions can only be executed if the SPI connection has been set as + nonatomic in the call to SPI_connect_ext. + + + + + + + + SPI_rollback + SPI_rollback_and_chain + + + SPI_rollback + 3 + + + + SPI_rollback + SPI_rollback_and_chain + abort the current transaction + + + + +void SPI_rollback(void) + + + +void SPI_rollback_and_chain(void) + + + + + Description + + + SPI_rollback rolls back the current transaction. It + is approximately equivalent to running the SQL + command ROLLBACK. After the transaction is rolled back, + a new transaction is automatically started using default transaction + characteristics, so that the caller can continue using SPI facilities. + + + SPI_rollback_and_chain is the same, but the new + transaction is started with the same transaction + characteristics as the just finished one, like with the SQL command + ROLLBACK AND CHAIN. + + + + These functions can only be executed if the SPI connection has been set as + nonatomic in the call to SPI_connect_ext. + + + + + + + + SPI_start_transaction + + + SPI_start_transaction + 3 + + + + SPI_start_transaction + obsolete function + + + + +void SPI_start_transaction(void) + + + + + Description + + + SPI_start_transaction does nothing, and exists + only for code compatibility with + earlier PostgreSQL releases. It used to + be required after calling SPI_commit + or SPI_rollback, but now those functions start + a new transaction automatically. + + + + + + + + Visibility of Data Changes + + + The following rules govern the visibility of data changes in + functions that use SPI (or any other C function): + + + + + During the execution of an SQL command, any data changes made by + the command are invisible to the command itself. For + example, in: + +INSERT INTO a SELECT * FROM a; + + the inserted rows are invisible to the SELECT + part. + + + + + + Changes made by a command C are visible to all commands that are + started after C, no matter whether they are started inside C + (during the execution of C) or after C is done. + + + + + + Commands executed via SPI inside a function called by an SQL command + (either an ordinary function or a trigger) follow one or the + other of the above rules depending on the read/write flag passed + to SPI. Commands executed in read-only mode follow the first + rule: they cannot see changes of the calling command. Commands executed + in read-write mode follow the second rule: they can see all changes made + so far. + + + + + + All standard procedural languages set the SPI read-write mode + depending on the volatility attribute of the function. Commands of + STABLE and IMMUTABLE functions are done in + read-only mode, while commands of VOLATILE functions are + done in read-write mode. While authors of C functions are able to + violate this convention, it's unlikely to be a good idea to do so. + + + + + + + The next section contains an example that illustrates the + application of these rules. + + + + + Examples + + + This section contains a very simple example of SPI usage. The + 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 RETURNING *', 1); +INFO: EXECQ: 2 -- 0 + 2, then execution was stopped by count + execq +------- + 1 +(1 row) + +=> SELECT execq('SELECT * FROM a', 10); +INFO: EXECQ: 0 +INFO: EXECQ: 1 +INFO: EXECQ: 2 + + execq +------- + 3 -- 10 is the max value only, 3 is the real number of rows +(1 row) + +=> SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1); + execq +------- + 3 -- all rows processed; count does not stop it, because nothing is returned +(1 row) + +=> SELECT * FROM a; + x +---- + 0 + 1 + 2 + 10 + 11 + 12 +(6 rows) + +=> DELETE FROM a; +DELETE 6 +=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); +INSERT 0 1 +=> SELECT * FROM a; + x +--- + 1 -- 0 (no rows in a) + 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 -- 1 (there was one row in a) + 1 +(2 rows) + +-- This demonstrates the data changes visibility rule. +-- execq is called twice and sees different numbers of rows each time: + +=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a; +INFO: EXECQ: 1 -- results from first execq +INFO: EXECQ: 2 +INFO: EXECQ: 1 -- results from second execq +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) + + + + -- cgit v1.2.3