Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "Sugandha Shah" <Sugandhas@cybage.com>,
	"Janning Vygen" <vygen@gmx.de>
Subject: Re: Few Queries
Date: Wed, 14 Aug 2002 14:34:16 +0100
User-Agent: KMail/1.4.1
Cc: <pgsql-sql@postgresql.org>
References: <01b301c24352$12570fb0$2005a8c0@cybage.com>
	<200208140702.g7E72Cd05129@janning.planwerk6.local>
	<027601c2438e$3a6304a0$2005a8c0@cybage.com>
In-Reply-To: <027601c2438e$3a6304a0$2005a8c0@cybage.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200208141434.16778.dev@archonet.com>

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=
=20
than one day. You should be able to adapt it to your needs. Your example ha=
d=20
a + rather than a - which would delete things in the future, that might hav=
e=20
been what you intended, or it might not.

The RAISE NOTICE line prints a useful debug value. I like to scatter these=
=20
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:=3D1;
    set_time :=3D current_date() - (var_history_age_limit::text || ''  days=
=20
'')::interval;
    RAISE NOTICE ''set time =3D %'',set_time;
    delete from history where complete_time <=3D set_time;

    return var_history_age_limit;
END;'
LANGUAGE 'plpgsql';

-- Richard Huxton