Add CTE prefixes

Added support for prefixes to the :class:`.CTE` construct, to allow
support for Postgresql 12 "MATERIALIZED" and "NOT MATERIALIZED" phrases.
Pull request courtesy Marat Sharafutdinov.

Fixes: #5040
Closes: #5043
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5043
Pull-request-sha: d1b9059a0b6dae8dc2479ac670999b4af07908e0

Change-Id: I2e9cb5d7f85961ec98ee51965de5b3ec4a97be2f

1 files changed, 22 insertions, 0 deletions

diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py
index 26f367e9f..ff5a12481 100644
--- a/test/sql/test_cte.py
+++ b/test/sql/test_cte.py
@@ -903,6 +903,28 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL):
             'ON anon_1."order" = "order"."order"',
         )
 
+    def test_prefixes(self):
+        orders = table("order", column("order"))
+        s = select([orders.c.order]).cte("regional_sales")
+        s = s.prefix_with("NOT MATERIALIZED", dialect="postgresql")
+        stmt = select([orders]).where(orders.c.order > s.c.order)
+
+        self.assert_compile(
+            stmt,
+            'WITH regional_sales AS (SELECT "order"."order" AS "order" '
+            'FROM "order") SELECT "order"."order" FROM "order", '
+            'regional_sales WHERE "order"."order" > regional_sales."order"',
+        )
+
+        self.assert_compile(
+            stmt,
+            'WITH regional_sales AS NOT MATERIALIZED '
+            '(SELECT "order"."order" AS "order" '
+            'FROM "order") SELECT "order"."order" FROM "order", '
+            'regional_sales WHERE "order"."order" > regional_sales."order"',
+            dialect="postgresql"
+        )
+
     def test_suffixes(self):
         orders = table("order", column("order"))
         s = select([orders.c.order]).cte("regional_sales")