如何根据PostgreSQL错误信息匹配官方错误码捕获特定异常

方法步骤 #

1. 查找错误对应的PostgreSQL错误码

你可以通过以下两种方式快速获取任意错误对应的SQLSTATE：

• 在psql中执行命令调整错误输出详细程度：\set VERBOSITY verbose，之后再运行触发错误的语句select 'a'::numeric;，错误输出中会直接显示对应的错误码。
• 也可以在PL/pgSQL异常块中通过内置变量SQLSTATE打印当前错误的编码。

你遇到的「文本转numeric类型语法无效」错误对应的SQLSTATE为22P02，对应预定义异常名为invalid_text_representation。

2. 调整函数异常捕获规则

将原来的WHEN OTHERS替换为对应错误码的捕获条件即可，如果你需要更严格的过滤，只捕获numeric相关的转换错误，可以额外结合错误信息判断，避免同错误码的其他类型转换错误被误捕获：

CREATE OR REPLACE FUNCTION public.to_number(input text) RETURNS numeric
    AS $$
BEGIN
    RETURN input::numeric;
EXCEPTION
    -- 仅捕获文本转类型无效的错误
    WHEN SQLSTATE '22P02' THEN
        -- 额外校验错误是否为numeric类型转换触发，不符合则重新抛出异常
        IF SQLERRM LIKE '%type numeric%' THEN
            RETURN NULL;
        ELSE
            RAISE;
        END IF;
END
$$
    LANGUAGE plpgsql
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
;

也可以直接使用预定义的异常名代替硬编码的错误码，可读性更高：

EXCEPTION
    WHEN invalid_text_representation THEN
        -- 同上逻辑

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