In-Reply-To: <475F6E69.3060207@reynolds.com.au>
References: <475F6E69.3060207@reynolds.com.au>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <C85DB7B4-6E20-43A2-A8F6-D44D4C6726FF@myemma.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik@myemma.com>
Subject: Re: Function result using execute
Date: Wed, 12 Dec 2007 00:25:10 -0600
To: Paul Lambert <paul.lambert@reynolds.com.au>


On Dec 11, 2007, at 11:15 PM, Paul Lambert wrote:

> I have a function which uses execute to populate the value of a =20
> 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=3D0;
>    END IF;
>    RAISE NOTICE '%',curr_amount;
>
> I've added the if found to trap if nothing is returned by the =20
> 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 =20
> that is correct based on the select it would be performing, but the =20=

> second raise notice gives me a 0, which suggests to me that =20
> although the execute has populated the curr_amount field with =20
> something, the IF NOT FOUND is always firing.
>
> Am I misunderstanding what the FOUND variable can be used for - =20
> i.e. is it not compatible with/not set by the EXECUTE command and =20
> 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 =20
curr_amount it sets it to NULL.  With that in mind,

IF curr_amount IS NULL THEN
	curr_amount :=3D 0;
END IF;

should do.

Erik Jones

Software Developer | Emma=AE
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