Database: PostgreSQL

I have this starting query with this starting result.

select 
plw.total_pages,
plw.page_range,
plw.from_page,
plw.to_page
from plw
where plw.id = 6

I want to get data from a query (I don't know if it can call it "populate"). There is only this one record in the database but I want to get total of to_page - from_page = 75 - 50 = 25 records as a result of the query. Each populated record should have another field page to track which page it is.

The result should be something like this:

Just so that I can get 25 records out of just 1 record within its page range.

Is there any solution to achieve this?

CodePudding user response：

You don't mention the specific database so here are two solutions:

1. generate_series() is the typical function that produces rows in a range of numbers, and it's trivial to use, if available.

    select p.*, n.curr
    from plw p
    cross join generate_series(
      (select from_page from plw where id = 6),
      (select to_page from plw where id = 6)
    ) n (curr)
    where p.id = 6
   

2. The more generic way is to use a recursive CTE that can produce the rows you want. For example:

    with
    n (curr, ls) as (
      select from_page, to_page from plw where id = 6
     union all
      select curr   1, ls from n where n < ls
    )
    select p.*, n.curr
    from plw p
    cross join n
    where p.id = 6