1

Using postgres's plpgsql, I'm trying to use a foreach loop to iterate over the elements of an array. I am trying to see if there's a way to get (from within the loop) the index of the current element of the array being processed by the foreach loop, but so far I haven't been able to find a way to do it in the documentation, is there any way to do this?

super potion
  • 105
  • 9
  • Whoops, I didn't mean to close this question all by myself… Do you think the duplicate target answers your question? – Bergi Apr 15 '22 at 22:07
  • In addition to the two answers below, you could also consider [a `FOR` loop and `UNNEST … WITH ORDINALTY`](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ac0c5f0813c5b775d3a3124bcf1f0a1d) – Bergi Apr 15 '22 at 22:12
  • Well it does in that it explains there's no ready to use way to do it, although the answers there address older versions of postgres, but from the answers I got here, it seems that hasn't changed so far in the current version (14). I tried looking around but that duplicate target didn't pop up with the terms I used, sorry – super potion Apr 15 '22 at 22:54
  • 1
    It took some special google-foo :-) I was trying to search for a related thing in the process of writing an answer with the approach from my second comment, and then it popped up. Another quite useful article that came up in the search is https://docs.yugabyte.com/preview/api/ysql/datatypes/type_array/looping-through-arrays/, although it doesn't answer your question either – Bergi Apr 15 '22 at 22:59
  • Oh thanks, I skimmed over it and it seems useful indeed, I will give it a read now – super potion Apr 15 '22 at 23:11
  • 1
    You ask for `the index of the current element of the array being processed by the foreach loop`. None of the current answers does that, exactly. Postgres arrays allow multiple dimensions with arbitrary array subscripts. See: https://stackoverflow.com/q/12011569/939860 `generate_subscripts()` produces *actual* indexes, while counting iterations produces a loop counter. Also `FOREACH` flattens multiple dimensions. Subtle differences. – Erwin Brandstetter Apr 25 '22 at 22:21

2 Answers2

3

There is no ready-made tool for this, but you can use an auxiliary variable, e.g.:

do $$
declare 
    a text;
    i int = 0;
begin
    foreach a in array array['a','b','c'] loop
        i:= i+ 1;
        raise notice '% %', i, a;
    end loop;
end $$
klin
  • 112,967
  • 15
  • 204
  • 232
2

Why not just use a for loop over an integer:

do 
$$
declare 
  input text[] := array['a','b','c'];
begin
  for i in 1..cardinality(input) loop
    raise notice 'Index: %, value: %', i, input[i];
  end loop;
end $$
;
  • 2
    I know, but I was trying to see if there was any way prebuilt way to do it with the foearch loop, I use that in javascript sometimes and wanted to see if there was something similar here, still testing the waters – super potion Apr 15 '22 at 22:48