Hello Richard,
I'm really thankful to you. It worked. Somehow I wasn't getting it to work.
This line did the trick.
set_time := current_date() - (var_history_age_limit::text || '' days
'')::interval;
Regards,
-Sugandha
----- Original Message -----
From: "Richard Huxton" <[EMAIL PROTECTED]>
To: "Sugandha Shah" <[EMAIL PROTECTED]>; "Janning Vygen" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, August 14, 2002 7:04 PM
Subject: Re: [SQL] 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
