Re: pgAgent question

________________________________

From: pgadmin-support-owner(at)postgresql(dot)org
[mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of Benjamin
Krajmalnik
Sent: 20 April 2006 17:53
To: pgadmin-support(at)postgresql(dot)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;

That looks straightforward enough - how quickly does it run in psql?
That uses PQexec like pgAgent, unlike pgAdmin which uses PQsendQuery
(asynchronously)?

Regards, Dave.