By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

plpgsql loop not returning value

P: n/a
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

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

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

Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
On Sat, 16 Oct 2004 00:59:34 -0600, Michael Fuhr <mi**@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

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

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

Nov 23 '05 #3

P: n/a
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.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/

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

Nov 23 '05 #4

P: n/a
On Sat, 16 Oct 2004 10:20:35 -0600, Michael Fuhr <mi**@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

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

Nov 23 '05 #5

P: n/a
Josh Close <na****@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
---------------------------(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

Nov 23 '05 #6

P: n/a
On 17 Oct 2004 01:24:27 -0400, Greg Stark <gs*****@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

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

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.