1

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;
  • 1
    The question seems clear enough to me. Neither of the 5 users who closed this question have activity in the [plpgsql] tag worth mentioning. – Erwin Brandstetter Apr 19 '18 at 0:16
3

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:

  • Great answer, Your answer is very helpful to me and also it is helpful for understand the array concepts within looping statements in pgsql. – M.Karthika Nov 27 '17 at 9:41
0

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
  • No, My question is can we use return query instead of raise notice within the looping statements to getting an output of all data from the particular array. – M.Karthika Nov 27 '17 at 9:47
0

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.

  • No, My question is can we use return query instead of raise notice within the looping statements to getting an output of all data from the particular array. – M.Karthika Nov 27 '17 at 9:49

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.