Loop

Experimental

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:

def loop(step, initial):
    result = []
    current = initial
    while current is not empty:
        result = append(result, current)
        current = step(current)

    return result

Examples

from [{n = 1}]
loop (
    filter n<4
    select n = n+1
)

# returns [1, 2, 3, 4]

WITH RECURSIVE table_0 AS (
  SELECT
    1 AS n
),
table_1 AS (
  SELECT
    n
  FROM
    table_0
  UNION
  ALL
  SELECT
    n + 1
  FROM
    table_1
  WHERE
    n < 4
)
SELECT
  n
FROM
  table_1 AS table_2

[!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.