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.

Midnight

Answer 1

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

Chroma

within my loop I am doing sql queries, those statements use = correct, ie UPDATE mytable SET this_id = finalid... – Midnight

@CQM: Correct, those are embedded SQL statements, where = is the assignement operator. More here. – Chroma

Answer 2

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

Packet