在postgresql中创建函数时如何使用案例

Question

我需要在我的年龄计算函数中使用条件，但我如何实现"案例何时"来比较这个人的年龄是"转弯"还是"转身"？

这是我不计算"转弯"年龄的函数;

CREATE OR REPLACE FUNCTION agecalculator(i date) RETURNS integer AS $$
    BEGIN
            RETURN EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM i);
    END;
$$ LANGUAGE plpgsql;

这就是我想让它检查一个人的 Bday 是否通过的工作;

CREATE OR REPLACE FUNCTION agecalculator(i date) RETURNS integer AS $$
    DECLARE age integer;
    BEGIN
            age = EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM i) - 1;
            SET age = CASE
            WHEN now_month = i_month AND now_day <= i_day
            THEN age = age + 1
            WHEN now_month < i_month
            THEN age = age + 1
            END;
            RETURN age;
    END;
$$ LANGUAGE plpgsql;

为什么SQL如此困难且不可期待？提前谢谢你！

Answer 1

试试这个

CREATE OR REPLACE FUNCTION agecalculator(i date) RETURNS integer AS $$
    DECLARE  age integer;
            now_month integer;
            now_day integer;
            i_month integer;
            i_day integer;
    BEGIN
            now_month := date_part('month',now());
            now_day := date_part('day',now());
            i_month := date_part('month',i);
            i_day := date_part('day',i);
            age = EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM i) - 1;
            age := CASE WHEN now_month = i_month AND now_day <= i_day
                        THEN age + 1
                        WHEN now_month < i_month
                        THEN age + 1
                   END;
            RETURN age;
    END;
$$ LANGUAGE plpgsql;