Re: problem with bytea and undecorated literals

On Thu, Apr 29, 2010 at 9:33 AM, Peter Eisentraut <[email protected]> wrote:

[...]
a = Binary('abc')
> print cursor.mogrify('SELECT bar1(%s)', [a])
> cursor.execute('SELECT bar1(%s)', [a])
> print cursor.fetchall()
>
>
> Output:
>
> SELECT bar1(E'abc')
> [('text version',)]   # WRONG

[...]
> PostgreSQL is going to
> default unknown literals to text, so adding an explicit type decoration
> is advisable.

I agree. I just want to point out (in order to help people finding the
same problem: you obviously know this) that an explicit cast can be
added in the query. So, in order to call the correct function with the
current psycopg version, the following query can be used:

In [12]: cursor.execute('SELECT bar1(%s::bytea)', [a])

In [13]: print cursor.fetchall()
[('bytea version',)]

This will not break if psycopg starts adding its own cast, as the
query produced (using an explicit cast plus psycopg one) would be
"SELECT bar1(E'abc'::bytea::bytea)", which is redundant but correct
(unless there are operators binding stronger than the cast, which I
doubt but it can easily be checked).

> Another instance of this problem is that array support with types like
> these is broken:

> b = [Binary('abc\000\123'), Binary('def')]
> cursor.execute('SELECT foo (%s)', [b])
> print cursor.fetchall()
>
> psycopg2.ProgrammingError: function foo(text[]) does not exist

This can be fixed too casting the query placeholder to 'bytea[]':

In [23]: cursor.execute('SELECT foo (%s::bytea[])', [b])

In [24]: print cursor.fetchall()
[(123,)]

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)

-- Daniele