CODEWITHSUNDEEP
c#asp.net-mvcvisual-studiopostgresqlpostgresql-9.4
cinfis
cinfis

Reputation: 345

function doesnt get the value issue postgresql 9.4

I have users table and want to check whether a user exists according to email. But when i call the function that will return username according to email, function raises exception says username is not found, while raise notice and exception are both able to show the username,. And then when i dont put this part;

IF NOT FOUND THEN
    RAISE NOTICE '%', v_username;
    RAISE EXCEPTION '% not found', v_username; 
END IF;

then it says;

control reached end of function without RETURN

and then when i call this function from visual studio then it gives an exception;

ERROR [42601] ERROR: syntax error at or near \"get_username\";\nError while executing the query

what is the problem?

CREATE OR REPLACE FUNCTION webuser.get_username(email varchar(30)) RETURNS varchar as $$
DECLARE
    v_username varchar(30); 
    v_state   varchar(1000);
    v_msg     varchar(1000);
    v_detail  varchar(1000);
    v_hint    varchar(1000);
    v_context varchar(1000);
BEGIN
        v_username := (SELECT username FROM webuser.users u WHERE  u.email = quote_ident($1));
    IF NOT FOUND THEN
        RAISE NOTICE '%', quote_ident($1);
        RAISE EXCEPTION '% not found', v_username;
    END IF;
    EXCEPTION WHEN others THEN
        GET STACKED DIAGNOSTICS
            v_state   = RETURNED_SQLSTATE,
            v_msg     = MESSAGE_TEXT,
            v_detail  = PG_EXCEPTION_DETAIL,
            v_hint    = PG_EXCEPTION_HINT,
            v_context = PG_EXCEPTION_CONTEXT;
    INSERT INTO webuser.error_table (v_state, v_msg, v_detail, v_hint, v_context) VALUES ( v_state, v_msg, v_detail, v_hint, v_context); 

RETURN v_username;
END;
$$ language PLPGSQL volatile--stable is not accepted because of error logging to error table?!
    SECURITY DEFINER
    SET search_path = webuser, pg_temp;

Upvotes: 0

Views: 126

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The correct sytaxis should be

<BODY>
RETURN v_username; -- no error return

EXCEPTION WHEN others THEN
     <BODY>
  RETURN <something>;  -- error return

Upvotes: 1

Related Questions