Re: Few Queries

On Wednesday 14 Aug 2002 1:29 pm, Sugandha Shah wrote:
> Hi ,
>
> No luck  . Even with Select Into . Please if any body has faced similar
> problem and knows a solution.

You don't need select into here - the sample below deletes everything older
than one day. You should be able to adapt it to your needs. Your example had
a + rather than a - which would delete things in the future, that might have
been what you intended, or it might not.

The RAISE NOTICE line prints a useful debug value. I like to scatter these
around while I'm testing.

CREATE FUNCTION del_old_history() RETURNS int4 AS '
DECLARE  var_history_age_limit int4;  set_time    timestamp;
BEGIN   var_history_age_limit:=1;   set_time := current_date() - (var_history_age_limit::text || ''  days
'')::interval;   RAISE NOTICE ''set time = %'',set_time;   delete from history where complete_time <= set_time;
   return var_history_age_limit;
END;'
LANGUAGE 'plpgsql';

-- Richard Huxton