On Dec 11, 2007, at 11:15 PM, Paul Lambert wrote:
I have a function which uses execute to populate the value of a
variable based on a defined select construct.
The relevant part of the code looks like thus:
EXECUTE curr_query INTO curr_amount;
RAISE NOTICE '%',curr_amount;
IF NOT FOUND THEN
curr_amount=0;
END IF;
RAISE NOTICE '%',curr_amount;
I've added the if found to trap if nothing is returned by the
execute so that the value gets set to a default 0 rather than null.
When I call the function, the first raise notice gives me a value
that is correct based on the select it would be performing, but the
second raise notice gives me a 0, which suggests to me that
although the execute has populated the curr_amount field with
something, the IF NOT FOUND is always firing.
Am I misunderstanding what the FOUND variable can be used for -
i.e. is it not compatible with/not set by the EXECUTE command and
should therefore I just be using a test of IF curr_amount IS NOT NULL?
If the result of your execute doesn't assign any value(s) to
curr_amount it sets it to NULL. With that in mind,
IF curr_amount IS NULL THEN
curr_amount := 0;
END IF;
should do.
Erik Jones
Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
