Thread: Function query efficiency and optimization
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 progressivelylayering limits, offsets, etc.
Dan Castido <dan.castido@yahoo.com> 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