PostgreSQL函数未触发预期异常：pg_input_is_valid测试问题排查

问题描述 #

测试PostgreSQL的pg_input_is_valid与pg_input_error_info函数时未达预期：期望对值56.7899999抛出提示，说明其不符合numeric(5,2)规则，但实际未触发任何异常，函数返回结果为2（表示两个输入都验证通过）。

函数代码 #

CREATE OR REPLACE FUNCTION input_check(t int[])
RETURNS int AS $$
DECLARE
    current int; 
    ok int := 0;  
    e text;
BEGIN
    FOREACH current IN ARRAY t LOOP
        IF pg_input_is_valid(current, 'numeric(5,2)') THEN
            ok := ok + 1;
        ELSE
            SELECT message, detail
            INTO e
            FROM pg_input_error_info(current, 'numeric(5,2)');
            RAISE NOTICE 'Skipping [%] because it is not valid: %', current, e;
        END IF;
    END LOOP;

    RETURN ok;
END
$$ LANGUAGE plpgsql;

执行语句及结果 #

postgres=#  SELECT input_check(ARRAY['12.34', '56.7899999']);
-[ RECORD 1 ]--
input_check | 2

原因分析 #

1. 参数类型不匹配：函数定义的输入参数是t int[]（整数数组），但传入的是含字符串的数组ARRAY['12.34', '56.7899999']。PostgreSQL会自动将字符串转换为整数，'12.34'和'56.7899999'被截断为整数12和56，这两个值自然符合numeric(5,2)规则，导致pg_input_is_valid返回true。
2. pg_input_is_valid的验证逻辑限制：该函数仅验证输入值能否转换为目标类型，不会校验精度和刻度限制——也就是说，它只会检查输入是否是有效的numeric格式，不会判断是否符合numeric(5,2)的总位数、小数位要求。

解决办法 #

方案1：修正参数类型+手动校验精度 #

将输入参数改为text[]避免自动截断，先验证格式有效性，再手动检查精度范围：

CREATE OR REPLACE FUNCTION input_check(t text[])
RETURNS int AS $$
DECLARE
    current text; 
    ok int := 0;  
    e text;
BEGIN
    FOREACH current IN ARRAY t LOOP
        -- 先验证是否为合法numeric格式
        IF pg_input_is_valid(current, 'numeric') THEN
            -- 检查是否符合numeric(5,2)的精度/刻度要求
            IF current::numeric BETWEEN -999.99 AND 999.99 THEN
                ok := ok + 1;
            ELSE
                RAISE NOTICE 'Skipping [%] because it exceeds numeric(5,2) precision/scale limit', current;
            END IF;
        ELSE
            SELECT message || ' ' || detail
            INTO e
            FROM pg_input_error_info(current, 'numeric');
            RAISE NOTICE 'Skipping [%] because it is not valid: %', current, e;
        END IF;
    END LOOP;

    RETURN ok;
END
$$ LANGUAGE plpgsql;

执行测试：

postgres=# SELECT input_check(ARRAY['12.34', '56.7899999']);
NOTICE:  Skipping [56.7899999] because it exceeds numeric(5,2) precision/scale limit
 input_check 
-------------
           1
(1 row)

方案2：利用异常捕获验证约束 #

直接尝试将输入转换为numeric(5,2)，通过捕获异常来判断是否符合规则：

CREATE OR REPLACE FUNCTION input_check(t text[])
RETURNS int AS $$
DECLARE
    current text; 
    ok int := 0;  
BEGIN
    FOREACH current IN ARRAY t LOOP
        BEGIN
            -- 强制转换为numeric(5,2)，触发系统约束检查
            PERFORM current::numeric(5,2);
            ok := ok + 1;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Skipping [%] because it is not valid: %', current, SQLERRM;
        END;
    END LOOP;

    RETURN ok;
END
$$ LANGUAGE plpgsql;

执行测试：

postgres=# SELECT input_check(ARRAY['12.34', '56.7899999']);
NOTICE:  Skipping [56.7899999] because it is not valid: numeric field overflow
DETAIL:  The magnitude of 56.79 is out of the range for type numeric(5,2).
 input_check 
-------------
           1
(1 row)

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