sql - Select a row, and the rows either side of it -

i'm putting blog posts implement json api. end, when retrieve post database, want include links next , previous posts. database means when retrieve row posts table, i'd retrieve row before , row after it.

i'm bit of noob sql (specifically postgres). @ moment have following:

select *    posts    id >= (     select id      posts      id < (       select id       posts       slug = 'the-slug'     )     , published = true     order id desc     limit 1   )   , published = true   order id asc   limit 3; 

(posts have serial id primary key, published boolean, , slug varchar)

all have access slug of centre post. works, breaks if the-slug represents first published row. seems quite naive. there better way go this?

edit:

i'm trying avoid question being specific problem answers might of more use many. however, above quite loose. consider table created following:

create table posts (     id serial primary key,     slug varchar(255) unique not null,     published boolean not null default false ); 

there more columns, should of no relevance question. i'm trying find, given slug, row matches slug , rows before , after (where exist). i'm trying achieve:

• if no slug matches, no rows.
• if slug matches post, , first published post...
  • one row if 1 post published.
  • two rows (the first , second) if more 1 post published.
• if slug matches post, , last published post...
  • one row if 1 post published.
  • two rows (the second last , last) if more 1 post published.
• if slug matches post , not first or last published post...
  • three rows (the row before matching row, row, , row after matching row).

here's hacky way it:

select *    posts   id in (     select id       posts      slug = 'the-slug'     union      select id+1       posts      slug = 'the-slug'     union      select id-1       posts      slug = 'the-slug'     )    , published = true  order id asc  limit 3; 

there may more efficient way lag , lead options.