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

function execution

100+
P: 138
hi all
i have written a function in postgres. here is my code

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION fn_new("varchar", float8, "varchar",  "varchar", date, "varchar")
  2.   RETURNS "varchar" AS
  3. '
  4. declare 
  5. r_temp varchar;
  6. r_temp1 varchar;
  7. a varchar;
  8.  
  9. Begin
  10.     SELECT field1 INTO r_temp FROM tbl_a;
  11.     IF r_temp IS NULL or r_temp='''' THEN 
  12.             begin
  13.               RETURN 1;
  14.                exit;
  15.             END;
  16.     END IF;
  17.  
  18.     SELECT field2 INTO r_temp1 FROM tbl_b WHERE field3=$1;
  19.     IF r_temp1 IS NULL OR r_temp1='''' THEN
  20.         begin
  21.           RETURN 2;
  22.           exit;
  23.         end;
  24.      END IF;
  25.  
  26.     insert into tbl_c values
  27.     ($4,$5,r_temp,$2,$3,$6,\'D\',\'Null\');
  28.  
  29.     insert into tbl_c values
  30.     ($4,$5,r_temp1,$2,$3,$6,\'C\',\'Null\');
  31.  
  32. return a;
  33. end'
  34.   LANGUAGE 'plpgsql' VOLATILE;
  35.  
this function runs fine when r_temp and r_temp1 is not null. if either one of them is null then it should return the respective value and exit the function. but my problem is the function continues even it has to exit. how to exit the query when the condition is satisfied and exit the function too. if r_temp is null, then it should exit the function, it should not run the next query for r_temp1. but now it is running for both the queries.

i need help
plz anyone can help me. any help is appreciated.
Jun 6 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 534
There are few things in this code that don't look right to me.

Your function is supposed to return a varchar, but in two cases it returns an integer, and in one case it returns uninitialized varchar, most likely a NULL.
Even if Postgres does some kind of implicit casting for you, you need to clean it up and make it return something explicitly compliant with the declaration.

Line 10 in your code reads:
SELECT field1 INTO r_temp FROM tbl_a;
Unlike what I see in line 18 there's no WHERE clause here.
I suppose what you get in r_temp is the value of the LAST NON NULL field1 from the entire result set.
Is this really what you had in mind?

The whole construct
Expand|Select|Wrap|Line Numbers
  1. IF <condition> THEN
  2.      begin
  3.          RETURN <something> ; 
  4.          exit;
  5.      end;
  6. END IF;
  7.  
looks strange to me. I would try this instead:

Expand|Select|Wrap|Line Numbers
  1. IF <condition> THEN
  2.          RETURN <something> ; 
  3. END IF;
  4.  
Also keep in mind that as it's written your function would not distinguish between getting a NULL or empty value in field1 or field2, and not getting any results because there are no records where field3 matches the first argument passed to the function.

Finally I don't believe you need to include type varchar in double quotes.
Jun 10 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.