Extract the date parsing into a user function

[tgranqvist]

The date parsing with substring, century detection et cetera should really be in its own UDF. Something like below. Note that this is marked immutable, so it can be used in generated columns.

CREATE OR REPLACE FUNCTION get_birthdate_from_hetu(hetu CHAR(11))
RETURNS DATE AS $$
DECLARE
    birthdate CHAR(6);
    century_indicator CHAR(1);
    birth_year INT;
    birth_month INT;
    birth_day INT;
    century INT;
BEGIN
    birthdate := SUBSTRING(hetu, 1, 6);
    century_indicator := SUBSTRING(hetu, 7, 1);
    
    birth_day := SUBSTRING(birthdate, 1, 2)::INT;
    birth_month := SUBSTRING(birthdate, 3, 2)::INT;
    birth_year := SUBSTRING(birthdate, 5, 2)::INT;

    CASE century_indicator
        WHEN '+' THEN
            century := 1800;
        WHEN '-', 'Y', 'X', 'W', 'V', 'U' THEN
            century := 1900;
        WHEN 'A', 'B', 'C', 'D', 'E', 'F' THEN
            century := 2000;
        ELSE
            RAISE EXCEPTION 'Invalid century indicator in HETU: %', century_indicator;
    END CASE;

    birth_year := century + birth_year;

    RETURN MAKE_DATE(birth_year, birth_month, birth_day);
END;
$$ LANGUAGE plpgsql IMMUTABLE;