Re: select single entry and its neighbours using direct-acess to index?

> 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').