(More) Questions about stored procedures

Поиск
Список
Период
Сортировка
От lmanorders
Тема (More) Questions about stored procedures
Дата
Msg-id BCF383F8C2CD46FDADE2F542CB09BFE7@LynnPC
обсуждение исходный текст
Ответ на Re: Problems with stored procedure  (Gavin Flower <GavinFlower@archidevsys.co.nz>)
Ответы Re: (More) Questions about stored procedures  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-novice
I'm still experimenting with stored procedures and I have a couple of
questions that I can't seem to find answers to. Here is the function

CREATE OR REPLACE FUNCTION getdetailamts ( acct_no char(22), beg_moyr
char(6),
       end_moyr char(6), OUT beg_bal float8, OUT half_bal float8) AS $$
DECLARE recs record;
BEGIN
   beg_bal := 0.0;        -- if this is left out the later addition doesn't
occur
   half_bal := 0.0;
   FOR recs IN SELECT apmoyr, drtype, dramt FROM detrec  WHERE
      detrec.acctno = $1 AND detrec.apmoyr >= $2 AND detrec.apmoyr <= $3"
   LOOP
      beg_bal := beg_bal + recs.dramt;
   END LOOP;
END; $$ language plpgsql IMMUTABLE STRICT;

My first question is that if I don't initially set beg_bal := 0.0, the
addition in the loop doesn't seem to work. It returns NULL. I don't see
anything in the documentation that explains this behavior. I assume it has
something to do with beg_bal's initial value being NULL?

I change the loop part to:

LOOP
  IF recs.apmoyr < $3 THEN beg_bal := beg_bal + recs.dramt;
END LOOP;

Now I get an error when I attempt to create the function. Why?

Thanks, Lynn




В списке pgsql-novice по дате отправления:

Предыдущее
От: Gavin Flower
Дата:
Сообщение: Re: Problems with stored procedure
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: (More) Questions about stored procedures