From 204ff1f60cf911b00b7494942fc58bc715dddeed Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 12 Jul 2021 14:28:19 -0400 Subject: 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 --- lib/sqlalchemy/sql/compiler.py | 19 +++++++++++++++++-- 1 file changed, 17 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/sql/compiler.py') 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: -- cgit v1.2.1