Expanding Date Ranges in a Postgresql Column

Question

This one is probably much simpler than I'm making it. I have to query a table that has a basic layout like this

(productNumber varchar, firstYearAvailable int, lastYearAvailable int, ...)

For example

(12345, 2004, 2006, ...),
(22293b, 2009, 2011, ...),
(a7564n, 2007, 2010, ...),
etc.

This table is queried in a function alongside other tables through a complex series of joins. As of now, the query is something like this

SELECT field1, field2, productNumber, firstYearAvailable, lastYearAvailable, ...
FROM ...
JOIN ...
WHERE ...
etc.

Rather than returning a year range for each product (first year - last year), it would be more useful to me to return an entry for each year for each product (firstYear, firstYear + 1,..., lastYear). For example, let's say the product number was 12345 and the range for that product was 2004-2006, then the query should return for that product and those two columns.

(12345,2004),
(12345,2005),
(12345,2006)

There is one more detail that makes this query especially difficult, the query searches across multiple part numbers, and each part number may have a different year range.

Does anyone have any tips on how to aproach this problem? I've fooled around with loops and joins and dynamically generating the SQL statement through conditionals, but none of those were effective.

Hopefully I've provided enough information, but if I haven't, just ask.

Thanks

Answer 1

The function generate_series(start int, stop int) is your answer:

create temporary table product_years
  (productNumber varchar, firstYearAvailable int, lastYearAvailable int);

insert into product_years values
  ('12345', 2004, 2006),
  ('22293b', 2009, 2011),
  ('a7564n', 2007, 2010);

select
    productNumber,
    generate_series(firstYearAvailable,lastYearAvailable) as productYear
  from product_years;

 productnumber | productyear
---------------+-------------
 12345         |        2004
 12345         |        2005
 12345         |        2006
 22293b        |        2009
 22293b        |        2010
 22293b        |        2011
 a7564n        |        2007
 a7564n        |        2008
 a7564n        |        2009
 a7564n        |        2010
(10 rows)