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)