pg错误日期转换异常处理

问题：一些错误的日期数据，导致to_date报错
例如：

select to_date('20230230','yyyymmdd')

SQL 错误[22008]: ERROR: date/time field value out of range: "202302300"

根据日期的规则，做了简单的验证，将超出范围的转为当月最大值（前提是要保证业务可以接受）

create or replace function to_date_advanced(in_date text)
returns date as $$

declare
    out_date date;
    var_date text;

begin

    select 
        case when 
            substring(in_date,5,2) in ('01','03','05','07','08','10','12') 
            and substring(in_date,7,2)::int > 31 
        then 
            substring(in_date,1,6)||'31'
        when 
            substring(in_date,5,2) in ('04','06','09') 
            and substring(in_date,7,2)::int > 30 
        then 
            substring(in_date,1,6)||'30'
        when 
            substring(in_date,5,2) = '02' 
            and substring(in_date,1,4)::int%4 = 0
            and substring(in_date,7,2)::int > 29 
        then 
            substring(in_date,1,6)||'29'
        when 
            substring(in_date,5,2) = '02' 
            and substring(in_date,1,4)::int%4 <> 0
            and substring(in_date,7,2)::int > 28 
        then 
            substring(in_date,1,6)||'28'
        else 
            in_date
        end into var_date;
        
    select 
        to_date(case when 
            substring(var_date,5,2)::int > 12
        then
            substring(var_date,1,4)||'12'||substring(var_date,7,2)
        else
            var_date
        end,'yyyymmdd') into out_date;
        
    return out_date;
    
end; $$ language plpgsql;