Create a Function that accepts a String and returns multiple rows-CodePudding

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?

CodePudding user response：

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 payment_id, rental_id, 
    case 
        when currency_type = 'EUR' then amount * 1.16     
        when currency_type = 'RMB' then amount * 6.44 
        when currency_type = 'USD' then amount 
    end as amount
    FROM payment;
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