Looping with array of values in PL/pgSQL

Question

Can we use RETURN QUERY within this loop to display all values from UserResponseList instead of RAISE NOTICE?

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

Answer 1

can we use return query within this loop ...?

You can use RETURN QUERY:

CREATE OR REPLACE FUNCTION f_pivot_arrays()
  RETURNS TABLE(question text, response_id int, response text) AS
$func$
DECLARE
   QuestionList         text[] := '{Who?,When?,WHY?}';
   UserResponseID_List  int[]  := '{1,2,3}';
   UserResponseList     text[] := '{Here.,Now.,Because.}';
BEGIN
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      RETURN QUERY SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i];
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

Or RETURN NEXT:

...
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i]
      INTO   question,        response_id,            response;
      RETURN NEXT;
   END LOOP;
...

But since Postgres 9.4, the simpler tool to unnest arrays in parallel is unnest():

   RETURN QUERY
   SELECT *
   FROM   unnest(QuestionList, UserResponseID_List, UserResponseList);

dbfiddle here

Related:

• How do you declare a set-returning-function to only be allowed in the FROM clause?

Answer 2

Are you looking for something like

select 
UserResponseList.UserResponseList , UserResponseID_List.value ,QuestionList.value

from
generate_series(1, array_upper(ARRAY[1,8,3,7,22,31], 1) ,1) UserResponseList 

left join
(   
    select generate_subscripts(a.arr,1) as subscript, a.arr[generate_subscripts(a.arr,1)] as value
    from (select ARRAY[25,8,3,7,29] as arr) a
)
UserResponseID_List on UserResponseList .UserResponseList  = UserResponseID_List .subscript 



left join
(   
    select generate_subscripts(a.arr,1) as subscript, a.arr[generate_subscripts(a.arr,1)] as value
    from (select ARRAY[1,3,16] as arr) a
)
QuestionList on UserResponseList .UserResponseList  = QuestionList .subscript

Answer 3

Yes, you can what you want is parallel array unnest.

SELECT row_number() OVER (), *
FROM table
CROSS JOIN LATERAL unnest( QuestionList, UserResponseID_List, UserResponseList );

I'm not sure what this has to do with RAISE NOTICE, but I imagine you can figure it out from there.