postgresql - 在 postgresql 函数的 if-else 中获取 else 子句返回查询

Question

显然我需要检查我失败的插入是否有效，以便我可以获得 else 查询作为结果。即使我在我的 varchars 中传递空字符串，我每次都能成功。

CREATE OR REPLACE FUNCTION public.ip_insert_corp_location(
    p_corp_id bigint,
    p_location_address varchar,
    p_location_name varchar)
    RETURNS TABLE(location_id bigint, status_value integer, status_msg character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
num_rows INTEGER:=0;
p_location_id bigint:=0;
BEGIN
    insert into ip_corp_locations_list(corp_id, location_address, location_name)
    values(p_corp_id, p_location_address, p_location_name)
    returning ip_corp_locations_list.location_id into p_location_id;
    
    GET DIAGNOSTICS num_rows = ROW_COUNT;
                if(num_rows>0)
                THEN
                        return query
                        select p_location_id,1::integer as status_value,'Location Inserted Successfully'::character varying as status_text;
                else
                        return query
                        select 0::bigint,0::integer as status_value,'Failed to Insert Location'::character varying as status_text;
                end if;
END;    
$BODY$;

标签： postgresql