plpgsql loop not returning value
| От | Josh Close |
|---|---|
| Тема | plpgsql loop not returning value |
| Дата | |
| Msg-id | 4a0cafe204101520566aaf3b88@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: plpgsql loop not returning value
|
| Список | 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
В списке pgsql-general по дате отправления: