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

Bwana

What do you really want to do? Stored procedure is not available in Redshift. – Dissever

Stored Procedures are now supported in Amazon Redshift from version 1.0.7287 (late April 2019). Please review the document "Creating Stored Procedures in Amazon Redshift" for more information on getting started with stored procedures. – Pyrrolidine

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

Augustaugusta

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

Foredate

Answer 3

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.

Soler

Note that since I answered this question, AWS added UDF capability to Redshift (see docs.aws.amazon.com/redshift/latest/dg/…), however what you are trying to accomplish does not look like it can be done with just a function. – Soler

Stored Procedures are now supported in Amazon Redshift from version 1.0.7287 (late April 2019). Please review the document "Creating Stored Procedures in Amazon Redshift" for more information on getting started with stored procedures. – Pyrrolidine