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
