Postgres 9.2 PL/pgSQL simple update in loop

plpgsql, postgresql, postgresql-9.2, sql

Solution

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;

Problem

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.

Original source