postgresql - 函数不返回任何值且不引发异常

Question

下面的函数既不返回值也不引发异常。

create or replace function get_custid(p_customerNum varchar2) 
RETURNS text AS $$
DECLARE
cust_id customer.customer_num%TYPE;
begin
raise notice '%', message_text;
select customer_num into cust_id
from customer
where customer_num = p_customerNum;
return cust_id;
exception
when OTHERS then
raise notice '%', message_text;
raise;
end $$ language plpgsql;


select get_custid('Ab12345') from dual;

-- 客户编号存在但未返回任何行。

select get_custid('DDDDDDD') from dual;

-- 客户号不存在但不会异常阻塞

标签： postgresql

Answer 1

在PL/pgSQL中，如果指定，SELECT INTO则仅在错误的行数上引发异常。STRICT

create or replace function get_custid(p_customerNum varchar) 
RETURNS text AS $$
DECLARE
cust_id customer.customer_num%TYPE;
begin
raise notice '%', 'message_text';
select customer_num into strict cust_id
from customer
where customer_num = p_customerNum;
return cust_id;
exception
when OTHERS then
raise notice '%', 'message_text';
raise;
end $$ language plpgsql;