Using RedShift CURSOR to insert and iterate

Question

I recently found that RedShift supports CURSOR, and more specifically it supports the commands: DECLARE & FETCH. I managed to create a cursor and fetch some of its rows but didn't find a way to:

1. Insert the fetched results into neither table or variable.
2. Iterate over the rows of the declared cursor in a dynamic fashion (based on while or any logical test)

I didn't find any documentation on Amazon on how to do that, does someone know if that is even possible? Thanks.

Answer 1

You can easily achieve this by creating a stored procedure which supports variables. You can read a dataset iterate through it and perform your logic.

The following example shows a procedure with output arguments. Arguments are input (IN), input and output (INOUT), and output (OUT).

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, 
f2 INOUT varchar(256), out_var OUT varchar(256))

AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)

source - https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-create.html