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 column list> <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 first and
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
equivalent to 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 PDCLP be 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 to the <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