CODEWITHSUNDEEP
databasepostgresqlloopsfor-loopplpgsql
A.Durant
A.Durant

Reputation: 51

Loop inside a FOR loop

I'd like to set up a query with a loop inside a for loop. I'm writing it like :

FOR ___ IN value..value BY 1 LOOP
  LOOP
    statement
    EXIT WHEN ...
  END LOOP;
END LOOP;

It returns me a syntax error around the second "loop", that's why I doubt the feasibility of such a loop inside a for loop.

I hope I made myself clear.

Upvotes: 2

Views: 5443

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

The LOOP statement in a FOR statement below works. *I use PostgreSQL 16.1.

DO $$
DECLARE
  num INT := 0;
BEGIN
  FOR num IN 1..3 BY 1 LOOP
    LOOP
      EXIT WHEN num >= 3; -- Here
      RAISE INFO 'Inner loop:num is %.', num;
      num := num + 1;
    END LOOP;
    RAISE INFO 'Outer loop:num is %.', num;
  END LOOP;
END
$$;

This is the output below:

INFO:  Inner loop:num is 1.
INFO:  Inner loop:num is 2.
INFO:  Outer loop:num is 3.
INFO:  Inner loop:num is 2.
INFO:  Outer loop:num is 3.
INFO:  Outer loop:num is 3.
DO

Upvotes: 0

Alex Yu
Alex Yu

Reputation: 3537

I see no problem to do a nested loop in plgpgsql in postgresql 11.10:

DO $$
DECLARE 
    i integer;
    j integer;
BEGIN
FOR i IN 1..2 LOOP
    FOR j IN 10..12 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
    RAISE NOTICE 'loop % %',i,j;
    END LOOP;
END LOOP;
END$$;

I see expected output:

loop 1 10
loop 1 11
loop 1 12
loop 2 10
loop 2 11
loop 2 12

Upvotes: 4

Daniel Barrett
Daniel Barrett

Reputation: 21

So, the initial problem I see is your counter is iterating on a range of "value..value"

Take a look at this code, the range should be 2 different values. Try this first.

do $$
begin 
  for counter in 1..6 by 2 loop
    raise notice 'counter: %', counter;
  end loop;
end; $$

Upvotes: 2

Related Questions