Re: cursors as table sources - Mailing list pgsql-general

[Please copy the mailing list on replies.  I'm forwarding your
entire message to the list without comment so others can see it;
I'll look at it when I get a chance.]

On Thu, Jan 12, 2006 at 04:21:04PM +0200, Peter Filipov wrote:
> It is the second case.
>
> I find cursors as good way to pass a result set from function to function.
> Because a function should not be burdened with the knowledge how its caller
> obtained the values that he is passing to her as arguments. Here is my
> case:
>
>     loop
>
>         css:=ces + '1 second'::interval;
>         ces:=tperiod_end(cpp,css);
>         perform cursor_rewind(pp);
>
>         select
>             css                         as
>             stime,
>             case
>                 when allp.tpri>apr.tpri then
>                 tperiod_condend((tperiod.*)::tperiod,css)
>                 else ces -- handles last 2 'or's
>             end                          as
>             etime,
>             (tperiod.*)::tperiod                 as
>             newcp,
>             (allp.*)::tperiod_pentry             as
>             aper
>         from
>             curs2set(pp,wd) as allp(id int, tpri int, tp int),
>             aperiod,
>             tperiod
>         where
>
>             allp.tp=aperiod.id and
>             aperiod.id=tperiod.tid and
>             tperiod.id<>cpp.id and
>             (
>                  (
>                 allp.tpri>apr.tpri and
>                 tperiod_condend((tperiod.*)::tperiod,css)<ces
>
>                 ) or
>                  (
>                 allp.tpri<apr.tpri and
>                 tperiod_stampin((tperiod.*)::tperiod,tperiod_tstampexplode(ces))
>                  ) or
>                  (
>                 tperiod_condend((tperiod.*)::tperiod,css)=ces
>                  )
>             )
>         order by
>             case
>                 when allp.tpri>apr.tpri then
>                 tperiod_condend((tperiod.*)::tperiod,css)
>                 else ces -- handles last 2 'or's
>             end asc,
>             allp.tpri desc
>         limit 1
>         into cmp;
>
>         mp:=found;
>         if mp then
>             css:=cmp.stime;
>             ces:=cmp.etime;
>             apr:=cmp.aper;
>             r.st:=css;
>             r.et:=ces;
>             r.csid:=apr.id;
>             r.tpid:=cpp.id;
>             -- it is important here that we give the current
>             period, not the next  !!!
>             cpp:=cmp.newcp;
>         else
>             r.st:=css;
>             r.et:=ces;
>             r.csid:=apr.id;
>             r.tpid:=cpp.id;
>         end if;
>         -- substract the total allowed length and handle current
>         period if  necesarry
>         cl:=r.et-r.st+sl;
>         r.et:=r.st+least(cl,tl)-sl;
>         tl:=tl-least(cl,tl);
>         -- return the current row
>         return next r;
>         -- check wether no more total length exists or there are no
>         more periods
>         if not mp     then    exit; end if;
>         if tl<sl    then    exit; end if;
>
>     end loop;
>
> Few notes.
> 1. Cursor rewind is plpgsql and rewinds the cursor to the begining by:
> execute 'move backward all from '||cursor_name(c);
>    I know it is bad idea but I commented few lines in 'spi.c' in order to
> make that possible
> 2. I think that: select * from table1,(fetch all from cursor1); is good
> idea but it is not possible to use it in a function.
>    If I replace curs2set(pp) with (fetch all from pp) I get errors
> 3. Of course 'pp' is function parameter
> 4. I think there is at least one advantage in allowing cursors as table
> sources: It gives you flexibility. It may bring performance
> penalties but those won't be as big as the penalty I get in my
> implementation here. It will still stay 'full scan' but will avoid copying
> here and there result sets.
>
>
> Regards,
> Peter Filipov
>
> On Wed, 11 Jan 2006 11:24:30 -0700, Michael Fuhr <mike@fuhr.org> wrote:
>
> >On Wed, Jan 11, 2006 at 04:11:18PM +0200, Peter Filipov wrote:
> >>Is the idea to use cursors as table sources good?
> >>Do you plan to implement it in the future and if you plan will it be
> >>soon?
> >
> >Do you mean the ability to use a cursor as one of the sources in
> >the FROM clause?  Something like the following non-working examples?
> >
> >  DECLARE curs CURSOR FOR SELECT * FROM table1;
> >  SELECT * FROM table2, curs;
> >
> >or
> >
> >  DECLARE curs CURSOR FOR SELECT * FROM table1;
> >  SELECT * FROM table2, (FETCH ALL FROM curs) AS s;
> >
> >As far as I know PostgreSQL doesn't allow anything like that;
> >somebody please correct me if I'm mistaken.  However, you could
> >write a set-returning function that takes a refcursor argument and
> >iterates through the cursor, returning each row, and use that
> >function in the FROM clause.  Whether that's a good idea or not is
> >something I haven't given much thought to.  Is there a reason you'd
> >want to use a cursor instead of, say, a view?
> >
> >Are you just curious or is there a problem you're trying to solve?
> >If I've misunderstood what you're asking then please elaborate.

--
Michael Fuhr