Re: Missing array support

Peter Eisentraut wrote:
> Some nice advances to SQL standard array support were made, but there are
> a few things that don't work yet in the sense of feature S091 "Basic array
> support".  Joe, do you want to take on some of these?  They should be
> pretty easy (for you).
> 
> * Declaration of multidimensional arrays (see clause 6.1):
> 
> create table test2 (a int, b text array[5] array[6]);
> ERROR:  syntax error at or near "array" at character 44

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?


> * Empty arrays (see clause 6.4):
> 
> insert into test values (1, array[]);
> ERROR:  syntax error at or near "]" at character 35

I saw this, but interpreted it as a data type specification, not an 
expression. Here's what SQL200x says:

<empty specification> ::=  ARRAY <left bracket or trigraph> <right bracket or trigraph>

Syntax Rules
1) The declared type DT of an <empty specification> ES is ET ARRAY[0], 
where the element type ET is determined by the context in which ES 
appears. ES is effectively replaced by CAST ( ES AS DT ).
NOTE 69 – In every such context, ES is uniquely associated with some 
expression or site of declared type DT, which thereby becomes the 
declared type of ES.

So array[] should produce '{}' of (an array) type determined by the 
context? OK -- seems easy enough.


> * Cardinality function (returns array dimensions, see clause 6.17).

<cardinality expression> ::=
CARDINALITY <left paren> <collection value expression> <right paren>

6) If <cardinality expression> is specified, then the declared type of 
the result is exact numeric with implementation-defined precision and 
scale 0 (zero).

8) The result of <cardinality expression> is the number of elements of 
the result of the <collection value expression>.

Seems easy.

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

Interesting. I already wrote (essentially) this function, but it was 
rejected months ago when we were discussing its limitations. I didn't 
realize there was a spec compliant way to do it:


<table reference> ::= <table primary>
<table primary> ::= <collection derived table> [ AS ] <correlation name>                    [ <left paren> <derived
columnlist> <right paren> ]
 

<collection derived table> ::=
UNNEST <left paren> <collection value expression> <right paren>
[ WITH ORDINALITY ]

1) If a <table reference> TR specifies a <collection derived table> CDT, 
then let C be the <collection value expression> immediately contained in 
CDT, let CN be the <correlation name> immediately contained in TR, and 
let TEMP be an <identifier> that is not equivalent to CN nor to any 
other <identifier> contained in TR.  a) Case:    i) If TR specifies a <derived column list> DCL, then       Case:
1)If CDT specifies WITH ORDINALITY, then DCL shall contain 2          <column name>s. Let N1 and N2 be respectively the
firstand          second of those <column name>s.       2) Otherwise, DCL shall contain 1 (one) <column name>; let N1
be         that <column name>. Let N2 be a <column name> that is not          equivalent to N1, CN, TEMP, or any other
<identifier>         contained in TR.    ii) Otherwise, let N1 and N2 be two <column name>s that are not
equivalentto one another nor to CN, TEMP, or any other        <identifier> contained in TR.
 
  b) Let RECQP be:     WITH RECURSIVE TEMP(N1, N2) AS ( SELECT C[1] AS N1, 1 AS N2     FROM (VALUES(1)) AS CN WHERE 0 <
CARDINALITY(C)    UNION     SELECT C[N2+1] AS N1, N2+1 AS N2 FROM TEMP     WHERE N2 < CARDINALITY(C))
 
  c) Case:     i) If TR specifies a <derived column list> DCL, then let PDCLP be        ( DCL )     ii) Otherwise, let
PDCLPbe a zero-length string.
 
  d) Case:     i) If CDT specifies WITH ORDINALITY, then let ELDT be:        LATERAL ( RECQP SELECT * FROM TEMP AS CN
PDCLP)     ii) Otherwise, let ELDT be:        LATERAL ( RECQP SELECT N1 FROM TEMP AS CN PDCLP )  e) CDT is equivalent
tothe <lateral derived table> ELDT.
 

14) A <collection derived table> is not updatable.


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

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


Does this look correct? Again, shouldn't be too hard as most of the work 
is already done. I'd just need to do some grammar modifications.


> * Some information schema work (doing that now...)
> 
So I take it I need not worry about that?


None of this is very difficult. I'll try to fit it in between now and 
Monday evening, but if not it's very doable for 7.5.

Joe