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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
