473,320 Members | 1,831 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

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
2 1281
"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
>>
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Covad | last post by:
Hi all, For some reason my change() function is only called when the page loads. I'd much rather it gets called when the select changes. Here's the code: window.onload = init; function...
7
by: Emanuel Ziegler | last post by:
Hello, I want to do some mathematics with functions. In my case the function classes are very complex, but this simple example has the same problems. To allow calculations that begin with a...
117
by: Peter Olcott | last post by:
www.halting-problem.com
1
by: Mohamed Fysal | last post by:
I have written a Regular DLL with many Export Functions and one CALLBACK fun ction . The callback function declared in the .cpp file of the Regular DLL is as fol lows: typedef BOOL...
4
by: Andy_Khosravi | last post by:
Hello, I'm having a problem with the MID function within Access 97. I have been trying to build a function to check to make sure that a field on a form does not have any spaces or dashes. This...
0
by: Lucas, Todd | last post by:
Hello everyone! I'm having a problem with a WebControl that I'm designing for a Menu. I've been at it for about 3 weeks now, and can't seem to get around this problem. So I'm hoping that someone...
78
by: Josiah Manson | last post by:
I found that I was repeating the same couple of lines over and over in a function and decided to split those lines into a nested function after copying one too many minor changes all over. The only...
5
by: jbenner | last post by:
I have opened a PMR for this with IBM, and am not asking for advice from the DB2 DBA community. I am posting this as an FYI that DB2 Health Monitor, even at the latest version of DB2, still can cause...
2
by: Ravikiranreddy | last post by:
Hi every one, Am pasting the error code returned in log please help me in fixing it--- ERROR:An error occurred during the execution of the command "/opt/IBM/db2/V8.1/instance/dascrt -u...
6
by: pauldepstein | last post by:
Let double NR( double x, double(*)(const double&) f ) be the signature of a Newton-Raphson function NR. Here, f is a function which returns a double and accepts a const double&. The aim of...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.