How to return a custom record using return next

When I try to return a table, my results are showing up like this

generateClassRecord
--------------------
(james, 5)
(bob, 10)

rather than like this

+-------+----------+
| name  | new_rank |
+-------+----------+
| james |        5 |
| bob   |       10 |
| cole  |       54 |
+-------+----------+

I am assuming I am not correctly returning the rows properly. Could someone advice me how I can return my data. I tried using return query but when I evoke my function it tells me the types do not match.

CREATE TABLE employees (
    id integer primary key
    name text,
    rank integer
);

create type empRecord as (
    name text,
    new_rank integer
);

INSERT INTO employees (id, name, rank) VALUES (1, 'james', 5), (2, 'bob', 10), (3, 'Cole', '54');

CREATE OR REPLACE FUNCTION
   generateEmpRecord()
   RETURNS empRecord
as $$

DECLARE
    r empRecord;
BEGIN
    FOR r IN
        SELECT name, rank
        FROM employees;   
    LOOP
        return next r;
    END LOOP;
    
END;

$$ language plpgsql

Answer

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