How to create a range from 1 to 100 in the Firebird selection instruction?

Question

How to create a range from 1 to 100 in the Firebird selection instruction?

I already found a way to convert a string to a list, but it doesn’t work for me because of the much wider range I need to generate How do I enter an array parameter from a value for a Firebird stored procedure? .

Is it possible to do such things without creating stored procedures?

Answer 1

One method is recursive CTE:

with recursive n as (
      select 1 as n
      from rdb$database
      union all
      select n.n + 1
      from n
      where n < 100
     )
select n.n
from n;

Answer 2

Besides the Gordon suggestion, you can also use a selective stored procedure to do this:

create procedure generate_range(startvalue integer, endvalue integer)
    returns (outputvalue integer)
as
begin
    outputvalue = startvalue;
    suspend;

    while (outputvalue < endvalue) do
    begin
        outputvalue = outputvalue + 1;
        suspend;
    end
end

Then you can use this as:

select outputvalue from generate_range(1, 100);

CTE, Gordon, , , CTE Firebird 1024.