while loop in Amazon redshift

Question

I am using sql server and we do have a while loop there which I have created in a Stored procedure.. I am evaluating redshift and looking for a equivalent in redshift amazon. Below is what I am trying to do, how do I do this in amazon redshift:

Declare
        @MaxId SmallInt,
        @CurrId SmallInt

Set @CurrId = 0

Set @MaxId = 10

While @CurrId <= @MaxId

    Select @CurrId

    set @CurrId = @CurrId + 1

end

Answer 1

Tested this in Redshift:

create or replace procedure just_a_loop()
language plpgsql
as $$
declare
    CurrId INTEGER := 0;
    MaxId INTEGER := 10;
begin
    while CurrId <= MaxId
    LOOP
        raise info 'CurrId = %', CurrId;
        CurrId = CurrId + 1;
    end LOOP;
    raise info 'Loop Statement Executed -_-||^';
end;
$$;

call just_a_loop();

INFO:  CurrId = 0
INFO:  CurrId = 1
INFO:  CurrId = 2
INFO:  CurrId = 3
INFO:  CurrId = 4
INFO:  CurrId = 5
INFO:  CurrId = 6
INFO:  CurrId = 7
INFO:  CurrId = 8
INFO:  CurrId = 9
INFO:  CurrId = 10
INFO:  Loop Statement Executed -_-||^
CALL

Answer 2

Amazon Redshift now supports SQL stored procedures to make migration to Amazon Redshift easier. Stored procedures are used to encapsulate logic for data transformation, data validation, and business-specific logic. By combining multiple SQL steps into a stored procedure, you can reduce round trips between your applications and the database.

Amazon Redshift supports stored procedures in PL/pgSQL dialect and can include variable declaration, control logic, loops, allow raising errors, support security definer, and other features. You can create stored procedures to perform functions without giving a user access to the underlying tables with security definer controls. You can find more information about creating and running stored procedures in the Amazon Redshift database developer guide.^AWS