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?

You would do well to read CASE. Hint you can't nest the SELECT. — Adrian Klaver
– Adrian Klaver, Commented Oct 9, 2021 at 20:21

Roberto Hernandez · Accepted Answer · 2021-10-10 08:18:07Z

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

edited Oct 10, 2021 at 8:18

answered Oct 9, 2021 at 20:34

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

WHOATEMYNOODLES Over a year ago

Oh I thought RETURNS TABLE would return them as separate columns, but it sent it back as one column with all the values. How would I return it so all 3 columns like in my screenshot above is returned?

Roberto Hernandez Over a year ago

I updated the post with a new demo in db<>fiddle., It does return the records in the form of a table.

Roberto Hernandez Over a year ago

@DIRTYDAVE, did you check my updated db<>fiddle ? it shows the result in the format of a table with columns as you expect. If so, I would appreciate if you upvote and accept the answer, of course if it solves your issue.

WHOATEMYNOODLES Over a year ago

Yeah just tried it and I didn't add an asterisk and only used select get_data('EUR');. It works perfectly, thanks!

Roberto Hernandez Over a year ago

@DIRTYDAVE, you are very welcome ;)

|

Erwin Brandstetter · Accepted Answer · 2021-10-10 01:26:52Z

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).

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:

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:

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();

Collectives™ on Stack Overflow

Create a function that accepts a string and returns multiple rows

2 Answers 2

7 Comments

Postgres 14 or later

Postgres 13 (or any version)

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Postgres 14 or later

Postgres 13 (or any version)

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related