function return multiple json rows

Question

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()

... RETURNS SETOF json ...

András Váczi
– András Váczi

2017-02-02 17:01:24 +00:00
Commented Feb 2, 2017 at 17:01 — András Váczi
– András Váczi, Commented Feb 2, 2017 at 17:01

score 1 · Accepted Answer · 2017-02-02 18:27:01Z

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 ;

Stack Exchange Network

function return multiple json rows

1 Answer 1

Hot Network Questions

function return multiple json rows

1 Answer 1

Related

Hot Network Questions