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

2d arrays are much general than records and it able to store multi
time series, that is important.

regards
Pavel Stehule

p.s. my first qustions was about real limits inside pg, and there are
not any limit.

>
> Records have a predefined and static (over the duration of the query)
> structure so it's easy to make an equivalence between single element
> records and the element itself (several very rigorously specified
> languages do this very successfully).  It's somewhat annoying that PG
> only does this sometimes:
>
>  SELECT x, n FROM now() x(n);
>
> Causes the record "x" to be of type timestamp (i.e. the same as "n") and
> not a record containing a timestamp.  Whereas:
>
>  SELECT x, n FROM (VALUES (1)) x(n);
>
> Causes the record "x" to remain as a record containing an integer and
> "n" to refer to the same integer.  All good fun, but not very relevant!
>
> The length of an array is specifically unknown, so assuming any
> equivalence between arrays and single elements of defined type is
> difficult at best.  In your example, everything works out because
> you're doing a transition from a tuple (record) to a vector (1d array)
> to a matrix (2d array), where the length of the vector is constant (as
> predicated on the source being a record) and hence width of the matrix
> is constant.  But I can't see why the user would always want to put this
> middle step in.
>
> Does that make any sense?
>
>
>  Sam
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>