~singpolyma/jmp-schemas

a86a650e25c3728ed641d084a9027859c0947c81 — Stephen Paul Weber a day ago 240b344 master
Allow check_and_notify_low_balance to be called outside of trigger

For use from, eg, jmp-pay
2 files changed, 13 insertions(+), 4 deletions(-)

M deploy/notify_low_balance.sql
M revert/notify_low_balance.sql
M deploy/notify_low_balance.sql => deploy/notify_low_balance.sql +12 -4
@@ 4,14 4,22 @@

BEGIN;

CREATE OR REPLACE FUNCTION check_and_notify_low_balance() RETURNS TRIGGER AS $$
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 = NEW.customer_id;
		SELECT balance INTO bal FROM balances WHERE customer_id = customerid;
		IF bal < 5 THEN
			SELECT pg_notify('low_balance', NEW.customer_id);
			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;


@@ 20,6 28,6 @@ CREATE TRIGGER notify_low_balance
	AFTER INSERT ON transactions
	FOR EACH ROW
	WHEN (NEW.amount < 0)
	EXECUTE PROCEDURE check_and_notify_low_balance();
	EXECUTE PROCEDURE check_and_notify_low_balance_trigger();

COMMIT;

M revert/notify_low_balance.sql => revert/notify_low_balance.sql +1 -0
@@ 3,6 3,7 @@
BEGIN;

DROP TRIGGER notify_low_balance ON transactions;
DROP FUNCTION IF EXISTS check_and_notify_low_balance_trigger;
DROP FUNCTION check_and_notify_low_balance;

COMMIT;