Thread: Re: pgAgent question

---

From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Benjamin Krajmalnik
Sent: 20 April 2006 17:53
To: pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] pgAgent question

The job has a single execution step:

 

select * from fn_calcstats2();

 

The function code follows:

 

CREATE OR REPLACE FUNCTION fn_calcstats2()
  RETURNS "timestamp" AS
$BODY$
DECLARE
   startdate timestamp;
   myrecord  Record;
   myrecord2 Record;
   mycursor refcursor;
BEGIN

 startdate := (current_date - interval '30 days')::timestamp;
 FOR myrecord in select * from tblkstests LOOP
   open mycursor for select  avg(replyval) as myavg  , stddev(replyval) as mysd from tblksraw where tblksraw.testguid = myrecord.testguid and tblksraw.testid = myrecord.testid and tblksraw.testtime >= startdate;
   fetch mycursor into myrecord2;
   update tblkstests set runningavg = myrecord2.myavg, sd = myrecord2.mysd, lcl = myrecord2.myavg - (3 * myrecord2.mysd), ucl = myrecord2.myavg + (3 * myrecord2.mysd) where tblkstests.testguid = myrecord.testguid and tblkstests.testid = myrecord.testid ;
   close mycursor;
 END LOOP;  
 
-- select startdate;
 return startdate;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;