Postgresql – function return multiple json rows

jsonpostgresql-9.4stored-procedures

The function below returns a single row of data, and I have not been able to modify it to return all 2 rows. Ive added a ROWS parameter at the bottom, then tried returning a SETOF, TABLE, and a few others – and all return errors. How can I return (using the date below) 3 rows, each formatted as json? Thanks!

-- drop table mytable;
create table mytable (
retailerid integer not null,
retailername character varying(200),
createdate timestamp default current_timestamp,
constraint pkretailerid primary key (retailerid)
);
insert into mytable (retailerid, retailername) values (1000, 'Johns Burgers');
insert into mytable (retailerid, retailername) values (1001, 'Joses Tex-Mex');
insert into mytable (retailerid, retailername) values (1002, 'Anns BBQ');
-- drop view myview;
create view myview as select retailerid, retailername from mytable;
-- select * from myview;

— with SETOF below returns "RETURN cannot have a parameter in function returning set"

-- drop function myfunc();
create or replace function myfunc()
  returns **SETOF** json as
$body$
 declare jrow myview%rowtype;
 begin
select retailerid,retailername into jrow from myview;
return row_to_json(jrow);
end
 $body$
  language plpgsql volatile
  cost 100;
--  select myfunc()

Best Answer

You need to also return multiple rows, not just a single value. You also don't need PL/pgSQL for this. A simple SQL function will do:

create or replace function myfunc()
  returns table (document json) 
as
$body$
  select row_to_json(myview) 
  from myview;
$body$
language sql;

returns table is the same as returns setof but you can define the column names.

You also need to put the function call into the from clause:

select * 
from myfunc();

You don't really need function for that in the first place. You could do that in the view directly:

create view myview 
as 
select row_to_json(t) as document
from (
  select retailerid, retailername
  from mytable
)t ;
Related Question