-- Deploy jmp:notify_low_balance to pg
-- requires: transactions
-- requires: balances
BEGIN;
CREATE OR REPLACE FUNCTION check_and_notify_low_balance() RETURNS TRIGGER AS $$
DECLARE
bal NUMERIC;
BEGIN
SELECT balance INTO bal FROM balances WHERE customer_id = NEW.customer_id;
IF bal < 5 THEN
SELECT pg_notify('low_balance', NEW.customer_id);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER notify_low_balance
AFTER INSERT ON transactions
FOR EACH ROW
WHEN (NEW.amount < 0)
EXECUTE PROCEDURE check_and_notify_low_balance();
COMMIT;