JSONPATH type can be used in casts in PostgreSQL

Introduced the type :class:`_postgresql.JSONPATH` that can be used
in cast expressions. This is required by some PostgreSQL dialects
when using functions such as ``jsonb_path_exists`` or
``jsonb_path_match`` that accept a ``jsonpath`` as input.

Fixes: #8216
Change-Id: I3e7337eab91680cab1604e1f3058854a0a19c5be

1 files changed, 14 insertions, 0 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 5e5c4f9bd..67e54e4f5 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -41,6 +41,8 @@ from sqlalchemy.dialects.postgresql import array_agg as pg_array_agg
 from sqlalchemy.dialects.postgresql import DOMAIN
 from sqlalchemy.dialects.postgresql import ExcludeConstraint
 from sqlalchemy.dialects.postgresql import insert
+from sqlalchemy.dialects.postgresql import JSONB
+from sqlalchemy.dialects.postgresql import JSONPATH
 from sqlalchemy.dialects.postgresql import TSRANGE
 from sqlalchemy.dialects.postgresql.base import PGDialect
 from sqlalchemy.dialects.postgresql.psycopg2 import PGDialect_psycopg2
@@ -2354,6 +2356,18 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "WHERE data.group_id = summary.group_id)",
         )
 
+    @testing.combinations(JSONB.JSONPathType, JSONPATH)
+    def test_json_path(self, type_):
+        data = table("data", column("id", Integer), column("x", JSONB))
+        stmt = select(
+            func.jsonb_path_exists(data.c.x, cast("$.data.w", type_))
+        )
+        self.assert_compile(
+            stmt,
+            "SELECT jsonb_path_exists(data.x, CAST(%(param_1)s AS JSONPATH)) "
+            "AS jsonb_path_exists_1 FROM data",
+        )
+
 
 class InsertOnConflictTest(fixtures.TablesTest, AssertsCompiledSQL):
     __dialect__ = postgresql.dialect()