Bryan Wilkerson wrote:
My first question concerns self ordering lists. Basically, a numeric
column that automatically maintains an order but allows arbitrary
reordering by the user. For example, a media playlist or the
priority of workitems within a parent container ;) This seems
like a common pattern.
priority | workitem
---------+-----------
1 | task 1
2 | task 2
3 | task 3
4 | task 4
5 | task 5
Insert a new task with priority==2 and...
update tablename set priority=priority+1 where priority >= 2
delete task with priority==2 and...
update tablename set priority=priority-1 where priority > 2
reorder task with priority==2, set its priority=4
update tablename set priority=priority+1 where priority >= 4
> update tablename set priority=priority-1 where priority > 2
> and priority < 4
etc....
I've implemented in my model code but it has some deadlock issues and
I really strongly have believed all along that this belongs in the db
anyway. Implementing the above with triggers is a tricky problem
because the trigger would key off the priority change and the
successive updates would recusively trigger.
Why not update everything into a temp table first, then update the
original with the new values from that? Or maybe a view is better suited
to this.
brian
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
