Re: PL/pgSQL EXECUTE '..' USING with unknown

Hello

2010/8/5 Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>:
> There's a little problem with EXECUTE USING when the parameters are of type
> unknown (going back to 8.4 where EXECUTE USING was introduced):
>
> do $$
> BEGIN
>  EXECUTE 'SELECT to_date($1, $2)' USING '17-DEC-80', 'DD-MON-YY';
> END;
> $$;
> ERROR:  failed to find conversion function from unknown to text
> CONTEXT:  SQL statement "SELECT to_date($1, $2)"
> PL/pgSQL function "inline_code_block" line 2 at EXECUTE statement
>
> The corresponding case works fine when used with PREPARE/EXECUTE:
>
> postgres=# PREPARE foostmt AS SELECT to_date($1, $2);
> PREPARE
> postgres=# EXECUTE foostmt ('17-DEC-80', 'DD-MON-YY');
>  to_date
> ------------
>  1980-12-17
> (1 row)
>
> With PREPARE/EXECUTE, the query is analyzed with parse_analyze_varparams()
> which allows unknown param types to be deduced from the context. Seems we
> should use that for EXECUTE USING as well, but there's no SPI interface for
> that.
>
> Thoughts? Should we add an SPI_prepare_varparams() function and use that?
>

+1 - There are similar problems with recordsets

> --
>  Heikki Linnakangas
>  EnterpriseDB   http://www.enterprisedb.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>