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.
5
SELECT name, statement, parameter_types FROM pg_prepared_statements;
7
PREPARE q1 AS SELECT 1 AS a;
10
SELECT name, statement, parameter_types FROM pg_prepared_statements;
13
PREPARE q1 AS SELECT 2;
17
PREPARE q1 AS SELECT 2;
20
PREPARE q2 AS SELECT 2 AS b;
21
SELECT name, statement, parameter_types FROM pg_prepared_statements;
24
DEALLOCATE PREPARE q1;
26
SELECT name, statement, parameter_types FROM pg_prepared_statements;
28
DEALLOCATE PREPARE q2;
29
-- the view should return the empty set again
30
SELECT name, statement, parameter_types FROM pg_prepared_statements;
32
-- parameterized queries
34
SELECT datname, datistemplate, datallowconn
35
FROM pg_database WHERE datname = $1;
37
EXECUTE q2('postgres');
39
PREPARE q3(text, int, float, boolean, oid, smallint) AS
40
SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
41
ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int)
44
EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint);
50
EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true);
53
EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea');
56
PREPARE q4(nonexistenttype) AS SELECT $1;
58
-- create table as execute
59
PREPARE q5(int, text) AS
60
SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2
62
CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA');
63
SELECT * FROM q5_prep_results;
65
-- unknown or unspecified parameter types: should succeed
67
SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2;
68
PREPARE q7(unknown) AS
69
SELECT * FROM road WHERE thepath = $1;
71
SELECT name, statement, parameter_types FROM pg_prepared_statements
74
-- test DEALLOCATE ALL;
76
SELECT name, statement, parameter_types FROM pg_prepared_statements