1PREPARE(7) SQL Commands PREPARE(7)
2
3
4
6 PREPARE - prepare a statement for execution
7
8
10 PREPARE name [ ( datatype [, ...] ) ] AS statement
11
12
14 PREPARE creates a prepared statement. A prepared statement is a server-
15 side object that can be used to optimize performance. When the PREPARE
16 statement is executed, the specified statement is parsed, rewritten,
17 and planned. When an EXECUTE command is subsequently issued, the pre‐
18 pared statement need only be executed. Thus, the parsing, rewriting,
19 and planning stages are only performed once, instead of every time the
20 statement is executed.
21
22 Prepared statements can take parameters: values that are substituted
23 into the statement when it is executed. When creating the prepared
24 statement, refer to parameters by position, using $1, $2, etc. A corre‐
25 sponding list of parameter data types can optionally be specified. When
26 a parameter's data type is not specified or is declared as unknown, the
27 type is inferred from the context in which the parameter is used (if
28 possible). When executing the statement, specify the actual values for
29 these parameters in the EXECUTE statement. Refer to EXECUTE [exe‐
30 cute(7)] for more information about that.
31
32 Prepared statements only last for the duration of the current database
33 session. When the session ends, the prepared statement is forgotten, so
34 it must be recreated before being used again. This also means that a
35 single prepared statement cannot be used by multiple simultaneous data‐
36 base clients; however, each client can create their own prepared state‐
37 ment to use. The prepared statement can be manually cleaned up using
38 the DEALLOCATE [deallocate(7)] command.
39
40 Prepared statements have the largest performance advantage when a sin‐
41 gle session is being used to execute a large number of similar state‐
42 ments. The performance difference will be particularly significant if
43 the statements are complex to plan or rewrite, for example, if the
44 query involves a join of many tables or requires the application of
45 several rules. If the statement is relatively simple to plan and re‐
46 write but relatively expensive to execute, the performance advantage of
47 prepared statements will be less noticeable.
48
50 name An arbitrary name given to this particular prepared statement.
51 It must be unique within a single session and is subsequently
52 used to execute or deallocate a previously prepared statement.
53
54 datatype
55 The data type of a parameter to the prepared statement. If the
56 data type of a particular parameter is unspecified or is speci‐
57 fied as unknown, it will be inferred from the context in which
58 the parameter is used. To refer to the parameters in the pre‐
59 pared statement itself, use $1, $2, etc.
60
61 statement
62 Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.
63
65 In some situations, the query plan produced for a prepared statement
66 will be inferior to the query plan that would have been chosen if the
67 statement had been submitted and executed normally. This is because
68 when the statement is planned and the planner attempts to determine the
69 optimal query plan, the actual values of any parameters specified in
70 the statement are unavailable. PostgreSQL collects statistics on the
71 distribution of data in the table, and can use constant values in a
72 statement to make guesses about the likely result of executing the
73 statement. Since this data is unavailable when planning prepared state‐
74 ments with parameters, the chosen plan might be suboptimal. To examine
75 the query plan PostgreSQL has chosen for a prepared statement, use
76 EXPLAIN [explain(7)].
77
78 For more information on query planning and the statistics collected by
79 PostgreSQL for that purpose, see the ANALYZE [analyze(7)] documenta‐
80 tion.
81
82 You can see all available prepared statements of a session by querying
83 the pg_prepared_statements system view.
84
86 Create a prepared statement for an INSERT statement, and then execute
87 it:
88
89 PREPARE fooplan (int, text, bool, numeric) AS
90 INSERT INTO foo VALUES($1, $2, $3, $4);
91 EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
92
93
94 Create a prepared statement for a SELECT statement, and then execute
95 it:
96
97 PREPARE usrrptplan (int) AS
98 SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
99 AND l.date = $2;
100 EXECUTE usrrptplan(1, current_date);
101
102 Note that the data type of the second parameter is not specified, so it
103 is inferred from the context in which $2 is used.
104
106 The SQL standard includes a PREPARE statement, but it is only for use
107 in embedded SQL. This version of the PREPARE statement also uses a
108 somewhat different syntax.
109
111 DEALLOCATE [deallocate(7)], EXECUTE [execute(7)]
112
113
114
115SQL - Language Statements 2014-02-17 PREPARE(7)