函数与存储过程

Halo 数据库支持使用 PL/pgSQL 创建自定义函数、聚合函数和存储过程。

自定义函数

-- 基本函数
CREATE FUNCTION add_numbers(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

SELECT add_numbers(10, 20);

-- 返回记录集的函数
CREATE FUNCTION get_employees_by_salary(min_salary NUMERIC)
RETURNS TABLE(id INT, name TEXT, salary NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT e.id, e.name, e.salary
    FROM employees e
    WHERE e.salary >= min_salary
    ORDER BY e.salary DESC;
END;
$$ LANGUAGE plpgsql;

自定义聚合函数

-- 创建状态转换函数
CREATE FUNCTION sum_sfunc(state NUMERIC, value NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN COALESCE(state, 0) + COALESCE(value, 0);
END;
$$ LANGUAGE plpgsql;

-- 创建最终计算函数
CREATE FUNCTION sum_ffunc(state NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN state;
END;
$$ LANGUAGE plpgsql;

-- 创建聚合函数
CREATE AGGREGATE my_sum (NUMERIC) (
    SFUNC = sum_sfunc,
    STYPE = NUMERIC,
    FINALFUNC = sum_ffunc,
    INITCOND = '0'
);

存储过程

CREATE PROCEDURE transfer_funds(
    from_account INT,
    to_account INT,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END;
$$;

-- 调用存储过程
CALL transfer_funds(1, 2, 500.00);

触发器函数

-- 创建触发器函数
CREATE FUNCTION log_update() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(table_name, operation, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器
CREATE TRIGGER trg_log_update
AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_update();