~singpolyma/jmp-schemas

jmp-schemas/verify/invite_per_15.sql -rw-r--r-- 1.4 KiB
395bb797Stephen Paul Weber Tag for notify on top up an expired account a month 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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Verify jmp:invite_per_15 on pg

BEGIN;

DO $$
DECLARE
	settled_after_exists INTEGER;
	invite_count INTEGER;
BEGIN

SELECT count(1)
FROM information_schema.columns
WHERE table_name='transactions' AND column_name='settled_after'
INTO settled_after_exists;

IF settled_after_exists = 1 THEN
	INSERT INTO transactions
		(customer_id, transaction_id, settled_after, amount)
		VALUES ('test1', 'test1a', localtimestamp, 5);
	INSERT INTO transactions
		(customer_id, transaction_id, settled_after, amount)
		VALUES ('test1', 'test1b', localtimestamp, 15);
	INSERT INTO transactions
		(customer_id, transaction_id, settled_after, amount)
		VALUES ('test1', 'test1c', localtimestamp, 30);
	INSERT INTO transactions
		(customer_id, transaction_id, settled_after, amount)
		VALUES ('test1', 'test1d', localtimestamp, -45);
ELSE
	INSERT INTO transactions
		(customer_id, transaction_id, amount)
		VALUES ('test1', 'test1a', 5);
	INSERT INTO transactions
		(customer_id, transaction_id, amount)
		VALUES ('test1', 'test1b', 15);
	INSERT INTO transactions
		(customer_id, transaction_id, amount)
		VALUES ('test1', 'test1c', 30);
	INSERT INTO transactions
		(customer_id, transaction_id, amount)
		VALUES ('test1', 'test1d', -45);
END IF;

SELECT COUNT(1) AS c FROM invites WHERE creator_id='test1' INTO invite_count;

IF invite_count IS NULL OR invite_count <> 3 THEN
	RAISE EXCEPTION 'Expected 3 invites, got: %', invite_count;
END IF;

END $$;

ROLLBACK;