On Sun, 2006-01-15 at 12:17 -0500, Tom Lane wrote:
> You're doing it wrong. There is no need for any special case whatever
> in gram.y --- ordinary lookup of the type name will do fine.
Woops, good point. Attached is a revised patch that doesn't modify the
grammar, and includes updates to the documentation and regression tests.
Applied to HEAD.
-Neil
============================================================
*** doc/src/sgml/ref/deallocate.sgml 47093d8fa4ee9a32c2913ea147d3d9502a51a12e
--- doc/src/sgml/ref/deallocate.sgml a9b19cf7cc5471f6cbd52c015eeb3fa24b57821d
***************
*** 25,31 ****
<refsynopsisdiv>
<synopsis>
! DEALLOCATE [ PREPARE ] <replaceable class="parameter">plan_name</replaceable>
</synopsis>
</refsynopsisdiv>
--- 25,31 ----
<refsynopsisdiv>
<synopsis>
! DEALLOCATE [ PREPARE ] <replaceable class="parameter">name</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 58,64 ****
</varlistentry>
<varlistentry>
! <term><replaceable class="parameter">plan_name</replaceable></term>
<listitem>
<para>
The name of the prepared statement to deallocate.
--- 58,64 ----
</varlistentry>
<varlistentry>
! <term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
The name of the prepared statement to deallocate.
============================================================
*** doc/src/sgml/ref/execute.sgml fa053d68c10cadae226e45da2c38199325043346
--- doc/src/sgml/ref/execute.sgml 12d45d714aeaf6494d6c3b12e995c980626f9099
***************
*** 25,31 ****
<refsynopsisdiv>
<synopsis>
! EXECUTE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]
</synopsis>
</refsynopsisdiv>
--- 25,31 ----
<refsynopsisdiv>
<synopsis>
! EXECUTE <replaceable class="PARAMETER">name</replaceable> [ (<replaceable class="PARAMETER">parameter</replaceable> [, ...] ) ]
</synopsis>
</refsynopsisdiv>
***************
*** 60,66 ****
<variablelist>
<varlistentry>
! <term><replaceable class="PARAMETER">plan_name</replaceable></term>
<listitem>
<para>
The name of the prepared statement to execute.
--- 60,66 ----
<variablelist>
<varlistentry>
! <term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
The name of the prepared statement to execute.
***************
*** 73,82 ****
<listitem>
<para>
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
! <command>PREPARE</command> command that created the prepared
! statement.
</para>
</listitem>
</varlistentry>
--- 73,81 ----
<listitem>
<para>
The actual value of a parameter to the prepared statement. This
! 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.
</para>
</listitem>
</varlistentry>
============================================================
*** doc/src/sgml/ref/prepare.sgml 3449586c977e47e193388d8c2fa4ff250b06e59f
--- doc/src/sgml/ref/prepare.sgml d54a7b2167a1c5a83fe305ad97b1d1bc270c92c9
***************
*** 25,31 ****
<refsynopsisdiv>
<synopsis>
! PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>
</synopsis>
</refsynopsisdiv>
--- 25,31 ----
<refsynopsisdiv>
<synopsis>
! PREPARE <replaceable class="PARAMETER">name</replaceable> [ (<replaceable class="PARAMETER">datatype</replaceable> [, ...] ) ] AS <replaceable class="PARAMETER">statement</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 45,57 ****
<para>
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 <command>PREPARE</command> statement, and, in the statement to
! be prepared itself, refer to the parameters by position using
! <literal>$1</literal>, <literal>$2</literal>, etc. When executing
! the statement, specify the actual values for these parameters in
! the <command>EXECUTE</command> statement. Refer to <xref
linkend="sql-execute" endterm="sql-execute-title"> for more
information about that.
</para>
--- 45,59 ----
<para>
Prepared statements can take parameters: values that are
! substituted into the statement when it is executed. When creating
! the prepared statement, refer to parameters by position, using
! <literal>$1</>, <literal>$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
! <literal>unknown</literal>, 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
! <command>EXECUTE</command> statement. Refer to <xref
linkend="sql-execute" endterm="sql-execute-title"> for more
information about that.
</para>
***************
*** 84,90 ****
<variablelist>
<varlistentry>
! <term><replaceable class="PARAMETER">plan_name</replaceable></term>
<listitem>
<para>
An arbitrary name given to this particular prepared
--- 86,92 ----
<variablelist>
<varlistentry>
! <term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
An arbitrary name given to this particular prepared
***************
*** 99,106 ****
<term><replaceable class="PARAMETER">datatype</replaceable></term>
<listitem>
<para>
! The data type of a parameter to the prepared statement. To
! refer to the parameters in the prepared statement itself, use
<literal>$1</literal>, <literal>$2</literal>, etc.
</para>
</listitem>
--- 101,111 ----
<term><replaceable class="PARAMETER">datatype</replaceable></term>
<listitem>
<para>
! The data type of a parameter to the prepared statement. If the
! data type of a particular parameter is unspecified or is
! specified as <literal>unknown</literal>, it will be inferred
! from the context in which the parameter is used. To refer to the
! parameters in the prepared statement itself, use
<literal>$1</literal>, <literal>$2</literal>, etc.
</para>
</listitem>
***************
*** 155,162 ****
<refsect1 id="sql-prepare-examples">
<title id="sql-prepare-examples-title">Examples</title>
<para>
! Create a prepared query for an <command>INSERT</command> statement,
! and then execute it:
<programlisting>
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
--- 160,167 ----
<refsect1 id="sql-prepare-examples">
<title id="sql-prepare-examples-title">Examples</title>
<para>
! Create a prepared statement for an <command>INSERT</command>
! statement, and then execute it:
<programlisting>
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
***************
*** 165,178 ****
</para>
<para>
! Create a prepared query for a <command>SELECT</command> statement,
! and then execute it:
<programlisting>
! PREPARE usrrptplan (int, date) 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);
</programlisting>
</para>
</refsect1>
<refsect1>
--- 170,186 ----
</para>
<para>
! Create a prepared statement for a <command>SELECT</command>
! statement, and then execute it:
<programlisting>
! 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);
</programlisting>
+
+ Note that the data type of the second parameter is not specified,
+ so it is inferred from the context in which <literal>$2</> is used.
</para>
</refsect1>
<refsect1>
============================================================
*** src/backend/parser/analyze.c ee280ecd5150ce51d4c3962f1a105cd790f557bf
--- src/backend/parser/analyze.c afb7af22d4d6a78cc2ef6f5a076206c6eaf5d5e6
***************
*** 2584,2593 ****
transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
{
Query *result = makeNode(Query);
! List *argtype_oids = NIL; /* argtype OIDs in a list */
Oid *argtoids = NULL; /* and as an array */
int nargs;
List *queries;
result->commandType = CMD_UTILITY;
result->utilityStmt = (Node *) stmt;
--- 2584,2594 ----
transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
{
Query *result = makeNode(Query);
! 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,2624 ****
if (nargs)
{
ListCell *l;
- int i = 0;
argtoids = (Oid *) palloc(nargs * sizeof(Oid));
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).
*/
! queries = parse_analyze((Node *) stmt->query, argtoids, nargs);
/*
* Shouldn't get any extra statements, since grammar only allows
--- 2599,2625 ----
if (nargs)
{
ListCell *l;
argtoids = (Oid *) palloc(nargs * sizeof(Oid));
+ i = 0;
foreach(l, stmt->argtypes)
{
TypeName *tn = lfirst(l);
Oid toid = typenameTypeId(tn);
argtoids[i++] = toid;
}
}
/*
! * 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_varparams((Node *) stmt->query,
! &argtoids, &nargs);
/*
* Shouldn't get any extra statements, since grammar only allows
***************
*** 2627,2634 ****
if (list_length(queries) != 1)
elog(ERROR, "unexpected extra stuff in prepared statement");
! stmt->query = linitial(queries);
return result;
}
--- 2628,2653 ----
if (list_length(queries) != 1)
elog(ERROR, "unexpected extra stuff in prepared statement");
! /*
! * 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;
}
============================================================
*** src/test/regress/expected/prepare.out ac00879338b4a595a7115ff49820258b9fea4556
--- src/test/regress/expected/prepare.out 64b0450b880f73c12262a4af051b68db51be450c
***************
*** 58,71 ****
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
------------+---------------+--------------
--- 58,63 ----
***************
*** 75,91 ****
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
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
--- 67,72 ----
***************
*** 160,162 ****
--- 141,166 ----
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)
+
============================================================
*** src/test/regress/sql/prepare.sql 90bc73b518b0d7ef0c1ede82d5ae088392ea3910
--- src/test/regress/sql/prepare.sql 2d9c730eeaf4ef93bd9a94cbf85a73e2f6af48fc
***************
*** 34,41 ****
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
--- 34,39 ----
***************
*** 42,49 ****
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
--- 40,45 ----
***************
*** 63,65 ****
--- 59,70 ----
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;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
