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 06:55:26PM +0100, Pavel Stehule wrote:
>> 2008/11/18 Sam Mason <sam(at)samason(dot)me(dot)uk>:
>> > On Tue, Nov 18, 2008 at 05:20:27PM +0100, Pavel Stehule wrote:
>> >> 2008/11/18 Sam Mason <sam(at)samason(dot)me(dot)uk>:
>> >> > I've used this syntax before and got a surprising message back. I'd
>> >> > expect to be able to do the following:
>> >> >
>> >> > ARRAY((SELECT col1, col2 FROM (VALUES ('a',1), ('b',2)) x(col1,col2)));
>> >> >
>> >> > and get the following back {"(a,1)","(b,2)"}. So I think I'm with
>> >> > David.
>> >>
>> >> this is different result - it's array of records, not 2d array.
>> >
>> > Yes, but an array of records is much more natural. There are only a
>> > few specific cases when what you want to do would be useful. It also
>> > naturally follows on from the current semantics:
>> >
>> > ARRAY(VALUES (1),(2));
>> >
>> > returns a 1d array of integers and not a 2d array of unit width---you
>> > don't get this back:
>> >
>> > ARRAY[ARRAY[1],ARRAY[2]]
>> >
>> > But I can't see any reason for changing the semantics between when you
>> > return a single column vs. many. In fact it may confuse calling code
>> > even more
>>
>> There are simple reason - I am not able to iterate over record in
>> plpgsql, and I should to do it over 2d array. I am sorry, but I don't
>> see any real reason for this limit - when I use array constructor and
>> input is one column, then result is one dimensional array (and it's
>> not important if it is array of scalar or array of record), when input
>> is tuple - vector, then natural result is array of array, that is 2d
>> array in pg.
>
> I really think you're solving this the wrong way around! Overloading a
> general array accumulation function with extra semantics seems strange.
> I've always been taught to design things so that the that the basic
> semantics should be as simple as possible which maintaining useful
> performance.
>
> I don't have your code that allows queries to return more than one row,
> but this is what I think you want to do in the context of aggregates:
>
> CREATE FUNCTION array_concat_(ANYARRAY,ANYARRAY) RETURNS ANYARRAY
> AS $$ SELECT array_cat($1,ARRAY[$2]); $$
> LANGUAGE SQL
> IMMUTABLE;
>
> CREATE AGGREGATE array_concat (ANYARRAY) (
> sfunc = array_concat_,
> stype = ANYARRAY,
> initcond = '{}'
> );
>
> A demo query being:
>
> SELECT array_concat(a) FROM (VALUES
> (ARRAY[1,2,3]),
> (ARRAY[5,6,7]),
> (ARRAY[7,8,9])) x(a);
>
> is that somewhat correct?
>
yes, it's should be - it's one way

actually there is similar way

select array_agg(a) from ...
select array(select a from ...

>> 2d arrays are much general than records and it able to store multi
>> time series, that is important.
>
> From a type-theoretic viewpoint "general" is not a useful description
> of the difference between tuples and lists, they both have *different*
> semantics and the situation you use them in determines which is more
> useful.

try to iterate over record in plpgsql or sql functions.

regards
Pavel Stehule

>
>> p.s. my first qustions was about real limits inside pg, and there are
>> not any limit.
>
> If subqueries can return more than one row, this can be done as you
> want---I think so anyway!
>
>
> 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
>