Postgres Pro
Downloads
Home   >   mailing lists

Thread: Set Returning Functions and array_agg()

Set Returning Functions and array_agg()

From
Stephen Scheck
Date:
Hi,

I have a UDF (written in C) that returns SETOF RECORD of an anonymous record type
(defined via OUT parameters). I'm trying to use array_agg() to transform its output to
an array:

pg_dev=# SELECT array_agg((my_setof_record_returning_func()).col1);
ERROR:  set-valued function called in context that cannot accept a set

Or the alternate syntax:

pg_dev=# SELECT array_agg(col1(my_setof_record_returning_func()));
ERROR:  set-valued function called in context that cannot accept a set

Can somebody explain why I get the error message? Presumably the parser is deciding that
the expression provided to array_agg() is a set, based on my_setof_record_returning_func()'s
definition. But shouldn't the column selection (or equivalent column-as-func-call notation) supply
the expected context to array_agg()?

Thanks.

Re: Set Returning Functions and array_agg()

From
hubert depesz lubaczewski
Date:
On Wed, Apr 24, 2013 at 12:48:44PM -0700, Stephen Scheck wrote:
> I have a UDF (written in C) that returns SETOF RECORD of an anonymous
> record type
> (defined via OUT parameters). I'm trying to use array_agg() to transform
> its output to
> an array:
> pg_dev=# SELECT array_agg((my_setof_record_returning_func()).col1);
> ERROR:  set-valued function called in context that cannot accept a set

Is there any reason why you're not using normal syntax:
select array_agg(col1) from my_setof_record_returning_func();
?

Best regards,

depesz

Re: Set Returning Functions and array_agg()

From
Stephen Scheck
Date:
Possibly due to my lack of thorough SQL understanding. Perhaps there's a better way of doing what I'm ultimately trying to accomplish, but still the question remains - why does this work:

pg_dev=# select unnest(array[1,2,3]);
 unnest 
--------
      1
      2
      3
(3 rows)

But not this:

pg_dev=# select array_agg(unnest(array[1,2,3]));
ERROR:  set-valued function called in context that cannot accept a set

The solution to the problem is actually of less interest right now then in understanding what's going on in the two statements above. It seems a bit inconsistent to me. If an aggregate function cannot handle rows generated in the columns-part of the statement, then why is a single-column row(s) result acceptable in the first statement?



On Wed, Apr 24, 2013 at 1:29 PM, hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Wed, Apr 24, 2013 at 12:48:44PM -0700, Stephen Scheck wrote:
> I have a UDF (written in C) that returns SETOF RECORD of an anonymous
> record type
> (defined via OUT parameters). I'm trying to use array_agg() to transform
> its output to
> an array:
> pg_dev=# SELECT array_agg((my_setof_record_returning_func()).col1);
> ERROR:  set-valued function called in context that cannot accept a set

Is there any reason why you're not using normal syntax:
select array_agg(col1) from my_setof_record_returning_func();
?

Best regards,

depesz


Re: Set Returning Functions and array_agg()

From
Stephen Scheck
Date:
I'm guessing the reason is something like this: even though the "things" returned by these two statements are the same logical entity (from a mathematics/set theory standpoint):

pg_dev=# select * from unnest(array[1,2,3]);
 unnest 
--------
      1
      2
      3
(3 rows)

pg_dev=# select unnest(array[1,2,3]);
 unnest 
--------
      1
      2
      3
(3 rows)

The processing code-path for an aggregate function gets fed row-by-row and is not just handed a complete set to work on. That would explain why set-returning functions are allowed in the columns-clause (no general prohibition on that) but not passable to aggregate functions.

But then, shouldn't it be possible to write something like array_agg that takes a set as input and returns an array, that is not an aggregate function, and is callable from the columns-clause?



On Wed, Apr 24, 2013 at 2:26 PM, Stephen Scheck <singularsyntax@gmail.com> wrote:
Possibly due to my lack of thorough SQL understanding. Perhaps there's a better way of doing what I'm ultimately trying to accomplish, but still the question remains - why does this work:

pg_dev=# select unnest(array[1,2,3]);
 unnest 
--------
      1
      2
      3
(3 rows)

But not this:

pg_dev=# select array_agg(unnest(array[1,2,3]));
ERROR:  set-valued function called in context that cannot accept a set

The solution to the problem is actually of less interest right now then in understanding what's going on in the two statements above. It seems a bit inconsistent to me. If an aggregate function cannot handle rows generated in the columns-part of the statement, then why is a single-column row(s) result acceptable in the first statement?



On Wed, Apr 24, 2013 at 1:29 PM, hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Wed, Apr 24, 2013 at 12:48:44PM -0700, Stephen Scheck wrote:
> I have a UDF (written in C) that returns SETOF RECORD of an anonymous
> record type
> (defined via OUT parameters). I'm trying to use array_agg() to transform
> its output to
> an array:
> pg_dev=# SELECT array_agg((my_setof_record_returning_func()).col1);
> ERROR:  set-valued function called in context that cannot accept a set

Is there any reason why you're not using normal syntax:
select array_agg(col1) from my_setof_record_returning_func();
?

Best regards,

depesz



Re: Set Returning Functions and array_agg()

From
Jasen Betts
Date:
On 2013-04-24, Stephen Scheck <singularsyntax@gmail.com> wrote:
> --f46d043c810aa794a404db21f464
> Content-Type: text/plain; charset=ISO-8859-1
>
> Possibly due to my lack of thorough SQL understanding. Perhaps there's a
> better way of doing what I'm ultimately trying to accomplish, but still the
> question remains - why does this work:
>
> pg_dev=# select unnest(array[1,2,3]);
>  unnest
> --------
>       1
>       2
>       3
> (3 rows)
>
> But not this:
>
> pg_dev=# select array_agg(unnest(array[1,2,3]));
> ERROR:  set-valued function called in context that cannot accept a set

the parser doesn't understand it for the reason given

same as it doesn't understand this.

 select avg(generate_series(1,3));

but it does understand this:

 select avg(a) from  generate_series(1,3) as s(a);

and this:

 select array_agg(i) from unnest(array[1,2,3])) as u(i);




--
⚂⚃ 100% natural

Re: Set Returning Functions and array_agg()

From
Merlin Moncure
Date:
On Wed, Apr 24, 2013 at 4:26 PM, Stephen Scheck
<singularsyntax@gmail.com> wrote:
> Possibly due to my lack of thorough SQL understanding. Perhaps there's a
> better way of doing what I'm ultimately trying to accomplish, but still the
> question remains - why does this work:
>
> pg_dev=# select unnest(array[1,2,3]);
>  unnest
> --------
>       1
>       2
>       3
> (3 rows)
>
> But not this:
>
> pg_dev=# select array_agg(unnest(array[1,2,3]));
> ERROR:  set-valued function called in context that cannot accept a set
>
> The solution to the problem is actually of less interest right now then in
> understanding what's going on in the two statements above. It seems a bit
> inconsistent to me. If an aggregate function cannot handle rows generated in
> the columns-part of the statement, then why is a single-column row(s) result
> acceptable in the first statement?

you can do it like this though:

select array(select unnest(array[1,2,3]));

merlin