RETURN cannot have a parameter in function returning set-CodePudding

I have written a function which takes parameter called id that gets integer. When function is called, it returns specific user's info that is user_id, name and password.

If specific user does not exist, function returns the last user of the table.

The function is:

create function getUser(id int) 
returns table(user_id int, name varchar, password varchar)
language plpgsql as 
$$
declare
   info users%rowtype;
begin
   select
      *
   from
     users
 into 
     info
 where 
     user_id = id;
 
 if not found then
     raise warning 'such user does not exist';
     select
         * 
     from 
         users
     into
         info
     where 
         user_id = MAX(users.user_id);
 end if;
 return info;
 end;
 $$;

but the result is:

 RETURN cannot have a parameter in function returning set
 LINE 27:     return info;
                ^
 HINT:  Use RETURN NEXT or RETURN QUERY.

users table consists of such columns and rows:

user_id | name   | password |

I don't know where i made mistake. What should I do to solve this?

CodePudding user response：

https://www.postgresql.org/docs/current/plpgsql-control-structures.html

3.6.1.1. RETURN

RETURN with an expression terminates the function and returns the value of expression to the caller. This form is used for PL/pgSQL functions that do not return a set.

43.6.1.2. RETURN NEXT and RETURN QUERY

When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing. RETURN NEXT can be used with both scalar and composite data types; with a composite result type, an entire “table” of results will be returned. RETURN QUERY appends the results of executing a query to the function's result set

your code: select * from users into info where user_id = id; can return more than 1 rows, So you should use return query

another issue is your declare variable user_id the same as identifier user_id, the parser don't know how to deal with it.
demo

CodePudding user response：

You can just declare a variable of data type users and do something like this:

create function getUser(id int) 
   returns  users
language plpgsql as 
$$
declare
  info users;
begin  
 select * from users where users.user_id=id into info;
 if info is null then
     select * from users order by user_id desc limit 1 into info;
 end if;
return info;
end;
$$;

Demo in dbfiddle