Thread: Alias in WHERE clause

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?

score_a is a (rather computation-intensive :-) PL/Perl function which
returns an integer.

I am using PostgreSQL 7.0


--
Eric Jain

[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