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