Thread: cursors as table sources
Hi, 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? Regards, Peter Filipov -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
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
Michael Fuhr <mike@fuhr.org> writes:
> ... 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.
From a performance point of view, at least, it's practically guaranteed
not to be a good idea. By hiding part of the query from the optimizer,
the SRF would prevent any meaningful join optimization from happening.
This applies whether the SRF is reading a cursor or just executing the
query directly.
For the same reason, I can't get very excited about the idea of allowing
a cursor reference in FROM. The cursor is already planned and there
would be no opportunity to alter its plan based on the join context.
I have some recollection that the cursor-in-FROM idea has come up before
and was shot down on yet other grounds than that. Try searching the
archives ...
regards, tom lane
Michael Fuhr wrote:
> ...
>
>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.
>
I have previously thought this to be the most straightforward way to
solve certain problems, including functions that amount to
parameter-dependent views. If I want to join them against something not
anticipated in the first function, I have to either a) write another
function, copying the code in question, and adding the JOIN I want, or
b) write another function, call the first function, and execute an
astronomical number of little queries myself. Both bad options. Why
can't I SELECT FROM cursor JOIN some_table?
Similarly, but admittedly offtopic, I've also been irritated by the
ability to call scalar and set-returning functions as column expressions
(SELECT set_returning_function(t.a) FROM some_table? t) but not
multi-column functions, which can only be accessed via SELECT * FROM
multi_column_function('abc'). Why can't I SELECT
multi_column_function(t.a) FROM some_table t? The only solution I've
implemented is to write a SETOF function that encapsulates the previous
query, which adds needless complexity and is annoying when you have a
couple dozen queries you want to run. The other option is to make
multi_column_function actually return a single column in some way that
the application can split it apart again, but that's really ugly.
--Will Glynn
Freedom Healthcare
Will Glynn <wglynn@freedomhealthcare.org> writes:
> Why can't I SELECT multi_column_function(t.a) FROM some_table t?
You can. At least if you're running a recent release ;-)
regression=# create function foo(int, out f1 int, out f2 int) as $$
regression$# begin
regression$# f1 := $1 + 1;
regression$# f2 := $1 + 2;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select unique1, foo(unique1) from tenk1;
unique1 | foo
---------+-------------
8800 | (8801,8802)
1891 | (1892,1893)
3420 | (3421,3422)
9850 | (9851,9852)
7164 | (7165,7166)
...
> The other option is to make
> multi_column_function actually return a single column in some way that
> the application can split it apart again, but that's really ugly.
That takes a little more hacking, but:
regression=# select unique1,(foo).* from (select unique1, foo(unique1) from tenk1 offset 0) ss;
unique1 | f1 | f2
---------+------+------
8800 | 8801 | 8802
1891 | 1892 | 1893
3420 | 3421 | 3422
9850 | 9851 | 9852
7164 | 7165 | 7166
...
(The OFFSET hack is to ensure the query doesn't get flattened into a
form where foo() will be evaluated multiple times per row.)
regards, tom lane
On Wed, Jan 11, 2006 at 01:41:31PM -0500, Will Glynn wrote: > Michael Fuhr wrote: > > >... > > > >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. > > > > I have previously thought this to be the most straightforward way to > solve certain problems, including functions that amount to > parameter-dependent views. If I want to join them against something not > anticipated in the first function, I have to either a) write another > function, copying the code in question, and adding the JOIN I want, or > b) write another function, call the first function, and execute an > astronomical number of little queries myself. Both bad options. Why > can't I SELECT FROM cursor JOIN some_table? I'm not quite following what you're trying to do here, but there may be a more practical way if you want to post a concrete example. Or maybe Tom's reply does what you need... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
[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