Re: SQL Programming Question

On 11 Sep 2010, at 12:09, Alban Hertroys wrote:

> It would be great to be able to use a WITH statement to lock down a data set for multiple subsequent operations,
somethinglike: 
>
> WITH nonduplicates (key, data1, data2, etc) AS (
>     SELECT key, data1, data2, etc FROM staging_table
>     EXCEPT
>     SELECT key, data1, data2, etc FROM live_table
> )
> INSERT INTO live_table (key, data1, data2, etc)
>     SELECT key, data1, data2, etc FROM nonduplicates
>     RETURNING key, data1, data2, etc
> UNION ALL
> DELETE FROM staging_table USING nonduplicates
>     WHERE key = nonduplicates.key
>     RETURNING key, data1, data2, etc;
>
> Or something like that. It's just an example from what I have in mind, after all ;)


Gosh, I was thinking too far ahead and forgot to explain why that would be cool!

First off, you'd end up with having moved all your non-duplicate data into the live_table and are left with all the
duplicatesin your staging_table. No need for an extra table to store them! 

Secondly, you get a list returned of all the non-duplicate records that were moved into the live_table. I realise that
shouldhave been a UNION and not a UNION ALL, or you get every record twice. As an alternative you could add a fictive
columnto each RETURNING statement to specify the origin of each record. That all depends on what you need the results
forof course... 
I think the RETURNING clauses are pretty much obligatory there, how else would you UNION that INSERT and DELETE
together?

Lastly, of course this is already entirely possible using a temp table, but that seems a bit ugly... Big kudos to the
peoplewho added WITH-queries to Postgres, I love that feature! :) 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c8b5a5710401646614364!