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

7 files changed, 132 insertions, 3 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 350a7c4d2..09d9e0958 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -44,7 +44,9 @@
         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.
+        Postgresql.  Also added a new construct
+        :class:`.postgresql.aggregate_order_by` in support of PG's
+        "ORDER BY" extension.
 
         .. seealso::
 
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index c146e2443..6e37fb04f 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -275,6 +275,17 @@ which is now available using :class:`.array_agg`::
     from sqlalchemy import func
     stmt = select([func.array_agg(table.c.value)])
 
+A Postgresql element for an aggreagte ORDER BY is also added via
+:class:`.postgresql.aggregate_order_by`::
+
+    from sqlalchemy.dialects.postgresql import aggregate_order_by
+    expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
+    stmt = select([expr])
+
+Producing::
+
+    SELECT array_agg(table1.a ORDER BY table1.b DESC) AS array_agg_1 FROM table1
+
 Additionally, functions like ``percentile_cont()``, ``percentile_disc()``,
 ``rank()``, ``dense_rank()`` and others that require an ordering via
 ``WITHIN GROUP (ORDER BY <expr>)`` are now available via the
diff --git a/doc/build/dialects/postgresql.rst b/doc/build/dialects/postgresql.rst
index efe59f7aa..facb2646e 100644
--- a/doc/build/dialects/postgresql.rst
+++ b/doc/build/dialects/postgresql.rst
@@ -24,6 +24,8 @@ construction arguments, are as follows:
 
 .. currentmodule:: sqlalchemy.dialects.postgresql
 
+.. autoclass:: aggregate_order_by
+
 .. autoclass:: array
 
 .. autoclass:: ARRAY
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py
index 28f66f9cb..538a2e800 100644
--- a/lib/sqlalchemy/dialects/postgresql/__init__.py
+++ b/lib/sqlalchemy/dialects/postgresql/__init__.py
@@ -18,6 +18,7 @@ from .constraints import ExcludeConstraint
 from .hstore import HSTORE, hstore
 from .json import JSON, JSONB
 from .array import array, ARRAY, Any, All
+from .ext import aggregate_order_by
 
 from .ranges import INT4RANGE, INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, \
     TSTZRANGE
@@ -29,5 +30,5 @@ __all__ = (
     'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'array', 'HSTORE',
     'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE',
     'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'Any', 'All',
-    'DropEnumType', 'CreateEnumType', 'ExcludeConstraint'
+    'DropEnumType', 'CreateEnumType', 'ExcludeConstraint', 'aggregate_order_by'
 )
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 1548b34d9..4022db14b 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1051,6 +1051,12 @@ class PGCompiler(compiler.SQLCompiler):
             self.process(binary.right, **kw)
         )
 
+    def visit_aggregate_order_by(self, element, **kw):
+        return "%s ORDER BY %s" % (
+            self.process(element.target, **kw),
+            self.process(element.order_by, **kw)
+        )
+
     def visit_match_op_binary(self, binary, operator, **kw):
         if "postgresql_regconfig" in binary.modifiers:
             regconfig = self.render_literal_value(
diff --git a/lib/sqlalchemy/dialects/postgresql/ext.py b/lib/sqlalchemy/dialects/postgresql/ext.py
new file mode 100644
index 000000000..57592bac2
--- /dev/null
+++ b/lib/sqlalchemy/dialects/postgresql/ext.py
@@ -0,0 +1,63 @@
+# postgresql/ext.py
+# Copyright (C) 2005-2015 the SQLAlchemy authors and contributors
+# <see AUTHORS file>
+#
+# This module is part of SQLAlchemy and is released under
+# the MIT License: http://www.opensource.org/licenses/mit-license.php
+
+from ...sql import expression
+from ...sql import elements
+
+
+class aggregate_order_by(expression.ColumnElement):
+    """Represent a Postgresql aggregate order by expression.
+
+    E.g.::
+
+        from sqlalchemy.dialects.postgresql import aggregate_order_by
+        expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
+        stmt = select([expr])
+
+    would represent the expression::
+
+        SELECT array_agg(a ORDER BY b DESC) FROM table;
+
+    Similarly::
+
+        expr = func.string_agg(
+            table.c.a,
+            aggregate_order_by(literal_column("','"), table.c.a)
+        )
+        stmt = select([expr])
+
+    Would represent::
+
+        SELECT string_agg(a, ',' ORDER BY a) FROM table;
+
+    .. versionadded:: 1.1
+
+    .. seealso::
+
+        :class:`.array_agg`
+
+    """
+
+    __visit_name__ = 'aggregate_order_by'
+
+    def __init__(self, target, order_by):
+        self.target = elements._literal_as_binds(target)
+        self.order_by = elements._literal_as_binds(order_by)
+
+    def self_group(self, against=None):
+        return self
+
+    def get_children(self, **kwargs):
+        return self.target, self.order_by
+
+    def _copy_internals(self, clone=elements._clone, **kw):
+        self.target = clone(self.target, **kw)
+        self.order_by = clone(self.order_by, **kw)
+
+    @property
+    def _from_objects(self):
+        return self.target._from_objects + self.order_by._from_objects
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 0407dcb81..78217bd82 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -12,8 +12,10 @@ from sqlalchemy import exc, schema
 from sqlalchemy.dialects import postgresql
 from sqlalchemy.dialects.postgresql import TSRANGE
 from sqlalchemy.orm import mapper, aliased, Session
-from sqlalchemy.sql import table, column, operators
+from sqlalchemy.sql import table, column, operators, literal_column
+from sqlalchemy.sql import util as sql_util
 from sqlalchemy.util import u
+from sqlalchemy.dialects.postgresql import aggregate_order_by
 
 
 class SequenceTest(fixtures.TestBase, AssertsCompiledSQL):
@@ -800,6 +802,48 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=postgresql.dialect()
         )
 
+    def test_aggregate_order_by_one(self):
+        m = MetaData()
+        table = Table('table1', m, Column('a', Integer), Column('b', Integer))
+        expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
+        stmt = select([expr])
+
+        # note this tests that the object exports FROM objects
+        # correctly
+        self.assert_compile(
+            stmt,
+            "SELECT array_agg(table1.a ORDER BY table1.b DESC) "
+            "AS array_agg_1 FROM table1"
+        )
+
+    def test_aggregate_order_by_two(self):
+        m = MetaData()
+        table = Table('table1', m, Column('a', Integer), Column('b', Integer))
+        expr = func.string_agg(
+            table.c.a,
+            aggregate_order_by(literal_column("','"), table.c.a)
+        )
+        stmt = select([expr])
+
+        self.assert_compile(
+            stmt,
+            "SELECT string_agg(table1.a, ',' ORDER BY table1.a) "
+            "AS string_agg_1 FROM table1"
+        )
+
+    def test_aggregate_order_by_adapt(self):
+        m = MetaData()
+        table = Table('table1', m, Column('a', Integer), Column('b', Integer))
+        expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc()))
+        stmt = select([expr])
+
+        a1 = table.alias('foo')
+        stmt2 = sql_util.ClauseAdapter(a1).traverse(stmt)
+        self.assert_compile(
+            stmt2,
+            "SELECT array_agg(foo.a ORDER BY foo.b DESC) AS array_agg_1 FROM table1 AS foo"
+        )
+
 
 class DistinctOnTest(fixtures.TestBase, AssertsCompiledSQL):