[postgis-users] [OT] function expression in FROM cannot refer to other relations of same query level [SOLVED]

[Stephen Woodbridge]

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