Thread: Can I reset the lower bound of an array.

Hi,
    I've been playing about with array's, and found the concat operator
'||' quite
useful, apart from the fact that prepending an element places it in a
lower subscript.

Is there a way of either:
a) prepending an element, but shifting existing elements up a subscript,
    so that the lower bound remains the same?
    eg: 1 ||> ARRAY[2,3,4]  -- new operator ||> shift array and prepend
or
b) resetting the lower bound of the array (without affecting elements)?
    eg: set_array_lower(1 || ARRAY[2,3,4], 1) -- 2nd arg is the new
lower bound

Also, while on the topic of array's:

Is there any way to specify array slices from a subscript to the
beginning or end of an array?
eg:
    element to end:      array[5:*]
    beginning to element:   array[*:5]

At present it is possible by using an extreme +ve or -ve subscript
value, but this isn't nice.

Cheers

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Mark Gibson wrote:

> b) resetting the lower bound of the array (without affecting elements)?
>    eg: set_array_lower(1 || ARRAY[2,3,4], 1) -- 2nd arg is the new
> lower bound


After a bit more playing about, i've discovered a kludgy work-around for
resetting an array's lower bound to 1.

To demonstrate:

 > SELECT array_lower(1 || ARRAY[2,3,4], 1);
 array_lower
-------------
           0

 > SELECT array_lower((1 || ARRAY[2,3,4])[-100:100], 1);
 array_lower
-------------
           1

A slice always has a lower bound of 1, so using a slice that extends well
beyond the bounds of the array will return the array with its lower bound
set at 1.

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Mark Gibson wrote:
> Mark Gibson wrote:
>
>> b) resetting the lower bound of the array (without affecting elements)?
>>    eg: set_array_lower(1 || ARRAY[2,3,4], 1) -- 2nd arg is the new
>> lower bound

It should be pretty easy to create a C function to reset the lower bound
of an array to some arbitrary value.

Worth noting, however, is that there have been some discussions
regarding the fact that the SQL spec defines arrays as hiving a fixed
lower bound of 1, and that we might need (or at least want) to comply
with that in order to support NULL array elements. I've been hoping to
put together a coherent proposal for such changes, but have
unfortunately not been able to find the time. I'd still like to try to
get agreement and make the changes for 7.5. If anyone has strong
opinions, now would be a good time to get them on the table.

Joe