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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
BEGIN;
SELECT _v.register_patch('proc-c2ec-status-listener', ARRAY['0001-c2ec-schema'], NULL);
SET search_path TO c2ec;
-- to create a function, the user needs USAGE privilege on arguments and return types
CREATE OR REPLACE FUNCTION emit_withdrawal_status()
RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('w_' || encode(NEW.wopid::BYTEA, 'base64'), NEW.withdrawal_status::TEXT);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION emit_withdrawal_status
IS 'The function encodes the wopid in base64 and
sends a notification on the channel "w_{wopid}"
with the status in the payload.';
-- for creating a trigger the user must have TRIGGER pivilege on the table.
-- to execute the trigger, the user needs EXECUTE privilege on the trigger function.
CREATE OR REPLACE TRIGGER c2ec_withdrawal_created
AFTER INSERT
ON withdrawal
FOR EACH ROW
EXECUTE FUNCTION emit_withdrawal_status();
COMMENT ON TRIGGER c2ec_withdrawal_created ON withdrawal
IS 'After creation of the withdrawal entry a notification shall
be triggered using this trigger.';
CREATE OR REPLACE TRIGGER c2ec_withdrawal_changed
AFTER UPDATE OF withdrawal_status
ON withdrawal
FOR EACH ROW
WHEN (OLD.withdrawal_status IS DISTINCT FROM NEW.withdrawal_status)
EXECUTE FUNCTION emit_withdrawal_status();
COMMENT ON TRIGGER c2ec_withdrawal_changed ON withdrawal
IS 'After the update of the status (only the status is of interest)
a notification shall be triggered using this trigger.';
COMMIT;
BEGIN;
SELECT _v.register_patch('proc-c2ec-retry-listener', ARRAY['0001-c2ec-schema'], NULL);
SET search_path TO c2ec;
-- to create a function, the user needs USAGE privilege on arguments and return types
CREATE OR REPLACE FUNCTION emit_retry_notification()
RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('retry', '' || NEW.withdrawal_id);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION emit_retry_notification
IS 'The function emits the id of the withdrawal for which the last
retry timestamp was updated. This shall trigger a retry operation.
How many retries are attempted is specified and handled by the application';
-- for creating a trigger the user must have TRIGGER pivilege on the table.
-- to execute the trigger, the user needs EXECUTE privilege on the trigger function.
CREATE OR REPLACE TRIGGER c2ec_retry_notify
AFTER UPDATE OF last_retry_ts
ON withdrawal
FOR EACH ROW
EXECUTE FUNCTION emit_retry_notification();
COMMENT ON TRIGGER c2ec_retry_notify ON withdrawal
IS 'After setting the last retry timestamp on the withdrawal,
trigger the retry mechanism through the respective mechanism.';
COMMIT;
BEGIN;
SELECT _v.register_patch('proc-c2ec-payment-notification-listener', ARRAY['0001-c2ec-schema'], NULL);
SET search_path TO c2ec;
-- to create a function, the user needs USAGE privilege on arguments and return types
CREATE OR REPLACE FUNCTION emit_payment_notification()
RETURNS TRIGGER AS $$
DECLARE
provider_name TEXT;
BEGIN
SELECT p.name INTO provider_name FROM c2ec.provider AS p
LEFT JOIN c2ec.terminal AS t
ON t.provider_id = p.provider_id
LEFT JOIN c2ec.withdrawal AS w
ON t.terminal_id = w.terminal_id
WHERE w.withdrawal_id = NEW.withdrawal_id;
PERFORM pg_notify('payment_notification',
provider_name || '|' ||
NEW.withdrawal_id || '|' ||
NEW.provider_transaction_id
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION emit_payment_notification
IS 'The function emits the name of the provider, row id of the withdrawal
and the provider_transaction_id, on the channel "payment_notification".
The format of the payload is as follows:
"{PROVIDER_NAME}|{WITHDRAWAL_ID}|{PROVIDER_TRANSACTION_ID}". The subscriber
shall decide which attestation process to use, based on the name of
the provider.';
-- for creating a trigger the user must have TRIGGER pivilege on the table.
-- to execute the trigger, the user needs EXECUTE privilege on the trigger function.
CREATE OR REPLACE TRIGGER c2ec_on_payment_notify
AFTER UPDATE OF provider_transaction_id
ON withdrawal
FOR EACH ROW
WHEN (NEW.provider_transaction_id IS NOT NULL)
EXECUTE FUNCTION emit_payment_notification();
COMMENT ON TRIGGER c2ec_on_payment_notify ON withdrawal
IS 'After setting the provider transaction id following a payment notification,
trigger the emit to the respective channel.';
COMMIT;
|
