Re: returns setof rec... simple exampe doesn't work

["Gauthier, Dave" <dave.gauthier@xxxxxxxxx>]

 

I’ll answer my own question...

 

select * from myfunc();

 

 

(dumb, dumb, dumb....)

 

-dave

---

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Gauthier, Dave
Sent: Monday, July 09, 2007 4:07 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: [GENERAL] returns setof rec... simple exampe doesn't work

 

I’ve googled this one and tried everything (except the correct solution of course) until tears are starting to flow.  Please help.  Complete example below.  7.4.13 on suse-64 x86

 

 

 

create table mytable (mystring varchar, myreal real);

insert into mytable (mystring,myreal) values ('abc',1.23);

insert into mytable (mystring,myreal) values ('def',4.56);

 

create type myrec as (mystring varchar, myreal real);

 

create or replace function myfunc() returns setof myrec as '

declare

  crec myrec;

begin

 

  for crec in select * from mytable loop

    return next crec;

  end loop;

   

  return;

end;

' language 'plpgsql';

 

 

stdb=# select myfunc();

ERROR:  set-valued function called in context that cannot accept a set

CONTEXT:  PL/pgSQL function "myfunc" line 6 at return next