Re: cursors as table sources

[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(at)fuhr(dot)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