Thread: SELECT composite type

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

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

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

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

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