Обсуждение: Rows from a stored procedure result are stringified

Hello,

Please copy me on replies as I'm not subscribed to this list.

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?

--
Chris Howie
http://www.chrishowie.com
http://en.wikipedia.org/wiki/User:Crazycomputers

If you correspond with me on a regular basis, please read this document:
http://www.chrishowie.com/email-preferences/

PGP fingerprint: 2B7A B280 8B12 21CC 260A DF65 6FCE 505A CF83 38F5

------------------------------------------------------------------------
                    IMPORTANT INFORMATION/DISCLAIMER

This document should be read only by those persons to whom it is
addressed.  If you have received this message it was obviously addressed
to you and therefore you can read it.

Additionally, by sending an email to ANY of my addresses or to ANY
mailing lists to which I am subscribed, whether intentionally or
accidentally, you are agreeing that I am "the intended recipient," and
that I may do whatever I wish with the contents of any message received
from you, unless a pre-existing agreement prohibits me from so doing.

This overrides any disclaimer or statement of confidentiality that may
be included on your message.

On Dec 21, 2011 8:52 PM, "Christopher David Howie" <me@chrishowie.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

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)')]
>>
>> 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>.

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?

--
Chris Howie
http://www.chrishowie.com
http://en.wikipedia.org/wiki/User:Crazycomputers

If you correspond with me on a regular basis, please read this document:
http://www.chrishowie.com/email-preferences/

PGP fingerprint: 2B7A B280 8B12 21CC 260A DF65 6FCE 505A CF83 38F5

------------------------------------------------------------------------
                    IMPORTANT INFORMATION/DISCLAIMER

This document should be read only by those persons to whom it is
addressed.  If you have received this message it was obviously addressed
to you and therefore you can read it.

Additionally, by sending an email to ANY of my addresses or to ANY
mailing lists to which I am subscribed, whether intentionally or
accidentally, you are agreeing that I am "the intended recipient," and
that I may do whatever I wish with the contents of any message received
from you, unless a pre-existing agreement prohibits me from so doing.

This overrides any disclaimer or statement of confidentiality that may
be included on your message.

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

On 12/22/2011 10:34 AM, Daniele Varrazzo wrote:
> 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.

I may do that in the future, as there's a good change I'll have more
questions, but for now this will be my last message, because:

>> On 12/21/2011 03:14 PM, Daniele Varrazzo wrote:
> 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)

This is exactly the solution I was looking for.  I'm not sure how I
missed it, the syntax is pretty obvious.  I didn't even notice when
fiddling with psql that "SELECT myfunc()" was in fact returning a string
and not many columns.  Thanks for the pointer.

The other information in your message was useful as well.  I will likely
be using composite types as columns soon and I'll need to map them to
Python types.  So thanks for the detailed explanation.  :)

Cheers,

--
Chris Howie
http://www.chrishowie.com
http://en.wikipedia.org/wiki/User:Crazycomputers

If you correspond with me on a regular basis, please read this document:
http://www.chrishowie.com/email-preferences/

PGP fingerprint: 2B7A B280 8B12 21CC 260A DF65 6FCE 505A CF83 38F5

------------------------------------------------------------------------
                    IMPORTANT INFORMATION/DISCLAIMER

This document should be read only by those persons to whom it is
addressed.  If you have received this message it was obviously addressed
to you and therefore you can read it.

Additionally, by sending an email to ANY of my addresses or to ANY
mailing lists to which I am subscribed, whether intentionally or
accidentally, you are agreeing that I am "the intended recipient," and
that I may do whatever I wish with the contents of any message received
from you, unless a pre-existing agreement prohibits me from so doing.

This overrides any disclaimer or statement of confidentiality that may
be included on your message.