Hi Stephen, I don't know if this is feasible with your data, but if you absolutely want to avoid the use of temporary tables, you could simply replace the A, B and C in the WHERE clause of your not working SQL query by the corresponding VALUES clauses.
Regards, Birgit. On 05.06.2010 16:33, Stephen Woodbridge wrote: > Stephen Woodbridge wrote: >> Hi, >> >> I have a challenging SQL problem, that I thought some of you might be >> able and interested to try and solve. >> >> My problem is that I need to compute some number of separate and >> costly queries as represented VALUES clauses below. And I need to >> eliminate the overlaps in the adjacent sub-selects, but not globally >> from the results. >> >> So in this example >> gid 6 and 7 would get eliminated from A and B and >> gid 11 and 12 would get eliminated from B and C but >> gid 1 would not get eliminated. >> >> Sadly I'm currently working on Postgresql 8.3.9 other wise I think >> using the WITH clause in 8.4 for would help. >> >> So at this point I'm kind of thinking that the only way I can do this >> with 8.3 is to "create temp table leg1 as select ..." and then work >> with the temp tables. This will be running in a plpgsql function it >> that matters. >> >> I would be interested in ideas/examples for both 8.3 and 8.4. >> >> Thanks, >> -Steve >> >> Here is a piece of not working SQL: >> >> select * >> from ( >> select gid, aa from >> (values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7)) as A (gid, aa) >> union all >> select gid, aa from >> (values (7,7),(6,6),(8,8),(9,9),(10,10),(11,11),(12,12)) as B >> (gid, aa) >> union all >> select gid, aa from >> (values (12,12),(11,11),(13,13),(14,14),(15,15),(16,16),(1,1)) as >> C (gid, aa) >> ) as foo >> -- the where clause does not work, specifically the sub-select >> -- this does global elimination, >> -- and it needs to be locally between sub-selects >> where gid not in ( >> (select gid from A intersect select gid from B) >> union >> (select gid from B intersect select gid from C) >> ) > > With a little more work and I was able to get this to work with temp > tables, although I would like to avoid temp tables if possible. > > create temp table A on commit drop as > select gid, aa from > (values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7)) as A (gid, aa); > > create temp table B on commit drop as > select gid, aa from > (values (7,7),(6,6),(8,8),(9,9),(10,10),(11,11),(12,12)) as B > (gid, aa); > > create temp table C on commit drop as > select gid, aa from > (values (12,12),(11,11),(13,13),(14,14),(15,15),(16,16),(1,1)) as > C (gid, aa); > > select * from ( > select * from A where gid not in ((select gid from A intersect > select gid from B)) > union all > select * from B where gid not in ( > ( select gid from A intersect select gid from B > union > select gid from B intersect select gid from C > ) > ) > union all > select * from C where gid not in ((select gid from B intersect > select gid from C)) > ) as foo; > > So I assume, that in 8.4 that I can convert the temp tables to WITH > clauses preceding the select. Is this the best that can be done with 8.3? > > -Steve > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
