From sgnerd@yahoo.com.sg Tue Jan  6 06:09:16 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 32060D1B4C6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jan 2004 06:09:16 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 70490-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jan 2004 02:08:33 -0400 (AST)
Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com
 [216.136.173.59])
	by svr1.postgresql.org (Postfix) with SMTP id 1ACA5D1B4B3
	for <pgsql-sql@postgresql.org>; Tue,  6 Jan 2004 02:08:26 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp015.mail.yahoo.com with SMTP; 6 Jan 2004 06:08:28 -0000
Message-ID: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Calendar Scripts - Quite a complex one
Date: Tue, 6 Jan 2004 11:36:01 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00A2_01C3D449.42BD28E0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=HTML_40_50
X-Spam-Level: 
X-Archive-Number: 200401/31
X-Sequence-Number: 16424

This is a multi-part message in MIME format.

------=_NextPart_000_00A2_01C3D449.42BD28E0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Friends,

I am working on Postgres 7.3.4 on RH Linux . For our application, we are in=
 a position to give calendar function (appointments and scheduling) with ou=
r application. Can somebody suggest me links or sample scripts for developi=
ng the calendar function.

It is much similar to MS outlook. Please shed some light.


Regards
Kumar

------=_NextPart_000_00A2_01C3D449.42BD28E0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I am working on Postgres 7.3.4 on RH Linux . For our=20
application, we are in a position to give calendar function (appointments a=
nd=20
scheduling) with our application. Can somebody suggest me links or sample=
=20
scripts for developing the calendar function.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>It is much similar to MS outlook. Please shed some=20
light.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Regards</FONT></DIV>
<DIV><FONT size=3D2>Kumar</FONT></DIV>
<P></P></BODY></HTML>

------=_NextPart_000_00A2_01C3D449.42BD28E0--




From peter_e@gmx.net Tue Jan  6 18:56:40 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 14132D1C526
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jan 2004 18:56:40 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 44951-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jan 2004 14:55:52 -0400 (AST)
Received: from curie.credativ.org (credativ.com [217.160.209.18])
	by svr1.postgresql.org (Postfix) with ESMTP id 1E8AAD1B511
	for <pgsql-sql@postgresql.org>; Tue,  6 Jan 2004 14:55:42 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by curie.credativ.org (Postfix) with ESMTP
	id 6967356243; Tue,  6 Jan 2004 19:55:42 +0100 (CET)
Received: from colt.pezone.net (dsl-213-023-254-001.arcor-ip.net
	[213.23.254.1]) (using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by curie.credativ.org (Postfix) with ESMTP
	id 7B3C156240; Tue,  6 Jan 2004 19:55:41 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: "Kumar" <sgnerd@yahoo.com.sg>, "psql" <pgsql-sql@postgresql.org>
Subject: Re: Calendar Scripts - Quite a complex one
Date: Tue, 6 Jan 2004 19:56:57 +0100
User-Agent: KMail/1.5.1
References: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
In-Reply-To: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200401061956.57063.peter_e@gmx.net>
X-Virus-Scanned: by AMaViS at credativ.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/32
X-Sequence-Number: 16425

Kumar wrote:
> I am working on Postgres 7.3.4 on RH Linux . For our application, we
> are in a position to give calendar function (appointments and
> scheduling) with our application. Can somebody suggest me links or
> sample scripts for developing the calendar function.

You can probably lift out the complete calendar functionality from an 
existing groupware solution, say, www.egroupware.org.  I'm not sure 
whether it's practical to do the calendar things in the database, since 
you will also need a significant amount of intelligence in the client 
to display reasonable calendar graphics, for instance.




From josh@agliodbs.com Tue Jan  6 22:37:43 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 1560AD1B433
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jan 2004 22:37:43 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 91253-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Jan 2004 18:36:56 -0400 (AST)
Received: from davinci.ethosmedia.com (server228.ethosmedia.com
	[209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id CAD3ED1B47D
	for <pgsql-sql@postgresql.org>; Tue,  6 Jan 2004 18:36:35 -0400 (AST)
Received: from [63.195.55.98] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 4213163; Tue, 06 Jan 2004 14:37:36 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Peter Eisentraut <peter_e@gmx.net>,
	"Kumar" <sgnerd@yahoo.com.sg>, "psql" <pgsql-sql@postgresql.org>
Subject: Re: Calendar Scripts - Quite a complex one
Date: Tue, 6 Jan 2004 14:13:12 -0800
User-Agent: KMail/1.4.3
References: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
	<200401061956.57063.peter_e@gmx.net>
In-Reply-To: <200401061956.57063.peter_e@gmx.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200401061413.12096.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/33
X-Sequence-Number: 16426

Peter,

> You can probably lift out the complete calendar functionality from an=20
> existing groupware solution, say, www.egroupware.org.  I'm not sure=20
> whether it's practical to do the calendar things in the database, since=
=20
> you will also need a significant amount of intelligence in the client=20
> to display reasonable calendar graphics, for instance.

But all of the appointments, holidays, etc can and should be stored in the=
=20
database, and by using function programming one can automate generating all=
=20
of the raw data for the calendar graphics.   We do this with our legal=20
calendaring app.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From sgnerd@yahoo.com.sg Wed Jan  7 06:09:50 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 8321CD1B475
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  7 Jan 2004 06:09:50 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 53667-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  7 Jan 2004 02:09:02 -0400 (AST)
Received: from smtp104.mail.sc5.yahoo.com (smtp104.mail.sc5.yahoo.com
	[66.163.169.223])
	by svr1.postgresql.org (Postfix) with SMTP id B68F7D1B449
	for <pgsql-sql@postgresql.org>; Wed,  7 Jan 2004 02:09:00 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp104.mail.sc5.yahoo.com with SMTP; 7 Jan 2004 06:08:59 -0000
Message-ID: <001701c3d4e4$62fc4dc0$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: <josh@agliodbs.com>, "Peter Eisentraut" <peter_e@gmx.net>,
	"psql" <pgsql-sql@postgresql.org>
References: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
	<200401061956.57063.peter_e@gmx.net>
	<200401061413.12096.josh@agliodbs.com>
Subject: Re: Calendar Scripts - Quite a complex one
Date: Wed, 7 Jan 2004 11:36:16 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/34
X-Sequence-Number: 16427

Hi,

The complexity comes while scheduling the appointments. Let us say, I have
scheduled so many meetings in my calendar of various schedules like daily, 3
days once, weekly, bi weekly. monthly, bi monthly, etc.

While I open the calendar for end of this year (say Dec 2004), I need to
show those meetings in my calendar, but I have data until Jan 2004.

What is the best way to show it. Populating the records from Jan 2004 to Dec
2004 in the pgsql function and display it in the calendar, or just write a
query to generate temporary records only for that Dec 2004 and not storing
them at the database.

Please shed some idea.

Regards
Kumar

----- Original Message ----- 
From: "Josh Berkus" <josh@agliodbs.com>
To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar" <sgnerd@yahoo.com.sg>;
"psql" <pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 3:43 AM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one


Peter,

> You can probably lift out the complete calendar functionality from an
> existing groupware solution, say, www.egroupware.org.  I'm not sure
> whether it's practical to do the calendar things in the database, since
> you will also need a significant amount of intelligence in the client
> to display reasonable calendar graphics, for instance.

But all of the appointments, holidays, etc can and should be stored in the
database, and by using function programming one can automate generating all
of the raw data for the calendar graphics.   We do this with our legal
calendaring app.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From chris@travelamericas.com Wed Jan  7 07:50:55 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 2F771D1B45B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  7 Jan 2004 07:50:55 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 65252-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  7 Jan 2004 03:50:06 -0400 (AST)
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by svr1.postgresql.org (Postfix) with ESMTP id CF658D1B447
	for <pgsql-sql@postgresql.org>; Wed,  7 Jan 2004 03:50:04 -0400 (AST)
Received: from [61.5.0.69] (helo=winxp)
	by noon.pghoster.com with asmtp (Exim 4.24)
	id 1Ae8S7-0000DW-Jo; Wed, 07 Jan 2004 01:50:08 -0600
Message-ID: <014301c3d4f2$ce2bffb0$9100053d@winxp>
From: "Chris Travers" <chris@travelamericas.com>
To: "Kumar" <sgnerd@yahoo.com.sg>, <josh@agliodbs.com>,
	"Peter Eisentraut" <peter_e@gmx.net>, "psql" <pgsql-sql@postgresql.org>
References: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
	<200401061956.57063.peter_e@gmx.net>
	<200401061413.12096.josh@agliodbs.com>
	<001701c3d4e4$62fc4dc0$7502a8c0@hdsc.com>
Subject: Re: Calendar Scripts - Quite a complex one
Date: Wed, 7 Jan 2004 14:49:28 +0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - travelamericas.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=BAYES_20,
	ORIGINAL_MESSAGE, QUOTED_EMAIL_TEXT, RCVD_IN_DSBL, REFERENCES
X-Spam-Level: 
X-Archive-Number: 200401/35
X-Sequence-Number: 16428

Hi all;

If I understand Kumar's post correctly, he is having some question relating
to the issue of even recurrance.  I would highly suggest reading the
ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject.
HERMES (my app with appointment/calendar functionality) doesn't yet support
appointment recurrance, and I have not formalized my approach to this.
However, here is the general approach I have been looking at:

1: Have a separate table of recurrance rules (1:1 with appointments) or have
a recurrance datatype.

2: Build some functions to calculate dates and times when the appointment
would recurr.  You can also have a "Recur Until" field so you can limit your
searches this way.

3:  Use a view to find recurring appointments on any given day.

This avoids a very nasty problem in the prepopulation approach-- that of a
cancelled recurring meeting.  How do you cancel ALL appropriate instances of
the meeting while leaving those that occured in the past available for
records?

Kumar-- if you are working with PHP, I would be happy to work with you in
this endevor so that the same functionality can exist in my open source
(GPL'd) application.  I think that the source for this would likely be one
of those things that might be best LGPL'd if added to my app.

Best Wishes,
Chris Travers

----- Original Message -----
From: "Kumar" <sgnerd@yahoo.com.sg>
To: <josh@agliodbs.com>; "Peter Eisentraut" <peter_e@gmx.net>; "psql"
<pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 1:06 PM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one


> Hi,
>
> The complexity comes while scheduling the appointments. Let us say, I have
> scheduled so many meetings in my calendar of various schedules like daily,
3
> days once, weekly, bi weekly. monthly, bi monthly, etc.
>
> While I open the calendar for end of this year (say Dec 2004), I need to
> show those meetings in my calendar, but I have data until Jan 2004.
>
> What is the best way to show it. Populating the records from Jan 2004 to
Dec
> 2004 in the pgsql function and display it in the calendar, or just write a
> query to generate temporary records only for that Dec 2004 and not storing
> them at the database.
>
> Please shed some idea.
>
> Regards
> Kumar
>
> ----- Original Message -----
> From: "Josh Berkus" <josh@agliodbs.com>
> To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar" <sgnerd@yahoo.com.sg>;
> "psql" <pgsql-sql@postgresql.org>
> Sent: Wednesday, January 07, 2004 3:43 AM
> Subject: Re: [SQL] Calendar Scripts - Quite a complex one
>
>
> Peter,
>
> > You can probably lift out the complete calendar functionality from an
> > existing groupware solution, say, www.egroupware.org.  I'm not sure
> > whether it's practical to do the calendar things in the database, since
> > you will also need a significant amount of intelligence in the client
> > to display reasonable calendar graphics, for instance.
>
> But all of the appointments, holidays, etc can and should be stored in the
> database, and by using function programming one can automate generating
all
> of the raw data for the calendar graphics.   We do this with our legal
> calendaring app.
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>




From sgnerd@yahoo.com.sg Wed Jan  7 12:42:37 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id E4E0DD1B44C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  7 Jan 2004 12:42:37 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 17307-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  7 Jan 2004 08:41:50 -0400 (AST)
Received: from smtp014.mail.yahoo.com (smtp014.mail.yahoo.com
 [216.136.173.58])
	by svr1.postgresql.org (Postfix) with SMTP id AD12CD1B444
	for <pgsql-sql@postgresql.org>; Wed,  7 Jan 2004 08:41:46 -0400 (AST)
Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login)
	by smtp014.mail.yahoo.com with SMTP; 7 Jan 2004 12:41:48 -0000
Message-ID: <006101c3d51b$43390730$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Chris Travers" <chris@travelamericas.com>, <josh@agliodbs.com>,
	"Peter Eisentraut" <peter_e@gmx.net>, "psql" <pgsql-sql@postgresql.org>
References: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
	<200401061956.57063.peter_e@gmx.net>
	<200401061413.12096.josh@agliodbs.com>
	<001701c3d4e4$62fc4dc0$7502a8c0@hdsc.com>
	<014301c3d4f2$ce2bffb0$9100053d@winxp>
Subject: Re: Calendar Scripts - Quite a complex one
Date: Wed, 7 Jan 2004 18:09:07 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/36
X-Sequence-Number: 16429

Hi,
yes yes. U understood in a very correct way, as i have 2 tables -
appointments and recurrences. And we are not gonna use PHP.

For future dates, I am not gonna populate, instead I am gonna check for the
recurrences tables for ever appointments and based on the conditions, I am
gonna say how many time that appointment recure in that month and the
timestamp.

To process that I have get all the appointment data and its recurrence
pattern data into the cursor. Is there a way to get the records one by one
from the cursor and calculate it patterns.

CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp)
  RETURNS refcursor AS
'DECLARE
 cal_daily_date ALIAS FOR $1;
 ref  REFCURSOR;

BEGIN
     OPEN ref FOR
     SELECT ....................

     RETURN ref;

    END;'
    LANGUAGE 'plpgsql' VOLATILE;

How to open the cursor here so that I could check its  recurrences pattern.

Please shed some light.

Regards
kumar

----- Original Message ----- 
From: "Chris Travers" <chris@travelamericas.com>
To: "Kumar" <sgnerd@yahoo.com.sg>; <josh@agliodbs.com>; "Peter Eisentraut"
<peter_e@gmx.net>; "psql" <pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 1:19 PM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one


> Hi all;
>
> If I understand Kumar's post correctly, he is having some question
relating
> to the issue of even recurrance.  I would highly suggest reading the
> ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject.
> HERMES (my app with appointment/calendar functionality) doesn't yet
support
> appointment recurrance, and I have not formalized my approach to this.
> However, here is the general approach I have been looking at:
>
> 1: Have a separate table of recurrance rules (1:1 with appointments) or
have
> a recurrance datatype.
>
> 2: Build some functions to calculate dates and times when the appointment
> would recurr.  You can also have a "Recur Until" field so you can limit
your
> searches this way.
>
> 3:  Use a view to find recurring appointments on any given day.
>
> This avoids a very nasty problem in the prepopulation approach-- that of a
> cancelled recurring meeting.  How do you cancel ALL appropriate instances
of
> the meeting while leaving those that occured in the past available for
> records?
>
> Kumar-- if you are working with PHP, I would be happy to work with you in
> this endevor so that the same functionality can exist in my open source
> (GPL'd) application.  I think that the source for this would likely be one
> of those things that might be best LGPL'd if added to my app.
>
> Best Wishes,
> Chris Travers
>
> ----- Original Message -----
> From: "Kumar" <sgnerd@yahoo.com.sg>
> To: <josh@agliodbs.com>; "Peter Eisentraut" <peter_e@gmx.net>; "psql"
> <pgsql-sql@postgresql.org>
> Sent: Wednesday, January 07, 2004 1:06 PM
> Subject: Re: [SQL] Calendar Scripts - Quite a complex one
>
>
> > Hi,
> >
> > The complexity comes while scheduling the appointments. Let us say, I
have
> > scheduled so many meetings in my calendar of various schedules like
daily,
> 3
> > days once, weekly, bi weekly. monthly, bi monthly, etc.
> >
> > While I open the calendar for end of this year (say Dec 2004), I need to
> > show those meetings in my calendar, but I have data until Jan 2004.
> >
> > What is the best way to show it. Populating the records from Jan 2004 to
> Dec
> > 2004 in the pgsql function and display it in the calendar, or just write
a
> > query to generate temporary records only for that Dec 2004 and not
storing
> > them at the database.
> >
> > Please shed some idea.
> >
> > Regards
> > Kumar
> >
> > ----- Original Message -----
> > From: "Josh Berkus" <josh@agliodbs.com>
> > To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar" <sgnerd@yahoo.com.sg>;
> > "psql" <pgsql-sql@postgresql.org>
> > Sent: Wednesday, January 07, 2004 3:43 AM
> > Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> >
> >
> > Peter,
> >
> > > You can probably lift out the complete calendar functionality from an
> > > existing groupware solution, say, www.egroupware.org.  I'm not sure
> > > whether it's practical to do the calendar things in the database,
since
> > > you will also need a significant amount of intelligence in the client
> > > to display reasonable calendar graphics, for instance.
> >
> > But all of the appointments, holidays, etc can and should be stored in
the
> > database, and by using function programming one can automate generating
> all
> > of the raw data for the calendar graphics.   We do this with our legal
> > calendaring app.
> >
> > --
> > -Josh Berkus
> >  Aglio Database Solutions
> >  San Francisco
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >




From chris@travelamericas.com Thu Jan  8 10:06:05 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id A6BF0D1B4B7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  8 Jan 2004 10:06:05 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 11229-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  8 Jan 2004 06:05:17 -0400 (AST)
Received: from noon.pghoster.com (noon.pghoster.com [64.246.0.64])
	by svr1.postgresql.org (Postfix) with ESMTP id 4E75BD1B478
	for <pgsql-sql@postgresql.org>; Thu,  8 Jan 2004 06:05:14 -0400 (AST)
Received: from [61.94.40.172] (helo=winxp)
	by noon.pghoster.com with asmtp (Exim 4.24)
	id 1AeX2N-0004oV-Lk; Thu, 08 Jan 2004 04:05:12 -0600
Message-ID: <00e601c3d5ce$d474ceb0$ac285e3d@winxp>
From: "Chris Travers" <chris@travelamericas.com>
To: "Kumar" <sgnerd@yahoo.com.sg>, <josh@agliodbs.com>,
	"Peter Eisentraut" <peter_e@gmx.net>, "psql" <pgsql-sql@postgresql.org>
References: <00a501c3d41b$2dc9a0e0$7502a8c0@hdsc.com>
	<200401061956.57063.peter_e@gmx.net>
	<200401061413.12096.josh@agliodbs.com>
	<001701c3d4e4$62fc4dc0$7502a8c0@hdsc.com>
	<014301c3d4f2$ce2bffb0$9100053d@winxp>
	<006101c3d51b$43390730$7502a8c0@hdsc.com>
Subject: Re: Calendar Scripts - Quite a complex one
Date: Thu, 8 Jan 2004 17:02:59 +0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - noon.pghoster.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - travelamericas.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/45
X-Sequence-Number: 16438

Hi Kumar and others;

I have never worked with functions to return references to cursors.  Is
there a reason why it has to be done this way rather than returning a setof
appointments?

In that case:
create function app_today returns setof appointment (date) as '
declare
    new_appoint appointment;
    appoint_recur recurrance
begin
    for appointment in [SELECT query]
    loop
        -- do calculations
        if [condition] then
            return next;
        end if;
    end loop;
end;
' language plpgsql;

Note that the function is off my head and not even guaranteed to be exactly
what you need.

Best Wishes,
Chris Travers

----- Original Message -----
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Chris Travers" <chris@travelamericas.com>; <josh@agliodbs.com>; "Peter
Eisentraut" <peter_e@gmx.net>; "psql" <pgsql-sql@postgresql.org>
Sent: Wednesday, January 07, 2004 7:39 PM
Subject: Re: [SQL] Calendar Scripts - Quite a complex one


> Hi,
> yes yes. U understood in a very correct way, as i have 2 tables -
> appointments and recurrences. And we are not gonna use PHP.
>
> For future dates, I am not gonna populate, instead I am gonna check for
the
> recurrences tables for ever appointments and based on the conditions, I am
> gonna say how many time that appointment recure in that month and the
> timestamp.
>
> To process that I have get all the appointment data and its recurrence
> pattern data into the cursor. Is there a way to get the records one by one
> from the cursor and calculate it patterns.
>
> CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp)
>   RETURNS refcursor AS
> 'DECLARE
>  cal_daily_date ALIAS FOR $1;
>  ref  REFCURSOR;
>
> BEGIN
>      OPEN ref FOR
>      SELECT ....................
>
>      RETURN ref;
>
>     END;'
>     LANGUAGE 'plpgsql' VOLATILE;
>
> How to open the cursor here so that I could check its  recurrences
pattern.
>
> Please shed some light.
>
> Regards
> kumar
>
> ----- Original Message -----
> From: "Chris Travers" <chris@travelamericas.com>
> To: "Kumar" <sgnerd@yahoo.com.sg>; <josh@agliodbs.com>; "Peter Eisentraut"
> <peter_e@gmx.net>; "psql" <pgsql-sql@postgresql.org>
> Sent: Wednesday, January 07, 2004 1:19 PM
> Subject: Re: [SQL] Calendar Scripts - Quite a complex one
>
>
> > Hi all;
> >
> > If I understand Kumar's post correctly, he is having some question
> relating
> > to the issue of even recurrance.  I would highly suggest reading the
> > ICalendar RFC (RFC 2445) as it has some interesting ideas on the
subject.
> > HERMES (my app with appointment/calendar functionality) doesn't yet
> support
> > appointment recurrance, and I have not formalized my approach to this.
> > However, here is the general approach I have been looking at:
> >
> > 1: Have a separate table of recurrance rules (1:1 with appointments) or
> have
> > a recurrance datatype.
> >
> > 2: Build some functions to calculate dates and times when the
appointment
> > would recurr.  You can also have a "Recur Until" field so you can limit
> your
> > searches this way.
> >
> > 3:  Use a view to find recurring appointments on any given day.
> >
> > This avoids a very nasty problem in the prepopulation approach-- that of
a
> > cancelled recurring meeting.  How do you cancel ALL appropriate
instances
> of
> > the meeting while leaving those that occured in the past available for
> > records?
> >
> > Kumar-- if you are working with PHP, I would be happy to work with you
in
> > this endevor so that the same functionality can exist in my open source
> > (GPL'd) application.  I think that the source for this would likely be
one
> > of those things that might be best LGPL'd if added to my app.
> >
> > Best Wishes,
> > Chris Travers
> >
> > ----- Original Message -----
> > From: "Kumar" <sgnerd@yahoo.com.sg>
> > To: <josh@agliodbs.com>; "Peter Eisentraut" <peter_e@gmx.net>; "psql"
> > <pgsql-sql@postgresql.org>
> > Sent: Wednesday, January 07, 2004 1:06 PM
> > Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> >
> >
> > > Hi,
> > >
> > > The complexity comes while scheduling the appointments. Let us say, I
> have
> > > scheduled so many meetings in my calendar of various schedules like
> daily,
> > 3
> > > days once, weekly, bi weekly. monthly, bi monthly, etc.
> > >
> > > While I open the calendar for end of this year (say Dec 2004), I need
to
> > > show those meetings in my calendar, but I have data until Jan 2004.
> > >
> > > What is the best way to show it. Populating the records from Jan 2004
to
> > Dec
> > > 2004 in the pgsql function and display it in the calendar, or just
write
> a
> > > query to generate temporary records only for that Dec 2004 and not
> storing
> > > them at the database.
> > >
> > > Please shed some idea.
> > >
> > > Regards
> > > Kumar
> > >
> > > ----- Original Message -----
> > > From: "Josh Berkus" <josh@agliodbs.com>
> > > To: "Peter Eisentraut" <peter_e@gmx.net>; "Kumar"
<sgnerd@yahoo.com.sg>;
> > > "psql" <pgsql-sql@postgresql.org>
> > > Sent: Wednesday, January 07, 2004 3:43 AM
> > > Subject: Re: [SQL] Calendar Scripts - Quite a complex one
> > >
> > >
> > > Peter,
> > >
> > > > You can probably lift out the complete calendar functionality from
an
> > > > existing groupware solution, say, www.egroupware.org.  I'm not sure
> > > > whether it's practical to do the calendar things in the database,
> since
> > > > you will also need a significant amount of intelligence in the
client
> > > > to display reasonable calendar graphics, for instance.
> > >
> > > But all of the appointments, holidays, etc can and should be stored in
> the
> > > database, and by using function programming one can automate
generating
> > all
> > > of the raw data for the calendar graphics.   We do this with our legal
> > > calendaring app.
> > >
> > > --
> > > -Josh Berkus
> > >  Aglio Database Solutions
> > >  San Francisco
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
> > >
> > >
>
>
>