"Eric Jain" <jain@gmx.net> writes:
> I would like to be able to say:
> SELECT url,score_a(text, CAST('term' AS TEXT)) AS score FROM articles
> WHERE score > 0
> ORDER BY score DESC;
> This returns: ERROR: Attribute 'score' not found.
> The following works:
> SELECT url,score_a(text, CAST('term' AS TEXT)) AS score FROM articles
> WHERE score_a(text, CAST('term' AS TEXT)) > 0
> ORDER BY score DESC;
> Doesn't seem efficient to me? Or are the results from score_a cached
> somehow?
They're not (presently), but that doesn't change the fact that what you
propose is not SQL. The WHERE clause cannot refer to the results of
SELECT-list expressions because the SELECT list hasn't been computed
yet at the point where we are trying to decide whether to accept a
particular tuple. In general the SELECT list *can't* be computed until
afterwards (aggregate function results being the most obvious reason).
WHERE behaves differently than HAVING and ORDER BY in this respect,
since those are evaluated post-GROUPing and thus have basically the
same semantics as SELECT-list expressions.
It might help to think of the SELECT process as a pipeline:
raw tuples -> WHERE filter -> GROUP BY -> HAVING filter -> ORDER BY/DISTINCT
> score_a is a (rather computation-intensive :-) PL/Perl function which
> returns an integer.
If it's that expensive you might consider computing and storing the
results as an additional column in your table ... then you'd not
have to re-evaluate it for every tuple on each SELECT ...
regards, tom lane