Merge branch 'pr126'

Conflicts:
	doc/build/changelog/changelog_10.rst

5 files changed, 156 insertions, 24 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 1d501f85b..20023af44 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -26,6 +26,14 @@
         courtesy Malik Diarra.
 
     .. change::
+        :tags: postgresql, feature
+        :pullreq: github:126
+
+        Added new method :meth:`.PGInspector.get_enums`, when using the
+        inspector for Postgresql will provide a list of ENUM types.
+        Pull request courtesy Ilya Pekelny.
+
+    .. change::
         :tags: mysql, bug
 
         The MySQL dialect will now disable :meth:`.ConnectionEvents.handle_error`
diff --git a/lib/sqlalchemy/dialects/postgresql/__init__.py b/lib/sqlalchemy/dialects/postgresql/__init__.py
index d755e6aa1..1cff8e3a0 100644
--- a/lib/sqlalchemy/dialects/postgresql/__init__.py
+++ b/lib/sqlalchemy/dialects/postgresql/__init__.py
@@ -13,7 +13,7 @@ from .base import \
     INTEGER, BIGINT, SMALLINT, VARCHAR, CHAR, TEXT, NUMERIC, FLOAT, REAL, \
     INET, CIDR, UUID, BIT, MACADDR, OID, DOUBLE_PRECISION, TIMESTAMP, TIME, \
     DATE, BYTEA, BOOLEAN, INTERVAL, ARRAY, ENUM, dialect, array, Any, All, \
-    TSVECTOR
+    TSVECTOR, DropEnumType
 from .constraints import ExcludeConstraint
 from .hstore import HSTORE, hstore
 from .json import JSON, JSONElement, JSONB
@@ -26,5 +26,6 @@ __all__ = (
     'DOUBLE_PRECISION', 'TIMESTAMP', 'TIME', 'DATE', 'BYTEA', 'BOOLEAN',
     'INTERVAL', 'ARRAY', 'ENUM', 'dialect', 'Any', 'All', 'array', 'HSTORE',
     'hstore', 'INT4RANGE', 'INT8RANGE', 'NUMRANGE', 'DATERANGE',
-    'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'JSONElement'
+    'TSRANGE', 'TSTZRANGE', 'json', 'JSON', 'JSONB', 'JSONElement',
+    'DropEnumType'
 )
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 5ff2f7c61..c2b1d66f4 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -401,6 +401,23 @@ 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.
 
+Special Reflection Options
+--------------------------
+
+The :class:`.Inspector` used for the Postgresql backend is an instance
+of :class:`.PGInspector`, which offers additional methods::
+
+    from sqlalchemy import create_engine, inspect
+
+    engine = create_engine("postgresql+psycopg2://localhost/test")
+    insp = inspect(engine)  # will be a PGInspector
+
+    print(insp.get_enums())
+
+.. autoclass:: PGInspector
+    :members:
+
+
 """
 from collections import defaultdict
 import re
@@ -1570,11 +1587,32 @@ class PGInspector(reflection.Inspector):
         reflection.Inspector.__init__(self, conn)
 
     def get_table_oid(self, table_name, schema=None):
-        """Return the oid from `table_name` and `schema`."""
+        """Return the OID for the given table name."""
 
         return self.dialect.get_table_oid(self.bind, table_name, schema,
                                           info_cache=self.info_cache)
 
+    def get_enums(self, schema=None):
+        """Return a list of ENUM objects.
+
+        Each member is a dictionary containing these fields:
+
+            * name - name of the enum
+            * schema - the schema name for the enum.
+            * visible - boolean, whether or not this enum is visible
+              in the default search path.
+            * labels - a list of string labels that apply to the enum.
+
+        :param schema: schema name.  If None, the default schema
+         (typically 'public') is used.  May also be set to '*' to
+         indicate load enums for all schemas.
+
+        .. versionadded:: 1.0.0
+
+        """
+        schema = schema or self.default_schema_name
+        return self.dialect._load_enums(self.bind, schema)
+
 
 class CreateEnumType(schema._CreateDropBase):
     __visit_name__ = "create_enum_type"
@@ -2039,7 +2077,12 @@ class PGDialect(default.DefaultDialect):
         c = connection.execute(s, table_oid=table_oid)
         rows = c.fetchall()
         domains = self._load_domains(connection)
-        enums = self._load_enums(connection)
+        enums = dict(
+            (
+                "%s.%s" % (rec['schema'], rec['name'])
+                if not rec['visible'] else rec['name'], rec) for rec in
+            self._load_enums(connection, schema='*')
+        )
 
         # format columns
         columns = []
@@ -2113,10 +2156,9 @@ class PGDialect(default.DefaultDialect):
             elif attype in enums:
                 enum = enums[attype]
                 coltype = ENUM
-                if "." in attype:
-                    kwargs['schema'], kwargs['name'] = attype.split('.')
-                else:
-                    kwargs['name'] = attype
+                kwargs['name'] = enum['name']
+                if not enum['visible']:
+                    kwargs['schema'] = enum['schema']
                 args = tuple(enum['labels'])
                 break
             elif attype in domains:
@@ -2424,7 +2466,8 @@ class PGDialect(default.DefaultDialect):
             for name, uc in uniques.items()
         ]
 
-    def _load_enums(self, connection):
+    def _load_enums(self, connection, schema=None):
+        schema = schema or self.default_schema_name
         if not self.supports_native_enum:
             return {}
 
@@ -2440,31 +2483,37 @@ class PGDialect(default.DefaultDialect):
                  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
                  LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
             WHERE t.typtype = 'e'
-            ORDER BY "name", e.oid -- e.oid gives us label order
         """
 
+        if schema != '*':
+            SQL_ENUMS += "AND n.nspname = :schema "
+
+        # e.oid gives us label order within an enum
+        SQL_ENUMS += 'ORDER BY "schema", "name", e.oid'
+
         s = sql.text(SQL_ENUMS, typemap={
             'attname': sqltypes.Unicode,
             'label': sqltypes.Unicode})
+
+        if schema != '*':
+            s = s.bindparams(schema=schema)
+
         c = connection.execute(s)
 
-        enums = {}
+        enums = []
+        enum_by_name = {}
         for enum in c.fetchall():
-            if enum['visible']:
-                # 'visible' just means whether or not the enum is in a
-                # schema that's on the search path -- or not overridden by
-                # a schema with higher precedence. If it's not visible,
-                # it will be prefixed with the schema-name when it's used.
-                name = enum['name']
-            else:
-                name = "%s.%s" % (enum['schema'], enum['name'])
-
-            if name in enums:
-                enums[name]['labels'].append(enum['label'])
+            key = (enum['schema'], enum['name'])
+            if key in enum_by_name:
+                enum_by_name[key]['labels'].append(enum['label'])
             else:
-                enums[name] = {
+                enum_by_name[key] = enum_rec = {
+                    'name': enum['name'],
+                    'schema': enum['schema'],
+                    'visible': enum['visible'],
                     'labels': [enum['label']],
                 }
+                enums.append(enum_rec)
 
         return enums
 
diff --git a/lib/sqlalchemy/testing/plugin/plugin_base.py b/lib/sqlalchemy/testing/plugin/plugin_base.py
index 4c245e9e9..9c63a2e1d 100644
--- a/lib/sqlalchemy/testing/plugin/plugin_base.py
+++ b/lib/sqlalchemy/testing/plugin/plugin_base.py
@@ -315,7 +315,7 @@ def _setup_requirements(argument):
 @post
 def _prep_testing_database(options, file_config):
     from sqlalchemy.testing import config
-    from sqlalchemy import schema, inspect
+    from sqlalchemy import schema, inspect, testing
 
     if options.dropfirst:
         for cfg in config.Config.all_configs():
@@ -358,6 +358,14 @@ def _prep_testing_database(options, file_config):
                                      schema="test_schema")
                     ))
 
+            if testing.against("postgresql"):
+                from sqlalchemy.dialects import postgresql
+                for enum in inspector.get_enums("*"):
+                    e.execute(postgresql.DropEnumType(
+                        postgresql.ENUM(
+                            name=enum['name'],
+                            schema=enum['schema'])))
+
 
 @post
 def _set_table_options(options, file_config):
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index 1d6a41765..bab41b0f7 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -1,5 +1,6 @@
 # coding: utf-8
 
+from sqlalchemy.engine import reflection
 from sqlalchemy.testing.assertions import eq_, assert_raises, \
     AssertsExecutionResults
 from sqlalchemy.testing import fixtures
@@ -622,6 +623,71 @@ class ReflectionTest(fixtures.TestBase):
         for fk in fks:
             eq_(fk, fk_ref[fk['name']])
 
+    @testing.provide_metadata
+    def test_inspect_enums_schema(self):
+        conn = testing.db.connect()
+        enum_type = postgresql.ENUM(
+            'sad', 'ok', 'happy', name='mood',
+            schema='test_schema',
+            metadata=self.metadata)
+        enum_type.create(conn)
+        inspector = reflection.Inspector.from_engine(conn.engine)
+        eq_(
+            inspector.get_enums('test_schema'), [{
+                'visible': False,
+                'name': 'mood',
+                'schema': 'test_schema',
+                'labels': ['sad', 'ok', 'happy']
+            }])
+
+    @testing.provide_metadata
+    def test_inspect_enums(self):
+        enum_type = postgresql.ENUM(
+            'cat', 'dog', 'rat', name='pet', metadata=self.metadata)
+        enum_type.create(testing.db)
+        inspector = reflection.Inspector.from_engine(testing.db)
+        eq_(inspector.get_enums(), [
+            {
+                'visible': True,
+                'labels': ['cat', 'dog', 'rat'],
+                'name': 'pet',
+                'schema': 'public'
+            }])
+
+    @testing.provide_metadata
+    def test_inspect_enums_star(self):
+        enum_type = postgresql.ENUM(
+            'cat', 'dog', 'rat', name='pet', metadata=self.metadata)
+        schema_enum_type = postgresql.ENUM(
+            'sad', 'ok', 'happy', name='mood',
+            schema='test_schema',
+            metadata=self.metadata)
+        enum_type.create(testing.db)
+        schema_enum_type.create(testing.db)
+        inspector = reflection.Inspector.from_engine(testing.db)
+
+        eq_(inspector.get_enums(), [
+            {
+                'visible': True,
+                'labels': ['cat', 'dog', 'rat'],
+                'name': 'pet',
+                'schema': 'public'
+            }])
+
+        eq_(inspector.get_enums('*'), [
+            {
+                'visible': True,
+                'labels': ['cat', 'dog', 'rat'],
+                'name': 'pet',
+                'schema': 'public'
+            },
+            {
+                'visible': False,
+                'name': 'mood',
+                'schema': 'test_schema',
+                'labels': ['sad', 'ok', 'happy']
+            }])
+
 
 class CustomTypeReflectionTest(fixtures.TestBase):