[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@xxxxxxxx> 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
