Skip site navigation (1) Skip section navigation (2)

Re: flexi adaption/casting scheme

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: Tobias Oberstein <tobias(dot)oberstein(at)gmail(dot)com>
Cc: Ronan Dunklau <rdunklau(at)gmail(dot)com>, psycopg(at)postgresql(dot)org
Subject: Re: flexi adaption/casting scheme
Date: 2012-09-21 17:39:54
Message-ID: CA+mi_8b1A3Tph5JTs6hB7j4ffkczmGC2dAcEieuenTtVqdntQg@mail.gmail.com (view raw or flat)
Thread:
Lists: psycopg
On Fri, Sep 21, 2012 at 6:07 PM, Tobias Oberstein
<tobias(dot)oberstein(at)gmail(dot)com> wrote:

> My understanding would be that the following code should work with an
> unmodified Psycopg, but it does not:
>
> https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test7.py

Postgres fails finding a cast for this statement (that you can get
with "cur.mogrify("SELECT test_employee(%s)", [v1])"):

    SELECT test_employee(('foo', 44, ARRAY[1, 2, 3], NULL,
('Duckhausen', NULL, 18, ARRAY[(10, NULL, 'blub'), (NULL, 5,
NULL)])));

The error is "Cannot cast type record[] to t_station[]". It's too
complex or ambiguous for postgres to perform this cast. To work around
it we must cast the (10, NULL, 'blub') generic record into a
t_station. You can work around it by registering a specific adapter
for that tuple (that you should subclass e.g. in a namedtuple).

    from collections import namedtuple
    station = namedtuple('station', 'x y label')

    # this adapter invokes the basic tuple adapter and adds a specific cast.
    class StationAdapter(object):
        def __init__(self, adapted):
            self.adapted = adapted
        def prepare(self,conn):
            self._conn = conn
        def getquoted(self):
            a = psycopg2.extensions.adapt(tuple(self.adapted))
            a.prepare(self._conn)
            return a.getquoted() + '::t_station'

    psycopg2.extensions.register_adapter(station, StationAdapter)

    v1 = ('foo', 44, [1, 2, 3], None, ('Duckhausen', None, 18,
[station(10, None, 'blub'), station(None, 5, None)]))
    print cur.mogrify("SELECT test_employee(%s)", [v1])

returns:

    SELECT test_employee(('foo', 44, ARRAY[1, 2, 3], NULL,
('Duckhausen', NULL, 18, ARRAY[(10, NULL, 'blub')::t_station, (NULL,
5, NULL)::t_station])))

Looks like for such complex cast you can't use the generic tuples but
will have to use some class of your own. This will reduce the need for
postgres to guess your types, likely making the communication more
robust.

-- Daniele


In response to

Responses

psycopg by date

Next:From: Tobias ObersteinDate: 2012-09-21 19:30:40
Subject: Re: flexi adaption/casting scheme
Previous:From: Tobias ObersteinDate: 2012-09-21 17:07:08
Subject: Re: flexi adaption/casting scheme

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group