Create a function that accepts a string and returns multiple rows

Question

I'm being required to create a function that transforms a single column's value based on the user's input. I need some help on the syntax for doing so.

Here is the query I'm currently performing to get the rows:

SELECT payment_id, rental_id, amount FROM payment

some pseudocode on what I'm trying to do:

function getReport(String currencyType){
    if(currencyType == 'EUR'){

       Multiply the values in the amounts column by 1.16 and append Euros to it
       Return all the rows in the table

    }else if(currencyType == 'RMB'){

       Multiple the values in the amounts column by 6.44 and append RMB to it
       Return all the rows in the table

    }else{

       Do nothing because the default column values are in USD
       Return all the rows in the table

    }
}

I've been trying to create one but I'm struggling with the syntax.
Does not work:

CREATE OR REPLACE FUNCTION get_data(currency_type text) RETURNS TABLE payment_info AS $$
    CASE currency_type
    WHEN 'EUR' THEN
        SELECT payment_id, rental_id, amount * 1.16 FROM payment;
    WHEN 'RMB' THEN
        SELECT payment_id, rental_id, amount * 6.44 FROM payment;
    WHEN 'USD' THEN
        SELECT payment_id, rental_id, amount FROM payment;
$$ LANGUAGE SQL;

Could someone please help me with the syntax to creating this function?

Answer 1

Something like this

CREATE OR REPLACE FUNCTION get_data(currency_type text) 
RETURNS TABLE  ( payment_id int, rental_id int, amount numeric(5,2) ) 
language plpgsql
as $$
begin 
   return query 
     SELECT b.payment_id, b.rental_id, 
    case 
        when currency_type = 'EUR' then b.amount * 1.16     
        when currency_type = 'RMB' then b.amount * 6.44 
        when currency_type = 'USD' then b.amount 
    end as amount 
    FROM payment b;
end;$$

It does return in the form of a table if you use

select * from get_data('EUR');

Here a demo

demo in db<>fiddle