On 3/17/06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Merlin Moncure" <mmoncure@xxxxxxxxx> writes: > > select f(x) from t where id = 1 order by n; > > can cause f to execute for the entire table even if id is unique. > > Really? I'd consider it a bug if so. Compare > > select 1/x from t where x > 0 > > If the presence of zeroes in t can make this throw a zero-divide error, > the database is broken. In my mind the SQL spec is perfectly clear that > WHERE filtering occurs before evaluation of the SELECT targetlist. > (Sorting, however, occurs afterward --- so there are certainly potential > gotchas of this ilk. But the specific example you give is bogus.) You are quite right..I didn't state the problem properly. The particular one that burned me was actually: select f(x) from t where k order by y limit 1; ...which may or may not execute f(x) more than once depending on how the planner implements order by y...the limit clause does not necessarily guard against this, but a where clause does provide a guarantee. for posterity, the fix was: select f(q.x) from (select x from t where k order by y limit 1) q; if you will recall the f(x) in my case was a user_lock function and the results were not pleasant :-) So out of habit I tend to separate the extration from the function execution via subquery. Merlin
