To: Niklas Johansson <spot@tele2.se>
cc: pgsql-sql@postgresql.org
Subject: Re: SELECT composite type 
In-reply-to: <40485D0C-8C3A-4206-9825-3A459934F356@tele2.se> 
References: <200604051257.09980.michael@engtech.ca>
	<40485D0C-8C3A-4206-9825-3A459934F356@tele2.se>
Comments: In-reply-to Niklas Johansson <spot@tele2.se>
	message dated "Wed, 05 Apr 2006 22:31:16 +0200"
Date: Wed, 05 Apr 2006 18:29:28 -0400
Message-ID: <12793.1144276168@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Niklas Johansson <spot@tele2.se> writes:
> On 5 apr 2006, at 17.57, Michael Burke wrote:
>> So I basically want to call get_xy for every row in
>> sightings, and use its output for two columns; or perhaps there is  
>> another way to think of this.

> 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:

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
OFFSET 0) bar;

There are some other features such as DISTINCT that also prevent
flattening, but there seems no call for that here.

			regards, tom lane