Looping through arrays in PL/pgSQL
The PL/pgSQL FOREACH loop brings dedicated syntax for looping over the contents of an array.
Overview
Note: See array_lower(), array_upper(), array_ndims() and cardinality() for descriptions of the functions that the following account mentions. It also mentions "row-major order". See Joint semantics, within the section "Functions for reporting the geometric properties of an array", for an explanation of this term. Syntax and semantics shows where, in the FOREACH loop header, the SLICE keyword is used.
-
When the operand of the
SLICEclause is0, and for the general case where the iterand array has any number of dimensions, YSQL assigns its successive values, in row-major order, to the loop iterator. Here, its effect is functionally analogous to that ofunnest(). -
For the special case where the iterand array is one-dimensional, the
FOREACHloop is useful only when the operand of theSLICEclause is0. In this use, it is a syntactically more compact way to achieve the effect that is achieved with aFOR var INloop like this:for var in array_lower(iterand_arr, 1)..array_upper(iterand_arr, 1) loop ... iterand_arr[var] ... end loop; -
When the operand of the
SLICEclause is greater than0, and when the dimensionality of the iterand array is greater than1, theFOREACHloop provides functionality thatunnest()cannot provide. Briefly, when the iterand array hasndimensions and the operand of theSLICEclause iss, YSQL assigns slices (that is, subarrays) of dimensionalitysto the iterator. The values in such a slice are those from the iterand array that remain when the distinct values of the first(n - s)indexes are used to drive the iteration. These two pseudocode blocks illustrate the idea:-- In this example, the SLICE operand is 1. -- As a consequence, array_ndims(iterator_array) is 1. -- Assume that array_ndims(iterand_arr) is 4. -- There are therefore (4 - 1) = 3 nested loops in this pseudocode. for i in array_lower(iterand_arr, 1)..array_upper(iterand_arr, 1) loop for j in array_lower(iterand_arr, 2)..array_upper(iterand_arr, 2) loop for k in array_lower(iterand_arr, 3)..array_upper(iterand_arr, 3) loop the (i, j, k)th iterator_array is set to iterand_arr[i][j][k][ for all values the remaining 4th index ] end loop; end loop; end loop;-- In this example, the SLICE operand is 3. -- As a consequence, array_ndims(iterator_array) is 3. -- Assume that array_ndims(iterand_arr) is 4. -- There is therefore (4 - 3) = 1 nested loop in this pseudocode. for i in array_lower(iterand_arr, 1)..array_upper(iterand_arr, 1) loop the (i)th iterator_array is set to iterand_arr[i][ for all values the remaining 2nd, 3rd, and 4th indexes ] end loop;
The examples below clarify the behavior of FOREACH.
Syntax and semantics
[ <<label>> ]
FOREACH var [ SLICE non_negative_integer_literal ] IN ARRAY expression LOOP
statements
END LOOP [ label ];
-
varmust be explicitly declared before theFOREACHloop. -
The operand of the optional
SLICEclause must be a non-negativeintliteral. -
Assume that
expressionhas the data typesome_type[]. - WhenSLICE 0is used,varmust be declared assome_type.- When the
SLICEclause's operand is positive,varmust be declared assome_type[].
- When the
-
SLICE 0has the same effect as omitting theSLICEclause. -
When
SLICE 0is used, or theSLICEclause is omitted, YSQL assigns each in turn of the array's values, visited in row-major order, tovar. -
When the
SLICEclause's operand is positive, then YSQL assigns successive slices of the iterand array tovaraccording to the following rule:- The extracted slices all have the same dimensionality given by this equality:
array_ndims(iterator_array) = slice_operand. - The number of extracted slices is given by this equality:
number_of_slices = cardinality(iterand_array)/cardinality(iterator_array). - The value of the
SLICEoperand must not exceedarray_ndims(iterator_array). - When the value of the
SLICEoperand is equal toarray_ndims(iterator_array) - 1, theFOREACHloop produces just a single iterator array value, and this is identical to the iterand array.
- The extracted slices all have the same dimensionality given by this equality:
-
The useful range for the
SLICEoperand is therefore0..(array_ndims(iterator_array) - 1).
Looping over the values in an array without the SLICE keyword
This loop is functionally identical to the FOR var IN loop. However, in the FOR loop, YSQL automatically defines var with the type int so that its scope is limited to the loop; but in the FOREACH loop, var must be explicitly declared, as noted in the "Syntax and semantics" section above.
do $body$
declare
arr1 int[] := array[1, 2];
arr2 int[] := array[3, 4, 5];
var int;
begin
<<"FOREACH eaxmple">>
foreach var in array arr1||arr2 loop
raise info '%', var;
end loop "FOREACH eaxmple";
end;
$body$;
It shows this (after manually stripping the "INFO:" prompt):
1
2
3
4
5
The || operator is used only to emphasize that the iterand can be any expression whose data type is an array.
The next loop shows these things of note:
- The iterand array is multidimensional.
- The array type is "rt[]" where rt is a user-defined "row" type.
- The syntax spot where "var" is used above need not be occupied by a single variable. Rather, "f1" and "f2" are used, to correspond to the fields in "rt".
- The
FOREACHloop is followed by a "cursor" loop whoseSELECTstatement usesunnest(). - The
FOREACHloop is more terse than the "cursor" loop. In particular, you can use the pair of declared variables "f1" and "f2" without any fuss (just as you could have used a single variable "r" of type "rt" without any fuss) as the iterator. YSQL looks after the proper assignment in both cases. But when you useunnest(), you have to look after this yourself.
create type rt as (f1 int, f2 text);
do $body$
declare
a1 rt[] := array[(1, 'dog'), (2, 'cat')];
a2 rt[] := array[(3, 'ant'), (4, 'rat')];
arr rt[] := array[a1, a2];
f1 int;
f2 text;
begin
raise info '';
raise info 'FOREACH';
foreach f1, f2 in array arr loop
raise info '% | %', f1::text, f2;
end loop;
raise info '';
raise info 'unnest()';
for f1, f2 in (
with v as (
select unnest(arr) as r)
select (r).f1, (r).f2 from v)
loop
raise info '% | %', f1::text, f2;
end loop;
end;
$body$;
It shows this:
FOREACH
1 | dog
2 | cat
3 | ant
4 | rat
unnest()
1 | dog
2 | cat
3 | ant
4 | rat
This shows that this use of the FOREACH loop (with an implied 0 as the SLICE clause's operand) is functionally equivalent to unnest().
Looping over the contents of a multidimensional array taking advantage of a non-zero SLICE operand
First, store a three-dimensional two-by-two-by-two array in a ::text[] field in a single-row table. Each of the subsequent DO blocks uses it.
create table t(k int primary key, arr text[] not null);
insert into t(k, arr) values(1, '
{
{
{001,002},
{003,004}
},
{
{005,006},
{007,008}
}
}'::text[]);
Next, show the outcome when a bad value is used for the SLICE operand:
do $body$
declare
arr constant text[] not null := (select arr from t where k = 1);
slice_iterator text[] not null := '{?}';
begin
raise info 'array_ndims(arr): %', array_ndims(arr)::text;
foreach slice_iterator slice 4 in array arr loop
raise info '%', slice_iterator::text;
end loop;
end;
$body$;
It shows array_ndims(arr): 3 and then it reports this error:
2202E: slice dimension (4) is out of the valid range 0..3
This test confirms that the value of the SLICE operand must not exceed the iterand array's dimensionality.
As has been seen, SLICE 0 (or equivalently omitting the SLICE clause) scans the array values in row-major order. The next test, with SLICE 3, demonstrates the meaning when the SLICE operand is equal to the iterand array's dimensionality.
do $body$
declare
arr constant text[] not null := (select arr from t where k = 1);
slice_iterator text[] not null := '{?}';
n int not null := 0;
begin
raise info 'FOREACH SLICE 3';
n := 0;
foreach slice_iterator slice 3 in array arr loop
assert
(array_ndims(slice_iterator) = 3) and
(slice_iterator = arr) ,
'assert failed';
n := n + 1;
raise info '% | %', n::text, slice_iterator::text;
end loop;
end;
$body$;
It shows this:
FOREACH SLICE 3
1 | {{{001,002},{003,004}},{{005,006},{007,008}}}
The FOREACH loop generates just a single iterator slice. And, as the assert shows, this is identical to the iterand array. In other words, setting the SLICE operand to be equal to the iterand array's dimensionality, while the result is well-defined, is not useful. So, using this example iterand array, the useful range for the SLICE operand is 0..2.
The next test uses SLICE 2:
do $body$
declare
arr constant text[] not null := (select arr from t where k = 1);
slice_iterator text[] not null := '{?}';
n int not null := 0;
begin
raise info 'FOREACH SLICE 2';
n := 0;
foreach slice_iterator slice 2 in array arr loop
assert (array_ndims(slice_iterator) = 2), 'assert failed';
n := n + 1;
raise info '% | %', n::text, slice_iterator::text;
end loop;
end;
$body$;
It shows this:
FOREACH SLICE 2
1 | {{001,002},{003,004}}
2 | {{005,006},{007,008}}
As the assert shows, the operand of the SLICE operator determines the dimensionality of the iterator slices.
The next test uses SLICE 1:
do $body$
declare
arr constant text[] not null := (select arr from t where k = 1);
slice_iterator text[] not null := '{?}';
n int not null := 0;
begin
raise info 'FOREACH SLICE 1';
n := 0;
foreach slice_iterator slice 1 in array arr loop
assert (array_ndims(slice_iterator) = 1), 'assert failed';
n := n + 1;
raise info '% | %', n::text, slice_iterator::text;
end loop;
end;
$body$;
It shows this:
FOREACH SLICE 1
1 | {001,002}
2 | {003,004}
3 | {005,006}
4 | {007,008}
Once again, the assert shows that the operand of the SLICE operator determines the dimensionality of the iterator slices.
The last FOREACH test uses SLICE 0. Notice that, now, the iterator is declared as the scalar text variable "var":
do $body$
declare
arr constant text[] not null := (select arr from t where k = 1);
var text not null := '?';
n int not null := 0;
begin
raise info 'FOREACH SLICE 0';
n := 0;
foreach var slice 0 in array arr loop
n := n + 1;
raise info '% | %', n::text, var;
end loop;
end;
$body$;
It shows this:
FOREACH SLICE 0
1 | 001
2 | 002
3 | 003
4 | 004
5 | 005
6 | 006
7 | 007
8 | 008
This is functionally equivalent to unnest() as the final test shows:
do $body$
<<b>>declare
arr constant text[] not null := (select arr from t where k = 1);
var text not null := '?';
n int not null := 0;
begin
raise info 'unnest()';
n := 0;
for b.n, b.var in (
with
v1 as (
select unnest(arr) as var),
v2 as (
select
v1.var,
row_number() over(order by v1.var) as n
from v1)
select v2.n, v2.var from v2)
loop
n := n + 1;
raise info '% | %', n::text, var;
end loop;
end b;
$body$;
It shows this:
unnest()
2 | 001
3 | 002
4 | 003
5 | 004
6 | 005
7 | 006
8 | 007
9 | 008
Using FOREACH to iterate over the elements in an array of DOMAIN values
You need to be aware of some special considerations to implement this scenario. Using FOREACH with an array of DOMAINs, within the dedicated section Using an array of DOMAIN values explains what you need to know.
Using a wrapper PL/pgSQL table function to expose the SLICE operand as a formal parameter
The fact that the SLICE operand must be a literal means that there are only two ways two parameterize this—and neither is satisfactory for real application code. Each uses a table function whose input is the iterand array and the value for the SLICE operand, and whose output is a SETOF iterator array values.
- The first approach is to encapsulate some particular range of
SLICEoperand values in an ordinary statically defined function that uses aCASEstatement to select theFOREACHloop that has the requiredSLICEoperand literal. This is unsatisfactory because you have to decide the range ofSLICEoperand values that you'll support up front. - The second approach overcomes the limitation of the up front determination of the supported range of
SLICEoperand values by encapsulating code in, a statically defined function, that in turn dynamically generates a function with the requiredFOREACHloop andSLICEoperand value and that then invokes it dynamically. This is unsatisfactory because it's some effort to implement and test such an approach. But it's unsatisfactory mainly because of the performance cost that dynamic generation and execution brings.
However, the requirements specification for real application code is unlikely to need more than one, or possibly just a few, specific values for the SLICE operand. Therefore, in overwhelming majority of practically important use cases, you can write exactly the code you need where you need it.
The code that follows uses the first approach. It's included here because it demonstrates a generically valuable PL/pgSQL programming technique: user-defined functions and procedures with polymorphic formal parameters (in this case anyarray and anyelement). The examples also use assert statements to confirm that the expected relationships hold between these quantities:
- the dimensionality of the iterand array
- the value of the
SLICEoperand - the lengths along the iterand array's dimensions
- the cardinalities of the iterand array and the iterator arrays
- the number of returned iterator values.
Here is the basic encapsulation. It's hard-coded to handle values for the SLICE operand in the range 0..4.
Recall that the iterator for SLICE 0 is a scalar and that the iterators for other values of the SLICE operand are arrays. And recall that a pair of functions with the same definitions of the input formal parameters cannot be overload-distinguished by the data type of their return values. For this reason, the encapsulation of the FOREACH loop for SLICE 0 is a dedicated function with just one input formal parameter: the iterand array. And the encapsulation of the FOREACH loop for other values of the SLICE operand is a second function with two input formal parameters: the iterand array and the value of the SLICE operand. Here they are:
-- First overload
create function array_slices(arr in anyarray)
returns table(ret anyelement)
language plpgsql
as $body$
declare
no_of_values int not null := 0;
begin
-- "slice 0" means the same
-- as omitting the "slice" clause.
foreach ret slice 0 in array arr
loop
no_of_values := no_of_values + 1;
return next;
end loop;
assert
(no_of_values = cardinality(arr)),
'array_slices 1st overload: no_of_values assert failed';
end;
$body$;
And:
-- Second overload
create function array_slices(arr in anyarray, slice_operand in int)
returns table(ret anyarray)
language plpgsql
as $body$
declare
no_of_values int not null := 0;
lengths_product int not null := 0;
begin
case slice_operand
when 1 then
lengths_product := array_length(arr, 4);
foreach ret slice 1 in array arr
loop
no_of_values := no_of_values + 1;
assert
(array_ndims(ret) = 1) and
(cardinality(ret) = lengths_product) ,
'assert failed';
return next;
end loop;
assert
(no_of_values = cardinality(arr)/lengths_product),
'array_slices 2nd overload: no_of_values assert #1 failed';
when 2 then
lengths_product := array_length(arr, 4) *
array_length(arr, 3);
foreach ret slice 2 in array arr
loop
no_of_values := no_of_values + 1;
assert
(array_ndims(ret) = 2) and
(cardinality(ret) = lengths_product) ,
'assert failed';
return next;
end loop;
assert
(no_of_values = cardinality(arr)/lengths_product),
'array_slices 2nd overload: no_of_values assert #2 failed';
when 3 then
lengths_product := array_length(arr, 4) *
array_length(arr, 3) *
array_length(arr, 2);
foreach ret slice 3 in array arr
loop
no_of_values := no_of_values + 1;
assert
(array_ndims(ret) = 3) and
(cardinality(ret) = lengths_product) ,
'assert failed';
return next;
end loop;
assert
(no_of_values = cardinality(arr)/lengths_product),
'array_slices 2nd overload: no_of_values assert #3 failed';
when 4 then
lengths_product := array_length(arr, 4) *
array_length(arr, 3) *
array_length(arr, 2) *
array_length(arr, 1);
foreach ret slice 4 in array arr
loop
no_of_values := no_of_values + 1;
assert
(array_ndims(ret) = 4) and
(cardinality(ret) = lengths_product) ,
'assert failed';
return next;
end loop;
assert
(no_of_values = cardinality(arr)/lengths_product),
'array_slices 2nd overload: no_of_values assert #4 failed';
else
raise exception 'slice_operand > 4 not supported';
end case;
end;
$body$;
You can see that each leg of the CASE is "generated" formulaically—albeit manually—by following a pattern that could be parameterized. You can use these encapsulations for iterand arrays of any dimensionality. But you must take responsibility for following the rule that the value of the SLICE operand must fall within the acceptable range. Otherwise, you'll get the error that was demonstrated above:
2202E: slice dimension % is out of the valid range 0..%
Here is the test harness. Both this procedure and the function that generates the to-be-tested iterand array are hard-coding for a dimensionality of 4.
-- Exercise each of the meaningful calls to array_slices().
--
-- You cannot declare local variables as "anyelement" or "anyarray".
-- (The attempt causes a compilation error). It's obvious why.
-- It's the caller's responsibility to determine the
-- real type by using appropriate actual arguments.
-- "val" (scalar) and "slice" (array) are needed as FOREACH loop runners.
-- "in out" is used to avoid the nominal performance penalty
-- of extra copying brought by plain "out".
-- The caller has no interest in whatever values they have
-- on return from this procedure.
--
-- NOTE: while you _can_ declare a local variable as
-- "some_formal%type", you _cannot_ use that mechanism to declare
-- a scalar with the data type that defines an array when
-- all you have to anchor "%type" to is the array.
--
create procedure test_array_slices(
-- The "real" formal.
arr in anyarray,
-- used as "local varables
val in out anyelement,
slice in out anyarray)
language plpgsql
as $body$
declare
arr_ndims constant int := array_ndims(arr);
begin
assert
(arr_ndims = 4),
'assert failed: test_array_slices() requires a 4-d array';
raise info '%', array_dims(arr);
declare
len_1 constant int := array_length(arr, 1);
len_2 constant int := array_length(arr, 2);
len_3 constant int := array_length(arr, 3);
len_4 constant int := array_length(arr, 4);
expected_slice_cardinalities constant int[] not null :=
array[
len_4,
len_4*len_3,
len_4*len_3,
len_4*len_3*len_2,
len_4*len_3*len_2*len_1];
slice_cardinality int not null := 0;
-- val anyelement not null := '?';
-- slice anyarray not null := '{?}';
begin
raise info ''; raise info 'slice_operand: %', 0;
for val in (select array_slices(arr)) loop
raise info '%', val::text;
end loop;
for slice_operand in 1..arr_ndims loop
raise info ''; raise info 'slice_operand: %', slice_operand;
for slice in (select array_slices(arr, slice_operand)) loop
slice_cardinality := cardinality(slice);
assert
(array_ndims(slice) = slice_operand) ,
(slice_cardinality = expected_slice_cardinalities[slice_operand]) ,
'assert failed.';
raise info '%', slice::text;
if slice_operand = arr_ndims then
assert (slice = arr), 'assert (slice = arr) failed';
end if;
end loop;
end loop;
end;
end;
$body$;
Here is a function to generate a four-dimensional array. Notice that the actual argument for the "lengths" formal parameter must be a one-dimensional int[] array with four values. These specify the lengths along each of the output array's dimensions.
create function four_d_array(lengths in int[])
returns text[]
language plpgsql
as $body$
declare
lengths_ndims constant int := array_ndims(lengths);
lengths_cardinality constant int := cardinality(lengths);
begin
assert
(lengths_ndims = 1) and
(lengths_cardinality = 4) ,
'assert failed: four_d_array() creates only a 4-d array.';
declare
-- Take the default for array_fill's optional 2nd formal:
-- all lower bounds are 1.
arr text[] not null := array_fill('00'::text, lengths);
begin
-- For readability of the results, define the created array's values so that,
-- when scanned in row-major order, they are seen to be a dense series
-- that increases in even steps, 001, 002, 003, and so on.
declare
n int not null := 0;
begin
for i1 in 1..lengths[1] loop
for i2 in 1..lengths[2] loop
for i3 in 1..lengths[3] loop
for i4 in 1..lengths[4] loop
n := n + 1;
arr[i1][i2][i3][i4] := ltrim(to_char(n, '009'));
end loop;
end loop;
end loop;
end loop;
end;
assert
(array_ndims(arr) = lengths_cardinality),
'assert failed.';
-- Sanity check. Include to demonstrate the useful
-- terseness of the FOREACH loop.
declare
product int not null := 1;
len int not null := 0;
begin
foreach len in array lengths loop
product := product*len;
end loop;
assert
(cardinality(arr) = product) ,
'assert failed.';
end;
return arr;
end;
end;
$body$;
And here is one example test invocation:
do $body$
declare
arr constant text[] not null := four_d_array('{2, 2, 2, 2}'::int[]);
dummy_var text := '?';
dummy_arr text[] := '{/}';
begin
call test_array_slices(arr, dummy_var, dummy_arr);
end;
$body$;
It produces this result:
[1:2][1:2][1:2][1:2]
slice_operand: 0
001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
slice_operand: 1
{001,002}
{003,004}
{005,006}
{007,008}
{009,010}
{011,012}
{013,014}
{015,016}
slice_operand: 2
{{001,002},{003,004}}
{{005,006},{007,008}}
{{009,010},{011,012}}
{{013,014},{015,016}}
slice_operand: 3
{{{001,002},{003,004}},{{005,006},{007,008}}}
{{{009,010},{011,012}},{{013,014},{015,016}}}
slice_operand: 4
{{{{001,002},{003,004}},{{005,006},{007,008}}},{{{009,010},{011,012}},{{013,014},{015,016}}}}