Re: Rows from a stored procedure result are stringified - Mailing list psycopg

On Wed, Dec 21, 2011 at 8:54 PM, Christopher David Howie
<me@chrishowie.com> wrote:

Hi Chris. Please consider subscribing to the ML, or I will have to
authorize every single message of yours. After subscribing, you may
also disable receiving mail if you want.

> On 12/21/2011 03:14 PM, Daniele Varrazzo wrote:
>>> 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)')]
>>>
>> You can extend psycopg adding your custom type

This is still true, however it may not be the most convenient way.
Sorry but from your example I may have missed the picture. Let's say
you have a SRF of some type, e.g.:

    test=> create type mytype as (a int, b text);
    CREATE TYPE
    test=> create function myfunc (x int, y int)
    returns setof mytype as $$
    select n, repeat('x', n) from generate_series($1,$2) n;
    $$ language sql;

You can now use this function either returning the whole result in a
single field:

    test=> select myfunc(1,4);
      myfunc
    ----------
     (1,x)
     (2,xx)
     (3,xxx)
     (4,xxxx)
    (4 rows)

or access to the components of the returned type:

    test=> select * from myfunc(1,4);
     a |  b
    ---+------
     1 | x
     2 | xx
     3 | xxx
     4 | xxxx
    (4 rows)

(you can also specify the fields such as "select b from myfunc"... you
got the picture).

Psycopg receives the same data displayed by psql: in the first case
you get the type returned by the function in a single field:

    In [5]: cur.execute("select myfunc(1,4)")

    In [6]: print cur.fetchall()
    [('(1,x)',), ('(2,xx)',), ('(3,xxx)',), ('(4,xxxx)',)]

and this is your "surprising version". If everything you have to do is
to get these data unpacked, you can use the second version of the
query, specifying the fields list:

    In [6]: cur.execute("select a,b from myfunc(1,4)")

    In [7]: cur.fetchall()
    Out[7]: [(1, 'x'), (2, 'xx'), (3, 'xxx'), (4, 'xxxx')]

and this is probably everything you need, if you just want to receive
the function result, and it could have been the first answer to your
question yesterday.

If, instead, you want to keep the identity of the type you get from
the function, you can do what suggested before: attaching a parser to
the oid of "mytype". Together with the result, Postgres passes to the
client some metadata about the returned type, so that e.g. we can tell
if "1" was the string "1" or the number 1:

    In [8]: cur.execute("select myfunc(1,4)")

    In [9]: cur.description
    Out[9]: (Column(name='myfunc', type_code=897686,
display_size=None, internal_size=-1, precision=None, scale=None,
null_ok=None),)

    In [10]: cur.fetchone()
    Out[10]: ('(1,x)',)

so we can attach a fancy parser to parse strings such as "(1,x)"
whenever we see a column of data with oid "897686" (which is the oid
in *my* database: in yours it will be different). All these operations
(getting the oid of mytype, inspecting what is composed of, generate a
parser for it, attach the parser to its oid) are performed by
register_composite().


> Hmm.  This makes sense to some degree.  I do note that strings coming
> back from the database are not quoted.  Is there a parsing routine in
> psycopg that I can leverage to turn this kind of database-provided
> tuple-as-a-string representation into a proper tuple/list?

The psycopg2.extras.CompositeCaster class has the parsing function,
but it is not part of the API. You may read the code to see what it
does, but chances are that you either just want to use
register_composite() or you don't want to get composites at all, using
the unpacking syntax in the query.


-- Daniele