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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
