Обсуждение: View
Hello,
I have dates in a database that look something like so :
CREATE TABLE myEvents (
id integer DEFAULT nextval('serial_seq') PRIMARY KEY,
cal_id integer not null
references saa_calendars(id)
on update cascade on delete cascade,
start_date timestamp with time zone NOT NULL DEFAULT now(),
end_date timestamp with time zone NOT NULL DEFAULT now() + interval '1
hour',
summary text NOT NULL DEFAULT '',
description text NOT NULL DEFAULT ''
);
CREATE TABLE myRecurrence (
id integer DEFAULT nextval('serial_seq') PRIMARY KEY,
event_id integer not null
references myEvents(id)
on update cascade on delete cascade,
timeInterval integer not null default '1', -- Number of
weeks/days/months etc
timeSpan text NOT NULL DEFAULT 'day', -- day,week,month etc
limitDate timestamp with time zone NOT NULL DEFAULT now()
);
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. I was hoping to wrap this up in a VIEW
however Im at a loss as to how to do it, since its not your typical JOIN
or anything.
Is this possible in a regular VIEW or am I going to need to create a
plpgsql function or some other procedural method of accomplishing this?
Thank you for your time,
- A Gilmore
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
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