1PREPARE(7)               PostgreSQL 11.6 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       Prepared statements can use generic plans rather than re-planning with
65       each set of supplied EXECUTE values. This occurs immediately for
66       prepared statements with no parameters; otherwise it occurs only after
67       five or more executions produce plans whose estimated cost average
68       (including planning overhead) is more expensive than the generic plan
69       cost estimate. Once a generic plan is chosen, it is used for the
70       remaining lifetime of the prepared statement. Using EXECUTE values
71       which are rare in columns with many duplicates can generate custom
72       plans that are so much cheaper than the generic plan, even after adding
73       planning overhead, that the generic plan might never be used.
74
75       A generic plan assumes that each value supplied to EXECUTE is one of
76       the column's distinct values and that column values are uniformly
77       distributed. For example, if statistics record three distinct column
78       values, a generic plan assumes a column equality comparison will match
79       33% of processed rows. Column statistics also allow generic plans to
80       accurately compute the selectivity of unique columns. Comparisons on
81       non-uniformly-distributed columns and specification of non-existent
82       values affects the average plan cost, and hence if and when a generic
83       plan is chosen.
84
85       To examine the query plan PostgreSQL is using for a prepared statement,
86       use EXPLAIN(7), e.g.  EXPLAIN EXECUTE. If a generic plan is in use, it
87       will contain parameter symbols $n, while a custom plan will have the
88       supplied parameter values substituted into it. The row estimates in the
89       generic plan reflect the selectivity computed for the parameters.
90
91       For more information on query planning and the statistics collected by
92       PostgreSQL for that purpose, see the ANALYZE(7) documentation.
93
94       Although the main point of a prepared statement is to avoid repeated
95       parse analysis and planning of the statement, PostgreSQL will force
96       re-analysis and re-planning of the statement before using it whenever
97       database objects used in the statement have undergone definitional
98       (DDL) changes since the previous use of the prepared statement. Also,
99       if the value of search_path changes from one use to the next, the
100       statement will be re-parsed using the new search_path. (This latter
101       behavior is new as of PostgreSQL 9.3.) These rules make use of a
102       prepared statement semantically almost equivalent to re-submitting the
103       same query text over and over, but with a performance benefit if no
104       object definitions are changed, especially if the best plan remains the
105       same across uses. An example of a case where the semantic equivalence
106       is not perfect is that if the statement refers to a table by an
107       unqualified name, and then a new table of the same name is created in a
108       schema appearing earlier in the search_path, no automatic re-parse will
109       occur since no object used in the statement changed. However, if some
110       other change forces a re-parse, the new table will be referenced in
111       subsequent uses.
112
113       You can see all prepared statements available in the session by
114       querying the pg_prepared_statements system view.
115

EXAMPLES

117       Create a prepared statement for an INSERT statement, and then execute
118       it:
119
120           PREPARE fooplan (int, text, bool, numeric) AS
121               INSERT INTO foo VALUES($1, $2, $3, $4);
122           EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
123
124       Create a prepared statement for a SELECT statement, and then execute
125       it:
126
127           PREPARE usrrptplan (int) AS
128               SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
129               AND l.date = $2;
130           EXECUTE usrrptplan(1, current_date);
131
132       Note that the data type of the second parameter is not specified, so it
133       is inferred from the context in which $2 is used.
134

COMPATIBILITY

136       The SQL standard includes a PREPARE statement, but it is only for use
137       in embedded SQL. This version of the PREPARE statement also uses a
138       somewhat different syntax.
139

SEE ALSO

141       DEALLOCATE(7), EXECUTE(7)
142
143
144
145PostgreSQL 11.6                      2019                           PREPARE(7)
Impressum