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 You can see all prepared statements available in the session by querying the
157 <link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link>
162 <refsect1 id="sql-prepare-examples">
163 <title id="sql-prepare-examples-title">Examples</title>
165 Create a prepared statement for an <command>INSERT</command>
166 statement, and then execute it:
168 PREPARE fooplan (int, text, bool, numeric) AS
169 INSERT INTO foo VALUES($1, $2, $3, $4);
170 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
175 Create a prepared statement for a <command>SELECT</command>
176 statement, and then execute it:
178 PREPARE usrrptplan (int) AS
179 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
181 EXECUTE usrrptplan(1, current_date);
184 Note that the data type of the second parameter is not specified,
185 so it is inferred from the context in which <literal>$2</> is used.
189 <title>Compatibility</title>
192 The SQL standard includes a <command>PREPARE</command> statement,
193 but it is only for use in embedded SQL. This version of the
194 <command>PREPARE</command> statement also uses a somewhat different
200 <title>See Also</title>
202 <simplelist type="inline">
203 <member><xref linkend="sql-deallocate"></member>
204 <member><xref linkend="sql-execute"></member>