To: Richard Albright <ralbright@insiderscore.com>
cc: pgsql-sql@postgresql.org
Subject: Re: sql wrapped plpgsql set returning function 
In-reply-to: <1177524609.17790.1.camel@MSI1036> 
References: <1177524609.17790.1.camel@MSI1036>
Comments: In-reply-to Richard Albright <ralbright@insiderscore.com>
	message dated "Wed, 25 Apr 2007 14:10:09 -0400"
Date: Wed, 25 Apr 2007 14:32:25 -0400
Message-ID: <3415.1177525945@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Richard Albright <ralbright@insiderscore.com> writes:
> I have figured out my looping issue, but am having difficulty wrapping
> my set returning plpgsql function getmovavgset with a getmovavg sql func
> when i run the following:

Hm, worksforme (see attached trivial example).  What PG version are you
using?

			regards, tom lane


regression=# create function foo() returns setof int8_tbl as $$
regression$# declare r record;
regression$# begin
regression$#   for r in select * from int8_tbl loop
regression$#     return next r;
regression$#   end loop;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from foo();
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

regression=# select foo();
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "foo" line 4 at return next
regression=# create function foowrap() returns setof int8_tbl as $$
regression$# select * from foo();
regression$# $$ language sql;
CREATE FUNCTION
regression=# select foowrap();
               foowrap
--------------------------------------
 (123,456)
 (123,4567890123456789)
 (4567890123456789,123)
 (4567890123456789,4567890123456789)
 (4567890123456789,-4567890123456789)
(5 rows)

regression=#