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

On Tue, Nov 18, 2008 at 07:32:33PM +0100, Pavel Stehule wrote:
> 2008/11/18 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> > 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 ...

Sorry, PG 8.3 doesn't seem to have anything that responds to that.

> select array(select a from ...

I just get an error of "could not find array type for datatype
integer[]" when I try to do that, hence why I wrote the above code.

> > On Tue, Nov 18, 2008 at 06:55:26PM +0100, Pavel Stehule wrote:
> >> 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.

We're talking completely cross purposes here and both saying the same
things. To be formal, lets start with a hopefully redundant set of
definitions: A list/array is a structure for holding a dynamically
varying number of elements, there are methods of dynamically iterating
through its elements. A tuple/record holds a static set of elements and
you can not iterate its elements dynamically---if there was some form of
introspection available in PG this would of course be false.

What you're after is an array; I'm just saying that if you add another
step in before you actually receive that array the language will be
strictly more powerful and general. This step can of course be hidden
behind some utility function; but, IMHO, "array" is a very important
name and the general function should probably behind this name by
default and not the utility function.

Sam