Thread: Avoiding evaluating functions twice.

Hello,

I've got the following situation:

select expensive_function(table) from table
                     where expensive_function(table) is not null;

Is there a way to avoid that expensive_function is evaluated twice (if it's
not null) ?
I've tried stuff like immutable, but that doesn't do anything for me.
Of course I can do a select into, but my guess is it would be even more
expensive than evaluating these functions twice.

Thanks in advance,

Han Holl

han.holl@informationslogik.nl writes:
> select expensive_function(table) from table
>                      where expensive_function(table) is not null;

> Is there a way to avoid that expensive_function is evaluated twice (if it's
> not null) ?

You can do something like this:

select f from
(select expensive_function(table) as f from table offset 0) ss
where f is not null;

The "offset 0" bit is a hack that keeps the planner from flattening the
sub-select into the upper query, which would result in two copies of the
function expression, which is what you want to avoid.

            regards, tom lane

On Tuesday 04 October 2005 17:25, Tom Lane wrote:
> han.holl@informationslogik.nl writes:
> > select expensive_function(table) from table
> >                      where expensive_function(table) is not null;
> >
> > Is there a way to avoid that expensive_function is evaluated twice (if
> > it's not null) ?
>
> You can do something like this:
>
> select f from
> (select expensive_function(table) as f from table offset 0) ss
> where f is not null;
>
Thanks.

I think I can safely say I wouldn't have invented this magic in a year.

Cheers

Han Holl

> On Tuesday 04 October 2005 17:25, Tom Lane wrote:
> > han.holl@informationslogik.nl writes:
> > > select expensive_function(table) from table
> > >                      where expensive_function(table) is not null;
> > >
> > > Is there a way to avoid that expensive_function is evaluated twice (if
> > > it's not null) ?
> >
> > You can do something like this:
> >
> > select f from
> > (select expensive_function(table) as f from table offset 0) ss
> > where f is not null;

Why the offset 0?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461