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

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!

CodePudding user response：

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.