1PREPARE(7)               PostgreSQL 13.3 Documentation              PREPARE(7)
2
3
4

NAME

6       PREPARE - prepare a statement for execution
7

SYNOPSIS

9       PREPARE name [ ( data_type [, ...] ) ] AS statement
10

DESCRIPTION

12       PREPARE creates a prepared statement. A prepared statement is a
13       server-side object that can be used to optimize performance. When the
14       PREPARE statement is executed, the specified statement is parsed,
15       analyzed, and rewritten. When an EXECUTE command is subsequently
16       issued, the prepared statement is planned and executed. This division
17       of labor avoids repetitive parse analysis work, while allowing the
18       execution plan to depend on the specific parameter values supplied.
19
20       Prepared statements can take parameters: values that are substituted
21       into the statement when it is executed. When creating the prepared
22       statement, refer to parameters by position, using $1, $2, etc. A
23       corresponding list of parameter data types can optionally be specified.
24       When a parameter's data type is not specified or is declared as
25       unknown, the type is inferred from the context in which the parameter
26       is first referenced (if possible). When executing the statement,
27       specify the actual values for these parameters in the EXECUTE
28       statement. Refer to EXECUTE(7) for more information about that.
29
30       Prepared statements only last for the duration of the current database
31       session. When the session ends, the prepared statement is forgotten, so
32       it must be recreated before being used again. This also means that a
33       single prepared statement cannot be used by multiple simultaneous
34       database clients; however, each client can create their own prepared
35       statement to use. Prepared statements can be manually cleaned up using
36       the DEALLOCATE(7) command.
37
38       Prepared statements potentially have the largest performance advantage
39       when a single session is being used to execute a large number of
40       similar statements. The performance difference will be particularly
41       significant if the statements are complex to plan or rewrite, e.g., if
42       the query involves a join of many tables or requires the application of
43       several rules. If the statement is relatively simple to plan and
44       rewrite but relatively expensive to execute, the performance advantage
45       of prepared statements will be less noticeable.
46

PARAMETERS

48       name
49           An arbitrary name given to this particular prepared statement. It
50           must be unique within a single session and is subsequently used to
51           execute or deallocate a previously prepared statement.
52
53       data_type
54           The data type of a parameter to the prepared statement. If the data
55           type of a particular parameter is unspecified or is specified as
56           unknown, it will be inferred from the context in which the
57           parameter is first referenced. To refer to the parameters in the
58           prepared statement itself, use $1, $2, etc.
59
60       statement
61           Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.
62

NOTES

64       A prepared statement can be executed with either a generic plan or a
65       custom plan. A generic plan is the same across all executions, while a
66       custom plan is generated for a specific execution using the parameter
67       values given in that call. Use of a generic plan avoids planning
68       overhead, but in some situations a custom plan will be much more
69       efficient to execute because the planner can make use of knowledge of
70       the parameter values. (Of course, if the prepared statement has no
71       parameters, then this is moot and a generic plan is always used.)
72
73       By default (that is, when plan_cache_mode is set to auto), the server
74       will automatically choose whether to use a generic or custom plan for a
75       prepared statement that has parameters. The current rule for this is
76       that the first five executions are done with custom plans and the
77       average estimated cost of those plans is calculated. Then a generic
78       plan is created and its estimated cost is compared to the average
79       custom-plan cost. Subsequent executions use the generic plan if its
80       cost is not so much higher than the average custom-plan cost as to make
81       repeated replanning seem preferable.
82
83       This heuristic can be overridden, forcing the server to use either
84       generic or custom plans, by setting plan_cache_mode to
85       force_generic_plan or force_custom_plan respectively. This setting is
86       primarily useful if the generic plan's cost estimate is badly off for
87       some reason, allowing it to be chosen even though its actual cost is
88       much more than that of a custom plan.
89
90       To examine the query plan PostgreSQL is using for a prepared statement,
91       use EXPLAIN(7), for example
92
93           EXPLAIN EXECUTE name(parameter_values);
94
95       If a generic plan is in use, it will contain parameter symbols $n,
96       while a custom plan will have the supplied parameter values substituted
97       into it.
98
99       For more information on query planning and the statistics collected by
100       PostgreSQL for that purpose, see the ANALYZE(7) documentation.
101
102       Although the main point of a prepared statement is to avoid repeated
103       parse analysis and planning of the statement, PostgreSQL will force
104       re-analysis and re-planning of the statement before using it whenever
105       database objects used in the statement have undergone definitional
106       (DDL) changes since the previous use of the prepared statement. Also,
107       if the value of search_path changes from one use to the next, the
108       statement will be re-parsed using the new search_path. (This latter
109       behavior is new as of PostgreSQL 9.3.) These rules make use of a
110       prepared statement semantically almost equivalent to re-submitting the
111       same query text over and over, but with a performance benefit if no
112       object definitions are changed, especially if the best plan remains the
113       same across uses. An example of a case where the semantic equivalence
114       is not perfect is that if the statement refers to a table by an
115       unqualified name, and then a new table of the same name is created in a
116       schema appearing earlier in the search_path, no automatic re-parse will
117       occur since no object used in the statement changed. However, if some
118       other change forces a re-parse, the new table will be referenced in
119       subsequent uses.
120
121       You can see all prepared statements available in the session by
122       querying the pg_prepared_statements system view.
123

EXAMPLES

125       Create a prepared statement for an INSERT statement, and then execute
126       it:
127
128           PREPARE fooplan (int, text, bool, numeric) AS
129               INSERT INTO foo VALUES($1, $2, $3, $4);
130           EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
131
132       Create a prepared statement for a SELECT statement, and then execute
133       it:
134
135           PREPARE usrrptplan (int) AS
136               SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
137               AND l.date = $2;
138           EXECUTE usrrptplan(1, current_date);
139
140       In this example, the data type of the second parameter is not
141       specified, so it is inferred from the context in which $2 is used.
142

COMPATIBILITY

144       The SQL standard includes a PREPARE statement, but it is only for use
145       in embedded SQL. This version of the PREPARE statement also uses a
146       somewhat different syntax.
147

SEE ALSO

149       DEALLOCATE(7), EXECUTE(7)
150
151
152
153PostgreSQL 13.3                      2021                           PREPARE(7)
Impressum