postgresql有条件地为每行生成值

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC
);

SELECT
    id,
    name,
    department,
    salary,
    CASE
        WHEN department = 'Sales' AND salary > 5000 THEN salary * 0.10
        WHEN department = 'IT' AND salary > 6000 THEN salary * 0.15
        ELSE 0
    END AS bonus
FROM employees;

CREATE OR REPLACE FUNCTION calculate_bonus(dept VARCHAR, sal NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    IF dept = 'Sales' AND sal > 5000 THEN
        RETURN sal * 0.10;
    ELSIF dept = 'IT' AND sal > 6000 THEN
        RETURN sal * 0.15;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT
    id,
    name,
    department,
    salary,
    calculate_bonus(department, salary) AS bonus
FROM employees;