Buy Me a Coffee☕

AFOR statement:

• can repeat aLOOP statement as long as there are values.

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

• can be run with aSELECT orINSERT,UPDATE orDELETE statement with aRETURNING clause.

• can be dynamically run with theEXECUTE which has aSELECT orINSERT,UPDATE orDELETE statement with aRETURNING clause. *My post explains anEXECUTE statement.

• can iterate an array. *My post explainsFOREACH andFOR statement with a 1D and 2D array.

*My post explainsLOOP,EXIT,CONTINUE andWHILE statement.

Now, you can create the PL/pgSQL functionmy_func() with theFOR statement whose range is1..3 and aLOOP statement as shown below:

CREATEFUNCTIONmy_func()RETURNSVOIDAS$$BEGINFORnumIN1..3LOOPRAISEINFO'%',num;ENDLOOP;END$$LANGUAGEplpgsql;

*Memos:

• You don't need declarenum local variable in aDECLARE clause but you can if you want

• My post explains a PL/pgSQL function.

• ARAISE statement can raise an error or message.

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

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

Next, you can useREVERSE with theFOR statement whose range is3..1 as shown below. *Be careful, if the range is1..3, no messages are raised:

CREATEFUNCTIONmy_func()RETURNSVOIDAS$$BEGINFORnumINREVERSE3..1LOOPRAISEINFO'%',num;ENDLOOP;END$$LANGUAGEplpgsql;

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

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

Next, you can use aBY clause with theFOR statement whose range is1..5 as shown below:

CREATEFUNCTIONmy_func()RETURNSVOIDAS$$BEGINFORnumIN1..5BY2LOOPRAISEINFO'%',num;ENDLOOP;END$$LANGUAGEplpgsql;

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

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

Next, you can use the inner and outerFOR statements whose ranges are3..3 and1..2 respectively as shown below:

CREATEFUNCTIONmy_func()RETURNSVOIDAS$$BEGINFORnumIN3..3LOOPFORnumIN1..2LOOPRAISEINFO'Inner loop:%',num;ENDLOOP;RAISEINFO'Outer loop:%',num;ENDLOOP;END$$LANGUAGEplpgsql;

Then, callingmy_func() raises 3 messages in the inner and outer loops, then exits the loop when the iterations of the last values2 and3 are finished respectively as shown below:

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

Next, you createperson table as shown below:

CREATETABLEperson(idINT,nameVARCHAR(20),ageINT);

Then, you insert 4 rows intoperson table as shown below:

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

Then, you can use theFOR statement with aSELECT statement as shown below:

CREATEFUNCTIONmy_func(minINT,maxINT)RETURNSVOIDAS$$DECLARErowRECORD;BEGINFORrowINSELECT*FROMpersonWHEREageBETWEENminANDmaxLOOPRAISEINFO'%',row;ENDLOOP;END$$LANGUAGEplpgsql;

*Memos:

• You must declarerow local variable in aDECLARE clause otherwise there isthe error.

• You cannot useREVERSE with theFOR statement which has SQL otherwise there is error so instead, you can useORDER BY id DESC for theSELECT statement.

• You cannot use aBY clause with theFOR statement which has SQL otherwise there is error so instead, you can useWHERE mod(id, 2) = 1 for theSELECT statement. *The doc explainsmod() in detail.

• You cannot use aSELECT INTO statement with aFOR statement otherwise there is the error(7).

• My post has the examples of aFOR statement with aRETURN NEXT statement.

Or, you can use theFOR statement with anEXECUTE statement which has aSELECT statement as shown below:

CREATEFUNCTIONmy_func(minINT,maxINT)RETURNSVOIDAS$$DECLARErowRECORD;BEGINFORrowINEXECUTE'SELECT * FROM person WHERE age BETWEEN $1 AND $2'USINGmin,maxLOOPRAISEINFO'%',row;ENDLOOP;END$$LANGUAGEplpgsql;

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

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

Next, you can use theFOR statement with aUPDATE statement as shown below. *You must set aRETURNING clause to theUPDATE statement with aFOR statement otherwise there is error:

CREATEFUNCTIONmy_func(my_ageINT,my_idINT)RETURNSVOIDAS$$DECLARErowRECORD;BEGINFORrowINUPDATEpersonSETage=my_ageWHEREid=my_idRETURNING*LOOPRAISEINFO'%',row;ENDLOOP;END$$LANGUAGEplpgsql;

Or, you can use theFOR statement with anEXECUTE statement which has aUPDATE statement as shown below:

CREATEFUNCTIONmy_func(my_ageINT,my_idINT)RETURNSVOIDAS$$DECLARErowRECORD;BEGINFORrowINEXECUTE'UPDATE person SET age = $1 WHERE id = $2 RETURNING *'USINGmy_age,my_idLOOPRAISEINFO'%',row;ENDLOOP;END$$LANGUAGEplpgsql;

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 ofDavid is updated to75 as shown below:

postgres=#SELECT*FROMperson;id|name|age----+--------+-----1|John|273|Robert|184|Mark|402|David|75(4rows)