plpgsql loop not returning value

From: Josh Close <narshe(at)gmail(dot)com>
To: POSTGRES <pgsql-general(at)postgresql(dot)org>
Subject: plpgsql loop not returning value
Date: 2004-10-16 03:56:06
Message-ID: 4a0cafe204101520566aaf3b88@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Glaesemann 2004-10-16 04:13:08 Re: Has anyone tried Date/Darwen/Lorentzos's model for temporal data?
Previous Message snpe 2004-10-16 03:39:51 Re: pgsql cvs