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

On Dec 4, 2011, at 22:43, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)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 better way later then changing the algorithm will be very simple. And it also should make you inline SQL easier to follow.

David J.