Prev: [HACKERS] PL/pgSQL EXECUTE '..' USING with unknown
Next: [HACKERS] LockDatabaseObject vs. LockSharedObject
From: Pavel Stehule on 5 Aug 2010 16:56
Hello

2010/8/5 Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.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(a)postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 5 Aug 2010 17:11
Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> writes:
> 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

This example doesn't seem terribly compelling. Why would you bother
using USING with constants?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Andrew Dunstan on 5 Aug 2010 17:31


On 08/05/2010 05:11 PM, Tom Lane wrote:
> Heikki Linnakangas<heikki.linnakangas(a)enterprisedb.com> writes:
>> 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
> This example doesn't seem terribly compelling. Why would you bother
> using USING with constants?
>
>

In a more complex example you might use $1 in more than one place in the
query.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 5 Aug 2010 18:13
Andrew Dunstan <andrew(a)dunslane.net> writes:
> On 08/05/2010 05:11 PM, Tom Lane wrote:
>> This example doesn't seem terribly compelling. Why would you bother
>> using USING with constants?

> In a more complex example you might use $1 in more than one place in the
> query.

Well, that's better than no justification, but it's still pretty weak.
A bigger problem is that doing anything like this will require reversing
the logical path of causation in EXECUTE USING. Right now, we evaluate
the USING expressions first, and then their types feed forward into
parsing the EXECUTE string. What Heikki is suggesting requires
reversing that, at least to some extent. I'm not convinced it's
possible without breaking other cases that are more important.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Heikki Linnakangas on 6 Aug 2010 03:36
On 06/08/10 01:13, Tom Lane wrote:
> Andrew Dunstan<andrew(a)dunslane.net> writes:
>> On 08/05/2010 05:11 PM, Tom Lane wrote:
>>> This example doesn't seem terribly compelling. Why would you bother
>>> using USING with constants?
>
>> In a more complex example you might use $1 in more than one place in the
>> query.
>
> Well, that's better than no justification, but it's still pretty weak.
> A bigger problem is that doing anything like this will require reversing
> the logical path of causation in EXECUTE USING. Right now, we evaluate
> the USING expressions first, and then their types feed forward into
> parsing the EXECUTE string. What Heikki is suggesting requires
> reversing that, at least to some extent. I'm not convinced it's
> possible without breaking other cases that are more important.

One approach is to handle the conversion from unknown to the right data
type transparently in the backend. Attached patch adds a
coerce-param-hook for fixed params that returns a CoerceViaIO node to
convert the param to the right type at runtime. That's quite similar to
the way unknown constants are handled.

The patch doesn't currently check that a parameter is only resolved to
one type in the same query, but that can be added.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
 |  Next  |  Last
Pages: 1 2
Prev: [HACKERS] PL/pgSQL EXECUTE '..' USING with unknown
Next: [HACKERS] LockDatabaseObject vs. LockSharedObject