Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Array assignment behavior (was Re: Stored procedure

From: Erik Jones <erik(at)myemma(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Postgres General <pgsql-general(at)postgresql(dot)org>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>,pgsql-admin(at)postgresql(dot)org
Subject: Re: [GENERAL] Array assignment behavior (was Re: Stored procedure
Date: 2006-09-29 18:51:59
Message-ID: 451D6B4F.8000104@myemma.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-generalpgsql-hackers
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(at)myemma(dot)com>
software development
emma(r)


In response to

pgsql-hackers by date

Next:From: Andrew SullivanDate: 2006-09-29 19:02:12
Subject: Replication hooks discussion
Previous:From: Tom LaneDate: 2006-09-29 17:59:27
Subject: Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits)

pgsql-admin by date

Next:From: Bruno Wolff IIIDate: 2006-09-29 21:03:32
Subject: Re: How can I restore from WAL log? [PG 7.3]
Previous:From: Sriram DandapaniDate: 2006-09-29 18:31:11
Subject: Re: [JDBC] number of transactions doubling

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2006-09-29 21:04:51
Subject: Re: pg web hosting with tsearch2?
Previous:From: Brandon AikenDate: 2006-09-29 18:44:59
Subject: Re: [NOVICE] Do non-sequential primary keys slow performance significantly??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group