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

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]); $$
LANGUAGESQL   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?

> 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.

> 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