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

2008/11/18 Sam Mason <sam@samason.me.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 | 32 | 3 | 43 | 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>