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

Answer 2

Postgres 14 or later

In Postgres 14 or later, I'd suggest the new standard SQL syntax:

CREATE OR REPLACE FUNCTION get_data(_currency_type text DEFAULT 'USD')
  RETURNS TABLE (payment_id int, rental_id int, amount numeric(5,2)) 
  STABLE PARALLEL SAFE
BEGIN ATOMIC
SELECT p.payment_id, p.rental_id
     , CASE _currency_type
          WHEN 'USD' THEN p.amount
          WHEN 'EUR' THEN p.amount * 1.16
          WHEN 'RMB' THEN p.amount * 6.44
FROM   payment p;
END;

See:

• What does BEGIN ATOMIC ... END mean in a Postgresql SQL function / procedure?

Most of the below still applies ...

Postgres 13 (or any version)

CREATE OR REPLACE FUNCTION get_data(_currency_type text DEFAULT 'USD')
  RETURNS TABLE  (payment_id int, rental_id int, amount numeric(5,2)) 
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT p.payment_id, p.rental_id
     , CASE _currency_type
          WHEN 'USD' THEN p.amount
          WHEN 'EUR' THEN p.amount * 1.16
          WHEN 'RMB' THEN p.amount * 6.44 
       -- ELSE 1/0 END   -- ??? this purposely raises an exception
FROM   payment p;
$func$;

Stick with LANGUAGE sql (like in your original attempt). There is no need for LANGUAGE plpgsql for the simple function - unless you want to add a custom error message or error handling for invalid input ..

See:

• Difference between language sql and language plpgsql in PostgreSQL functions

Use a simpler switched CASE (like in your original attempt).

• Display column name with max value between several columns
• Simplify nested case when statement

Provide an explicit ELSE branch. (SQL CASE defaults to NULL if ELSE is not spelled out.)

If payment_info, featured in your original attempt, is an existing row type matching your desired return type, use a simple RETURNS SETOF payment_info instead of RETURNS TABLE(...).

It's good style to table-qualify columns with possibly ambiguous names like demonstrated. (It's never a bad idea in any case.)
But it's a requirement in a LANGUAGE plpgsql function with RETURNS TABLE ...) implicitly declaring OUT parameters of the same name as table columns. That would raise an exception like:

ERROR: column reference "payment_id" is ambiguous

See:

• INSERT INTO ... RETURNING - ambiguous column reference
• How to return result of a SELECT inside a function in PostgreSQL?

Also: numeric(5,2)? That raises an exception for amount > 999.99. Looks like a loaded foot-gun. Just use numeric or something like numeric(20,2) for the rounding effect.

About STABLE:

• How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

About PARALLEL SAFE:

• When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

Finally, as has been cleared up already, to decompose result rows, call with SELECT * FROM:

SELECT * FROM get_data('USD');

See:

• Simple PostgreSQL function to return rows
• PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

I added an default value for the input parameter: DEFAULT 'USD'. That's convenient, but totally optional. It documents what's most commonly expected and allows a short call without explicit parameter:

SELECT * FROM get_data();