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

Gauthier, Dave wrote:
>
>
> I’ll answer my own question...
>
>
> select * from myfunc();
>
>
> (dumb, dumb, dumb....)

If it makes you feel any better, it is a common mistake :)

Joshua D. Drake

>
>
>
> -dave
>
> * From: * pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Gauthier, Dave
> *Sent:* Monday, July 09, 2007 4:07 PM
> *To:* pgsql-general(at)postgresql(dot)org
> *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
>
>
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/