[Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[<pa...@bo...>]

On Wed, 5 Jun 2002 18:40:11 +0200 Gerhard Häring <ger...@gm...> wrote:
>CC-ing to pypgsql-users list, maybe other users have comments on this issue,
>too.

[Arrays vs. 'IN']

>QUERY: DECLARE PgSQL_08120A6C CURSOR FOR select * from cages where name
>       in '{"mammals","apes"}'
>
>... which obviously cannot work. The reason is that if pyPgSQL encounters a
>tuple or a list in the statement parameters, it will quote them suitable for
>use as an ARRAY. Unfortunately, the ARRAY syntax is different from what you
>would need for the IN operator in SQL.

Right, and there's no way of knowing what the database engine expects for that 
parameter until it has parsed the statement. I suppose there isn't a way of 
getting type information from PostgreSQL about parameters - indeed, does 
PostgreSQL actually support parameters in the way that other database systems 
do? I got the impression that PostgreSQL database modules have to bundle the 
parameter values into the query as shown above, rather than passing those 
values separately.

>I'm not sure if something can be done about this problem. It seems to me that
>it's an either/or with ARRAY vs. your desired conversion for the IN operator.

That's an unfortunate limitation, but thankfully I didn't need to use 'IN' 
after all. :-)

>I've fiddled with adding Unicode support for some time now, but I didn't get
>enough testers yet to make me comfortable enough with it for adding it to a
>pyPgSQL release. On the Sourceforge patches section, I've uploaded a patch for
>full Unicode support, including getting Unicode results back from the database,
>insert Unicode strings, and automatic encoding conversions. I'll try to update
>this patch today to work with the latest pyPgSQL 2.1 release and drop you a
>note when it's ready.

That would be great. Where XML data and PostgreSQL come together, it quickly 
becomes a requirement to handle the data gracefully or transparently, although 
I suspect that many database systems still struggle with Unicode.

Thanks for the explanation and the great work!

Paul