Thread: BUG #2389: function within function return value

The following bug has been logged online:

Bug reference:      2389
Logged by:          James M Doherty
Email address:      jim@jdoherty.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)

"James M Doherty" <jim@jdoherty.net> writes:
> The above function is called as follows:
> trec.tot_value   :=
> get_glaccttotals(RECORD_DATE,BANKID,''A500000'',''A500299'');
> The result is always null.

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