blob: 5688a6940b1dd41c2a62a24f47e78c434f69dfd5 (
plain)
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
|
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;
|
