为何PostgreSQL中SELECT INTO未找到行时未触发NO_DATA_FOUND异常？

问题场景 #

我有一个空表stock_holdings，执行查询返回0行：

select qty,total_amount from stock_holdings where account_id=1 and ticker_cd='XYZ';

 qty | total_amount
-----+--------------
(0 rows)

执行以下PL/pgSQL代码块时，预期会触发NO_DATA_FOUND异常，但实际未触发，变量被置为NULL：

NOTICE:  before v_qty: -100
NOTICE:  before v_total_amount: -1000
NOTICE:  after v_qty: <NULL>
NOTICE:  after v_total_amount: <NULL>
DO

Query returned successfully in 70 msec.

对应的PL/pgSQL代码：

do 
    language plpgsql
$$
declare
v_qty numeric := -100;
v_total_amount numeric := -1000;
begin
    raise notice 'before v_qty: %', v_qty;
    raise notice 'before v_total_amount: %', v_total_amount;
    SELECT qty,total_amount INTO v_qty,v_total_amount
    FROM stock_holdings
    WHERE account_id = 1 AND ticker_cd = 'XYZ';
    raise notice 'after v_qty: %', v_qty;
    raise notice 'after v_total_amount: %', v_total_amount;
exception
    when NO_DATA_FOUND then
    raise notice 'No data found error: %', sqlstate;
    when others then
    raise notice 'Other error: %', sqlstate;
end;
$$

原因分析 #

PL/pgSQL中SELECT ... INTO的默认行为是：查询返回0行时触发NO_DATA_FOUND异常，返回多行时触发TOO_MANY_ROWS异常，仅返回1行时正常完成变量赋值。

你的情况未触发异常，大概率是当前会话启用了plpgsql.select_null_on_no_data配置参数。该参数从PostgreSQL 11开始引入，开启后（值为on），SELECT ... INTO在查询返回0行时不会抛出异常，而是将所有目标变量置为NULL。

验证与解决方法 #

验证参数设置 #

执行以下SQL查看当前参数值：

SHOW plpgsql.select_null_on_no_data;

如果结果为on，即可确认是该参数导致的行为变化。

恢复默认异常触发行为 #

• 会话级临时修改：

SET plpgsql.select_null_on_no_data = off;

• 全局永久修改：修改postgresql.conf文件中的plpgsql.select_null_on_no_data参数为off，重启PostgreSQL服务生效。

强制触发异常（无需修改参数） #

在SELECT ... INTO后添加STRICT关键字，强制要求查询必须返回恰好1行，否则触发对应异常：

SELECT qty,total_amount INTO STRICT v_qty,v_total_amount
FROM stock_holdings
WHERE account_id = 1 AND ticker_cd = 'XYZ';

内容的提问来源于stack exchange，提问作者akaghzi