1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/prepare.sgml,v 1.1 2002/08/27 04:55:07 tgl Exp $
PostgreSQL documentation
-->
<refentry id="SQL-PREPARE">
<refmeta>
<refentrytitle id="sql-prepare-title">PREPARE</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>
PREPARE
</refname>
<refpurpose>
create a prepared query
</refpurpose>
</refnamediv>
<refsynopsisdiv>
<refsynopsisdivinfo>
<date>2002-08-12</date>
</refsynopsisdivinfo>
<synopsis>
PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">query</replaceable>
</synopsis>
<refsect2 id="R2-SQL-PREPARE-1">
<refsect2info>
<date>2002-08-12</date>
</refsect2info>
<title>
Inputs
</title>
<para>
<variablelist>
<varlistentry>
<term><replaceable class="PARAMETER">plan_name</replaceable></term>
<listitem>
<para>
An arbitrary name given to this particular prepared query. It
must be unique within a single session, and is used to execute
or remove a previously prepared query.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">datatype</replaceable></term>
<listitem>
<para>
The data-type of a parameter to the prepared query.
To refer to the parameters in the prepared query itself,
use <literal>$1</literal>, <literal>$2</literal>, etc.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
<refsect2 id="R2-SQL-PREPARE-2">
<refsect2info>
<date>2002-08-12</date>
</refsect2info>
<title>
Outputs
</title>
<para>
<variablelist>
<varlistentry>
<term><computeroutput>
<returnvalue>PREPARE</returnvalue>
</computeroutput></term>
<listitem>
<para>
The query has been prepared successfully.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
</refsynopsisdiv>
<refsect1 id="R1-SQL-PREPARE-1">
<refsect1info>
<date>2002-08-12</date>
</refsect1info>
<title>
Description
</title>
<para>
<command>PREPARE</command> creates a prepared query. A prepared
query is a server-side object that can be used to optimize
performance. When the <command>PREPARE</command> statement is
executed, the specified query is parsed, rewritten, and
planned. When a subsequent <command>EXECUTE</command> statement is
issued, the prepared query need only be executed. Thus, the
parsing, rewriting, and planning stages are only performed once,
instead of every time the query is executed.
</para>
<para>
Prepared queries can take parameters: values that are
substituted into the query when it is executed. To specify the
parameters to a prepared query, include a list of data-types with
the <command>PREPARE</command> statement. In the query itself, you
can refer to the parameters by position using
<literal>$1</literal>, <literal>$2</literal>, etc. When executing
the query, specify the actual values for these parameters in the
<command>EXECUTE</command> statement -- refer to <xref
linkend="sql-execute" endterm="sql-execute-title">
for more information.
</para>
<para>
Prepared queries are stored locally (in the current backend), and
only exist for the duration of the current database session. When
the client exits, the prepared query is forgotten, and so it must be
re-created before being used again. This also means that a single
prepared query cannot be used by multiple simultaneous database
clients; however, each client can create their own prepared query
to use.
</para>
<para>
Prepared queries have the largest performance advantage when a
single backend is being used to execute a large number of similar
queries. The performance difference will be particularly
significant if the queries are complex to plan or rewrite. For
example, if the query involves a join of many tables or requires
the application of several rules. If the query is relatively simple
to plan and rewrite but relatively expensive to execute, the
performance advantage of prepared queries will be less noticeable.
</para>
<refsect2 id="R2-SQL-PREPARE-3">
<refsect2info>
<date>2002-08-12</date>
</refsect2info>
<title>
Notes
</title>
<para>
In some situations, the query plan produced by
<productname>PostgreSQL</productname> for a prepared query may be
inferior to the plan produced if the query were submitted and
executed normally. This is because when the query is planned (and
the optimizer attempts to determine the optimal query plan), the
actual values of any parameters specified in the query are
unavailable. <productname>PostgreSQL</productname> collects
statistics on the distribution of data in the table, and can use
constant values in a query to make guesses about the likely
result of executing the query. Since this data is unavailable when
planning prepared queries with parameters, the chosen plan may be
sub-optimal.
</para>
<para>
For more information on query planning and the statistics
collected by <productname>PostgreSQL</productname> for query
optimization purposes, see the <xref linkend="sql-analyze"
endterm="sql-analyze-title"> documentation.
</para>
</refsect2>
</refsect1>
<refsect1 id="R1-SQL-PREPARE-3">
<title>
Compatibility
</title>
<refsect2 id="R2-SQL-PREPARE-4">
<refsect2info>
<date>2002-08-12</date>
</refsect2info>
<title>
SQL92
</title>
<para>
SQL92 includes a <command>PREPARE</command> statement, but it is
only for use in embedded SQL clients. The
<command>PREPARE</command> statement implemented by
<productname>PostgreSQL</productname> also uses a somewhat
different syntax.
</para>
</refsect2>
</refsect1>
</refentry>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->
|
