Re: Generic casters for composite types - Mailing list psycopg

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15/12/2011 17:21, Daniele Varrazzo wrote:
> On Thu, Dec 15, 2011 at 3:17 PM, Ronan Dunklau <rdunklau@gmail.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

How can I have missed that ?! ;)
Sorry for bothering you with something I could have found in the docs.

> 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))

Thank you for such a detailed answer.

> 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.

You're welcome, I'm glad this mail turned out to be useful.


- --
Ronan Dunklau
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.18 (GNU/Linux)

iQEcBAEBAgAGBQJO6iR8AAoJECTYLCgFy323QOAH/i9X+K9VF2IDTgtx1+qTsqvJ
xPdUgJYNuKZeO7EquNFJb3mBHLW+V9N4ov6FGW/Y+jzSvFLfm1WBjAThISBnsdda
TWiJU2p9YOcQXC1sPtTTwgjuAAmze6KIG3QnGqtF1Kjk4Lwx/7wuPAO0wpgPCIdW
kRl0lOJEpx9/UpBkCzq8UirtluQwVi2gdUJrf+pGng17mG1rHEJrlmk62Tkr36CR
Any6TG6oNHYR/rPqpEBTVj4qL0V9PUPoldhn8Uc0sYsuNGxoqE+QPQu1nYvBz3Q9
XhGExwqp4tyWEmUIc4JCDzUhv6PR+99H8XpKw5QZAHti7SDPl9fG5cmpEBrBxB8=
=OcDB
-----END PGP SIGNATURE-----