From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Eric Jain" <jain(at)gmx(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Alias in WHERE clause |
Date: | 2000-05-20 15:41:38 |
Message-ID: | 13001.958837298@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"Eric Jain" <jain(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Louis-David Mitterrand | 2000-05-20 16:06:35 | Re: rules on INSERT can't UPDATE new instance? |
Previous Message | Bruce Momjian | 2000-05-20 14:41:53 | Re: rules on INSERT can't UPDATE new instance? |