3

How to update table fields from array to avoid temp table using? User passes array to reorder table.

create TABLE items
(
id serial primary key,
name text, 
select_order int
)

insert into items(name, select_order)
values
('cucumber',0),
('milk',1),
('coffee',2),
('water',3)

select * from items order by select_order;

DO
$body$
DECLARE var_array int[]:='{3,0,2,1}';
BEGIN
update items ??

END;
$body$

The final result in this example should be

select * from items order by select_order;
name        select_order
------------------------
water       0
cucumber    1
coffee      2
milk        3
ZedZip
  • 5,794
  • 15
  • 66
  • 119
  • Can be a little more specific? How do want this `update` to happen? I cannot figure it out from your question, `var_array` is just a list of `integer`s. – redneb Sep 06 '16 at 07:43
  • Index in the input array is a corresponding old position. As I see from answers below th "ordinality" is a right way – ZedZip Sep 06 '16 at 09:26

1 Answers1

5

Assuming the index in the array corresponds to the value in select_order the following query returns the new and old value:

select i.id, i.select_order, t.new_sort
from items i
   join lateral unnest(array[3,0,2,1]) with ordinality as t(new_sort, idx) on t.idx - 1 = i.select_order

Now this can be used to update the target table:

update items
  set select_order = r.new_sort
from (
  select i.id, i.select_order, t.new_sort
  from items i
    join lateral unnest(array[3,0,2,1]) with ordinality as t(new_sort, idx) on t.idx - 1 = i.select_order
) r
where r.id = items.id;

This also assumes that select_order starts a 0 and has no gaps.