Thread: referring to computed values from the select list in the where and order clauses
referring to computed values from the select list in the where and order clauses
From
Stephen Howard
Date:
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
Re: referring to computed values from the select list in the where and order clauses
From
Richard Huxton
Date:
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
Re: referring to computed values from the select list in the where and order clauses
From
Martijn van Oosterhout
Date:
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
Attachment
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
Re: referring to computed values from the select list in the where and order clauses
From
"Karl O. Pinc"
Date:
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 >