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.