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

Reply via email to