Luca Ferrari: FOR loops automatically declared variables in PL/PgSQL

In PL/PgSQL FOR loops the iterator is automatically declared, and this could bring some problems. FOR loops automatically declared variables in PL/PgSQL Consider the following simple function that returns a table made by three columns: CREATE OR REPLACE FUNCTION public.a_table() RETURNS TABLE( i int, j int, k int ) AS $CODE$ BEGIN FOR i IN 1 .. 2 LOOP FOR j IN 1 .. 2 LOOP FOR k IN 1 .. 2 LOOP RAISE INFO 'i=%, j=%, k=%', i, j, k; RETURN NEXT; END LOOP; END LOOP; END LOOP; END $CODE$ LANGUAGE plpgsql VOLATILE What is the result of invoking the above function? Depending on how you know the FOR loop in PL/PgSQL, it could be surprising: testdb=> select * from a_table(); INFO: i=1, j=1, k=1 INFO: i=1, j=1, k=2 INFO: i=1, j=2, k=1 INFO: i=1, j=2, k=2 INFO: i=2, j=1, k=1 INFO: i=2, j=1, k=2 INFO: i=2, j=2, k=1 INFO: i=2, j=2, k=2 i | j | k ---+---+--- | | | | | | | | | | | | | | | | (8 rows) Why is the result set empty even if the variables have values? Because the FOR iterator is automatically declared and scoped to the loop itself. The PostgreSQL Documentation explains it: The variable name is automatically defined as type integer and exists only inside the loop (any existing definition of the variable name is ignored It should be clear that I’m referring to the integer FOR loop variant here. However, the problem is that while i, j and k are defined as variables for the function (the returning columns), the FOR loops create variables with the same name but an innser scope, so that it is not possible to refer to the returning columns. Please note that the problem is not caught even with the warnings about shadowed variables : testdb=> SET plpgsql.extra_warnings TO 'shadowed_variables'; SET testdb=> select * from a_table(); INFO: i=1, j=1, k=1 INFO: i=1, j=[...]