Re: date range to set of dates expansion

Gary Stainburn <gary.stainburn@ringways.co.uk> writes:

> Hi,
>
> How can I expand a date range in a table to a set of date records?
>
> I have a table of availabilities thus:
>
>   Column   |            Type             |                         Modifiers
> -----------+-----------------------------+------------------------------------------------------------
>  aid       | integer                     | not null default 
> nextval('availability_aid_seq'::regclass)
>  asid      | integer                     | not null
>  asdate    | date                        | not null
>  afdate    | date                        | not null
>  adays     | integer                     |
>  acomments | text                        |
>
>
> asdate is the start date
> afdate is the finish date
>
> How can I expand this to a set of
>
>   Column   |            Type             |                         Modifiers
> -----------+-----------------------------+------------------------------------------------------------
>  aid       | integer                     | not null 
>  asid      | integer                     | not null
>  adate    | date                        | not null
>  acomments | text                        |
>
> i.e.
>
>  aid | asid |   asdate   |   afdate   | adays |      acomments
> -----+------+------------+------------+-------+--------------------
>   12 |    1 | 2007-08-11 | 2007-08-12 |     1 | Early finish Sunday
>
> Becomes
>
>  aid | asid |   asdate   |      acomments
> -----+------+------------+--------------------
>   12 |    1 | 2007-08-11 | Early finish Sunday
>   12 |    1 | 2007-08-12 | Early finish Sunday
>
> I have a function date_range to return a set of dates, but so far I can't get 
> a valid view to work.

Why don't you just use the built-in PostgreSQL function for that?

SELECT aid, asid,      generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate,      acomments
FROM tbl