Обсуждение: referring to computed values from the select list in the where and order clauses

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;

I've tried it both with and without an AS alias, but in both instances
pgsql complains that the column does not exist.  I would like to be able
to use the value computed with out having to recompute it every place i
wish to  use the value.  I've not had much luck finding this information
in the Postgres documentation.  Anyone know what syntax i should be using?

thanks,
Stephen

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.

--
  Richard Huxton
  Archonet Ltd

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

Hello

Is there any documentation that lists all the
PostgreSQL reserved words and function names?

Just dont want to create object names with those
words?

Karam

__________________________________
Do you Yahoo!?
Get better spam protection with Yahoo! Mail.
http://antispam.yahoo.com/tools

Karam Chand wrote:

> Hello
>
> Is there any documentation that lists all the
> PostgreSQL reserved words and function names?
>
> Just dont want to create object names with those
> words?

http://www.postgresql.org/docs/7.4/static/sql-keywords-appendix.html

Mike Mascari

Hey

Thanks for the pointer. In MySQL ( which I had been
using for a long time )...YEAR comes under
miscellenous function..like date etc. and keywords are
given as select, group etc.

What about PostgrSQL?

Regards
Karam

--- Mike Mascari <mascarm@mascari.com> wrote:
> Karam Chand wrote:
>
> > Hello
> >
> > Is there any documentation that lists all the
> > PostgreSQL reserved words and function names?
> >
> > Just dont want to create object names with those
> > words?
>
>
http://www.postgresql.org/docs/7.4/static/sql-keywords-appendix.html
>
> Mike Mascari
>


__________________________________
Do you Yahoo!?
Get better spam protection with Yahoo! Mail.
http://antispam.yahoo.com/tools

On 2004.03.01 03:21 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;
>
> 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.

Depending on your requirements you may be able to store the results
in a temporary table CREATE TABLE TEMP ... ; INSERT INTO...

Don't know that this is a better alternative.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

I've decided the STABLE keyword is probably the best bet.  Makes the
query a bit ugly, but does what i want.

Karl O. Pinc wrote:

>
> On 2004.03.01 03:21 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;
>>
>> 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.
>
>
> Depending on your requirements you may be able to store the results
> in a temporary table CREATE TABLE TEMP ... ; INSERT INTO...
>
> Don't know that this is a better alternative.
>
> Karl <kop@meme.com>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein
>