Alias in WHERE clause

[Charset iso-8859-1 unsupported, filtering to ASCII...]

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.

We just don't support aliases in WHERE, as you suggest. I see your
problem if score_a is complicated. The issue is that the target list is
not evaluated until _after_ the WHERE clause.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

"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

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 ...

Thanks... Unfortunatly the 'term' will be different for every query I
can't store any precomputed values. However I figure I could do the
following for every query:

SELECT url,score_a(text, CAST('term' AS TEXT)) AS score
INTO TEMP scores
FROM articles;

SELECT url,score
FROM scores
WHERE score > 0
ORDER BY score DESC;

Now I just hope this won't cause any problems if several users try to
issue different queries at the same time?

--
Eric Jain

"Eric Jain" <jain@gmx.net> writes:

Now I just hope this won't cause any problems if several users try to
issue different queries at the same time?

Nope. Each backend has its own TEMP tables, even if the logical table
names are the same.

regards, tom lane