Thread: Questions about setting an array element value outside of the update

Lets say i have subquery which produce array[], position and new_value

Is here less clumsy way to set  array[position] to the new_value (not update but just change an element inside an array) than:

SELECT
        _array[1:pos-1]
        ||newval
        ||_array[_pos+1:array_length(_array, 1)]
FROM
(
    SELECT _array,
                   pos,
                   newval
     FROM
                   some_colmplicated_logic
);

The:
        _array[1:pos-1]
        ||newval
        ||_array[_pos+1:array_length(_array, 1)]
part is very clumsy for my eyes.

PS: that is just small part of the complicated WITH RECURSIVE iterator in real task.

--
Maxim Boguk
Senior Postgresql DBA.

On Dec 4, 2011, at 22:43, Maxim Boguk <maxim.boguk@gmail.com> wrote:

> Lets say i have subquery which produce array[], position and new_value
>
> Is here less clumsy way to set  array[position] to the new_value (not update but just change an element inside an
array)than: 
>
> SELECT
>        _array[1:pos-1]
>        ||newval
>        ||_array[_pos+1:array_length(_array, 1)]
> FROM
> (
>    SELECT _array,
>                   pos,
>                   newval
>     FROM
>                   some_colmplicated_logic
> );
>
> The:
>        _array[1:pos-1]
>        ||newval
>        ||_array[_pos+1:array_length(_array, 1)]
> part is very clumsy for my eyes.
>
> PS: that is just small part of the complicated WITH RECURSIVE iterator in real task.
>
> --
> Maxim Boguk
> Senior Postgresql DBA.

My first reaction is that you should question whether you really want to deal with arrays like this in the first place.
Maybe describe what you want to accomplish and look for alternatives. 

I do not know if there is a cleaner way but regardless you should code your logic as a function.  If you devise a
betterway later then changing the algorithm will be very simple.  And it also should make you inline SQL easier to
follow.

David J.

David Johnston <polobo@yahoo.com> writes:
>> Is here less clumsy way to set  array[position] to the new_value (not update but just change an element inside an
array)than: 
>>
>> SELECT
>> _array[1:pos-1]
>> ||newval
>> ||_array[_pos+1:array_length(_array, 1)]

> I do not know if there is a cleaner way but regardless you should code
> your logic as a function.

Inside a plpgsql function, you could just do

    array[pos] := newval;

so perhaps it'd be worth creating a helper function that's a wrapper
around that.

            regards, tom lane