Re: View

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.

--
Michael Fuhr