[postgis-users] OT: SQL problem/challenge

[Stephen Woodbridge]

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