From: Michael Burke <michael@engtech.ca>
To: pgsql-sql@postgresql.org
Subject: Re: SELECT composite type
Date: Thu, 06 Apr 2006 09:45:23 -0300
Message-ID: <200604060945.24231.michael@engtech.ca> (raw)
In-Reply-To: <12793.1144276168@sss.pgh.pa.us>
References: <200604051257.09980.michael@engtech.ca>
<40485D0C-8C3A-4206-9825-3A459934F356@tele2.se>
<12793.1144276168@sss.pgh.pa.us>
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
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: michael@engtech.ca
Subject: Re: SELECT composite type
In-Reply-To: <200604060945.24231.michael@engtech.ca>
* 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