Re: Missing array support

Joe Conway writes:

> I don't see anything about multidimensional arrays at all. I take it
> this is SQL99 (ISO/IEC 9075-2:1999 (E))? Can you point to a more
> specific paragraph?

It doesn't say anything specifically about multidimensional arrays, but
the grammar clearly allows declaring arrays of arrays.

<data type> ::=
<predefined type>
| <row type>
| <user-defined type>
| <reference type>
| <collection type>

<collection type> ::=
<data type> <array specification>

<array specification> ::=
<collection type constructor>
<left bracket or trigraph> <unsigned integer> <right bracket or trigraph>

<collection type constructor> ::=
ARRAY

This also has some consequences for the cardinality function. In order to
get the cardinality of the second dimension, you'd need to call
cardinality(a[1]). (I suppose it allows different cardinalities at
various positions, so the array does not need to be an n-dimensional
rectangle.)

> > * Using an array as a table source using UNNEST, something like:
> >
> > select * from unnest(test.b);
> > (Check the exact spec to be sure; clause 7.6.)

> Whew! Anyone care to help me interpret that! At it's most basic level, I
> think these are valid:
>
> select * from unnest(array['a','b']);
> ?column?
> ----------
> a
> b
>
> select * from unnest(array['a','b']) WITH ORDINALITY;
> ?column? | ?column?
> ----------+----------
> 1 | a
> 2 | b

Yes.

> select * from unnest(array['a','b']) as t(f1, f2) WITH ORDINALITY;
> f1 | f2
> ----+----
> 1 | a
> 2 | b

The WITH ORDINALITY goes before the AS clause.

The reason it is defined in terms of the LATERAL clause is that that
allows you to refer to column aliases defined in FROM items to its left.
This is the way variable arguments of function calls as table sources can
be resolved. (At least this is my interpretation. I found some examples
on the web a few months ago about this.)

--
Peter Eisentraut peter_e(at)gmx(dot)net