public inbox for [email protected]  

help / color / mirror / Atom feed

From: Greg Sabino Mullane <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: Re: Abitity to identify the current iteration in a recursive SELECT (feature request)
Date: Wed, 18 Dec 2024 09:06:40 -0500
Message-ID: <CAKAnmmL34MAd436EJxXx6vV-3QHQvQDr3TQb5AjbuyzEwm=GKw@mail.gmail.com> (raw)
In-Reply-To: <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>
References: <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>

On Wed, Dec 18, 2024 at 5:00 AM <[email protected]> wrote:

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

Do you mean something like "... WHERE pg_magic_iteration_number < 10"?
Looking at the source code, I don't see a trivial way to accomplish that.
Maintaining the count as a column in your select is still the canonical
way. As someone who writes a lot of recursive CTEs (especially each
December!), I'm not sure how useful this feature would be, as the number of
loops is rarely the criteria for ending the iterations.

I'm using the a recursive SELECT in order to join iteratively several
> (virtual) tables computed dinamically based on the iteration number.
>

Certainly the best solution is to use pl/pgsql, which gets you iterative
loops, lots of introspection and ways to break out of the loop, and even
true recursion.

Cheers,
Greg

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Abitity to identify the current iteration in a recursive SELECT (feature request)
  In-Reply-To: <CAKAnmmL34MAd436EJxXx6vV-3QHQvQDr3TQb5AjbuyzEwm=GKw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox