Thread: Passing refcursors between pl/pgsql functions
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
> > 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. Hello, there isn't any available statement for transformation from cursor to table. You have to iterate over FETCH statement and to use a RETURN NEXT statement. Regards Pavel Stehule > > 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 >
On Wed, Oct 13, 2010 at 5:35 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>> 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.
>
> Hello, there isn't any available statement for transformation from
> cursor to table. You have to iterate over FETCH statement and to use a
> RETURN NEXT statement.
In other words, something like this:
create or replace function test() returns setof foo as
$$
declare
r refcursor;
f foo;
i int;
begin
open r for select * from foo;
for i in 1..10
loop
fetch 1 from r into f;
exit when not found;
return next f;
end loop;
end;
$$ language plpgsql;
Having defined refcursor separately from the place it is being used
really had no bearing on the peculiarities of the 'fetch' statement.
merlin
Hi, Merlin. You wrote: > In other words, something like this: > > create or replace function test() returns setof foo as > $$ > declare > r refcursor; > f foo; > i int; > begin > open r for select * from foo; > > for i in 1..10 > loop > fetch 1 from r into f; > exit when not found; > return next f; > end loop; > end; > $$ language plpgsql; > > Having defined refcursor separately from the place it is being used > really had no bearing on the peculiarities of the 'fetch' statement. This isn't quite what I was looking for; perhaps I didn't make myself clear. I want to invoke one function, and get an open refcursor returned. That much, I know how to do. I then want to be able to call a second function, repeatedly, which will essentially perform a "fetch 20" from that open refcursor. The second function should have an input of a refcursor (already open), and should return a set of rows from the table on which it was opened. This isn't the way that I would want to do things, but my client's application structure seems to require it, at least for now. So, is there a way to do this? Reuven -- Reuven M. Lerner -- Web development, consulting, and training Mobile: +972-54-496-8405 * US phone: 847-230-9795 Skype/AIM: reuvenlerner
Hi, Merlin. Thanks for the clarification and explanation; it was quite helpful. I'll give it a shot! Reuven
On Thu, Oct 14, 2010 at 12:31 AM, Reuven M. Lerner <reuven@lerner.co.il> wrote:
>
>
> Hi, Merlin. You wrote:
>
>> In other words, something like this:
>>
>> create or replace function test() returns setof foo as
>> $$
>> declare
>> r refcursor;
>> f foo;
>> i int;
>> begin
>> open r for select * from foo;
>>
>> for i in 1..10
>> loop
>> fetch 1 from r into f;
>> exit when not found;
>> return next f;
>> end loop;
>> end;
>> $$ language plpgsql;
>>
>> Having defined refcursor separately from the place it is being used
>> really had no bearing on the peculiarities of the 'fetch' statement.
>
> This isn't quite what I was looking for; perhaps I didn't make myself clear.
>
> I want to invoke one function, and get an open refcursor returned. That
> much, I know how to do.
>
> I then want to be able to call a second function, repeatedly, which will
> essentially perform a "fetch 20" from that open refcursor. The second
> function should have an input of a refcursor (already open), and should
> return a set of rows from the table on which it was opened.
>
> This isn't the way that I would want to do things, but my client's
> application structure seems to require it, at least for now. So, is there a
> way to do this?
yes: if you review the example above, the key snippet is:
for i in 1..10
loop
fetch 1 from r into f;
exit when not found;
return next f;
end loop;
Which would make the body of your consumer function. I understand
that you need to do it in separate functions -- that part is easy and
covered via the documentation on cursors. The problem is you can't
direct the ouput of 'fetch n' into the return of a function or some
other variable, except in the special case of 'fetch 1' where we can
use a record variable. So we have to simulate 'fetch 10/20 etc' with
a loop. You can split the function above into two separate functions
and you should have what you want.
A hypothetical improvement to postgresql that would make life
easier/faster would be to allow fetch to be used in a CTE:
with rows as (fetch 20 from r) ...
So you could point it at 'return next', record array, temp table, etc.
merlin