From 0887c8899aa7424abd7762a48c0c876c7c1b5fb1 Mon Sep 17 00:00:00 2001
From: Eric Masseran <eric.masseran@gmail.com>
Date: Tue, 7 Sep 2021 18:50:51 +0200
Subject: [PATCH] Add documentation

---
 doc/build/changelog/unreleased_14/4132.rst |  3 +-
 lib/sqlalchemy/sql/selectable.py           | 33 ++++++++++++++++++++++
 2 files changed, 34 insertions(+), 2 deletions(-)

diff --git a/doc/build/changelog/unreleased_14/4132.rst b/doc/build/changelog/unreleased_14/4132.rst
index 87c044b2e8..5c30b69788 100644
--- a/doc/build/changelog/unreleased_14/4132.rst
+++ b/doc/build/changelog/unreleased_14/4132.rst
@@ -3,5 +3,4 @@
     :tickets: 4123
 
     Add `nesting` property on :class:`_sql.CTE` and parameter on
-    :meth:`_sql.HasCTE.add_cte` and :meth:`_sql.HasCTE.cte`
-    to render a nesting CTE.
\ No newline at end of file
+    :meth:`_sql.HasCTE.cte` to render a nesting CTE.
\ No newline at end of file
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 5ef2283000..82303e258d 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -2229,6 +2229,8 @@ class HasCTE(roles.HasCTERole):
          A recursive common table expression is intended to be used in
          conjunction with UNION ALL in order to derive rows
          from those already selected.
+        :param nesting: if ``True``, will render the CTE locally to the
+         actual statement.
 
         The following examples include two from PostgreSQL's documentation at
         https://www.postgresql.org/docs/current/static/queries-with.html,
@@ -2349,6 +2351,37 @@ class HasCTE(roles.HasCTERole):
 
             connection.execute(upsert)
 
+        Example 4, Nesting CTE::
+
+            value_a = select(
+                literal("root").label("n")
+            ).cte("value_a")
+
+            # A nested CTE with the same name as the root one
+            value_a_nested = select(
+                literal("nesting").label("n")
+            ).cte("value_a", nesting=True)
+
+            # Nesting CTEs takes acsendency locally
+            # over the CTEs at a higher level
+            value_b = select(value_a_nested.c.n).cte("value_b")
+
+            value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))
+
+            print(value_ab)
+            # WITH value_a AS
+            #     (SELECT :param_1 AS n),
+            # value_b AS
+            #     (WITH value_a AS
+            #         (SELECT :param_2 AS n)
+            #     SELECT value_a.n AS n
+            #     FROM value_a)
+            # SELECT value_a.n AS a, value_b.n AS b
+            # FROM value_a, value_b
+
+            conn.execute(value_ab).fetchall()
+            # (a="root", b="nesting")
+
         .. seealso::
 
             :meth:`_orm.Query.cte` - ORM version of
-- 
2.47.3