| 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> |