How to loop over a 2d array in PostgreSQL?

Question

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]]);

@VaoTsun im really sorry, i noticed an error in calling the function(i asked for money but was sending an int) and was afraid I would get roasted for that — JohnDorian
– JohnDorian, Commented Dec 6, 2017 at 10:45

Laurenz Albe · Accepted Answer · 2020-05-25 06:22:37Z

3

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.

edited May 25, 2020 at 6:22

answered Dec 6, 2017 at 10:49

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

JohnDorian Over a year ago

This works and gives me exactly the output you listed below the call of the scan_rows function. However I want to get the single values out. So: row = {1,2,3} should return me: 1 2 3 as single numbers, not as a combination

Laurenz Albe Over a year ago

Stackoverflow is not a code writing service. Looping through the values in x is straightforward and left as an exercise to the reader.

ebk Over a year ago

@LaurenzAlbe It works, but could you please tell me what the value of SLICE means here? I have read the manual and have tried with some code but still haven't figured it out.

Laurenz Albe Over a year ago

This is explained here. "With a positive SLICE value, FOREACH iterates through slices of the array rather than single elements." So SLICE 1 means that each iteration of the loop will get a one-element array for x.

ebk Over a year ago

@LaurenzAlbe Honestly I still don't get it. Maybe I have a wrong understanding of what's a SLICE in Postgres. I will try to figure it out. Thank you all the same.

|

blackgreen · Accepted Answer · 2024-02-19 11:17:53Z

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
$$;

Collectives™ on Stack Overflow

How to loop over a 2d array in PostgreSQL?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related