Re: select single entry and its neighbours using direct-acess to index?
От | Pierre-Frédéric Caillaud |
---|---|
Тема | Re: select single entry and its neighbours using direct-acess to index? |
Дата | |
Msg-id | opsik9v9kacq72hf@musicbox обсуждение исходный текст |
Ответ на | Re: select single entry and its neighbours using direct-acess (peter pilsl <pilsl@goldfisch.at>) |
Список | pgsql-general |
> 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').
В списке pgsql-general по дате отправления: