Thread: Calendar Scripts - Quite a complex one

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.

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 BerkusAglio Database SolutionsSan Francisco

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 BerkusAglio Database SolutionsSan Francisco

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

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

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

Hi all,

Thank you for reading this mail.

I am trying to do the following:
Extract the first half of _aaa and put it in column _bbb

Here is the table, named: _table:

Varchar[10]   |   Double Precision
_aaa            _bbb
_________________________________

1234567890

I used two functions to do it: substring() and to_number(). The SQL is
like this:
UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '99999');
The machine fails me and said
ERROR: invalid input syntac for type numeric: " "

I guess the machine can not treat TEXT as CHAR[5]. I tries to CAST TEXT as
CHAR[5]. It also doesnt allow me to do so.

Can anyone give me some hints on this?

regards,
Daniel

Daniel Lau <iedaniel@ust.hk> writes:
> I used two functions to do it: substring() and to_number(). The SQL is
> like this:
> UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '99999');
> The machine fails me and said
> ERROR: invalid input syntac for type numeric: " "

Works for me ...

regression=# create table _table (_aaa varchar(10), _bbb double precision);
CREATE TABLE
regression=# insert into _table values('1234567890', null);
INSERT 180987 1
regression=# UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '99999');
UPDATE 1
regression=# select * from _table;   _aaa    | _bbb
------------+-------1234567890 | 12345
(1 row)

regression=#

I don't think you have accurately described what you did.
        regards, tom lane

On Friday 09 January 2004 07:35, Daniel Lau wrote:
> Hi all,
>
> Thank you for reading this mail.
>
> I am trying to do the following:
> Extract the first half of _aaa and put it in column _bbb

> Varchar[10]   |   Double Precision
> _aaa            _bbb
> 1234567890

> I used two functions to do it: substring() and to_number(). The SQL is
> like this:
> UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '99999');
> The machine fails me and said
> ERROR: invalid input syntac for type numeric: " "

Works here.

richardh=# \d foo           Table "richardh.foo"Column |         Type          | Modifiers
--------+-----------------------+-----------_aaa   | character varying(10) |_bbb   | double precision      |

richardh=# UPDATE foo SET _bbb = to_number(substring(_aaa from 1 for 
5),'99999');
UPDATE 1
richardh=# SELECT * FROM foo
richardh-# ;   _aaa    | _bbb
------------+-------1234567890 | 12345
(1 row)


Since your error seems to be complaining about a space, I'd guess you've got 
other than numeric values in _aaa.

-- Richard Huxton

Richard Huxton <dev@archonet.com> writes:
> Since your error seems to be complaining about a space, I'd guess you've got 
> other than numeric values in _aaa.

In fact, with a bit of experimentation I see the same error message:

regression=# select to_number('12345', '99999');to_number
-----------    12345
(1 row)

regression=# select to_number('1234 ', '99999');to_number
-----------     1234
(1 row)

regression=# select to_number(' 1234', '99999');to_number
-----------     1234
(1 row)

regression=# select to_number('     ', '99999');
ERROR:  invalid input syntax for type numeric: " "
regression=# select to_number('zzzzz', '99999');
ERROR:  invalid input syntax for type numeric: " "
regression=#

The error message's report of the input string seems a tad misleading,
especially in the last case.  (Karel, is this fixable?)  But anyway,
it sure looks like the problem is bad input data.
        regards, tom lane

On Fri, 9 Jan 2004, Tom Lane wrote:

> Richard Huxton <dev@archonet.com> writes:
> > Since your error seems to be complaining about a space, I'd guess you've got 
> > other than numeric values in _aaa.
> 
> In fact, with a bit of experimentation I see the same error message:
> 
> regression=# select to_number('12345', '99999');
>  to_number
> -----------
>      12345
> (1 row)
> 
> regression=# select to_number('1234 ', '99999');
>  to_number
> -----------
>       1234
> (1 row)
> 
> regression=# select to_number(' 1234', '99999');
>  to_number
> -----------
>       1234
> (1 row)
> 
> regression=# select to_number('     ', '99999');
> ERROR:  invalid input syntax for type numeric: " "
> regression=# select to_number('zzzzz', '99999');
> ERROR:  invalid input syntax for type numeric: " "
> regression=#
> 
> The error message's report of the input string seems a tad misleading,
> especially in the last case.  (Karel, is this fixable?)  But anyway,
> it sure looks like the problem is bad input data.
> 
>             regards, tom lane
> 

Thanks Tom and Richard. Yes, it is the problem of bad input data. I have
4000 rows of data and there are 10 rows containing blank string ('
'). I have to add a Where clause to carry out the SQL:

UPDATE _table
SET _bbb = to_number(substring(_aaa from 1 for 5), '99999')
WHERE _aaa <> '     ';

I guess a function checking if a string contains only numbers would be
betteroff. But I find no such functions. Checking that it's not blank
would be the only solution I can think of.

Thanks again.

regards,
Daniel Lau