Re: Set Returning Functions and array_agg()

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(at)gmail(dot)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(at)depesz(dot)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
>>
>>
>