473,288 Members | 2,350 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

referring to computed values from the select list in the where andorder 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

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

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

Nov 23 '05 #1
7 2151
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
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
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
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
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

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Elaine Jackson | last post by:
List comprehensions don't work the way you intuitively expect them to work. I realize many people have no intuitions about how list comprehensions 'should' work, so if you recognize yourself in...
9
by: DMAC | last post by:
If i want to split a computed column into two or more columns based on the the length (its a varchar) of the computed column, how often will sql server determine what the computed column is?...
18
by: war_wheelan | last post by:
I am very new to Transact-SQL programming and don't have a programming background and was hoping that someone could point me in the right direction. I have a SELECT statement SELECT FIXID,...
4
by: Henning N?rg?rd | last post by:
I'm working as software developer mostely om SQL-server platform. On SQL-server we are using a lot of "Computed Columns" Does anyone know if that is possible to do in DB2 too ? I mean - define...
5
by: William Wisnieski | last post by:
Hello Everyone, I have a query by form with several list boxes. The user selects items from the list boxes and clicks a button that returns results in a datasheet subform. One of the list...
3
by: Remaniak | last post by:
Hi all, In my query I am trying to select values from my table ("data table") which lie between two values that are stored in a different table ("Contract list double 71") My Code is: ...
2
by: Jim.Mueksch | last post by:
I am having a problem with using calculated values in a WHERE clause. My query is below. DB2 gives me this error message: Error: SQL0206N "APPRAISAL_LESS_PRICE" is not valid in the context where...
7
by: Aamir Mahmood | last post by:
Hi All I have DataTable object. Is there a way that I can know which fields (columns) in the table are computed. Apparantly the DataTable.Columns returns all columns both computed and other....
2
by: GTalbot | last post by:
Hello fellow comp.infosystems.www.authoring.stylesheets colleagues, Imagine this situation: #grand-parent-abs-pos { height: 400px; position: absolute; width: 600px; }
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.