Postgres捕获select into异常时如何获取出错行的ocid字段值

问题原因 #

你当前的写法存在两个核心问题：

1. 整条SELECT语句是原子执行的，触发22008错误的时机是PostgreSQL在执行cte.date < timestamp '2018-01-01'条件的隐式日期格式转换时，此时查询还未完成结果返回，rec变量尚未被赋值，所以异常块中打印rec自然是空值。
2. SELECT INTO要求查询结果恰好返回1行，要么你的CTE返回多行、要么查询中途报错，都会导致逻辑中断，甚至掩盖异常栈信息。

解决方法 #

核心思路是逐行遍历CTE的记录，对每一行单独做日期转换校验，每行的处理逻辑加单独的异常捕获，这样哪行转换出错就能直接拿到该行的ocid，参考代码如下：

do
$$
declare
    v_row record;
    v_test_date timestamp;
    v_state   TEXT;
    v_msg     TEXT;
begin
    -- 遍历所有CTE返回的记录，这里把你的原CTE逻辑替换到下方CTE定义中
    for v_row in with cte as (
        [...] -- 替换为你原来的CTE声明逻辑
    ) select ocid, date as raw_date from cte
    loop
        -- 对当前行的日期单独做转换校验
        begin
            v_test_date := v_row.raw_date::timestamp;
            -- 如果你原来的过滤逻辑需要保留，可以在这里加判断
            if v_test_date < timestamp '2018-01-01' then
                -- 这里可以加你原来的正常业务逻辑，不需要的话可以省略
                raise notice '合法记录 ocid: %，日期: %', v_row.ocid, v_test_date;
            end if;
        -- 捕获当前行的转换异常
        exception when others then
            get stacked diagnostics
                v_state   = returned_sqlstate,
                v_msg     = message_text;
            -- 直接打印出错行的ocid和错误信息
            raise notice '异常记录 ocid: %, 错误码: %, 错误信息: %, 原始日期值: %', 
                v_row.ocid, v_state, v_msg, v_row.raw_date;
        end;
    end loop;
end; $$;

补充说明 #

• 如果你的date字段是字符串类型存储的，还可以额外加格式校验逻辑，比如用to_date(v_row.raw_date, 'MM/DD/YYYY')指定你业务中实际的日期格式，避免隐式转换的歧义。
• 遍历过程中所有出错的记录都会逐个打印，不会因为单条记录错误中断整个遍历过程，可以一次性拿到所有有问题的ocid。

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