Debian Codegraph @

doc/src/sgml/ref/prepare.sgml

raw
1 <!--
2 doc/src/sgml/ref/prepare.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-prepare">
7 <indexterm zone="sql-prepare">
8 <primary>PREPARE</primary>
9 </indexterm>
10
11 <indexterm zone="sql-prepare">
12 <primary>prepared statements</primary>
13 <secondary>creating</secondary>
14 </indexterm>
15
16 <refmeta>
17 <refentrytitle>PREPARE</refentrytitle>
18 <manvolnum>7</manvolnum>
19 <refmiscinfo>SQL - Language Statements</refmiscinfo>
20 </refmeta>
21
22 <refnamediv>
23 <refname>PREPARE</refname>
24 <refpurpose>prepare a statement for execution</refpurpose>
25 </refnamediv>
26
27 <refsynopsisdiv>
28 <synopsis>
29 PREPARE <replaceable class="parameter">name</replaceable> [ ( <replaceable class="parameter">data_type</replaceable> [, ...] ) ] AS <replaceable class="parameter">statement</replaceable>
30 </synopsis>
31 </refsynopsisdiv>
32
33 <refsect1>
34 <title>Description</title>
35
36 <para>
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.
45 </para>
46
47 <para>
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.
60 </para>
61
62 <para>
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 <link linkend="sql-deallocate"><command>DEALLOCATE</command></link> command.
70 </para>
71
72 <para>
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.
81 </para>
82 </refsect1>
83
84 <refsect1>
85 <title>Parameters</title>
86
87 <variablelist>
88 <varlistentry>
89 <term><replaceable class="parameter">name</replaceable></term>
90 <listitem>
91 <para>
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
95 statement.
96 </para>
97 </listitem>
98 </varlistentry>
99
100 <varlistentry>
101 <term><replaceable class="parameter">data_type</replaceable></term>
102 <listitem>
103 <para>
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.
110 </para>
111 </listitem>
112 </varlistentry>
113
114 <varlistentry>
115 <term><replaceable class="parameter">statement</replaceable></term>
116 <listitem>
117 <para>
118 Any <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
119 <command>DELETE</command>, <command>MERGE</command>, or <command>VALUES</command>
120 statement.
121 </para>
122 </listitem>
123 </varlistentry>
124 </variablelist>
125 </refsect1>
126
127 <refsect1 id="sql-prepare-notes">
128 <title>Notes</title>
129
130 <para>
131 A prepared statement can be executed with either a <firstterm>generic
132 plan</firstterm> or a <firstterm>custom plan</firstterm>. A generic
133 plan is the same across all executions, while a custom plan is generated
134 for a specific execution using the parameter values given in that call.
135 Use of a generic plan avoids planning overhead, but in some situations
136 a custom plan will be much more efficient to execute because the planner
137 can make use of knowledge of the parameter values. (Of course, if the
138 prepared statement has no parameters, then this is moot and a generic
139 plan is always used.)
140 </para>
141
142 <para>
143 By default (that is, when <xref linkend="guc-plan-cache-mode"/> is set
144 to <literal>auto</literal>), the server will automatically choose
145 whether to use a generic or custom plan for a prepared statement that
146 has parameters. The current rule for this is that the first five
147 executions are done with custom plans and the average estimated cost of
148 those plans is calculated. Then a generic plan is created and its
149 estimated cost is compared to the average custom-plan cost. Subsequent
150 executions use the generic plan if its cost is not so much higher than
151 the average custom-plan cost as to make repeated replanning seem
152 preferable.
153 </para>
154
155 <para>
156 This heuristic can be overridden, forcing the server to use either
157 generic or custom plans, by setting <varname>plan_cache_mode</varname>
158 to <literal>force_generic_plan</literal>
159 or <literal>force_custom_plan</literal> respectively.
160 This setting is primarily useful if the generic plan's cost estimate
161 is badly off for some reason, allowing it to be chosen even though
162 its actual cost is much more than that of a custom plan.
163 </para>
164
165 <para>
166 To examine the query plan <productname>PostgreSQL</productname> is using
167 for a prepared statement, use <link linkend="sql-explain"><command>EXPLAIN</command></link>, for example
168 <programlisting>
169 EXPLAIN EXECUTE <replaceable>name</replaceable>(<replaceable>parameter_values</replaceable>);
170 </programlisting>
171 If a generic plan is in use, it will contain parameter symbols
172 <literal>$<replaceable>n</replaceable></literal>, while a custom plan
173 will have the supplied parameter values substituted into it.
174 </para>
175
176 <para>
177 For more information on query planning and the statistics collected
178 by <productname>PostgreSQL</productname> for that purpose, see
179 the <xref linkend="sql-analyze"/>
180 documentation.
181 </para>
182
183 <para>
184 Although the main point of a prepared statement is to avoid repeated parse
185 analysis and planning of the statement, <productname>PostgreSQL</productname> will
186 force re-analysis and re-planning of the statement before using it
187 whenever database objects used in the statement have undergone
188 definitional (DDL) changes or their planner statistics have
189 been updated since the previous use of the prepared
190 statement. Also, if the value of <xref linkend="guc-search-path"/> changes
191 from one use to the next, the statement will be re-parsed using the new
192 <varname>search_path</varname>. (This latter behavior is new as of
193 <productname>PostgreSQL</productname> 9.3.) These rules make use of a
194 prepared statement semantically almost equivalent to re-submitting the
195 same query text over and over, but with a performance benefit if no object
196 definitions are changed, especially if the best plan remains the same
197 across uses. An example of a case where the semantic equivalence is not
198 perfect is that if the statement refers to a table by an unqualified name,
199 and then a new table of the same name is created in a schema appearing
200 earlier in the <varname>search_path</varname>, no automatic re-parse will occur
201 since no object used in the statement changed. However, if some other
202 change forces a re-parse, the new table will be referenced in subsequent
203 uses.
204 </para>
205
206 <para>
207 You can see all prepared statements available in the session by querying the
208 <link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link>
209 system view.
210 </para>
211 </refsect1>
212
213 <refsect1 id="sql-prepare-examples" xreflabel="Examples">
214 <title>Examples</title>
215 <para>
216 Create a prepared statement for an <command>INSERT</command>
217 statement, and then execute it:
218 <programlisting>
219 PREPARE fooplan (int, text, bool, numeric) AS
220 INSERT INTO foo VALUES($1, $2, $3, $4);
221 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
222 </programlisting>
223 </para>
224
225 <para>
226 Create a prepared statement for a <command>SELECT</command>
227 statement, and then execute it:
228 <programlisting>
229 PREPARE usrrptplan (int) AS
230 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
231 AND l.date = $2;
232 EXECUTE usrrptplan(1, current_date);
233 </programlisting>
234
235 In this example, the data type of the second parameter is not specified,
236 so it is inferred from the context in which <literal>$2</literal> is used.
237 </para>
238 </refsect1>
239 <refsect1>
240 <title>Compatibility</title>
241
242 <para>
243 The SQL standard includes a <command>PREPARE</command> statement,
244 but it is only for use in embedded SQL. This version of the
245 <command>PREPARE</command> statement also uses a somewhat different
246 syntax.
247 </para>
248 </refsect1>
249
250 <refsect1>
251 <title>See Also</title>
252
253 <simplelist type="inline">
254 <member><xref linkend="sql-deallocate"/></member>
255 <member><xref linkend="sql-execute"/></member>
256 </simplelist>
257 </refsect1>
258 </refentry>