By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,799 Members | 1,327 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,799 IT Pros & Developers. It's quick & easy.

Problem with function

P: n/a
Hi all

Please read the following function.

The idea is to pass it a value, and the key to another table.
If the value is not null, return the value.
If the value is null, look up a default value in the other table using the key, and return that value.

create or replace function uom(varchar(3), char) returns varchar(3) as '
declare
prod_uom alias for $1 ;
prod_class alias for $2 ;
uom varchar(3) ;
begin
if prod_uom is not null then
uom := prod_uom ;
else
uom := (select uom from prodclass where code = prod_class) ;
end if ;
return uom ;
end;'
language 'plpgsql';

If the original value is not null, the function returns the value correctly.
If the value is null, the function returns null, even though the default value does exist on the other table.

If I rewrite the function as follows, it works correctly.
Instead of storing the result in a variable, I return it directly.

create or replace function uom(varchar(3), char) returns varchar(3) as '
declare
prod_uom alias for $1 ;
prod_class alias for $2 ;
begin
if prod_uom is not null then
return prod_uom ;
else
return (select uom from prodclass where code = prod_class) ;
end if ;
end;'
language 'plpgsql';

Could someone please explain what is wrong with the first version.

Platform is PostgreSQL 7.4.1 running on Redhat 9.

Many thanks

Frank Millman

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
"Frank Millman" <fr***@chagford.com> writes:
uom := (select uom from prodclass where code = prod_class) ;

^^^ ^^^

It's a bad idea to use plpgsql variable names that match fields of your
tables. In this case, since the variable uom starts out NULL, the
select effectively reads "(select NULL from ...)".

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #2

P: n/a
>>
uom := (select uom from prodclass where code = prod_class) ;

Now I want to know why this syntax even compiles!?
What does this mean in plpgsql and where can I find a discussionin the
documentation?

Rick
Tom Lane wrote:
"Frank Millman" <fr***@chagford.com> writes:

uom := (select uom from prodclass where code = prod_class) ;

^^^ ^^^

It's a bad idea to use plpgsql variable names that match fields of your
tables. In this case, since the variable uom starts out NULL, the
select effectively reads "(select NULL from ...)".

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match


--


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.