Loop

Experimental

  1. loop {step_function} {initial_relation}

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

The behavior of WITH RECURSIVE may depend on the database configuration in MySQL. The compiler assumes the behavior described by the 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, e.g. SQLite. For now, we suggest step functions are kept simple enough to fit into a single SELECT statement.