Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6578B9FB292; Fri, 29 Sep 2006 15:52:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 96129-07; Fri, 29 Sep 2006 15:52:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from myemma.com (db1.e2ma.net [66.179.147.163]) by postgresql.org (Postfix) with ESMTP id 878699FB222; Fri, 29 Sep 2006 15:52:24 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by myemma.com (Postfix) with ESMTP id 5249823CE4; Fri, 29 Sep 2006 13:52:23 -0500 (CDT) Received: from myemma.com ([127.0.0.1]) by localhost (velma [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26829-06; Fri, 29 Sep 2006 13:52:22 -0500 (CDT) Received: from [10.0.23.48] (unknown [205.241.239.26]) by myemma.com (Postfix) with ESMTP id 5055323CDF; Fri, 29 Sep 2006 13:52:16 -0500 (CDT) Message-ID: <451D6B4F.8000104@myemma.com> Date: Fri, 29 Sep 2006 13:51:59 -0500 From: Erik Jones User-Agent: Thunderbird 1.5.0.7 (Windows/20060909) MIME-Version: 1.0 To: Tom Lane Cc: Postgres General , PostgreSQL-development , pgsql-admin@postgresql.org Subject: Re: [GENERAL] Array assignment behavior (was Re: Stored procedure References: <451C48AB.6090508@pnlassociates.com> <9461.1159502232@sss.pgh.pa.us> <451D34FD.3050709@pnlassociates.com> <451D37C8.8060502@pnlassociates.com> <16926.1159546451@sss.pgh.pa.us> <595CA7DC-7EC2-47B6-A894-DCD0ED3825F3@pandora.com> <18110.1159552767@sss.pgh.pa.us> In-Reply-To: <18110.1159552767@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at myemma.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.188 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/379 X-Sequence-Number: 23293 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 > immediately contained in . > > 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 software development emma(r)