CODEWITHSUNDEEP
sqlarraysdatabasepostgresqlmultidimensional-array
JohnDorian
JohnDorian

Reputation: 21

How to loop over a 2d array in PostgreSQL?

I am trying to loop over a 2D array getting the first and second value from each row to update a table

CREATE OR REPLACE FUNCTION fc_update_arrangement(input_arrangementid int, input_NAME text, input_price money, input_expirationdate date, products int[][])
RETURNS void AS
$BODY$
BEGIN
  UPDATE arrangement SET "NAME" = $2, price = $3, expirationdate = $4 WHERE arrangementid = $1;
  -- loop through array getting the first and second value
  -- UPDATE productinarrangement SET amount = arrayinputnumber2 WHERE productid = arrayinputnumber1 AND arrangementid = $1
END;
$BODY$ LANGUAGE plpgsql STRICT;

With help I got the function call right, which doesn't return errors anymore. I don't understand how I can loop through the array getting the values within? I've commented out the lines and I don't know what to do. I call the function within this line:

SELECT fc_update_arrangement(1::int,'tom'::text,15::money,now()::date,array[array[1,2],array[3,4]]);

Upvotes: 2

Views: 3134

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can get the 1st and 2nd elements of each row from a 2D(two dimensional) array as shown below:

CREATE FUNCTION my_func(_2D_arr INT[]) RETURNS VOID
AS $$
DECLARE
  temp INT[];
BEGIN
  FOREACH temp SLICE 1 IN ARRAY _2d_arr LOOP
    RAISE INFO '%', array_to_string(temp[1:2], ' ');
  END LOOP;
END
$$ LANGUAGE plpgsql;

*Memos:

  • The doc explains array_to_string().

Then, calling my_func() with a 2D array gets the 1st and 2nd elements of each row as shown below:

postgres=# SELECT my_func(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
INFO:  1 2
INFO:  4 5
INFO:  7 8
INFO:  10 11
 my_func
---------

(1 row)

This is the example with a DO statement:

DO $$
DECLARE
  temp VARCHAR[];
  _2d_arr VARCHAR[] := ARRAY[
    [1,2,3],
    [4,5,6],
    [7,8,9],
    [10,11,12]
  ];
BEGIN
  FOREACH temp SLICE 1 IN ARRAY _2d_arr LOOP
    RAISE INFO '%', array_to_string(temp[1:2], ' ');
  END LOOP;
END
$$;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247865

There is an example in the documentation:

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}
NOTICE:  row = {7,8,9}
NOTICE:  row = {10,11,12}

To try a verbal description: If the array of of type whatever[] and you loop with SLICE 1, the array gets cut into slices of one element each. The loop variable then will contain arrays of the same type whatever[], each containing a single element of the original array. If you choose SLICE 2, the loop variable will contain arrays of size 2.

Upvotes: 3

Related Questions