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;
