Attached is a patch that makes the specification of parameters to the
PREPARE SQL statement more flexible: the user can specify "unknown" to
have the type of a particular parameter inferred from the context in
which the parameter is used (if possible). If fewer parameter types are
specified than the number of parameters used in the query, the remaining
parameter types will be inferred in the same way. This is analogous to
the way that parameters can be specified and inferred for protocol-level
prepared statements.
The only trickiness was allowing "UNKNOWN" to be specified in the list
of type names in the PREPARE statement. Since UNKNOWN was previously an
unreserved_keyword, this caused reduce/reduce conflicts:
state 1186
788 prep_type_elem: UNKNOWN .
1477 unreserved_keyword: UNKNOWN .
')' reduce using rule 788 (prep_type_elem)
')' [reduce using rule 1477 (unreserved_keyword)]
',' reduce using rule 788 (prep_type_elem)
',' [reduce using rule 1477 (unreserved_keyword)]
$default reduce using rule 1477 (unreserved_keyword)
I promoted UNKNOWN to be a col_name_keyword (like most of the other
builtin type names), which solved the problem. If there is a better fix,
let me know (I won't claim to be a yacc expert).
This is just a quick and dirty patch; if no one objects to this feature,
tomorrow I will post a revised patch that includes updates to the
documentation and regression tests.
-Neil
============================================================
*** src/backend/parser/analyze.c ee280ecd5150ce51d4c3962f1a105cd790f557bf
--- src/backend/parser/analyze.c c0423f774b8082b2617727a2440f53d9275bb39c
***************
*** 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,2637 ----
if (nargs)
{
ListCell *l;
argtoids = (Oid *) palloc(nargs * sizeof(Oid));
+ i = 0;
foreach(l, stmt->argtypes)
{
TypeName *tn = lfirst(l);
! Oid toid;
! /*
! * For UNKNOWN, we have the type OID already. Otherwise,
! * lookup the OID from the TypeName.
! */
! if (tn->names == NULL)
! {
! Assert(tn->typeid == UNKNOWNOID);
! toid = tn->typeid;
! }
! else
! 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;
}
--- 2640,2665 ----
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/backend/parser/gram.y 5f713badc9a33150446537ceced6fc0043ea2d79
--- src/backend/parser/gram.y f305df1c4ea437626215e90005c02ed1beef2adb
***************
*** 53,58 ****
--- 53,59 ----
#include "catalog/index.h"
#include "catalog/namespace.h"
+ #include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
#include "parser/gramparse.h"
#include "storage/lmgr.h"
***************
*** 299,304 ****
--- 300,306 ----
CharacterWithLength CharacterWithoutLength
ConstDatetime ConstInterval
Bit ConstBit BitWithLength BitWithoutLength
+ prep_type_elem
%type <str> character
%type <str> extract_arg
%type <str> opt_charset
***************
*** 4995,5005 ****
| /* EMPTY */ { $$ = NIL; }
;
! prep_type_list: Typename { $$ = list_make1($1); }
! | prep_type_list ',' Typename
{ $$ = lappend($1, $3); }
;
PreparableStmt:
SelectStmt
| InsertStmt
--- 4997,5019 ----
| /* EMPTY */ { $$ = NIL; }
;
! prep_type_list: prep_type_elem { $$ = list_make1($1); }
! | prep_type_list ',' prep_type_elem
{ $$ = lappend($1, $3); }
;
+ /*
+ * We allow a normal type name, or "UNKNOWN": the type of an unknown
+ * parameter will be inferred by the analysis phase.
+ */
+ prep_type_elem: Typename { $$ = $1; }
+ | UNKNOWN
+ {
+ $$ = makeNode(TypeName);
+ $$->typeid = UNKNOWNOID;
+ }
+ ;
+
PreparableStmt:
SelectStmt
| InsertStmt
***************
*** 8314,8320 ****
| TYPE_P
| UNCOMMITTED
| UNENCRYPTED
- | UNKNOWN
| UNLISTEN
| UNTIL
| UPDATE
--- 8328,8333 ----
***************
*** 8381,8386 ****
--- 8394,8400 ----
| TREAT
| TRIM
| VARCHAR
+ | UNKNOWN
;
/* Function identifier --- keywords that can be function names.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
