How to return a custom record using return next

Your function should be declared as returns setof emprecord as you are returning multiple rows.

But the whole example can be simplified by using returns table – then you don’t need the extra “return type”. You also don’t need PL/pgSQL for this. A (usually more efficient) language sql function is enough:

CREATE OR REPLACE FUNCTION generateemprecord()
   RETURNS table(name text, new_rank int)
as $$
  SELECT name, rank
  FROM employees;   
$$ 
language sql;

Online example