RE: func() & select func()

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
> >> Currently we do not consider the presence of noncachable functions as
> >> a reason that prevents reducing a subplan to an initplan. I thought
> >> about it but didn't like the performance penalty. It seems to me that
> >> it's debatable which is the correct semantics, anyway. Arguably an
>
> > Shouldn't functions be evaluated once(per the combination of parameters)
> > at the time when a query started ?
>
> I don't think I want to buy into guaranteeing that, either.

I'm still confused and now suspicious if we could expect
unambiguous results for the queries which constain function
calls which cause strong side effect.
I think there are 2 ways.

1) Function calls with strong side effect should be inhibited
except the simple procedure call query "select func()".
Seems Oracle has a similar restriction(I don't know details
sorry).

2) Users are responsible for calling functions without strong side
effect. Optimizer could freely change the order of evaluation
and cache the funtion result.

> In the
> first place, that makes it impossible to get a random sampling of your
> data by methods like
> SELECT * FROM foo WHERE random() < 0.01;

I don't understand what we should expect for the query.
Random sampling may be useful but it doesn't necessarily mean
proper. Shouldn't we make random() an exception by adding
another attribute for it if we expect random sampling ?

BTW for the query
SELECT * FROM foo where random() < 0.01 and id < 100;

Is random() called for each row or for rows which satisfy id < 100 ?

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp