Re: Stored Procedure Question

On Thu, 2003-01-16 at 23:09, simran wrote:
> 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.

Here's one way to do it using a PL/pgSQL function:

CREATE OR REPLACE FUNCTION get_next_birthday(DATE)
RETURNS DATE AS '
DECLARE
ts TIMESTAMP;
yr INTEGER;
mth INTEGER;
day INTEGER;
res DATE;
BEGIN
ts := $1::TIMESTAMP;
mth := EXTRACT(''month'' FROM ts);
day := EXTRACT(''day'' FROM ts);
yr := EXTRACT(''year'' FROM CURRENT_TIMESTAMP);
LOOP
ts := (yr || ''-'' || mth || ''-'' || day || '' 00:00:00'')::TIMESTAMP;
EXIT WHEN ts > CURRENT_TIMESTAMP;
yr := yr + 1;
END LOOP;
res := ts::DATE;
RETURN res;
END;
' LANGUAGE 'plpgsql';

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If anyone has material possessions and sees his
brother in need but has no pity on him, how can the
love of God be in him?"
I John 3:17