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>$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 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 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.
126 <refsect1 id="SQL-PREPARE-notes">
130 Prepared statements can use generic plans rather than re-planning with
131 each set of supplied <command>EXECUTE</command> values. This occurs
132 immediately for prepared statements with no parameters; otherwise
133 it occurs only after five or more executions produce plans whose
134 estimated cost average (including planning overhead) is more expensive
135 than the generic plan cost estimate. Once a generic plan is chosen,
136 it is used for the remaining lifetime of the prepared statement.
137 Using <command>EXECUTE</command> values which are rare in columns with
138 many duplicates can generate custom plans that are so much cheaper
139 than the generic plan, even after adding planning overhead, that the
140 generic plan might never be used.
144 A generic plan assumes that each value supplied to
145 <command>EXECUTE</command> is one of the column's distinct values
146 and that column values are uniformly distributed. For example,
147 if statistics record three distinct column values, a generic plan
148 assumes a column equality comparison will match 33% of processed rows.
149 Column statistics also allow generic plans to accurately compute the
150 selectivity of unique columns. Comparisons on non-uniformly-distributed
151 columns and specification of non-existent values affects the average
152 plan cost, and hence if and when a generic plan is chosen.
156 To examine the query plan <productname>PostgreSQL</productname> is using
157 for a prepared statement, use <xref linkend="sql-explain">, e.g.
158 <command>EXPLAIN EXECUTE</>.
159 If a generic plan is in use, it will contain parameter symbols
160 <literal>$<replaceable>n</></literal>, while a custom plan will have the
161 supplied parameter values substituted into it.
162 The row estimates in the generic plan reflect the selectivity
163 computed for the parameters.
167 For more information on query planning and the statistics collected
168 by <productname>PostgreSQL</productname> for that purpose, see
169 the <xref linkend="sql-analyze">
174 Although the main point of a prepared statement is to avoid repeated parse
175 analysis and planning of the statement, <productname>PostgreSQL</> will
176 force re-analysis and re-planning of the statement before using it
177 whenever database objects used in the statement have undergone
178 definitional (DDL) changes since the previous use of the prepared
179 statement. Also, if the value of <xref linkend="guc-search-path"> changes
180 from one use to the next, the statement will be re-parsed using the new
181 <varname>search_path</>. (This latter behavior is new as of
182 <productname>PostgreSQL</productname> 9.3.) These rules make use of a
183 prepared statement semantically almost equivalent to re-submitting the
184 same query text over and over, but with a performance benefit if no object
185 definitions are changed, especially if the best plan remains the same
186 across uses. An example of a case where the semantic equivalence is not
187 perfect is that if the statement refers to a table by an unqualified name,
188 and then a new table of the same name is created in a schema appearing
189 earlier in the <varname>search_path</>, no automatic re-parse will occur
190 since no object used in the statement changed. However, if some other
191 change forces a re-parse, the new table will be referenced in subsequent
196 You can see all prepared statements available in the session by querying the
197 <link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link>
202 <refsect1 id="sql-prepare-examples">
203 <title id="sql-prepare-examples-title">Examples</title>
205 Create a prepared statement for an <command>INSERT</command>
206 statement, and then execute it:
208 PREPARE fooplan (int, text, bool, numeric) AS
209 INSERT INTO foo VALUES($1, $2, $3, $4);
210 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
215 Create a prepared statement for a <command>SELECT</command>
216 statement, and then execute it:
218 PREPARE usrrptplan (int) AS
219 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
221 EXECUTE usrrptplan(1, current_date);
224 Note that the data type of the second parameter is not specified,
225 so it is inferred from the context in which <literal>$2</> is used.
229 <title>Compatibility</title>
232 The SQL standard includes a <command>PREPARE</command> statement,
233 but it is only for use in embedded SQL. This version of the
234 <command>PREPARE</command> statement also uses a somewhat different
240 <title>See Also</title>
242 <simplelist type="inline">
243 <member><xref linkend="sql-deallocate"></member>
244 <member><xref linkend="sql-execute"></member>