Re: Set Returning Functions and array_agg()

On Wed, Apr 24, 2013 at 4: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?

you can do it like this though:

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

merlin