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
