Re: 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;

________________________________

From: Dave Page [mailto:dpage(at)vale-housing(dot)co(dot)uk]
Sent: Thu 4/20/2006 9:35 AM
To: Benjamin Krajmalnik; pgadmin-support(at)postgresql(dot)org
Subject: RE: [pgadmin-support] 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 15:02
To: pgadmin-support(at)postgresql(dot)org
Cc: Dave Page
Subject: Re: [pgadmin-support] pgAgent question


Yes.
PgAdmin/pgAgent and PostgreSQL ar running on the same box.
RUnning the stored procedure even from a remote pgAdmin at the console results in the same 16 second performance.

I may well have to take this one off to another list as pgAgent simply executes the query using libpq like pgAdmin (though it does so synchronously iirc, whereas pgAdmin runs asynchronously and displays the results as they come in).

Can you share the code in the job step(s), and the stored procedure?

Regards, Dave.