1 files changed, 41 insertions, 0 deletions
diff --git a/c2ec/db/proc-c2ec_transfer_listener.sql b/c2ec/db/proc-c2ec_transfer_listener.sql
new file mode 100644
index 0000000..ded172d
--- /dev/null
+++ b/c2ec/db/proc-c2ec_transfer_listener.sql
@@ -0,0 +1,41 @@
+
+BEGIN;
+
+SELECT _v.register_patch('proc-c2ec-transfer-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_transfer_notification() 
+RETURNS TRIGGER AS $$
+BEGIN
+    PERFORM pg_notify('transfer', encode(NEW.request_uid::BYTEA, 'base64'));
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+COMMENT ON FUNCTION emit_transfer_notification 
+	IS 'The function emits the request_uid of a transfer which shall trigger a transfer
+    by the receiver of the notification.';
+
+-- 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_transfer_failed 
+    AFTER INSERT
+    ON transfer
+    FOR EACH ROW    
+    EXECUTE FUNCTION emit_transfer_notification();
+COMMENT ON TRIGGER c2ec_on_transfer_failed ON transfer
+    IS 'When a new transfer is set, the transfer shall executed. This trigger aims to
+    trigger this operation at its listeners.';
+
+CREATE OR REPLACE TRIGGER c2ec_on_transfer_failed 
+    AFTER UPDATE OF retries
+    ON transfer
+    FOR EACH ROW
+    WHEN (NEW.retries > 0)
+    EXECUTE FUNCTION emit_transfer_notification();
+COMMENT ON TRIGGER c2ec_on_transfer_failed ON transfer
+    IS 'When retries is (re)set this will trigger the notification of the listening 
+    receivers, which will further process the transfer';
+
+COMMIT;
+\ No newline at end of file