SPI_prepare

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 procedure, 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

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.

This function should only be called from a connected procedure.

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.