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
– ellmo, Commented Dec 10, 2012 at 16:06

score 15 · Accepted Answer · 2012-12-10 15:52:36Z

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, 2012 at 15:52

answered Dec 10, 2012 at 15:33

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

ellmo Over a year ago

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.

Collectives™ on Stack Overflow

Postgres 9.2 PL/pgSQL simple update in loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related