Re: is any reason why only one columns subselect are allowed in array()?

2008/11/18 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Tue, Nov 18, 2008 at 05:19:12PM +0100, Pavel Stehule wrote:
>> there are some not necessary limits, because we should some operations:
>>
>> postgres=# select array(select * from foo);
>> ERROR: subquery must return only one column
>> LINE 1: select array(select * from foo);
>
> The current limitation that a sub-query can only return a single column
> is very annoying and is one of the more common restrictions I bump into
> when writing SQL.
>

yes, and typical limitation is return only one row too. So these
typical rules are broken yet. I did some fast hacking and after change
about no more than 10 rows I am able remove this limit. It is similar
aggregates that were one argument too, and we have multi parametric
aggregates now.

postgres=# select array(select a from test);
?column?
----------
{1,2,3}
(1 row)

postgres=# select array(select a,b from test);
?column?
---------------------
{{1,2},{2,3},{3,4}}
(1 row)

postgres=# select array(select a,b,c from test);
?column?
---------------------------
{{1,2,3},{2,3,4},{3,4,5}}
(1 row)

postgres=# select array(select (a,b,c) from test);
?column?
---------------------------------
{"(1,2,3)","(2,3,4)","(3,4,5)"}
(1 row)

postgres=# select * from test;
a | b | c
---+---+---
1 | 2 | 3
2 | 3 | 4
3 | 4 | 5
(3 rows)

It's need some parser lines more (finding most common type), but it is
full compatible - and usefull for multicolumn time series.

regards
Pavel

>
> Sam
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>