optimizing calendar repeat events - Mailing list pgsql-general

I'm currently trying to optimize a calendar application's handling of
repeat events. Here are two relevant tables:

-- Calendar event entry
-- cal_date is an integer of the format YYYYMMDD
-- cal_time is an integer of the format HHMM
-- cal_duration is in minutes
-- cal_priority: 1=Low, 2=Med, 3=High
-- cal_type: E=Event, M=Repeating event
-- cal_access:
--       P=Public
--       R=Confidential (others can see time allocated but not what it is)
CREATE TABLE webcal_entry (
  cal_id INT NOT NULL,
  cal_group_id INT,
  cal_create_by VARCHAR(25) NOT NULL,
  cal_date INT NOT NULL,
  cal_time INT,
  cal_mod_date INT,
  cal_mod_time INT,
  cal_duration INT NOT NULL,
  cal_priority INT DEFAULT 2,
  cal_type CHAR(1) DEFAULT 'E',
  cal_access CHAR(1) DEFAULT 'P',
  cal_name VARCHAR(80) NOT NULL,
  cal_description TEXT,
  PRIMARY KEY ( cal_id )
);

-- repeat cal_type are: Daily, Weekly, Monthly (by day),
-- Monthly (by date), Yearly
-- cal_frequency is an interval of how the event will repeat, i.e.
--  every (2) months, every (3) weeks, etc.
-- cal_days is for weekly repeats. default is 'nnnnnnn'. If you want to
--  repeat weekly on sunday and monday, you'd have cal_type set to
--  'weekly' and cal_days set to 'yynnnnn'.
-- cal_end is when the day repeat will end.
-- cal_id is the event id
CREATE TABLE webcal_entry_repeats (
   cal_id INT DEFAULT '0' NOT NULL,
   cal_type VARCHAR(20),
   cal_end INT,
   cal_frequency INT DEFAULT '1',
   cal_days CHAR(7),
   PRIMARY KEY (cal_id)
);

When I want all the events for a particular date range, I query for those
rows in webcal_entry. In addition, I have to pull in ALL the rows for
webcal_entry_repeats, loops through each webcal_entry row. For each
webcal_entry row, loop through all the webcal_entry_repeat rows to see if
any repeat events go before or after that webcal_entry row. As you can
see, with many rows of webcal_entry_repeats, this can get rather bogged
down and slow, not to mention memory intensive. Also, this current method
doesn't allow me to use the LIMIT clause to 'paginate' the results to
display only certain number of events per page.

What I'm looking to do is combine the columns of webcal_entry_repeats into
webcal_entry so describe each event. If that event doesn't repeat then
cal_type is set to "no-repeat". Then when I query for a date range, the
query would somehow figure out if the combination of repeat type columns
would include that event in the date range i'm querying. This would allow
me to do it in one SELECT and make use of the LIMIT clause to 'paginate'
results per page (say results of 20 events per query).

I thought about it for a while, but couldn't come up with an efficient
solution. It's especially hard with the frequency, one would have to
somehow loop in the sql query a certain times of frequency while the end
date is within our selected range.

Thanks for any suggestions.