Avoiding evaluating functions twice.

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