Ok, just so I can be sure I understand what I just read: i. says that
you can assign to an array that has not been initialized. ii. states
that the index of an insertion into an array should not be limited by
the current range of index values of the array and requires any gaps in
the index range to be set with values of null. I really don't have
anything to add to discussion other than that I agree with Tom's
assessment, but rather want clarify what might be a slightly hazy
interpretation of the specs listed below.
Tom Lane wrote:
Actually, now that I look closely, I think the SQL spec demands exactly
this. Recall that SQL99 only allows one-dimensional, lower-bound-one
arrays. The specification for UPDATE ... SET C[I] = SV ... reads
Case:
i) If the value of C is null, then an exception condition is
raised: data exception - null value in array target.
ii) Otherwise:
1) Let N be the maximum cardinality of C.
2) Let M be the cardinality of the value of C.
3) Let I be the value of the <simple value specification>
immediately contained in <update target>.
4) Let EDT be the element type of C.
5) Case:
A) If I is greater than zero and less than or equal to
M, then the value of C is replaced by an array A
with element type EDT and cardinality M derived as
follows:
I) For j varying from 1 (one) to I-1 and from I+1 to
M, the j-th element in A is the value of the j-th
element in C.
II) The I-th element of A is set to the specified
update value, denoted by SV, by applying the
General Rules of Subclause 9.2, "Store assignment",
to the I-th element of A and SV as TARGET and
VALUE, respectively.
B) If I is greater than M and less than or equal to
N, then the value of C is replaced by an array A
with element type EDT and cardinality I derived as
follows:
I) For j varying from 1 (one) to M, the j-th element
in A is the value of the j-th element in C.
II) For j varying from M+1 to I-1, the j-th element in
A is the null value.
III) The I-th element of A is set to the specified
update value, denoted by SV, by applying the
General Rules of Subclause 9.2, "Store assignment",
to the I-th element of A and SV as TARGET and
VALUE, respectively.
C) Otherwise, an exception condition is raised: data
exception - array element error.
We currently violate case i by allowing the null array value to be
replaced by a single-element array. I'm disinclined to change that,
as I think our behavior is more useful than the spec's. But case ii.5.B
pretty clearly describes null-fill, so I think we'd better do that, now
that we can.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
erik jones <erik@xxxxxxxxxx>
software development
emma(r)
