Vadim Mikheev Transcribed 1998-01-16 The Server Programming Interface (SPI) gives users the ability to run SQL queries inside user-defined C functions. The available Procedural Languages (PL) give an alternate means to build functions that can execute queries. In fact, SPI is just a set of native interface functions to simplify access to the Parser, Planner, Optimizer and Executor. SPI also does some memory management. To avoid misunderstanding we'll use function to mean SPI interface functions and procedure for user-defined C-functions using SPI. Procedures which use SPI are called by the Executor. The SPI calls recursively invoke the Executor in turn to run queries. When the Executor is invoked recursively, it may itself call procedures which may make SPI calls. Note that if during execution of a query from a procedure the transaction is aborted, 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. This will probably be changed in future versions. A related restriction is the inability to execute BEGIN, END and ABORT (transaction control statements). This will also be changed in the future. If successful, SPI functions return a non-negative result (either via a returned integer value or in SPI_result global variable, as described below). On error, a negative or NULL result will be returned. SPI_connect SPI - Connection Management SPI_connect Connects your procedure to the SPI manager. SPIconnecting SPI_connect 1997-12-24 int SPI_connect(void) 1997-12-24 None 1997-12-24 int Return status SPI_OK_CONNECT if connected SPI_ERROR_CONNECT if not connected 1997-12-24 SPI_connect opens a connection from a procedure invocation to the SPI manager. You must call this function if you will need to execute queries. Some utility SPI functions may be called from un-connected procedures. If your procedure is already connected, SPI_connect will return an SPI_ERROR_CONNECT error. Note that this may happen if a procedure which has called SPI_connect directly calls another procedure which itself calls SPI_connect. While recursive calls to the SPI manager are permitted when an SPI query invokes another function which uses SPI, directly nested calls to SPI_connect and SPI_finish are forbidden. SPI_connect performs the following: Initializes the SPI internal structures for query execution and memory management. SPI_finish SPI - Connection Management SPI_finish Disconnects your procedure from the SPI manager. SPIdisconnecting SPI_finish 1997-12-24 SPI_finish(void) 1997-12-24 None 1997-12-24 int SPI_OK_FINISH if properly disconnected SPI_ERROR_UNCONNECTED if called from an un-connected procedure 1997-12-24 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 may get the error return SPI_ERROR_UNCONNECTED if SPI_finish is called without having a current valid connection. There is no fundamental problem with this; it means that nothing was done by the SPI manager. SPI_finish must be called as a final step by a connected procedure, or you may get unpredictable results! However, you do not need to worry about making this happen if the transaction is aborted via elog(ERROR). In that case SPI will clean itself up. SPI_finish performs the following: Disconnects your procedure from the SPI manager and frees all memory allocations made by your procedure via palloc since the SPI_connect. These allocations can't be used any more! See Memory management. SPI_exec SPI - Connection Management SPI_exec Creates an execution plan (parser+planner+optimizer) and executes a query. SPIexecuting SPI_exec 1997-12-24 SPI_exec(query, tcount) 1997-12-24 char *query String containing query plan int tcount Maximum number of tuples to return 1997-12-24 int SPI_ERROR_UNCONNECTED if called from an un-connected procedure SPI_ERROR_ARGUMENT if query is NULL or tcount < 0. SPI_ERROR_UNCONNECTED if procedure is unconnected. SPI_ERROR_COPY if COPY TO/FROM stdin. SPI_ERROR_CURSOR if DECLARE/CLOSE CURSOR, FETCH. SPI_ERROR_TRANSACTION if BEGIN/ABORT/END. SPI_ERROR_OPUNKNOWN if type of query is unknown (this shouldn't occur). If execution of your query was successful then one of the following (non-negative) values will be returned: SPI_OK_UTILITY if some utility (e.g. CREATE TABLE ...) was executed SPI_OK_SELECT if SELECT (but not SELECT ... INTO!) was executed SPI_OK_SELINTO if SELECT ... INTO was executed SPI_OK_INSERT if INSERT (or INSERT ... SELECT) was executed SPI_OK_DELETE if DELETE was executed SPI_OK_UPDATE if UPDATE was executed 1997-12-24 SPI_exec creates an execution plan (parser+planner+optimizer) and executes the query for tcount tuples. This should only be called from a connected procedure. If tcount is zero then it executes the query for all tuples returned by the query scan. Using tcount > 0 you may restrict the number of tuples for which the query will be executed (much like a LIMIT clause). For example, SPI_exec ("INSERT INTO tab SELECT * FROM tab", 5); will allow at most 5 tuples to be inserted into table. If execution of your query was successful then a non-negative value will be returned. You may pass multiple queries in one string or query string may be re-written by RULEs. SPI_exec returns the result for the last query executed. The actual number of tuples for which the (last) query was executed is returned in the global variable SPI_processed (if not SPI_OK_UTILITY). If SPI_OK_SELECT is returned then you may use global pointer SPITupleTable *SPI_tuptable to access the result tuples. SPI_exec may return one of the following (negative) values: SPI_ERROR_ARGUMENT if query is NULL or tcount < 0. SPI_ERROR_UNCONNECTED if procedure is unconnected. SPI_ERROR_COPY if COPY TO/FROM stdin. SPI_ERROR_CURSOR if DECLARE/CLOSE CURSOR, FETCH. SPI_ERROR_TRANSACTION if BEGIN/ABORT/END. SPI_ERROR_OPUNKNOWN if type of query is unknown (this shouldn't occur). If SPI_OK_SELECT is returned then you may use the global pointer SPITupleTable *SPI_tuptable to access the selected tuples. Structure SPITupleTable is defined in spi.h: typedef struct { MemoryContext tuptabcxt; /* memory context of result table */ uint32 alloced; /* # of alloced vals */ uint32 free; /* # of free vals */ TupleDesc tupdesc; /* tuple descriptor */ HeapTuple *vals; /* tuples */ } SPITupleTable; vals is an array of pointers to tuples (the number of useful entries is given by SPI_processed). tupdesc is a tuple descriptor which you may pass to SPI functions dealing with tuples. tuptabcxt, alloced, and free are internal fields not intended for use by SPI callers. 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 one SPI_exec or SPI_execp across later calls. 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. SPI_prepare SPI - Plan Preparation SPI_prepare Prepares a plan for a query, without executing it yet SPIconnecting SPI_prepare 1997-12-24 SPI_prepare(query, nargs, argtypes) 1997-12-24 query Query string nargs Number of input parameters ($1 ... $nargs - as in SQL-functions) argtypes Pointer to array of type OIDs for input parameter types 1997-12-24 void * Pointer to an execution plan (parser+planner+optimizer) 1997-12-24 SPI_prepare creates and returns an execution plan (parser+planner+optimizer) but doesn't execute the query. Should only be called from a connected procedure. When the same or similar query is to be executed repeatedly, it may be advantageous to perform query planning only once. SPI_prepare converts a query string into an execution plan that can be passed repeatedly to SPI_execp. A prepared query can be generalized by writing parameters ($1, $2, etc) in place of what would be constants in a normal query. The values of the parameters are then specified when SPI_execp is called. This allows the prepared query to be used over a wider range of situations than would be possible without parameters. However, there is a disadvantage: since the planner does not know the values that will be supplied for the parameters, it may make worse query planning choices than it would make for a simple query with all constants visible. If the query uses parameters, their number and data types must be specified in the call to SPI_prepare. The plan returned by SPI_prepare may be used only in current invocation of the procedure since SPI_finish frees memory allocated for a plan. But see SPI_saveplan to save a plan for longer. If successful, a non-null pointer will be returned. Otherwise, you'll get a NULL plan. In both cases SPI_result will be set like the value returned by SPI_exec, except that it is set to SPI_ERROR_ARGUMENT if query is NULL or nargs < 0 or nargs > 0 && argtypes is NULL. SPI_execp SPI - Plan Execution SPI_execp Executes a plan from SPI_prepare SPIconnecting SPI_execp 1997-12-24 SPI_execp(plan, values, nulls, tcount) 1997-12-24 void *plan Execution plan Datum *values Actual parameter values char *nulls Array describing which parameters are NULLs n indicates NULL (values[] entry ignored) space indicates not NULL (values[] entry is valid) int tcount Number of tuples for which plan is to be executed 1997-12-24 int Returns the same value as SPI_exec as well as SPI_ERROR_ARGUMENT if plan is NULL or tcount < 0 SPI_ERROR_PARAM if values is NULL and plan was prepared with some parameters. SPI_tuptable initialized as in SPI_exec if successful SPI_processed initialized as in SPI_exec if successful 1997-12-24 SPI_execp executes a plan prepared by SPI_prepare. tcount has the same interpretation as in SPI_exec. If nulls is NULL then SPI_execp assumes that all parameters (if any) are NOT NULL. If one of the objects (a relation, function, etc.) referenced by the prepared plan is dropped during your session (by your backend or another process) then the results of SPI_execp for this plan will be unpredictable. SPI_cursor_open SPI - Cursor Support SPI_cursor_open Sets up a cursor using a plan created with SPI_prepare SPIcursors SPI_cursor_open 2001-11-14 SPI_cursor_open(name, plan, values, nulls) 2001-11-14 char *name Name for portal, or NULL to let the system select a name void *plan Execution plan Datum *values Actual parameter values char *nulls Array describing which parameters are NULLs n indicates NULL (values[] entry ignored) space indicates not NULL (values[] entry is valid) 2001-11-14 Portal Pointer to Portal containing cursor, or NULL on error 2001-11-14 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 rowset result. If nulls is NULL then SPI_cursor_open assumes that all parameters (if any) are NOT NULL. SPI_cursor_find SPI - Cursor Support SPI_cursor_find Finds an existing cursor (Portal) by name SPIcursors SPI_cursor_find 2001-11-14 SPI_cursor_find(name) 2001-11-14 char *name Name of portal 2001-11-14 Portal Pointer to Portal with given name, or NULL if not found 2001-11-14 SPI_cursor_find finds a pre-existing Portal by name. This is primarily useful to resolve a cursor name returned as text by some other function. SPI_cursor_fetch SPI - Cursor Support SPI_cursor_fetch Fetches some rows from a cursor SPIcursors SPI_cursor_fetch 2001-11-14 SPI_cursor_fetch(portal, forward, count) 2001-11-14 Portal portal Portal containing cursor bool forward True for fetch forward, false for fetch backward int count Maximum number of rows to fetch 2001-11-14 SPI_tuptable initialized as in SPI_exec if successful SPI_processed initialized as in SPI_exec if successful 2001-11-14 SPI_cursor_fetch fetches some (more) rows from a cursor. This is equivalent to the SQL command FETCH. SPI_cursor_move SPI - Cursor Support SPI_cursor_move Moves a cursor SPIcursors SPI_cursor_move 2001-11-14 SPI_cursor_move(portal, forward, count) 2001-11-14 Portal portal Portal containing cursor bool forward True for move forward, false for move backward int count Maximum number of rows to move 2001-11-14 None 2001-11-14 SPI_cursor_move skips over some number of rows in a cursor. This is equivalent to the SQL command MOVE. SPI_cursor_close SPI - Cursor Support SPI_cursor_close Closes a cursor SPIcursors SPI_cursor_close 2001-11-14 SPI_cursor_close(portal) 2001-11-14 Portal portal Portal containing cursor 2001-11-14 None 2001-11-14 SPI_cursor_close closes a previously created cursor and releases its Portal storage. All open cursors are closed implicitly at transaction end. SPI_cursor_close need only be invoked if it is desirable to release resources sooner. SPI_saveplan SPI - Plan Storage SPI_saveplan Saves a passed plan SPIconnecting SPI_saveplan 1997-12-24 SPI_saveplan(plan) 1997-12-24 void *query Passed plan 1997-12-24 void * Execution plan location. NULL if unsuccessful. SPI_result SPI_ERROR_ARGUMENT if plan is NULL SPI_ERROR_UNCONNECTED if procedure is un-connected 1997-12-24 SPI_saveplan stores a plan prepared by SPI_prepare in safe memory protected from freeing by SPI_finish or the transaction manager. In the current version of PostgreSQL there is no ability to store prepared plans in the system catalog and fetch them from there for execution. This will be implemented in future versions. As an alternative, there is the ability to reuse prepared plans in the subsequent invocations of your procedure in the current session. Use SPI_execp to execute this saved 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. 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 (see below). If one of the objects (a relation, function, etc.) referenced by the prepared plan is dropped during your session (by your backend or another process) then the results of SPI_execp for this plan will be unpredictable. The functions described here provide convenient interfaces for extracting information from tuple sets returned by SPI_exec and other SPI interface functions. All functions described in this section may be used by both connected and unconnected procedures. SPI_fnumber SPI - Tuple Information SPI_fnumber Finds the attribute number for specified attribute name SPIdecoding tuples SPI_fnumber 1997-12-24 SPI_fnumber(tupdesc, fname) 1997-12-24 TupleDesc tupdesc Input tuple description char * fname Field name 1997-12-24 int Attribute number Valid one-based index number of attribute SPI_ERROR_NOATTRIBUTE if the named attribute is not found 1997-12-24 SPI_fnumber returns the attribute number for the attribute with name in fname. Attribute numbers are 1 based. If the given fname refers to a system attribute (eg, oid) then the appropriate negative attribute number will be returned. The caller should be careful to test for exact equality to SPI_ERROR_NOATTRIBUTE to detect error; testing for result <= 0 is not correct unless system attributes should be rejected. SPI_fname SPI - Tuple Information SPI_fname Finds the attribute name for the specified attribute number SPIdecoding tuples SPI_fname 1997-12-24 SPI_fname(tupdesc, fnumber) 1997-12-24 TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 char * Attribute name NULL if fnumber is out of range SPI_result set to SPI_ERROR_NOATTRIBUTE on error 1997-12-24 SPI_fname returns the attribute name for the specified attribute. Attribute numbers are 1 based. Returns a newly-allocated copy of the attribute name. (Use pfree() to release the copy when done with it.) SPI_getvalue SPI - Tuple Information SPI_getvalue Returns the string value of the specified attribute SPIdecoding tuples SPI_getvalue 1997-12-24 SPI_getvalue(tuple, tupdesc, fnumber) 1997-12-24 HeapTuple tuple Input tuple to be examined TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 char * Attribute value or NULL if attribute is NULL fnumber is out of range (SPI_result set to SPI_ERROR_NOATTRIBUTE) no output function available (SPI_result set to SPI_ERROR_NOOUTFUNC) 1997-12-24 SPI_getvalue returns an external (string) representation of the value of the specified attribute. Attribute numbers are 1 based. The result is returned as a palloc'd string. (Use pfree() to release the string when done with it.) SPI_getbinval SPI - Tuple Information SPI_getbinval Returns the binary value of the specified attribute SPIdecoding tuples SPI_getbinval 1997-12-24 SPI_getbinval(tuple, tupdesc, fnumber, isnull) 1997-12-24 HeapTuple tuple Input tuple to be examined TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 Datum Attribute binary value bool * isnull flag for null value in attribute SPI_result SPI_ERROR_NOATTRIBUTE 1997-12-24 SPI_getbinval returns the specified attribute's value in internal form (as a Datum). Attribute numbers are 1 based. Does not allocate new space for the datum. In the case of a pass-by- reference data type, the Datum will be a pointer into the given tuple. SPI_gettype SPI - Tuple Information SPI_gettype Returns the type name of the specified attribute SPIdecoding tuples SPI_gettype 1997-12-24 SPI_gettype(tupdesc, fnumber) 1997-12-24 TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 char * The type name for the specified attribute number SPI_result SPI_ERROR_NOATTRIBUTE 1997-12-24 SPI_gettype returns a copy of the type name for the specified attribute, or NULL on error. Attribute numbers are 1 based. Returns a newly-allocated copy of the type name. (Use pfree() to release the copy when done with it.) SPI_gettypeid SPI - Tuple Information SPI_gettypeid Returns the type OID of the specified attribute SPIdecoding tuples SPI_gettypeid 1997-12-24 SPI_gettypeid(tupdesc, fnumber) 1997-12-24 TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 OID The type OID for the specified attribute number SPI_result SPI_ERROR_NOATTRIBUTE 1997-12-24 SPI_gettypeid returns the type OID for the specified attribute. Attribute numbers are 1 based. SPI_getrelname SPI - Tuple Information SPI_getrelname Returns the name of the specified relation SPIdecoding tuples SPI_getrelname 1997-12-24 SPI_getrelname(rel) 1997-12-24 Relation rel Input relation 1997-12-24 char * The name of the specified relation 1997-12-24 SPI_getrelname returns the name of the specified relation. Returns a newly-allocated copy of the rel name. (Use pfree() to release the copy when done with it.) 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 --- 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 allocated memory object (such as a value of a pass-by-reference data type), you can't allocate the return object using palloc, at least not while you are connected to SPI. If you try, the object will be deallocated during SPI_finish, and your procedure will not work reliably! To solve this problem, use SPI_palloc to allocate your return object. SPI_palloc allocates space from upper Executor memory --- that is, the memory context that was current when SPI_connect was called, which is precisely the right context for return values of your procedure. If called while not connected to SPI, SPI_palloc acts the same as plain 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. After SPI_connect is called, the current context is the procedure's private context made by SPI_connect. All allocations made via palloc/repalloc or by 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 can't 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 backend functions (palloc etc). SPI_copytuple SPI - Tuple Copy SPI_copytuple Makes copy of tuple in upper Executor context SPIcopying tuples SPI_copytuple 1997-12-24 SPI_copytuple(tuple) 1997-12-24 HeapTuple tuple Input tuple to be copied 1997-12-24 HeapTuple Copied tuple non-NULL if tuple is not NULL and the copy was successful NULL only if tuple is NULL 1997-12-24 SPI_copytuple makes a copy of tuple in upper Executor context. TBD SPI_copytupledesc SPI - Tuple Descriptor Copy SPI_copytupledesc Makes copy of tuple descriptor in upper Executor context SPIcopying tuple descriptors SPI_copytupledesc 2001-08-02 SPI_copytupledesc(tupdesc) 2001-08-02 TupleDesc tupdesc Input tuple descriptor to be copied 2001-08-02 TupleDesc Copied tuple descriptor non-NULL if tupdesc is not NULL and the copy was successful NULL only if tupdesc is NULL 2001-08-02 SPI_copytupledesc makes a copy of tupdesc in upper Executor context. TBD SPI_copytupleintoslot SPI - Tuple and Descriptor Copy SPI_copytupleintoslot Makes copy of tuple and descriptor in upper Executor context SPIcopying tuples SPI_copytupleintoslot 1997-12-24 SPI_copytupleintoslot(tuple, tupdesc) 1997-12-24 HeapTuple tuple Input tuple to be copied TupleDesc tupdesc Input tuple descriptor to be copied 1997-12-24 TupleTableSlot * Tuple slot containing copied tuple and descriptor non-NULL if tuple and tupdesc are not NULL and the copy was successful NULL only if tuple or tupdesc is NULL 1997-12-24 SPI_copytupleintoslot makes a copy of tuple in upper Executor context, returning it in the form of a filled-in TupleTableSlot. TBD SPI_modifytuple SPI - Tuple Modify SPI_modifytuple Creates a tuple by replacing selected fields of a given tuple SPImodifying tuples SPI_modifytuple 1997-12-24 SPI_modifytuple(rel, tuple, nattrs, attnum, Values, Nulls) 1997-12-24 Relation rel Used only as source of tuple descriptor for tuple. (Passing a relation rather than a tuple descriptor is a misfeature.) HeapTuple tuple Input tuple to be modified int nattrs Number of attribute numbers in attnum array int * attnum Array of numbers of the attributes that are to be changed Datum * Values New values for the attributes specified char * Nulls Which new values are NULL, if any 1997-12-24 HeapTuple New tuple with modifications non-NULL if tuple is not NULL and the modify was successful NULL only if tuple is NULL SPI_result SPI_ERROR_ARGUMENT if rel is NULL or tuple is NULL or natts <= 0 or attnum is NULL or Values is NULL. SPI_ERROR_NOATTRIBUTE if there is an invalid attribute number in attnum (attnum <= 0 or > number of attributes in tuple) 1997-12-24 SPI_modifytuple creates a new tuple by substituting new values for selected attributes, copying the original tuple's attributes at other positions. The input tuple is not modified. If successful, a pointer to the new tuple is returned. The new tuple is allocated in upper Executor context. SPI_palloc SPI - Memory Management SPI_palloc Allocates memory in upper Executor context SPIallocating space SPI_palloc 1997-12-24 SPI_palloc(size) 1997-12-24 Size size Octet size of storage to allocate 1997-12-24 void * New storage space of specified size 1997-12-24 SPI_palloc allocates memory in upper Executor context. TBD SPI_repalloc SPI - Memory Management SPI_repalloc Re-allocates memory in upper Executor context SPIallocating space SPI_repalloc 1997-12-24 SPI_repalloc(pointer, size) 1997-12-24 void * pointer Pointer to existing storage Size size Octet size of storage to allocate 1997-12-24 void * New storage space of specified size with contents copied from existing area 1997-12-24 SPI_repalloc re-allocates memory in upper Executor context. This function is no longer different from plain repalloc. It's kept just for backward compatibility of existing code. SPI_pfree SPI - Memory Management SPI_pfree Frees memory in upper Executor context SPIallocating space SPI_pfree 1997-12-24 SPI_pfree(pointer) 1997-12-24 void * pointer Pointer to existing storage 1997-12-24 None 1997-12-24 SPI_pfree frees memory in upper Executor context. This function is no longer different from plain pfree. It's kept just for backward compatibility of existing code. SPI_freetuple SPI - Memory Management SPI_freetuple Frees a tuple allocated in upper Executor context SPIallocating space SPI_freetuple 1997-12-24 SPI_freetuple(pointer) 1997-12-24 HeapTuple pointer Pointer to allocated tuple 1997-12-24 None 1997-12-24 SPI_freetuple frees a tuple previously allocated in upper Executor context. This function is no longer different from plain heap_freetuple. It's kept just for backward compatibility of existing code. SPI_freetuptable SPI - Memory Management SPI_freetuptable Frees a tuple set created by SPI_exec or similar function SPIallocating space SPI_freetuptable 2001-11-14 SPI_freetuptable(tuptable) 2001-11-14 SPITupleTable * tuptable Pointer to tuple table 2001-11-14 None 2001-11-14 SPI_freetuptable frees a tuple set created by a prior SPI query function, such as SPI_exec. This function is useful if a SPI procedure needs to execute multiple queries and does not want to keep the results of earlier queries around until it ends. Note that any unfreed tuple sets will be freed anyway at SPI_finish. SPI_freeplan SPI - Memory Management SPI_freeplan Releases a previously saved plan SPIallocating space SPI_freeplan 2001-11-14 SPI_freeplan(plan) 2001-11-14 void *plan Passed plan 2001-11-14 int SPI_ERROR_ARGUMENT if plan is NULL 2001-11-14 SPI_freeplan releases a query plan previously returned by SPI_prepare or saved by SPI_saveplan. PostgreSQL data changes visibility rule: during a query execution, data changes made by the query itself (via SQL-function, SPI-function, triggers) are invisible to the query scan. For example, in query INSERT INTO a SELECT * FROM a tuples inserted are invisible for SELECT's scan. In effect, this duplicates the database table within itself (subject to unique index rules, of course) without recursing. Changes made by query Q are visible to queries that are started after query Q, no matter whether they are started inside Q (during the execution of Q) or after Q is done. This example of SPI usage demonstrates the visibility rule. There are more complex examples in src/test/regress/regress.c and in contrib/spi. This is a very simple example of SPI usage. The procedure execq accepts an SQL-query in its first argument and tcount in its second, executes the query using SPI_exec and returns the number of tuples for which the query executed: #include "executor/spi.h" /* this is what you need to work with SPI */ int execq(text *sql, int cnt); int execq(text *sql, int cnt) { char *query; int ret; int proc; /* Convert given TEXT object to a C string */ query = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(sql))); SPI_connect(); ret = SPI_exec(query, cnt); proc = SPI_processed; /* * If this is SELECT and some tuple(s) fetched - * returns tuples to the caller 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(query); return (proc); } Now, compile and create the function: CREATE FUNCTION execq (text, integer) RETURNS integer AS '...path_to_so' LANGUAGE C; vac=> SELECT execq('CREATE TABLE a (x INTEGER)', 0); execq ----- 0 (1 row) vac=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)',0)); INSERT 167631 1 vac=> SELECT execq('SELECT * FROM a',0); INFO: EXECQ: 0 <<< inserted by execq INFO: EXECQ: 1 <<< value returned by execq and inserted by upper INSERT execq ----- 2 (1 row) vac=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a',1); execq ----- 1 (1 row) vac=> SELECT execq('SELECT * FROM a', 10); INFO: EXECQ: 0 INFO: EXECQ: 1 INFO: EXECQ: 2 <<< 0 + 2, only one tuple inserted - as specified execq ----- 3 <<< 10 is max value only, 3 is real # of tuples (1 row) vac=> DELETE FROM a; DELETE 3 vac=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); INSERT 167712 1 vac=> SELECT * FROM a; x - 1 <<< no tuples in a (0) + 1 (1 row) vac=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); INFO: EXECQ: 0 INSERT 167713 1 vac=> SELECT * FROM a; x - 1 2 <<< there was single tuple in a + 1 (2 rows) -- This demonstrates data changes visibility rule: vac=> 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 vac=> SELECT * FROM a; x - 1 2 2 <<< 2 tuples * 1 (x in first tuple) 6 <<< 3 tuples (2 + 1 just inserted) * 2 (x in second tuple) (4 rows) ^^^^^^^^ tuples visible to execq() in different invocations