sorry for being unclear.
but you guessed right. ID is UNIQUE and and I want to select a row by its ID and also get the previous and next ones in the name, name2-order.
For the selected row I need all datafields and for the next and previous I need only the ID (to have it referenced on the dataoutputpage for a certain row).
OK, this is a lot clearer now.
I suppose you have a UNIQUE(name,name2) or else, if you have several rows with the same (name,name2) you'll get one of them, but you won't know which one.
For example : select * from test; id | name | name2 ----+------+------- 1 | a | a 2 | a | b 3 | a | c 4 | b | a 5 | b | b 6 | b | c 7 | c | a 8 | c | b 9 | c | c (9 lignes)
Solution #1 :
- In you application : SELECT * FROM test WHERE id=4; id | name | name2 ----+------+------- 4 | b | a
You then fetch name and name2 and issue the two following SELECT, replacing 'a' and 'b' with name2 and name1 :
SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY name,name2 ASC LIMIT 1;
id | name | name2
----+------+-------
5 | b | b
SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY name,name2 DESC LIMIT 1;
id | name | name2
----+------+-------
3 | a | c
These should use an index on (name,name2).
Solution #2 :
You could do the same in a pl/pgsql function, which will be a lot faster, and return three rows.
It is a pity you cannot use (name,name2) > ('a','b').---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
