Обсуждение: 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>: > 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!!! 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