Questions about setting an array element value outside of the update

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 better way 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