Date: Wed, 05 Apr 2006 12:57:09 -0300
From: Michael Burke <michael@engtech.ca>
Subject: SELECT composite type
To: pgsql-sql@postgresql.org
Message-id: <200604051257.09980.michael@engtech.ca>
Organization: Engineering Technologies Canada Ltd.
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
Content-disposition: inline
User-Agent: KMail/1.9.1

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