0

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

1 Answer 1

1

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.