Обсуждение: Problem with function
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.
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';
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 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.
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';
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
"Frank Millman" <frank@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
>>
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" <frank@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
>
>
>
--