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.Elphick@lfix.co.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