~singpolyma/jmp-schemas

ref: 8e704d587f92885c87b2e45675f8bdfce0fb3d7a jmp-schemas/deploy/notify_possible_renewal_on_expired_top_up.sql -rw-r--r-- 724 bytes
8e704d58Stephen Paul Weber Maybe renew a customer when they top up 2 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
-- Deploy jmp:notify_possible_renewal_on_expired_top_up to pg
-- requires: transactions
-- requires: customer_plans

BEGIN;

CREATE OR REPLACE FUNCTION check_and_notify_possible_renewal_on_expired_top_up_trigger() RETURNS TRIGGER AS $$
	DECLARE
		expired BOOLEAN;
	BEGIN
		SELECT expires_at < LOCALTIMESTAMP INTO expired
		FROM customer_plans WHERE customer_id=NEW.customer_id;

		IF expired THEN
			PERFORM pg_notify('possible_renewal', NEW.customer_id);
		END IF;

		RETURN NEW;
	END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_possible_renewal_on_expired_top_up
	AFTER INSERT ON transactions
	FOR EACH ROW
	WHEN (NEW.amount > 0)
	EXECUTE PROCEDURE check_and_notify_possible_renewal_on_expired_top_up_trigger();

COMMIT;