implement independent CTEs

Added new method :meth:`_sql.HasCTE.add_cte` to each of the
:func:`_sql.select`, :func:`_sql.insert`, :func:`_sql.update` and
:func:`_sql.delete` constructs. This method will add the given
:class:`_sql.CTE` as an "independent" CTE of the statement, meaning it
renders in the WITH clause above the statement unconditionally even if it
is not otherwise referenced in the primary statement. This is a popular use
case on the PostgreSQL database where a CTE is used for a DML statement
that runs against database rows independently of the primary statement.

Fixes: #6752
Change-Id: Ibf635763e40269cbd10f4c17e208850d8e8d0188

1 files changed, 17 insertions, 2 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 4b3b2c293..880479a37 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2313,8 +2313,7 @@ class SQLCompiler(Compiled):
                 ) and not existing.proxy_set.intersection(bindparam.proxy_set):
                     raise exc.CompileError(
                         "Bind parameter '%s' conflicts with "
-                        "unique bind parameter of the same name"
-                        % bindparam.key
+                        "unique bind parameter of the same name" % name
                     )
                 elif existing._is_crud or bindparam._is_crud:
                     raise exc.CompileError(
@@ -3075,6 +3074,10 @@ class SQLCompiler(Compiled):
         else:
             byfrom = None
 
+        if select_stmt._independent_ctes:
+            for cte in select_stmt._independent_ctes:
+                cte._compiler_dispatch(self, **kwargs)
+
         if select_stmt._prefixes:
             text += self._generate_prefixes(
                 select_stmt, select_stmt._prefixes, **kwargs
@@ -3551,6 +3554,10 @@ class SQLCompiler(Compiled):
         if insert_stmt._hints:
             _, table_text = self._setup_crud_hints(insert_stmt, table_text)
 
+        if insert_stmt._independent_ctes:
+            for cte in insert_stmt._independent_ctes:
+                cte._compiler_dispatch(self, **kw)
+
         text += table_text
 
         if crud_params_single or not supports_default_values:
@@ -3700,6 +3707,10 @@ class SQLCompiler(Compiled):
         else:
             dialect_hints = None
 
+        if update_stmt._independent_ctes:
+            for cte in update_stmt._independent_ctes:
+                cte._compiler_dispatch(self, **kw)
+
         text += table_text
 
         text += " SET "
@@ -3808,6 +3819,10 @@ class SQLCompiler(Compiled):
         else:
             dialect_hints = None
 
+        if delete_stmt._independent_ctes:
+            for cte in delete_stmt._independent_ctes:
+                cte._compiler_dispatch(self, **kw)
+
         text += table_text
 
         if delete_stmt._returning: