cursors as table sources

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