From: Tom Lane <tgl@sss.pgh.pa.us>
To: Niklas Johansson <spot@tele2.se>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT composite type
Date: Wed, 05 Apr 2006 18:29:28 -0400
Message-ID: <12793.1144276168@sss.pgh.pa.us> (raw)
In-Reply-To: <40485D0C-8C3A-4206-9825-3A459934F356@tele2.se>
References: <200604051257.09980.michael@engtech.ca>
<40485D0C-8C3A-4206-9825-3A459934F356@tele2.se>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, spot@tele2.se
Subject: Re: SELECT composite type
In-Reply-To: <12793.1144276168@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox