Обсуждение: 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