plpgsql loop not returning value
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
On Fri, Oct 15, 2004 at 10:56:06PM -0500, Josh Close wrote:
FOR this_rSum IN EXECUTE this_sQuery LOOP
this_iSum := this_rSum.isum;
END LOOP;
this_iTotal := this_iTotal + this_iSum;
[snip]
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.
Add some RAISE INFO statements to print variables' values at key
places. I wonder if one of your SUMs is returning NULL, causing
your addition to evaluate to NULL. If so, then perhaps you should
use COALESCE to turn those NULLs into zeros.
If I've misunderstood the problem then please clarify.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Sat, 16 Oct 2004 00:59:34 -0600, Michael Fuhr <mike@fuhr.org> wrote:
Add some RAISE INFO statements to print variables' values at key
places. I wonder if one of your SUMs is returning NULL, causing
your addition to evaluate to NULL. If so, then perhaps you should
use COALESCE to turn those NULLs into zeros.If I've misunderstood the problem then please clarify.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Thanks. That's exactly what was happening. I'm still new to postgres.
This is actually the first function I've wrote. I didn't even know
about RAISE or COALESCE. I added them both and it works great now!
Thanks.
-Josh
On Sat, Oct 16, 2004 at 09:30:32AM -0500, Josh Close wrote:
On Sat, 16 Oct 2004 00:59:34 -0600, Michael Fuhr <mike@fuhr.org> wrote:
Add some RAISE INFO statements to print variables' values at key
places. I wonder if one of your SUMs is returning NULL, causing
your addition to evaluate to NULL. If so, then perhaps you should
use COALESCE to turn those NULLs into zeros.Thanks. That's exactly what was happening. I'm still new to postgres.
Glad you got it working.
A question about your design: you appear to have a tblBatchHistory_X
table for each iId value in tblServers. Is there a reason for doing
that instead of having a single tblBatchHistory table with a foreign
key reference to tblServers?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Sat, 16 Oct 2004 10:20:35 -0600, Michael Fuhr <mike@fuhr.org> wrote:
Glad you got it working.
A question about your design: you appear to have a tblBatchHistory_X
table for each iId value in tblServers. Is there a reason for doing
that instead of having a single tblBatchHistory table with a foreign
key reference to tblServers?
I didn't write it, but it's to avoid locking. Each table is for a
different server. They are all writing at the same time, and I guess
it speeds up the inserts to have them in different tables. It makes
querying them a little bit more tricky, but it's not bad.
-Josh
Josh Close <narshe@gmail.com> writes:
I didn't write it, but it's to avoid locking. Each table is for a
different server. They are all writing at the same time, and I guess
it speeds up the inserts to have them in different tables.
Uh, not in Postgres. Perhaps you're thinking of another database system?
In fact I suspect it's slowing down your system somewhat.
--
greg
On 17 Oct 2004 01:24:27 -0400, Greg Stark <gsstark@mit.edu> wrote:
Uh, not in Postgres. Perhaps you're thinking of another database system?
In fact I suspect it's slowing down your system somewhat.--
greg
So, there is no locking taking place during inserts at all? Or updates?
Also, where would I find some more basic info on stuff like this? In
the postgres docs?
-Josh