Skip site navigation (1) Skip section navigation (2)

View

From: A Gilmore <agilmore(at)shaw(dot)ca>
To: Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: View
Date: 2005-09-24 19:11:09
Message-ID: 4335A4CD.0@shaw.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

  • Re: View at 2005-09-26 03:40:39 from Michael Fuhr

pgsql-novice by date

Next:From: Rafael BarbosaDate: 2005-09-25 18:51:30
Subject: Windows XP + Postgre
Previous:From: AnnounceDate: 2005-09-24 14:55:20
Subject: Re: Primary and Foreign Key?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group