Convert function return set into array

Question

I have a postgres user-defined function f_test with return type as RETURNS SETOF TYPE1. TYPE1 is a type with around 10 columns.

Inside postgres procedure P1, I want to store the output of f_test into an array variable.

I tried the below code to do the same inside the procedure P1.

v_array := array_agg(f_test());

But I get the below error due to this statement.

%aggregate function calls cannot contain set-returning function calls

I also tried the below code

v_array := array_agg(row(f_test()));

which returns the same error too.

How can I store the result set of f_test into array variable?

Julius Tuskenis · Accepted Answer · 2021-04-14 06:28:36Z

1

You can use SELECT INTO method:

select  
  array_agg(f_test)
into 
  v_array
from 
  f_test();

You can also use assignment operator:

v_array := (select array_agg(f_test) from f_test());

answered Apr 13, 2021 at 21:17

Julius Tuskenis

1,63010 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1