Using array_agg around "row" function does not parse correctly

[Elliot Cameron]

I'm using PostgreSQL and trying to build an array aggregate of tuples by selecting a column like this:

sa.func.array_agg(sa.func.row(User.id, User.name))

This parses as an ugly list of characters.

Putting the "row" on the outside works.

sa.func.row(sa.func.array_agg(User.id), sa.func.array_agg(User.name))

Maybe I'm missing something.

[Mike Bayer]

the database does not appear to send typing information back to the driver (psycopg2) unless an explicit cast is used:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class User(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))

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([User(name='u1'), User(name='u2')])
s.commit()

for row in s.query(cast(func.array_agg(func.row(User.id, User.name)), ARRAY(String))):
    print row[0][0]
    print row[0][1]

renders:

SELECT CAST(array_agg(row(a.id, a.name)) AS VARCHAR[]) AS anon_1 
FROM a
2016-06-23 14:41:54,788 INFO sqlalchemy.engine.base.Engine {}

and we get back separate tuple-strings (e.g. they are strings):

(1,u1)
(2,u2)

the need for the CAST is a driver / psycopg2 / postgresql issue, nothing can be changed for that on this end (except an unconditional CAST in all cases which is heavy-handed). You can make a subclass of ARRAY which renders this cast within the column_expression() method.

The part where the "RECORD" datatype comes back as a string is another thing psycopg2 doesn't know anything about. #3566 proposes to deal with this type. For now, here's a type that will do everything you need:

from sqlalchemy.types import TypeDecorator
import re

class ArrayOfRecord(TypeDecorator):
    impl = String

    def process_result_value(self, value, dialect):
        elems = re.match(r"^\{(\".+?\")*\}$", value).group(1)
        elems = [e for e in re.split(r'"(.*?)",?', elems) if e]
        return [tuple(
            re.findall(r'[^\(\),]+', e)
        ) for e in elems]

def array_agg_row(*arg):
    return func.array_agg(func.row(*arg), type_=ArrayOfRecord)

for row in s.query(array_agg_row(User.id, User.name)):
    print row[0]

[Mike Bayer]

Duplicate of #3566.

[Elliot Cameron]

Thanks for the detailed response!