Using SQL aggregate fcns on a PGArray column

[David Korz]

I have a table like such:

CREATE TABLE test (
mykey integer not null,
mydata double precision [2][10]
) without oids;

with a table defn like:

test = Table("test",metadata,
Column("mykey",Integer,nullable=False),
Column("mydata",PGArray(Float))
)

I want to do a query like:

select mykey,sum(mydata[1][1]) from test group by mykey;

In SA I want to do something like:

query = select([test.c.mykey,func.sum(test.c.mydata[1]
[1])],from_obj=[test]).group_by(test.c.mykey)

test.c.mydata can't be indexed so this is illegal. Is there a way I
can access an array element in SA without just putting the actual SQL
text in?

[Michael Bayer]

This is an unimplemented feature at the moment. I've created a recipe that hand-rolls the syntax plus typing behavior as a @compiles recipe, which is available at: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PGArrayIndex .