On Wednesday 05 April 2006 19:29, Tom Lane wrote: > Niklas Johansson <[EMAIL PROTECTED]> writes: > > You could try > > > > SELECT foo.x, foo.y, title FROM > > (SELECT > > get_xy(SetSRID(sightings.location, 26910), 4326) AS foo, > > sightings.title > > FROM sightings > > WHERE sighting_id = 25) bar; > > Note however that the above is only a cosmetic answer: you avoid typing > the function call twice, but the planner will "flatten" the subquery > into the outer query and thereby end up with two evaluations anyway. > If you're really intent on avoiding the extra evaluation then you need > to do something to prevent the flattening from happening. One > handy trick is to use a LIMIT or OFFSET clause in the subquery as an > optimization fence:
Thanks for the suggestions. This case is mostly for aesthetic reasons, or perhaps to prevent typing errors in the future (in case I have to modify the function call). Forming the sub-query that way will effectively solve my problem, even if it doesn't specifically aid in efficiency -- however my get_xy function is STABLE, so it shouldn't harm it too much (unsure about SetSRID); additionally, adding the OFFSET 0 is an interesting trick that I will also try. Thus, my final query: SELECT (xy).x, (xy).y, title FROM (SELECT get_xy(SetSRID(sightings.location, 26910), 4326) AS xy, sightings.title FROM sightings WHERE sighting_id = 25 OFFSET 0) bar; Thanks again. Mike. -- Michael Burke Engineering Technologies Canada Ltd. - http://www.engtech.ca/ [EMAIL PROTECTED] 1 (902) 628-1705 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
