SELECT statement or scalar in UDFs

Help Procedural SQL
1

Hello,

We are trying to create a simple function to handle currency conversion. We have a table that stores the currency conversion rates and all I wanted to do is a scalar function that takes the two currency codes and the amount and returns the converted amount like convert_currency(‘USD’, ‘EUR’, 10) and it would return the amount in EUR.

I tried using UDFs but apparently they cannot contain select statements and are meant purely for computational stuff. Since I only need the conversion rate I also tried using the scalar function with the select string as parameter to get the rate. It then creates the UDF but errors when calling it. Can anyone think of a solution?

If I mark the return type as TABLE and use a TVF it works but it is not exactly what I want to do, I need to return a scalar to use inline on the selects where I need currency conversion:

DELIMITER //
CREATE FUNCTION convert_currency(currencyFrom VARCHAR(3), currencyTo VARCHAR(3), amount DECIMAL(18,8))
RETURNS TABLE AS RETURN
    SELECT ConversionRate * amount as convertedAmount
    FROM ConversionRates 
    WHERE SourceCurrency = currencyFrom 
    AND TargetCurrency = currencyTo 
    LIMIT 1;
//
DELIMITER ;

The picture shows what I want to achieve but done in postgress:

image
image1920×1148 246 KB

2

One reason we don’t support SQL in UDFs is that it can lead to bad performance problems in large distributed environments.

There are a few options to consider:

  1. use a TFV and join on an additional column containing your desired conversion result
  2. if you are okay without having the rates up-to-the-minute, consider creating the UDF every so often, like every day, with a large literal it with your rate conversion data
  3. use an external function