DEV Community

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

Posted on • Edited on

FOREACH and FOR statements with arrays in PostgreSQL

#postgres #foreach #for #array

Buy Me a Coffee☕

A FOREACH statement:

  • can repeat a LOOP statement as long as there are array values.

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

Now, you can create the DO statement which has a FOREACH statement with a LOOP statement to iterate a 1D(one-dimensional) array as shown below:

DO $$ DECLARE temp VARCHAR; _1d_arr VARCHAR[] := ARRAY['a','b','c']; BEGIN FOREACH temp SLICE 0 IN ARRAY _1d_arr LOOP RAISE INFO '%', temp; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

*Memos:

  • SLICE 0 can iterate zero or more elements.

  • SLICE 1, SLICE 2, SLICE 3, etc can iterate zero or more arrays.

  • For a 1D array, you can set SLICE 0 or SLICE 1 so if you set SLICE 2, there is error.

  • You can omit SLICE 0, but the way of the iteration is same as SLICE 0.

  • The type of temp with SLICE 0 must not be array (VARCHAR[]) otherwise there is error.

  • You can set VARCHAR[][], VARCHAR[][][], etc to _1d_arr, then the type of _1d_arr is automatically converted to VARCHAR[](CHARACTER VARYING[]) but if you set VARCHAR to _1d_arr, the type of arr is VARCHAR(CHARACTER VARYING), then there is the error in the FOREACH statement.

  • You must declare temp local variable in a DECLARE clause otherwise there is the error.

  • My post explains DO statement.

  • My post explains LOOP statement.

  • A RAISE statement can raise an error or message.

  • My answer explains how to create and use the 1D array of VARCHAR[].

  • My answer explains how to create and use the 1D array of INT[].

Then, all elements are outputted as shown below:

INFO: a INFO: b INFO: c DO
Enter fullscreen mode Exit fullscreen mode

Next, you can set SLICE 1 with temp of array type (VARCHAR[]) as shown below:

DO $$ DECLARE -- ↓ Here temp VARCHAR[]; _1d_arr VARCHAR[] := ARRAY['a','b','c']; BEGIN -- ↓ ↓ ↓ ↓ FOREACH temp SLICE 1 IN ARRAY _1d_arr LOOP RAISE INFO '%', temp; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

*Memos:

  • The type of temp with SLICE 1 must not be non-array (VARCHAR) otherwise there is error.

  • You can set VARCHAR[][], VARCHAR[][][], etc to temp, then the type of temp is automatically converted to VARCHAR[](CHARACTER VARYING[]).

Then, the whole 1D array is outputted as shown below:

INFO: {a,b,c} DO
Enter fullscreen mode Exit fullscreen mode

Next, you can iterate a 2D(two-dimensional) array with a FOREACH statement as shown below:

DO $$ DECLARE temp VARCHAR; _2d_arr VARCHAR[] := ARRAY[ ['a','b','c'], ['d','e','f'] ]; BEGIN FOREACH temp SLICE 0 IN ARRAY _2d_arr LOOP RAISE INFO '%', temp; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

*Memos:

  • For a 2D array, you can set SLICE 0, SLICE 1 or SLICE 2 so if you set SLICE 3, there is error.

  • My post explains how to create and use a 2D array.

Then, all elements are outputted as shown below:

INFO: a INFO: b INFO: c INFO: d INFO: e INFO: f DO
Enter fullscreen mode Exit fullscreen mode

Next, you can set SLICE 1 with temp of array type (VARCHAR[]) to iterate a 2D array as shown below:

DO $$ DECLARE temp VARCHAR[]; _2d_arr VARCHAR[] := ARRAY[ ['a','b','c'], ['d','e','f'] ]; BEGIN FOREACH temp SLICE 1 IN ARRAY _2d_arr LOOP RAISE INFO '%', temp; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

Then, 2 arrays are outputted as shown below:

INFO: {a,b,c} INFO: {d,e,f} DO
Enter fullscreen mode Exit fullscreen mode

Next, you can set SLICE 2 with temp of array type (VARCHAR[]) to iterate a 2D array as shown below:

DO $$ DECLARE temp VARCHAR[]; _2d_arr VARCHAR[] := ARRAY[ ['a','b','c'], ['d','e','f'] ]; BEGIN FOREACH temp SLICE 2 IN ARRAY _2d_arr LOOP RAISE INFO '%', temp; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

Then, the whole 2D array is outputted as shown below:

INFO: {{a,b,c},{d,e,f}} DO
Enter fullscreen mode Exit fullscreen mode

In addition, you can use a FOR statement with a LOOP statement to iterate a 1D array as shown below. *My post explains FOR statement:

DO $$ DECLARE _1d_arr VARCHAR[] := ARRAY['a','b','c']; BEGIN FOR num IN 1..3 LOOP RAISE INFO '%', _1d_arr[num]; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

Or, you can use array_lower() and array_upper() as shown below:

DO $$ DECLARE _1d_arr VARCHAR[] := ARRAY['a','b','c']; BEGIN FOR num IN array_lower(_1d_arr, 1)..array_upper(_1d_arr, 1) LOOP RAISE INFO '%', _1d_arr[num]; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

*Memos:

  • You can replace array_upper() with array_length().

  • The doc explains array_lower(), array_upper() and array_length() in detail

Then, all elements are outputted as shown below:

INFO: a INFO: b INFO: c DO
Enter fullscreen mode Exit fullscreen mode

And, you can use a FOR statement with a LOOP statement to iterate a 2D array as shown below:

DO $$ DECLARE _2d_arr VARCHAR[] := ARRAY[ ['a','b','c'], ['d','e','f'] ]; BEGIN FOR num1 IN 1..2 LOOP FOR num2 IN 1..3 LOOP RAISE INFO '%', _2d_arr[num1][num2]; END LOOP; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

Or, you can use array_lower() and array_upper() as shown below. *You can replace array_upper() with array_length():

DO $$ DECLARE _2d_arr VARCHAR[] := ARRAY[ ['a','b','c'], ['d','e','f'] ]; BEGIN FOR num1 IN array_lower(_2d_arr, 1)..array_upper(_2d_arr, 1) LOOP FOR num2 IN array_lower(_2d_arr, 2)..array_upper(_2d_arr, 2) LOOP RAISE INFO '%', _2d_arr[num1][num2]; END LOOP; END LOOP; END $$;
Enter fullscreen mode Exit fullscreen mode

Then, all elements are outputted as shown below:

INFO: a INFO: b INFO: c INFO: d INFO: e INFO: f DO
Enter fullscreen mode Exit fullscreen mode

Top comments (0)