- 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

4 files changed, 69 insertions, 2 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 2c14731aa..0a6543575 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,16 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: feature, sql, postgresql
+        :tickets: 3132
+
+        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.
+
+    .. change::
         :tags: feature, sql
         :tickets: 3516
 
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index 80ee31b0f..d536c3008 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -626,3 +626,25 @@ class sysdate(AnsiFunction):
 
 class user(AnsiFunction):
     type = sqltypes.String
+
+
+class array_agg(GenericFunction):
+    """support for the ARRAY_AGG function.
+
+    The ``func.array_agg(expr)`` construct returns an expression of
+    type :class:`.Array`.
+
+    e.g.
+
+        stmt = select([func.array_agg(table.c.values)[2:5]])
+
+    .. versionadded:: 1.1
+
+    """
+
+    def __init__(self, *args, **kwargs):
+        args = [_literal_as_binds(c) for c in args]
+        kwargs.setdefault('type_', sqltypes.Array(_type_from_args(args)))
+        kwargs['_parsed_args'] = args
+        GenericFunction.__init__(self, *args, **kwargs)
+
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index 2d5c2aaa1..da45c2f2a 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -916,6 +916,33 @@ class ArrayRoundTripTest(fixtures.TablesTest, AssertsExecutionResults):
         assert isinstance(tbl.c.intarr.type.item_type, Integer)
         assert isinstance(tbl.c.strarr.type.item_type, String)
 
+    @testing.provide_metadata
+    def test_array_agg(self):
+        values_table = Table('values', self.metadata, Column('value', Integer))
+        self.metadata.create_all(testing.db)
+        testing.db.execute(
+            values_table.insert(),
+            [{'value': i} for i in range(1, 10)]
+        )
+
+        stmt = select([func.array_agg(values_table.c.value)])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            list(range(1, 10))
+        )
+
+        stmt = select([func.array_agg(values_table.c.value)[3]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            3
+        )
+
+        stmt = select([func.array_agg(values_table.c.value)[2:4]])
+        eq_(
+            testing.db.execute(stmt).scalar(),
+            [2, 3, 4]
+        )
+
     def test_array_index_slice_exprs(self):
         """test a variety of expressions that sometimes need parenthesizing"""
 
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index ccc9b2dcd..f080046ff 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -1,8 +1,8 @@
-from sqlalchemy.testing import eq_
+from sqlalchemy.testing import eq_, is_
 import datetime
 from sqlalchemy import func, select, Integer, literal, DateTime, Table, \
     Column, Sequence, MetaData, extract, Date, String, bindparam, \
-    literal_column
+    literal_column, Array
 from sqlalchemy.sql import table, column
 from sqlalchemy import sql, util
 from sqlalchemy.sql.compiler import BIND_TEMPLATES
@@ -489,6 +489,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             MissingType().compile
         )
 
+    def test_array_agg(self):
+        m = MetaData()
+        t = Table('t', m, Column('data', Integer))
+        expr = func.array_agg(t.c.data)
+        is_(expr.type._type_affinity, Array)
+        is_(expr.type.item_type._type_affinity, Integer)
+
+
 
 class ExecuteTest(fixtures.TestBase):