function execution

[coolminded]

hi all
i have written a function in postgres. here is my code

CREATE OR REPLACE FUNCTION fn_new("varchar", float8, "varchar",  "varchar", date, "varchar")
  RETURNS "varchar" AS
'
declare 
r_temp varchar;
r_temp1 varchar;
a varchar;
 
Begin
    SELECT field1 INTO r_temp FROM tbl_a;
    IF r_temp IS NULL or r_temp='''' THEN 
            begin
              RETURN 1;
               exit;
            END;
    END IF;
 
    SELECT field2 INTO r_temp1 FROM tbl_b WHERE field3=$1;
    IF r_temp1 IS NULL OR r_temp1='''' THEN
        begin
          RETURN 2;
          exit;
        end;
     END IF;
 
    insert into tbl_c values
    ($4,$5,r_temp,$2,$3,$6,\'D\',\'Null\');
 
    insert into tbl_c values
    ($4,$5,r_temp1,$2,$3,$6,\'C\',\'Null\');
 
return a;
end'
  LANGUAGE 'plpgsql' VOLATILE;
 

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.

[michaelb]

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

IF <condition> THEN
     begin
         RETURN <something> ; 
         exit;
     end;
END IF;
 

looks strange to me. I would try this instead:

IF <condition> THEN
         RETURN <something> ; 
END IF;
 

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.