RETURN cannot have a parameter in function returning set

Question

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?

Does this answer your question? Using %rowtype when returning in a PostgreSQL function — user18098820
– user18098820, Commented May 9, 2022 at 9:42
@a_horse_with_no_name I have used return query but still i faced error — Eugene09
– Eugene09, Commented May 9, 2022 at 10:19

jian · Accepted Answer · 2022-05-09 11:03:49Z

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

emomaliev · Accepted Answer · 2022-11-23 06:02:53Z

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 sqldaddy

Collectives™ on Stack Overflow

RETURN cannot have a parameter in function returning set

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related