By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,571 Members | 991 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,571 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
7 Replies

P: n/a
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

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

Nov 23 '05 #2

P: n/a
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 <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

iD8DBQFAQwizY5Twig3Ge+YRAhbeAKC5okljZwDZskN6d1QCyb KNpKEHeACg1cXE
W1A0yzZy/HgrpgvuSTr9xd8=
=n8QV
-----END PGP SIGNATURE-----

Nov 23 '05 #3

P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #5

P: n/a
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*****@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/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

Nov 23 '05 #6

P: n/a

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 <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

Nov 23 '05 #7

P: n/a
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 <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

Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.