Merge "Only convert Range for sqlalchemy Range object" into main

7 files changed, 86 insertions, 23 deletions

diff --git a/doc/build/changelog/changelog_20.rst b/doc/build/changelog/changelog_20.rst
index 19427c9b4..e3470f028 100644
--- a/doc/build/changelog/changelog_20.rst
+++ b/doc/build/changelog/changelog_20.rst
@@ -514,6 +514,8 @@
 
         .. seealso::
 
+            :ref:`change_7156`
+
             :ref:`postgresql_ranges`
 
     .. change::
diff --git a/doc/build/changelog/unreleased_20/8690.rst b/doc/build/changelog/unreleased_20/8690.rst
new file mode 100644
index 000000000..d4ae25b82
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/8690.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: usecase, postgresql
+    :tickets: 8690
+
+    Refined the new approach to range objects described at :ref:`change_7156`
+    to accommodate driver-specific range and multirange objects, to better
+    accommodate both legacy code as well as when passing results from raw SQL
+    result sets back into new range or multirange expressions.
diff --git a/doc/build/changelog/whatsnew_20.rst b/doc/build/changelog/whatsnew_20.rst
index abdebab5c..98865f233 100644
--- a/doc/build/changelog/whatsnew_20.rst
+++ b/doc/build/changelog/whatsnew_20.rst
@@ -1868,6 +1868,29 @@ the :meth:`_types.TypeEngine.with_variant` method as follows::
         Column("value", Float(5).with_variant(oracle.FLOAT(16), "oracle")),
     )
 
+.. _change_7156:
+
+New RANGE / MULTIRANGE support and changes for PostgreSQL backends
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+RANGE / MULTIRANGE support has been fully implemented for psycopg2, psycopg3,
+and asyncpg dialects.  The new support uses a new SQLAlchemy-specific
+:class:`_postgresql.Range` object that is agnostic of the different backends
+and does not require the use of backend-specific imports or extension
+steps.  For multirange support, lists of :class:`_postgresql.Range`
+objects are used.
+
+Code that used the previous psycopg2-specific types should be modified
+to use :class:`_postgresql.Range`, which presents a compatible interface.
+
+See the documentation at :ref:`postgresql_ranges` for background on
+using the new feature.
+
+
+.. seealso::
+
+    :ref:`postgresql_ranges`
+
 .. _change_7086:
 
 ``match()`` operator on PostgreSQL uses ``plainto_tsquery()`` rather than ``to_tsquery()``
diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
index 96bac59d9..cd161d28e 100644
--- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py
+++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
@@ -293,13 +293,11 @@ class AsyncpgCHAR(sqltypes.CHAR):
 
 class _AsyncpgRange(ranges.AbstractRangeImpl):
     def bind_processor(self, dialect):
-        Range = dialect.dbapi.asyncpg.Range
-
-        NoneType = type(None)
+        asyncpg_Range = dialect.dbapi.asyncpg.Range
 
         def to_range(value):
-            if not isinstance(value, (str, NoneType)):
-                value = Range(
+            if isinstance(value, ranges.Range):
+                value = asyncpg_Range(
                     value.lower,
                     value.upper,
                     lower_inc=value.bounds[0] == "[",
@@ -328,7 +326,7 @@ class _AsyncpgRange(ranges.AbstractRangeImpl):
 
 class _AsyncpgMultiRange(ranges.AbstractMultiRangeImpl):
     def bind_processor(self, dialect):
-        Range = dialect.dbapi.asyncpg.Range
+        asyncpg_Range = dialect.dbapi.asyncpg.Range
 
         NoneType = type(None)
 
@@ -337,8 +335,8 @@ class _AsyncpgMultiRange(ranges.AbstractMultiRangeImpl):
                 return value
 
             def to_range(value):
-                if not isinstance(value, (str, NoneType)):
-                    value = Range(
+                if isinstance(value, ranges.Range):
+                    value = asyncpg_Range(
                         value.lower,
                         value.upper,
                         lower_inc=value.bounds[0] == "[",
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py
index 7ca274e2c..400c3186e 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py
@@ -164,13 +164,11 @@ class _PGBoolean(sqltypes.Boolean):
 
 class _PsycopgRange(ranges.AbstractRangeImpl):
     def bind_processor(self, dialect):
-        Range = cast(PGDialect_psycopg, dialect)._psycopg_Range
-
-        NoneType = type(None)
+        psycopg_Range = cast(PGDialect_psycopg, dialect)._psycopg_Range
 
         def to_range(value):
-            if not isinstance(value, (str, NoneType)):
-                value = Range(
+            if isinstance(value, ranges.Range):
+                value = psycopg_Range(
                     value.lower, value.upper, value.bounds, value.empty
                 )
             return value
@@ -193,18 +191,20 @@ class _PsycopgRange(ranges.AbstractRangeImpl):
 
 class _PsycopgMultiRange(ranges.AbstractMultiRangeImpl):
     def bind_processor(self, dialect):
-        Range = cast(PGDialect_psycopg, dialect)._psycopg_Range
-        Multirange = cast(PGDialect_psycopg, dialect)._psycopg_Multirange
+        psycopg_Range = cast(PGDialect_psycopg, dialect)._psycopg_Range
+        psycopg_Multirange = cast(
+            PGDialect_psycopg, dialect
+        )._psycopg_Multirange
 
         NoneType = type(None)
 
         def to_range(value):
-            if isinstance(value, (str, NoneType)):
+            if isinstance(value, (str, NoneType, psycopg_Multirange)):
                 return value
 
-            return Multirange(
+            return psycopg_Multirange(
                 [
-                    Range(
+                    psycopg_Range(
                         element.lower,
                         element.upper,
                         element.bounds,
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 350f4b616..4a8df5b5e 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -503,16 +503,14 @@ class _Psycopg2Range(ranges.AbstractRangeImpl):
     _psycopg2_range_cls = "none"
 
     def bind_processor(self, dialect):
-        Range = getattr(
+        psycopg2_Range = getattr(
             cast(PGDialect_psycopg2, dialect)._psycopg2_extras,
             self._psycopg2_range_cls,
         )
 
-        NoneType = type(None)
-
         def to_range(value):
-            if not isinstance(value, (str, NoneType)):
-                value = Range(
+            if isinstance(value, ranges.Range):
+                value = psycopg2_Range(
                     value.lower, value.upper, value.bounds, value.empty
                 )
             return value
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index 1f93a4023..91eada9a8 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -3891,6 +3891,23 @@ class _RangeTypeRoundTrip(fixtures.TablesTest):
         cols = insp.get_columns("data_table")
         assert isinstance(cols[0]["type"], self._col_type)
 
+    def test_textual_round_trip_w_dialect_type(self, connection):
+        """test #8690"""
+        data_table = self.tables.data_table
+
+        data_obj = self._data_obj()
+        connection.execute(
+            self.tables.data_table.insert(), {"range": data_obj}
+        )
+
+        q1 = text("SELECT range from data_table")
+        v = connection.scalar(q1)
+
+        q2 = select(data_table).where(data_table.c.range == v)
+        v2 = connection.scalar(q2)
+
+        eq_(data_obj, v2)
+
     def _assert_data(self, conn):
         data = conn.execute(select(self.tables.data_table.c.range)).fetchall()
         eq_(data, [(self._data_obj(),)])
@@ -4348,6 +4365,23 @@ class _MultiRangeTypeRoundTrip(fixtures.TablesTest):
         data = conn.execute(select(self.tables.data_table.c.range)).fetchall()
         eq_(data, [(self._data_obj(),)])
 
+    def test_textual_round_trip_w_dialect_type(self, connection):
+        """test #8690"""
+        data_table = self.tables.data_table
+
+        data_obj = self._data_obj()
+        connection.execute(
+            self.tables.data_table.insert(), {"range": data_obj}
+        )
+
+        q1 = text("SELECT range from data_table")
+        v = connection.scalar(q1)
+
+        q2 = select(data_table).where(data_table.c.range == v)
+        v2 = connection.scalar(q2)
+
+        eq_(data_obj, v2)
+
     def test_insert_obj(self, connection):
         connection.execute(
             self.tables.data_table.insert(), {"range": self._data_obj()}