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

10

我有一个以下的表格:

+----+----------+----------+
| 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;

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


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

15

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

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;

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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,