pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction result using execute
4+ messages / 3 participants
[nested] [flat]
* Function result using execute
@ 2007-12-12 05:15 Paul Lambert <paul.lambert@reynolds.com.au>
0 siblings, 2 replies; 4+ messages in thread
From: Paul Lambert @ 2007-12-12 05:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Function result using execute
@ 2007-12-12 06:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul Lambert <paul.lambert@reynolds.com.au>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2007-12-12 06:01 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Function result using execute
@ 2007-12-12 06:25 Erik Jones <erik@myemma.com>
parent: Paul Lambert <paul.lambert@reynolds.com.au>
1 sibling, 0 replies; 4+ messages in thread
From: Erik Jones @ 2007-12-12 06:25 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@reynolds.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Function result using execute
@ 2007-12-12 06:30 Paul Lambert <paul.lambert@reynolds.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Paul Lambert @ 2007-12-12 06:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-12-12 06:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-12 05:15 Function result using execute Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-12 06:30 ` Paul Lambert <paul.lambert@reynolds.com.au>
2007-12-12 06:25 ` Erik Jones <erik@myemma.com>
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