test / document postgresql_ops against a labeled expression

Since postgresql_ops explicitly states that it expects
string keys, to apply to a function call or expression one
needs to give the SQL expression a label that can be referred
to by name in the dictionary.   test / document this.

Change-Id: I4bc4ade46dac27f9c1b92e7823433292beab97b9
Fixes: #3970

2 files changed, 45 insertions, 8 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 802c6a905..b9c15ca2d 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -566,20 +566,31 @@ http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html).
 The :class:`.Index` construct allows these to be specified via the
 ``postgresql_ops`` keyword argument::
 
-    Index('my_index', my_table.c.id, my_table.c.data,
-                            postgresql_ops={
-                                'data': 'text_pattern_ops',
-                                'id': 'int4_ops'
-                            })
-
-.. versionadded:: 0.7.2
-    ``postgresql_ops`` keyword argument to :class:`.Index` construct.
+    Index(
+        'my_index', my_table.c.id, my_table.c.data,
+        postgresql_ops={
+            'data': 'text_pattern_ops',
+            'id': 'int4_ops'
+        })
 
 Note that the keys in the ``postgresql_ops`` dictionary are the "key" name of
 the :class:`.Column`, i.e. the name used to access it from the ``.c``
 collection of :class:`.Table`, which can be configured to be different than
 the actual name of the column as expressed in the database.
 
+If ``postgresql_ops`` is to be used against a complex SQL expression such
+as a function call, then to apply to the column it must be given a label
+that is identified in the dictionary by name, e.g.::
+
+    Index(
+        'my_index', my_table.c.id,
+        func.lower(my_table.c.data).label('data_lower'),
+        postgresql_ops={
+            'data_lower': 'text_pattern_ops',
+            'id': 'int4_ops'
+        })
+
+
 Index Types
 ^^^^^^^^^^^^
 
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index eddf20877..0533016cd 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -316,6 +316,32 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             '(data text_pattern_ops, data2 int4_ops)',
                             dialect=postgresql.dialect())
 
+    def test_create_index_with_labeled_ops(self):
+        m = MetaData()
+        tbl = Table('testtbl', m,
+                    Column('data', String),
+                    Column('data2', Integer, key='d2'))
+
+        idx = Index('test_idx1', func.lower(tbl.c.data).label('data_lower'),
+                    postgresql_ops={'data_lower': 'text_pattern_ops'})
+
+        idx2 = Index(
+            'test_idx2',
+            (func.xyz(tbl.c.data) + tbl.c.d2).label('bar'),
+            tbl.c.d2.label('foo'),
+            postgresql_ops={'bar': 'text_pattern_ops',
+                            'foo': 'int4_ops'})
+
+        self.assert_compile(schema.CreateIndex(idx),
+                            'CREATE INDEX test_idx1 ON testtbl '
+                            '(lower(data) text_pattern_ops)',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx2),
+                            'CREATE INDEX test_idx2 ON testtbl '
+                            '((xyz(data) + data2) text_pattern_ops, '
+                            'data2 int4_ops)',
+                            dialect=postgresql.dialect())
+
     def test_create_index_with_text_or_composite(self):
         m = MetaData()
         tbl = Table('testtbl', m,