| -- Regression tests for prepareable statements. We query the content |
| -- of the pg_prepared_statements view as prepared statements are |
| -- created and removed. |
| |
| SELECT name, statement, parameter_types FROM pg_prepared_statements; |
| |
| PREPARE q1 AS SELECT 1 AS a; |
| EXECUTE q1; |
| |
| SELECT name, statement, parameter_types FROM pg_prepared_statements; |
| |
| -- should fail |
| PREPARE q1 AS SELECT 2; |
| |
| -- should succeed |
| DEALLOCATE q1; |
| PREPARE q1 AS SELECT 2; |
| EXECUTE q1; |
| |
| PREPARE q2 AS SELECT 2 AS b; |
| SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY 1,2,3; |
| |
| -- sql92 syntax |
| DEALLOCATE PREPARE q1; |
| |
| SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY 1,2,3; |
| |
| DEALLOCATE PREPARE q2; |
| -- the view should return the empty set again |
| SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY 1,2,3; |
| |
| -- parameterized queries |
| PREPARE q2(text) AS |
| SELECT datname, datistemplate, datallowconn |
| FROM pg_database WHERE datname = $1; |
| |
| EXECUTE q2('regression'); |
| |
| PREPARE q3(text, int, float, boolean, oid, smallint) AS |
| SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR |
| ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int) ORDER BY 1,2,3,4; |
| |
| EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint); |
| |
| -- too few params |
| EXECUTE q3('bool'); |
| |
| -- too many params |
| EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true); |
| |
| -- wrong param types |
| EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea'); |
| |
| -- invalid type |
| PREPARE q4(nonexistenttype) AS SELECT $1; |
| |
| -- create table as execute |
| PREPARE q5(int, text) AS |
| SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2; |
| CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA'); |
| SELECT * FROM q5_prep_results ORDER BY 1,2,3,4; |
| |
| -- unknown or unspecified parameter types: should succeed |
| PREPARE q6 AS |
| SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; |
| PREPARE q7(unknown) AS |
| SELECT * FROM road WHERE thepath = $1; |
| |
| SELECT name, statement, parameter_types FROM pg_prepared_statements |
| ORDER BY name; |