'\" t .\" Title: SPI_prepare .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.79.1 .\" Date: 2019 .\" Manual: PostgreSQL 9.6.12 Documentation .\" Source: PostgreSQL 9.6.12 .\" Language: English .\" .TH "SPI_PREPARE" "3" "2019" "PostgreSQL 9.6.12" "PostgreSQL 9.6.12 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" SPI_prepare \- prepare a statement, without executing it yet .SH "SYNOPSIS" .sp .nf SPIPlanPtr SPI_prepare(const char * \fIcommand\fR, int \fInargs\fR, Oid * \fIargtypes\fR) .fi .SH "DESCRIPTION" .PP \fBSPI_prepare\fR creates and returns a prepared statement for the specified command, but doesn\*(Aqt execute the command\&. The prepared statement can later be executed repeatedly using \fBSPI_execute_plan\fR\&. .PP 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\&. \fBSPI_prepare\fR 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\&. .PP 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 \fBSPI_execute_plan\fR is called\&. This allows the prepared command to be used over a wider range of situations than would be possible without parameters\&. .PP The statement returned by \fBSPI_prepare\fR can be used only in the current invocation of the procedure, since \fBSPI_finish\fR frees memory allocated for such a statement\&. But the statement can be saved for longer using the functions \fBSPI_keepplan\fR or \fBSPI_saveplan\fR\&. .SH "ARGUMENTS" .PP const char * \fIcommand\fR .RS 4 command string .RE .PP int \fInargs\fR .RS 4 number of input parameters ($1, $2, etc\&.) .RE .PP Oid * \fIargtypes\fR .RS 4 pointer to an array containing the OIDs of the data types of the parameters .RE .SH "RETURN VALUE" .PP \fBSPI_prepare\fR returns a non\-null pointer to an SPIPlan, which is an opaque struct representing a prepared statement\&. On error, NULL will be returned, and \fISPI_result\fR will be set to one of the same error codes used by \fBSPI_execute\fR, except that it is set to SPI_ERROR_ARGUMENT if \fIcommand\fR is NULL, or if \fInargs\fR is less than 0, or if \fInargs\fR is greater than 0 and \fIargtypes\fR is NULL\&. .SH "NOTES" .PP If no parameters are defined, a generic plan will be created at the first use of \fBSPI_execute_plan\fR, and used for all subsequent executions as well\&. If there are parameters, the first few uses of \fBSPI_execute_plan\fR will generate custom plans that are specific to the supplied parameter values\&. After enough uses of the same prepared statement, \fBSPI_execute_plan\fR 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 \fBSPI_prepare_cursor\fR, to force use of generic or custom plans respectively\&. .PP Although the main point of a prepared statement is to avoid repeated parse analysis and planning of the statement, PostgreSQL will force re\-analysis and re\-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes since the previous use of the prepared statement\&. Also, if the value of search_path changes from one use to the next, the statement will be re\-parsed using the new \fIsearch_path\fR\&. (This latter behavior is new as of PostgreSQL 9\&.3\&.) See \fBPREPARE\fR(7) for more information about the behavior of prepared statements\&. .PP This function should only be called from a connected procedure\&. .PP 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\&. .PP The name SPIPlanPtr is somewhat historical, since the data structure no longer necessarily contains an execution plan\&.