~singpolyma/jmp-schemas

ref: 240b3444466fb458b517d84c83bd6a782a21934e jmp-schemas/deploy/notify_low_balance.sql -rw-r--r-- 568 bytes
240b3444Stephen Paul Weber NOTIFY on low balance 6 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
-- 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;