Postgres 9.2中使用PL/pgSQL进行简单循环更新

Question

我有一个以下的表格：

+----+----------+----------+
| id | trail_id | position |
+----+----------+----------+
| 11 | 16       | NULL     |
| 12 | 121      | NULL     |
| 15 | 121      | NULL     |
| 19 | 42       | NULL     |
| 20 | 42       | NULL     |
| 21 | 42       | NULL     |
+----+----------+----------+

我正在寻找一种简单的方法来使用递增整数（每个父级）更新position。因此在操作完成后，表格应该像这样:

+----+-----------+----------+
| id | trail_id  | position |
+----+-----------+----------+
| 11 | 16        | 1        |
| 12 | 121       | 1        |
| 15 | 121       | 2        |
| 19 | 42        | 1        |
| 20 | 42        | 2        |
| 21 | 42        | 3        |
+----+-----------+----------+

我认为我需要的是一个函数，循环遍历给定的trail所有行，有一个简单的递增索引并更新position列。不过我是pgSQL新手，如果有更简便的方法，我会很高兴听到。

我现在正在尝试的解决方案如下：

CREATE FUNCTION fill_positions(tid integer) RETURNS integer AS $$
DECLARE
    pht RECORD;
    i INTEGER := 0;
BEGIN
    FOR pht IN SELECT * FROM photos WHERE photos.trail_id = tid LOOP
        i := i + 1;
        UPDATE photos SET position = i WHERE id = pht.id;
    END LOOP;
    RETURN i;
END;
$$ LANGUAGE plpgsql;

我相信它可以更简洁，而且不必使用函数。

- ellmo

---

我稍微修改了我的原始问题。最初我认为执行会因为某些错误而冻结，但事实并非如此。我使用的是GUI客户端，其中一个进程在尝试修改“photos”表时被冻结，这当然会锁定该表，使得任何其他尝试更新它的进程都无法进行。现在一切都好了，我发布的解决方案可行，但我正在寻找更好的解决方案。 - ellmo

Answer 1

你不需要为此编写存储函数，你可以用单个语句完成。

with new_numbers as (
   select id,  
          trail_id,
          row_number() over (partition by trail_id order by id) as position
   from photos
)
update photos
  set position = nn.position
from new_numbers nn
where nn.id = photos.id;

- user330315

---

哦天啊，我正在尝试编辑我的初始帖子，以便您的答案适合（_这是我的错误，我在问题中使用了不同的名称_），但我认为您也在更改您的答案 :)此外，row_number()方法让我大吃一惊。这太完美了。 - ellmo