I'm having a problem with a value coming out of a loop.
CREATE OR REPLACE FUNCTION funmessagespermintotal()
RETURNS int8 AS
'
DECLARE
this_rServer record;
this_rSum record;
this_iSum bigint;
this_iTotal bigint;
this_iMsgsPerMin bigint;
this_sQuery varchar(500);
BEGIN
this_iTotal := 0;
FOR this_rServer IN SELECT iId FROM tblServers LOOP
this_sQuery := \'
SELECT SUM( iNumSent ) AS iSum
FROM tblBatchHistory_\' || this_rServer.iid || \'
WHERE tStamp > now() - interval \'\'5 min\'\';
\';
FOR this_rSum IN EXECUTE this_sQuery LOOP
this_iSum := this_rSum.isum;
END LOOP;
this_iTotal := this_iTotal + this_iSum;
END LOOP;
this_iMsgsPerMin := this_iTotal / 5;
IF this_iMsgsPerMin IS NULL THEN
this_iMsgsPerMin := 0;
END IF;
RETURN this_iMsgsPerMin;
END;
'
LANGUAGE 'plpgsql';
If I return this_iSum or this_iTotal in the loop, I get a value. If I
return directly after the loop, the value is NULL. I can't figure out
why it's doing this. The value comes out of the inside for loop just
fine, but not the outer loop.
What's going on?
-Josh
