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