Functions & Stored Procedures

Halo database supports creating custom functions, aggregate functions, and stored procedures using PL/pgSQL.

Custom Functions

-- Basic function
CREATE FUNCTION add_numbers(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

SELECT add_numbers(10, 20);

-- Function returning a record set
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;

Custom Aggregate Functions

-- Create state transition function
CREATE FUNCTION sum_sfunc(state NUMERIC, value NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN COALESCE(state, 0) + COALESCE(value, 0);
END;
$$ LANGUAGE plpgsql;

-- Create final calculation function
CREATE FUNCTION sum_ffunc(state NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN state;
END;
$$ LANGUAGE plpgsql;

-- Create aggregate function
CREATE AGGREGATE my_sum (NUMERIC) (
    SFUNC = sum_sfunc,
    STYPE = NUMERIC,
    FINALFUNC = sum_ffunc,
    INITCOND = '0'
);

Stored Procedures

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 the stored procedure
CALL transfer_funds(1, 2, 500.00);

Trigger Functions

-- Create trigger function
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;

-- Bind trigger
CREATE TRIGGER trg_log_update
AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_update();