From 106a3695f588a1efd4d68e40fd175a6ee6a3ae84 Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Sun, 15 Jan 2006 22:18:47 +0000 Subject: [PATCH] Allow the types of parameters to PREPARE to be inferred. If a parameter's data type is unspecified or is declared to be "unknown", the type will be inferred from the context in which the parameter is used. This was already possible for protocol-level prepared statements. --- doc/src/sgml/ref/deallocate.sgml | 6 ++-- doc/src/sgml/ref/execute.sgml | 13 ++++----- doc/src/sgml/ref/prepare.sgml | 42 ++++++++++++++++----------- src/backend/parser/analyze.c | 39 ++++++++++++++++++------- src/test/regress/expected/prepare.out | 42 +++++++++++++++------------ src/test/regress/sql/prepare.sql | 13 ++++++--- 6 files changed, 95 insertions(+), 60 deletions(-) diff --git a/doc/src/sgml/ref/deallocate.sgml b/doc/src/sgml/ref/deallocate.sgml index 9481708dd0..1baff4acfb 100644 --- a/doc/src/sgml/ref/deallocate.sgml +++ b/doc/src/sgml/ref/deallocate.sgml @@ -1,5 +1,5 @@ @@ -25,7 +25,7 @@ PostgreSQL documentation -DEALLOCATE [ PREPARE ] plan_name +DEALLOCATE [ PREPARE ] name @@ -58,7 +58,7 @@ DEALLOCATE [ PREPARE ] plan_name - plan_name + name The name of the prepared statement to deallocate. diff --git a/doc/src/sgml/ref/execute.sgml b/doc/src/sgml/ref/execute.sgml index 628617c508..68a240e383 100644 --- a/doc/src/sgml/ref/execute.sgml +++ b/doc/src/sgml/ref/execute.sgml @@ -1,5 +1,5 @@ @@ -25,7 +25,7 @@ PostgreSQL documentation -EXECUTE plan_name [ (parameter [, ...] ) ] +EXECUTE name [ (parameter [, ...] ) ] @@ -60,7 +60,7 @@ EXECUTE plan_name [ ( - plan_name + name The name of the prepared statement to execute. @@ -73,10 +73,9 @@ EXECUTE plan_name [ ( The actual value of a parameter to the prepared statement. This - must be an expression yielding a value of a type compatible with - the data type specified for this parameter position in the - PREPARE command that created the prepared - statement. + must be an expression yielding a value that is compatible with + the data type of this parameter, as was determined when the + prepared statement was created. diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml index 51c3985117..738b6320a4 100644 --- a/doc/src/sgml/ref/prepare.sgml +++ b/doc/src/sgml/ref/prepare.sgml @@ -1,5 +1,5 @@ @@ -25,7 +25,7 @@ PostgreSQL documentation -PREPARE plan_name [ (datatype [, ...] ) ] AS statement +PREPARE name [ (datatype [, ...] ) ] AS statement @@ -45,13 +45,15 @@ PREPARE plan_name [ ( Prepared statements can take parameters: values that are - substituted into the statement when it is executed. To include - parameters in a prepared statement, supply a list of data types in - the PREPARE statement, and, in the statement to - be prepared itself, refer to the parameters by position using - $1, $2, etc. When executing - the statement, specify the actual values for these parameters in - the EXECUTE statement. Refer to $1, $2, etc. A corresponding list of + parameter data types can optionally be specified. When a + parameter's data type is not specified or is declared as + unknown, the type is inferred from the context + in which the parameter is used (if possible). When executing the + statement, specify the actual values for these parameters in the + EXECUTE statement. Refer to for more information about that. @@ -84,7 +86,7 @@ PREPARE plan_name [ ( - plan_name + name An arbitrary name given to this particular prepared @@ -99,8 +101,11 @@ PREPARE plan_name [ (datatype - The data type of a parameter to the prepared statement. To - refer to the parameters in the prepared statement itself, use + The data type of a parameter to the prepared statement. If the + data type of a particular parameter is unspecified or is + specified as unknown, it will be inferred + from the context in which the parameter is used. To refer to the + parameters in the prepared statement itself, use $1, $2, etc. @@ -155,8 +160,8 @@ PREPARE plan_name [ ( Examples - Create a prepared query for an INSERT statement, - and then execute it: + Create a prepared statement for an INSERT + statement, and then execute it: PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3, $4); @@ -165,14 +170,17 @@ EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00); - Create a prepared query for a SELECT statement, - and then execute it: + Create a prepared statement for a SELECT + statement, and then execute it: -PREPARE usrrptplan (int, date) AS +PREPARE usrrptplan (int) AS SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2; EXECUTE usrrptplan(1, current_date); + + Note that the data type of the second parameter is not specified, + so it is inferred from the context in which $2 is used. diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index a8e1510882..abfb0fbf30 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.327 2005/11/22 18:17:15 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.328 2006/01/15 22:18:46 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -2584,10 +2584,11 @@ static Query * transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt) { Query *result = makeNode(Query); - List *argtype_oids = NIL; /* argtype OIDs in a list */ + List *argtype_oids; /* argtype OIDs in a list */ Oid *argtoids = NULL; /* and as an array */ int nargs; List *queries; + int i; result->commandType = CMD_UTILITY; result->utilityStmt = (Node *) stmt; @@ -2598,27 +2599,27 @@ transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt) if (nargs) { ListCell *l; - int i = 0; argtoids = (Oid *) palloc(nargs * sizeof(Oid)); + i = 0; foreach(l, stmt->argtypes) { TypeName *tn = lfirst(l); Oid toid = typenameTypeId(tn); - argtype_oids = lappend_oid(argtype_oids, toid); argtoids[i++] = toid; } } - stmt->argtype_oids = argtype_oids; - /* - * Analyze the statement using these parameter types (any parameters - * passed in from above us will not be visible to it). + * Analyze the statement using these parameter types (any + * parameters passed in from above us will not be visible to it), + * allowing information about unknown parameters to be deduced + * from context. */ - queries = parse_analyze((Node *) stmt->query, argtoids, nargs); + queries = parse_analyze_varparams((Node *) stmt->query, + &argtoids, &nargs); /* * Shouldn't get any extra statements, since grammar only allows @@ -2627,8 +2628,26 @@ transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt) if (list_length(queries) != 1) elog(ERROR, "unexpected extra stuff in prepared statement"); - stmt->query = linitial(queries); + /* + * Check that all parameter types were determined, and convert the + * array of OIDs into a list for storage. + */ + argtype_oids = NIL; + for (i = 0; i < nargs; i++) + { + Oid argtype = argtoids[i]; + if (argtype == InvalidOid || argtype == UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_INDETERMINATE_DATATYPE), + errmsg("could not determine data type of parameter $%d", + i + 1))); + + argtype_oids = lappend_oid(argtype_oids, argtype); + } + + stmt->argtype_oids = argtype_oids; + stmt->query = linitial(queries); return result; } diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out index 54616199b6..f7d1a75814 100644 --- a/src/test/regress/expected/prepare.out +++ b/src/test/regress/expected/prepare.out @@ -58,14 +58,6 @@ SELECT name, statement, parameter_types FROM pg_prepared_statements; PREPARE q2(text) AS SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname = $1; -SELECT name, statement, parameter_types FROM pg_prepared_statements; - name | statement | parameter_types -------+--------------------------------------------------------------------------------------------------------+----------------- - q2 | PREPARE q2(text) AS - SELECT datname, datistemplate, datallowconn - FROM pg_database WHERE datname = $1; | {25} -(1 row) - EXECUTE q2('regression'); datname | datistemplate | datallowconn ------------+---------------+-------------- @@ -75,17 +67,6 @@ 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); -SELECT name, statement, parameter_types FROM pg_prepared_statements; - name | statement | parameter_types -------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------- - q2 | PREPARE q2(text) AS - SELECT datname, datistemplate, datallowconn - FROM pg_database WHERE datname = $1; | {25} - q3 | 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); | {25,23,701,16,26,21} -(2 rows) - EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint); unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- @@ -160,3 +141,26 @@ SELECT * FROM q5_prep_results; 5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx (16 rows) +-- 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; + name | statement | parameter_types +------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------- + q2 | PREPARE q2(text) AS + SELECT datname, datistemplate, datallowconn + FROM pg_database WHERE datname = $1; | {25} + q3 | 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); | {25,23,701,16,26,21} + q5 | PREPARE q5(int, text) AS + SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2; | {23,25} + q6 | PREPARE q6 AS + SELECT * FROM tenk1 WHERE unique1 = $1 AND stringu1 = $2; | {23,19} + q7 | PREPARE q7(unknown) AS + SELECT * FROM road WHERE thepath = $1; | {602} +(5 rows) + diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql index 95db2a0910..d42b7a0879 100644 --- a/src/test/regress/sql/prepare.sql +++ b/src/test/regress/sql/prepare.sql @@ -34,16 +34,12 @@ PREPARE q2(text) AS SELECT datname, datistemplate, datallowconn FROM pg_database WHERE datname = $1; -SELECT name, statement, parameter_types FROM pg_prepared_statements; - 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); -SELECT name, statement, parameter_types FROM pg_prepared_statements; - EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint); -- too few params @@ -63,3 +59,12 @@ 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; + +-- 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; -- 2.40.0