Merge "Add CTE prefixes"

2 files changed, 19 insertions, 4 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 4ec3b93ea..807b01c24 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1851,7 +1851,8 @@ class SQLCompiler(Compiled):
                     kwargs["positional_names"] = self.cte_positional[cte] = []
 
                 assert kwargs.get("subquery", False) is False
-                text += " AS \n(%s)" % (
+                text += " AS %s\n(%s)" % (
+                    self._generate_prefixes(cte, cte._prefixes, **kwargs),
                     cte.element._compiler_dispatch(
                         self, asfrom=True, **kwargs
                     ),
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index bece0b3c5..ed7a6c2b9 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -1451,7 +1451,7 @@ class TableSample(AliasedReturnsRows):
             return functions.func.system(self.sampling)
 
 
-class CTE(Generative, HasSuffixes, AliasedReturnsRows):
+class CTE(Generative, HasPrefixes, HasSuffixes, AliasedReturnsRows):
     """Represent a Common Table Expression.
 
     The :class:`.CTE` object is obtained using the
@@ -1469,6 +1469,7 @@ class CTE(Generative, HasSuffixes, AliasedReturnsRows):
             ("_restates", InternalTraversal.dp_clauseelement_unordered_set),
             ("recursive", InternalTraversal.dp_boolean),
         ]
+        + HasPrefixes._traverse_internals
         + HasSuffixes._traverse_internals
     )
 
@@ -1490,11 +1491,14 @@ class CTE(Generative, HasSuffixes, AliasedReturnsRows):
         recursive=False,
         _cte_alias=None,
         _restates=frozenset(),
+        _prefixes=None,
         _suffixes=None,
     ):
         self.recursive = recursive
         self._cte_alias = _cte_alias
         self._restates = _restates
+        if _prefixes:
+            self._prefixes = _prefixes
         if _suffixes:
             self._suffixes = _suffixes
         super(CTE, self)._init(selectable, name=name)
@@ -1526,6 +1530,7 @@ class CTE(Generative, HasSuffixes, AliasedReturnsRows):
             name=name,
             recursive=self.recursive,
             _cte_alias=self,
+            _prefixes=self._prefixes,
             _suffixes=self._suffixes,
         )
 
@@ -1535,6 +1540,7 @@ class CTE(Generative, HasSuffixes, AliasedReturnsRows):
             name=self.name,
             recursive=self.recursive,
             _restates=self._restates.union([self]),
+            _prefixes=self._prefixes,
             _suffixes=self._suffixes,
         )
 
@@ -1544,6 +1550,7 @@ class CTE(Generative, HasSuffixes, AliasedReturnsRows):
             name=self.name,
             recursive=self.recursive,
             _restates=self._restates.union([self]),
+            _prefixes=self._prefixes,
             _suffixes=self._suffixes,
         )
 
@@ -1570,13 +1577,20 @@ class HasCTE(roles.HasCTERole):
         when combined with RETURNING, as well as a consumer of
         CTE rows.
 
+        .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as
+           CTE, CTEs added to UPDATE/INSERT/DELETE.
+
         SQLAlchemy detects :class:`.CTE` objects, which are treated
         similarly to :class:`.Alias` objects, as special elements
         to be delivered to the FROM clause of the statement as well
         as to a WITH clause at the top of the statement.
 
-        .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as
-           CTE, CTEs added to UPDATE/INSERT/DELETE.
+        For special prefixes such as PostgreSQL "MATERIALIZED" and
+        "NOT MATERIALIZED", the :meth:`.CTE.prefix_with` method may be
+        used to establish these.
+
+        .. versionchanged:: 1.3.13 Added support for prefixes.
+           In particular - MATERIALIZED and NOT MATERIALIZED.
 
         :param name: name given to the common table expression.  Like
          :meth:`._FromClause.alias`, the name can be left as ``None``