Example: convert a currency in result set on the fly

In this example, we look up exchange rates using a REST call and translate a price into a different currency on the fly, using the latest exchange rate.

A few things that are of interest in this example:

• the code makes a REST call to an outside service

• the result of the REST call is cached so that we only have to make the call once an hour

• the column "list_price" is recomputed using the exchange rate

The following is the code for a Postgres result set filter for table products in the tutorial database.

// Only do this if there is indeed a list_price in the result set
if (!context.packet.list_price) {
return;
}

// If we have never fetched the exchange rates, or it's time to fetch again
if (!context.filterContext.nextFetchDate ||
new Date().getTime() >= context.filterContext.nextFetchDate) {
const JavaURL = Java.type("java.net.URL");

// Note: you'll need to add your API key to the URL
const url = new JavaURL("http://api.exchangeratesapi.io/v1/latest?symbols=CAD&access_key=xxx");
let conn = url.openConnection();
conn.setRequestMethod("GET");
let status = conn.getResponseCode();
if (status === 200) {
const IOUtils = Java.type("org.apache.commons.io.IOUtils");
let json = IOUtils.toString(conn.getInputStream());
context.filterContext.fxRates = JSON.parse(json);
// We'll fetch again in an hour
context.filterContext.nextFetchDate = new Date().getTime() + (1000*60*60);
}
else {
log.error("Error retrieving exchange rates");
// Try again in 5 minutes
context.filterContext.nextFetchDate = new Date().getTime() + (1000*60*5);
}
}

// Now convert list_price to Canadian dollars
if (context.filterContext.fxRates && context.filterContext.fxRates.rates.CAD) {
context.packet.list_price = Math.round(context.packet.list_price *
context.filterContext.fxRates.rates.CAD * 100)/100;
}

The end result is that the list_price data received by the database clients is now automatically converted to Canadian dollars.