referring to computed values from the select list in the where andorder clauses

#1

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#2

On Monday 01 March 2004 01:04, Stephen Howard wrote:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#3

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:

Quote:

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 (AT) meme (DOT) com
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#4

On 2004.03.01 03:21 Richard Huxton wrote:

Quote:

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 (AT) meme (DOT) com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

referring to computed values from the select list in the where andorder clauses

comp.databases.postgresql.general comp.databases.postgresql.general

referring to computed values from the select list in the where andorder clauses - 02-29-2004 , 07:04 PM

Re: referring to computed values from the select list in the where and order clauses - 03-01-2004 , 03:21 AM

Re: referring to computed values from the select list in - 03-01-2004 , 02:25 PM

Re: referring to computed values from the select list in the where and order clauses - 03-01-2004 , 02:30 PM