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

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
> > >
> > >
>
>
>