Re: SELECT composite type

On Wednesday 05 April 2006 19:29, Tom Lane wrote:
> Niklas Johansson <spot@tele2.se> 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/
michael@engtech.ca  1 (902) 628-1705