Обсуждение: Set Returning Functions and array_agg()
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.
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
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:Is there any reason why you're not using normal syntax:
> 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
select array_agg(col1) from my_setof_record_returning_func();
?
Best regards,
depesz
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--------123(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 setThe 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:Is there any reason why you're not using normal syntax:
> 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
select array_agg(col1) from my_setof_record_returning_func();
?
Best regards,
depesz
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
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