2 doc/src/sgml/ref/prepare.sgml
3 PostgreSQL documentation
6 <refentry id="sql-prepare">
7 <indexterm zone="sql-prepare">
8 <primary>PREPARE</primary>
11 <indexterm zone="sql-prepare">
12 <primary>prepared statements</primary>
13 <secondary>creating</secondary>
17 <refentrytitle>PREPARE</refentrytitle>
18 <manvolnum>7</manvolnum>
19 <refmiscinfo>SQL - Language Statements</refmiscinfo>
23 <refname>PREPARE</refname>
24 <refpurpose>prepare a statement for execution</refpurpose>
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>, <literal>$2</literal>, 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 first referenced (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 potentially have the largest performance advantage
74 when a 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, e.g.
77 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 first referenced. 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>, <command>INSERT</command>, <command>UPDATE</command>,
119 <command>DELETE</command>, or <command>VALUES</command> statement.
126 <refsect1 id="sql-prepare-notes">
130 A prepared statement can be executed with either a <firstterm>generic
131 plan</firstterm> or a <firstterm>custom plan</firstterm>. A generic
132 plan is the same across all executions, while a custom plan is generated
133 for a specific execution using the parameter values given in that call.
134 Use of a generic plan avoids planning overhead, but in some situations
135 a custom plan will be much more efficient to execute because the planner
136 can make use of knowledge of the parameter values. (Of course, if the
137 prepared statement has no parameters, then this is moot and a generic
138 plan is always used.)
142 By default (that is, when <xref linkend="guc-plan-cache_mode"/> is set
143 to <literal>auto</literal>), the server will automatically choose
144 whether to use a generic or custom plan for a prepared statement that
145 has parameters. The current rule for this is that the first five
146 executions are done with custom plans and the average estimated cost of
147 those plans is calculated. Then a generic plan is created and its
148 estimated cost is compared to the average custom-plan cost. Subsequent
149 executions use the generic plan if its cost is not so much higher than
150 the average custom-plan cost as to make repeated replanning seem
155 This heuristic can be overridden, forcing the server to use either
156 generic or custom plans, by setting <varname>plan_cache_mode</varname>
157 to <literal>force_generic_plan</literal>
158 or <literal>force_custom_plan</literal> respectively.
159 This setting is primarily useful if the generic plan's cost estimate
160 is badly off for some reason, allowing it to be chosen even though
161 its actual cost is much more than that of a custom plan.
165 To examine the query plan <productname>PostgreSQL</productname> is using
166 for a prepared statement, use <xref linkend="sql-explain"/>, for example
168 EXPLAIN EXECUTE <replaceable>stmt_name</replaceable>(<replaceable>parameter_values</replaceable>);
170 If a generic plan is in use, it will contain parameter symbols
171 <literal>$<replaceable>n</replaceable></literal>, while a custom plan
172 will have the supplied parameter values substituted into it.
176 For more information on query planning and the statistics collected
177 by <productname>PostgreSQL</productname> for that purpose, see
178 the <xref linkend="sql-analyze"/>
183 Although the main point of a prepared statement is to avoid repeated parse
184 analysis and planning of the statement, <productname>PostgreSQL</productname> will
185 force re-analysis and re-planning of the statement before using it
186 whenever database objects used in the statement have undergone
187 definitional (DDL) changes since the previous use of the prepared
188 statement. Also, if the value of <xref linkend="guc-search-path"/> changes
189 from one use to the next, the statement will be re-parsed using the new
190 <varname>search_path</varname>. (This latter behavior is new as of
191 <productname>PostgreSQL</productname> 9.3.) These rules make use of a
192 prepared statement semantically almost equivalent to re-submitting the
193 same query text over and over, but with a performance benefit if no object
194 definitions are changed, especially if the best plan remains the same
195 across uses. An example of a case where the semantic equivalence is not
196 perfect is that if the statement refers to a table by an unqualified name,
197 and then a new table of the same name is created in a schema appearing
198 earlier in the <varname>search_path</varname>, no automatic re-parse will occur
199 since no object used in the statement changed. However, if some other
200 change forces a re-parse, the new table will be referenced in subsequent
205 You can see all prepared statements available in the session by querying the
206 <link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link>
211 <refsect1 id="sql-prepare-examples">
212 <title id="sql-prepare-examples-title">Examples</title>
214 Create a prepared statement for an <command>INSERT</command>
215 statement, and then execute it:
217 PREPARE fooplan (int, text, bool, numeric) AS
218 INSERT INTO foo VALUES($1, $2, $3, $4);
219 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
224 Create a prepared statement for a <command>SELECT</command>
225 statement, and then execute it:
227 PREPARE usrrptplan (int) AS
228 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
230 EXECUTE usrrptplan(1, current_date);
233 In this example, the data type of the second parameter is not specified,
234 so it is inferred from the context in which <literal>$2</literal> is used.
238 <title>Compatibility</title>
241 The SQL standard includes a <command>PREPARE</command> statement,
242 but it is only for use in embedded SQL. This version of the
243 <command>PREPARE</command> statement also uses a somewhat different
249 <title>See Also</title>
251 <simplelist type="inline">
252 <member><xref linkend="sql-deallocate"/></member>
253 <member><xref linkend="sql-execute"/></member>