Efficiently selecting single row from a select with window functions row_number, lag and lead - Mailing list pgsql-general

select id, row_number() over w as rownum, lag(id, 1) over w as prev, lead(id, 1) over w as next from route where id=1350 window w as (order by shortname, id asc rows between 1 preceding and 1 following)  order by shortname, id ;

However this gives the result

1350;1;;

The following query gives the result I am expecting

select * from (select id, row_number() over w as rownum,
lag(id, 1) over w as prev, lead(id, 1) over w as next
from route window w as (order by shortname, id
rows between 1 preceding and 1 following) order by shortname, id) as s where id=1350

1350;3;1815;1813

The explain plan is
"Subquery Scan on s  (cost=0.14..15.29 rows=1 width=32)"
"  Filter: (s.id = 1350)"
"  ->  WindowAgg  (cost=0.14..13.51 rows=143 width=12)"
"        ->  Index Only Scan using route_idx on route  (cost=0.14..10.29 rows=143 width=12)"

as it makes use of the index created as follows

CREATE INDEX route_idx
  ON route
  USING btree
  (shortname COLLATE pg_catalog."default", id);

I believe that the index has all the data that is needed to obtain the results in a single query.

Is it possible to write the query as a single select and if so how?

Thanks in advance

Andrew Bailey