SQL Function to Currency Convertion-CodePudding

I'm trying to create a function that will do currency conversion based on a table. The table is called ds_exchange_rate and looks like this:

As you can see the base column contains only USD and currency can be either BRL or MXN, whereas rate is the conversion value from USD to BRL/MXN for that given date.

With the objective of making my queries simpler I tried to create a function that would handle the conversions. The function statment looks like this:

CREATE OR REPLACE FUNCTION CONVERT_FROM_TO(
    value NUMERIC, 
    from_currency TEXT, 
    to_currency TEXT, 
    reference_date DATE DEFAULT CURRENT_DATE
)
    RETURN NUMERIC
    LANGUAGE plpgsql
    AS
    $$
    DECLARE
        conversion_rate NUMERIC;
    BEGIN
        IF from_currency = 'USD' THEN 
            SELECT rate
            INTO rate
            FROM ds_exchange_rate 
            WHERE currency = to_currency
            AND date = reference_date;
        
            RETURN value * conversion_rate;
        ELSIF to_currency = 'USD' THEN
            SELECT 1/rate
            INTO rate
            FROM ds_exchange_rate 
            WHERE currency = from_currency
            AND date = reference_date;
        
            RETURN value * conversion_rate;
        END IF;
    END;
    $$;

I only want USD <-> BRL/MXN conversions so don't worry about BRL <-> MXN.

When trying to create the function I got some syntax erros at or near the first return and the elsif. I believe it's something with the ";", but I wasn't able to fix it (I'm not used to create SQL functions).

If someone could help out I'd appreciate a lot!

Error Message

SQL Error [42601]: ERROR: syntax error at or near "ELSIF"
  Position: 1

CodePudding user response：

There are some errors, the reserved word for returning function is "returns" also your selects should return into your conversion_rate variable, so corrected looks like this:

CREATE OR REPLACE FUNCTION CONVERT_FROM_TO(
    value NUMERIC, 
    from_currency TEXT, 
    to_currency TEXT, 
    reference_date DATE DEFAULT CURRENT_DATE
)
    returns NUMERIC
    LANGUAGE plpgsql
    AS
    $$
    DECLARE
        conversion_rate NUMERIC;
    BEGIN
        IF from_currency = 'USD' THEN 
            SELECT rate
            INTO conversion_rate
            FROM ds_exchange_rate 
            WHERE currency = to_currency
            AND date = reference_date;        
            RETURN value * conversion_rate;
        ELSIF to_currency = 'USD' THEN
            SELECT 1/rate
            INTO conversion_rate
            FROM ds_exchange_rate 
            WHERE currency = from_currency
            AND date = reference_date;        
            RETURN value * conversion_rate;
        END IF;
    END;
    $$;