-- 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;