Обсуждение: 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