set returning functions.
Hi all,
How can one make a set-returning function behave like
generate_series(). If I cobble up an srf with plpgsql i have to do
select * from mysrf().
But i can do select generate_series(1,10) or select this, that,the
other,generate_series(1,5) from sometable. Essentially I would like to
be able to have my srf behave like that.
Rhys
Live Long & Prosper | Peace & Love
Hello Rhys
its not mystery
create or replace function mysrf(a integer)
returns integer
as $$
begin
for i in 1..a loop
return next i; -- <<<< important
end loop;
return;
end;
$$ language plpgsql;
you can call select * from mysrf(10);
Regards
Pavel Stehule
2007/9/21, Rhys Stewart <rhys.stewart@gmail.com>:
Show quoted text
Hi all,
How can one make a set-returning function behave like
generate_series(). If I cobble up an srf with plpgsql i have to do
select * from mysrf().
But i can do select generate_series(1,10) or select this, that,the
other,generate_series(1,5) from sometable. Essentially I would like to
be able to have my srf behave like that.Rhys
Live Long & Prosper | Peace & Love---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:
select myfunc(10);
yes indeed. thats exactly it scott!!!
Show quoted text
On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:select myfunc(10);
2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:select myfunc(10);
Then you have to use sql language
create or replace function mysrf(int)
returns setof integer as $$
select i from generate_series(1, $1) g(i);
$$ language sql;
Regards
Pavel Stehule
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:select myfunc(10);
Then you have to use sql language
create or replace function mysrf(int)
returns setof integer as $$
select i from generate_series(1, $1) g(i);
$$ language sql;
Do you HAVE to use sql plsql to get this to work? I thought that all
pl languages worked like this with pgsql.
2007/9/25, Scott Marlowe <scott.marlowe@gmail.com>:
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:select myfunc(10);
Then you have to use sql language
create or replace function mysrf(int)
returns setof integer as $$
select i from generate_series(1, $1) g(i);
$$ language sql;Do you HAVE to use sql plsql to get this to work? I thought that all
pl languages worked like this with pgsql.
if you can call SRF function in normal context (like SELECT srf()),
you have to use SQL language
Pavel
On 9/25/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2007/9/25, Scott Marlowe <scott.marlowe@gmail.com>:
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2007/9/25, Rhys Stewart <rhys.stewart@gmail.com>:
yes indeed. thats exactly it scott!!!
On 9/24/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On 9/24/07, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello Rhys
its not mystery
Yeah, but I think he wanted to be able to make his own function he
could call like:select myfunc(10);
Then you have to use sql language
create or replace function mysrf(int)
returns setof integer as $$
select i from generate_series(1, $1) g(i);
$$ language sql;Do you HAVE to use sql plsql to get this to work? I thought that all
pl languages worked like this with pgsql.if you can call SRF function in normal context (like SELECT srf()),
you have to use SQL language
You can also do this in a C function also. you can always wrap your
plpgsql function in an sql function and sneak around the problem.
That said, it would be extremely nice if pl/pgsql functions could work
this way.
merlin