From: Erik Jones <erik@myemma.com>
To: Paul Lambert <paul.lambert@reynolds.com.au>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Function result using execute
Date: Wed, 12 Dec 2007 00:25:10 -0600
Message-ID: <C85DB7B4-6E20-43A2-A8F6-D44D4C6726FF@myemma.com> (raw)
In-Reply-To: <475F6E69.3060207@reynolds.com.au>
References: <475F6E69.3060207@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
> 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®
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, paul.lambert@reynolds.com.au
Subject: Re: Function result using execute
In-Reply-To: <C85DB7B4-6E20-43A2-A8F6-D44D4C6726FF@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox