DEV Community

Super Kai (Kazuya Ito)
Super Kai (Kazuya Ito)

Posted on • Updated on

FOR statement in PostgreSQL

Buy Me a Coffee

A FOR statement:

*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;
Enter fullscreen mode Exit fullscreen mode

*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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Next, you create person table as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

*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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)