Re: Alias in WHERE clause

"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