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
- const char * command
-
command string
- int nargs
-
number of input parameters ( $1 , $2 , etc.)
- Oid * argtypes
-
pointer to an array containing the OID s 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.
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.