From bf2f3595d07002d56c77b329387932d4d27e3c45 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Sun, 2 Oct 2011 17:17:46 -0400
Subject: [PATCH]   - Added "postgresql_using" argument to Index(), produces   
  USING clause to specify index implementation for     PG.  [ticket:2290]. 
 Thanks to Ryan P. Kelly for     the patch.

---
 CHANGES                                    |  5 ++++
 lib/sqlalchemy/dialects/postgresql/base.py | 27 +++++++++++++++++++---
 test/dialect/test_postgresql.py            | 21 +++++++++++++++++
 3 files changed, 50 insertions(+), 3 deletions(-)

diff --git a/CHANGES b/CHANGES
index 16333d494b..f4f846ff9f 100644
--- a/CHANGES
+++ b/CHANGES
@@ -214,6 +214,11 @@ CHANGES
     extensions are in use or not.
 
 - postgresql
+  - Added "postgresql_using" argument to Index(), produces
+    USING clause to specify index implementation for
+    PG.  [ticket:2290].  Thanks to Ryan P. Kelly for
+    the patch.
+
   - Reflection functions for Table, Sequence no longer
     case insensitive.  Names can be differ only in case
     and will be correctly distinguished.  [ticket:2256]
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 8c3babd313..b3be7bc998 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -141,6 +141,20 @@ 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.
 
+Index Types
+^^^^^^^^^^^^
+
+PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well as
+the ability for users to create their own (see
+http://www.postgresql.org/docs/8.3/static/indexes-types.html). These can be
+specified on :class:`.Index` using the ``postgresql_using`` keyword argument::
+
+    Index('my_index', my_table.c.data, postgresql_using='gin')
+
+The value passed to the keyword argument will be simply passed through to the
+underlying CREATE INDEX command, so it *must* be a valid index type for your
+version of PostgreSQL.
+
 """
 
 import re
@@ -629,11 +643,18 @@ class PGDDLCompiler(compiler.DDLCompiler):
         if index.unique:
             text += "UNIQUE "
         ops = index.kwargs.get('postgresql_ops', {})
-        text += "INDEX %s ON %s (%s)" \
-                % (
+        text += "INDEX %s ON %s " % (
                     preparer.quote(
                         self._index_identifier(index.name), index.quote),
-                    preparer.format_table(index.table),
+                    preparer.format_table(index.table)
+                )
+
+        if 'postgresql_using' in index.kwargs:
+            using = index.kwargs['postgresql_using']
+            text += "USING %s " % preparer.quote(using, index.quote)
+
+        text += "(%s)" \
+                % (
                     ', '.join([
                         preparer.format_column(c) + 
                         (c.key in ops and (' ' + ops[c.key]) or '')
diff --git a/test/dialect/test_postgresql.py b/test/dialect/test_postgresql.py
index adf292999d..8f90becd59 100644
--- a/test/dialect/test_postgresql.py
+++ b/test/dialect/test_postgresql.py
@@ -174,6 +174,27 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
                             '(data text_pattern_ops, data2 int4_ops)',
                             dialect=postgresql.dialect())
 
+    def test_create_index_with_using(self):
+        m = MetaData()
+        tbl = Table('testtbl', m, Column('data', String))
+
+        idx1 = Index('test_idx1', tbl.c.data)
+        idx2 = Index('test_idx2', tbl.c.data, postgresql_using='btree')
+        idx3 = Index('test_idx3', tbl.c.data, postgresql_using='hash')
+
+        self.assert_compile(schema.CreateIndex(idx1),
+                            'CREATE INDEX test_idx1 ON testtbl '
+                            '(data)',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx2),
+                            'CREATE INDEX test_idx2 ON testtbl '
+                            'USING btree (data)',
+                            dialect=postgresql.dialect())
+        self.assert_compile(schema.CreateIndex(idx3),
+                            'CREATE INDEX test_idx3 ON testtbl '
+                            'USING hash (data)',
+                            dialect=postgresql.dialect())
+
     @testing.uses_deprecated(r".*'postgres_where' argument has been "
                              "renamed.*")
     def test_old_create_partial_index(self):
-- 
2.47.3