~singpolyma/jmp-schemas

ref: a86a650e25c3728ed641d084a9027859c0947c81 jmp-schemas/deploy/notify_low_balance.sql -rw-r--r-- 762 bytes
a86a650eStephen Paul Weber Allow check_and_notify_low_balance to be called outside of trigger 3 months ago
                                                                                
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
26
27
28
29
30
31
32
33
-- Deploy jmp:notify_low_balance to pg
-- requires: transactions
-- requires: balances

BEGIN;

CREATE OR REPLACE FUNCTION check_and_notify_low_balance(
	customerid TEXT
) RETURNS VOID AS $$
	DECLARE
		bal NUMERIC;
	BEGIN
		SELECT balance INTO bal FROM balances WHERE customer_id = customerid;
		IF bal < 5 THEN
			PERFORM pg_notify('low_balance', customerid);
		END IF;
	END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_and_notify_low_balance_trigger() RETURNS TRIGGER AS $$
	BEGIN
		PERFORM check_and_notify_low_balance(NEW.customer_id);
		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_trigger();

COMMIT;