Re: Three types of functions, ala function redux.

Tom Lane wrote:
>
> mlw <markw(at)mohawksoft(dot)com> writes:
> > There are three basic types of SQL behaviors that should be able to be
> > performed.
>
> > (1) "function()" returns a single value. Postgres should be able to
> > understand how to optimize this to be: "select * from table where col =
> > value" where value is the datum returned by function.
>
> You get this now if the function is marked proiscachable.

Doh! RTFM!

>
> > (2) "function()" returns a number of values that are independent of the
> > query. Postgres should be able to optimize this to be: "select * from
> > table where col in (val1, val2, val3, ..valn)." I guess Postgres can
> > loop until done, using the isDone flag?
>
> I object to the notion that "scalar = set" should be automatically
> transformed into "scalar IN set". It would be nice to be smarter about
> optimizing IN operations where the subselect only returns a few rows
> into multiple indexscans, but how should the planner know that in advance?

That is sort of my point. If one marks a function as "Iscachable" and
returns an isDone as false, will postgres keep calling until all values
have been returned, and then use an index scan with the finite (cached?)
set of results?

If so, this is exactly what I need.

>
> > (3) "function()" returns a value based on the query. (This seems to be
> > how it currently functions.) where "select * from table where col =
> > function()" will end up doing a full table scan.
>
> You get this now if the function is not marked proiscachable.

A lot of my confusion has cleared, the "iscachable" flag is an
enlightenment. Boy am I schmuck. ;-}

--
http://www.mohawksoft.com