Hi again,

What is the standard way to restructure a query when you get the

"ERROR: function expression in FROM cannot refer to other relations of same query level"

So while this email was open and getting composed I did some more research and found an answer. So I get to present a problem and solution all at once. I think this probably has applicability to others here. Sorry for the cross post. If anyone has a better way of dealing with this please add to this thread.

This is my query. It was ported from Oracle that used the TABLE() function to expose the fields of the stored procedure and I think SQL99 defines this as LATERAL():

select a.*

, b.*

, abs(b.cdist - a.cdist)::real as ecdist

, abs(b.zdist - a.zdist)::real as ezdist

from

testdata a

, imt_rgeo_countyzip(a.x,a.y) as b

where

a.gid=b.gid and a.gid=552;

ERROR: function expression in FROM cannot refer to other relations of same query level

I tried this one which works except is calls the stored procedure 6 times (one for each result field):

select a.*

, b.*

, abs(b.cdist - a.cdist)::real as ecdist

, abs(b.zdist - a.zdist)::real as ezdist

from

testdata a

, (select c.gid, (imt_rgeo_countyzip(c.x,c.y)).*

from testdata c) as b

where

a.gid=b.gid and a.gid=552;

So following the previous thread on this problem I can transform this query into:

select a.*

, (b).*

, abs((b).cdist - a.cdist)::real as ecdist

, abs((b).zdist - a.zdist)::real as ezdist

from

(select c.*, imt_rgeo_countyzip(c.x,c.y) as b

from testdata c where gid=552 offset 0) as a;

Which converts the join into a subquery and only calls the function 1 time. This is anything but obvious! and it is still not obvious why it works the way it does. I think some of these problems are getting resolved in 9.0, but I have not upgraded any of my systems to that yet.

I hope this helps someone else so you do not need to spend all day banging your head against a similar problem like this.

Now back to your regular programming!

-Steve