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
