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, something like:
>
> 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 duplicates in 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 should have been a UNION and not a UNION ALL, or you get every record twice. As an alternative you could add a fictive column to each RETURNING statement to specify the origin of each record. That all depends on what you need the results for of 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 people who 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!