Thread: Function result using execute
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; IFNOT 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? Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company
Paul Lambert <paul.lambert@reynolds.com.au> writes: > 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; > ... which suggests to me that although the > execute has populated the curr_amount field with something, the IF NOT > FOUND is always firing. IIRC, the EXECUTE command does not change FOUND --- leastwise it's not listed as one of the plpgsql commands that do set FOUND. Do you really need an EXECUTE? If so, maybe you could restructure this using a FOR ... IN EXECUTE, or some such thing. > therefore I just be using a test of IF curr_amount IS NOT NULL? Well, that might work. Have you thought through the corner case where the query does find a row but the field's value is null? regards, tom lane
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 THENcurr_amount := 0; END IF; should do. Erik Jones Software Developer | Emma® erik@myemma.com 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
Tom Lane wrote: > Do you really need an EXECUTE? If so, maybe you could restructure this > using a FOR ... IN EXECUTE, or some such thing. I'll always only ever have a single result since the function gets passes all the fields making up the primary key of the table, so doing a for in seems like it's doing more work than is needed. I need an execute because I'm dynamically constructing an SQL statement based on the parameters passed into the function - unless there is some other way of doing it that I'm not aware of. > >> therefore I just be using a test of IF curr_amount IS NOT NULL? > > Well, that might work. Have you thought through the corner case > where the query does find a row but the field's value is null? > The field in question is marked not null in the tables schema, so unless PG lets things get past this constraing I don't believe that would be an issue. Having the test at is not null seems to be doing the job. Thanks. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds & Reynolds Company