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/
Show quoted text
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
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:
Show quoted text
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
Import Notes
Reply to msg id not found: 20040301143050.C20780@mofo.meme.com
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