FOR statement in PostgreSQL

Buy Me a Coffee☕

A FOR statement:

• can repeat a LOOP statement as long as there are values.

• can be used only with a PL/pgSQL function and procedure and DO statement.

• can be run with a SELECT or INSERT, UPDATE or DELETE statement with a RETURNING clause.

• can be dynamically run with the EXECUTE which has a SELECT or INSERT, UPDATE or DELETE statement with a RETURNING clause. *My post explains an EXECUTE statement.

• can iterate an array. *My post explains FOREACH and FOR statement with a 1D and 2D array.

*My post explains LOOP, EXIT, CONTINUE and WHILE statement.

Now, you can create the PL/pgSQL function my_func() with the FOR statement whose range is 1..3 and a LOOP statement as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN 1..3 LOOP
    RAISE INFO '%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;

*Memos:

• You don't need declare num local variable in a DECLARE clause but you can if you want

• My post explains a PL/pgSQL function.

• A RAISE statement can raise an error or message.

Then, calling my_func() raises 3 messages in the loop, then exits the loop when the iteration of the last value 3 is finished as shown below:

postgres=# SELECT my_func();
INFO:  1
INFO:  2
INFO:  3
 my_func
---------

(1 row)

Next, you can use REVERSE with the FOR statement whose range is 3..1 as shown below. *Be careful, if the range is 1..3, no messages are raised:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN REVERSE 3..1 LOOP
    RAISE INFO '%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Then, calling my_func() raises 3 messages in the loop, then exits the loop when the iteration of the last value 1 is finished as shown below:

postgres=# SELECT my_func();
INFO:  3
INFO:  2
INFO:  1
 my_func
---------

(1 row)

Next, you can use a BY clause with the FOR statement whose range is 1..5 as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN 1..5 BY 2 LOOP
    RAISE INFO '%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Then, calling my_func() raises 3 messages in the loop by 2 steps, then exits the loop when the iteration of the last value 5 is finished as shown below:

postgres=# SELECT my_func();
INFO:  1
INFO:  3
INFO:  5
 my_func
---------

(1 row)

Next, you can use the inner and outer FOR statements whose ranges are 3..3 and 1..2 respectively as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN 3..3 LOOP
    FOR num IN 1..2 LOOP
      RAISE INFO 'Inner loop:%', num;
    END LOOP;
    RAISE INFO 'Outer loop:%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Then, calling my_func() raises 3 messages in the inner and outer loops, then exits the loop when the iterations of the last values 2 and 3 are finished respectively as shown below:

postgres=# SELECT my_func();
INFO:  Inner loop:1
INFO:  Inner loop:2
INFO:  Outer loop:3
 my_func
---------

(1 row)

Next, you create person table as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);

Then, you insert 4 rows into person table as shown below:

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32), (3, 'Robert', 18), (4, 'Mark', 40);

Then, you can use the FOR statement with a SELECT statement as shown below:

CREATE FUNCTION my_func(min INT, max INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN SELECT * FROM person WHERE age BETWEEN min AND max LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;

*Memos:

• You must declare row local variable in a DECLARE clause otherwise there is the error.

• You cannot use REVERSE with the FOR statement which has SQL otherwise there is error so instead, you can use ORDER BY id DESC for the SELECT statement.

• You cannot use a BY clause with the FOR statement which has SQL otherwise there is error so instead, you can use WHERE mod(id, 2) = 1 for the SELECT statement. *The doc explains mod() in detail.

• You cannot use a SELECT INTO statement with a FOR statement otherwise there is the error(7).

• My post has the examples of a FOR statement with a RETURN NEXT statement.

Or, you can use the FOR statement with an EXECUTE statement which has a SELECT statement as shown below:

CREATE FUNCTION my_func(min INT, max INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN EXECUTE 'SELECT * FROM person WHERE age BETWEEN $1 AND $2' USING min, max LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Then, calling my_func() raises 2 messages in the loop, then exits the loop when the iteration of the last row is finished as shown below:

postgres=# SELECT my_func(30, 40);
INFO:  (2,David,32)
INFO:  (4,Mark,40)
 my_func
---------

(1 row)

Next, you can use the FOR statement with a UPDATE statement as shown below. *You must set a RETURNING clause to the UPDATE statement with a FOR statement otherwise there is error:

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN UPDATE person SET age = my_age WHERE id = my_id RETURNING * LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Or, you can use the FOR statement with an EXECUTE statement which has a UPDATE statement as shown below:

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN EXECUTE 'UPDATE person SET age = $1 WHERE id = $2 RETURNING *' USING my_age, my_id LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;

Then, it raises a message in the loop, then exits the loop when the iteration of the last row is finished as shown below:

postgres=# SELECT my_func(75, 2);
INFO:  (2,David,75)
 my_func
---------

(1 row)

Then, age of David is updated to 75 as shown below:

postgres=# SELECT * FROM person;
 id |  name  | age
----+--------+-----
  1 | John   |  27
  3 | Robert |  18
  4 | Mark   |  40
  2 | David  |  75
(4 rows)