函数没有得到值问题postgresql 9.4

本文关键字：问题 postgresql 函数 | 更新日期: 2023-09-27 18:15:40

我有一个users表，想根据email查看一个用户是否存在。但是当我调用将根据电子邮件返回用户名的函数时，函数引发异常说没有找到用户名，而raise notice和exception都能够显示用户名。然后当我不放这部分的时候;

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

然后它说;

control reached end of function without RETURN

然后当我从visual studio调用这个函数时它会给出一个异常;

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

有什么问题吗?

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;

函数没有得到值问题postgresql 9.4

正确的系统应该是

<BODY>
RETURN v_username; -- no error return
EXCEPTION WHEN others THEN
     <BODY>
  RETURN <something>;  -- error return
相关文章：