| blob | 5815e17b39cc8c933b2c4ba9d00ecfbb96206bcd |
1 -- Regression tests for prepareable statements. We query the content
2 -- of the pg_prepared_statements view as prepared statements are
3 -- created and removed.
4 SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
5 name | statement | parameter_types | result_types
6 ------+-----------+-----------------+--------------
7 (0 rows)
9 PREPARE q1 AS SELECT 1 AS a;
10 EXECUTE q1;
11 a
12 ---
13 1
14 (1 row)
16 SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
17 name | statement | parameter_types | result_types
18 ------+------------------------------+-----------------+--------------
19 q1 | PREPARE q1 AS SELECT 1 AS a; | {} | {integer}
20 (1 row)
22 -- should fail
23 PREPARE q1 AS SELECT 2;
24 ERROR: prepared statement "q1" already exists
25 -- should succeed
26 DEALLOCATE q1;
27 PREPARE q1 AS SELECT 2;
28 EXECUTE q1;
29 ?column?
30 ----------
31 2
32 (1 row)
34 PREPARE q2 AS SELECT 2 AS b;
35 SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
36 name | statement | parameter_types | result_types
37 ------+------------------------------+-----------------+--------------
38 q1 | PREPARE q1 AS SELECT 2; | {} | {integer}
39 q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer}
40 (2 rows)
42 -- sql92 syntax
43 DEALLOCATE PREPARE q1;
44 SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
45 name | statement | parameter_types | result_types
46 ------+------------------------------+-----------------+--------------
47 q2 | PREPARE q2 AS SELECT 2 AS b; | {} | {integer}
48 (1 row)
50 DEALLOCATE PREPARE q2;
51 -- the view should return the empty set again
52 SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements;
53 name | statement | parameter_types | result_types
54 ------+-----------+-----------------+--------------
55 (0 rows)
57 -- parameterized queries
58 PREPARE q2(text) AS
59 SELECT datname, datistemplate, datallowconn
60 FROM pg_database WHERE datname = $1;
61 EXECUTE q2('postgres');
62 datname | datistemplate | datallowconn
63 ----------+---------------+--------------
64 postgres | f | t
65 (1 row)
67 PREPARE q3(text, int, float, boolean, smallint) AS
68 SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
69 ten = $3::bigint OR true = $4 OR odd = $5::int)
70 ORDER BY unique1;
71 EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 4::bigint);
72 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
73 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
74 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx
75 102 | 612 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 102 | 102 | 4 | 5 | YDAAAA | OXAAAA | AAAAxx
76 802 | 2908 | 0 | 2 | 2 | 2 | 2 | 802 | 802 | 802 | 802 | 4 | 5 | WEAAAA | WHEAAA | AAAAxx
77 902 | 1104 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 902 | 902 | 4 | 5 | SIAAAA | MQBAAA | AAAAxx
78 1002 | 2580 | 0 | 2 | 2 | 2 | 2 | 2 | 1002 | 1002 | 1002 | 4 | 5 | OMAAAA | GVDAAA | AAAAxx
79 1602 | 8148 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 1602 | 1602 | 4 | 5 | QJAAAA | KBMAAA | AAAAxx
80 1702 | 7940 | 0 | 2 | 2 | 2 | 2 | 702 | 1702 | 1702 | 1702 | 4 | 5 | MNAAAA | KTLAAA | AAAAxx
81 2102 | 6184 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 2102 | 2102 | 4 | 5 | WCAAAA | WDJAAA | AAAAxx
82 2202 | 8028 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 2202 | 2202 | 4 | 5 | SGAAAA | UWLAAA | AAAAxx
83 2302 | 7112 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 2302 | 2302 | 4 | 5 | OKAAAA | ONKAAA | AAAAxx
84 2902 | 6816 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 2902 | 2902 | 4 | 5 | QHAAAA | ECKAAA | AAAAxx
85 3202 | 7128 | 0 | 2 | 2 | 2 | 2 | 202 | 1202 | 3202 | 3202 | 4 | 5 | ETAAAA | EOKAAA | AAAAxx
86 3902 | 9224 | 0 | 2 | 2 | 2 | 2 | 902 | 1902 | 3902 | 3902 | 4 | 5 | CUAAAA | UQNAAA | AAAAxx
87 4102 | 7676 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 4102 | 4102 | 4 | 5 | UBAAAA | GJLAAA | AAAAxx
88 4202 | 6628 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 4202 | 4202 | 4 | 5 | QFAAAA | YUJAAA | AAAAxx
89 4502 | 412 | 0 | 2 | 2 | 2 | 2 | 502 | 502 | 4502 | 4502 | 4 | 5 | ERAAAA | WPAAAA | AAAAxx
90 4702 | 2520 | 0 | 2 | 2 | 2 | 2 | 702 | 702 | 4702 | 4702 | 4 | 5 | WYAAAA | YSDAAA | AAAAxx
91 4902 | 1600 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 4902 | 4902 | 4 | 5 | OGAAAA | OJCAAA | AAAAxx
92 5602 | 8796 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 602 | 5602 | 4 | 5 | MHAAAA | IANAAA | AAAAxx
93 6002 | 8932 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 1002 | 6002 | 4 | 5 | WWAAAA | OFNAAA | AAAAxx
94 6402 | 3808 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 1402 | 6402 | 4 | 5 | GMAAAA | MQFAAA | AAAAxx
95 7602 | 1040 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 2602 | 7602 | 4 | 5 | KGAAAA | AOBAAA | AAAAxx
96 7802 | 7508 | 0 | 2 | 2 | 2 | 2 | 802 | 1802 | 2802 | 7802 | 4 | 5 | COAAAA | UCLAAA | AAAAxx
97 8002 | 9980 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 3002 | 8002 | 4 | 5 | UVAAAA | WTOAAA | AAAAxx
98 8302 | 7800 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 3302 | 8302 | 4 | 5 | IHAAAA | AOLAAA | AAAAxx
99 8402 | 5708 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 3402 | 8402 | 4 | 5 | ELAAAA | OLIAAA | AAAAxx
100 8602 | 5440 | 0 | 2 | 2 | 2 | 2 | 602 | 602 | 3602 | 8602 | 4 | 5 | WSAAAA | GBIAAA | AAAAxx
101 9502 | 1812 | 0 | 2 | 2 | 2 | 2 | 502 | 1502 | 4502 | 9502 | 4 | 5 | MBAAAA | SRCAAA | AAAAxx
102 9602 | 9972 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 4602 | 9602 | 4 | 5 | IFAAAA | OTOAAA | AAAAxx
103 (29 rows)
105 -- too few params
106 EXECUTE q3('bool');
107 ERROR: wrong number of parameters for prepared statement "q3"
108 DETAIL: Expected 5 parameters but got 1.
109 -- too many params
110 EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 4::bigint, true);
111 ERROR: wrong number of parameters for prepared statement "q3"
112 DETAIL: Expected 5 parameters but got 6.
113 -- wrong param types
114 EXECUTE q3(5::smallint, 10.5::float, false, 4::bigint, 'bytea');
115 ERROR: parameter $3 of type boolean cannot be coerced to the expected type double precision
116 LINE 1: EXECUTE q3(5::smallint, 10.5::float, false, 4::bigint, 'byte...
117 ^
118 HINT: You will need to rewrite or cast the expression.
119 -- invalid type
120 PREPARE q4(nonexistenttype) AS SELECT $1;
121 ERROR: type "nonexistenttype" does not exist
122 LINE 1: PREPARE q4(nonexistenttype) AS SELECT $1;
123 ^
124 -- create table as execute
125 PREPARE q5(int, text) AS
126 SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2
127 ORDER BY unique1;
128 CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA');
129 SELECT * FROM q5_prep_results;
130 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
131 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
132 200 | 9441 | 0 | 0 | 0 | 0 | 0 | 200 | 200 | 200 | 200 | 0 | 1 | SHAAAA | DZNAAA | HHHHxx
133 497 | 9092 | 1 | 1 | 7 | 17 | 97 | 497 | 497 | 497 | 497 | 194 | 195 | DTAAAA | SLNAAA | AAAAxx
134 1173 | 6699 | 1 | 1 | 3 | 13 | 73 | 173 | 1173 | 1173 | 1173 | 146 | 147 | DTAAAA | RXJAAA | VVVVxx
135 1849 | 8143 | 1 | 1 | 9 | 9 | 49 | 849 | 1849 | 1849 | 1849 | 98 | 99 | DTAAAA | FBMAAA | VVVVxx
136 2525 | 64 | 1 | 1 | 5 | 5 | 25 | 525 | 525 | 2525 | 2525 | 50 | 51 | DTAAAA | MCAAAA | AAAAxx
137 3201 | 7309 | 1 | 1 | 1 | 1 | 1 | 201 | 1201 | 3201 | 3201 | 2 | 3 | DTAAAA | DVKAAA | HHHHxx
138 3877 | 4060 | 1 | 1 | 7 | 17 | 77 | 877 | 1877 | 3877 | 3877 | 154 | 155 | DTAAAA | EAGAAA | AAAAxx
139 4553 | 4113 | 1 | 1 | 3 | 13 | 53 | 553 | 553 | 4553 | 4553 | 106 | 107 | DTAAAA | FCGAAA | HHHHxx
140 5229 | 6407 | 1 | 1 | 9 | 9 | 29 | 229 | 1229 | 229 | 5229 | 58 | 59 | DTAAAA | LMJAAA | VVVVxx
141 5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx
142 6581 | 4686 | 1 | 1 | 1 | 1 | 81 | 581 | 581 | 1581 | 6581 | 162 | 163 | DTAAAA | GYGAAA | OOOOxx
143 7257 | 1895 | 1 | 1 | 7 | 17 | 57 | 257 | 1257 | 2257 | 7257 | 114 | 115 | DTAAAA | XUCAAA | VVVVxx
144 7933 | 4514 | 1 | 1 | 3 | 13 | 33 | 933 | 1933 | 2933 | 7933 | 66 | 67 | DTAAAA | QRGAAA | OOOOxx
145 8609 | 5918 | 1 | 1 | 9 | 9 | 9 | 609 | 609 | 3609 | 8609 | 18 | 19 | DTAAAA | QTIAAA | OOOOxx
146 9285 | 8469 | 1 | 1 | 5 | 5 | 85 | 285 | 1285 | 4285 | 9285 | 170 | 171 | DTAAAA | TNMAAA | HHHHxx
147 9961 | 2058 | 1 | 1 | 1 | 1 | 61 | 961 | 1961 | 4961 | 9961 | 122 | 123 | DTAAAA | EBDAAA | OOOOxx
148 (16 rows)
150 CREATE TEMPORARY TABLE q5_prep_nodata AS EXECUTE q5(200, 'DTAAAA')
151 WITH NO DATA;
152 SELECT * FROM q5_prep_nodata;
153 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
154 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
155 (0 rows)
157 -- unknown or unspecified parameter types: should succeed
158 PREPARE q6 AS
159 SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;
160 PREPARE q7(unknown) AS
161 SELECT * FROM road WHERE thepath = $1;
162 -- DML statements
163 PREPARE q8 AS
164 UPDATE tenk1 SET stringu1 = $2 WHERE unique1 = $1;
165 SELECT name, statement, parameter_types, result_types FROM pg_prepared_statements
166 ORDER BY name;
167 name | statement | parameter_types | result_types
168 ------+------------------------------------------------------------------+----------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------
169 q2 | PREPARE q2(text) AS +| {text} | {name,boolean,boolean}
170 | SELECT datname, datistemplate, datallowconn +| |
171 | FROM pg_database WHERE datname = $1; | |
172 q3 | PREPARE q3(text, int, float, boolean, smallint) AS +| {text,integer,"double precision",boolean,smallint} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
173 | SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR+| |
174 | ten = $3::bigint OR true = $4 OR odd = $5::int) +| |
175 | ORDER BY unique1; | |
176 q5 | PREPARE q5(int, text) AS +| {integer,text} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
177 | SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2 +| |
178 | ORDER BY unique1; | |
179 q6 | PREPARE q6 AS +| {integer,name} | {integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,name,name,name}
180 | SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | |
181 q7 | PREPARE q7(unknown) AS +| {path} | {text,path}
182 | SELECT * FROM road WHERE thepath = $1; | |
183 q8 | PREPARE q8 AS +| {integer,name} |
184 | UPDATE tenk1 SET stringu1 = $2 WHERE unique1 = $1; | |
185 (6 rows)
187 -- test DEALLOCATE ALL;
188 DEALLOCATE ALL;
189 SELECT name, statement, parameter_types FROM pg_prepared_statements
190 ORDER BY name;
191 name | statement | parameter_types
192 ------+-----------+-----------------
193 (0 rows)