Re: Generic casters for composite types

On Thu, Dec 15, 2011 at 3:17 PM, Ronan Dunklau <rdunklau(at)gmail(dot)com> wrote:

> I think it may be interesting to add a generic mechanism for registering
> composite type casters.

Such as this one? :)
http://initd.org/psycopg/docs/extras.html#composite-types-casting

> One of the features I like about postgresql is the ability to perform
> queries like this one:
>
> select user_group, array_agg(user) from user_group natural join user
> group by user_group
>
> Where user_group and user are two tables related by a foreign key.
>
> Such a query is pleasant to write, but unusable for now because psycopg
> returns those values as strings.
>
> Since those types are defined by postgresql at table creation time, it
> should be easy to parse those results to return namedtuples.

The register_composite() does *almost* what you want. Without the
"almost", the result, after committing the tables created in your
example, is:

>>> import psycopg2
>>> import psycopg2.extras
>>> cnn = psycopg2.connect('dbname=test')
>>> psycopg2.extras.register_composite('test2', cnn)
>>> psycopg2.extras.register_composite('test1', cnn)
>>> cur = cnn.cursor()
>>> cur.execute("""select test1, array_agg(test2) as test2s from
test1 inner join test2 on test1.id = test2.test_id group by test1;""")
>>> cur.fetchone()
(test1(id=1, label='test1'),
[test2(id=1, label='testa', test_id=1),
test2(id=2, label='testb', test_id=1)])
>>> cur.fetchone()
(test1(id=2, label='test2'),
[test2(id=3, label='testc', test_id=2),
test2(id=4, label='testd', test_id=2)])

Why the "almost"? The query to introspect the database was tested on
composite types created by CREATE TYPE, not on the ones created after
the tables, and it doesn't account for dropped fields and hidden
columns (cmin, xmin ecc.). Plus, there was an error raising the error
message after the column count mismatch. With the following patch, the
above works as expected. It takes psycopg 2.4.3 as previous version
didn't support arrays of composites:

diff --git a/lib/extras.py b/lib/extras.py
index 491a390..d9c2ab9 100644
--- a/lib/extras.py
+++ b/lib/extras.py
@@ -841,8 +841,8 @@ class CompositeCaster(object):
tokens = self.tokenize(s)
if len(tokens) != len(self.atttypes):
raise psycopg2.DataError(
- "expecting %d components for the type %s, %d
found instead",
- (len(self.atttypes), self.name, len(self.tokens)))
+ "expecting %d components for the type %s, %d found instead"
+ % (len(self.atttypes), self.name, len(tokens)))

attrs = [ curs.cast(oid, token)
for oid, token in zip(self.atttypes, tokens) ]
@@ -914,6 +914,8 @@ FROM pg_type t
JOIN pg_namespace ns ON typnamespace = ns.oid
JOIN pg_attribute a ON attrelid = typrelid
WHERE typname = %%s and nspname = %%s
+AND NOT attisdropped
+AND attnum > 0
ORDER BY attnum;
""" % typarray, (tname, schema))

So, thank you very much for your test case: I will include it in the
test suite together with the above patch to make sure the composite
adapter works with tables too.

-- Daniele