Обсуждение: Stored Procedure Question

I wanted to create a store procedure (unless there is a easier way) so
that i get a person's next birthday date given their birthday.

Aka, in one of my queries i am trying to do:


psql> update users set next_birthday=get_next_birthday(dob) where
user_id='42';

except, i don't quite know how to go about creating the
get_next_birthday function.
'dob' is a field in users that is of type 'date'.

I have tried the following method:

psql> update users set next_birthday=dob + (date_part('year', timestamp
'now') - date_part('year', dob))*365.25;

Which works wonderfully, except when when i if the date of birth is say
'1973-01-10' - in this case, it will put
the next_birthday as 2003-01-10 as it always converts the year to the
current year, not realising that the date/month
have already passed for this year.

Any help would be appreciated,

kind regards,

simran.

Once you have a function there should be no need to do an update on a table.
Just query using the function whenever you need the next birthday.

Here's a quick hack. It works on my birthday. Birthdays in the future give
unpredictable results. In other words, play with it as a starting point but
don't rely on it until proven good ('' is two single quotes not one double
quote though I think the double-quote version will work as well):

create function nextbday(date) returns date as
'select ($1 + (''1 year'' + date_trunc(''year'',age(now(),$1))))::date'
language sql;

Cheers,
Steve


On Thursday 16 January 2003 3:30 pm, simran wrote:
> I wanted to create a store procedure (unless there is a easier way) so
> that i get a person's next birthday date given their birthday.
>
> Aka, in one of my queries i am trying to do:
>
>
> psql> update users set next_birthday=get_next_birthday(dob) where
> user_id='42';
>
> except, i don't quite know how to go about creating the
> get_next_birthday function.
> 'dob' is a field in users that is of type 'date'.
>
> I have tried the following method:
>
> psql> update users set next_birthday=dob + (date_part('year', timestamp
> 'now') - date_part('year', dob))*365.25;
>
> Which works wonderfully, except when when i if the date of birth is say
> '1973-01-10' - in this case, it will put
> the next_birthday as 2003-01-10 as it always converts the year to the
> current year, not realising that the date/month
> have already passed for this year.
>
> Any help would be appreciated,
>
> kind regards,
>
> simran.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Hi Steve,

Thanks for your help... i was trying something in the mean time anyway
and came up with:

===================
CREATE FUNCTION "get_next_birthday" (integer,integer) RETURNS date AS '
DECLARE
        month ALIAS FOR $1;
        day ALIAS FOR $2;
BEGIN
        IF date(\'now\') <= (date(date_part(\'year\', timestamp \'now\') || \'-01-01\') + (month || \'
month\')::interval+ (day || \' day\')::interval - \'1 month 1 day\'::interval) 
        THEN
          RETURN (date(date_part(\'year\', timestamp \'now\') || \'-01-01\') + (month || \' month\')::interval + (day
||\' day\')::interval - \'1 month 1 day\'::interval); 
        ELSE
          RETURN (date(date_part(\'year\', timestamp \'now\') || \'-01-01\') + (month || \' month\')::interval + (day
||\' day\')::interval - \'1 month 1 day\'::interval) + \'1 year\'::interval; 
        END IF;

END;
' LANGUAGE 'plpgsql';
===================

As you can see my version is not the simplest, so i'll use yours as a re-starting point...

thanks again,

simran.


On Fri, 2003-01-17 at 11:54, Steve Crawford wrote:
> Once you have a function there should be no need to do an update on a table.
> Just query using the function whenever you need the next birthday.
>
> Here's a quick hack. It works on my birthday. Birthdays in the future give
> unpredictable results. In other words, play with it as a starting point but
> don't rely on it until proven good ('' is two single quotes not one double
> quote though I think the double-quote version will work as well):
>
> create function nextbday(date) returns date as
> 'select ($1 + (''1 year'' + date_trunc(''year'',age(now(),$1))))::date'
> language sql;
>
> Cheers,
> Steve
>
>
> On Thursday 16 January 2003 3:30 pm, simran wrote:
> > I wanted to create a store procedure (unless there is a easier way) so
> > that i get a person's next birthday date given their birthday.
> >
> > Aka, in one of my queries i am trying to do:
> >
> >
> > psql> update users set next_birthday=get_next_birthday(dob) where
> > user_id='42';
> >
> > except, i don't quite know how to go about creating the
> > get_next_birthday function.
> > 'dob' is a field in users that is of type 'date'.
> >
> > I have tried the following method:
> >
> > psql> update users set next_birthday=dob + (date_part('year', timestamp
> > 'now') - date_part('year', dob))*365.25;
> >
> > Which works wonderfully, except when when i if the date of birth is say
> > '1973-01-10' - in this case, it will put
> > the next_birthday as 2003-01-10 as it always converts the year to the
> > current year, not realising that the date/month
> > have already passed for this year.
> >
> > Any help would be appreciated,
> >
> > kind regards,
> >
> > simran.
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Hi Everybody,

I need help on how access a created database from a user other than the
postgres user.
Thanks for your help.

Rosta


************************************
Rosta Farzan
Laboratory for Adaptive Hypermedia and Assistive Technologies
Department of Math and Computer Science CSU Hayward
rosta@acc.csuhayward.edu
(510) 885-4026
*************************************