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