recursive sql / prql

[ledinscak]

Hello, I'm very interested how would you approach on solving recursions in prql, for instance how to rewrite this simple sql recursion:

with numbers as (
  select 1 as n
  union all
  select n + 1 from numbers where n <= 10
)
select * from numbers

love the idea of prql, best of luck!

[max-sixty]

Thanks for the issue @ledinscak

What would the materialized SQL you'd want to output for that, approximately?

[ledinscak]

this is simplest form of recursion it is one column with integers from 1 to 11

n
1
2
3
4
5
6
7
8
9
10
11

I took example from: https://medium.com/swlh/recursion-in-sql-explained-graphically-679f6a0f143b

[ledinscak]

There is ANSI SQL standard for recursions, so I thought approach with functions could do very well here, this is of course simple arithmetic example, see on linked page examples with parent - child relations. Point is that select part is repeated two or even three times with different where filters. In simple example last select could have also been written as:
select * from numbers -> select n from numbers, so we have
select 1
...
select n + 1 from ...
...
select n from ...
I didn't think over solution at all, just thought it is something worth to consider, because recursions are always hard to understand :-)

[max-sixty]

My guess would be that we don't need recursion in the transpiler from PRQL to SQL initially. Though we should be open to it if people find good cases.

I'm actually not familiar with recursion in SQL, maybe I haven't used the cool DBs that much. If there is SQL that does recursion then we should be able to produce it.

The thing that would be helpful is the actual SQL that you'd like to produce (PRQL just produces SQL, it doesn't do anything with the data itself). If it's an important case, please feel free to put a PR in with an example of the SQL and an estimate of the PRQL that should produce it.

Thanks @ledinscak