PostgreSql:如何从数组更新表？

Question

如何从数组更新表字段以避免使用临时表？用户将数组传递给重新排序表。

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$

这个例子的最终结果应该是

select * from items order by select_order;
name        select_order
------------------------
water       0
cucumber    1
coffee      2
milk        3

Answer 1

假设数组中的索引对应于 select_order 中的值，以下查询将返回新值和旧值:

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

现在这可以用来更新目标表:

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;

这还假设 select_order 以 0 开始并且没有间隙。