Re: date range to set of dates expansion

Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)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