Message-ID: <3926d507-2ef4-4b94-ba84-2dafd2e1110a@gmx.net>
Date: Fri, 20 Dec 2024 16:25:39 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Abitity to identify the current iteration in a recursive SELECT
 (feature request)
To: pgsql-sql@lists.postgresql.org
References: 
 <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>
Content-Language: de-DE, en-US
From: shammat@gmx.net
In-Reply-To: 
 <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:1SVpaJrQyCk=;/5GTT+6RByUZJAGtv176puA/yX7
 n8A+XqshMc1F3otHKPwF+lnK7sXPyTScp96oR1LPg1dsQ3R15qvAGl9snZW63aDCk36l21G9u
 aXvrs8ke1BfF7JPZk4x825a/w39bItF/Nfp1hJVTuh+l2lXr0CtwKv1AtOOKRGebwlegy70IQ
 3HhpU7puDhuVopkvCUOFsTok3jgr+36doe4FSQnM1vsEw7GJkKuH7KfjiV3qb2LVHlzUP9sLx
 DXJk2LYWK5jKhtId0wQG3PJItGP4LQm1AYvFmV4vPCIFQ2D0S8rjHld9qsP3bz/u54MvMwv06
 QlnLpUHE8kj57T66+BIfB1BRTqadiWjcZXSog9YPWIuB7U9Ly0rc/qZhny3LJAggny8JbBBLr
 D9xSgZFQClM/K8RshLEhJMFdvANOid4i4GNTLwCL1Pfrd0gNVEAj4p9Yv6DUKA3wbdx3+cOzb
 ZoxkV1G8Q3H2m6MzW8MItJx6JWeEhCsAgCVQ3glMcQVnGkfnVHB2HhYzbprCtDSTiVRSF53sn
 kwGUYjr3UbcH66rRUieEdKrysRXxNx1heZmX9CJ1XcpaEdb8kWQ6SFWI+l9rqXJJ0HeAv7Uy6
 jhldfdEtyHAMEOAnA6OZyi50TARBgDKCmwJczGPXAv3kBSbdVEyrObwOBseuzXC/OM8duoec9
 4gRTHPUSko2ODE+YKyG2hNEh9mnUcnZphC9sRffNhjypJ57R/S3uN0wBZn2evqrvkXcRxQBGr
 BRKcLyEVIMMD0AWHjuOoNFmePSxcxESW/z214b54YQi/JsgyipUGciYpKm1jNt8cSXSG+x3Va
 zCuQ/k4prdd2spWGwApGSxd2tsmnx/csaekigg4itAoPUX1OZVodmXNtyRqmv9uji27s9RLPz
 97GDJTTTGOnpT7UY9kf0bE1AGYD3cakht4AnIh6hfvl/tHVPHVyu5LUMyEJLIsit0ce0dmw4R
 CBjJ+UbwVACHynoPjMMEbpVm5qtk2xmKo2dzK6yRxYf4uD51R6QBf7Cz2zoGspcEkNfH6F+y/
 7Ojqfn4MH8z6U/5ITiXOwxiUe09wacUuBYm6qx2hQap3yBCqqR3q7DfdXGfLAKsVAo6emPIoD
 hL+P0gJTFZPT51o1rE2MwuZ3Jq+IWI
Archived-At: 
 <https://www.postgresql.org/message-id/3926d507-2ef4-4b94-ba84-2dafd2e1110a%40gmx.net>
Precedence: bulk

Am 16.12.24 um 11:21 schrieb sulfinu@gmail.com:
> the algorithm present here <https://www.postgresql.org/docs/current/
> queries-with.html#QUERIES-WITH-RECURSIVE> for the resolution of WITH
> RECURSIVE queries is, broadly speaking, iterative, with the
> iteration sequence number indicated in the first row item of the
> field generated by the SEARCH BREADTH FIRST clause,
> i.e. /0/, /1/, /2/ etc.
>
> Is there a way to obtain directly this iteration sequence number
> within the SELECT statement following the UNION keyword in a
> recursive construction? I know it can by obtained by maintaining its
> value in a working table column, but that's suboptimal - I need it
> as a "magic" variable, akin, for example, the excluded variable
> available inside the ON CONFLICT DO UPDATE clause of an INSERT
> statement.
>

I assume you don't want to do something like the following when you
refer to "working table column"?

with recursive cte as (
    select ..., 1 as level
    from ...
    union all
    select ...., parent.level + 1
    from ...
      join cte as parent on ...
)