Please add array_agg function (add a series of PG ARRAY functions)

[Former user]

PostgreSQL has this nice array_agg function, and it'd be awesome if sqlalchemy would know that it's return type is of ARRAY(type of thing passed in).

BTW, Thanks for making SQLAlchemy!

[Mike Bayer]

you can do this yourself

http://docs.sqlalchemy.org/en/rel_0_9/core/functions.html?highlight=function#sqlalchemy.sql.functions.GenericFunction

class array_agg(GenericFunction):
    type = ARRAY

[Mike Bayer]

oh you need to pass it through, then this one:

from sqlalchemy.sql.functions import ReturnTypeFromArgs
class array_agg(ReturnTypeFromArgs):
    pass

feel free to test the feature there and let me know if _type_from_args() works in the case of ARRAY(something).

[Mike Bayer]

array_agg is also SQL standard, here's DB2, http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.sqlref/src/tpc/db2z_bif_arrayagg.dita, and for that matter, ARRAYs themselves are SQL standard, to have this in core we'd need something array-like in base types.

[Mike Bayer]

• Added support for the SQL-standard function :class:.array_agg, which automatically returns an :class:.Array of the correct type and supports index / slice operations. As arrays are only supported on Postgresql at the moment, only actually works on Postgresql. fixes #3132

→ <<cset cfae9c2eaf00>>

[Sebastian Bank]

Nice. Is it desired/possible to also support the ORDER BY clause of aggregates?

So far, I have been using variations of this recipe for array_agg and also string_agg.

[Mike Bayer]

I was really hoping this would just be the normal "WITHIN GROUP" thing I just did at https://bitbucket.org/zzzeek/sqlalchemy/issues/1370/support-percentile_cont-other-ordered-set but unfortunately ORDER BY inside of a function argument like this is not SQL standard, and we don't put PG-specific things into the Core. This would have to be postgresql.aggregate_order_by(expr, order_by) . Please send a PR, thanks.

[Mike Bayer]

• add PG-specific aggregate_order_by(), references #3132

→ <<cset 5295a683f9ac>>

[Mike Bayer]

no PR needed thanks

[Sebastian Bank]

Thanks! (with string_agg the call looks a little suprising, right?)

Maybe the docs can give a hint to wrap array_agg in type_coerce(..., postgresql.ARRAY) if one wants to use contains or other postgres-specific methods.

[Mike Bayer]

• add a postgresql-specific form of array_agg() that injects the ARRAY type, references #3132

→ <<cset 410be197ef5d>>

[jmagnusson]

@zzzeek Does aggregate_order_by somehow support multiple ORDER BY arguments in it's current form?

[Mike Bayer]

we can make that easier but for the moment you can send a list of items as a sqlalchemy.sql.expression.ClauseList. Send a PR to make this more part of the API (see how Selectable.order_by does it).