SPI The Server Programming Interface (SPI) gives writers of user-defined C functions the ability to run SQL commands inside their functions. SPI is a set of interface functions to simplify access to the parser, planner, optimizer, and executor. SPI also does some memory management. The available procedural languages provide various means to execute SQL commands from procedures. Some of these are based on or modelled after SPI, so this documentation might be of use for users of those languages as well. To avoid misunderstanding we'll use the term function when we speak of SPI interface functions and procedure for a user-defined C-function that is using SPI. Note that if during the execution of a procedure the transaction is aborted because of an error in a command, then control will not be returned to your procedure. Rather, all work will be rolled back and the server will wait for the next command from the client. A related restriction is the inability to execute BEGIN, COMMIT, and ROLLBACK (transaction control statements) inside a procedure. Both of these restrictions will probably be changed in the future. 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 connect a procedure to the SPI manager SPI_connect int SPI_connect(void) SPI_connect opens a connection from a procedure invocation to the SPI manager. You must call this function if you want to execute commands through SPI. Some utility SPI functions may be called from unconnected procedures. If your procedure is already connected, SPI_connect will return the error code SPI_ERROR_CONNECT. This could happen if a procedure that has called SPI_connect directly calls another procedure that calls SPI_connect. While recursive calls to the SPI manager are permitted when an SQL command called through SPI invokes another function that uses SPI, directly nested calls to SPI_connect and SPI_finish are forbidden. SPI_OK_CONNECT on success SPI_ERROR_CONNECT on error SPI_finish SPI_finish disconnect a procedure from the SPI manager SPI_finish 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 procedure's current invocation. You do not need to worry about making this happen, however, if you abort the transaction via elog(ERROR). In that case SPI will clean itself up automatically. If SPI_finish is called without having a valid connection, it will return SPI_ERROR_UNCONNECTED. There is no fundamental problem with this; it means that the SPI manager has nothing to do. SPI_OK_FINISH if properly disconnected SPI_ERROR_UNCONNECTED if called from an unconnected procedure SPI_exec SPI_exec execute a command SPI_exec int SPI_exec(const char * command, int count) SPI_exec executes the specified SQL command for count rows. This function should only be called from a connected procedure. If count is zero then it executes the command for all rows that it applies to. If count is greater than 0, then the number of rows for which the command will be executed is restricted (much like a LIMIT clause). For example, SPI_exec("INSERT INTO tab SELECT * FROM tab", 5); will allow at most 5 rows to be inserted into the table. You may pass multiple commands in one string, and the command may be rewritten by rules. SPI_exec returns the result for the command executed last. The actual number of rows for which the (last) command was executed is returned in the global variable SPI_processed (unless the return value of the function is SPI_OK_UTILITY). If the return value of the function is SPI_OK_SELECT then you may the use global pointer SPITupleTable *SPI_tuptable to access the result rows. The structure SPITupleTable is defined thus: typedef struct { MemoryContext tuptabcxt; /* memory context of result table */ uint32 alloced; /* number of alloced vals */ uint32 free; /* number of free vals */ TupleDesc tupdesc; /* row descriptor */ HeapTuple *vals; /* rows */ } SPITupleTable; vals is an array of pointers to rows. (The number of valid entries is given by SPI_processed). tupdesc is a row descriptor which you may pass to SPI functions dealing with rows. tuptabcxt, alloced, and free are internal fields not intended for use by SPI callers. SPI_finish frees all SPITupleTables allocated during the current procedure. 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 int count maximum number of rows to process or return 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_DELETE if a DELETE was executed SPI_OK_INSERT if an INSERT was executed SPI_OK_UPDATE if an UPDATE was executed SPI_OK_UTILITY if a utility command (e.g., CREATE TABLE) was executed 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_CURSOR if DECLARE, CLOSE, or FETCH was attempted SPI_ERROR_TRANSACTION if BEGIN, COMMIT, or ROLLBACK was attempted SPI_ERROR_OPUNKNOWN if the command type is unknown (shouldn't happen) SPI_ERROR_UNCONNECTED if called from an unconnected procedure The functions SPI_exec, SPI_execp, and SPI_prepare change both SPI_processed and SPI_tuptable (just the pointer, not the contents of the structure). Save these two global variables into local procedure variables if you need to access the result of SPI_exec or SPI_execp across later calls. SPI_prepare SPI_prepare prepare a plan for a command, without executing it yet SPI_prepare void * SPI_prepare(const char * command, int nargs, Oid * argtypes) SPI_prepare creates and returns an execution plan for the specified command but doesn't execute the command. This function should only be called from a connected procedure. When the same or a similar command is to be executed repeatedly, it may be advantageous to perform the planning only once. SPI_prepare converts a command string into an execution plan that can be executed repeatedly using SPI_execp. 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_execp is called. This allows the prepared command to be used over a wider range of situations than would be possible without parameters. The plan returned by SPI_prepare can be used only in the current invocation of the procedure since SPI_finish frees memory allocated for a plan. But a plan can be saved for longer using the function SPI_saveplan. 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 non-null pointer to an execution plan. On error, NULL will be returned. In both cases, SPI_result will be set analogous to the value returned by SPI_exec, 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. There is a disadvantage to using parameters: since the planner does not know the values that will be supplied for the parameters, it may make worse planning choices than it would make for a normal command with all constants visible. SPI_execp SPI_execp executes a plan prepared by SPI_prepare SPI_execp int SPI_execp(void * plan, Datum * values, const char * nulls, int count) SPI_execp executes a plan prepared by SPI_prepare. tcount has the same interpretation as in SPI_exec. void * plan execution plan (returned by SPI_prepare) Datum *values actual parameter values const char * nulls An array describing which parameters are null. n indicates a null value (entry in values will be ignored); a space indicates a nonnull value (entry in values is valid). If nulls is NULL then SPI_execp assumes that no parameters are null. int count number of row for which plan is to be executed The return value is the same as for SPI_exec or one of the following: SPI_ERROR_ARGUMENT if plan is NULL 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_exec if successful. If one of the objects (a table, function, etc.) referenced by the prepared plan is dropped during the session then the result of SPI_execp for this plan will be unpredictable. SPI_cursor_open SPI_cursor_open set up a cursor using a plan created with SPI_prepare SPI_cursor_open Portal SPI_cursor_open(const char * name, void * plan, Datum * values, const char * nulls) SPI_cursor_open sets up a cursor (internally, a portal) that will execute a plan prepared by SPI_prepare. Using a cursor instead of executing the plan directly has two benefits. First, the result rows can be retrieved a few at a time, avoiding memory overrun for queries that return many rows. Second, a portal can outlive the current procedure (it can, in fact, live to the end of the current transaction). Returning the portal name to the procedure's caller provides a way of returning a row set as result. const char * name name for portal, or NULL to let the system select a name void * plan execution plan (returned by SPI_prepare) Datum * values actual parameter values const char *nulls An array describing which parameters are null values. n indicates a null value (entry in values will be ignored); a space indicates a nonnull value (entry in values is valid). If nulls is NULL then SPI_cursor_open assumes that no parameters are null. pointer to portal containing the cursor, or NULL on error SPI_cursor_find SPI_cursor_find find an existing cursor by name SPI_cursor_find 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 fetch some rows from a cursor SPI_cursor_fetch void SPI_cursor_fetch(Portal portal, bool forward, int count) SPI_cursor_fetch fetches some rows from a cursor. This is equivalent to the SQL command FETCH. Portal portal portal containing the cursor bool forward true for fetch forward, false for fetch backward int count maximum number of rows to fetch SPI_processed and SPI_tuptable are set as in SPI_exec if successful. SPI_cursor_move SPI_cursor_move move a cursor SPI_cursor_move void SPI_cursor_move(Portal portal, bool forward, int count) SPI_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 bool forward true for move forward, false for move backward int count maximum number of rows to move SPI_cursor_close SPI_cursor_close close a cursor SPI_cursor_close 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_saveplan SPI_saveplan save a plan SPI_saveplan void * SPI_saveplan(void * plan) SPI_saveplan saves a passed plan (prepared by SPI_prepare) in memory protected from freeing by SPI_finish and by the transaction manager and returns a pointer to the saved plan. This gives you the ability to reuse prepared plans in the subsequent invocations of your procedure in the current session. You may save the pointer returned in a local variable. Always check if this pointer is NULL or not either when preparing a plan or using an already prepared plan in SPI_execp. void * plan the plan to be saved Pointer to the saved plan; NULL if unsuccessful. On error, SPI_result is set thus: SPI_ERROR_ARGUMENT if plan is NULL SPI_ERROR_UNCONNECTED if called from an unconnected procedure If one of the objects (a table, function, etc.) referenced by the prepared plan is dropped during the session then the results of SPI_execp for this plan will be unpredictable. The functions described here provide an interface for extracting information from result sets returned by SPI_exec and other SPI functions. All functions described in this section may be used by both connected and unconnected procedures. SPI_fname SPI_fname determine the column name for the specified column number SPI_fname char * SPI_fname(TupleDesc rowdesc, int colnumber) SPI_fname returns 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 determine the column number for the specified column name SPI_fnumber 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., oid) then the appropriate negative column number will be returned. The caller should be careful to test the return value for exact equality to SPI_ERROR_NOATTRIBUTE to detect an error; testing the result for less than or equal to 0 is not correct unless system columns should be rejected. TupleDesc rowdesc input row description const char * colname column name Column number (count starts at 1), or SPI_ERROR_NOATTRIBUTE if the named column was not found. SPI_getvalue SPI_getvalue return the string value of the specified column SPI_getvalue 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 no output function available (SPI_result is set to SPI_ERROR_NOOUTFUNC). SPI_getbinval SPI_getbinval return the binary value of the specified column SPI_getbinval 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 rownumber 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 return the data type name of the specified column SPI_gettype char * SPI_gettype(TupleDesc rowdesc, int colnumber) SPI_gettype returns 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 return the data type OID of the specified column SPI_gettypeid 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 return the name of the specified relation SPI_getrelname char * SPI_getrelname(Relation rel) SPI_getrelname returns 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. PostgreSQL allocates memory within memory contextsmemory contextin SPI, which provide a convenient method of managing allocations made in many different places that need to live for differing amounts of time. Destroying a context releases all the memory that was allocated in it. Thus, it is not necessary to keep track of individual objects to avoid memory leaks; instead only a relatively small number of contexts have to be managed. palloc and related functions allocate memory from the current context. SPI_connect creates a new memory context and makes it current. SPI_finish restores the previous current memory context and destroys the context created by SPI_connect. These actions ensure that transient memory allocations made inside your procedure are reclaimed at procedure exit, avoiding memory leakage. However, if your procedure needs to return an object in allocated memory (such as a value of a pass-by-reference data type), you cannot allocate that memory using palloc, at least not while you are connected to SPI. If you try, the object will be deallocated by SPI_finish, and your procedure will not work reliably. To solve this problem, use SPI_palloc to allocate memory for your return object. SPI_palloc allocates memory in the upper executor context, that is, the memory context that was current when SPI_connect was called, which is precisely the right context for return a value from your procedure. If SPI_palloc is called while the procedure is not connected to SPI, then it acts the same as a normal palloc. Before a procedure connects to the SPI manager, the current memory context is the upper executor context, so all allocations made by the procedure via palloc or by SPI utility functions are made in this context. When SPI_connect is called, the private context of the procedure, which is created by SPI_connect, is made the current context. All allocations made by palloc, repalloc, or SPI utility functions (except for SPI_copytuple, SPI_copytupledesc, SPI_copytupleintoslot, SPI_modifytuple, and SPI_palloc) are made in this context. When a procedure disconnects from the SPI manager (via SPI_finish) the current context is restored to the upper executor context, and all allocations made in the procedure memory context are freed and cannot be used any more. All functions described in this section may be used by both connected and unconnected procedures. In an unconnected procedure, they act the same as the underlying ordinary server functions (palloc, etc.). SPI_palloc SPI_palloc allocate memory in the upper executor context SPI_palloc void * SPI_palloc(Size size) SPI_palloc allocates memory in the upper executor context. Size size size in bytes of storage to allocate pointer to new storage space of the specified size SPI_repalloc SPI_repalloc reallocate memory in the upper executor context SPI_repalloc 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 free memory in the upper executor context SPI_pfree 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 make a copy of a row in the upper executor context SPI_copytuple HeapTuple SPI_copytuple(HeapTuple row) SPI_copytuple makes a copy of a row in the upper executor context. HeapTuple row row to be copied the copied row; NULL only if tuple is NULL SPI_copytupledesc SPI_copytupledesc make a copy of a row descriptor in the upper executor context SPI_copytupledesc TupleDesc SPI_copytupledesc(TupleDesc tupdesc) SPI_copytupledesc makes a copy of a row descriptor in the upper executor context. TupleDesc tupdesc row descriptor to be copied the copied row descriptor; NULL only if tupdesc is NULL SPI_copytupleintoslot SPI_copytupleintoslot make a copy of a row and descriptor in the upper executor context SPI_copytupleintoslot TupleTableSlot * SPI_copytupleintoslot(HeapTuple row, TupleDesc rowdesc) SPI_copytupleintoslot makes a copy of a row in the upper executor context, returning it in the form of a filled-in TupleTableSlot structure. HeapTuple row row to be copied TupleDesc rowdesc row descriptor to be copied TupleTableSlot containing the copied row and descriptor; NULL only if row or rowdesc are NULL SPI_modifytuple SPI_modifytuple create a row by replacing selected fields of a given row SPI_modifytuple HeapTuple SPI_modifytuple(Relation rel, HeapTuple row, ncols, 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. 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 column numbers in the array colnum int * colnum array of the numbers of the columns that are to be changed (count starts at 1) Datum * values new values for the specified columns const char * Nulls which new values are null, if any (see SPI_execp for the format) new row with modifications, allocated in the upper executor context; NULL only if row is NULL On error, SPI_result is set as follows: SPI_ERROR_ARGUMENT if rel is NULL, or if row is NULL, or if ncols is less than or equal to 0, or if colnum is NULL, or if values is NULL. SPI_ERROR_NOATTRIBUTE if colnum contains an invalid column number (less than or equal to 0 or greater than the number of column in row) SPI_freetuple SPI_freetuple frees a row allocated in the upper executor context SPI_freetuple 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 free a row set created by SPI_exec or a similar function SPI_freetuptable void SPI_freetuptable(SPITupleTable * tuptable) SPI_freetuptable frees a row set created by a prior SPI command execution function, such as SPI_exec. Therefore, this function is usually called with the global variable SPI_tupletable as argument. This function is useful if a SPI procedure needs to execute multiple commands and does not want to keep the results of earlier commands around until it ends. Note that any unfreed row sets will be freed anyway at SPI_finish. SPITupleTable * tuptable pointer to row set to free SPI_freeplan SPI_freeplan free a previously saved plan SPI_freeplan int SPI_freeplan(void *plan) SPI_freeplan releases a command execution plan previously returned by SPI_prepare or saved by SPI_saveplan. void * plan pointer to plan to free SPI_ERROR_ARGUMENT if plan is NULL. The following two 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 (or by function called by the command, including trigger functions) are invisible to the command. For example, in command 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. The next section contains an example that illustrates the application of these rules. This section contains a very simple example of SPI usage. The procedure execq takes an SQL command as its first argument and a row count as its second, executes the command using SPI_exec and returns the number of rows that were processed by the command. You can find more complex examples for SPI in the source tree in src/test/regress/regress.c and in contrib/spi. #include "executor/spi.h" int execq(text *sql, int cnt); int execq(text *sql, int cnt) { char *command; int ret; int proc; /* Convert given text object to a C string */ command = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(sql))); SPI_connect(); ret = SPI_exec(command, cnt); proc = SPI_processed; /* * If this is a SELECT and some rows were fetched, * then the rows are printed via elog(INFO). */ if (ret == SPI_OK_SELECT && SPI_processed > 0) { TupleDesc tupdesc = SPI_tuptable->tupdesc; SPITupleTable *tuptable = SPI_tuptable; char buf[8192]; int i, j; for (j = 0; j < proc; j++) { HeapTuple tuple = tuptable->vals[j]; for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++) snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s", SPI_getvalue(tuple, tupdesc, i), (i == tupdesc->natts) ? " " : " |"); elog (INFO, "EXECQ: %s", buf); } } SPI_finish(); pfree(command); return (proc); } (This function uses call convention version 0, to make the example easier to understand. In real applications you should user the new version 1 interface.) This is how you declare the function after having compiled it into a shared library: CREATE FUNCTION execq(text, integer) RETURNS integer AS 'filename' LANGUAGE C; Here is a sample session: => SELECT execq('CREATE TABLE a (x integer)', 0); execq ------- 0 (1 row) => INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0)); INSERT 167631 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 167712 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: 0 INSERT 167713 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