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

- ZedZip

---

可以再具体一些吗？您希望如何进行此“更新”？从您的问题中我无法弄清楚，var_array只是一个整数列表。 - redneb

输入数组中的索引是相应的旧位置。从下面的答案中我看到“序数”是正确的方式。 - ZedZip

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开始，且没有间隙。

- user330315