1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 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;