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

There is no procedural language available in Redshift. Cursors are available, but no variables, stored procedures, or user created functions. You will have to use an external process to do what you are asking.

Answer 2

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 3

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