create or replace procedure w2wtransfer(source varchar, destination varchar, amount int8,
                                        description varchar(255), INOUT txid varchar) AS
$$
DECLARE
    sourceWallet      RECORD;
    destinationWallet RECORD;
BEGIN
    -- Query the source wallet
    SELECT * into sourceWallet FROM wallets WHERE pan = source;
    if not FOUND then
        RAISE EXCEPTION 'Invalid source wallet provided: %', source USING HINT = '9000';
    end if;

    -- Query the destination wallet
    SELECT * into destinationWallet FROM wallets WHERE pan = destination;
    if not FOUND then
        RAISE EXCEPTION 'Invalid destination wallet provided: %', destination USING HINT = '9001';
    end if;

    if sourceWallet.balance < amount then
        RAISE EXCEPTION 'Source wallet has insufficient funds: %', source USING HINT = '8000';
    end if;

    txid = concat(sourceWallet.pan, ' (', sourceWallet.balance, ')');
END;
$$ LANGUAGE plpgsql;



create or replace function transfer(source varchar, destination varchar, amount int8, description varchar) returns varchar
    LANGUAGE plpgsql
AS
$$
DECLARE
    txid varchar;
BEGIN
    CALL w2wtransfer(source, destination, amount, destination, txid);
    return txid;
END
$$;

SELECT transfer('RPS427470342427770', 'RPS188070575873036', 500, 'This is a test');