Re: View

Michael Fuhr wrote:
> On Sat, Sep 24, 2005 at 12:11:09PM -0700, A Gilmore wrote:
>
>>So I have event bookings, then if they use some kind of recurrence, its
>>stored in myRecurrence.  This has worked fine, since sorting out
>>recurring dates (creating the duplicate events) is all done client-side
>>based on these entries.
>>
>>However now I need the server to be aware of recurring dates.  So if an
>>event is booked, then to recur each month for 5 months, Id have 5
>>entires show up in my SELECT.
>
>
> You might be able to use generate_series() (a standard set-returning
> function in 8.0, and trivial to write in earlier versions).  Here's
> a simplified example based on what you posted; it might be close
> to what you're looking for:
>
> CREATE TABLE event (
>     id    integer PRIMARY KEY,
>     date  date NOT NULL
> );
>
> CREATE TABLE recurrence (
>     id       integer PRIMARY KEY,
>     eventid  integer NOT NULL REFERENCES event,
>     num      integer NOT NULL CHECK (num > 0),
>     freq     interval NOT NULL
> );
>
> INSERT INTO event (id, date) VALUES (1, '2005-01-01');
> INSERT INTO event (id, date) VALUES (2, '2005-02-02');
> INSERT INTO event (id, date) VALUES (3, '2005-03-03');
>
> INSERT INTO recurrence (id, eventid, num, freq) VALUES (1, 2, 2, '1 week');
> INSERT INTO recurrence (id, eventid, num, freq) VALUES (2, 3, 5, '1 month');
>
> SELECT e.id,
>        e.date AS origdate,
>        r.num,
>        r.freq,
>        (e.date + generate_series(0, coalesce(r.num - 1, 0))
>                  * coalesce(r.freq, '1 day'))::date AS recurdate
> FROM event AS e
> LEFT OUTER JOIN recurrence AS r ON r.eventid = e.id
> ORDER by e.id, recurdate;
>
>  id |  origdate  | num |  freq  | recurdate
> ----+------------+-----+--------+------------
>   1 | 2005-01-01 |     |        | 2005-01-01
>   2 | 2005-02-02 |   2 | 7 days | 2005-02-02
>   2 | 2005-02-02 |   2 | 7 days | 2005-02-09
>   3 | 2005-03-03 |   5 | 1 mon  | 2005-03-03
>   3 | 2005-03-03 |   5 | 1 mon  | 2005-04-03
>   3 | 2005-03-03 |   5 | 1 mon  | 2005-05-03
>   3 | 2005-03-03 |   5 | 1 mon  | 2005-06-03
>   3 | 2005-03-03 |   5 | 1 mon  | 2005-07-03
> (8 rows)
>
> Note that the documentation says that using a set-returning function
> in the select list is deprecated and might not work in future
> versions of PostgreSQL.  It does work in 8.0.3 and 8.1beta2, but
> for forward compatibility you might want to write a function to do
> the iteration unless somebody posts an alternative.
>

Thank you, thats quite interesting, I wasn't aware of generate_series
functionality.  But due to the deprecation and that the real tables are
quite complex I think I'd be best to focus on doing it in a function.

- A Gilmore