Re: referring to computed values from the select list in the where and order clauses - Mailing list pgsql-general

On Mon, Mar 01, 2004 at 09:21:32AM +0000, Richard Huxton wrote:
> On Monday 01 March 2004 01:04, Stephen Howard wrote:
> > I've got a moderately complex function defined which i then want to be
> >
> > able to test the value of, as well as select the value:
> >
> > select id, vector_cosine(document,'[ qw( foo,bar,baz )]') as threshold
> > from search_vectorspace where threshold > 0 order by threshold desc;
>
> Can't be done that way, I'm afraid. The aliasing is defined as occuring after
> the where clause has been evaluated.
>
> However, you can mark functions as immutable/stable/volatile which can allow
> PG to cache the query results. See the manuals (CREATE FUNCTION) for details.

Or wrap the query in a another query, like:

SELECT * from
  (select id, vector_cosine(document,'[ qw( foo,bar,baz )]') as threshold
   from search_vectorspace where threshold > 0) AS x
order by threshold desc;

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> If the Catholic church can survive the printing press, science fiction
> will certainly weather the advent of bookwarez.
>    http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow