Обсуждение: set returning functions.

Поиск
Список
Период
Сортировка

set returning functions.

От
"Rhys Stewart"
Дата:
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

Re: set returning functions.

От
"Pavel Stehule"
Дата:
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
>

Re: set returning functions.

От
"Scott Marlowe"
Дата:
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);

Re: set returning functions.

От
"Rhys Stewart"
Дата:
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);
>

Re: set returning functions.

От
"Pavel Stehule"
Дата:
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

Re: set returning functions.

От
"Scott Marlowe"
Дата:
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.

Re: set returning functions.

От
"Pavel Stehule"
Дата:
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

Re: set returning functions.

От
"Merlin Moncure"
Дата:
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