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?
Advertisement
Answer
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