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

Search Discussions

  • Pavel Stehule at Oct 13, 2010 at 9:36 am

    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
  • Merlin Moncure at Oct 13, 2010 at 12:43 pm

    On Wed, Oct 13, 2010 at 5:35 AM, Pavel Stehule 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
  • Reuven M. Lerner at Oct 14, 2010 at 9:35 am

    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
  • Merlin Moncure at Oct 14, 2010 at 2:49 pm

    On Thu, Oct 14, 2010 at 12:31 AM, Reuven M. Lerner 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
  • Reuven M. Lerner at Oct 14, 2010 at 2:33 pm
    Hi, Merlin. Thanks for the clarification and explanation; it was
    quite helpful. I'll give it a shot!

    Reuven

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 13, '10 at 9:25a
activeOct 14, '10 at 2:49p
posts6
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase