Skip site navigation (1) Skip section navigation (2)

BUG #2389: function within function return value

From: "James M Doherty" <jim(at)jdoherty(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2389: function within function return value
Date: 2006-04-11 19:32:10
Message-ID: 200604111932.k3BJWAu8003428@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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)

Responses

pgsql-bugs by date

Next:From: Martin MarquesDate: 2006-04-11 19:34:43
Subject: BUG in logs
Previous:From: Tom LaneDate: 2006-04-11 19:19:53
Subject: Re: right sibling is not next child

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group