Loop

experimental

Iteratively applies step function to initial relation until the step returns an empty table. Returns a relation that contains rows of initial relation and all intermediate relations.

This behavior could be expressed with following pseudo-code:

1. def loop(step, initial):
2. result = []
3. current = initial
4. while current is not empty:
5. result = append(result, current)
6. current = step(current)

8. return result

Examples

PRQL

1. from_text format:json '[{"n": 1 }]'
2. loop (
3. filter n<4
4. select n = n+1
5. )

7. # returns [1, 2, 3, 4]

SQL

1. WITH table_0 AS (
2. SELECT
3. 1 AS n
4. ),
5. table_4 AS (
6. WITH RECURSIVE loop AS (
7. SELECT
8. n
9. FROM
10. table_0 AS table_1
11. UNION
12. ALL
13. SELECT
14. n + 1
15. FROM
16. loop AS table_2
17. WHERE
18. n < 4
19. )
20. SELECT
21. *
22. FROM
23. loop
24. )
25. SELECT
26. n
27. FROM
28. table_4 AS table_3

Note

Behavior of WITH RECURSIVE may depend on database configuration (MySQL). prql-compiler assumes behavior described by Postgres documentation and will not produce correct results for alternative configurations of MySQL.

Note

Currently, loop may produce references to the recursive CTE in sub-queries, which is not supported by some database engines (SQLite). For now, we suggest you keep step functions simple enough to fit into a single SELECT statement.