2 doc/src/sgml/ref/prepare.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-PREPARE">
8 <refentrytitle>PREPARE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>PREPARE</refname>
15 <refpurpose>prepare a statement for execution</refpurpose>
18 <indexterm zone="sql-prepare">
19 <primary>PREPARE</primary>
22 <indexterm zone="sql-prepare">
23 <primary>prepared statements</primary>
24 <secondary>creating</secondary>
29 PREPARE <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">data_type</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>
34 <title>Description</title>
37 <command>PREPARE</command> creates a prepared statement. A prepared
38 statement is a server-side object that can be used to optimize
39 performance. When the <command>PREPARE</command> statement is
40 executed, the specified statement is parsed, analyzed, and rewritten.
41 When an <command>EXECUTE</command> command is subsequently
42 issued, the prepared statement is planned and executed. This division
43 of labor avoids repetitive parse analysis work, while allowing
44 the execution plan to depend on the specific parameter values supplied.
48 Prepared statements can take parameters: values that are
49 substituted into the statement when it is executed. When creating
50 the prepared statement, refer to parameters by position, using
51 <literal>$1</>, <literal>$2</>, etc. A corresponding list of
52 parameter data types can optionally be specified. When a
53 parameter's data type is not specified or is declared as
54 <literal>unknown</literal>, the type is inferred from the context
55 in which the parameter is used (if possible). When executing the
56 statement, specify the actual values for these parameters in the
57 <command>EXECUTE</command> statement. Refer to <xref
58 linkend="sql-execute"> for more
59 information about that.
63 Prepared statements only last for the duration of the current
64 database session. When the session ends, the prepared statement is
65 forgotten, so it must be recreated before being used again. This
66 also means that a single prepared statement cannot be used by
67 multiple simultaneous database clients; however, each client can create
68 their own prepared statement to use. Prepared statements can be
69 manually cleaned up using the <xref linkend="sql-deallocate"> command.
73 Prepared statements have the largest performance advantage when a
74 single session is being used to execute a large number of similar
75 statements. The performance difference will be particularly
76 significant if the statements are complex to plan or rewrite, for
77 example, if the query involves a join of many tables or requires
78 the application of several rules. If the statement is relatively simple
79 to plan and rewrite but relatively expensive to execute, the
80 performance advantage of prepared statements will be less noticeable.
85 <title>Parameters</title>
89 <term><replaceable class="PARAMETER">name</replaceable></term>
92 An arbitrary name given to this particular prepared
93 statement. It must be unique within a single session and is
94 subsequently used to execute or deallocate a previously prepared
101 <term><replaceable class="PARAMETER">data_type</replaceable></term>
104 The data type of a parameter to the prepared statement. If the
105 data type of a particular parameter is unspecified or is
106 specified as <literal>unknown</literal>, it will be inferred
107 from the context in which the parameter is used. To refer to the
108 parameters in the prepared statement itself, use
109 <literal>$1</literal>, <literal>$2</literal>, etc.
115 <term><replaceable class="PARAMETER">statement</replaceable></term>
118 Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
119 <command>DELETE</>, or <command>VALUES</> statement.
130 If a prepared statement is executed enough times, the server may eventually
131 decide to save and re-use a generic plan rather than re-planning each time.
132 This will occur immediately if the prepared statement has no parameters;
133 otherwise it occurs only if the generic plan appears to be not much more
134 expensive than a plan that depends on specific parameter values.
135 Typically, a generic plan will be selected only if the query's performance
136 is estimated to be fairly insensitive to the specific parameter values
141 To examine the query plan <productname>PostgreSQL</productname> is using
142 for a prepared statement, use <xref linkend="sql-explain">.
143 If a generic plan is in use, it will contain parameter symbols
144 <literal>$<replaceable>n</></literal>, while a custom plan will have the
145 current actual parameter values substituted into it.
149 For more information on query planning and the statistics collected
150 by <productname>PostgreSQL</productname> for that purpose, see
151 the <xref linkend="sql-analyze">
156 Although the main point of a prepared statement is to avoid repeated parse
157 analysis and planning of the statement, <productname>PostgreSQL</> will
158 force re-analysis and re-planning of the statement before using it
159 whenever database objects used in the statement have undergone
160 definitional (DDL) changes since the previous use of the prepared
161 statement. Also, if the value of <xref linkend="guc-search-path"> changes
162 from one use to the next, the statement will be re-parsed using the new
163 <varname>search_path</>. (This latter behavior is new as of
164 <productname>PostgreSQL</productname> 9.3.) These rules make use of a
165 prepared statement semantically almost equivalent to re-submitting the
166 same query text over and over, but with a performance benefit if no object
167 definitions are changed, especially if the best plan remains the same
168 across uses. An example of a case where the semantic equivalence is not
169 perfect is that if the statement refers to a table by an unqualified name,
170 and then a new table of the same name is created in a schema appearing
171 earlier in the <varname>search_path</>, no automatic re-parse will occur
172 since no object used in the statement changed. However, if some other
173 change forces a re-parse, the new table will be referenced in subsequent
178 You can see all prepared statements available in the session by querying the
179 <link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link>
184 <refsect1 id="sql-prepare-examples">
185 <title id="sql-prepare-examples-title">Examples</title>
187 Create a prepared statement for an <command>INSERT</command>
188 statement, and then execute it:
190 PREPARE fooplan (int, text, bool, numeric) AS
191 INSERT INTO foo VALUES($1, $2, $3, $4);
192 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
197 Create a prepared statement for a <command>SELECT</command>
198 statement, and then execute it:
200 PREPARE usrrptplan (int) AS
201 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
203 EXECUTE usrrptplan(1, current_date);
206 Note that the data type of the second parameter is not specified,
207 so it is inferred from the context in which <literal>$2</> is used.
211 <title>Compatibility</title>
214 The SQL standard includes a <command>PREPARE</command> statement,
215 but it is only for use in embedded SQL. This version of the
216 <command>PREPARE</command> statement also uses a somewhat different
222 <title>See Also</title>
224 <simplelist type="inline">
225 <member><xref linkend="sql-deallocate"></member>
226 <member><xref linkend="sql-execute"></member>