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