Incrementing a number in a loop in plpgsql

Question

I couldn't find this immediately from the examples. I want to increment a variable in a loop, in a function.

For instance:

DECLARE
   iterator float4;
BEGIN
   iterator = 1;

    while iterator < 999
       .....
      iterator ++;
END;

How would this be done?

I was looking at this document about flow control:
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html

And none of them seem to be relevant for me, unless these are absolutely the only ways to simulate incrementing a variable.

Answer 1

For a sscce

DO $$
DECLARE
   counter INTEGER := 0 ; 
BEGIN
   WHILE counter <= 5 LOOP
      counter := counter + 1 ; 
      RAISE NOTICE 'Counter: %', counter;
   END LOOP ; 
END; $$

if you want to avoid declare variable (more concise)

DO $$
BEGIN
   FOR counter IN 1..5 LOOP
      RAISE NOTICE 'Counter: %', counter;
   END LOOP;
END; $$

credits

Answer 2

To increment a variable in plpgsql:

iterator := iterator + 1;

There is no ++ operator.

About the assignment operator in plpgsql:

• The forgotten assignment operator "=" and the commonplace ":="

Correct syntax for loops in PL/pgSQL in the manual.

Your code fragment would work like this:

DECLARE
   iterator float4 := 1;  -- we can init at declaration time
BEGIN
   WHILE iterator < 999
   LOOP
      iterator := iterator + 1;
      -- do stuff
   END LOOP;
END;

Simpler, faster alternative with a FOR loop:

   FOR i in 1 .. 999   -- i is integer automatically, not float4
   LOOP
      -- do stuff
   END LOOP;

The manual:

The variable name is automatically defined as type integer and exists only inside the loop (any existing definition of the variable name is ignored within the loop).