Re: [COMMITTERS] pgsql: Coerce 'unknown' type parameters to the right type in the

On 18/08/10 18:03, Heikki Linnakangas wrote:
> On 18/08/10 16:57, Tom Lane wrote:
>> heikki@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