The following bug has been logged online:

Bug reference: 2389
Logged by: James M Doherty
Email address: jim (AT) jdoherty (DOT) net
PostgreSQL version: 8.1
Operating system: RH9
Description: function within function return value
Details:

reate or replace function get_glaccttotals(text,integer,text,text) returns
float
as '
declare
RECORD_DATE alias for $1;
BANKID alias for $2;
BEG_GL_RANGE alias for $3;
END_GL_RANGE alias for $4;
arec record;
grec record;
brec record;
total_due numeric := 0;
total numeric := 0;
BEGIN
total_due := 0;
total := 0;
--================================================== ===
-- now for Total Cash & Due
--================================================== ===
for arec in select * from amggenledger ag
where (ag.amg_gl_nbr between BEG_GL_RANGE
and END_GL_RANGE)
loop
for grec in select * from genledger g
where g.amg_gl_nbr = arec.id
and g.bank_id = BANKID
loop

select into total sum(bbs.bbs_current_balance)
from bank_balance_sheet as bbs
where bbs.bank_id = BANKID
and grec.id = bbs.bbs_bank_acct_nbr
and date_eq(bbs.record_date,date(RECORD_DATE));

--============================================
-- the select got us the total for this invidual
-- account we not need to keep track of the total
-- so we know what to return from all accounts
--============================================
raise NOTICE ''[0]get_accttotals() -TOTAL DUE(%)
total(%)'',total_due,total;
total_due := total_due + total;
end loop; --END OF for grec in select *
end loop; --END OF for arec in select * from amggenledger ag
raise NOTICE ''[1]get_accttotals() -TOTAL DUE(%)'',total_due;
RETURN total_due;
END;
' language 'plpgsql';

The above function is called as follows:
trec.tot_value :=
get_glaccttotals(RECORD_DATE,BANKID,''A500000'','' A500299'');

The result is always null. When called on its own via: select * from
get_glaccttotals(RECORD_DATE,BANKID,''A500000'','' A500299'');

it returns the correct value:

get_glaccttotals
------------------
5234938.4
(1 row)

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

"James M Doherty" <jim (AT) jdoherty (DOT) net> writes:
It's impossible to do much with this when you have not shown us a
complete test case, but I'm wondering if your calling function is
passing parameter values that don't match anything in the
bank_balance_sheet table. That would cause the sum() to return
null and then total_due would go to null as well.

It's pretty bogus that SQL defines sum() over no rows to return
null rather than zero, but the spec is perfectly clear about it.
You might want to change sum(bbs.bbs_current_balance) to
coalesce(sum(bbs.bbs_current_balance), 0) if you need to deal with
such situations.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend