add explicit REGCONFIG, pg full text functions

Added support for explicit use of PG full text functions with asyncpg and
psycopg (SQLAlchemy 2.0 only), with regards to the ``REGCONFIG`` type cast
for the first argument, which previously would be incorrectly cast to a
VARCHAR, causing failures on these dialects that rely upon explicit type
casts. This includes support for :class:`_postgresql.to_tsvector`,
:class:`_postgresql.to_tsquery`, :class:`_postgresql.plainto_tsquery`,
:class:`_postgresql.phraseto_tsquery`,
:class:`_postgresql.websearch_to_tsquery`,
:class:`_postgresql.ts_headline`, each of which will determine based on
number of arguments passed if the first string argument should be
interpreted as a PostgreSQL "REGCONFIG" value; if so, the argument is typed
using a newly added type object :class:`_postgresql.REGCONFIG` which is
then explicitly cast in the SQL expression.

Fixes: #8977
Change-Id: Ib36698a984fd4194bd6e0eb663105f790f3db7d3

1 files changed, 6 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py
index 400c3186e..67d1370f5 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py
@@ -70,6 +70,7 @@ from ._psycopg_common import _PGExecutionContext_common_psycopg
 from .base import INTERVAL
 from .base import PGCompiler
 from .base import PGIdentifierPreparer
+from .base import REGCONFIG
 from .json import JSON
 from .json import JSONB
 from .json import JSONPathType
@@ -90,6 +91,10 @@ class _PGString(sqltypes.String):
     render_bind_cast = True
 
 
+class _PGREGCONFIG(REGCONFIG):
+    render_bind_cast = True
+
+
 class _PGJSON(JSON):
     render_bind_cast = True
 
@@ -270,6 +275,7 @@ class PGDialect_psycopg(_PGDialect_common_psycopg):
         _PGDialect_common_psycopg.colspecs,
         {
             sqltypes.String: _PGString,
+            REGCONFIG: _PGREGCONFIG,
             JSON: _PGJSON,
             sqltypes.JSON: _PGJSON,
             JSONB: _PGJSONB,