From: Michael Burke <michael@engtech.ca>
To: pgsql-sql@postgresql.org
Subject: SELECT composite type
Date: Wed, 05 Apr 2006 12:57:09 -0300
Message-ID: <200604051257.09980.michael@engtech.ca> (raw)
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
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: <200604051257.09980.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