Re: problem with bytea and undecorated literals

On Thu, Apr 29, 2010 at 11:32 AM, Karsten Hilbert
<[email protected]> wrote:
> On Thu, Apr 29, 2010 at 11:09:54AM +0100, Daniele Varrazzo wrote:
>
>> This is actually a shortcoming in the PostgreSQL casting algorithm, as
>> the same problem would appear issuing the query directly in psql:
>>
>> test=> SELECT foo (ARRAY[E'abc\\\\000S', E'def']);
>> ERROR:  function foo(text[]) does not exist
>> LINE 1: SELECT foo (ARRAY[E'abc\\\\000S', E'def']);
>>                ^
>> HINT:  No function matches the given name and argument types. You
>> might need to add explicit type casts.
>> test=> SELECT foo (ARRAY[E'abc\\\\000S', E'def']::bytea[]);
>>  foo
>> -----
>>  123
>> (1 row)
>
> I'm not sure I agree it's the same problem. Psql (and by
> extension PostgreSQL) does NOT know the "intended" datatype
> (and choses to not second guess) whereas psycopg2 DOES
> (that's what it is proud of - mapping Python data types to
> PG ones and back).
>
> Or am I missing something ?

Well, Postgres does some educate guessing: when some text is passed as
argument to a function and no signature exists for a text argument,
Postgres does look for a datatype into which the argument can be
casted:

test=> create function haveadate(x timestamp) returns text language sql
 as $$ select 'got a ts'::text $$;
CREATE FUNCTION
test=> select haveadate ('2010-01-01');
 haveadate
-----------
 got a ts
(1 row)

This doesn't work in Peter's example because a function accepting a
text argument exists, shadowing the one the caller wanted to invoke.

With arrays instead it seems Postgres is not so aggressive in casting
the argument to one for which a signature is available:

test=> create function haveadatea(x timestamp[]) returns text language sql
 as $$ select 'got a ts[]'::text $$;
CREATE FUNCTION
test=> select haveadatea (array['2010-01-01']);
ERROR:  function haveadatea(text[]) does not exist
LINE 1: select haveadatea (array['2010-01-01']);
               ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

So a cast is required even in places where the text representation of
the argument would have worked for non-array case:

test=> select haveadatea (array['2010-01-01'::timestamp]);
 haveadatea
------------
 got a ts[]
(1 row)

test=> select haveadatea (array['2010-01-01']::timestamp[]);
 haveadatea
------------
 got a ts[]
(1 row)

These problems can be worked around with explicit casting, but of
course some help from psycopg wouldn't be bad to avoid surprises.

-- Daniele