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, 17 insertions, 3 deletions

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``