Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like:

for(int i = 0 ; i < array1.length ; i++){
  my_method(array1[i], array2[i], array3[i]);

Here is my attempt:

select dugong.session_hugeInsert( 3, '5,2,3,1', '4,3,3,2');
NOTICE:  The transaction is in an uncommitable state.Transaction was rolled back.
NOTICE:  query "SELECT Question_ID, UserResponseID_List, UserResponseList" returned 3 columns 42601
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function dugong.session_hugeinsert(integer,text,text) line 61 at SQL statement

My function:

 CREATE OR REPLACE FUNCTION Dugong.Session_hugeInsert(
    Quiz_ID_ int,       --FK
    QuestionList_ TEXT,
    UserResponseList_ TEXT
    Session_ID integer;
    QuestionList INT[];
    Question_ID integer;
    QuizQuestionisDone boolean;
    UserResponseList INT[];
    UserResponseID_List INT[];  -- Get from database
    UserResponseID integer;
    tmp int;

    IF EXISTS ( SELECT 1 FROM dugong.quiz WHERE quiz_id = Quiz_ID_ ) THEN

    QuestionList = string_to_array(QuestionList_, ',');
    UserResponseList = string_to_array(UserResponseList_, ',');
    FOREACH UserResponseID in ARRAY UserResponseList
        tmp := Dugong.UserResponse_Add();
        UserResponseID_List :=array_append(UserResponseID_List, tmp );
    END LOOP;  -- Memo: Got UserResponseID_List array

    FOREACH Question_ID, UserResponseID, tmp IN ARRAY Question_ID, UserResponseID_List, UserResponseList
        RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i] ;
    RETURN 200;

        RETURN -1;
    END IF;

My first solution does not seem very good:

i:=0; j:=0; k:=0;
FOREACH Question_ID IN ARRAY QuestionList
    j := 0;
    FOREACH UserResponseID IN ARRAY UserResponseID_List
        k := 0;
        FOREACH tmp IN ARRAY UserResponseList
        IF (i = j AND j = k) THEN
           RAISE NOTICE '%, %, %', Question_ID, UserResponseID, tmp;
        END IF;
        k := k+1;
            END LOOP;
    j := j+1;
i := i + 1;

Please let me know the proper way to go.

  • The error looks like it has nothing to do with the question above. Please show your plpgsql code. Commented Aug 18, 2015 at 14:57
  • @DanielVérité Here you are. I have to clean my code. I am preparing for worst case I will use count index i, j, and k. Loop over them and IF(i==j and j==k) my_function(.....);
    – sarit
    Commented Aug 18, 2015 at 15:29
  • Explicit ROLLBACK in a function is not allowed and is the default outcome anyway. I would remove the entire exception block. What it does now is: in case of any error, report and raise the error. But this is just what happens when there's no custom exception handling. Commented Aug 18, 2015 at 16:30
  • @DanielVérité. New knowledge! How can I use ROLLBACK properly in my PLPGSQL code? In my opinion any complete (already committed) transaction in plpgsql is unable to do ROLLBACK. Am I right?
    – sarit
    Commented Aug 18, 2015 at 16:51
  • You cannot use ROLLBACK inside plpgsql at all, like @Daniel already told you. A plpgsql block is always inside a transaction automatically. Any untrapped error will roll back everything automatically. Commented Aug 21, 2015 at 18:51

1 Answer 1


While FOREACH is very convenient to loop through a single array, it's not particularly useful to step through multiple arrays in parallel. Use a plain FOR loop with array_lower() / array_upper() instead:

FOR i IN 1 .. array_upper(UserResponseList, 1)
   RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i];

Since you created the array in the function, we know the index starts with 1 (default). Else, use:

FOR i IN array_lower(UserResponseList, 1) .. array_upper(UserResponseList, 1)

The index i is defined automatically, the documentation:

The variable name is automatically defined as type integer and exists only inside the loop.

Related cases with more information:

  • Thank you. New knowledge! 1. Index starts from 1 2. array_lower()/array_upper() are used in this way. I had read the manual in the document, but I did not understand. I though it was not related to my case. I'll be back and try your answer in my program within several hours.
    – sarit
    Commented Aug 23, 2015 at 15:45
  • 1
    @Sarit: Postgres array indexes are 1-based by default (unlike JSON arrays, for instance, which start with 0). But that's just the default. More: stackoverflow.com/q/12011569/939860 Commented Aug 23, 2015 at 15:52
  • Sorry for late response. I have clicked green check, but my reputation is not reach threshold so I can not vote yet, sorry for that. Somewhat wonder, why I can't use @ sign infront of your name in small post here?
    – sarit
    Commented Aug 25, 2015 at 4:26
  • @Sarit: Because the author of the post (that's me) gets your comment automatically. Commented Aug 25, 2015 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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