Postgres 9.2 PL/pgSQL simple update in loop

Question

I have a following table:

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

And I'm looking for a simple way to update position with incrementing integers ( per parent ). So after I'm done, the table should look like this:

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

What I think I need, is a function, that loops over all rows for a given trail, has a simple incrementing index and updates the position column. I am a pgSQL newbie however, so I'll be glad to hear there are simpler ways to do this.

The solution I'm trying right now looks like this

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;

I'm pretty sure it can be cleaner and it doesn't have to use functions.

I changed my original question a bit. Initially I thought the execution freezes because of some error, it wasn't the case however. I was using a GUI client, whose one of processes froze when trying to modify the `photos` table, this of course locked the table for any other process trying to update it. Everything is fine now, the solution I posted works, I'm looking for a better one tho' — ellmo, Dec 10 '12 at 16:06

score 15 · Accepted Answer · 2012-12-10T15:52:36.203

15

You don't need a stored function for that. You can do that with a single statement.

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;

edited Dec 10 '12 at 15:52

answered Dec 10 '12 at 15:33

Oh god, I'm trying to edit my initial post, so that your answer would fit ( _that's my mistake, I used different names in the question_ ), but I think you're chaning your answer too :) Also `row_number()` method blew my mind. This is perfect. – ellmo Dec 10 '12 at 15:56

Postgres 9.2 PL/pgSQL simple update in loop

1 Answers1