Thread: cursors as table sources

cursors as table sources

From
"Peter Filipov"
Date:
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/

Re: cursors as table sources

From
Michael Fuhr
Date:
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

Re: cursors as table sources

From
Tom Lane
Date:
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

Re: cursors as table sources

From
Will Glynn
Date:
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

Re: cursors as table sources

From
Tom Lane
Date:
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

Re: cursors as table sources

From
"Jim C. Nasby"
Date:
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

Re: cursors as table sources

From
Michael Fuhr
Date:
[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