plpgsql: combine two results from function call

I have a function func1(ID int) that returns rows for a given ID.

I have another function func2(IDs int[]) that gets an array of IDs and uses a loop to call func1 for each ID, finally it should combine all results and return it.

both functions return SETOF y so they are compatible in their schema of columns.

What I want to do is inside the loop of func2 to merge the results. If it was in the same SQL query I would simply use UNION but the results are from a function.

CREATE OR REPLACE FUNCTION func2(IDs integer[])
  RETURNS SETOF y AS

$BODY$
declare 
    a_id int;
    r y;

begin
  FOREACH a_id IN ARRAY $1
  LOOP
    SELECT * into r FROM  func1(a_id) ; 
    //CODE TO ENTER FOR UNION EACH ITERATION WITH THE LAST ONE
  END LOOP;

return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

r should contain the result of a single call to func1. how do I combine all r and return them?

In simple words it should do: func1(1st ID) UNION func1(2nd ID) UNION ... UNION func1(nth ID) and return it to whom ever called func2(IDs int[])


Source: sql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.