- Added support for reflecting the source of materialized views

to the Postgresql version of the :meth:`.Inspector.get_view_definition`
method.
fixes #3587

3 files changed, 39 insertions, 5 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 9ce3975c2..b2012f4ac 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,14 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: bug, postgresql
+        :tickets: 3587
+
+        Added support for reflecting the source of materialized views
+        to the Postgresql version of the :meth:`.Inspector.get_view_definition`
+        method.
+
+    .. change::
         :tags: bug, orm
         :tickets: 3582
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index abc1b65d9..ef870a177 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1961,11 +1961,27 @@ class PGDialect(default.DefaultDialect):
             current_schema = schema
         else:
             current_schema = self.default_schema_name
-        s = """
-        SELECT definition FROM pg_views
-        WHERE schemaname = :schema
-        AND viewname = :view_name
-        """
+
+        if self.server_version_info >= (9, 3):
+            s = """
+            SELECT definition FROM pg_views
+            WHERE schemaname = :schema
+            AND viewname = :view_name
+
+            UNION
+
+            SELECT definition FROM pg_matviews
+            WHERE schemaname = :schema
+            AND matviewname = :view_name
+
+            """
+        else:
+            s = """
+            SELECT definition FROM pg_views
+            WHERE schemaname = :schema
+            AND viewname = :view_name
+            """
+
         rp = connection.execute(sql.text(s),
                                 view_name=view_name, schema=current_schema)
         if rp:
diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py
index ee87e7325..851facd2a 100644
--- a/test/dialect/postgresql/test_reflection.py
+++ b/test/dialect/postgresql/test_reflection.py
@@ -13,6 +13,7 @@ from sqlalchemy import exc
 import sqlalchemy as sa
 from sqlalchemy.dialects.postgresql import base as postgresql
 from sqlalchemy.dialects.postgresql import ARRAY
+import re
 
 
 class ForeignTableReflectionTest(fixtures.TablesTest, AssertsExecutionResults):
@@ -130,6 +131,15 @@ class MaterializedViewReflectionTest(
         insp = inspect(testing.db)
         eq_(set(insp.get_view_names()), set(['test_mview', 'test_regview']))
 
+    def test_get_view_definition(self):
+        insp = inspect(testing.db)
+        eq_(
+            re.sub(
+                r'[\n\t ]+', ' ',
+                insp.get_view_definition("test_mview").strip()),
+            "SELECT testtable.id, testtable.data FROM testtable;"
+        )
+
 
 class DomainReflectionTest(fixtures.TestBase, AssertsExecutionResults):
     """Test PostgreSQL domains"""