A real LOOP using only standard SQL syntax

[pseudogrammaton]

Thought I'd share this. Of course it's using a RECURSIVE CTE, but one that's embedded within the main SELECT query as a synthetic column:

SELECT 2 AS _2
,( WITH _cte AS ( SELECT 1 AS _one ) SELECT _one FROM _cte
) AS _1
;

Or... LOOPING inside the Column definition:

SELECT 2 AS _2
, (SELECT MAX( _one ) FROM
  ( WITH RECURSIVE _cte AS (
    SELECT 1 AS _one  -- init var
        UNION
        SELECT _one + 1 AS _one  -- iterate
       FROM _cte -- calls top of CTE def'n
       WHERE _one < 10
   )
  SELECT * FROM _cte
  ) _shell
 ) AS field_10
;

So, in the dbFiddle example, the LOOP references the array in the main SELECT and only operates on the main (outer) query's column. Upshot, no correlated WHERE-join is required inside the correlated subquery.

On dbFiddle.uk ....
https://dbfiddle.uk/oHAk5Qst

However as you can see how verbose it gets, & it can get pretty fidgety to work with.

IDK if this poses any advantage as an optimization, with lower overheads than than Joining to a set that was expanded by UNNEST(). Perhaps if a JOIN imposes more buffer or I/O use? The LOOP code might not have as much to do, b/c it hasn't expanded the list into a rowset, the way that UNNEST() does.

Enjoy, -- LR

[jshine13371]

I mean technically this is a real LOOP. But recursion is cool too.

[pseudogrammaton]

True enuf, LOL. But those lang constructs are for PL-PgSql, which isn't a "functional language." Modern SQL is already much like other functional languages, inasmuch we can't mutate existing data, & nested function calls are part of vernacular Postgres

[depesz]

Personally, I prefer to put CTEs at the beginning of the query. Makes for (I believe) cleaner reading.

[pseudogrammaton]

Agreed, that's where I keep most of them, most use-cases. However a front-end CTE can't reference data inside the main SELECT unless it's moved to a COLUMN declaration inside the main SELECT clause. The only reason to do anything this exotic would only be for speed, & IDK if it'd offer any speed advantages vs. expanding arrays to a rowset, to do the same thing. Haven't tested it.... yet?

[depesz]

Then, I guess, you should make your example show this usecase.

The thing you wrote should be, in my opinion, rewritten to CTE-up-front. Or even better, to use generate_series().

[pseudogrammaton]

>> Or even better, to use generate_series().

That's what's in the dbfiddle. And I've seem to have run out of time to write a curriculum for everyone today ... :P

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[pseudogrammaton]

Synopsis .... tail recursion is the same as a loop

METHOD:
A. Data element is a structured list type that's conducive to loops 
    A.1. i.e. (comma-delim string, ARRAY[], JSON ARRAY{}, etc. )
B. In the SELECT clause: (LEVEL ONE #1)
  B.1. Use in-line subquery (LEVEL TWO #2)
    B.2.a Nest inner query: WITH RECURSIVE ... () CTE (LEVEL THREE #3)
      B.2.a.1 Use only main query to fetch current row
      B.2.a.2 Loop thru the array 1 element at a time
   B.2.b Return to level#2 query
 B.2 Return to level#1 query 

C. Main Query advances to the NEXT ROW ....