Another question: Order of entries

Hello everyone,

I have unfortunately another question (just to show how little I know about
functions in PostGreSQL):

I have a table ClassProblem. The table contains a reference to the Problem
table and contains a 'location' attribute. The entries in ClassProblem have
to be returned from in ascending location order.

Let's say I have:

ProblemId       |     Location
  --------------------+------------------
         1            |          3
         2            |          1
         3            |          2

Now two issues:
------------------------

1. I want to add another entry and it should have location=4, but I do not
know that it will have this location till I look at the table. I cannot use
sequence, since I will have many of these 'sets' in this table (I
simplified the table for my question.) So a function should first check for
the largest current location and then add 1 to the result and use it to
create the new entry.

2. I want to change the order the of one problem; let's say problem 3 to
location 1; so I just want to send in my SQL:

UPDATE ClassProblem SET location=1 WHERE problemid=3;

but since location is unique, the other entries should be adjusted
respectively to give me the following:

ProblemId       |     Location
  --------------------+------------------
         1            |          3
         2            |          2
         3            |          1

Note: Problem 2 shifted down to location 2.

I know how to solve the issue using Python (the programming language I
use), but it would require N*log(N) update statements by average, which can
be very expensive, especially when many people run this code at the same time.

Thanks a lot for your help in advance!

Regards,
Stephan
--
Stephan Richter
CBU - Physics and Chemistry Student
Web2k - Web Design/Development & Technical Project Management