> pgsql@mohawksoft.com writes:
>> For example I'll use the Oracle "contains" function, though this is not
>> exactly what I'm doing, it just illustrates the issue clearly.
>
>> select *, score(1) from mytable where contains(mytable.title, 'Winding
>> Road', 1) order by score(1);
>
>> The "contains" function does a match against mytable.title for the term
>> 'Winding Road' and both returns and saves an integer score which may be
>> retrieved later using the "score(...)" function.
>
> This is just a bad, bad idea. Side-effects in a WHERE-clause function
> are guaranteed to cause headaches. When (not if) it breaks, you get
> to keep both pieces.
Well, I guess I need to alter the question a bit.
I need to perform an operation during query time and there are multiple
results based on the outcome. For instance: (Lets try this)
select myrank(t1.column1, t2.column2, 1) as rank,
myscore(t1.column1,t2.column2, 1) as score from t1, t2 where
myrank(t1.column1,t2.column2) > 10 order by myscore(t1.column1,
t2.column2, 1) desc;
This is a bit messier, and I wanted to resist this approach as it is ugly.
The underlying code will check the values of the first and second
parameters and only perform the operation if a previous call did not
already act on the current parameters.
Now, can I assume that in the above select statement, that each
permutation of t1.column1 and t2.column2 will only be evaluated once and
that myscore(...) and myrank(...) will all be called before the next
permutation is evaluated?
So, basically, I don't want to recalculate the values for each and every
function call as that would make the system VERY slow.
