Re: Using functions as filters in queries

Could you please stop to send these message?


 --- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote: >
> On Mon, 10 Mar 2003, Chris Mungall wrote:
>
> > I have a problem that can be reduced to this
> equivalent but simpler
> > problem:
>
> > EXPLAIN ANALYZE select * from t where n=5 AND x
> like 'a%';
>
> 5 is a constant.
>
> > Case 2:
> >
> > Now I want to replace the "n=5" clause with a
> function:
> >
> > CREATE FUNCTION f(t, int) RETURNS bool AS
> >  'SELECT $1.n = $2'
> > LANGUAGE 'sql';
> >
> > EXPLAIN ANALYZE select * from t where f(t, 5) AND
> x like 'a%';
> >
> > Now looking at the two cases (without and with
> function) I can see that
> > they are equivalent, but Pg treats them
> differently. I guess it is failing
> > to distiguish between two cases - if the function
> has no FROM clause, then
> > it is a simple case of variable substitution into
> the original WHERE
> > clause. If it does include a FROM clause, it's not
> so simple and
> > optimisation is hard, so the function becomes the
> filter.
>
> It doesn't look at the contents of the function.  It
> looks at whether the
> function is defined IMMUTABLE, STABLE or VOLATILE.
> With a VOLATILE
> function (the default), the system is not guaranteed
> that given the
> same arguments that the result is the same.  You
> might want to read
> the description in the manpage for CREATE FUNCTION.
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

______________________________________________________________________
Post your free ad now! http://personals.yahoo.ca