On 18/08/10 18:03, Heikki Linnakangas wrote:
On 18/08/10 16:57, Tom Lane wrote:
hei...@postgresql.org (Heikki Linnakangas) writes:
Log Message:
---
Coerce 'unknown' type parameters to the right type in the fixed-params
parse_analyze() function. That case occurs e.g with PL/pgSQL
EXECUTE ... USING 'stringconstant'.
The coercion with a CoerceViaIO node. The result is similar to the
coercion
via input function performed for unknown constants in coerce_type(),
except that this happens at runtime.
Unfortunately, this entirely fails to enforce the rule that an unknown
Param be coerced the same way everywhere. You'd need a cleanup pass as
well, cf check_variable_parameters().
Yeah, you're right. I'll find a way to do the cleanup pass in fixed
params case too.
It turned out to be messier than I imagined, but I have a working patch
now. It still doesn't behave exactly like the variable params case,
though. To wit:
postgres=# DO $$
declare
t text;
begin
EXECUTE 'SELECT 1+ $1, $1' INTO t USING '123' ;
RAISE NOTICE '%', t;
end;
$$;
ERROR: could not determine data type of parameter $1
LINE 1: SELECT 1+ $1, $1
^
QUERY: SELECT 1+ $1, $1
CONTEXT: PL/pgSQL function inline_code_block line 5 at EXECUTE statement
The varparams code doesn't thrown an error on that. At the first
reference to $1, the parameter is resolved to int4. On the 2nd
reference, it's an int4 and there's nothing to force coercion to
anything else, so it's returned as an int4. In the fixed params case,
however, that throws an error. We could make it behave the same if we
really wanted to, but that would add even more code.
I'm starting to wonder if it's worth enforcing the rule that all unknown
Params must be coerced to the same target type. We could just document
the behavior. Or maybe we should revert the whole thing, and add a check
to PL/pgSQL EXECUTE USING code to just throw a nicer error message if
you pass an unknown parameter in the USING clause.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6b99a10..2cb9f31 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -92,6 +92,10 @@ parse_analyze(Node *parseTree, const char *sourceText,
query = transformStmt(pstate, parseTree);
+ /* make sure all is well with parameter types */
+ if (numParams 0)
+ check_fixed_parameters(pstate, query);
+
free_parsestate(pstate);
return query;
diff --git a/src/backend/parser/parse_param.c b/src/backend/parser/parse_param.c
index 60917f4..7cb34c2 100644
--- a/src/backend/parser/parse_param.c
+++ b/src/backend/parser/parse_param.c
@@ -59,7 +59,10 @@ static Node *variable_coerce_param_hook(ParseState *pstate, Param *param,
static Node *fixed_coerce_param_hook(ParseState *pstate, Param *param,
Oid targetTypeId, int32 targetTypeMod,
int location);
-static bool check_parameter_resolution_walker(Node *node, ParseState *pstate);
+static bool check_fixed_parameter_resolution_walker(Node *node,
+ ParseState *pstate);
+static bool check_variable_parameter_resolution_walker(Node *node,
+ ParseState *pstate);
/*
@@ -322,6 +325,135 @@ variable_coerce_param_hook(ParseState *pstate, Param *param,
return NULL;
}
+
+/*
+ * Check for consistent assignment of unknown parameters after completion
+ * of parsing with parse_fixed_parameters.
+ *
+ * Note: this code intentionally does not check that all parameter positions
+ * were used, nor that all got non-UNKNOWN types assigned. Caller of parser
+ * should enforce that if it's important.
+ */
+void
+check_fixed_parameters(ParseState *pstate, Query *query)
+{
+ FixedParamState *parstate = (FixedParamState *) pstate-p_ref_hook_state;
+
+ /*
+ * If parse_fixed_parameters() didn't resolve any unknown types, there's
+ * nothing to do.
+ */
+ if (parstate-unknownParamTypes != NULL)
+ (void) query_tree_walker(query,
+ check_fixed_parameter_resolution_walker,
+ (void *) pstate, 0);
+}
+
+/*
+ * Traverse a fully-analyzed tree to verify that all references to unknown
+ * Params are coerced to the same type. Although we check in
+ * fixed_coerce_param_hook() that an unknown Param is not coerced to different
+ * types at different locations in the query, some Params might still be
+ * uncoerced, if there wasn't anything to force their coercion, and yet other
+ * instances seen later might have gotten coerced.
+ */
+static bool
+check_fixed_parameter_resolution_walker(Node *node, ParseState *pstate)
+{
+ FixedParamState *parstate = (FixedParamState *) pstate-p_ref_hook_state;
+
+ if (node == NULL)
+ return false;
+
+ /*
+ * Check if this is a CoerceViaIO(Param of type 'unknown') construct,
+ * created by parse_fixed_parameters(). In theory, it could be a similar
+ * construct created by other means, but that doesn't currently happen;
+ * unknown Params needing coercion
