Re: Rows from a stored procedure result are stringified

On Dec 21, 2011 8:52 PM, "Christopher David Howie" <me(at)chrishowie(dot)com>
wrote:
>
> I'm working on an application using psycopg2 2.2.1. Regarding stored
> procedures, I'm seeing some odd behavior. I am calling the procedure
like:
>
> SELECT sp_foobar(1, 2, 3);
>
> In this case, the stored procedure is declared to return a "setof" a
> custom type, and it returns using "RETURN QUERY SELECT ...".
>
> I would expect the rows to come back from cursor.fetchall() in this
> structure:
>
> [(1,2),(3,4),(5,6)]
>
> But they are returned like this:
>
> [('(1,2)'),('(3,4)'),('(5,6)')]
>
> This makes no sense to me. Is this expected/documented behavior?

Yes: it is expected: if psycopg finds a type it doesn't know, it will just
return the string passed by the database. Doesn't it make sense? :-)

You can extend psycopg adding your custom type using register_composite():
see < http://initd.org/psycopg/docs/extras.html#composite-types-casting>.
It requires psycopg 2.4 though. Alternatively you can write and register
your own type parser: see <
http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects
>.

-- Daniele