From: | "lmanorders" <lmanorders(at)gmail(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | (More) Questions about stored procedures |
Date: | 2012-08-23 21:08:18 |
Message-ID: | BCF383F8C2CD46FDADE2F542CB09BFE7@LynnPC |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2012-08-23 21:13:28 | Re: (More) Questions about stored procedures |
Previous Message | Gavin Flower | 2012-08-22 23:03:03 | Re: Problems with stored procedure |