Hi, everyone. I'm working with a client using PostgreSQL 8.3 on a Windows
system, trying to improve performance of their database.
They have a PL/PgSQL function which takes three parameters -- a filter (a
custom type describing a user's query), an offset, and a limit. The query
that runs the filter is fairly heavy, taking 5-8 seconds to execute.
The way that they're currently doing things, they execute the query with
an offset and limit (passed from the function's parameters into the SQL
query) for each page. So first they execute the query with offset 0 and
limit 20, then with offset 20 limit 20, and so forth. Not surprisingly,
this means that this is frustrating for users, who want to scroll through
pages of data, but have to wait for the query to execute each time.
I saw this, and immediately thought, "Aha, we'll replace this with a
cursor." And indeed, in my manual tests, the cursor dramatically improved
the speed of things. (Yay!)
The rub is that we can't rip apart the application right now, and it
relies very heavily on a number of PL/PgSQL function. Our thought was
that perhaps we could rewrite things such that we have two functions: One
that opens a cursor for the query, and a second that retrieves the rows
(with an offset and limit) from the cursor.
We have no problems writing a function that returns a cursor. We also
have no problems writing a function that uses a cursor that it has opened.
My question is whether I can write a function that returns an open
cursor, and then write a second function that uses that open cursor to
retrieve some rows.
In other words, the following works just great:
CREATE OR REPLACE FUNCTION get_me_refcursor() RETURNS refcursor AS $$
DECLARE
ref refcursor;
BEGIN
open ref for select * from test_table;
return ref;
END $$ language plpgsql;
What I would like is something like the following, assuming it's possible:
CREATE OR REPLACE FUNCTION fetch_from_refcursor(ref refcursor) RETURNS
SETOF test_table AS $$
BEGIN
RETURN FETCH 1 FROM ref; -- Does not work, but can it?
END $$ language plpgsql;
Is it possible to do such a thing? I have a feeling that it isn't, but
I'd love to be proven wrong.
Thanks in advance,
Reuven
--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner