Re: is any reason why only one columns subselect are allowed in array()? - Mailing list pgsql-hackers

2008/11/18 Sam Mason <sam@samason.me.uk>:
> On Tue, Nov 18, 2008 at 06:55:26PM +0100, Pavel Stehule wrote:
>> 2008/11/18 Sam Mason <sam@samason.me.uk>:
>> > On Tue, Nov 18, 2008 at 05:20:27PM +0100, Pavel Stehule wrote:
>> >> 2008/11/18 Sam Mason <sam@samason.me.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>