returning set of records in stored procedure

Question

I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:

create or replace function sqlProc () returns setof integer as $$                                                                                                                                              
  select * from foo;
$$ language sql;


create or replace function plpgSqlProc () returns setof integer as $$
declare
  c integer;
begin
  for c in select * from foo loop
    return next c;
end loop;

end;
$$ language plpgsql;

The pure SQL version has 2x better time performance than the pl/pgsql one! Unfortunately I have other logic that cannot be expressed in pure SQL so I am wondering how I should write my stored procedure?

Answer 1

There are a few ways of doing this in PostgreSQL depending on the versions you have to support. The simplest is:

create or replace function plpgSqlProc () returns setof integer as $$
BEGIN

RETURN QUERY select * from foo;
END;
$$ LANGUAGE PLPGSQL;

This being said, I find where you don't need helper procedural code, plain SQL functions are usually better. They are usually fast and sometimes they are even planner-transparent.