CQM CQM - 2 months ago 15
SQL Question

Incrementing a number in a loop in plpgsql

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

For instance:

iterator float 4;

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 way to simulate incrementing a variable.

Answer

To increment a variable in plpgsql:

iterator := iterator + 1;

There is no ++ operator.

Also, the assignment operator of plpgsql is :=, not =.
And find correct syntax for loops in PL/pgSQL here.
Your code fragment could work like this:

DECLARE
   iterator float4 := 1;  -- you can init at declaration time

BEGIN
   WHILE iterator < 999
   LOOP
      iterator := iterator + 1;
      -- do stuff
   END LOOP;
END;

Another option would be a FOR loop:

FOR i in 1 .. 999   -- i would be an integer, not a float4
LOOP
   -- do stuff
END LOOP;