Stored Procedure Question

From: simran <simran(dot)list(at)leonardchristian(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Stored Procedure Question
Date: 2003-01-16 23:09:35
Message-ID: 1042758575.3226.89.camel@pingu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi All,

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message simran 2003-01-16 23:30:35 Stored Procedure Question
Previous Message Tom Lane 2003-01-16 21:13:20 Re: VACUUM ANALYSE...