4

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
)
RETURNS int AS $$
DECLARE
    Session_ID integer;
    QuestionList INT[];
    Question_ID integer;
    QuizQuestionisDone boolean;
    UserResponseList INT[];
    UserResponseID_List INT[];  -- Get from database
    UserResponseID integer;
    tmp int;

BEGIN
    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
    LOOP
        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
    LOOP
        RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i] ;
    END LOOP;
    RETURN 200;

    ELSE
        RETURN -1;
    END IF;
END;
$$ LANGUAGE PLPGSQL;

My first solution does not seem very good:

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

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. – Daniel Vérité Aug 18 '15 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 Aug 18 '15 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. – Daniel Vérité Aug 18 '15 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 Aug 18 '15 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. – Erwin Brandstetter Aug 21 '15 at 18:51
3

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)
LOOP
   RAISE NOTICE '%, %, %', QuestionList[i], UserResponseID_List[i], UserResponseList[i];
END LOOP;

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 Aug 23 '15 at 15:45
  • @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 – Erwin Brandstetter Aug 23 '15 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 Aug 25 '15 at 4:26
  • @Sarit: Because the author of the post (that's me) gets your comment automatically. – Erwin Brandstetter Aug 25 '15 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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