How to get entire table data or multiple rows returned from a function in PG/PLSQL with pgAdmin 4.2.?

Question

I tried using setof and table. While creating function in pgAdmin 4.2 there is no return type called setof or table. If I create function with setof and table name as a selected return type, it only returns one row of table.

CREATE FUNCTION pgsql_returnrecords() RETURNS SETOF RECORD(name char, city, char, id integer) AS 
$BODY$
DECLARE
 rec RECORD;
BEGIN
select name,city,id INTO rec from test;
return next rec;
END;
$BODY$ language plpgsql;

I want my function to return table data with all rows and columns.

Answer 1

It's either returns setof record or returns table(....) or setof table_name With returns setof record you have to specify the column names when using the function.

You are also not returning a complete result, because you only fetch a single row, put it into the record and return that. To return a real "set" you need to use return query in PL/pgSQL. But such a function is much better written as a SQL function:

CREATE FUNCTION pgsql_returnrecords() 
  RETURNS table(name text, city text, id integer) 
AS 
$BODY$
  select name,city,id 
  from test;
$BODY$ 
language sql;

If you want to always return a complete row from the table test you can simplify that using returns setof test instead of returns table(..)

CREATE FUNCTION pgsql_returnrecords() 
  RETURNS setof test
AS 
$BODY$
  select *
  from test;
$BODY$ language sql;

Or, if you insist on PL/pgSQL:

CREATE FUNCTION pgsql_returnrecords() 
  RETURNS table(name text, city text, id integer) 
AS 
$BODY$
BEGIN
return query
  select name,city,id   
  from test;
END;
$BODY$ 
language plpgsql;

In both cases you have to use the function like a table in the FROM clause:

select *
from pgsql_returnrecords() ;