How to use CASE in postgres function to return Select statement?

Question

I am trying to write a function with an argument which is supposed to return one of two SELECTs depending on the argument of this fucntion:

CREATE FUNCTION fx(m integer)
RETURNS SETOF AS $$
BEGIN
    CASE m
        WHEN 1 then return query select * from pg_catalog.pg_roles
        WHEN 2 then return query select * from pg_catalog.pg_auth_members
    END
END
$$ LANGUAGE plpgsql;

SELECT fx(1);

Sorry I am completely new to sql functions so what am I doing wrong exactly? I am getting this error while executing:

"SQL Error [42883]: ERROR: function fx(integer) does not exist Hint: No function matchesthe given name and argument types. You might need to add explicit type casts"

Thanks beforehand!

you get "function fx does not exist" because the function was never created as returns setof as $$ is invalid to begin with. But you will need to use returns table() and specify the columns you want. You can't return a different set of columns depending on the input parameters. Additionally: each statement inside a CASE statement also needs to be terminated with a ; and the CASE needs to be ended with a END CASE; — user330315
– user330315, Commented Apr 25, 2022 at 14:58
@a_horse_with_no_name Hi! Thank you for response! But how can I specify columns if I have different tables to return depending on function argument? Can you please show me an example? — Vlad Vlad
– Vlad Vlad, Commented Apr 25, 2022 at 15:07

Belayer · Accepted Answer · 2022-04-26 00:50:45Z

How can you specify columns if I have different tables to return depending on function argument? You cannot. A function must always have the same return type and the exact return must be declared in the function definition.
You can sort-of get what you are after by having your function return a refcursor; see Cursors 43.7.3.5 (or the appropriate section for you Postgres version) Something like:

create or replace function confused_function( What_to_do integer) 
  returns refcursor
 language plpgsql
as $$
declare 
  rec  refcursor; 
begin 
    case when What_to_do  = 1 then 
              open rec for select * from t1; 
             
         when What_to_do  = 2 then
              open rec for select * from t2; 
         
        else 
             raise exception 'Invalid What_to_do parameter value specified (%)', What_to_do
                using Hint = 'Value for Parameter: What_to_do must be 1 or 2'; 
    end case; 

    return rec; 
end;
$$;

This however transfers the cursor processing to the calling routine. You would be better off writing 2 functions (one for each SQL). Let the calling routine analyze the 'parameter' and invoke to appropriate function (or just run the SQL without an intervening function).

A little development philosophy: A function should do one thing, do it well, and know everything it needs accomplish it. Your function does not know what to do; it needs to be told.

Collectives™ on Stack Overflow

How to use CASE in postgres function to return Select statement?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related