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