From: | Stephan Richter <srichter(at)cbu(dot)edu> |
---|---|
To: | PGSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Another question: Order of entries |
Date: | 2000-09-09 14:58:32 |
Message-ID: | 4.3.1.0.20000909092716.00a8ddc0@198.78.130.6 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | K Parker | 2000-09-09 16:35:05 | Re: Race conditions... |
Previous Message | Gary MacDougall | 2000-09-09 14:44:57 | Transaction Manager |