Обсуждение: 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.
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