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

Re: View

From: A Gilmore <agilmore(at)shaw(dot)ca>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Re: View
Date: 2005-09-27 17:54:45
Message-ID: 43398765.80808@shaw.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

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

pgsql-novice by date

Next:From: Ennio-SrDate: 2005-09-27 20:04:35
Subject: right align (justify) to_char outcome in a column
Previous:From: Steve TucknottDate: 2005-09-27 06:22:38
Subject: Dumping/Restoring Later Release into Earlier

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