Incrementing a number in a loop in plpgsql

increment, plpgsql, postgresql, postgresql-8.4, sql

Solution

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).

Problem

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.

Original source

Related problems