Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

I am writing a plpgsql script.
I want to get the array contents one by one from a two dimension array.

DECLARE
  m varchar[];
  arr varchar[][] := array[['key1','val1'],['key2','val2']];
BEGIN
  for m in select arr
  LOOP
    raise NOTICE '%',m;
  END LOOP;
END;

But the above code returns

{{key1,val1},{key2,val2}}

in one line. I want to be able to loop over and call another function which takes parameters like:

another_func(key1,val1)
share|improve this question
What's your version of PostgreSQL? – Erwin Brandstetter Mar 20 '12 at 10:23
it is version 9.1 – Harpreet Mar 28 '12 at 16:10

1 Answer

up vote 7 down vote accepted

Since PostgreSQL 9.1 there is the very convenient (and fast) FOREACH you can:

DO
$BODY$
DECLARE
    m   varchar[];
    arr varchar[] := array[['key1','val1'],['key2','val2']];
BEGIN
    FOREACH m SLICE 1 IN ARRAY arr
    LOOP
        RAISE NOTICE 'another_func(%,%)',m[1], m[2];
    END LOOP;
END;
$BODY$ language plpgsql

I saw in your comment that got deleted, that you already found a solution for older versions:

DO
$BODY$
DECLARE
    arr varchar[] := '{{key1,val1},{key2,val2}}';
BEGIN
    FOR i IN array_lower(arr, 1) .. array_upper(arr, 1)
    LOOP
        RAISE NOTICE 'another_func(%,%)',arr[i][2], arr[i][2];
    END LOOP;
END;
$BODY$ language plpgsql

Also, there is no difference between varchar[] and varchar[][] for the PostgreSQL type system. I explain in more detail here.

The DO statement requires at least PostgreSQL 9.0, btw.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.