CODEWITHSUNDEEP
postgresql
Mano
Mano

Reputation: 711

Convert function return set into array

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?

Upvotes: 0

Views: 631

Answers (1)

Julius Tuskenis
Julius Tuskenis

Reputation: 1610

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());

Upvotes: 1

Related Questions