Re: SELECT composite type

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