Re: [GENERAL] Array assignment behavior (was Re: Stored procedure - Mailing list pgsql-admin

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@myemma.com>
software development
emma(r)