Date: Thu, 06 Apr 2006 09:45:23 -0300
From: Michael Burke <michael@engtech.ca>
Subject: Re: SELECT composite type
In-reply-to: <12793.1144276168@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Message-id: <200604060945.24231.michael@engtech.ca>
Organization: Engineering Technologies Canada Ltd.
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT
Content-disposition: inline
References: <200604051257.09980.michael@engtech.ca>
	<40485D0C-8C3A-4206-9825-3A459934F356@tele2.se>
	<12793.1144276168@sss.pgh.pa.us>
User-Agent: KMail/1.9.1

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