FAQ
Hi.

Assuming I have a function like:

CREATE FUNCTION f () RETURNS SETOF INTEGER LANGUAGE PLPgSQL AS
$$
BEGIN
RETURN QUERY SELECT * FROM t;
END;
$$;

And I call:

SELECT * FROM f LIMIT 10;

Is Postgres smart enough to ensure that the function won't do a full scan of the table and then discard all but ten results?

I'm trying to work out if i can structure things so that i can reuse most of the functions for different queries by progressively layering limits, offsets, etc.

Search Discussions

  • Tom Lane at Mar 8, 2011 at 5:50 pm

    Dan Castido writes:
    Hi.
    Assuming I have a function like:
    CREATE FUNCTION f () RETURNS SETOF INTEGER LANGUAGE PLPgSQL AS
    $$
    BEGIN
    RETURN QUERY SELECT * FROM t;
    END;
    $$;
    And I call:
    SELECT * FROM f LIMIT 10;
    Is Postgres smart enough to ensure that the function won't do a full scan of the table and then discard all but ten results?
    No, not with plpgsql. I think the equivalent construct with a
    plain-SQL-language function might behave the way you want.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 8, '11 at 5:15p
activeMar 8, '11 at 5:50p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Dan Castido: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase