pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT composite type
5+ messages / 4 participants
[nested] [flat]
* SELECT composite type
@ 2006-04-05 15:57 Michael Burke <michael@engtech.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Michael Burke @ 2006-04-05 15:57 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a function get_xy that returns a composite type with columns "x" and
"y". I would like to SELECT these as well as some data from a table, like
so:
SELECT
(get_xy(SetSRID(sightings.location, 26910), 4326)).x,
(get_xy(SetSRID(sightings.location, 26910), 4326)).y,
sightings.title
FROM sightings
WHERE sighting_id = 25;
This statement works, but I don't want to duplicate the function call as this
should be unnecessary. Selecting simply get_xy returns both fields in a
single column, which is undesirable.
I tried:
SELECT foo.x, foo.y, sightings.title
FROM sightings, get_xy(SetSRID(sightings.location, 26910), 4326) foo
WHERE sighting_id = 25;
But, because the function refers to sightings, I get this error:
ERROR: function expression in FROM may not refer to other relations of same
query level
...which is reasonable. 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.
I am using Postgres 8.1.2 (same with client) on FreeBSD, with PostGIS 1.1.1:
$ postmaster --version
postmaster (PostgreSQL) 8.1.2
Thanks in advance!
Mike.
--
Michael Burke
Engineering Technologies Canada Ltd. - http://www.engtech.ca/
michael@engtech.ca 1 (902) 628-1705
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT composite type
@ 2006-04-05 20:31 Niklas Johansson <spot@tele2.se>
parent: Michael Burke <michael@engtech.ca>
1 sibling, 1 reply; 5+ messages in thread
From: Niklas Johansson @ 2006-04-05 20:31 UTC (permalink / raw)
To: pgsql-sql
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;
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT composite type
@ 2006-04-05 22:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Niklas Johansson <spot@tele2.se>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2006-04-05 22:29 UTC (permalink / raw)
To: Niklas Johansson <spot@tele2.se>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT composite type
@ 2006-04-06 09:28 Markus Schaber <schabi@logix-tt.com>
parent: Michael Burke <michael@engtech.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Markus Schaber @ 2006-04-06 09:28 UTC (permalink / raw)
To: Michael Burke <michael@engtech.ca>; +Cc: pgsql-sql
Hi, Michael,
Michael Burke wrote:
> This statement works, but I don't want to duplicate the function call as this
> should be unnecessary.
Is this for aesthetic reasons (typing the query), or for speed reasons?
If the latter one is true, then check that all functions are defined as
"immutable" or, at least, "stable". Then the qery planner should flatten
them to be called only once.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT composite type
@ 2006-04-06 12:45 Michael Burke <michael@engtech.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Burke @ 2006-04-06 12:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-06 12:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-05 15:57 SELECT composite type Michael Burke <michael@engtech.ca>
2006-04-05 20:31 ` Niklas Johansson <spot@tele2.se>
2006-04-05 22:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-06 12:45 ` Michael Burke <michael@engtech.ca>
2006-04-06 09:28 ` Markus Schaber <schabi@logix-tt.com>
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