- changelog, migration for pr github:134

7 files changed, 181 insertions, 93 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 4d5ab1f06..a746abeac 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -22,6 +22,18 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: feature, postgresql
+        :pullreq: github:134
+
+        Added support for the FILTER keyword as applied to aggregate
+        functions, supported by Postgresql 9.4.   Pull request
+        courtesy Ilja Everilä.
+
+        .. seealso::
+
+            :ref:`feature_gh134`
+
+    .. change::
         :tags: bug, sql, engine
         :tickets: 3215
 
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index 0e9dd8d7b..b0ac868ec 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -1026,6 +1026,22 @@ running 0.9 in production.
 
 :ticket:`2891`
 
+.. _feature_gh134:
+
+Postgresql FILTER keyword
+-------------------------
+
+The SQL standard FILTER keyword for aggregate functions is now supported
+by Postgresql as of 9.4.  SQLAlchemy allows this using
+:meth:`.FunctionElement.filter`::
+
+    func.count(1).filter(True)
+
+.. seealso::
+
+    :meth:`.FunctionElement.filter`
+
+    :class:`.FunctionFilter`
 
 MySQL internal "no such table" exceptions not passed to event handlers
 ----------------------------------------------------------------------
diff --git a/doc/build/core/sqlelement.rst b/doc/build/core/sqlelement.rst
index 61600e927..44a969dbb 100644
--- a/doc/build/core/sqlelement.rst
+++ b/doc/build/core/sqlelement.rst
@@ -35,6 +35,8 @@ used to construct any kind of typed SQL expression.
 
 .. autodata:: func
 
+.. autofunction:: funcfilter
+
 .. autofunction:: label
 
 .. autofunction:: literal
@@ -109,6 +111,9 @@ used to construct any kind of typed SQL expression.
 .. autoclass:: sqlalchemy.sql.elements.False_
    :members:
 
+.. autoclass:: FunctionFilter
+   :members:
+
 .. autoclass:: Label
    :members:
 
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 53838358d..db14031d2 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2895,6 +2895,17 @@ class FunctionFilter(ColumnElement):
     which controls which rows are passed to it.
     It's supported only by certain database backends.
 
+    Invocation of :class:`.FunctionFilter` is via
+    :meth:`.FunctionElement.filter`::
+
+        func.count(1).filter(True)
+
+    .. versionadded:: 1.0.0
+
+    .. seealso::
+
+        :meth:`.FunctionElement.filter`
+
     """
     __visit_name__ = 'funcfilter'
 
@@ -2916,11 +2927,29 @@ class FunctionFilter(ColumnElement):
         This function is also available from the :data:`~.expression.func`
         construct itself via the :meth:`.FunctionElement.filter` method.
 
+        .. versionadded:: 1.0.0
+
+        .. seealso::
+
+            :meth:`.FunctionElement.filter`
+
+
         """
         self.func = func
         self.filter(*criterion)
 
     def filter(self, *criterion):
+        """Produce an additional FILTER against the function.
+
+        This method adds additional criteria to the initial criteria
+        set up by :meth:`.FunctionElement.filter`.
+
+        Multiple criteria are joined together at SQL render time
+        via ``AND``.
+
+
+        """
+
         for criterion in list(criterion):
             criterion = _expression_literal_as_text(criterion)
 
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index a07eca8c6..9280c7d60 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -131,7 +131,14 @@ class FunctionElement(Executable, ColumnElement, FromClause):
             from sqlalchemy import funcfilter
             funcfilter(func.count(1), True)
 
-        See :func:`~.expression.funcfilter` for a full description.
+        .. versionadded:: 1.0.0
+
+        .. seealso::
+
+            :class:`.FunctionFilter`
+
+            :func:`.funcfilter`
+
 
         """
         if not criterion:
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index ed13e8455..3e6b87351 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2190,97 +2190,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable"
         )
 
-    def test_funcfilter(self):
-        self.assert_compile(
-            func.count(1).filter(),
-            "count(:param_1)"
-        )
-        self.assert_compile(
-            func.count(1).filter(
-                table1.c.name != None
-            ),
-            "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)"
-        )
-        self.assert_compile(
-            func.count(1).filter(
-                table1.c.name == None,
-                table1.c.myid > 0
-            ),
-            "count(:param_1) FILTER (WHERE mytable.name IS NULL AND "
-            "mytable.myid > :myid_1)"
-        )
-
-        self.assert_compile(
-            select([func.count(1).filter(
-                table1.c.description != None
-            ).label('foo')]),
-            "SELECT count(:param_1) FILTER (WHERE mytable.description "
-            "IS NOT NULL) AS foo FROM mytable"
-        )
-
-        # test from_obj generation.
-        # from func:
-        self.assert_compile(
-            select([
-                func.max(table1.c.name).filter(
-                    literal_column('description') != None
-                )
-            ]),
-            "SELECT max(mytable.name) FILTER (WHERE description "
-            "IS NOT NULL) AS anon_1 FROM mytable"
-        )
-        # from criterion:
-        self.assert_compile(
-            select([
-                func.count(1).filter(
-                    table1.c.name == 'name'
-                )
-            ]),
-            "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) "
-            "AS anon_1 FROM mytable"
-        )
-
-        # test chaining:
-        self.assert_compile(
-            select([
-                func.count(1).filter(
-                    table1.c.name == 'name'
-                ).filter(
-                    table1.c.description == 'description'
-                )
-            ]),
-            "SELECT count(:param_1) FILTER (WHERE "
-            "mytable.name = :name_1 AND mytable.description = :description_1) "
-            "AS anon_1 FROM mytable"
-        )
-
-        # test filtered windowing:
-        self.assert_compile(
-            select([
-                func.rank().filter(
-                    table1.c.name > 'foo'
-                ).over(
-                    order_by=table1.c.name
-                )
-            ]),
-            "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
-            "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable"
-        )
-
-        self.assert_compile(
-            select([
-                func.rank().filter(
-                    table1.c.name > 'foo'
-                ).over(
-                    order_by=table1.c.name,
-                    partition_by=['description']
-                )
-            ]),
-            "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
-            "OVER (PARTITION BY mytable.description ORDER BY mytable.name) "
-            "AS anon_1 FROM mytable"
-        )
-
     def test_date_between(self):
         import datetime
         table = Table('dt', metadata,
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index 9b7649e63..ec8d9b5c0 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -1,7 +1,8 @@
 from sqlalchemy.testing import eq_
 import datetime
 from sqlalchemy import func, select, Integer, literal, DateTime, Table, \
-    Column, Sequence, MetaData, extract, Date, String, bindparam
+    Column, Sequence, MetaData, extract, Date, String, bindparam, \
+    literal_column
 from sqlalchemy.sql import table, column
 from sqlalchemy import sql, util
 from sqlalchemy.sql.compiler import BIND_TEMPLATES
@@ -15,6 +16,13 @@ from sqlalchemy.testing import fixtures, AssertsCompiledSQL, engines
 from sqlalchemy.dialects import sqlite, postgresql, mysql, oracle
 
 
+table1 = table('mytable',
+               column('myid', Integer),
+               column('name', String),
+               column('description', String),
+               )
+
+
 class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = 'default'
 
@@ -367,6 +375,108 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
         expr = func.rows("foo").alias('bar')
         assert len(expr.c)
 
+    def test_funcfilter_empty(self):
+        self.assert_compile(
+            func.count(1).filter(),
+            "count(:param_1)"
+        )
+
+    def test_funcfilter_criterion(self):
+        self.assert_compile(
+            func.count(1).filter(
+                table1.c.name != None
+            ),
+            "count(:param_1) FILTER (WHERE mytable.name IS NOT NULL)"
+        )
+
+    def test_funcfilter_compound_criterion(self):
+        self.assert_compile(
+            func.count(1).filter(
+                table1.c.name == None,
+                table1.c.myid > 0
+            ),
+            "count(:param_1) FILTER (WHERE mytable.name IS NULL AND "
+            "mytable.myid > :myid_1)"
+        )
+
+    def test_funcfilter_label(self):
+        self.assert_compile(
+            select([func.count(1).filter(
+                table1.c.description != None
+            ).label('foo')]),
+            "SELECT count(:param_1) FILTER (WHERE mytable.description "
+            "IS NOT NULL) AS foo FROM mytable"
+        )
+
+    def test_funcfilter_fromobj_fromfunc(self):
+        # test from_obj generation.
+        # from func:
+        self.assert_compile(
+            select([
+                func.max(table1.c.name).filter(
+                    literal_column('description') != None
+                )
+            ]),
+            "SELECT max(mytable.name) FILTER (WHERE description "
+            "IS NOT NULL) AS anon_1 FROM mytable"
+        )
+
+    def test_funcfilter_fromobj_fromcriterion(self):
+        # from criterion:
+        self.assert_compile(
+            select([
+                func.count(1).filter(
+                    table1.c.name == 'name'
+                )
+            ]),
+            "SELECT count(:param_1) FILTER (WHERE mytable.name = :name_1) "
+            "AS anon_1 FROM mytable"
+        )
+
+    def test_funcfilter_chaining(self):
+        # test chaining:
+        self.assert_compile(
+            select([
+                func.count(1).filter(
+                    table1.c.name == 'name'
+                ).filter(
+                    table1.c.description == 'description'
+                )
+            ]),
+            "SELECT count(:param_1) FILTER (WHERE "
+            "mytable.name = :name_1 AND mytable.description = :description_1) "
+            "AS anon_1 FROM mytable"
+        )
+
+    def test_funcfilter_windowing_orderby(self):
+        # test filtered windowing:
+        self.assert_compile(
+            select([
+                func.rank().filter(
+                    table1.c.name > 'foo'
+                ).over(
+                    order_by=table1.c.name
+                )
+            ]),
+            "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
+            "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable"
+        )
+
+    def test_funcfilter_windowing_orderby_partitionby(self):
+        self.assert_compile(
+            select([
+                func.rank().filter(
+                    table1.c.name > 'foo'
+                ).over(
+                    order_by=table1.c.name,
+                    partition_by=['description']
+                )
+            ]),
+            "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
+            "OVER (PARTITION BY mytable.description ORDER BY mytable.name) "
+            "AS anon_1 FROM mytable"
+        )
+
 
 class ExecuteTest(fixtures.TestBase):