Proposal: Range-based FOR statement

[Adriano dos Santos Fernandes]

# Range-based FOR statement (FB 6.0)

## Description

The range-based `FOR` statement is used to iterate over a range of
values. The iteration is performed in increasing
order when used with `TO` clause and in decreasing order when used with
`DOWNTO` clause.

## Syntax

```
[<label> :]
FOR <variable> = <initial value> {TO | DOWNTO} <final value> DO
<statement>
```

## Details

- `<variable>` must be a declared variable of an exact numeric type with
zero scale.
- At the `FOR` start, `<initial value>` is assigned to `<variable>` and
`<final value>` is evaluated, converted to the
same type as of `<variable>` and cached for future use.
- At the start of all loop iterations, `<variable>` is checked to be
less than or equal (`TO`) or greater than or equal
(`DOWNTO`) to the cached value of `<final value>`. If the condition is
not met, the loop is exited.
- At the end of all loop iterations, `<variable>` is incremented (`TO`)
or decremented (`DOWNTO`) by one.
- `<statement>` is executed for each iteration.
- `BREAK [<label>]` can be used to exit to the next statement after the
`FOR`.
- `CONTINUE [<label>]` can be used to skip the current `<statement>`
iteration, increment (`TO`) or decrement (`DOWNTO`)
`<variable>` and continue the loop.
- `<variable>` may be assigned by user code inside the loop.

## Examples

```
execute block returns (out integer)
as
begin
for out = 1 to 3 do
suspend;
end

/* Result:
1
2
3
*/
```

```
execute block returns (out integer)
as
begin
for out = 9 downto 7 do
suspend;
end

/* Result:
9
8
7
*/
```

```
execute block returns (out integer)
as
begin
for out = 5 to 3 do
suspend;
end

/* Result:
*/
```

```
execute block
as
declare i integer;
begin
for i = 1 to 10 do
begin
insert into table1 values (i);
insert into table2 values (i);
end
end

/* Result:
10 records inserted into table1
10 records inserted into table2
*/
```


Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 25.03.2025 2:06:
> - `<variable>` may be assigned by user code inside the loop.

This is inconsistent with behaviour of <final value>. Either both of them can
be assigned or none.

--
WBR, SD.

[Mark Rotteveel]

On 25/03/2025 02:06, Adriano dos Santos Fernandes wrote:
> # Range-based FOR statement (FB 6.0)
>
> ## Description
>
> The range-based `FOR` statement is used to iterate over a range of
> values. The iteration is performed in increasing
> order when used with `TO` clause and in decreasing order when used with
> `DOWNTO` clause.
>
> ## Syntax
>
> ```
> [<label> :]
> FOR <variable> = <initial value> {TO | DOWNTO} <final value> DO
> <statement>
> ```

Is this syntax inspired by another DBMS, if so, which one?

For example, PostgreSQL uses the following syntax:

```
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];
```

(See
https://www.postgresql.org/docs/17/plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR
)

In Firebird syntax, that would be something like:

```
[label:]
FOR varname IN [ REVERSE ] <expression> .. <expression> [ BY
<expression> ] DO
<compound_statement>
```

If you want only your proposed features (increment/decrement by 1):

```
[label:]
FOR varname IN [ REVERSE ] <expression> .. <expression> DO
<compound_statement>
```

To be clear, I'm fine with the original syntax, but using syntax similar
to another DBMS may have its advantages.

For comparison, Oracle PL/SQL uses:

```
FOR index IN lower_bound .. upper_bound
LOOP
statements;
END LOOP;
```

Which is similar to the PostgreSQL syntax.

(See also
https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/FOR-LOOP-statement.html)

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

Where did you read that things?

Assign to final value?

Like: 10 = something?

Adriano

[Adriano dos Santos Fernandes]

Em ter., 25 de mar. de 2025 06:34, 'Mark Rotteveel' via firebird-devel <firebir...@googlegroups.com> escreveu:

On 25/03/2025 02:06, Adriano dos Santos Fernandes wrote:
> # Range-based FOR statement (FB 6.0)
>
> ## Description
>
> The range-based `FOR` statement is used to iterate over a range of
> values. The iteration is performed in increasing
> order when used with `TO` clause and in decreasing order when used with
> `DOWNTO` clause.
>
> ## Syntax
>
> ```
> [<label> :]
> FOR <variable> = <initial value> {TO | DOWNTO} <final value> DO
>      <statement>
> ```

Is this syntax inspired by another DBMS, if so, which one?

Pascal

To be clear, I'm fine with the original syntax, but using syntax similar
to another DBMS may have its advantages.

IMO the Pascal syntax is more PSQL-like than these others and is familiar to many Firebird users.

Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 25.03.2025 10:54:
> Where did you read that things?
>
> Assign to final value?
>
> Like: 10 = something?

Like `for i := 0 to j do j = j - 1;`

--
WBR, SD.

[Mark Rotteveel]

The proposal talks about changing <variable>, not <final value>.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 25.03.2025 11:11:
>>    Like `for i := 0 to j do j = j - 1;`
>
> The proposal talks about changing <variable>, not <final value>.

That's why I called this proposal inconsistent: it should be talking about
changing of both (or better none).

IIRC, in Pascal this is a correct code:

For i := 0 to j do
begin
i := i + 1;

j := j - 1;

end;

--
WBR, SD.

[Virgo Pärna]

On 25.03.2025 12:22, 'Dimitry Sibiryakov' via firebird-devel wrote:
>
>   IIRC, in Pascal this is a correct code:
>
>   For i := 0 to j do
>    begin
>     i := i + 1;

At which version? Those, that I know will report error on such assignment.
And changing j inside for loop in pascal does not change, how many
times loop is executed.


--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Omacht András]

No, you don't need to modify the value of the loop variable within the for loop for correct operation.

András

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/0d034975-60c9-443e-b73b-dbbb25c35f18%40ibphoenix.com.

[Mark Rotteveel]

On 25/03/2025 11:31, Omacht András wrote:
> No, you don't need to modify the value of the loop variable within the for loop for correct operation.

We're not talking about it being necessary for correct operation, but
about the possibility to modify it, and thus affect how long the loop
will actually run.

Mark
--
Mark Rotteveel

[Adriano dos Santos Fernandes]

Reevaluating the final value would be problematic for most use cases,
like TO LENGTH(STR). For this case, one may use a WHILE.

Pascal also caches it.


Adriano

[Adriano dos Santos Fernandes]

On 25/03/2025 07:30, Virgo Pärna wrote:
> On 25.03.2025 12:22, 'Dimitry Sibiryakov' via firebird-devel wrote:
>>
>>    IIRC, in Pascal this is a correct code:
>>
>>    For i := 0 to j do
>>     begin
>>      i := i + 1;
>
>     At which version? Those, that I know will report error on such
> assignment.
>     And changing j inside for loop in pascal does not change, how many
> times loop is executed.
>     

AFAIK Pascal does not allow it.

But I would allow it for Firebird, because it's sometimes very useful
(skip an item) and is an explicit intention of the developer. It should
just have defined behavior.


Adriano

[Adriano dos Santos Fernandes]

On 25/03/2025 07:22, 'Dimitry Sibiryakov' via firebird-devel wrote:
> 'Mark Rotteveel' via firebird-devel wrote 25.03.2025 11:11:
>>>    Like `for i := 0 to j do j = j - 1;`
>>
>> The proposal talks about changing <variable>, not <final value>.
>
>   That's why I called this proposal inconsistent: it should be talking
> about changing of both (or better none).
>

We should not talk about assignment to values, it makes no sense.

The fact you use a variable as a value has nothing to do with it.

We just document that value is cached (*), so changes to it does not
reflect in the comparisons.

(*) The spec just describes an observable behavior. If one uses a
constant, there is no reason to copy it.

>   IIRC, in Pascal this is a correct code:
>
>   For i := 0 to j do
>    begin
>     i := i + 1;
>     j := j - 1;
>    end;
>

Looks like you're wrong.


Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 25.03.2025 11:54:
> But I would allow it for Firebird, because it's sometimes very useful
> (skip an item) and is an explicit intention of the developer.

WHILE loop can be used for that.

> We should not talk about assignment to values, it makes no sense.

So I suggest to remove ability to modify loop variable from this proposal.

--
WBR, SD.

[Adriano dos Santos Fernandes]

On 25/03/2025 08:01, 'Dimitry Sibiryakov' via firebird-devel wrote:
>
>> We should not talk about assignment to values, it makes no sense.
>
>   So I suggest to remove ability to modify loop variable from this
> proposal.
>

I'm not going to accept such suggestion as

1) the spec clears defines what happens in this case
2) who is afraid, do not need to do it
3) such a check would make engine code complex


Adriano

[Dimitry Sibiryakov]

Adriano dos Santos Fernandes wrote 25.03.2025 12:08:
> 3) such a check would make engine code complex

Doesn't engine already contain code that prohibit assignment to OLD context
variables?

--
WBR, SD.

[liviuslivius]

A aggree here with Adriano. Starting point and ending point of the loop should be frozen at the beggining of the loop. 

So "j" variable or any expression here should be cashed. 

But about variable "i", changing it inside the loop should raise an error.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Adriano dos Santos Fernandes <adri...@gmail.com>

Data: 25.03.2025 12:08 (GMT+01:00)

Do: firebir...@googlegroups.com

Temat: Re: [firebird-devel] Proposal: Range-based FOR statement

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/3492f52c-baaa-449c-afb7-1a7b41e66d6d%40gmail.com.

[Dmitry Yemanov]

Adriano et al,

> ## Description
>
> The range-based `FOR` statement is used to iterate over a range of
> values. The iteration is performed in increasing
> order when used with `TO` clause and in decreasing order when used with
> `DOWNTO` clause.
>
> ## Syntax
>
> ```
> [<label> :]
> FOR <variable> = <initial value> {TO | DOWNTO} <final value> DO
> <statement>
> ```

I understand that this is a handy shortcut for the most popular loop
type and everything more complex may be written using WHILE, but still
I'd prefer to have an optional [BY <offset>] part as offered in the
Mark's suggestion. If omitted, 1/-1 is implied. It's discussable whether
negative offsets should be allowed or rejected. Personally, I see no
problems with them.

Apart from that, I'm fine with the proposal. Every DBMS has its own PSQL
implementation and they're not compatible with each other, and the SQL
standard does not seem to define the range-based loop, so we're free to
choose what fits us better.


Dmitry

[Adriano dos Santos Fernandes]

On 25/03/2025 12:47, Dmitry Yemanov wrote:
> Adriano et al,
>
>> ## Description
>>
>> The range-based `FOR` statement is used to iterate over a range of
>> values. The iteration is performed in increasing
>> order when used with `TO` clause and in decreasing order when used with
>> `DOWNTO` clause.
>>
>> ## Syntax
>>
>> ```
>> [<label> :]
>> FOR <variable> = <initial value> {TO | DOWNTO} <final value> DO
>>      <statement>
>> ```
>
> I understand that this is a handy shortcut for the most popular loop
> type and everything more complex may be written using WHILE, but still
> I'd prefer to have an optional [BY <offset>] part as offered in the
> Mark's suggestion.

I like it.

> If omitted, 1/-1 is implied. It's discussable whether
> negative offsets should be allowed or rejected. Personally, I see no
> problems with them.
>

Then I'd go like PostgreSQL and raise error for BY values <= 0.

It should always be positive and TO/DOWNTO gives the direction.


Adriano

[Adriano dos Santos Fernandes]

On 27/03/2025 08:42, Adriano dos Santos Fernandes wrote:
> On 25/03/2025 12:47, Dmitry Yemanov wrote:
>> Adriano et al,
>>
>>> ## Description
>>>
>>> The range-based `FOR` statement is used to iterate over a range of
>>> values. The iteration is performed in increasing
>>> order when used with `TO` clause and in decreasing order when used with
>>> `DOWNTO` clause.
>>>
>>> ## Syntax
>>>
>>> ```
>>> [<label> :]
>>> FOR <variable> = <initial value> {TO | DOWNTO} <final value> DO
>>>      <statement>
>>> ```
>>
>> I understand that this is a handy shortcut for the most popular loop
>> type and everything more complex may be written using WHILE, but still
>> I'd prefer to have an optional [BY <offset>] part as offered in the
>> Mark's suggestion.
>

With BY, it looks like for me that we should also remove restriction of
"zero scale" variable allowing iterations like this:

DECLARE i NUMERIC(10,1);

FOR i = 10.1 TO 20.2 BY 1.1


Adriano

[James Starkey]

Yes.

Jim Starkey

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/5415b7d1-27f4-4049-a9ef-b0a73c606b4c%40gmail.com.

[Dmitry Yemanov]

28.03.2025 14:05, Adriano dos Santos Fernandes wrote:
>
> With BY, it looks like for me that we should also remove restriction of
> "zero scale" variable allowing iterations like this:
>
> DECLARE i NUMERIC(10,1);
>
> FOR i = 10.1 TO 20.2 BY 1.1

Sounds reasonable.


Dmitry

[liviuslivius]

I like this version, it is really functional

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Adriano dos Santos Fernandes <adri...@gmail.com>

Data: 28.03.2025 12:05 (GMT+01:00)

Do: firebir...@googlegroups.com

Temat: Re: [firebird-devel] Proposal: Range-based FOR statement