Re: Stored Procedure Question

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(at)postgresql(dot)org