CREATE TABLE transfers_to_process(
trx_transfer_id int,
sender_country VARCHAR(3),
recipient_country VARCHAR(3),
origin_country VARCHAR(3),
destination_country VARCHAR(3),
status VARCHAR(1) DEFAULT 'N',
PRIMARY KEY (trx_transfer_id)
);
CREATE TABLE contries(
country_iso3 VARCHAR(3),
can_send VARCHAR(1),
can_receive VARCHAR(1),
PRIMARY KEY (country_iso3)
);
CREATE TABLE blacklist(
record_id INT,
country VARCHAR(3),
PRIMARY KEY (record_id)
);
INSERT INTO blacklist VALUES
(1, 'bad');
INSERT INTO contries VALUES
('out', 'Y', 'Y'),
('inn', 'Y', 'Y'),
('ams', 'Y', 'N'),
('ben', 'Y', 'Y');
INSERT INTO transfers_to_process VALUES
(1, 'inn', 'out', 'ams', 'ben'),
(2, NULL, NULL, NULL, NULL);
CREATE OR REPLACE PROCEDURE checker(transfer INT)
LANGUAGE plpgsql
AS
$$
DECLARE sender VARCHAR(3);
DECLARE recipient VARCHAR(3);
DECLARE origin VARCHAR(3);
DECLARE destination VARCHAR(3);
DECLARE send_s VARCHAR(3);
DECLARE receive_r VARCHAR(3);
BEGIN
SELECT sender_country, recipient_country, origin_country, destination_country
INTO sender, recipient, origin, destination
FROM transfers_to_process
WHERE trx_transfer_id = transfer;
SELECT can_send INTO send_s FROM contries
WHERE country_iso3 = sender;
SELECT can_receive INTO receive_r FROM contries
WHERE country_iso3 = recipient;
IF NOT(origin IN (SELECT country FROM blacklist) OR
destination IN (SELECT country FROM blacklist) OR
send_s <> 'Y' OR receive_r <> 'Y') THEN
UPDATE transfers_to_process
SET status = 'P'
WHERE trx_transfer_id = transfer;
ELSE
UPDATE transfers_to_process
SET status = 'F'
WHERE trx_transfer_id = transfer;
END IF;
END;
$$;
CALL checker(1);
SELECT * FROM transfers_to_process;
SELECT trx_transfer_id FROM transfers_to_process WHERE sender_country IS NULL;
Telegram: DB developers channel