plpgsql loop not returning value

[Josh Close]

I'm having a problem with a value coming out of a loop.

CREATE OR REPLACE FUNCTION funmessagesperm intotal()
RETURNS int8 AS
'
DECLARE

this_rServer record;
this_rSum record;
this_iSum bigint;
this_iTotal bigint;
this_iMsgsPerMi n 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.ii d || \'
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_iMsgsPerMi n := this_iTotal / 5;

IF this_iMsgsPerMi n IS NULL THEN
this_iMsgsPerMi n := 0;
END IF;

RETURN this_iMsgsPerMi n;

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Fuhr]

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/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Josh Close]

On Sat, 16 Oct 2004 00:59:34 -0600, Michael Fuhr <mi**@fuhr.or g> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Fuhr]

On Sat, Oct 16, 2004 at 09:30:32AM -0500, Josh Close wrote:

On Sat, 16 Oct 2004 00:59:34 -0600, Michael Fuhr <mi**@fuhr.or g> 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/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Josh Close]

On Sat, 16 Oct 2004 10:20:35 -0600, Michael Fuhr <mi**@fuhr.or g> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Greg Stark]

Josh Close <na****@gmail.c om> 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Josh Close]

On 17 Oct 2004 01:24:27 -0400, Greg Stark <gs*****@mit.ed u> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly