Thread: 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
* Gauthier, Dave (dave.gauthier@intel.com) wrote:
> stdb=# select myfunc();
> ERROR: set-valued function called in context that cannot accept a set
select * from myfunc(); ?
Stephen
Attachment
I’ll answer my own question...
select * from myfunc();
(dumb, dumb, dumb....)
-dave
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
Sent: Monday, July 09, 2007 4:07 PM
To: pgsql-general@postgresql.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
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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] *On Behalf Of *Gauthier, Dave
> *Sent:* Monday, July 09, 2007 4:07 PM
> *To:* pgsql-general@postgresql.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/