Amazon redshift stored procedure, CONTINUE cannot be used outside a loop;

Question

Im building a stored procedure in amazon redshift. This is an example of what im trying to do.

CREATE OR REPLACE PROCEDURE test_sp1()
LANGUAGE plpgsql
AS $$
DECLARE v_test RECORD;
BEGIN
FOR v_test IN select * from pg_user
  LOOP
    RAISE INFO 'before loop';
    CONTINUE;
    RAISE INFO 'after loop';
  END LOOP;
END;
$$;
CALL test_sp1();

This piece of code gives me an exception "[42601][500310] Amazon Invalid operation: CONTINUE cannot be used outside a loop".

Why can i not use continue in this loop?

Why are you putting a LOOP within a FOR? CONTINUE would be used to exit from a nested loop. Doing a Continue in a single loop just means the loop would run again, which doesn't seem to make sense. What are you actually wanting to accomplish? — John Rotenstein
It's just an example. In reality my sp is different and very long, but the problem with continue is similar. — user1402866

Sasa Lotina Sasa Lotina · Accepted Answer · 2020-03-31T11:44:13

The error appears to be happening when CONTINUE passes control to the beginning of the loop but there's no more iterating to be done.

Amazon redshift stored procedure, CONTINUE cannot be used outside a loop;

1 Answers