Postgres' arrays in subqueries

[artee]

Mike and others :)

I have the following sql:

select 

    models.code,

    array(

        select (clients.id, clients.code) from clients

        group by clients.id, clients.code

    ) as envs

from models

I ended with something like this:

items = DBSession.query(Client.id, Client.code). \

        group_by(Client.id, Client.code). \

        subquery()

return DBSession.query(

        Model.code,

        array(items)). \

    order_by(Model.code)

but the exception is thrown:

TypeError: __init__() argument after * must be a sequence, not Alias

The question is how to use an array to be returned in query (select (clients.id, clients.code)) and how to pass subquery to the array?

Could you provide some ideas?

I've tested it on SA 1.0.8.

Thanks

Artur

[Mike Bayer]

the "array()" construct currently represents only an array literal of column expressions, and it produces the PG expression ARRAY[], which is not what you want here.   For a vanilla array() function, use func.array():

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(Integer)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)


s = Session(e)
s.add_all([A(data=1), A(data=2), A(data=3)])
s.commit()

subq = s.query(A.data).subquery().as_scalar()

q = s.query(A, func.array(subq))

print q.all()


output:

SELECT a.id AS a_id, a.data AS a_data, array((SELECT a.data
FROM a)) AS array_1
FROM a

Thanks

Artur

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.