计算延迟退休日期函数（根据L序列O序列计算）

 传入性别、职级、生日，计算出延迟退休的日期。

CREATE OR REPLACE FUNCTION c_caqc_hr_pub_db.biphr_calculate_retirement_date(
    p_gender INT,                   -- 性别: 1-男 2-女
    p_professional_level VARCHAR,   -- 聘任专业职级(L或O开头)
    p_birthday DATE                 -- 出生日期
) 
RETURNS DATE AS $$
DECLARE
    v_category VARCHAR(20);
    v_retirement_date DATE;
BEGIN
    -- 如果任一参数为NULL，直接返回NULL
    IF p_gender IS NULL OR p_professional_level IS NULL OR p_birthday IS NULL THEN
        RETURN NULL;
    END IF;
    
    -- 检查性别必须是1或2
    IF p_gender NOT IN (1, 2) THEN
        RETURN NULL;
    END IF;
    
    -- 检查女性职级必须以L或O开头
    IF p_gender = 2 AND p_professional_level NOT LIKE 'L%' AND p_professional_level NOT LIKE 'O%' THEN
        RETURN NULL;
    END IF;
    
    -- 检查生日是否是有效日期(PostgreSQL的DATE类型已经保证有效性，这里主要防止异常)
    BEGIN
        -- 尝试使用日期，如果出错则返回NULL
        PERFORM p_birthday::text::date;
    EXCEPTION WHEN OTHERS THEN
        RETURN NULL;
    END;

    -- 根据性别和专业职级确定员工类型
    IF p_gender = 1 THEN
        v_category := '男职工';
    ELSE -- p_gender = 2
        IF p_professional_level LIKE 'L%' THEN
            v_category := '女干部';
        ELSE -- LIKE 'O%' (前面已经确保以L或O开头)
            v_category := '女员工';
        END IF;
    END IF;

    -- 计算退休日期
    SELECT 
        CASE v_category
            WHEN '男职工' THEN p_birthday + INTERVAL '60 year' 
            WHEN '女干部' THEN p_birthday + INTERVAL '55 year'
            WHEN '女员工' THEN p_birthday + INTERVAL '50 year'
        END + 
        CASE v_category
            WHEN '男职工' THEN make_interval(0, LEAST(36, 1+(EXTRACT(year FROM age(p_birthday,'1965-01-01'::date))*12 + EXTRACT(month FROM age(p_birthday,'1965-01-01'::date)))::int/4))
            WHEN '女干部' THEN make_interval(0, LEAST(36, 1+(EXTRACT(year FROM age(p_birthday,'1970-01-01'::date))*12 + EXTRACT(month FROM age(p_birthday,'1970-01-01'::date)))::int/4))
            WHEN '女员工' THEN make_interval(0, LEAST(50, 1+(EXTRACT(year FROM age(p_birthday,'1975-01-01'::date))*12 + EXTRACT(month FROM age(p_birthday,'1975-01-01'::date)))::int/2))
        END
    INTO v_retirement_date;

    RETURN v_retirement_date;
END;
$$ LANGUAGE plpgsql;