auto-cast PG range types

Range type handling has been enhanced so that it automatically
renders type casts, so that in-place round trips for statements that don't
provide the database with any context don't require the :func:`_sql.cast`
construct to be explicit for the database to know the desired type.

Change-Id: Id630b726f8a23059dd2f4cbc410bf5229d89cbfb
References: #8540

4 files changed, 44 insertions, 5 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
index 4cc04d20a..c953d3447 100644
--- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py
+++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
@@ -291,7 +291,7 @@ class AsyncpgCHAR(sqltypes.CHAR):
     render_bind_cast = True
 
 
-class _AsyncpgRange(ranges.AbstractRange):
+class _AsyncpgRange(ranges.AbstractRangeImpl):
     def bind_processor(self, dialect):
         Range = dialect.dbapi.asyncpg.Range
 
@@ -326,7 +326,7 @@ class _AsyncpgRange(ranges.AbstractRange):
         return to_range
 
 
-class _AsyncpgMultiRange(ranges.AbstractMultiRange):
+class _AsyncpgMultiRange(ranges.AbstractMultiRangeImpl):
     def bind_processor(self, dialect):
         Range = dialect.dbapi.asyncpg.Range
 
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py
index 371bf2bc2..7ca274e2c 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py
@@ -162,7 +162,7 @@ class _PGBoolean(sqltypes.Boolean):
     render_bind_cast = True
 
 
-class _PsycopgRange(ranges.AbstractRange):
+class _PsycopgRange(ranges.AbstractRangeImpl):
     def bind_processor(self, dialect):
         Range = cast(PGDialect_psycopg, dialect)._psycopg_Range
 
@@ -191,7 +191,7 @@ class _PsycopgRange(ranges.AbstractRange):
         return to_range
 
 
-class _PsycopgMultiRange(ranges.AbstractMultiRange):
+class _PsycopgMultiRange(ranges.AbstractMultiRangeImpl):
     def bind_processor(self, dialect):
         Range = cast(PGDialect_psycopg, dialect)._psycopg_Range
         Multirange = cast(PGDialect_psycopg, dialect)._psycopg_Multirange
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 5dcd449ca..a01f20e99 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -507,7 +507,7 @@ class _PGJSONB(JSONB):
         return None
 
 
-class _Psycopg2Range(ranges.AbstractRange):
+class _Psycopg2Range(ranges.AbstractRangeImpl):
     _psycopg2_range_cls = "none"
 
     def bind_processor(self, dialect):
diff --git a/lib/sqlalchemy/dialects/postgresql/ranges.py b/lib/sqlalchemy/dialects/postgresql/ranges.py
index edbe165d9..327feb409 100644
--- a/lib/sqlalchemy/dialects/postgresql/ranges.py
+++ b/lib/sqlalchemy/dialects/postgresql/ranges.py
@@ -91,6 +91,35 @@ class AbstractRange(sqltypes.TypeEngine):
 
     """  # noqa: E501
 
+    render_bind_cast = True
+
+    def adapt(self, impltype):
+        """dynamically adapt a range type to an abstract impl.
+
+        For example ``INT4RANGE().adapt(_Psycopg2NumericRange)`` should
+        produce a type that will have ``_Psycopg2NumericRange`` behaviors
+        and also render as ``INT4RANGE`` in SQL and DDL.
+
+        """
+        if issubclass(impltype, AbstractRangeImpl):
+            # two ways to do this are:  1. create a new type on the fly
+            # or 2. have AbstractRangeImpl(visit_name) constructor and a
+            # visit_abstract_range_impl() method in the PG compiler.
+            # I'm choosing #1 as the resulting type object
+            # will then make use of the same mechanics
+            # as if we had made all these sub-types explicitly, and will
+            # also look more obvious under pdb etc.
+            # The adapt() operation here is cached per type-class-per-dialect,
+            # so is not much of a performance concern
+            visit_name = self.__visit_name__
+            return type(
+                f"{visit_name}RangeImpl",
+                (impltype, self.__class__),
+                {"__visit_name__": visit_name},
+            )()
+        else:
+            return super().adapt(impltype)
+
     class comparator_factory(sqltypes.Concatenable.Comparator):
         """Define comparison operations for range types."""
 
@@ -165,10 +194,20 @@ class AbstractRange(sqltypes.TypeEngine):
             return self.expr.op("+")(other)
 
 
+class AbstractRangeImpl(AbstractRange):
+    """marker for AbstractRange that will apply a subclass-specific
+    adaptation"""
+
+
 class AbstractMultiRange(AbstractRange):
     """base for PostgreSQL MULTIRANGE types"""
 
 
+class AbstractMultiRangeImpl(AbstractRangeImpl, AbstractMultiRange):
+    """marker for AbstractRange that will apply a subclass-specific
+    adaptation"""
+
+
 class INT4RANGE(AbstractRange):
     """Represent the PostgreSQL INT4RANGE type."""