Message-ID: <475F6E69.3060207@reynolds.com.au>
Date: Wed, 12 Dec 2007 14:15:21 +0900
From: Paul Lambert <paul.lambert@reynolds.com.au>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Function result using execute
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

Cheers,
Paul.

-- 
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company