On Fri, Sep 21, 2012 at 6:07 PM, Tobias Oberstein
<tobias.oberstein@gmail.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