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
5SELECT name, statement, parameter_types FROM pg_prepared_statements;
6
7PREPARE q1 AS SELECT 1 AS a;
8EXECUTE q1;
9
10SELECT name, statement, parameter_types FROM pg_prepared_statements;
11
12-- should fail
13PREPARE q1 AS SELECT 2;
14
15-- should succeed
16DEALLOCATE q1;
17PREPARE q1 AS SELECT 2;
18EXECUTE q1;
19
20PREPARE q2 AS SELECT 2 AS b;
21SELECT name, statement, parameter_types FROM pg_prepared_statements;
22
23-- sql92 syntax
24DEALLOCATE PREPARE q1;
25
26SELECT name, statement, parameter_types FROM pg_prepared_statements;
27
28DEALLOCATE PREPARE q2;
29-- the view should return the empty set again
30SELECT name, statement, parameter_types FROM pg_prepared_statements;
31
32-- parameterized queries
33PREPARE q2(text) AS
34	SELECT datname, datistemplate, datallowconn
35	FROM pg_database WHERE datname = $1;
36
37EXECUTE q2('postgres');
38
39PREPARE q3(text, int, float, boolean, smallint) AS
40	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
41	ten = $3::bigint OR true = $4 OR odd = $5::int)
42	ORDER BY unique1;
43
44EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 4::bigint);
45
46-- too few params
47EXECUTE q3('bool');
48
49-- too many params
50EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 4::bigint, true);
51
52-- wrong param types
53EXECUTE q3(5::smallint, 10.5::float, false, 4::bigint, 'bytea');
54
55-- invalid type
56PREPARE q4(nonexistenttype) AS SELECT $1;
57
58-- create table as execute
59PREPARE q5(int, text) AS
60	SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2
61	ORDER BY unique1;
62CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA');
63SELECT * FROM q5_prep_results;
64CREATE TEMPORARY TABLE q5_prep_nodata AS EXECUTE q5(200, 'DTAAAA')
65    WITH NO DATA;
66SELECT * FROM q5_prep_nodata;
67
68-- unknown or unspecified parameter types: should succeed
69PREPARE q6 AS
70    SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;
71PREPARE q7(unknown) AS
72    SELECT * FROM road WHERE thepath = $1;
73
74SELECT name, statement, parameter_types FROM pg_prepared_statements
75    ORDER BY name;
76
77-- test DEALLOCATE ALL;
78DEALLOCATE ALL;
79SELECT name, statement, parameter_types FROM pg_prepared_statements
80    ORDER BY name;
81