Plan Management
SPI_prepare
SPI_prepare creates and returns an execution plan (parser+planner+optimizer) but does not execute the query.
SPI_prepare(query, nargs, argtypes) |
Inputs
- query
Query string
- nargs
Number of input parameters ($1 ... $nargs - as in SQL functions)
- argtypes
Pointer list of type OIDs to input arguments
Outputs
- void *
Pointer to an execution plan (parser+planner+optimizer)
Note
SPI_prepare should be called only from a connected procedure.
Usage
nargs is number of parameters ($1 ... $nargs - as in SQL functions), and nargs can be 0 only if there is not any $1 in query.
Execution of prepared execution plans is sometimes much faster, so this feature may be useful if the same query will be executed many times.
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. See the Section called SPI_saveplan.
If successful, a non-null pointer will be returned. Otherwise, you will 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_saveplan
SPI_saveplan stores a plan prepared by SPI_prepare in safe memory protected from freeing by SPI_finish or the transaction manager.
SPI_saveplan(plan) |
Inputs
- void *plan
Passed plan.
Outputs
- void *
Execution plan location. NULL if unsuccessful.
- SPI_result
SPI_ERROR_ARGUMENT if plan is NULL SPI_ERROR_UNCONNECTED if procedure is un-connected
Note
In the current version of Red Hat Database 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 consequent invocations of your procedure in the current session. Use SPI_execp to execute this saved plan.
Usage
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.
 | 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_execp
SPI_execp stores a plan prepared by SPI_prepare in safe memory protected from freeing by SPI_finish or the transaction manager.
SPI_execp(plan, values, nulls, tcount) |
Inputs
- void *plan
Execution plan
- Datum *values
Actual parameter values
- char *nulls
Array describing what parameters get NULLs
'n' indicates NULL allowed ' ' indicates NULL not allowed - int tcount
Number of tuples for which plan is to be executed
Outputs
- 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
Note
In the current version of Red Hat Database 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 a work around, there is the ability to reuse prepared plans in the consequent invocations of your procedure in the current session. Use SPI_execp to execute this saved plan.
Usage
If nulls is NULL, then SPI_execp assumes that all values (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. |