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

[Stephen Howard]

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(d ocument,'[ qw( foo,bar,baz )]') as threshold
from search_vectorsp ace 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

[Richard Huxton]

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(d ocument,'[ qw( foo,bar,baz )]') as threshold
from search_vectorsp ace 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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Martijn van Oosterhout]

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(d ocument,'[ qw( foo,bar,baz )]') as threshold
from search_vectorsp ace 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(d ocument,'[ qw( foo,bar,baz )]') as threshold
from search_vectorsp ace where threshold > 0) AS x
order by threshold desc;

Hope this helps,
--
Martijn van Oosterhout <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAQwizY5T wig3Ge+YRAhbeAK C5okljZwDZskN6d 1QCybKNpKEHeACg 1cXE
W1A0yzZy/HgrpgvuSTr9xd8=
=n8QV
-----END PGP SIGNATURE-----

[Karam Chand]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Mike Mascari]

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/s...-appendix.html

Mike Mascari
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Karam Chand]

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 <ma*****@mascar i.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/s...-appendix.html
Mike Mascari

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Karl O. Pinc]

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(d ocument,'[ qw( foo,bar,baz )]') as

threshold

from search_vectorsp ace 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 <ko*@meme.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

[Stephen Howard]

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(d ocument,'[ qw( foo,bar,baz )]') as

threshold

> from search_vectorsp ace 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 <ko*@meme.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