Recursive iteration with RECURSE()

SELECT
    ROW(1 AS n, 'hello' AS tag).RECURSE(
        SELECT
            prev[n] + 1 AS n
        WHILE n < 4
        MAX_LEVEL 10
    ) AS result
;

SELECT
    ROW(10 AS n).RECURSE(
        SELECT
            prev[n] + 1 AS n
        WHILE n < 5
        MAX_LEVEL 10
    ) AS result
;

WITH
    seed_n := INPUT(BIGINT)
SELECT
    seed_n,
    ROW(seed_n AS n).RECURSE(
        SELECT
            prev[n] + 1 AS n
        WHILE n < 5
        MAX_LEVEL 10
    ) AS result
FOR
    seed_n := BIND_VALUES(ARRAY[1, 10])
;

WITH
    seed_b := INPUT(BIGINT)
SELECT
    seed_b,
    ROW(0 AS a, seed_b AS b).RECURSE(
        SELECT
            prev[b] AS a,
            prev[a] + prev[b] AS b
        WHILE b < 100
        MAX_LEVEL 20
    ) AS result
FOR
    seed_b := BIND_VALUES(ARRAY[1, 3])
;

WITH
    CLIENTS := ENTITY(),
    client_id := INPUT(BIGINT#CLIENTS),
    client_source := EXTERNAL_COLUMNS(
        client_id BIGINT#CLIENTS BIND TO client_id,
        initial_balance DECIMAL(10,2)
        FROM TABLE(recurse_test.clients)
    ),
    initial_balance := client_source[initial_balance]
SELECT
    client_id,
    ROW(0.01 AS rate, 500.00 AS payment, 0 AS period, initial_balance AS balance).RECURSE(
        SELECT
            prev[rate] AS rate,
            prev[payment] AS payment,
            prev[period] + 1 AS period,
            prev[balance] + prev[balance] * prev[rate] - prev[payment] AS balance
        WHILE balance > 0
        MAX_LEVEL 360
    ) AS result
FOR
    client_id := BIND_VALUES(ARRAY[1, 2])
;

SELECT
    RECURSE(
        ROW(1 AS n)
        USING (
            SELECT
                prev[n] + 1 AS n
            WHILE n < 4
            MAX_LEVEL 10
        )
    ) AS result
;

SELECT
    ROW(1 AS n).RECURSE(
        SELECT
            prev[n] + 1 AS n
        MAX_LEVEL 3
    ) AS result
;

SELECT
    ROW(1 AS n, 'start' AS label).RECURSE(
        SELECT
            prev[n] + 1 AS n,
            CASE WHEN prev[n] + 1 > 3 THEN 'big' ELSE 'small' END AS label
        WHILE n < 6
        MAX_LEVEL 10
    ) AS result
;

WITH
    cat_id := INPUT(BIGINT),
    cat_source := EXTERNAL_COLUMNS(
        category_id BIGINT BIND TO cat_id,
        category_name VARCHAR,
        parent_category_id BIGINT
        FROM TABLE(recurse_test.categories)
    ),
    category_id := cat_source[category_id],
    category_name := cat_source[category_name],
    category_label := UPPER(category_name),
    parent_category_id := cat_source[parent_category_id]
SELECT
    cat_id,
    ROW(cat_id AS start).RECURSE(
        WITH
            lower_label := LOWER(step[category_label]),
        SELECT
            step[category_id] AS id,
            step[category_name] AS name,
            lower_label AS label
        VIA start BIND TO category_id
        FOLLOW parent_category_id
        MAX_LEVEL 10
    ) AS result
FOR
    cat_id := BIND_VALUES(ARRAY[4, 6])
;

WITH
    cat_id := INPUT(BIGINT),
    cat_source := EXTERNAL_COLUMNS(
        category_id BIGINT BIND TO cat_id,
        category_name VARCHAR,
        parent_category_id BIGINT
        FROM TABLE(recurse_test.categories)
    ),
    category_id := cat_source[category_id],
    category_name := cat_source[category_name],
    parent_category_id := cat_source[parent_category_id]
SELECT
    cat_id,
    ROW(cat_id AS start).RECURSE(
        SELECT
            step[category_id] AS id,
            step[category_name] AS name
        VIA start BIND TO parent_category_id
        FOLLOW category_id
        MAX_LEVEL 10
    ) AS result
FOR
    cat_id := BIND_VALUE(1)
;

WITH
    ACCOUNTS := ENTITY(),
    TRANSFERS := ENTITY(),
    account_id := INPUT(BIGINT#ACCOUNTS),
    transfer_id := INPUT(BIGINT#TRANSFERS),
    account_source := EXTERNAL_COLUMNS(
        account_id BIGINT#ACCOUNTS BIND TO account_id,
        account_name VARCHAR
        FROM TABLE(recurse_test.accounts)
    ),
    transfer_source := EXTERNAL_COLUMNS(
        transfer_id BIGINT#TRANSFERS BIND TO transfer_id,
        from_id BIGINT#ACCOUNTS,
        to_id BIGINT#ACCOUNTS,
        amount DOUBLE
        FROM TABLE(recurse_test.transfers)
    ),
    amount := transfer_source[amount],
    from_id := transfer_source[from_id],
    to_id := transfer_source[to_id]
SELECT
    account_id,
    ROW(account_id AS start, 0e0 AS cumulated).RECURSE(
        SELECT
            step[amount] AS amt,
            prev[cumulated] + amt AS cumulated
        WHILE cumulated <=.2 500e0
        VIA start BIND TO from_id
        FOLLOW to_id
        MAX_LEVEL 10
    ) AS result
FOR
    account_id := BIND_VALUES(ARRAY[1, 3]),
    NESTED transfer_id := BIND_VALUES(ARRAY[101, 102, 103, 104])
;

WITH
    ACCOUNTS := ENTITY(),
    TRANSFERS := ENTITY(),
    account_id := INPUT(BIGINT#ACCOUNTS),
    transfer_id := INPUT(BIGINT#TRANSFERS),
    account_source := EXTERNAL_COLUMNS(
        account_id BIGINT#ACCOUNTS BIND TO account_id,
        account_name VARCHAR
        FROM TABLE(recurse_test.accounts)
    ),
    transfer_source := EXTERNAL_COLUMNS(
        transfer_id BIGINT#TRANSFERS BIND TO transfer_id,
        from_id BIGINT#ACCOUNTS,
        to_id BIGINT#ACCOUNTS,
        amount DOUBLE
        FROM TABLE(recurse_test.transfers_with_cycle)
    ),
    amount := transfer_source[amount],
    from_id := transfer_source[from_id],
    to_id := transfer_source[to_id]
SELECT
    account_id,
    ROW(account_id AS start, 0e0 AS cumulated).RECURSE(
        SELECT
            step[amount] AS amt,
            prev[cumulated] + amt AS cumulated
        VIA start BIND TO from_id
        FOLLOW to_id
        MAX_LEVEL 20
    ) AS result
FOR
    account_id := BIND_VALUE(1),
    NESTED transfer_id := BIND_VALUES(ARRAY[101, 102, 103, 104])
;

WITH
    steps := ROW(1 AS n).RECURSE(
        SELECT prev[n] + 1 AS n
        WHILE n < 5
        MAX_LEVEL 10
    )
SELECT
    steps.TRANSFORM(SELECT SUM(n)).UNWRAP_ONE() AS total
;