Re: Three types of functions, ala function redux.

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.

> (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?

> (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.

regards, tom lane